八数据库编程PPT课件

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

2、2】 datatype2 .)IS AS 变量声明部分；变量声明部分；-可选，声明不使用可选，声明不使用DECLARE关键字关键字 BEGIN SQL 语句语句 PL/SQL 语句语句 EXCEPTION 可选可选 错误发生时的处理动作 END procedure_name; 存储过程的定义存储过程的定义3PL/SQL块块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

3、.age); end loop;end;存储过程示例存储过程示例4create 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; 存储过程示例存储过程示例1.in 1.in 可以省略可以省略; ;2.2.不能定义形参的长度不能定义形参的长度5create or replace procedure p_s(p_s# i

4、n char, p_sname out nvarchar2, p_D# out nvarchar2 ) is begin select sname,D# into p_sname,p_D# from s where s#=p_s#; end;存储过程示例存储过程示例6 在SQL*PLUS中，需要使用CALL或EXECUTE命令， exec p_select_s; call p_select_s(); 注意：注意：set set serveroutputserveroutput on on后可以显示输出结果。后可以显示输出结果。在在PL/SQL块中，可以直接引用 begin p_select_s

5、; end;存储过程的调用存储过程的调用7有参存储过程的调用begin p_insert_S (sg2,王明,男);end;或：或：call p_insert_S (sg2,王明,男);存储过程的调用存储过程的调用8有输出参数的存储过程的调用有输出参数的存储过程的调用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;存储过程的调用存储过程的调用9 示例：输入教

6、师编号，根据表profprof中D#字段的值，修改教师工资；若部门号为D1，则工资加200；若部门号为D2，则工资加300；否则工资加400。 create or replace procedure p_changesal (p_p# in char)asv_increment prof.sal%type;v_pdept prof.D#%type;10begin 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_increm

7、ent:=300; else v_increment:=400; end if; update prof set sal=sal+v_increment where p#=p_p#; commit;end;11删除存储过程删除存储过程DROP PROCEDURE procedure_name;查看存储过程的代码查看存储过程的代码SELECT TEXT FROM USER_SOURCE WHERE NAME= procedure_name其中：其中： USER_SOURCE是用户对象源代码数据字典视图是用户对象源代码数据字典视图 存储过程的维护存储过程的维护12 存储过程中的异常处理存储过程中的

8、异常处理示例:输入教师姓名，教师不存在，则输出“没有这个教师”。如果有多个，则输出每个的编号和工资。create or replace procedure p_info(p_pname in char) asv_p# prof.p#%type;v_sal prof.sal%type;begin select p#,sal into v_p#,v_sal from prof where pname=p_pname; dbms_output.put_line(v_p#|:|v_sal);13exception when NO_DATA_FOUND then dbms_output.put_line

9、(没有这个教师！没有这个教师！); when TOO_MANY_ROWS then for v_prof in(select p#,sal from prof where pname=p_pname) loop dbms_output.put_line(v_prof.p#|:|v_prof.sal); end loop;end;14COMMIT （提交事务）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变。SAVEPOINT（保存点）在当前事务中，标记事务的保存点。ROLLBACK （回滚事务）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决

10、的数据改变。ROLLBACK TO SAVEPOINT （回滚事务到指定的保存点)回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。 存储过程中的事务处理存储过程中的事务处理15存储过程中的事务处理存储过程中的事务处理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 other

11、s then rollback; raise_application_error(-20001,数据插入失败数据插入失败!);end;16create or replace 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; 存储过程中的事务处理存储过程中的事务处理17except

12、ion when dup_val_on_index then rollback to savepoint1; commit; raise_application_error(-20001,违反唯一性约束!); when others then rollback;end;18实验实验建立存储过程分别完成图书管理系统的功能：1借书2预约3 还书19借书证号借书证号姓名姓名单位单位性别性别地址地址联系电话联系电话身份证编号身份证编号20051001王菲四川绵阳西科大计算机学院女.20062001张江四川绵阳中心医院男.20061234郭敬明四川江油305男.借阅流水号借阅流水号借书证号借书证号图书编

13、号图书编号借书日期借书日期归还日期归还日期罚款分类号罚款分类号备注备注12008123730070712010/09/192010/09/2022007123510050632010/10/202011/02/20132007123520012322011/09/01图书编号图书编号ISBN是否借出是否借出备注备注20012317040195836否20012327040195836是100505097875063362否图书分类号图书分类号类名类名100文学200科技300哲学ISBNISBN书名书名作者作者出版单位出版单位单价单价图书分类号图书分类号7040195836数据库王珊高教出版社

14、39.0020097875080401红楼梦曹雪芹人民出版社2010097875063362红楼梦曹雪芹作家出版社34.3100读者书目图书分类图书借阅20实验实验 1、建立存储过程完成图书管理系统中的借书功能。 （1）借书时要求输入借阅流水号，借书证号，图书编号。（即该函数有3个输入参数） （2）借书时，借书日期为系统时间。 （3）图书的是否借出改为是create or replace procedure p_borrow_book(p_jylsh in 借阅.借阅流水号%type,p_jszh in 借阅.借书证号%type,p_tsbh in 借阅.图书编号%type)asv_sfjc 图书.是否借出%type;21实验实验begin select 是否借出 into v_sfjc from 图书 where 图书编号=p_tsbh; if v_s