数据库实验
(安工大周兵老师)
数据库实验报告(安工大周兵老师)
《数据库系统概论》
实验报告书
专业班级 学 号 姓 名 指导教师
安徽工业大学计算机学院
软件工程***班
********* *** 周兵
实验一:数据定义语言
[ 实验日期 ] 年 月 日 [ 实验目的 ]
熟悉Oracle上机环境及Oracle客户端的配置;熟练掌握和
使用DDL语言,建立、修改和删除数据库
、主键、外键约束关系
和索引。 [ 实验内容 ]
Oracle上机环境以及Oracle客户端的配置参见附录。 1( SQL数据定义语句:
例1-1: 建立教学数据库的四个数据库表,其中Student表中不包含SSEX 字段,Sname 字段为Sname且可为空。 一、建表
1:创建表Student CREATE TABLE Student ,
SNAME CHAR NOT NULL, SDEPT CHAR NOT NULL,
SCLASS CHAR NOT NULL, SSEX CHAR, SAGE NUMBER,
CONSTRAINT SNO_PK PRIMARY KEY); 2:创建表
Course CREATE TABLE Course ,
CNAME CHAR UNIQUE, CTIME NUMBER,
CONSTRAINT Cno_pk PRIMARY KEY); 3:创建表Teach
CREATE TABLE Teach , TSEX CHAR, CNO CHAR, TDATE DATE, TDEPT CHAR,
CONSTRAINT Tname_pk PRIMARY KEY,
CONSTRAINT Cno_fk FOREIGN KEY REFERENCES Course); 4:创建表Score CREATE TABLE Score , CNO CHAR, SCORE NUMBER,
CONSTRAINT SCno_fk FOREIGN KEY REFERENCES Course, CONSTRAINT SSno_fk FOREIGN KEY REFERENCES Student); 二、插入数据 1:Student
INSERT INTO Student VALUES;
INSERT INTO Student VALUES;
INSERT INTO Student VALUES;
INSERT INTO Student VALUES;
INSERT INTO Student VALUES;
INSERT INTO Student VALUES;
INSERT INTO Student VALUES;
INSERT INTO Student VALUES;
INSERT INTO Student VALUES; 2:Course
INSERT INTO Course VALUES; INSERT INTO Course VALUES; INSERT INTO Course VALUES; INSERT INTO Course VALUES; INSERT INTO Course VALUES; INSERT INTO Course VALUES; INSERT INTO Course VALUES;
INSERT INTO Course VALUES; 3:Teach
INSERT INTO Teach VALUES; INSERT INTO Teach VALUES; INSERT INTO Teach VALUES; INSERT INTO Teach VALUES; INSERT INTO Teach VALUES; INSERT INTO Teach VALUES; INSERT INTO Teach VALUES; INSERT INTO Teach VALUES; INSERT INTO Teach VALUES; 4:Score
INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES; INSERT INTO Score VALUES;
INSERT INTO Score VALUES; INSERT INTO Score VALUES;
例1-2: 在Student表中增加SEX 字段。 ALTER TABLE
Student ADD SEX CHAR;
例1-3: 将Student表中列名SEX修改为SSEX。 ALTER
TABLE Student RENAME COLUMN SEX TO SSEX;
例1-4: 将Student表中把Sname 字段修改为Sname
且为非空。 ALTER TABLE Student MODIFY NOT NULL);
例1-5: 为Score表按课程号升序、分数降序建立索引,
索引名为SC_GRADE。 CREATE INDEX SC_GRADE ON Score; 例1-6: 删除索引SC_GRADE。 DROP INDEX SC_GRADE;
例1-7: 建立数据库表S1,其字段类型定义与Student
表中的相应字段的数据类型定义相同。 CREATE TABLE S1 ,
SNAME CHAR NOT NULL, SD CHAR NOT NULL, SA NUMBER,
CONSTRAINT S1Sno_pk PRIMARY KEY);
例1-8: 删除成绩表Score的参照完整性约束关系。
ALTER TABLE Score DROP CONSTRAINT SCno_fk; ALTER TABLE Score DROP CONSTRAINT SSno_fk;
例1-9: 添加成绩表Score的参照完整性约束关系。
ALTER TABLE Score ADD CONSTRAINT SCno_fk FOREIGN KEY REFERENCES Course; ALTER TABLE Score ADD CONSTRAINT SSno_fk FOREIGN KEY REFERENCES Student; 例
1-10: 将数据库表S1改名为Student_Temp。 RENAME S1 TO Student_Temp;
[ 实验要求 ]
? 熟悉Oracle上机环境,掌握Oracle客户端的配置; ? 建立数据库表,修改数据库表结构,建立、删除索引; [ 实验方法 ]
? 按照附录中的操作步骤进行客户端的配置; ? 将实验需求用SQL语句表示;
? 执行SQL语句;
? 查看执行结果,如果结果不正确,进行修改,直到正确为止。
[ 实验
]
? SQL语句以及执行结果;
? 对重点实验结果进行分析;
? 实验中的问题和提高;
? 收获与体会。
[ 实验总结 ]
实验通过使用sqlplus,学习对oracle的客户端创建,在服务器上建立用户,基本表是数据库的核
心,所以创建正确的基本表是数据库的使用的前提,本次实验学习正确建立基本表并根据基本表中
的属性建立索引和参照完整性约束关系,学习插入,修改,删除基本表。
创建基本表时,首先要确定具体要创建几张基本表,再根据具体某张基本表确定该表的具体属性,
为每个属性确定数据类型,再跟数据间的关系确定表的主键和外键。基本表建立完成后,根据基本
表的数据类型插入具体的数据
再插入数据时,对于日期的插入总是不会,后来在通过同学帮助,并且我也通过查资料,最终解
决了这个问题。
实验二:数据操纵语言
[ 实验日期 ] 年 月 日
[ 实验目的 ]
在实验一的基础上,熟练掌握和使用DML语言,对数据进行增加、修改和删除操作。
[ 实验内容 ]
2( SQL数据操纵语句:
例2-1: 按前面各表中的数据分别插入到教学数据库的四个数据库表中。
见实验一中“二、插入数据”;
例2-2: 将表Student表中计算机系的学生数据插入到表S1中。
INSERT INTO Student_Temp SElECT
SNO,SNAME,SDEPT,SAGE FROM Student WHERE =„CS?;
例2-3: 求每一个学生的平均成绩,把结果存入数据库表
Student_Gr中。
CREATE TABLE Student_Gr
PRIMARY KEY,
AVGSCORE NUMBER);
INSERT INTO Student_Gr SELECT SNO,AVG FROM
SCORE GROUP BY SNO;
例2-4: 将Student_Temp表中所有学生的年龄加2。
UPDATE Student_Temp SET SA=SA+2;
例2-5: 将Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。
UPDATE Course SET CTIME= WHERE CNAME=„程
序设计?;
例2-6: 向Score表中插入数据,根据返回信息解释其原因。
INSERT INTO Score VALUES;
因为违反了完整性约束CONSTRAINT SSno_fk FOREIGN KEY REFERENCES Student Student
表中没有建立98001的学生
例2-7: 向Score表中插入数据,根据返回信息解释其原因。
INSERT INTO Score VALUES;
因为违反了完整性约束CONSTRAINT Cno_fk FOREIGN KEY REFERENCES Course Course表
中没有010的课程号
例2-8: 删除Student表中学号为‘96001’的学生信息,
根据返回信息解释其原因。
DELETE FROM Student WHERE SNO=„96001?;
因为违反了完整性约束CONSTRAINT SSno_fk FOREIGN KEY REFERENCES Student
例2-9: 删除Course表中课程号为‘003’ 的课程信
息,根据返回信息解释其原因。
DELETE FROM Course WHERE CNO=„003?;
因为违反了完整性约束CONSTRAINT Cno_fk FOREIGN KEY REFERENCES Course
例2-10: 删除学生表Student_Temp中学号以96打头的
学生信息。
DELETE FROM Student_Temp WHERE SNO LIKE „96%?;
例2-11: 删除数据库表Student_Temp中所有学生的数
据。
DELETE FROM Student_Temp;
例2-12: 删除数据库表Student_Temp和Student_Gr。
DROP TABLE Student_Temp CASCADE CONSTRAINT;
DROP TABLE Student_Gr CASCADE CONSTRAINT;
[ 实验要求 ]
对数据库表进行插入、修改和删除数据的操作。
[ 实验方法 ]
? 按照附录中的操作步骤进行客户端的配置;
? 将实验需求用SQL语句表示; ? 执行SQL语句;
? 查看执行结果,如果结果不正确,进行修改,直到正确为止。
[ 实验总结 ]
? SQL语句以及执行结果;
? 对重点实验结果进行分析;
? 实验中的问题和提高;
? 收获与体会。
[ 实验总结 ]
创建基本表时,首先要确定具体要创建几张基本表,再根据具体某张基本表确定该表的具体属性,
为每个属性确定数据类型,再跟数据间的关系确定表的主键和外键。基本表建立完成后,根据基
本表的数据类型插入具体的数据,在插入数据时,对于日期的插入总是不会,后来在通过同学帮
助,并且我也通过查资料,最终解决了这个问题。并且我学会了对基本表结构的一些基本操作,
这对我以后对Oracle的学习都有很大帮助,此次试验让我受益匪浅
实验三:数据查询语言
[ 实验日期 ] 年 月 日
[ 实验目的 ]
体会SQL语言数据查询功能的丰富和复杂。
[ 实验内容 ]
3( SQL数据查询语句:
例3-1: 求全体学生的学号、姓名、性别和年龄。
SELECT SNO,SNAME,SSEX,SAGE FROM Student;
例3-2: 求选修了课程的学生学号。
SELECT DISTINCT SNO FROM Score WHERE CNO IS
NOT NULL;
例3-3: 求全体学生的详细信息。
SELECT * FROM Student;
例3-4: 求全体学生的学号、姓名和出生年份。
SELECT SNO,SNAME,2016-SAGE AS “YEAR OF
BIRTH” FROM Student;
例3-5: 求学生的学号和出生年份,显示时使用别名“学号”和“出生年份”。
SELECT SNO AS”学号”,2016-SAGE AS “出生年份” FROM Student;
例3-6: 求年龄大于19岁的学生的姓名和年龄。
SELECT SNAME,SAGE FROM Student WHERE SAGE>19;
例3-7: 求计算机系或信息系年龄大于18岁的学生的
姓名、系和年龄。
SELECT SNAME,SDEPT,SAGE FROM Student WHERE SDEPT IN;
例3-8: 求年龄在19岁与22岁之间的学生的学号和年
龄。
SELECT SNO,SAGE FROM Student WHERE SAGE BETWEEN 19 AND 22;
例3-9: 求年龄不在19岁与22岁之间的学生的学号和
年龄。
SELECT SNO,SAGE FROM Student WHERE SAGE NOT BETWEEN 19 AND 22;
例3-10: 求在下列各系的学生信息:数学系、计算机系。
SELECT * FROM Student WHERE SDEPT IN;
例3-11: 求不是数学系、计算机系的学生信息。
SELECT * FROM Student WHERE SDEPT NOT IN;
例3-12: 求姓名是以“李”打头的学生。
SELECT SNAME FROM Student WHERE SNAME LIKE „李%? ;
例3-13: 求姓名中含有“志”的学生。
SELECT SNAME FROM Student WHERE SNAME LIKE „%志%?;
例3-14: 求姓名长度至少是三个汉字且倒数第三个汉字必须
是“马”的学生。
SELECT SNAME FROM Student WHERE SNAME LIKE „%马__?;
例3-15: 求选修课程001或003,成绩在80至90之间,
学号为96xxx的学生的学号、
课程号和成绩。
SELECT SNO,CNO,SCORE FROM Score
WHERE CNO IN AND SCORE BETWEEN 80 AND 90 AND SNO LIKE „96%? ;
例3-16: 求课程名中包含 ’_’ 字符的课程号、课程
名和学时数。
SELECT CNO,CNAME,CTIME FROM Course WHERE CNAME LIKE „%A_%? ESCAPE?A? ;
例3-17: 求缺少学习成绩的学生的学号和课程号。
SELECT SNO,CNO FROM Score WHERE SCORE IS NULL;
例3-18: 求选修003课程或004课程的学生的学号、课
程号和分数,要求按
课程号升序、分数降序的顺序显示结果。
SELECT * FROM Score
WHERE CNO IN
ORDER BY CNO ,SCORE DESC;
例3-19: 求学生总人数。
SELECT COUNT FROM Student;
例3-20: 求选修了课程的学生人数。
SELECT COUNT FROM Score;
例3-21: 求计算机系学生的平均年龄。
SELECT AVG FROM Student WHERE SDEPT=„CS?;
例3-22: 求选修了课程001的最高、最低与平均成绩。
SELECT MAX,MIN,AVG FROM Score
WHERE CNO=„001?;
例3-23: 求各门课程的平均成绩与总成绩。
SELECT AVG,SUM FROM Score
GROUP BY CNO;
例3-24: 求各系、各班级的人数和平均年龄。
SELECT COUNT,AVG FROM Student
GROUP BY SDEPT,SCLASS;
例3-25: 输入以下查询语句并执行,观察出现的其结果并分析其原因。
SELECT SNAME,SDEPT,COUNTFROM STUDENT
WHERE SDEPT=’CS’ GROUP BY SDEPT;
ERROR 位于第 1 行:
ORA-00979: 不是 GROUP BY 表达式
例3-26: 分析以下语句为什么会出现错误。并给出正确
的查询语句。
SELECT SAGE FROM STUDENT GROUP BY SNO;
ERROR 位于第 1 行:
ORA-00979: 不是 GROUP BY 表达式
例3-27: 求学生人数不足3人的系及其相应的学生数。
SELECT SDEPT,COUNT FROM Student
GROUP BY SDEPT
HAVING COUNT 例3-28: 求各系中除01班之外的
各班的学生人数。
SELECT SDEPT,SCLASS,COUNT FROM Student
WHERE SCLASS!=„01?
GROUP BY SDEPT,SCLASS;
例3-29: 分别观察各组函数、行的显示顺序以及分组查
询与空值的关系。
例3-30: 求选修了课程001且成绩在70分以下或成绩
在90分以上的学生的姓名、课程名称和成绩。
SELECT SNAME,CNAME,SCORE FROM Student,Course,Score
WHERE =„001?
AND
AND =
AND =;
例3-31: 求选修了课程的学生的学生姓名、课程号和成
绩。
SELECT SNAME,CNO,SCORE FROM Student,Score
WHERE =;
例3-32: 求年龄大于 ’李丽’ 的所有学生的姓名、系
和年龄。
SELECT ,, FROM Student X,Student Y
WHERE >
AND =„李丽;
例3-33: 求选修了课程002或003的学生的学号、课程
号、课程名和成绩,要求必须将002和003课程的相关信息显示出来。
SELECT ,,,SCORE,CTIME FROM Course,Score
WHERE IN
AND =;
例3-34: 求与 ‘黎明’ 年龄相同的学生的姓名和系。
SELECT SNAME,SDEPT FROM Student
WHERE SAGE IN ;
例3-35: 求选修了课程名为 ’数据结构’ 的学生的学
号和姓名。
SELECT SNO,SNAME FROM Student
WHERE SNO IN ) ;
例3-36: 求比数学系中某一学生年龄大的学生的姓名和
系。
SELECT SNAME,SDEPT FROM Student
WHERE SAGE>ANY
AND SDEPT?MA?;
例3-37: 求比数学系中全体学生年龄大的学生的姓名和
系。
SELECT SNAME,SDEPT FROM Student
WHERE SAGE>ALL
AND SDEPT?MA?;
例3-38: 求选修了课程004的学生的姓名和系。
SELECT SNAME FROM STUDENT
WHERE EXISTS
;
例3-39: 求与 ‘李丽’ 同系且同龄的学生的姓名和系。
SELECT SNAME,SDEPT FROM Student
WHERE SDEPT=
AND SAGE=;
例3-40: 求与 ‘‘黎明’ 同系,且年龄大于 ‘李丽’
的学生的信息。
SELECT * FROM Student
WHERE SDEPT IN
AND SAGE=;
例3-41: 求数学系中年龄相同的学生的姓名和年龄。
SELECT , FROM STUDENT X,STUDENT Y
WHERE =
AND =„MA?
AND =„MA?
AND ;
例3-42: 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。 SELECT SNAME FROM STUDENT
WHERE SNO IN
AND SSEX=„女?);
例3-43: 检索选修某课程的学生人数多于3人的教师姓名。
SELECT TNAME FROM TEACH
WHERE CNO IN );
例3-44: 列出所有教师和同学的姓名和性别。
SELECT SNAME AS “NAME”,SSEX AS “SEX” FROM
STUDENT
UNION
SELECT TNAME,TSEX FROM TEACH;
例3-45: 求未选修课程004的学生的姓名。
SELECT SNAME FROM STUDENT
WHERE NOT EXISTS
;
例3-46: 求选修了全部课程的学生的姓名。
SELECT SNAME FROM STUDENT
WHERE NOT EXISTS
);
例3-47: 求至少选修了学生 ‘96002’ 所选修的全部课程的学生的学号。
SELECT DISTINCT SNO FROM SCORE SC1
WHERE NOT EXISTS);
例3-48: 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。 SELECT SNO,CNO,SCORE FROM SCORE SC1
WHERE EXISTS
AND CNO=);
例3-49: 查询被一个以上的学生选修的课程号。
SELECT CNO FROM COURSE
WHERE EXISTS
>1
AND CNO=);
例3-50: 查询所有未选课程的学生姓名和所在系。
SELECT SNAME,SDEPT FROM STUDENT
WHERE NOT EXISTS 建立计算机系的学生的视图
STUDENT_CS。
CREATE VIEW STUDENT_CS AS SELECT * FROM
Student
WHERE SDEPT=„CS?;
例4-2: 建立由学号和平均成绩两个字段的视图
STUDENT_GR。
CREATE VIEW STUDENT_GR
AS SELECT SNO,AVG FROM Score
GROUP BY SNO;
例4-3: 利用视图STUDENT_CS,求年龄大于19岁的
学生的全部信息。
SELECT * FROM STUDENT_CS
WHERE SAGE>19;
例4-4: 利用视图STUDENT_GR,求平均成绩为88
分以上的学生的学号和平均成绩。 SELECT SNO,GAVG FROM STUDENT_GR
WHERE GAVG>88;
例4-5: 利用视图STUDENT_CS,增加学生。 INSERT INTO STUDENT_CS VALUES;
例4-6: 利用视图STUDENT_CS,将学生年龄增加1岁。观察其运行结果并分析原因。 UPDATE STUDENT_CS SET
SAGE=SAGE+1
因为年龄是按照基本表直接得来的,不需要计算,可以直接更改;
例4-7: 利用视图STUDENT_GR,将平均成绩增加2分。观察其运行结果并分析原因。 UPDATE STUDENT_GR SET
GAVG=GAVG+2
因为平均成绩是按照基本表计算得来的,不可以直接更改;
例4-8: 删除视图STUDENT_CS中学号为 ‘96006’ 的学生的全部数据。
DELETE FROM STUDENT_CS WHERE SNO=„96006?;
例4-9: 删除视图STUDENT_GR的全部数据。
DELETE FROM STUDENT_GR WHERE 1!=2;
例4-10: 删除视图STUDENT_CS和STUDENT_GR。
DROP VIEW STUDENT_CS;
DROP VIEW STUDENT_GR;
5( SQL数据控制语句:
例5-1: 给左右邻近同学授予在表Student上的SELECT权限,并使这两个用户具有给其他用户授予相同权限的权限。
GRANT SELECT ON Student TO Z109074281
WITH GRANT OPTION;
例5-2: 给邻近同学授予Teach表上的所有权限。
GRANT ALL ON Teach TO Z109074281;
例5-3: 给所有用户授予Score表上的SELECT权限。
GRANT SELECT ON Score TO PUBLIC;
例5-4: 观察左右邻近同学查询你所授权的表中的内容。
例5-5: 收回上面例子中的所有授予的权限。
REVOKE SELECT ON Student FROM Z109074281
REVOKE ALL ON Teach FROM PUBLIC
REVOKE SELECT ON Score FROM PUBLIC;
6( SQL事务处理:
例6-1: 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时,查询全部课程的总学时数后,取消所有修改。再次查询全部课程的总学时数。注意比较分析两次查询的结果。
SQL> SELECT * FROM COURSE;
例6-2: 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时,查询全部课程的总学时数后,确认所有修改。再次查询全部课程的总学时数。注
意比较分析两次查询的结果。
[ 实验要求 ]
? 建立视图,视图查询,视图更新;
? 给某一或全部用户授权和收回授权;
? 事务回退,事务提交。
[ 实验方法 ]
? 将实验需求用SQL语句表示;
? 执行SQL语句;
? 查看执行结果,如果结果不正确,进行修改,直到正确为止。
[ 实验总结 ]
? SQL语句以及执行结果;
? 对重点实验结果进行分析;
? 实验中的问题和提高;
? 收获与体会。
[ 实验总结 ]
本次实验学习对SQL的数据控制和事务处理,包括使用GTANT授予权限,REVOKE撤销权限,COMMIT事务提交,ROLLBACK事务提交。数据控制包括事务管理和并发控制,这是数据库的安全性和数据的完整性控制。授权的目的是使不同的用户对不
同的数据对象有不同的存取权限。在事务处理中,在数据库增删改时,如果对数据库进行了误操作,在为提交数居前,可利用事务回退,返回操作,这样保护了数据的一致和完整。
实验五:Oracle存储过程与触发器
[ 实验日期 ] 年 月 日
[ 实验目的 ]
通过实验进一步理解和掌握Oracle数据库的存储过程和触发器。
[ 实验内容 ]
4( 存储过程与触发器:
例7-1: 创建一个显示学生总人数的存储过程。
CREATE PROCEDURE STU_COUNT
AS
BEGIN
SELECT COUNT INTO N FROM STUDENT;
END;
/
SET SERVEROUTPUT ON;
DECLARE
SCOUNT NUMBER;
BEGIN
STU_COUNT;
dbms__line;
END;
/
例7-2: 创建显示学生信息的存储过程STUDENT_LIST,并引用STU_COUNT存储过程。 create or replace procedure
STUDENT_LIST
as
p1 %type;
p2 %type;
p3 %type;
p4 %type;
p5 %type;
p6 %type;
N number;
cursor C_S is select * from Student;
begin
OPEN C_S;
STU_COUNT;
FOR i IN 1..N LOOP
FETCH C_S INTO p1,p2,p3,p4,p5,p6;
dbms__line;
END LOOP;
END;
/
execute student_list;
例7-3: 创建一个显示学生平均成绩的存储过程。
CREATE OR REPLACE PROCEDURE STU_SAVG
AS
V_SNAME %TYPE;
V_AVG NUMBER;
c
BEGIN
SELECT AVG INTO V_AVG FROM SCORE WHERE SNO=V_SNO;
SELECT SNAME INTO V_SNAME FROM STUDENT WHERE SNO=V_SNO;
DBMS__LINE;
END;
/
EXECUTE STU_SAVG;
例7-4: 创建显示所有学生平均成绩的存储过程。
create or replace procedure SCORE_AVG
as
Savg number;
begin
select avg into Savg from SCORE;
dbms__line;
end;
/
SET SERVEROUTPUT ON;
execute SCORE_AVG;
例7-5: 在Student表中增加SAVG 字段。
ALTER TABLE STUDENT ADD SAVG NUMBER;
例7-6: 创建存储过程,计算每个学生的平均成绩保存到学生表SAVG字段中。 create or replace procedure EVERYSTU_AVG
as
v_no %type;
v_name %type;
v_dept %type;
v_class %type;
v_age %type;
v_sex %type;
v_avg %type;
m number;
cursor C_ADD is select * from Student;
begin
STU_COUNT;
OPEN C_ADD;
FOR i IN 1..m LOOP
FETCH C_ADD INTO v_no,v_name,v_dept,v_class,v_age,v_sex,v_avg;
dbms__line;
END LOOP;
END;
/
例7-7: 当更新学生成绩表SCORE中的学生成绩时,自动计算该学生的平均成绩保存到学生表SAVG字段中。
CREATE OR REPLACE TRIGGER SC_UP AFTER
UPDATE ON Score
REFERENCING NEW AS NEW OLD AS OLD FOR EACH
ROW
BEGIN
IF UPDATING THEN
SAVE_AVG;
END IF;
END;
/
例7-8: 创建包含插入、删除、修改多种触发事件的触
发器DML_LOG,对SCORE表的操作进行记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。
先创建事件
LOGS,该表用来对操作进行记录。该表的字段含义解释如下:
LOG_ID:操作记录的编号,数值型,它是该表的主键,自动增1,可由序列自动生成。
LOG_TABLE:进行操作的表名,字符型,非空,该表设计成可以由多个触发器共享使用。比如我们可以为Student表创建类似的触发器,同样将操作记录到该表。
LOG_DML:操作的动作,即INSERT、DELETE或UPDATE三种之一。
LOG_KEY_ID:操作时表的主键值,数值型。之所以记录表的主键,是因为主键是表的记录的惟一标识,可以识别是对哪一条记录进行了操作。对于Score表,主键是由SNO_CNO构成。
LOG_DATE:操作的日期,日期型,取当前的系统时间。
LOG_USER:操作者,字符型,取当时的操作者账户名。比如登录SCOTT账户进行操作,在该字段中,记录账户名为SCOTT。
CREATE TABLE LOGS PRIMARY KEY,
LOG_TABLE VARCHAR2 NOT NULL,
LOG_DML VARCHAR2,
LOG_KEY_ID NUMBER,
LOG_DATE DATE,
LOG_USER VARCHAR2);
CREATE SEQUENCE LOGS_ID_SQU INCREMENT BY 1
START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;
CREATE OR REPLACE TRIGGER DML_LOG
BEFORE DELETE OR INSERT OR UPDATE
ON SCORE
FOR EACH ROW -- 行级触发器
BEGIN
IF INSERTING THEN
INSERT INTO LOGS VALUES;
ELSIF DELETING THEN
INSERT INTO LOGS VALUES;
ELSE
INSERT INTO LOGS VALUES;
END IF;
END;
/
[ 实验要求 ]
? 创建、调试和修改、调用和执行Oracle存储过程;
? 创建、调试和修改、测试Oracle触发器。
[ 实验方法 ]
? 将实验需求用SQL语句表示;
? 执行SQL语句;
? 查看执行结果,如果结果不正确,进行修改,直到正确为止。
[ 实验总结 ]
? SQL语句以及执行结果;
? 对重点实验结果进行分析;
? 实验中的问题和提高;
? 收获与体会。
[ 实验总结 ]
本次实验学习使用存储过程和触发器。在学习此内容之前,我学习了PL/SQL语言,掌握了基本的语言用法后才能进行存储过程和触发器的编写。在一开始用存储过程输出结果时,却发现没有内容显示,后来才发现在使用dbms_output之前需要使用set serverout
on打开serveroutput开关。存储过程的执行可以用直接执行的方法,例如:execute 过程名;或者利用其他过程调用。