西南科技大学数据库实验三(orcal19g)
实验环境
Orcal 19 plsql
实验3 存储过程和触发器
实验内容
1.建立存储过程
2.调用存储过程
3.掌握触发器的建立和应用
1、建立存储过程完成图书管理系统中的借书功能,并调用该存储过程实现借书功能。
功能要求:
借书时要求输入借阅流水号,借书证号,图书编号。(即该存储过程有3个输入参数)
借书时,借书日期为系统时间。
图书的是否借出改为‘是’
create or replace procedure PR_借书
(
v_借阅流水号 in 借阅.借阅流水号%type,
v_借书证号 in 借阅.借书证号%type,
v_图书编号 in 借阅.图书编号%type
)
as
begin
insert into 借阅 values(v_借阅流水号,v_借书证号,v_图书编号,sysdate,null,null,null);
update 图书 set 图书.是否借出='是' where 图书.图书编号=v_图书编号;
commit;
end;
---在另外sql窗口运行下面例子
————调用存储过程实现借书证号“20051001”借出图书编号为“1005050”的图书。
call PR_借书(7,20051001,1005050);
当输入借书的指令 call PR_借书(7,20051001,1005050); 后在另外的sql窗口运行下列代码查看是否出现借阅号为7的借阅记录
2、建立存储过程完成图书管理系统中的预约功能。
预约时要求输入预约流水号,借书证号,ISBN。(即该存储过程有3个输入参数)
存储过程先检查输入的ISBN版本的图书是否都已借出,如果是则进行预约,否则提示“该书目有可借图书,请查找”。
预约时间为系统时间。
在这里插入代码片
create or replace procedure PR_预约
(
v_预约流水号 in 预约.预约流水号%type,
v_借书证号 in 预约.借书证号%type,
v_ISBN in 预约.ISBN%type
)
as
v_数量 number;
begin
select count(*) into v_数量 from 图书 where 图书.ISBN=v_ISBN and 图书.是否借出='否';
if v_数量=0 then
insert into 预约 values(v_预约流水号,v_借书证号,v_ISBN,sysdate);
commit;
else
dbms_output.put_line('该书目有可借图书,请查找!');
end if;
end;
----在另外sql窗口运行下面程序
————调用存储过程实现借书证号“20081237”预约ISBN为“9787508040110”的图书
call PR_预约(2,20081237,9787508040110);
当运行完上面的 create or replace procedure PR_预约 代码后 再在另外的窗口 运行 call PR_预约(2,20081237,9787508040110); 预约代码后 再去查看预约表是否出现预约的流水号
3、建立存储过程完成图书管理系统中的还书功能。
还书时要求输入借书证号,图书编号,罚款分类号(即该存储过程有3个输入参数)。
还书日期为系统时间。
图书的是否借出改为‘否’。
create or replace procedure PR_还书
(
v_借书证号 借阅.借书证号%type,
v_图书编号 借阅.图书编号%type,
v_罚款分类号 借阅.罚款分类号%type
)
as
begin
update 借阅 set 借阅.归还日期=sysdate,借阅.罚款分类号=v_罚款分类号 where 借阅.借书证号=v_借书证号 and 借阅.图书编号=v_图书编号;
update 图书 set 图书.是否借出='否' where 图书.图书编号=v_图书编号;
commit;
end;
-----在另外sql窗口运行下面例子
call PR_还书(20051001,1005050,null);
当运行完 create or replace procedure PR_还书 代码后 再在另外的sql窗口 运行 call PR_还书(20051001,1005050,null); 代码后 再去查看 借阅表是否更新还书信息
4、通过序列和触发器实现借阅表中借阅流水号字段的自动递增。
create sequence SEQ_序列
minvalue 1
maxvalue 1.0E28
start with 8
increment by 1
cache 20;
create or replace trigger TR_借阅流水号自增 before insert on 借阅 for each row
begin
select SEQ_序列.nextval into :new.借阅流水号 from dual;
end;
-----在求改完seq的下一个数字后 在另外窗口运行下面代码,触发触发器
insert into 借阅(借书证号,图书编号,借书日期) values ('20081237','1005050',sysdate);
commit;
在运行完 create sequence SEQ_序列 后 去修改seq下一个数字
代码部分
先修改seq 参数,确定下面要增加的数字 修改为就那么运行后借阅流水编号增加到9
在窗口运行触发代码
查看借阅表,出现流水号为9的借阅记录
5、修改借书功能的存储过程。
该存储过程要求:
(1)借书时输入借书证号,图书编号。(即该函数有2个输入参数)
(2)借书时,借书日期为系统时间。
*该存储过程主体部分只有insert into语句。
create or replace procedure PR_借书
(
v_借书证号 in 借阅.借书证号%type,
v_图书编号 in 借阅.图书编号%type
)
as
v_是否借出 图书.是否借出%type;
begin
select 图书.是否借出 into v_是否借出 from 图书 where 图书.图书编号=v_图书编号;
if v_是否借出='否' then
insert into 借阅(借书证号,图书编号,借书日期) values(v_借书证号,v_图书编号,sysdate);
update 图书 set 图书.是否借出='是' where 图书.图书编号=v_图书编号;
commit;
else
dbms_output.put_line('该书已经被借走了!');
end if;
end;
-----另外窗口运行
insert into 借阅(借书证号,图书编号,借书日期) values(20071235,2001232,sysdate);
commit;
在运行完 create or replace procedure PR_借书 后 在另外窗口运行
insert into 借阅 代码 然后 在借阅表 查看是否更新
借阅证为 20071235图书编号为2001232的图书更新借书日期,时间为系统时间
图书编号为2001232的是否借出改为是
6建立与借书存储过程相对应的触发器,当借阅表中加入借阅信息时,该触发器触发,自动修改所借图书的是否借出改为‘是’
create or replace trigger TR_借书 after insert on 借阅 for each row
begin
update 图书 set 图书.是否借出='是' where 图书.图书编号=:new.图书编号;
end;
图书编号为10050的图书是否借出变为 是