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

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

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

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

4、令：命令: SQLDROPPROCEDURE存储过程程名;2009/07/09尚洋信德德存储过程程存储过程程的注释释-注释一行行2009/07/09尚洋信德德存储过程程存储过程程入参与与返回值值createorreplaceprocedurerunbyparmeters(isal in emp.sal%type,snameoutvarchar,sjobinoutvarchar)asicountnumber;beginificount=1then.else.endif;end;2009/07/09尚洋信德德存储过程程存储过程程变量定定义、常常用变量量类型realsalemp.sal%type;r

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

6、影响本本Beginend。变量声明明必须在在存储过过程开头头或者Beginend块的开头头部分。2009/07/09尚洋信德德存储过程程存储过程程操作符符+-*/|合并如如：sp_str1=“ASD”|”ERT”,则：sp_str1=“ASDERT”2009/07/09尚洋信德德存储过程程存储过程程异常控控制exceptionwhen too_many_rowsthenDBMS_OUTPUT.PUT_LINE(返回值多多于1行);when othersthenDBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出出错！);2009/07/09尚洋信德德存储过程程存储过

7、程程结构块块BEGIN第一步处处理；END;BEGIN第二步处处理；END;BEGIN第三步处处理；END;注释:存储过程程BEGINEND之间可以以进行嵌嵌套。2009/07/09尚洋信德德存储过程程存储过程程游标1.带参数的的游标CURSORC_USER(C_ID NUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID NUMBER;OPENC_USER(变量值);LOOPFETCHC_USERINTOV_NAME;EXIT WHENC_USER%NOTFOUND;ENDLOOP;CLOSEC_USER;2009/07/09尚洋信德德存储过程程2.不带参数数

8、的游标标CURSORC_USERISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPEN C_USER;LOOPFETCHC_USERINTOV_NAME;EXIT WHENC_USER%NOTFOUND;ENDLOOP;CLOSEC_USER;2009/07/09尚洋信德德存储过程程存储过程程LOOP循环Counter:=0;LOOPcounter :=counter+1;EXIT WHENcounter=5;ENDLOOP;2009/07/09尚洋信德德存储过程程存储过程程for循环1.方式一:BEGINFORcur_resultincurLOOPBEGINV_S

9、UM:=cur_result.列名1+cur_result.列名2 ;END;ENDLOOP;END;2009/07/09尚洋信德德存储过程程2.方式二:BEGINFORcur_resultREVERSE1.5LOOPBEGINDBMS_OUTPUT.PUT_LINE(cur_result );END;ENDLOOP;END;2009/07/09尚洋信德德存储过程程存储过程程while循环Counter:=0;WHILEcounter str2thenresult:=1;ELSIFstr2 str1THENresult:=-1;ELSEresult:=0;ENDIF;2009/07/09尚洋信

10、德德存储过程程存储过程程if条件表达达式比较符=!=ANDORNOT(NOT)BETWEENAND(NOT)IN( , ,)IS(NOT)NULL(NOT)LIKE2009/07/09尚洋信德德存储过程程%type定义方法法1.在pl/sql中可以将将变量和和常量声声明为内内建或用用户定义义的数据据类型,以引用一一个列名名,同时继承承他的数数据类型型和大小小.注释:v_anumber(5):=10;v_bv_a%type:=15;v_cv_a%type;2009/07/09尚洋信德德存储过程程游标属性性1.orcale在对DML操作时会会产生隐隐式游标标.2.DML是指:insert、upda

11、te、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/07/09尚洋信德德存储过程程存储过程程控制语语句的跳跳出exit when

12、eixt注释:exit语句可立立即结束束循环exitwhen语句是在在指定条条件下结结束循环环,并且可以以出现在循循环代码码中的任任何位置置.2009/07/09尚洋信德德存储过程程游标的使使用1.loop循环.createorreplaceproceduredept_procedure(ainvarchar2,v_aout dept%rowtype)is-声明游标标cursorc_de(ainvarchar2)isselecttidfromdept where dname=a;begin-打开游标标，对其其中找到到的记录录进行遍遍历2009/07/09尚洋信德德存储过程程open c_de(

13、a);Loopfetchc_deintov_a;exit whenc_de%notfound;/(exitwhen语句一定定要紧跟跟在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);endloop;closec_de;end;2009/07/09尚洋信德德存储过程程游标的使使用2.for循环.CURSORcur_testISSELECTp_number,p_name,

14、p_manager,p_clientFROM projectWHEREp_name LIKES%;BEGININSERTINTOproject VALUES(v_number,v_name,v_manager,v_client);FORrec IN cur_test LOOPDBMS_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);ENDLOOP;END;2009/07/09尚

15、洋信德德存储过程程游标的使使用3.while循环.cursorc_postype(a in varchar2)isselectbid,bidnamefromdeptwheredname=a;openc_postype(a);beginfetchc_postypeintov_postype,v_description;whilec_postype%foundloopfetchc_postypeintov_postype,v_description;endloop;closec_postype;end;2009/07/09尚洋信德德存储过程程储存过程程嵌套createorreplaceproced

16、ureinnerBlock(p1invarchar2)aso1varchar2(10) := out1;inner1varchar2(20);Begindbms_output.put_line();begininner1:=inner1;exceptionwhen othersthennull;end;end;2009/07/09尚洋信德德存储过程程储存过程程中建表表createorreplaceprocedureskeletonas beginexecute immediatecreate table table1(idnumber,namevarchar2(20);End;2009/07/

17、09尚洋信德德存储过程程存储过程程的返回回值Oracle存储过程程的返回回值,必须在创创建一个个存储过过程时定定义返回回值.2009/07/09尚洋信德德存储过程程存储过程程的执行行Executexxx;Executexxx(值1,值2);Callxxx(值1,值2,值3);Selectxxx(值1)from dual;注释:Selectxxx(值1)from dual用法一般般只在函函数及包包代码中中用到.2009/07/09尚洋信德德存储过程程常用存储储过程介介绍Spcmpdaysum(sp_statdate,26)Spcmpmonthsum(sp_statdate)Spfnul2zero

18、2009/07/09尚洋信德德存储过程程开发一个个存储过过程1.不带参数数的储存存过程createorreplaceprocedurerunbyparmetersbeginificount=1then.else.endif;exception/存储过程程异常when too_many_rowsthenDBMS_OUTPUT.PUT_LINE(返回值多多于1行);when othersthenDBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出出错！);end;2009/07/09尚洋信德德存储过程程开发一个个存储过过程注释:如果没有有orreplace语句，则则仅仅

19、是是新建一一个存储储过程,如果系统统存在该该存储过过程，则则会报错错。Createorreplaceprocedure如果系统统中没有有此存储储过程就就新建一一个，如如果系统统中有此此存储过过程则把把原来删删除掉，重新创创建一个个存储过过程.存储过程程名定义义：包括括存储过过程名和和参数列列表,参数名和和参数类类型,参数名不不能重复复.as(is)为关键字字,可以理解解为pl/sql的declare关键字,用于声明明变量.2009/07/09尚洋信德德存储过程程开发一个个存储过过程2.带参数的的储存过过程createorreplaceprocedurerunbyparmeters(isalin

20、emp.sal%type,snameout varchar, sjobinout varchar)asicountnumber;beginselectcount(*)intoicountfromempwheresalisaland job=sjob;ificount=1thenelseendif;exception/存储过程程异常when too_many_rowsthenDBMS_OUTPUT.PUT_LINE(返回值多多于1行);when othersthenDBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出出错！);end;2009/07/09尚洋信德德存储

21、过程程开发一个个存储过过程注释:sal%type目的是为为了保持持与传参参过来的的数据宽宽度一致致.IN/OUT即可作输输入参数数，也可可作输出出参数。变量声明明块：紧紧跟着的的as(is)关键字,用于声明明变量。IN按值传递递，并且且它不允允许在存存储过程程中被重重新赋值值。如果果存储过过程的参参数没有有指定存存参数传传递类型型，默认认为IN.OUT参数：作作为输出出参数，需要注注意，当当一个参参数被指指定为OUT类型时，就算在在调用存存储过程程之前对对该参数数进行了了赋值，在存储储过程中中该参数数的值仍仍然是null.IN参数的宽宽度是由由外部决决定,对于OUT和INOUT参数的宽宽度是由

22、由存储过过程内部部决定。2009/07/09尚洋信德德存储过程程开发一个个存储过过程3.参数的存存储过程程默认值值createorreplaceprocedureprocdefault(p1varchar2, p2 varchar2 defaultmark)asbegindbms_output.put_line(p2);end;SQL execprocdefault(a);或者SQL execprocdefault2(p2 =aa);注释:1.可以通过过default关键字为为存储过过程的参参数指定定默认值值。在对对存储过过程调用用时，就就可以省省略默认认值。2.默认值仅仅仅支持持IN传输类型

23、型的参数数。OUT和INOUT不能指定定默认值值2009/07/09尚洋信德德存储过程程存储过程程调用方方式1.方式一:Declarerealsalemp.sal%type;realnamevarchar(40);realjobvarchar(40);BEGINrealsal:=1100;realname:=;realjob:=CLERK;runbyparmeters(realsal,realname,realjob);必须须按顺序序DBMS_OUTPUT.PUT_LINE(REALNAME|REALJOB);(输出模式式)END;2009/07/09尚洋信德德存储过程程存储过程程调用方方式2

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

25、m_inoutvarchar2(28);beginparam_inout:=ff;proce_test(dd,param_out,param_inout);dbms_output.put_line(param_out);end;注释:存储过程程调试主主要根据据开发者者自己的的决定,一般情况况下通过过打印语语句来完完成,这里就不不详细说说明.2009/07/09尚洋信德德存储过程程系统异常常错误信信息ACCESS_INTO_NULL试图给为为初始化化对象的的属性赋赋值CASE_NOT_FOUNDCASE中若未包包含相应应的WHEN，并且没没有设置置COLLECTION_IS_NULL试图向为为初

26、始化化的嵌套套表和变变长数组组赋值时时,引发异常常CURSER_ALREADY_OPEN试图打开开一个已已经打开开的游标标时产生生异常DUP_VAL_ON_INDEX唯一索引引对应的的列上有有重复的的值INVALID_CURSOR在不合法法的游标标上进行行操作INVALID_NUMBER内嵌的SQL语句不能能将字符符转换为为数字NO_DATA_FOUND使用selectinto未返回行行，或企企图在表表中访问问为初始始化的数数据TOO_MANY_ROWS执行selectinto时，结果果集超过过一行ZERO_DIVIDE试图用0除某个数数字SUBSCRIPT_BEYOND_COUNT元素下标标

27、超过嵌嵌套表或或VARRAY的最大值值SUBSCRIPT_OUTSIDE_LIMIT试图使用用嵌套表表或VARRAY时，将下下标指定定为负数数2009/07/09尚洋信德德存储过程程系统异常常错误信信息VALUE_ERROR发生算术术,转换,截断或大大小约束束错误.LOGIN_DENIEDPL/SQL应用程序序连接到到oracle数据库时时，提供供了不正正确的用用户名或密码NOT_LOGGED_ONPL/SQL应用程序序在试图图连接数数据库之之前访问问数据库库中的数数据PROGRAM_ERRORPL/SQL内部问题题，可能能需要重重装数据据字典pl./SQL系统包ROWTYPE_MISMATC

28、H宿主游标标变量与与PL/SQL游标变量量的返回回类型不不兼容SELF_IS_NULL使用对象象类型时时，在null对象上调调用对象象方法STORAGE_ERROR运行PL/SQL时，内存存用尽或或者内存存出现问问题SYS_INVALID_ID无效的ROWID字符串TIMEOUT_ON_RESOURCE当数据库库等待某某资源时时超时2009/07/09尚洋信德德存储过程程ORACLE和informix存储过程程区别1.建立存储储过程的的语法Oracle:createor replace procedureprocedue_name (arg1 in |out |inout type(argn

29、in| out| in outtype,)is|as变量定义义区beginendprocedure_name;Informix:createprocedure proc_name(.in_parameter_list)returningout_para_list/ out_result_set;2009/07/09尚洋信德德存储过程程ORACLE和informix存储过程程区别2.没有参数数也没有有返回值值Oracle:createorreplaceprocedurepNoParamasbegindeletefromt1;commit;end;endpNoParam;Informix:2009

30、/07/09尚洋信德德存储过程程createprocedure pNoParam()Returningintbeginonexceptionrollbackwork;endexceptionbeginwork;deletefromt1;commitwork;return;end;endprocedure;2009/07/09尚洋信德德存储过程程ORACLE和informix存储过程程区别3.返回记录录集Oracle:procedurepReturnSet(RefCursoroutvarchar2,Refdefineoutvarchar2)as localCursorTestRefCursorT

31、yp;localnumberTestRefCursorTyp;beginselectf1, f2into localCursor ,localnumberfrom t1;RefCursor:=localCursor;Refdefine:= localnumber;endpReturnSet;2009/07/09尚洋信德德存储过程程Informix:createprocedure pReturnSet()returninginteger;defineiinteger;definejvarchar(10);beginforeachselectf1, f2 intoi, jfrom t1endfor

32、each;returni, j;End;endprocedure;2009/07/09尚洋信德德存储过程程ORACLE和informix存储过程程区别4.错误捕捉捉Oracle:Exceptionwhen othersthenDBMS_OUTPUT.PUT_LINE(在XX过程中出出错！);Informix:ONEXCEPTIONSET sp_errsql,sp_errisam,sp_errstrIF(sp_errsql!=0)THENRAISEEXCEPTION-746,sp_errisam,错误码:|sp_errsql|;ENDIF;ENDEXCEPTION;2009/07/09尚洋信德德

33、存储过程程ORACLE和informix存储过程程区别5.对游标的的处理Oracle:createorreplaceprocedurepHasCursorasv_f1number(10,0);cursorcurt1isselectf1fromt1forupdate;beginopen curt1;loopfetchcurt1Intov_f1;exit whencurt1%notfound;endloop;Closecurt1;End;2009/07/09尚洋信德德存储过程程Informix:createprocedure pHasCursor()definev_f1integer；beginf

34、oreachselectf1intov_f1from t1-注意这里里没有分分号if(v_f1= 1) thenupdatet1setf2=onewheref3=1001;endif;endforeach;End;EndpHasCursor;2009/07/09尚洋信德德存储过程程ORACLE和informix存储过程程区别6.打印调试试信息的的处理Oracle:DBMS_OUTPUT.PUT_LINE(开始执行行存储过过程);DBMS_OUTPUT.PUT_LINE(v_date=|v_date);DBMS_OUTPUT.PUT_LINE(存储过程程执行完完毕);Informix:setde

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

36、ber)asbeginx := 0;endp1;Informix:createprocedure p1()returning integer;return0;2009/07/09尚洋信德德存储过程程ORACLE和informix存储过程程区别8.存储过程程中调用用另一个个存储过过程Oracle:CallpNoParam;CallpNormalParam(1,a,v_Result);Informix:spNoParam()pNormalParam(1,a)returningv_Result;2009/07/09尚洋信德德存储过程程ORACLE和informix存储过程程区别9.if使用Oracl

37、e:IFstr1 str2thenresult:=1;ELSIFstr2 str1THENresult:=-1;ELSEresult:=0;ENDIFInformix:2009/07/09尚洋信德德存储过程程IFstr1 str2thenresult=1;ELIF str2str1 THENresult=-1;ELSEresult=0;ENDIF2009/07/09尚洋信德德存储过程程ORACLE和informix存储过程程区别10.赋值Oracle:v_1:=100;Informix:letv_1 =100;2009/07/09尚洋信德德存储过程程练习创建一个个存储过过程,使用游标标从dept表中取BID为010开头的记记录信息息,把它往表表t01_psn中插入一一条记录录,并且循环环打印插插入的数数据记录录.2009/07/09尚洋信德德函数1.函数结构构CREATEORREPLACEFUNCTION函数名(参数1 IN NUMBER,参数2 IN NUMBER)RETURN类型IS| ASBEGINFUNCTION_BODYEND函数名;注释:函数与存存储过程程相似,唯一区别别函数