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函数名名;注释:函数与与存储储过程程相似似,唯一区区别函函数必