第8章_2 Oracle存储过程

《第8章_2 Oracle存储过程》由会员分享，可在线阅读，更多相关《第8章_2 Oracle存储过程（27页珍藏版）》请在文档大全上搜索。

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