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

oracle存储过程

培训材料版权所有，2009©

CYTSSysnetElectronicsCO.,LTD寿险二部帅晓锋存储过程存储过程的建立存储过程的语法结构存储过程的控制语句存储过程的开发存储过程的运行存储过程的调试Informix与oracle存储过程的差异函数包2009/07/09存储过程存储过程的编写编写后缀名为sql的文件，一个存储过程一个文件。过程格式：CREATEORREPLACEPROCEDURE存储过程名字(

参数1INNUMBER,

参数2INNUMBER)IS变量1INTEGER:=0;变量2DATE;BEGINEND存储过程名字;2009/07/09存储过程创建一个简单存储过程的方式1、1.从Window打开SQL*Plus并且从SQL*Plus登录到你的数据库;打开skeleton.sql文件.2.在SQL>命令提示符下输入以下命令：SQL>@skeleton

注释:(SQL*Plus装载skeleton.sql文件的内容到SQL*Plus缓冲区,并且执行SQL*Plus语句;SQL*Plus会通知你存储过程已经被成功地创建)3.写一个存储过程,实例:CREATEORREPLACEPROCEDUREskeletonISBEGINDBMS_OUTPUT.PUT_LINE('HelloWorld!');END;2009/07/09存储过程运行查看信息1.SQL>EXECUTEskeleton;

注释(SQL*Plus输出一下信息确信存储过程成功执即PL/SQLproceduresuccessfullycompleted).2.在SQL*Plus命令行提示符,键入:SQL>SETSERVEROUTPUTON再次敲入SQL>EXECUTEskeleton即可.注释:查看存储过程中的打印语句信息,实行以上命令.2009/07/09存储过程删除一个存储过程1.在SQL>命令提示符下输入以下命令：命令:SQL>DROPPROCEDURE存储过程名;2009/07/09存储过程存储过程的注释--注释一行2009/07/09存储过程存储过程入参与返回值createorreplaceprocedurerunbyparmeters(isalinemp.sal%type,snameoutvarchar,sjobinoutvarchar)asicountnumber;beginificount=1then....else....endif;end;2009/07/09存储过程存储过程变量定义、常用变量类型realsalemp.sal%type;realnamevarchar2(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块内声明的变变量影响本Beginend。变量声明必须须在存储过程程开头或者Beginend块的开头部分分。2009/07/09存储过程存储过程操作作符+-*/||合并如如：sp_str1=“ASD”||””ERT”,则：sp_str1=“ASDERT””2009/07/09存储过程存储过程异常常控制exceptionwhentoo_many_rowsthenDBMS_OUTPUT.PUT_LINE('返回值多于1行');whenothersthenDBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！！');2009/07/09存储过程存储过程结构构块BEGIN第一步处理；；END;BEGIN第二步处理；；END;BEGIN第三步处理；；END;注释:存储过程BEGIN……END之间可以进行行嵌套。2009/07/09存储过程存储过程游标标1.带参数的游标标CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_IDNUMBER;OPENC_USER(变量值);LOOPFETCHC_USERINTOV_NAME;EXITWHENC_USER%NOTFOUND;ENDLOOP;CLOSEC_USER;2009/07/09存储过程2.不带参数的游游标CURSORC_USERISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPENC_USER;LOOPFETCHC_USERINTOV_NAME;EXITWHENC_USER%NOTFOUND;ENDLOOP;CLOSEC_USER;2009/07/09存储过程存储过程LOOP循环Counter:=0;LOOPcounter:=counter+1;EXITWHENcounter=5;ENDLOOP;2009/07/09存储过程存储过程for循环1.方式一:BEGINFORcur_resultincurLOOPBEGINV_SUM:=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<6LOOPcounter:=counter+1;ENDLOOP;2009/07/09存储过程存储过程if控制IFstr1>str2thenresult:=1;ELSIFstr2>str1THENresult:=-1;ELSEresult:=0;ENDIF;2009/07/09存储过程存储过程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、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/07/09存储过程存储过程控制制语句的跳出出exitwheneixt注释:exit语句可立即结结束循环exitwhen语句是在指定定条件下结束束循环,并且可以出现在循环代代码中的任何何位置.2009/07/09存储过程游标的使用1.loop循环.createorreplaceproceduredept_procedure(ainvarchar2,v_aoutdept%rowtype)is--声明游标cursorc_de(ainvarchar2)isselecttidfromdeptwheredname=a;begin--打开游标，对对其中找到的的记录进行遍遍历2009/07/09存储过程openc_de(a);Loopfetchc_deintov_a;exitwhenc_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,p_manager,p_clientFROMprojectWHEREp_nameLIKE'S%';BEGININSERTINTOprojectVALUES(v_number,v_name,v_manager,v_client);FORrecINcur_testLOOPDBMS_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存储过程游标的使用用3.while循环.cursorc_postype(ainvarchar2)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存储过程储存过程嵌嵌套createorreplaceprocedureinnerBlock(p1invarchar2)aso1varchar2(10):='out1';inner1varchar2(20);Begindbms_output.put_line(‘…………’);begininner1:='inner1';exceptionwhenothersthennull;end;end;2009/07/09存储过程储存过程中中建表createorreplaceprocedureskeletonasbeginexecuteimmediate'createtabletable1(idnumber,namevarchar2(20))';End;2009/07/09存储过程存储过程的的返回值Oracle存储过程的的返回值,必须在创建建一个存储储过程时定定义返回值值.2009/07/09存储过程存储过程的的执行Executexxx;Executexxx(值1,值2);Callxxx(值1,值2,值3);Selectxxx(值1)fromdual;注释:Selectxxx(值1)fromdual用法一般只只在函数及及包代码中中用到.2009/07/09存储过程常用存储过过程介绍Spcmpdaysum(sp_statdate,26)Spcmpmonthsum(sp_statdate)Spfnul2zero2009/07/09存储过程开发一个存存储过程1.不带参数的的储存过程程createorreplaceprocedurerunbyparmetersbeginificount=1then....else....endif;exception//存储过程异异常whentoo_many_rowsthenDBMS_OUTPUT.PUT_LINE('返回值多于于1行');whenothersthenDBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错错！');end;2009/07/09存储过程开发一个存存储过程注释:如果没有orreplace语句，则仅仅仅是新建建一个存储储过程,如果系统存存在该存储储过程，则则会报错。。Createorreplaceprocedure如果系统中中没有此存存储过程就就新建一个个，如果系系统中有此此存储过程程则把原来来删除掉，，重新创建建一个存储储过程.存储过程名名定义：包包括存储过过程名和参参数列表,参数名和参参数类型,参数名不能能重复.as(is)为关键字,可以理解为为pl/sql的declare关键字,用于声明变变量.2009/07/09存储过程开发一个存存储过程2.带参数的储储存过程createorreplaceprocedurerunbyparmeters(isalinemp.sal%type,snameoutvarchar,sjobinoutvarchar)asicountnumber;beginselectcount(*)intoicountfromempwheresal>isalandjob=sjob;ificount=1thenelseendif;exception//存储过程异异常whentoo_many_rowsthenDBMS_OUTPUT.PUT_LINE('返回值多于于1行');whenothersthenDBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错错！');end;2009/07/09存储过程开发一个存存储过程注释:sal%type目的是为了了保持与传传参过来的的数据宽度度一致.IN/OUT即可作输入入参数，也也可作输出出参数。变量声明块块：紧跟着着的as(is)关键字,用于声明变变量。IN按值传递，，并且它不不允许在存存储过程中中被重新赋赋值。如果果存储过程程的参数没没有指定存存参数传递递类型，默默认为IN.OUT参数：作为为输出参数数，需要注注意，当一一个参数被被指定为OUT类型时，就就算在调用用存储过程程之前对该该参数进行行了赋值，，在存储过过程中该参参数的值仍仍然是null.IN参数的宽度度是由外部部决定,对于OUT和INOUT参数的宽度度是由存储储过程内部部决定。2009/07/09存储过程开发一个存存储过程3.参数的存储储过程默认认值createorreplaceprocedureprocdefault(p1varchar2,p2varchar2default'mark')asbegindbms_output.put_line(p2);end;SQL>execprocdefault('a');或者SQL>execprocdefault2(p2=>'aa');注释:1.可以通过default关键字为存存储过程的的参数指定定默认值。。在对存储储过程调用用时，就可可以省略默默认值。2.默认值仅仅仅支持IN传输类型的的参数。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.方式二:declarerealsalemp.sal%type;realnamevarchar(40);realjobvarchar(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);param_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试图向为初初始化的嵌嵌套表和变变长数组赋赋值时,引发异常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元素素下下标标超超过过嵌嵌套套表表或或VARRAY的最最大大值值SUBSCRIPT_OUTSIDE_LIMIT试图图使使用用嵌嵌套套表表或或VARRAY时，，将将下下标标指指定定为为负负数数2009/07/09存储储过过程程系统统异异常常错错误误信信息息VALUE_ERROR发生生算算术术,转换换,截断断或或大大小小约约束束错错误误.LOGIN_DENIEDPL/SQL应用用程程序序连连接接到到oracle数据据库库时时，，提提供供了了不不正正确确的的用用户户名名或密密码码NOT_LOGGED_ONPL/SQL应用用程程序序在在试试图图连连接接数数据据库库之之前前访访问问数数据据库库中中的的数数据据PROGRAM_ERRORPL/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[orreplace]procedureprocedue_name[(arg1[{in|out|inout}]type(argn[{in|out|inout}]type,)]{is|as}[变量定义区]beginendprocedure_name;Informix:createprocedureproc_name([....in_parameter_list])returningout_para_list/out_result_set;2009/07/09存储过程ORACLE和informix存储过程区别别2.没有参数也没没有返回值Oracle:createorreplaceprocedurepNoParamasbegindeletefromt1;commit;end;endpNoParam;Informix:2009/07/09存储过程createprocedurepNoParam()Returningintbeginonexceptionrollbackwork;endexceptionbeginwork;deletefromt1;commitwork;return;end;endprocedure;2009/07/09存储过程ORACLE和informix存储过程区别别3.返回记录集Oracle:procedurepReturnSet(RefCursoroutvarchar2,Refdefineoutvarchar2)aslocalCursorTestRefCursorTyp;localnumberTestRefCursorTyp;beginselectf1,f2intolocalCursor,localnumberfromt1;RefCursor:=localCursor;Refdefine:=localnumber;endpReturnSet;2009/07/09存储过程Informix:createprocedurepReturnSet()returninginteger;defineiinteger;definejvarchar(10);beginforeachselectf1,f2intoi,jfromt1endforeach;returni,j;End;endprocedure;2009/07/09存储过程ORACLE和informix存储过程区别别4.错误捕捉Oracle:ExceptionwhenothersthenDBMS_OUTPUT.PUT_LINE('在XX过程中出错！！');Informix:ONEXCEPTIONSETsp_errsql,sp_errisam,sp_errstrIF(sp_errsql!=0)THENRAISEEXCEPTION-746,sp_errisam,",错误码:["||sp_errsql||"]";ENDIF;ENDEXCEPTION;2009/07/09存储过程ORACLE和informix存储过程区别别5.对游标的处理理Oracle:createorreplaceprocedurepHasCursorasv_f1number(10,0);cursorcurt1isselectf1fromt1forupdate;beginopencurt1;loopfetchcurt1Intov_f1;exitwhencurt1%notfound;endloop;Closecurt1;End;2009/07/09存储过程Informix:createprocedurepHasCursor()definev_f1integer；beginforeachselectf1intov_f1fromt1--注意这里没有有分号if(v_f1=1)thenupdatet1setf2='one'wheref3=‘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:setdebugfileto"trace_check";--withappend;说明“withappend”表示以追加模模式打开跟踪踪结果文trace'开始执行存储储过程'trace'v_date='||v_date;trace‘‘存储过程执行行完毕’traceoff;2009/07/09存储过程ORACLE和informix存储过程区别别7.关于参数的说说明注释:如果存储过程程想返回一个个参数,在informix中是通过返回回值的形式实实现的,而在oracle是通过输出参参数或者输入入输出参数实实现的.Oracle:createorreplaceprocedurep1(xoutnumber)asbeginx:=0;endp1;Informix:createprocedurep1()returninginteger;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使用Oracle:IFstr1>str2thenresult:=1;ELSIFstr2>str1THENresult:=-1;ELSEresult:=0;ENDIFInformix:2009/07/09存储过程IFstr1>str2thenresult=1;ELIFstr2>str1THENresult=-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函数名(参数1INNUMBER,参数2INNUMBER)RETURN类型IS|ASBEGINFUNCTION_BODYEND函数名;注释:函数与存储过过程相似,唯一区别函数数必须向调用用他的语句返返回一个值,它与存储过合合起来被称为为存储子程序序,从某种意义讲讲为小程序.2009/07/09函数创建一个函数数CREATEORREPLACEFUNCTIONAREA(P_radiusinnumber)returnnumberAsV_pinumber:=3.145;V_area