Oracle2014课程设计题with answer

xingmin varchar2(20); code varchar2(20); shuming varchar2(20); returnday date; borrowday date; begin

open pro_borrow_rid_cur(rid);

fetch pro_borrow_rid_cur into xingmin,borrowday,returnday,shuming,code;

dbms_output.put_line(rid||','||xingmin||','||borrowday||','||returnday||','||shuming||','||code);

close pro_borrow_rid_cur; end;

6.删除存储过程PRO_Borrow_RID。 drop procedure pro_borrow_rid;

【任务11】函数

1.编写一函数F_GetBName，该函数的功能是在图书中根据指定的书号，返回该书的书名，并在匿名块中调用函数F_GetBName找出ISBN号为“9787113124830”的图书名称。 create or replace function f_getbname (bookisbn in varchar2) return varchar2 is

bookname varchar2(20); begin

select b_name into bookname from book where isbn=bookisbn; return bookname; end; begin

dbms_output.put_line(f_getbname('9787113124830')); end;

2.删除函数F_GetBName。 Drop function f_getBname

【任务12】触发器

1.创建触发器TR_Delete，实现如果某一读者在读者表Reader中被删除，系统将自动删除该读者的借书信息和还书信息。 create or replace trigger tr_delete after delete on reader for each row begin

delete from borrow where r_id=:old.r_id;

dbms_output.put_line('已删除'||:old.r_id||'的记录'); end;

2.测试触发器TR_Delete的执行情况。 Select * from borrow;

Delete from reader where Select * from borrow;

3.创建触发器TR_Pub，禁止向publish表中插入新的记录，然后验证该触发器。 create or replace trigger tr_pub

instead of insert on v_publish for each row begin

dbms_output.put_line('禁止插入记录'); delete from publish where p_id=:new.p_id; end;

4.删除触发器TR_Pub。 Drop trigger tr_pub