oracle存储过程培训材料(动画版本)

1、版权所有， 2009 CYTS Sysnet Electronics CO., LTD寿险二部帅晓锋存储过程存储过程的建立存储过程的语法结构存储过程的控制语句存储过程的开发存储过程的运行存储过程的调试Informix与oracle存储过程的差异函数包2009/07/09尚洋信德存储过程存储过程的编写编写后缀名为sql的文件，一个存储过程一个文件。过程格式：CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字;2009/0

2、7/09尚洋信德存储过程创建一个简单存储过程的方式1、1.从Window打开SQL*Plus并且从SQL*Plus登录到你的数据库;打开skeleton.sql文件. 2.在SQL命令提示符下输入以下命令： SQLskeleton 注释:(SQL*Plus装载skeleton.sql文件的内容到SQL*Plus缓冲区, 并且执行SQL*Plus语句;SQL*Plus会通知你存储过程已经被成功地创建)3.写一个存储过程,实例: CREATE OR REPLACE PROCEDURE skeleton IS BEGIN DBMS_OUTPUT.PUT_LINE(HelloWorld!); END;

3、2009/07/09尚洋信德存储过程运行查看信息1.SQLEXECUTE skeleton; 注释(SQL*Plus输出一下信息确信存储过程成功执 即PL/SQLproceduresuccessfullycompleted). 2.在SQL*Plus命令行提示符,键入: SQLSET SERVEROUTPUT ON 再次敲入SQLEXECUTE skeleton即可. 注释: 查看存储过程中的打印语句信息,实行以上命令. 2009/07/09尚洋信德存储过程删除一个存储过程1.在SQL命令提示符下输入以下命令： 命令 : SQLDROP PROCEDURE 存储过程名;2009/07/09尚洋

4、信德存储过程存储过程的注释- 注释一行2009/07/09尚洋信德存储过程存储过程入参与返回值 create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar, sjob in out varchar) as icount number; begin if icount=1 then . else . end if; end; 2009/07/09尚洋信德存储过程存储过程变量定义、常用变量类型realsal emp.sal%type; realname varchar2(40); realj

5、ob varchar2(40); Price number(5,2);Product_id interger;注释 : 同一存储过程中，变量名最好不要重复。2009/07/09尚洋信德存储过程存储过程赋值语句:=realjob := work ;Product_id := 100001 ;realname := Brunhilda;Price := 3.1415;this_day := TODAY;2009/07/09尚洋信德存储过程存储过程变量先声明且必须声明才能使用。Begin end 块外声明的变量影响全局。Begin end 块内声明的变量影响本Begin end 。变量声明必须在存储

6、过程开头或者Begin end 块的开头部分。2009/07/09尚洋信德存储过程存储过程操作符+-*/| 合并 如：sp_str1=“ASD”|”ERT” ,则： sp_str1=“ASDERT” 2009/07/09尚洋信德存储过程存储过程异常控制exception when too_many_rows then DBMS_OUTPUT.PUT_LINE(返回值多于1行); when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出错！);2009/07/09尚洋信德存储过程存储过程结构块BEGIN 第一步处理；END;BEGIN 第

7、二步处理；END;BEGIN 第三步处理；END; 注释: 存储过程BEGIN END 之间可以进行嵌套。2009/07/09尚洋信德存储过程存储过程游标1.带参数的游标CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID = C_ID NUMBER; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT WHEN C_USER%NOTFOUND; END LOOP; CLOSE C_USER;2009/07/09尚洋信德存储过程2.不带参数的游标CURSOR C_

8、USER IS SELECT NAME FROM USER WHERE TYPEID = C_ID; OPEN C_USER; LOOP FETCH C_USER INTO V_NAME; EXIT WHEN C_USER%NOTFOUND; END LOOP; CLOSE C_USER;2009/07/09尚洋信德存储过程存储过程LOOP循环 Counter :=0; LOOP counter :=counter +1 ; EXIT WHEN counter =5; END LOOP;2009/07/09尚洋信德存储过程存储过程for循环1.方式一:BEGIN FOR cur_result

9、in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 ;END; END LOOP; END;2009/07/09尚洋信德存储过程2.方式二:BEGIN FOR cur_result REVERSE 1.5 LOOP BEGIN DBMS_OUTPUT.PUT_LINE(cur_result );END; END LOOP; END;2009/07/09尚洋信德存储过程存储过程while循环Counter :=0; WHILE counter str2 thenresult :=1;ELSIF str2 str1 THENresult

10、:= -1;ELSEresult := 0;END IF;2009/07/09尚洋信德存储过程存储过程if条件表达式比较符 =!=AND OR NOT(NOT) BETWEEN AND (NOT) IN ( , , , ,)IS (NOT) NULL(NOT) LIKE2009/07/09尚洋信德存储过程 %type定义方法1. 在pl/sql中可以将变量和常量声明为内建或用户定义的数据类型,以引用一个列名,同时继承他的数据类型和大小. 注释: v_a number(5):=10; v_b v_a%type:=15; v_c v_a%type;2009/07/09尚洋信德存储过程游标属性1.o

11、rcale 在对DML操作时会产生隐式游标.2.DML是指:insert、update、delete, select 的操作.3.隐式游标只使用sql%found, sql%notfound, sql%rowcount三个属性.4.sql%found,sql%notfound是布尔值，sql%rowcount是整数值。5. sql%found为 true, sql%notfound为 false.6. sql%rowcount是返回当前位置为止游标读取的记录行数.7.在执行任何DML语句之前, sql%found,sql%notfound,sql%rowcount的 值都是null. 2009

12、/07/09尚洋信德存储过程存储过程控制语句的跳出 exit when eixt 注释: exit 语句可立即结束循环 exit when 语句是在指定条件下结束循环,并且可以 出现在循环代码中的任何位置. 2009/07/09尚洋信德存储过程游标的使用1. loop 循环.create or replace procedure dept_procedure(a in varchar2,v_a out dept%rowtype)is-声明游标cursor c_de(a in varchar2)is select tid from dept where dname=a;begin-打开游标，对其

13、中找到的记录进行遍历2009/07/09尚洋信德存储过程open c_de(a);Loopfetch c_de into v_a;exit when c_de%notfound; / (exit when语句一定要紧跟在fetch之后。必避免多余的数据处理。) dbms_output.put_line(deptno:|v_a.deptno);dbms_output.put_line(dname:|v_a.dname);dbms_output.put_line(loc:|v_a.loc);end loop;close c_de;end;2009/07/09尚洋信德存储过程游标的使用2. for

14、循环.CURSOR cur_test ISSELECT p_number,p_name,p_manager,p_client FROM project WHERE p_name LIKE S% ; BEGIN INSERT INTO project VALUES (v_number,v_name,v_manager,v_client); FOR rec IN cur_test LOOP DBMS_OUTPUT.put_line(rec.p_number); DBMS_OUTPUT.put_line(rec.p_name); DBMS_OUTPUT.put_line(rec.p_manager)

15、; DBMS_OUTPUT.put_line(rec.p_client); END LOOP; END; 2009/07/09尚洋信德存储过程游标的使用3. while 循环.cursor c_postype(a in varchar2)is select bid ,bidname from dept where dname=a;open c_postype(a);begin fetch c_postype into v_postype,v_description; while c_postype%found loop fetch c_postype into v_postype,v_desc

16、ription ; end loop;close c_postype;end;2009/07/09尚洋信德存储过程储存过程嵌套create or replace procedure innerBlock(p1 in varchar2) as o1 varchar2(10) := out1;inner1 varchar2(20); Begindbms_output.put_line(); begin inner1 :=inner1; exception when others then null; end; end; 2009/07/09尚洋信德存储过程储存过程中建表 create or rep

17、lace procedure skeleton as begin execute immediate create table table1(id number,name varchar2(20); End ;2009/07/09尚洋信德存储过程存储过程的返回值 Oracle 存储过程的返回值,必须在创建一个存储过程时定义返回值.2009/07/09尚洋信德存储过程存储过程的执行 Execute xxx; Execute xxx(值1,值2); Call xxx(值1,值2,值3); Select xxx(值1) from dual; 注释: Select xxx(值1) from dual

18、用法一般只在函数及包代码中用到. 2009/07/09尚洋信德存储过程常用存储过程介绍Spcmpdaysum(sp_statdate,26)Spcmpmonthsum(sp_statdate)Spfnul2zero2009/07/09尚洋信德存储过程开发一个存储过程1.不带参数的储存过程create or replace procedure runbyparmeters begin if icount=1 then . else . end if; exception /存储过程异常 when too_many_rows then DBMS_OUTPUT.PUT_LINE(返回值多于1行);

19、when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出错！); end;2009/07/09尚洋信德存储过程开发一个存储过程 注释:如果没有or replace语句，则仅仅是新建一个存储过程,如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程.存储过程名定义：包括存储过程名和参数列表,参数名和参数类型,参数名不能重复.as (is )为关键字,可以理解为pl/sql的declare关键字,用于声明变量.

20、2009/07/09尚洋信德存储过程开发一个存储过程2.带参数的储存过程create or replace procedure runbyparmeters(isal in emp.sal%type, sname out varchar, sjob in out varchar) as icount number; begin select count(*) into icount from emp where salisal and job=sjob; if icount=1 then else end if; exception /存储过程异常 when too_many_rows the

21、n DBMS_OUTPUT.PUT_LINE(返回值多于1行); when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出错！); end;2009/07/09尚洋信德存储过程开发一个存储过程注释:sal%type目的是为了保持与传参过来的数据宽度一致.IN /OUT 即可作输入参数，也可作输出参数。变量声明块：紧跟着的as (is )关键字,用于声明变量。IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN.OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在

22、调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null.IN参数的宽度是由外部决定,对于OUT 和IN OUT 参数的宽度是由存储过程内部决定。2009/07/09尚洋信德存储过程开发一个存储过程3.参数的存储过程默认值create or replace procedure procdefault(p1 varchar2, p2 varchar2 default mark) as begin dbms_output.put_line(p2); end; SQL exec procdefault(a); 或者SQL exec procdefault2(p2 =aa);注释: 1

23、.可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。 2. 默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值2009/07/09尚洋信德存储过程存储过程调用方式 1.方式一:Declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); BEGIN realsal:=1100; realname:=; realjob:=CLERK; runbyparmeters(realsal,realname,realjob); 必须按顺序 DBMS_OUT

24、PUT.PUT_LINE(REALNAME| |REALJOB); (输出模式)END;2009/07/09尚洋信德存储过程存储过程调用方式2. 方式二:declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); begin realsal:=1100; realname:=; realjob:=CLERK; runbyparmeters(sname=realname,isal=realsal,sjob=realjob); -指定值对应变量顺序可变 DBMS_OUTPUT.PUT_LINE(REALNAME|

25、 |REALJOB);(输出模式)END; 2009/07/09尚洋信德存储过程储存过程调试declare param_out varchar2(28); param_inout varchar2(28); begin param_inout:=ff; proce_test(dd,param_out,param_inout); dbms_output.put_line(param_out); end; 注释: 存储过程调试主要根据开发者自己的决定,一般情况下通过打印语句来完成, 这里就不详细说明.2009/07/09尚洋信德存储过程系统异常错误信息ACCESS_INTO_NULL 试图给为初始

26、化对象的属性赋值 CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置 COLLECTION_IS_NULL 试图向为初始化的嵌套表和变长数组赋值时,引发异常CURSER_ALREADY_OPEN 试图打开一个已经打开的游标时产生异常DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值 INVALID_CURSOR 在不合法的游标上进行操作 INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字 NO_DATA_FOUND 使用 select into 未返回行，或企图在表中访问为初始化的数据 TOO_MANY_ROWS 执行 selec

27、t into 时，结果集超过一行 ZERO_DIVIDE 试图用0 除某个数字SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值 SUBSCRIPT_OUTSIDE_LIMIT 试图使用嵌套表或 VARRAY 时，将下标指定为负数 2009/07/09尚洋信德存储过程系统异常错误信息VALUE_ERROR 发生算术,转换,截断或大小约束错误.LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名 或密码 NOT_LOGGED_ON PL/SQL 应用程序在试图连接数据库之前访问数据库中的数据PROGRAM_E

28、RROR PL/SQL 内部问题，可能需要重装数据字典pl./SQL 系统包 ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法 STORAGE_ERROR 运行 PL/SQL 时，内存用尽或者内存出现问题SYS_INVALID_ID 无效的 ROWID 字符串 TIMEOUT_ON_RESOURCE 当数据库等待某资源时超时 2009/07/09尚洋信德存储过程ORACLE 和和informix 存储过程区别存储过程区别1.建立存储过程的语法Oracle:create or re

29、place procedure procedue_name (arg1 in | out | in out type(argn in | out | in out type,) is | as 变量定义区 begin end procedure_name;Informix:create procedure proc_name( .in_parameter_list)returning out_para_list / out_result_set;2009/07/09尚洋信德存储过程ORACLE 和和informix 存储过程区别存储过程区别2.没有参数也没有返回值Oracle:create o

30、r replace procedure pNoParam as begin delete from t1; commit; end; end pNoParam;Informix: 2009/07/09尚洋信德存储过程create procedure pNoParam()Returning int begin on exception rollback work;end exception begin work; delete from t1;commit work; return; end; end procedure;2009/07/09尚洋信德存储过程ORACLE 和和informix 存

31、储过程区别存储过程区别3.返回记录集Oracle: procedure pReturnSet (RefCursor out varchar2, Refdefine out varchar2) as localCursor TestRefCursorTyp; localnumber TestRefCursorTyp; begin select f1, f2 into localCursor , localnumber from t1; RefCursor := localCursor; Refdefine := localnumber;end pReturnSet; 2009/07/09尚洋信德

32、存储过程Informix:create procedure pReturnSet() returning integer; define i integer; define j varchar(10); beginforeach select f1, f2 into i, j from t1 end foreach; return i, j; End ;end procedure;2009/07/09尚洋信德存储过程ORACLE 和和informix 存储过程区别存储过程区别4.错误捕捉Oracle:Exceptionwhen others thenDBMS_OUTPUT.PUT_LINE(在

33、XX过程中出错！);Informix:ON EXCEPTION SET sp_errsql, sp_errisam, sp_errstr IF (sp_errsql!=0) THEN RAISE EXCEPTION -746,sp_errisam,错误码:|sp_errsql|; END IF; END EXCEPTION;2009/07/09尚洋信德存储过程ORACLE 和和informix 存储过程区别存储过程区别5.对游标的处理Oracle:create or replace procedure pHasCursor as v_f1 number(10,0); cursor curt1

34、is select f1 from t1 for update ; begin open curt1; loop fetch curt1 Into v_f1; exit when curt1%notfound; end loop; Close curt1;End;2009/07/09尚洋信德存储过程Informix:create procedure pHasCursor() define v_f1 integer； begin foreach select f1 into v_f1 from t1 - 注意这里没有分号 if (v_f1 = 1) then update t1 set f2 =

35、 one where f3=1001; end if; end foreach;End ;End pHasCursor;2009/07/09尚洋信德存储过程ORACLE 和和informix 存储过程区别存储过程区别6.打印调试信息的处理打印调试信息的处理Oracle: DBMS_OUTPUT.PUT_LINE(开始执行存储过程开始执行存储过程); DBMS_OUTPUT.PUT_LINE(v_date=|v_date); DBMS_OUTPUT.PUT_LINE(存储过程执行完毕存储过程执行完毕); Informix:set debug file to trace_check; - with

36、 append; 说明说明“with append”表示以追加模式打开跟踪结果文表示以追加模式打开跟踪结果文trace 开始执行存储过程开始执行存储过程 trace v_date=|v_date; trace 存储过程执行完毕存储过程执行完毕 trace off;2009/07/09尚洋信德存储过程ORACLE 和和informix 存储过程区别存储过程区别7.关于参数的说明 注释: 如果存储过程想返回一个参数,在informix中是通过返回值的形式实现的,而在oracle是通过输出参数或者输入输出参数实现的.Oracle:create or replace procedure p1(x ou

37、t number) as begin x := 0; end p1;Informix:create procedure p1() returning integer; return 0;2009/07/09尚洋信德存储过程存储过程ORACLE 和和informix 存储过程区别存储过程区别8.存储过程中调用另一个存储过程Oracle: Call pNoParam; Call pNormalParam(1, a, v_Result); Informix:spNoParam()pNormalParam(1, a) returning v_Result;2009/07/09尚洋信德存储过程存储过程O

38、RACLE 和和informix 存储过程区别存储过程区别9.if 使用Oracle:IF str1 str2 thenresult :=1;ELSIF str2 str1 THENresult := -1;ELSEresult := 0;END IFInformix:2009/07/09尚洋信德存储过程 IF str1 str2 thenresult =1;ELIF str2 str1 THENresult = -1;ELSEresult = 0;END IF2009/07/09尚洋信德存储过程ORACLE 和和informix 存储过程区别存储过程区别10.赋值 Oracle: v_1 := 100; Informix: let v_1 = 100;2009/07/09尚洋信德存储过程练习创建一个存储过程, 使用游标从dept表中取BID为010开头的记录信息, 把它往表t01_psn中插入一条记录,并且循环打印插入的数据记录.2009/07/09尚洋信德函数1. 函数结构 CREATE OR REPLACE FUNCTION 函数名(参数1 IN NUMBER, 参数2 IN NUMBER )RETURN 类型 IS | ASBEGIN FUNCTION _BODYEND函数名; 注释: 函数与存储过程相似,唯一区别函