第8章_2 Oracle存储过程

1、八八 数据库编程数据库编程 存储过程存储过程 预编译，优化预编译，优化 存储过程是预编译过的，并且经优化后存储于SQL内 存中，使用时无需再次编译，提高了工作效率； 代码存放于数据库代码存放于数据库 存储过程的代码直接存放于服务器数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度； 安全性安全性 执行存储过程的用户要具有一定的权限才能使用存储过程； 存储过程的优点存储过程的优点CREATE 【OR REPLACE】 PROCEDURE procedure_name(parameter1 【model】 datatype1, parameter2 【mode

2、2】 datatype2 .)IS AS 变量声明部分；变量声明部分；-存储过程中的声明不使用存储过程中的声明不使用DECLARE关键字关键字 BEGIN PL/SQL Block;END procedure_name; 存储过程的定义存储过程的定义create or replace procedure p_select_sasbegin for vr_s in (select sname,age from s) loop dbms_output.put_line(姓名姓名:|vr_s.sname| 年龄年龄:|vr_s.age); end loop;end;存储过程示例存储过程示例creat

3、e or replace procedure p_insert_s(p_s# in char, p_sname in nvarchar2, p_sex in nvarchar2 ) as begin insert into s(s#,sname,sex) values(p_s#,p_sname,p_sex); commit;end; in 可以省略不能定义形参的长度存储过程示例存储过程示例create or replace procedure p_s(p_s# in char, p_sname out nvarchar2, p_D# out nvarchar2 ) is begin selec

4、t sname,D# into p_sname,p_D# from s where s#=p_s#; end;存储过程示例存储过程示例 在SQL*PLUS中，需要使用CALL或EXECUTE命令， exec p_select_s; call p_select_s(); 注意：注意：set serveroutput onset serveroutput on后可以显示输出结果。后可以显示输出结果。在在PL/SQL块中，可以直接引用 begin p_select_s; end;存储过程的调用存储过程的调用有参存储过程的调用begin p_insert_S (sg2,王明,男);end;或：或：ca

5、ll p_insert_S (sg2,王明,男);存储过程的调用存储过程的调用有输出参数的存储过程的调用有输出参数的存储过程的调用Declare v_sname s.sname%TYPE; v_D# s.D#%TYPE;Begin p_s(S3,v_sname, v_D#); dbms_output.put_line(姓名:|v_sname); dbms_output.put_line(部门:|v_D#);end;存储过程的调用存储过程的调用 示例：输入教师编号，根据表profprof中D#字段的值，修改教师工资；若部门号为D1，则工资加100；若部门号为D2，则工资加300；否则工资加400

6、。 create or replace procedure p_changesal(p_p# in char)asv_increment prof.sal%type;v_pdept prof.D#%type;begin select D# into v_pdept from prof where p#=p_p#; if v_pdept= D1 then v_increment:=200; elsif v_pdept= D2 then v_increment:=300; else v_increment:=400; end if; update prof set sal=sal+v_increm

7、ent where p#=p_p#; commit;end;删除存储过程删除存储过程DROP PROCEDURE procedure_name;查看存储过程的代码查看存储过程的代码SELECT TEXT FROM USER_SOURCE WHERE NAME= procedure_name其中：其中： procedure_name是存储过程的名字是存储过程的名字 存储过程的维护存储过程的维护 存储过程中的异常处理存储过程中的异常处理示例:输入教师姓名，教师不存在，则输出There is not such an employee。如果有多个，则输出每个的编号和工资。create or repla

8、ce procedure p_info(p_pname in char) asv_sal prof.sal%type;begin select sal into v_sal from prof where pname=p_pname; dbms_output.put_line(p_pname|:|v_sal);exception when NO_DATA_FOUND then dbms_output.put_line(There is not such an employee!); when TOO_MANY_ROWS then for v_prof in(select p#,sal from

9、 prof where pname=p_pname) loop dbms_output.put_line(v_prof.p#|:|v_prof.sal); end loop;end;COMMIT （提交事务）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变。SAVEPOINT（保存点）在当前事务中，标记事务的保存点。ROLLBACK （回滚事务）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决的数据改变。ROLLBACK TO SAVEPOINT （回滚事务到指定的保存点)回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。 存

10、储过程中的事务处理存储过程中的事务处理存储过程中的事务处理存储过程中的事务处理create or replace procedure p_insert_s(p_s# in char, p_sname in varchar2, p_sex in char ) as begin insert into s(s#,sname,sex) values(p_s#,p_sname,p_sex); commit; exception when others then rollback; raise_application_error(-20001,数据插入失败数据插入失败!);end;create or r

11、eplace procedure p_transasbegin insert into c values(666,英语1,null); SAVEPOINT savepoint1; insert into c values(667,化学1,null); SAVEPOINT savepoint2; insert into c values(667,物理1,null); commit; 存储过程中的事务处理存储过程中的事务处理exception when dup_val_on_index then rollback to savepoint1; commit; raise_application_e

12、rror(-20001,违反唯一性约束!); when others then rollback;end;CREATE 【OR REPLACE】 FUNCTION function_name(parameter1 【model】 datatype1,parameter2 【mode2】 datatype2 .) RETURN return_datatypeIS ASBEGIN PL/SQL Block;END function_name; 函数函数示例：建立一个存储函数，统计指定部门的人数。CREATE OR REPLACE FUNCTION COUNTNUM (p_D# CHAR) RETU

13、RN NUMBER ISV_SUM NUMBER;BEGINSELECT COUNT(*) INTO V_SUM FROM prof WHERE D#=p_D#;RETURN V_SUM;END;函数的调用函数的调用 在SQL*PLUS中，需要使用CALL或EXECUTE命令， exec dbms_output.put_line(countnum(D1); call dbms_output.put_line(countnum(D1); 注意：注意：set serveroutput onset serveroutput on后可以显示输出结果。后可以显示输出结果。 在在PL/SQL块中，可以直接

14、引用 begin dbms_output.put_line(countnum(D1); end; 练习练习建立存储过程分别完成图书管理系统的功能：1借书2预约3 还书练习练习 1、建立存储过程完成图书管理系统中的借书功能。 （1）借书时要求输入借阅流水号，借书证号，图书编号。（即该函数有3个输入参数） （2）借书时，借书日期为系统时间。 （3）图书的是否借出改为是create or replace procedure p_borrow_book(v_jylsh in number,v_jszh in number,v_tsbh in number)asv_sfjc 图书.是否借出%type;b

15、egin select 是否借出 into v_sfjc from 图书 where 图书编号=v_tsbh; if v_sfjc=否 then insert into 借阅(借阅流水号,借书证号,图书编号,借书日期) values(v_jylsh,v_jszh,v_tsbh,to_date(to_char(sysdate,YYYY/MM/DD),YYYY/MM/DD); update 图书 set 是否借出=是 where 图书编号=v_tsbh; else dbms_output.put_line(该图书已经借出！); end if;end;练习练习 1、建立存储过程完成图书管理系统中的借

16、书功能。 （1）借书时要求输入借阅流水号，借书证号，图书编号。（即该函数有3个输入参数） （2）借书时，借书日期为系统时间。 （3）图书的是否借出改为是create or replace procedure p_borrow_book(v_jylsh in number,v_jszh in number,v_tsbh in number)asv_sfjc 图书.是否借出%type;begin select 是否借出 into v_sfjc from 图书 where 图书编号=v_tsbh; if v_sfjc=否 then insert into 借阅(借阅流水号,借书证号,图书编号,借书日

17、期) values(v_jylsh,v_jszh,v_tsbh,to_date(to_char(sysdate,YYYY/MM/DD),YYYY/MM/DD); update 图书 set 是否借出=是 where 图书编号=v_tsbh; else dbms_output.put_line(该图书已经借出！); end if;end;练习练习 2、建立存储过程完成图书管理系统中的预约功能。、建立存储过程完成图书管理系统中的预约功能。 （1）预约时要求输入预约流水号，借书证号，预约流水号，借书证号，ISBN。（即该函数有3个输入参数） (2)存储过程先检查输入的ISBN版本的图书是否都已借出，

18、如果是则进行预约，否则提示“该书目有可借图书，请查找”。 （3）预约时间为系统时间。create or replace procedure p_order_book(v_yylsh in number,v_jszh in number,v_ISBN in number)Asv_sfjc 图书.是否借出%type;Begin select 是否借出 into v_sfjc from 图书 where 图书编号= v_ISBN;if v_sfjc=是 then insert into 预约(预约流水号，借书证号，ISBN，预约时间) values(v_yylsh , v_jszh v_ISBN ,

19、to_date(to_char(sysdate,YYYY/MM/DD),YYYY/MM/DD);else dbms_output.put_line(该书目有可借图书，请查找！); end if;end;练习练习3、建立存储过程完成图书管理系统中的还书还书功能。（1）还书时要求输入借书证号，图书编号，罚款分类号借书证号，图书编号，罚款分类号。（即该函数有3个输入参数）（3）还书日期为系统日期 （3）图书的是否借出改为否create or replace procedure p_return_book(v_jszh in number,v_tsbh in number,v_fkflh in number)Asv_sfjc 图书.是否借出%type;Beginselect 是否借出 into v_sfjc from 图书 where 图书编号= v_tsbh ;if v_sfjc=是 thenupdate 借阅 set 归还日期= to_date(to_char(sysdate,YYYY/MM/DD),YYYY/MM/DD) where 借书证号 = v_jszh and 图书编号= v_tsbh ;update 借阅 set 罚款分类号= v_fkflh whe