太原理工大学 oracle 大型数据库实验报告

实验项目：

实验地点：

专业班级：软件工程

学生姓名：指导教师：

本科实验报告

大型数据库系统 创建数据库和表 多学科楼4506 0901 学号：200

2012年 4 月 17 日

一、 实验目的和要求

1. 了解数据库的结构以及一些基本概念。 2. 了解表的结构特点。

3. 了解Oracle 11g的基本数据类型。 4. 学会使用DBCA创建数据库。 5. 学会使用界面方式创建表。

6. 学会使用SQL语句手工创建数据库。 7. 学会使用SQL语句创建表。

二、 实验内容和原理

1. 能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。

2. 创建数据库必须要确定数据库名、所有者（即创建数据库的用户）、数据库大小、SGA分配和存储数据库的文件。

3. 确定数据库包含哪些表以及所包含的各表的结构，还要了解Oracle 11g的常用数据类型，以创建数据库的表。

4. 创建企业管理的员工管理数据库YGGL，包含Employees（员工自然信息 ）表、Departments（部门信息）表和Salary（员工薪水情况）表。各表的结构如下所示。

表T1.1 Employees表结构 列名 数据类型 长度 是否允许为空值 说明 EmployeeID Char 6 × 员工编号，主键 Name Char 10 × 姓名 Birthday Date × 出生日期 Sex Number 1 × 性别 Address Char 20 √ 地址 Zip Char 6 √ 邮编 PhoneNumber Char 12 √ 电话号码 Department Char 3 × 员工部门号、外键 表T1.2 Departments表结构 列名 数据类型 长度 是否允许为空值 说明 DepartmentID Char 3 × 部门编号、主键 DepartmentName Note Char Varchar2 20 100 × √ 部门号 备注 列名 EmplyeeID InCome OutCome 数据类型 Char Number Number 表T1.3 Salary表结构 长度 是否允许为空值 说明 6 × 员工编号、主键 8,2 8,2 × × 收入 支出

三、 主要仪器设备

Windows XP/7、oracle 10g/11g

四、 操作方法与实验步骤

1. 利用DBCA创建数据库

1) 数据库名称为YGGL，它的全局数据库名称为YGGL。

2) 控制文件三个，存放路径为……………………，名称分别为

CONTROL01.CR、CONTROL02.CR和CONTROL03.CR。 3) 重做日志文件三个，大小为100MB，存放路径为：………………，

名称分别为redo01.log、redo02.log和redo03.log。 4) 创建临时表空间temp01.dpf。

5) 数字字符集为ZHS16GBK，国家字符集为AL16UTF16。 6) 数据块大小为4KB。

进入DBCA，根据其提示逐步完成数据库的创建工作。

实际情况，我以创建表空间代替。

2. 利用DBCA删除数据库（实际不操作） 3. 在OEM和利用sql语句分别创建表

下面列出建表语句：

/**

创建Departments表 */

create table Departments( department_id char(3),

department_name varchar2(20) not null, note varchar2(100),

constraint departments_pk primary key(department_id) )

tablespace yggl;

/**

创建Salary表 */

create table Salary( employee_id char(6),

income number(8, 2) not null, outcome number(8, 2) not null,

constraint Salary_pk primary key(employee_id), constraint employee_id_foreign_key

foreign key(employee_id) references employees(employee_id) )

tablespace yggl;

五、 实验结果与分析

创建表空间：

以下为创建表，因为自己的命名习惯，对其中部分名称进行了修正，另外，一些数据的数据

类型该用更合适的数据类型。 创建employees表：

利用sql语句建表也全部成功。

六、 讨论、心得（可选）

本次试验考察内容基本为数据定义语言，有数据库的创建（因为以前已建过了，所以这次我用名称空间代替），表的创建（图形化操作与sql语句操作）。差不多就这样子，通过这次试验大概可以掌握oracle数据库的基本操作，主要有助于对其环境的熟悉。

本科实验报告

课程名称： 大型数据库系统

实验项目： 表数据插入、修改和删除

实验地点： 多学科楼4506

专业班级：软件工程0901 学号：20090 学生姓名：

指导教师：

2012年 4 月 19 日

一、 实验目的和要求

1. 学会使用PL/SQL语句对数据库表进行插入、修改和删除数据操作。 2. 学会使用SQL Developer对数据库表进行插入、修改和删除数据的操作。

3. 了解数据更新操作时要注意数据完整性。 4. 了解PL/SQL语句对表数据操作的灵活控制功能。

二、 实验内容和原理

1. 了解对表数据的插入、删除、修改都属于表数据的更新操作。对表

数据的操作可以在SQL Developer中进行，也可以由PL/SQL语句实现。

2. 掌握PL/SQL语句中用于对表数据进行插入、修改和删除的命令分别

是INSERT、UPDATE和DELETE（或TRANCATE TABLE）。 3. 在执行插入、删除、修改等数据更新操作时，必须保证数据的完整

性。

4. 使用PL/SQL语句在对表数据进行插入、修改及删除时，比在OEM

中操作表数据更为灵活，功能更强大。

在实验1中，用于实验的YGGL数据库中的三个表已经建立，现在要将各表的样本数据添加到表中。样本数据如表T2.1、表T2.2和表T2.3所示。

表T2.1 Employees表数据样本 编号 姓名 出生日期 性住址 邮编 电话号部门别 码 号 0000王林 1966-01-1 中山路210003 8335562 01 23 32-1-508 68 0100伍容1976-03-1 北京东路210001 8332131 08 华 28 100-2 21 表T2.2 Departments表数据样本 部门号 部门名称 备注 部门号 部门名称 备注 1 2 3 财务部 人力资源部 经理办公室 NULL NULL NULL 4 5 研发部 市场部 NULL NULL 编号 000001 010008 102201 收入 2100.8 1582.62 2568.88 表T2.3 Salary表数据样本 支出 编号 收入 123.09 88.03 185.65 1091 020010 020018 3259.98 2860.0 2347.68 支出 281.52 198.0 180.0 三、 主要仪器设备

WINDOWS XP/WINDOWS 7 Oracle 10g/11g

四、 操作方法与实验步骤

分别使用SQL Developer和PL/SQL语句，在实验1建立的数据库YGGL的表Employees、表Departments和表Salary中插入多行数据记录，然后修改和删除一些记录。使用PL/SQL命令进行有的修改和删除。

(一) 使用SQL Developer操作数据

启动SQL Developer，展开yggl_ora连接，单击”Employees”表，在左边窗口中选择”Data”选项卡。在此窗口中，单击”Insert row”按钮，表中将增加一个新行，在新行中双击一列空白处后输入新数据，输完后单击”Commit Changes”按钮，将数据保存到数据库中。修改数据的方法和添加数据类似，如果要删除一行数据，选中该行数据，单击”Delete Selected Row(s)”按钮，之后该行的行号前会显示一个“——”号，删除后单击”Commit Changes”按钮保存。

(二) 使用PL/SQL命令操作数据

1. 使用PL/SQL语句分别向YGGL数据库的表Employees、表Departments和表Salary中插入插入一行记录。

在启动SQL*Plus窗口或SQL Developer的代码编辑窗口中，输入以下PL/SQL语句并执行：

一下为对3个表的数据的所有插入语句：

/**

向DEPARTMENTS表中插入数据样本 */

insert into DEPARTMENTS values('001', '财务部', null); insert into DEPARTMENTS

values('002', '人力资源部', null); INSERT INTO Departments

VALUES('003','经理办公室',NULL); INSERT INTO Departments

VALUES('004','研发部',NULL); INSERT INTO Departments

VALUES('005','市场部',NULL); commit; /**

向employee表中插入数据样本 */

insert into employees

values('000001', '王林', to_date('1966-01-23', 'YYYY-MM-DD'), 1, '中山路 32-1-508', '210003', '83355668', '002'); INSERT INTO Employees

VALUES('010008','伍容华',TO_DATE('19760328','YYYYMMDD'),1, '北京东路100-2','210001','83321321','001'); insert into employees

values('020010', '王向荣', to_date('1982-12-19', 'YYYY-MM-DD'), 1, '四牌楼 10-0-108', '210006', '83792361', '001'); INSERT INTO Employees

VALUES('020018','李丽',TO_DATE('19600723','YYYYMMDD'),0, '中山东路102-2','210002','83413301', '001'); INSERT INTO Employees

VALUES('102201','刘明',TO_DATE('19721018','YYYYMMDD'),1, '虎距路100-2','210013','83606608','005'); INSERT INTO Employees

VALUES('102208','朱俊',TO_DATE('19650928','YYYYMMDD'),1, '牌楼巷5-3-106','210004','84708817','005'); INSERT INTo Employees

VALUES('1091','钟敏',TO_DATE('19790810','YYYYMMDD'),0, '中山路10-3-105','210003','83346722','003'); INSERT INTO Employees

VALUES('111006','张石兵',TO_DATE('19741001','YYYYMMDD'),1, '路34-1-203','210010','84563418','005');

INSERT INTO Employees

VALUES('210678','林涛',TO_DATE('19770402','YYYYMMDD'),1, '中山北路24-35','210008','83467336','003'); INSERT INTo Employees

VaLUES('302566','李玉珉',TO_DATE('19680920','YYYYMMDD'),1, '热和路209-3','210001','58765991','004'); INSERT INTO Employees

VALUES('308759','叶凡',TO_DATE('19781118','YYYYMMDD'),1, '北京西路3-7-52','210002','833001','004'); INSERT INTO Employees

VALUES('504209','陈琳琳',TO_DATE('19690903','YYYYMMDD'),0, '汉中路120-4-12','210018','84468158','004'); commit; /**

向Salary表中插入数据样本 */

insert into Salary

values('000001', 2100.8, 123.09); INSERT INTO Salary

VALUES('010008',1582.62,88.03); INSERT INTO Salary

VALUES('102201',2569.88,185.65); INSERT INTO Salary

VALUES('111006',1987.01,79.58); INSERT INTO Salary

VALUES('504209',2066.15,108.0); INSERT INTO Salary

VALUES('302566',2980.7,210.2); INSERT INTO Salary

VALUES('1091',3259.98,281.52); INSERT INTO Salary

VALUES('020010',2860.0,198.0); INSERT INTO Salary

VALUES('020018',2347.68,180.0); INSERT INTO Salary

VALUES('308759',2531.98,199.08); INSERT INTO Salary

VALUES('210678',2240.0,121.0); INSERT INTO Salary

VALUES('102208',1980.0,100.0); commit;

2. 使用PL/SQL命令修改表Salary中的某个记录的字段值。 语句:

/**

update SALARY表 **/

UPDATE SALARY SET INCOME = 20

WHERE EMPLOYEE_ID = '000001';

COMMIT;

按回车键，执行上述语句，将编号为000001的职工收入改为20.在OEM中打开YGGL数据库的Salary表，观察数据变化。

3. 使用PL/SQL语句修改表Salary中的所有记录的字段值。

在SQL*Plus界面中输入以下PL/SQL语句： 语句

/**

* 将其所有行的IMCOME加100 **/

UPDATE SALARY

SET INCOME = INCOME + 100;

COMMIT;

按回车键，执行上述语句，将所有职工的收入增加100。 可见，使用PL/SQL语句操作表数据比在SQL Developer中操作表数据更为灵活。输入以下PL/SQL语句，观察数据变化。 SELECT* FROM Salary;

4. 使用DELETE语句删除Salary表中一行记录。

/**

* 删除salary表中的一行记录 **/

DELETE FROM SALARY

WHERE employee_id = '010008';

COMMIT;

5. 使用TRANCATE TABLE语句删除表中所有行。

在SQL*Plus界面中输入以下PL/SQL语句：

/**

*清空表 **/

delete from Salary; delete from employees; delete from departments;

TRUNCATE TABLE SALARY;

五、 实验结果与分析

按上述过程操作，试验结果均满足预期要求。 六、 讨论、心得（可选）

本次试验主要考察对表的数据的基本更新操作，即dml语言。通过对表的增删改操作熟悉对应的sql语句以及OEM操作。

课程名称：

实验项目：

实验地点：

专业班级：软件工程

学生姓名：指导教师：

本科实验报告

大型数据库系统 数据库的查询和视图 多学科楼506 0901 学号：200900

2012年 4月21日

一、实验目的和要求

1、掌握SELECT语句的基本语法 2、掌握子查询的表示方法 3、掌握连接查询的表示方法 4、掌握数据汇总的方法

5、掌握SELECT语句的GROUP BY子句的作用和使用方法 6、掌握SELECT语句的ORDER BY子句的作用和使用方法 7、掌握视图的使用方法

二、实验内容

1. 了解SELECT语句的基本语法格式和执行方法

2. 了解子查询的表示方法、连接查询和数据汇总的方法 3. 了解SELECT语句的GROUP BY子句的作用和使用方法 4. 了解SELECT语句的ORDER BY子句的作用 5. 了解视图的作用和创建方法 6. 了解视图的使用方法

三、实验环境与设备

Win7、Oracle 11g

四、操作方法与实验步骤

1、SELECT语句的基本使用

（1）对于实验2给出的数据库表结构，查询每个雇员的所有数据 在SQL*Plus窗口或SQL Developer中输入语句并执行 SELECT * FROM Employees;

SELECT * FROM Departments; SELECT * FROM Salary; （2）查询每个雇员的地址和电话 /**

* 查询每个雇员的地址和电话 **/

SELECT ADDRESS, PHONE_NUMBER

FROM EMPLOYEES;

（3）查询EmployeeID为000001的雇员的地址和电话 SELECT Address,PhoneNumber FROM Employees

WHERE Employee_ID='000001';

（4）查询Employees表中所有女雇员的地址和电话，使用AS子句将结果

中各列的标题分别指定为地址和电话

SELECT Address AS 地址,PhoneNumber AS 电话 FROM Employees WHERE sex=0;

/**

* 查询每个女性雇员的地址和电话，并对列名重新命名 **/

SELECT ADDRESS as 地址, PHONE_NUMBER 电话 FROM EMPLOYEES

WHERE SEX = '0';

（5）计算ID号以10开头的雇员的实际收入 /**

* 计算ID号以10开头的雇员的实际收入 **/

SELECT employee_id， INCOME - OUTCOME AS 实际收入 FROM SALARY

WHERE EMPLOYEE_ID LIKE '10%';

2、子查询的使用

（1） 查找在财务部工作的雇员的情况

SELECT * FROM EMPLOYEES WHERE Department_ID= (

SELECT Department_ID FROM Departments

WHERE DepartmentName='财务部' );

（2） 查询财务部年龄不低于所有研发部雇员年龄的雇员的姓名

/**

* 查找财务部年龄不低于所有研发部雇员年龄的雇员的姓名 **/

SELECT * FROM EMPLOYEES WHERE department_id IN (

SELECT DEPARTMENT_ID FROM departments WHERE department_name = '财务部' ) AND

birthday <= ALL (

SELECT BIRTHDAY FROM EMPLOYEES WHERE DEPARTMENT_ID IN (

SELECT DEPARTMENT_ID FROM departments WHERE department_name = '研发部' )

);

（3） 查找比所有财务部的雇员收入都搞得雇员的姓名

/**

* 查找比所有财务部的雇员收入都要高的雇员的姓名 **/

SELECT NAME FROM EMPLOYEES WHERE EMPLOYEE_id IN (

SELECT EMPLOYEE_ID FROM SALARY WHERE INCOME >= ALL (

SELECT INCOME FROM SALARY WHERE EMPLOYEE_ID IN (

SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = (

SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = '财务部' ) ) )

)

3、连接查询的使用

（1）查询每个雇员的情况以及其薪水的情况

SELECT Employees.*,Salary.* FROM Employees,Salary

WHERE Employees.Employee_ID=Salary.Employee_ID; （2）查找财务部收入在2200元以上的雇员的姓名及其薪水详情 /**

* 查找财务部收入在2200元以上的雇员姓名及其薪水详情 **/

SELECT name, INCOME, OUTCOME

FROM EMPLOYEES, SALARY, DEPARTMENTS WHERE employees.employee_id = salary.employee_id AND

employees.department_id = departments.department_id AND

departments.department_NAME = '财务部' AND

INCOME > 2200;数据汇总

（1） 求财务部雇员的平均收入

/**

* 查找财务部雇员的平均收入 **/

SELECT AVG(INCOME) FROM SALARY, EMPLOYEES, DEPARTMENTS WHERE salary.employee_id = employees.employee_id AND

employees.department_id = departments.department_id AND

departments.department_name = '财务部';

（2） 求财务部雇员的平均实际收入

SELECT AVG(InCome-OutCome) AS 财务部平均实际收入 FROM Salary

WHERE Employee_ID IN

(SELECT Employee_ID FROM Employees WHERE Department_ID=

(SELECT Department_ID FROM Departments WHERE Department_Name='财务部')); （3） 求财务部雇员的总人数

SELECT COUNT(Employee_ID) FROM Employees WHERE Department_ID=

(SELECT Department_ID FROM Departments WHERE Department_Name='财务部');

5、GROUP BY和ORDER BY子句的使用 （1）求各部门的雇员数 /**

* 求各部门的雇员数 **/

SELECT DEPARTMENT_NAME, COUNT(*) FROM EMPLOYEES, departments WHERE employees.department_id = departments.department_id

GROUP BY DEPARTMENTS.department_NAME;

（2）将各部门的情况按收入由低到高排列

/**

* 将各雇员的情况按收入由低到高排序 **/

SELECT EMPLOYEES.*, SALARY.INCOME, salary.outcome FROM EMPLOYEES, SALARY

WHERE employees.employee_id = salary.employee_id

ORDER BY INCOME;

6、使用视图 （1）创建视图

a、查看雇员的实际情况

CREATE OR REPLACE VIEW cx_employees

AS

SELECT EmployeeID,Name,Birthday,Sex,DepartmentID FROM Employees;

b、各部门经理只能查找本部门雇员的薪水情况，如财务经理

想查看自己部门雇员姓名及其薪水详情

/**

* 创建财务部视图 **/

CREATE OR REPLACE VIEW CX_SALARY AS

SELECT NAME, INCOME, OUTCOME FROM EMPLOYEES, SALARY, DEPARTMENTS WHERE employees.employee_id = salary.employee_id

AND employees.department_id = departments.department_id AND department_name = '财务部';

（2）使用视图 /**

* 查询财务部雇员薪水情况 **/

SELECT * FROM CX_SALARY;

五、实验结果与分析

查询employees表的全部数据

查询每个雇员的地址和电话

查询每个女性雇员的地址和电话，并对列名重新命名

计算ID号以10开头的雇员的实际收入

查找财务部年龄不低于所有研发部雇员年龄的雇员的姓名

查找比所有财务部的雇员收入都要高的雇员的姓名

查找财务部收入在2200元以上的雇员姓名及其薪水详情

查找财务部雇员的平均收入

求各部门的雇员数

将各雇员的情况按收入由低到高排序

查询财务部雇员薪水情况

六、讨论、心得

本实验考察对表的查询操作，算是sql语句中最常用的操作，这也是我们每个学生应该必须掌握的基本操作，此后是关于视图的部分，它是一个很有效的功能，能够有效各用户只对自己有权操作的数据进行操作。

课程名称：

实验项目：

实验地点：

专业班级：软件工程学生姓名：指导教师：

本科实验报告

大型数据库系统 索引和完整性 多学科楼506 0901 学号：2009

2012年 4月21日

一、实验目的和要求

目的：

（1）掌握索引的使用方法

（2）掌握数据完整性的概念及分类 （3）掌握各种数据完整性的实现方法 要求：

（1）了解索引的作用与分类 （2）掌握索引的创建

（3）理解数据完整性的概念及分类 （4）了解各种数据完整性的实现方法

二、实验内容和原理

1、建立索引

对YGGL数据库中Employees表的DepartmentID列建立索引 /**

* 在Employees表的Departm_ID 列建立索引 **/

create index EMP_DEID ON EMPLOYEES(DEPARTMENT_ID)

TABLESPACE USERS PCTFREE 48 INITRANS 10 MAXTRANS 100

STORAGE (INITIAL K NEXT K MINEXTENTS 5 MAXEXTENTS 20 PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1)

PARALLEL (DEGREE DEFAULT); 2、实现域完整性

/**

* 在Employees表的phone_number 列上建立check约束 **/

ALTER TABLE EMPLOYEES

ADD( CONSTRAINT CH_PHONE CHECK(PHONE_NUMBER BETWEEN '0' AND '9'));

3、实现实体完整性 （参见实验1） 4、实现参照完整性 （参见实验1）

三、主要仪器设备

Win7、Oracle 11g

四、实验结果与分析（必填）

在Employees表的Departm_ID 列建立索引

在Employees表的phone_number 列上建立check约束

五、讨论、心得

本次实验首先是对索引的建立，它是一种可以提升用户查询效率的机制，在这一部分，我有注意到建立索引时是需要指定模式名的，似乎不同的用户查询同一张表格可能会用到不同的索引，不知道这样理解正不正确。

然后就是关于完整性的部分，由于在实验一建立表的时候，我就已经安照要求建立好了完整性约束，所以这里没有再重复做。

课程名称：

实验项目：

实验地点：

专业班级：软件工程

学生姓名：指导教师：

本科实验报告

大型数据库系统 PL/SQL编程 多学科楼506 1 学号：2002

2012年 4月21日

一、 实验目的与要求

（1） 进一步巩固第2章~第4章所学内容 （2） 掌握变量的分类与使用 （3） 掌握各种运算符的使用 （4） 掌握各种控制语句的使用

（5） 掌握系统函数及用户自定义函数的使用

二、 实验内容和原理

（1） （2） （3） （4）

了解PL/SQL支持的各种基本数据类型

了解PL/SQL各种运算符、控制语句的功能及使用方法 了解系统函数的调用方法

了解用户自定义函数时用的一般步骤

三、 主要仪器设备

Windows XP/7、oracle 10g/11g 四、 操作方法与实验步骤

1．

条件结构的使用

在SQL*Plus的编辑窗口中分别输入【例6.5】、【例6.6】、【例6.7】和例【6.8】程序并执行。观察结果。

2．

循环结构的使用

在SQL*Plus的编辑窗口中分别输入【例6.9】、【例6.10】、【例6.11】和例【6.12】程序并执行。观察结果。

3．

选择和跳转语句

在SQL*Plus的编辑窗口中分别输入【例6.13】和【例6.14】程序并执行。观察结果。

4．

自定义函数的使用

（1） 定义一个函数实现如下功能。对于给定的DepartmentID值，查询

该值在Departments表中是否存在，若存在返回0，否则返回-1。

语句：

/**

* 给定一个Department_id值，查询其在Departments表中是否存在，若存在返回0，否则返回-1. **/

CREATE OR REPLACE FUNCTION CHECK_ID (P_DEPARTMENT_ID IN CHAR) RETURN NUMBER AS

NUM NUMBER; BEGIN

SELECT COUNT(*) INTO NUM FROM DEPARTMENTS

WHERE DEPARTMENT_ID = P_DEPARTMENT_ID;

IF NUM >0 THEN NUM := 0; ELSE

NUM := -1; END IF;

RETURN (NUM); END;

（2） 写一段PL/SQL脚本程序调用上述函数。当向Employees表中插入

一条记录时，首先调用CHECK_ID检索该记录的DepartmentID值在表Departments的DepartmentID字段中是否存在对应值，若存在，则将该记录插入Employees表。

语句：

/**

* 对上述函数进行测试 **/

set serveroutput on DECLARE

NUM NUMBER; BEGIN

NUM := check_id('002'); IF NUM = 0 THEN

dbms_output.put_line('存在！');

INSERT INTO EMPLOYEES VALUES(

'990210', '张文', TO_DATE('19820324', 'yyyymmdd'), 0, '南京镇江路2号', '210009', '3497534', '002' ); END IF; END ;

五、 实验结果与分析

给定一个Department_id值，查询其在Departments表中是否存在，若存在返回0，否则返回-1.

对上述函数进行测试

六、 讨论、心得

本实验，书上给的实例代码是不能正确运行的，主要原因是PL/SQL在执行时并不区分大写小，所以必须给参数和变量命不同的名字。

然后就是，在调试程序时，我用了dbms_output.put_line('存在！');这样的打印语句，它需要打开输入服务才能在sqldeveloper环境下正确运行。set serveroutput on;

本科实验报告

课程名称：

实验项目：实验

实验地点：

专业班级：学生姓名：指导教师： 大型数据库系统 6 存储过程和触发器的使用 学院楼 502 软件0901 学号：20

2012年 4月21日

一、 实验目的和要求

(1) 掌握存储过程的使用方法 (2) 掌握触发器的使用方法 二、 实验内容和原理

1. 创建触发器

对表Employees的DepartmentID列与表Departments 的DepartmentID列应满足参照完整性，规则如下：

1） 向表Employees添加一条记录时，该记录的

DepartmentID在Departments表中应存在： 2） 修改Departments表DepartmentID字段时在

Employees表中应对应修改。

3） 删除Departments表中一记录时，该记录对于于

Employees表中的记录也应删除. 利用触发器实现上述约束。 2. 创建存储过程

1） 添加职员记录的存储过程EmployeeAdd。 2） 修改职员记录的存储过程EmployeeUpdate。 3） 删除职员记录的存储过程EmployeeDelete。 3. 调用存储过程 三、 主要仪器设备

Win7电脑一台，oracle11g 四、 操作方法与实验步骤

1. 创建触发器 对表Employees的

DEPARTMENT_ID列与表Departments

的 DEPARTMENT_ID列应满足参照完整性，规则如下：

1） 向表Employees添加一条记录时，该记录的

DEPARTMENT_ID在Departments表中应存在：

/**

* 向表Employees添加或修改一条记录时，该记录的 DEPARTMENT_ID在Departments表中应存在 **/

CREATE OR REPLACE TRIGGER EMPLOYEES_INS

AFTER INSERT OR UPDATE ON EMPLOYEES FOR EACH ROW DECLARE

NUM NUMBER; BEGIN

SELECT COUNT(*) INTO NUM FROM DEPARTMENTS

WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID; IF NUM = 0 THEN

RAISE_APPLICATION_ERROR(-20001, '职员所在的部门不存在'); END IF; END ;

2） 修改Departments表 DEPARTMENT_ID字段时在

Employees表中应对应修改。

/**

* 修改Departments表 DEPARTMENT_ID字段时在Employees表中应对应修改 **/

CREATE OR REPLACE TRIGGER DEPARTMENTS_UPDATE AFTER UPDATE ON DEPARTMENTS FOR EACH ROW BEGIN

UPDATE EMPLOYEES

SET DEPARTMENT_ID = :NEW.DEPARTMENT_ID WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID; END;

3） 删除Departments表中一记录时，该记录对于于

Employees表中的记录也应删除.

/**

* 删除Departments表中一记录时，该记录对于于Employees表中的记录也应删除 **/

CREATE OR REPLACE TRIGGER DEPARTMENTS_DELETE AFTER DELETE ON DEPARTMENTS FOR EACH ROW BEGIN

DELETE FROM EMPLOYEES

WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID; END;

因为Employees表与Salary表存在外键约束，所以，对于此也建立一个触发器以使得上面的触发器有效。

/**

* 删除Employees表中一记录时，该记录对于于Salary表中的记录也应删除 **/

CREATE OR REPLACE TRIGGER EMPLOYEES_SALARY AFTER DELETE ON EMPLOYEES FOR EACH ROW BEGIN

DELETE FROM SALARY

WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID; END;

2. 存储过程的创建及调用

1） 添加职员记录的存储过程EmployeeAdd。

/**

* 添加职员记录的存储过程EmployeeAdd **/

CREATE OR REPLACE PROCEDURE EMPLOYEE_ADD (

P_EMPLOYEE_ID IN CHAR, P_NAME IN CHAR, P_BIRTHDAY IN DATE, P_SEX IN NUMBER,

P_ADDRESS IN CHAR, P_ZIP IN CHAR, P_PHONE_NUMBER IN CHAR, P_DEPARTMENT_ID IN CHAR ) AS BEGIN

INSERT INTO EMPLOYEES VALUES (

P_EMPLOYEE_ID, P_NAME, P_BIRTHDAY, P_SEX, P_ADDRESS, P_ZIP,

P_PHONE_NUMBER, P_DEPARTMENT_ID );

COMMIT; END;

EXEC EMPLOYEE_ADD('990230', '刘朝', TO_DATE('190909', 'YYYYMMDD'), 1, '武汉小洪山5号', '210003', '83355668', '003');

（这里注意，调用存储过程时不能换行！）

2） 修改职员记录的存储过程EmployeeUpdate。

/**

* 修改职员记录的存储过程EmployeeUpdate **/

CREATE OR REPLACE PROCEDURE EMPLOYEE_UPDATE (

OLD_EMPLOYEE_ID IN CHAR, P_NAME IN CHAR, P_BIRTHDAY IN DATE, P_SEX IN NUMBER,

P_ADDRESS IN CHAR, P_ZIP IN CHAR, P_PHONE_NUMBER IN CHAR, P_DEPARTMENT_ID IN CHAR ) AS BEGIN

UPDATE EMPLOYEES SET name = P_NAME,

BIRTHDAY = P_BIRTHDAY, SEX = P_SEX,

ADDRESS = P_ADDRESS, ZIP = P_ZIP,

PHONE_NUMBER = P_PHONE_NUMBER, DEPARTMENT_ID = P_DEPARTMENT_ID

WHERE EMPLOYEE_ID = OLD_EMPLOYEE_ID; COMMIT; END;

EXEC EMPLOYEE_UPDATE('990230', '刘天', TO_DATE('190909', 'YYYYMMDD'), 1, '武汉小洪山5号', '210003', '83355668', '003');

3） 删除职员记录的存储过程EmployeeDelete。

/**

* 删除职员记录的存储过程EmployeeDelete **/

CREATE OR REPLACE PROCEDURE EMPLOYEE_DELETE (ID IN CHAR) AS BEGIN

DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = ID; COMMIT; END;

EXEC EMPLOYEE_DELETE('990230');

五、 实验结果与分析

验证触发器1:

insert into employees

values('009001', '王林2', to_date('1966-01-23', 'YYYY-MM-DD'), 1,

'中山路 32-1-508', '210003', '83355668', '006');

验证触发器2(注意：由于此触发器会与前面的触发器产生冲突，所以首先要删掉前面那个触发器)

update departments

set department_id = '007' where department_id = '002'; 重新查询后：

验证触发器3：

DELETE FROM DEPARTMENTS WHERE DEPARTMENT_ID = '002';

存储过程均正确实现功能。 六、讨论、心得

这一部分花了我最多的时间，首先吐槽一下关于这本书错误之详细，几乎覆盖了在PL/SQL命令中能够发现的所有错误。 在实现触发器之时，我遇到了很多问题，我总是这么悲剧的，也就麻木了，主要一个是关于触发器“死锁”的问题，最开始我实现触发器后，测验插入语句时，它报错说无法从套接字中获取更多的数据，之后无论执行什么语句都会报这样一个sql错误，仿佛它进入了死循环似的，无法在响应任何命令，只能断开连接重连。后来找到问题原因是我应用:NEW使多了一个空格，但具体原因仍然未知。 其他就是关于外键约束，已经触发器之间的冲突问题，语句基本上都改写了，浪费了很多宝贵的时间。 还有做的时候发现，对存储过程的调用语句居然必须写在一行上，这个设定无语了。

本科实验报告

课程名称：

实验项目：

实验地点：

专业班级：学生姓名：指导教师： 大型数据库系统 用户、角色和概要文件 学院楼 502 软件0901 学号：200

2012年 4月21日

一、 实验目的和要求

（1） 掌握数据库的安全功能。 （2） 掌握使用用户实现数据库安全性。 （3） 掌握使用角色实现数据库安全性。 （4） 掌握使用该药文件实现数据库安全性。

二、 实验内容和原理

（1） 了解数据库安全性管理。

（2） 了解数据库安全姓包括的几个方面。 （3） 了解使用用户实现数据库安全性的方法。 （4） 了解使用角色实现数据库安全性的方法。 （5） 了解使用概要文件实现数据库安全性的方法。

三、 主要仪器设备

Windows XP/7、oracle 10g/11g

四、 操作方法与实验步骤

1、创建用户

在YGGL数据库中创建一个用户MANAGER，授予DBA角色和SYDBA系统权限，它可以替代system系统用户。

/**

* 创建用户 **/

CREATE USER MANAGER PROFILE \"DEFAULT\"

IDENTIFIED BY MANAGER

DEFAULT TABLESPACE \"USERS\" TEMPORARY TABLESPACE \"TEMP\" ACCOUNT UNLOCK;

GRANT SYSDBA TO \"MANAGER\"; GRANT \"CONNECT\" TO \"MANAGER\"; GRANT \"DBA\" TO \"MANAGER\";

ALTER USER MANAGER GRANT CONNECT THROUGH SYSTEM;

2、创建角色

在YGGL数据库中创建一个角色ADMIN，授予DBA角色和SYSDBA系统

权限。

/**

* 创建角色 **/

CREATE ROLE ADMIN

IDENTIFIED BY \"MANAGER\";

GRANT \"CONNECT\" TO ADMIN;

GRANT \"DBA\" TO ADMIN;

3、创建概要文件

在YGGL数据库中创建概要文件YGGL_PROFILE并分配给用户MANAGER。

/**

* 创建概要文件 **/

CREATE PROFILE \"YGGL_PROFILE\" LIMIT COMPOSITE_LIMIT DEFAULT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 20 PASSWORD_GRACE_TIME 60 PASSWORD_LIFE_TIME 30

PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME 120

PASSWORD_VERIFY_FUNCTION DEFAULT;

五、 实验结果与分析

创建用户

创建角色

创建概要文件

六、 讨论、心得（可选）

这一部分实验主要是关于数据库权限的操作，用户那部分进行的比较顺利，然后角色时发现即使是以sys的sysdba登陆也不能给admin角色授予sysdba角色，所以只好下结论，这是不合法的操作，不知道是不是别的原因导致的。

然后，就是创建概要文件时，书上的代码少了一个Limit关键字，这个看来是必须有的。