一、实验目的
(1)了解存储过程的概念、优点
(2)熟练掌握创建存储过程的方法
(3)熟练掌握存储过程的调用方法
(4)了解触发器的概念、优点
(5)掌握触发器的方法和步骤
(6)掌握触发器的使用
二、实验环境
PLSQL Developer 12
Oracle Database 11 home
三、实验步骤、出现的问题及解决方案
实验步骤:
1、建立存储过程完成图书管理系统中的借书功能,并调用该存储过程实现借书功能。
功能要求:
借书时要求输入借阅流水号,借书证号,图书编号。(即该存储过程有3个输入参数)
借书时,借书日期为系统时间。
图书的是否借出改为“是”。
调用存储过程实现借书证号“20051001”借出图书编号为“1005050”的图书。
创建储存过程:
CREATE OR REPLACE PROCEDURE 借书(借阅流水号 VARCHAR,借书证号 VARCHAR,图书编号 VARCHAR)
AS
BEGIN
INSERT INTO 借阅 VALUES(借书.借阅流水号,借书.借书证号,借书.图书编号,TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’),’’,’’,’’);
UPDATE 图书 SET 是否借出=‘是’ WHERE 图书.图书编号=借书.图书编号;
COMMIT;
END;
调用储存过程:
CALL 借书(‘7’,‘20051001’,‘1005050’);
调用前:
调用后:
2、建立存储过程完成图书管理系统中的预约功能。
预约时要求输入预约流水号,借书证号,ISBN。(即该存储过程有3个输入参数)
存储过程先检查输入的ISBN版本的图书是否都已借出,如果是则进行预约,否则提示“该书目有可借图书,请查找”。
预约时间为系统时间。
调用存储过程实现借书证号“20081237”预约ISBN为“9787508040110”的图书。
创建储存过程:
CREATE OR REPLACE PROCEDURE 预约图书(预约流水号 VARCHAR,借书证号 VARCHAR,ISBN VARCHAR)
AS
预约结果 INT;
BEGIN
SELECT COUNT(*) INTO 预约结果
FROM 图书
WHERE 图书.ISBN=预约图书.ISBN AND 图书.是否借出=‘否’;
IF 预约结果!=0
THEN
DBMS_OUTPUT.PUT_LINE(‘该书目有可借图书,请查找’);
ELSE
INSERT INTO 预约 VALUES(预约图书.预约流水号,预约图书.借书证号,预约图书.ISBN,TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’));
COMMIT;
END IF;
END;
调用储存过程:
CALL 预约图书(‘2’,‘20081237’,‘9787508040110’);
调用前:
调用后:
3、建立存储过程完成图书管理系统中的还书功能。
还书时要求输入借书证号,图书编号,罚款分类号(即该存储过程有3个输入参数)。
还书日期为系统时间。
图书的是否借出改为‘否’。
调用存储过程实现借书证号“20051001”归还图书编号为“1005050”的图书。
创建储存过程:
CREATE OR REPLACE PROCEDURE 还书(借书证号 VARCHAR,图书编号 VARCHAR,罚款分类号 VARCHAR)
AS
BEGIN
UPDATE 借阅
SET 借阅.归还日期=TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’),借阅.罚款分类号=还书.罚款分类号
WHERE 借阅.借书证号=还书.借书证号 AND 借阅.图书编号=还书.图书编号;
UPDATE 图书 SET 是否借出=‘否’ WHERE 图书.图书编号=还书.图书编号;
COMMIT;
END;
调用储存过程:
CALL 还书(‘20051001’,‘1005050’,‘1’)
调用前:
调用后:
4、通过序列和触发器实现借阅表中借阅流水号字段的自动递增。
创建序列:
CREATE SEQUENCE 借阅流水号序列 START WITH 8;–创建序列,并且从8开始
创建触发器:
CREATE OR REPLACE TRIGGER 自动递增
BEFORE INSERT ON 借阅
FOR EACH ROW
BEGIN
SELECT 借阅流水号序列.NEXTVAL INTO :NEW.借阅流水号 FROM DUAL;
END;
5、修改借书功能的存储过程。
该存储过程要求:
(1)借书时输入借书证号,图书编号。(即该函数有2个输入参数)
(2)借书时,借书日期为系统时间。
*该存储过程主体部分只有insert into语句。
修改储存过程:
CREATE OR REPLACE PROCEDURE 借书(借书证号 VARCHAR,图书编号 VARCHAR)
AS
BEGIN
INSERT INTO 借阅 VALUES(’’,借书.借书证号,借书.图书编号,TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’),’’,’’,’’);
COMMIT;
END;
6、建立与借书存储过程相对应的触发器,当借阅表中加入借阅信息时,该触发器触发,自动修改所借图书的是否借出改为‘是’。
创建触发器:
CREATE OR REPLACE TRIGGER 自动是否借出
AFTER INSERT ON 借阅
FOR EACH ROW
BEGIN
UPDATE 图书 SET 是否借出=‘是’ WHERE 图书.图书编号=:NEW.图书编号;
END;
调用修改后的借书储存过程以及激活触发器:
CALL 借书(‘20051001’,‘1005050’);
CALL 借书(‘20051001’,‘2001231’);
调用前:
调用后:
出现的问题及解决方案:
1、PLSQL创建储存过程编译出错不会给出错误提示,导致调用时提示储存过程处于无效状态。解决方案:使用SQLPLUS,不过SQLPLUS只会提示编译错误,不会提示具体原因,还可以使用Navicat工具,Navicat会给出更加详细的错误原因,仅供参考。
2、创建储存过程时,设置变量参数类型时,指定了字符长度导致创建失败。解决方案:直接设置变量数据类型,不设置其字符长度。
3、使用TO_DATE(SYSDATE,‘YYYY/MM/DD’)获取当前日期作为借阅日期导致调用借书储存过程失败,提示参数类型错误。解决方案:因为TO_DATE()函数是将字符类型转换成日期类型,而SYSDATE本来就是日期类型,所以导致调用失败,使用TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’)将SYSDATE转换成字符类型再转换成日期类型。
4、使用DBMS_OUTPUT.PUT_LINE()函数输出提示,没有反应。解决方案:在SQLPLUS中需要先使用SET SERVEROUTPUT ON;打开输出模式才能看见输出,而在PLSQL中输出的内容在另一个Output窗口中,而不是没有反应。
5、创建自动递增借阅流水号的触发器时,使用NEW关键字改变借阅流水号,导致创建触发器失败,解决方案:使用NEW关键字时,需要在前面加一个“:”号,如“:NEW.借阅流水号”。
6、调用修改后的借书储存过程时,发送错误,提示违反唯一约束条件以及COMMIT;不能再触发器中使用。解决方案:删除在触发器中的COMMIT;,然后删除序列“借阅流水号序列”,重新创建序列“借阅流水号序列”,并且设置初始值为8,因为借阅表中已经有借阅流水号1到7的数据了,然后创建序列时未指定初始值,序列默认从1开始,导致违反唯一约束条件,从而导致调用储存过程失败。
四、实验心得体会
通过本次实验,学会了储存过程以及触发器和序列的使用方法,对存储过程有了一个直观的认识,对触发器的工作原理和作用有了更加深刻的认识,使用触发器可以在修改数据前后规范数据,使数据规范化和标准化。使用储存过程将一系列的相关联的数据库操作封装成一个储存过程,使数据库操作更加简便,数据修改更加规范,数据库设计更加严谨。
数据库实验三 存储过程与触发器
最新推荐文章于 2023-01-14 14:35:41 发布