oracle储存过程培训资料

1、 ORACLE储存过程开发指南文档编号：001版 本：1.0二零零九年七月1.版本更新说明编号修订内容简述修订日期修订前版本号修订后版本号修订人1创建2009-7-71.01.02创建2009-7-231.01.12.目录oracle存储过程开发指南21版本说明32目录42.1说明实例中:红色oracle代表关键字.黄色代表自定义.绿色表示例解说明.2.2oracle储存过程开发须知2.2.1需要的技巧 1.SQL基础知识,包括插入、修改、删除等2.使用Oracle'sSQL*Plus工具的基本技巧3.使用Oracle'sPL/SQL编程语言的基本技巧2.2.2存储过程常用数据

2、类型varchar2(长度)可变长字符串 char(长度) 定长 number()表示整数或者浮点数number(8) number(8,2) interger 整型2.2.3存储的基本结构 1.存储过程包含三部分： 声明，执行部分，异常.2.3创建储存过程简单命令2.3.1创建储存过程 1.从Window打开SQL*Plus并且从SQL*Plus登录到你的数据库;打开skeleton.sql文件. 2.在SQL>命令提示符下输入以下命令：SQL>skeleton 注释:(SQL*Plus装载skeleton.sql文件的内容到SQL*Plus缓冲区,并且执行SQL*Plus语句;

3、SQL*Plus会通知你存储过程已经被成功地创建)3.写一个存储过程,实例:CREATE OR REPLACE PROCEDURE skeletonISBEGINDBMS_OUTPUT.PUT_LINE('HelloWorld!');END;2.3.2运行一个存储过程命令SQL>EXECUTE skeleton; 注释(SQL*Plus输出一下信息确信存储过程成功执即PL/SQLproceduresuccessfullycompleted).2.3.3查看储存打印信息1. 在SQL*Plus命令行提示符,键入:SQL>SET SERVEROUTPUT ON2. 再次

4、敲入SQL>EXECUTE skeleton即可. 注释:查看存储过程中的打印语句信息,必须实行以上命令.2.3.4删除一个存储过程 命令 : SQL>DROP PROCEDURE skeleton;2.4存储过程2.4.1存储过程基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字;2.4.2存储过程的基本语法2.4.2.0 LOOP循环 Counter :=0; LOOP counter :=c

5、ounter +1 ; EXIT WHEN counter =5; END LOOP;2.4.2.1 IF 判断 IF str1 > str2 thenresult :=1;ELSIF str2 > str1 THENresult := -1;ELSEresult := 0;END IF2.4.2.2 while循环 Counter :=0; WHILE counter < 6 LOOP counter :=counter +1; END LOOP;2.4.2.3变量定义及赋值declare realsal emp.sal%type; realname varchar(40)

6、; realjob varchar(40); Price number(5,2); Product_id interger; realjob := work ;Product_id := 100001 ;realname := 'Brunhilda'Price := 3.1415;this_day := TODAY;2.4.2.4 for使用 1,方式一: BEGIN FOR cur_result REVERSE 1.5 LOOP BEGIN DBMS_OUTPUT.PUT_LINE(cur_result );END; END LOOP; END;2.方式二:IS C

7、URSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP; END;2.4.2.5带参数的cursor 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 FETCH C_USER%NOTFOUND

8、; END LOOP; .CLOSE C_USER;注释: 与存储过程和函数相似,可以将参数传递给游标并在查询中使用.2.4.2.6存储过程操作符+-*/| 合并 如：sp_str1=“ASD”|”ERT” ,则： sp_str1=“ASDERT”2.4.2.7存储过程结构块 BEGIN 第一步处理；END;BEGIN 第二步处理；END;BEGIN 第三步处理；END;2.4.2.8存储过程异常错误控制 exception when too_many_rows then DBMS_OUTPUT.PUT_LINE('返回值多于1行'); when others then DBM

9、S_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！'); 2.4.2.9存储过程游标 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); DBMS_OUTPUT.put_line(rec.p_client); END LOOP;2.4.2.10存储过程条件表达式存储过程if条件表达式比较符 <><=>=<>

10、;!=AND OR NOT(NOT) BETWEEN AND (NOT) IN ( , , , ,)IS (NOT) NULL(NOT) LIKE2.4.2.11存储过程控制语句的跳出 exit when  eixt 注释: exit 语句可立即结束循环 exit when 语句是在指定条件下结束循环,并且可以出现在循环代码中的任何位置. 2.4.2.12存储过程返回值out Oracle 存储过程的返回值,必须在创建一个存储过程时定义.2.4.2.13存储过程的执行 Execute spdata;Execute spdata(var1);Execute spdata(va

11、r1=值1,var2=值,2);2.4.2.14隐形游标 1.dml语句是指:insert、update、delete和lock table的操作.2.对dml操作会产生隐式游标.3.隐式游标只使用sql%found,sql%notfound,sql%rowcount三个属性.4.sql%found,sql%notfound是布尔值，sql%rowcount是整数值。5. sql%found为true,sql%notfound为false, 6.在执行任何dml语句之前,sql%rowcount的值都是null7. %ROWCOUNT返回当前位置为止游标读取的记录行数.2.4.2.15 %ty

12、pe属性 1. 在pl/sql中可以将变量和常量声明为内建或用户定义的数据类型,以引用一个列名,同时继承他的数据类型和大小. 注释: delcarev_a number(5):=10;v_b v_a%type:=15;v_c v_a%type; 2.5开发一个储存过程2.5.1不带参数的储存过程注释:1. 如果没有or replace语句，则仅仅是新建一个存储过程,如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程.2. 存储过程名定义：包括存储过程名和参数列表

13、,参数名和参数类型,参数名不能重复.3. as (is )为关键字,可以理解为pl/sql的declare关键字,用于声明变量. create or replace procedure runbyparmeters begin select count(*) into icount from emp where sal>isal and job=sjob; if icount=1 then . else . end if; exception /存储过程异常 when too_many_rows then DBMS_OUTPUT.PUT_LINE('返回值多于1行');

14、when others then DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！'); end;2.5.2带参数存储过程含赋值 注释:1. sal%type目的是为了保持与传参过来的数据宽度一致.2. IN /OUT 即可作输入参数，也可作输出参数。3. 变量声明块：紧跟着的as (is )关键字,用于声明变量。4. IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN.5. OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，

15、在存储过程中该参数的值仍然是null. 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 sal>isal and job=sjob; if icount=1 then . else . end if; exception when too_many_rows then DBMS_OUTPUT.P

16、UT_LINE('返回值多于1行'); when others then DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错'); end; 2.5.3参数的存储过程默认值注释:1.可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。 2.默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值create or replace procedure procdefault(p1 varchar2, p2 varchar2 default 'mark')

17、as begin dbms_output.put_line(p2); end; SQL> exec procdefault('a'); 或者SQL> exec procdefault2(p2 =>'aa');例如二:create or replace procedure procdefault2(p1 varchar2 default 'remark', p2 varchar2 ) as begin dbms_output.put_line(p1); end;SQL> exec procdefault2('aa&#

18、39;); /注释: 这种赋值方式是错误的.SQL>exec procdefault2(sjob=>p2);2.5.4.存储过程调用方式2.5.4.1方式一:declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); begin realsal:=1100; realname:='' realjob:='CLERK' runbyparmeters(realsal,realname,realjob); 必须按顺序 DBMS_OUTPUT.PUT_LINE(REALNA

19、ME|' '|REALJOB); (输出模式) END;2.5.4.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|

20、' '|REALJOB);(输出模式)END; 2.5.5游标的使用2.5.5.1实例一:注释:1. 游标的定义只能用使关键字IS，它与AS不通用.2 loop 循环.create or replace procedure dept_procedure(a in varchar2,v_a out dept%rowtype)is-声明游标cursor c_de(a in varchar2)is select * from dept where dname=a;begin-打开游标，对其中找到的记录进行遍历open c_de(a);loopfetch c_de into v_a;e

21、xit 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;2.5.5.2实例二:1. for循环2. 注意for 循环rec 这个变量无需要在循环外进行声明，无需要为其指定数据

22、类型,它应该是一个记录类型，具体的结构是由游标决定的。CREATE OR REPLACE PROCEDURE test(v_number project.p_number%TYPE, v_name project.p_name%TYPE, v_manager project.p_manager%TYPE, v_client project.p_client%TYPE )ISCURSOR cur_test ISSELECT p_number,p_name,p_manager,p_client FROM project WHERE p_name LIKE 'S%' ; BEGIN

23、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); DBMS_OUTPUT.put_line(rec.p_client); END LOOP; COMMIT;EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.p

24、ut_line(SQLERRM); END; 2.5.5.3实例三:1. while循环create or replace procedure proccycle(p varchar2) as cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6; v_postype varchar2(20); v_description varchar2(50); begin open c_postype; if c_postype%found then dbms_output.put_l

25、ine('found true'); elsif c_postype%found = false then dbms_output.put_line('found false'); else dbms_output.put_line('found null'); end if; fetch c_postype into v_postype,v_description; while c_postype%found loop fetch c_postype into v_postype,v_description ; end loop;close c

26、_postype;END;注释:先取数据后循环,防止直接退出. while来循环处理游标是最复杂的方法2.5.6储存过程嵌套2.5.6.1实例一:create or replace procedure innerBlock(p1 varchar2) as o1 varchar2(10) := 'out1' inner1 varchar2(20); begin dbms_output.put_line(o1); begin inner1 :='inner1' dbms_output.put_line(inner1); exception when others t

27、hen null; end; end; 2.5.7储存过程建表2.5.7.1实例一:create or replace procedure skeleton as begin execute immediate 'create table table1(id number,name varchar2(20)' end ;2.5.8储存过程调试2.5.8.1实例一: declare param_out varchar2(28); param_inout varchar2(28);begin param_inout:='ff' proce_test('dd&

28、#39;,param_out,param_inout); dbms_output.put_line(param_out);end;注释: Oracle 调试主要根据用户开发的逻辑来决定,这里就不详解. 2.5.9系统异常错误信息2.5.9.1系统报错关键字ACCESS_INTO_NULL 试图给为初始化对象的属性赋值 CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置 COLLECTION_IS_NULL 试图向为初始化的嵌套表和变长数组赋值时,引发异常CURSER_ALREADY_OPEN 试图打开一个已经打开的游标时产生异常DUP_VAL_ON_INDEX

29、唯一索引对应的列上有重复的值 INVALID_CURSOR 在不合法的游标上进行操作 INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字 NO_DATA_FOUND 使用 select into 未返回行，或企图在表中访问为初始化的数据 TOO_MANY_ROWS 执行 select into 时，结果集超过一行 ZERO_DIVIDE 试图用0 除某个数字SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值 SUBSCRIPT_OUTSIDE_LIMIT 试图使用嵌套表或 VARRAY 时，将下标指定为负数 VALUE_ERROR 发

30、生算术,转换,截断或大小约束错误.LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码 NOT_LOGGED_ON PL/SQL 应用程序在试图连接数据库之前访问数据库中的数据PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典pl./SQL 系统包 ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法 STORAGE_ERROR 运行 PL/SQL 时，内存用尽或者内存出现问题SYS_INVALID_ID 无

31、效的 ROWID 字符串 TIMEOUT_ON_RESOURCE 当数据库等待某资源时超时2.6 ORACLE 和informix 存储过程区别2.6.1实例一:Oracle:create or replace procedure getstudent( in_sno in VARCHAR2(7), out_sname out VARCHAR2(20), out_sage out NUMBER(2) ) is begin select sname into out_sname sage into out_sage from student where sno = in_sno; excepti

32、on when NO_DATA_FOUND return; end getstudent;Informix:CREATE PROCEDURE spMgrp320(sp_did varchar(12),sp_pid char(14),sp_statdate date,sp_verid integer,sp_StartDay integer,sp_EndDay integer )RETURNING decimal,decimal,decimal,decimal;DEFINE sp_errsql integer; - SQL语句错误DEFINE sp_errisam integer; - ISAM错

33、误DEFINE sp_errstr char(80); - ISAM错误SET DEBUG FILE TO "spMgrp320.debug"TRACE ON;BEGIN BEGIN ON EXCEPTION SET sp_errsql, sp_errisam, sp_errstr - trap all errors IF sp_errsql != 0 THEN RAISE EXCEPTION -746, sp_errisam,"spMgrp320:t08_psncompensate表提取数据失败,错误码:"|sp_errsql|""

34、 END IF; END EXCEPTION; IF sp_Compcode IS NULL THEN RETURN 0.0,0.0,0.0,0.0; END IF; LET sp_t08money = spFNul2Zero(sp_t08money); END;END;TRACE OFF;RETURN sp_t08money;END PROCEDURE;2.6.2详细说明:2.6.2.1建立存储过程的语法 Oracle: create or replace procedure procedue_name (arg1 in | out | in out type(argn in | out |

35、 in out type,) is | as -代替DECLARE关键字 变量定义区 begin end procedure_name;Informix:create procedure proc_name( .in_parameter_list)returning out_para_list / out_result_set;2.6.2.2没有参数也没有返回值 Oracle: create or replace procedure pNoParam as begin delete from t1; commit; exception when others then begin rollba

36、ck; end; end pNoParam;Informix:create procedure pNoParam() begin on exception rollback work;return; end exception begin work; delete from t1;commit work; end; end procedure;2.6.2.3返回记录集 Oracle: procedure pReturnSet (RefCursor out TestRefCursorTyp) as localCursor TestRefCursorTyp; begin open localCur

37、sor for select f1, f2 from t1; RefCursor := localCursor; end pReturnSet; end TestRefCursorPkg; Informix create procedure pReturnSet() returning integer, varchar(10); define i integer; define j varchar(10); foreach select f1, f2 into i, j from t1 end foreach; return i, j with resume; end procedure;2.

38、6.2.4错误捕捉 Oracle: exception when others thenInformix:on exception end exception2.6.2.5对游标的处理 Oracle: create or replace procedure pHasCursor as v_f1 number(10,0); cursor curt1 is select f1 from t1 for update; begin open curt1; loop fetch curt1 into v_f1; exit when curt1%notfound; if (v_f1 = 1) then u

39、pdate t1 set f2 = 'one' where current of curt1; end if; end loop; exception when others then begin end; end pHasCursor;Informix:create procedure pHasCursor() define v_f1 integer； begin begin work; foreach select f1 into v_f1 from t1 - 注意这里没有分号 if (v_f1 = 1) then update t1 set f2 = 'one&#

40、39; where current of curt1; elif (v_f1 = 2) then update t1 set f2 = 'two' where current of curt1; else update t1 set f2 = 'others' where current of curt1; end if; end foreach;2.6.2.6打印调试信息 Oracle: DBMS_OUTPUT.PUT_LINE(开始执行存储过程); DBMS_OUTPUT.PUT_LINE('v_date='|v_date); DBMS_OU

41、TPUT.PUT_LINE(存储过程执行完毕); Informin:set debug file to "trace_check" - with append; 说明“with append”表示以追加模式打开跟踪结果文trace '开始执行存储过程' trace 'v_date='|v_date; trace 存储过程执行完毕 trace off;2.6.2.7关于参数的说明 注释: 如果存储过程想返回一个参数,在informix中是通过返回值的形式实现的,而在oracle是通过输出参数或者输入输出参数实现的.Oracle: create

42、or replace procedure p1(x out number) as begin x := 0; end p1;Informix:create procedure p1() returning integer; return 0;2.6.2.8存储过程中调用另一个存储过程 Oracle: Call pNoParam; Call pNormalParam(1, a, v_Result);Informix:spNoParam()pNormalParam(1, a) returning v_Result;2.6.2.9赋值 Oracle: v_1 := 100;Informix: let v_1 = 100;2.6.2.10 IF 判断 Oracle: Oracle:IF str1 > str2 thenresult :=1;ELSIF st