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

ORACLE存储过程培训材料版权所有，2009©CYTSSysnetElectronicsCO.,LTD寿险二部帅晓锋存储过程存储过程的建立存储过程的语法结构存储过程的控制语句存储过程的开发存储过程的运行存储过程的调试Informix与oracle存储过程的差异函数包•2009/07/09•尚洋信德存储过程存储过程的编写编写后缀名为sql的文件，一个存储过程一个文件。过程格式：CREATEORREPLACEPROCEDURE存储过程名字(参数1INNUMBER,参数2INNUMBER)IS变量1INTEGER:=0;变量2DATE;BEGINEND存储过程名字;•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.SQLEXECUTEskeleton;注释(SQL*Plus输出一下信息确信存储过程成功执即PL/SQLproceduresuccessfullycompleted).2.在SQL*Plus命令行提示符,键入:SQLSETSERVEROUTPUTON再次敲入SQLEXECUTEskeleton即可.注释:查看存储过程中的打印语句信息,实行以上命令.•2009/07/09•尚洋信德存储过程删除一个存储过程1.在SQL命令提示符下输入以下命令：命令:SQLDROPPROCEDURE存储过程名;•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•尚洋信德存储过程存储过程异常控制exceptionwhentoo_many_rowsthenDBMS_OUTPUT.PUT_LINE('返回值多于1行');whenothersthenDBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');•2009/07/09•尚洋信德存储过程存储过程结构块BEGIN第一步处理；END;BEGIN第二步处理；END;BEGIN第三步处理；END;注释:存储过程BEGIN…END之间可以进行嵌套。•2009/07/09•尚洋信德存储过程存储过程游标1.带参数的游标CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_IDNUMBER;OPENC_USER(变量值);LOOPFETCHC_USERINTOV_NAME;EXITWHENC_USER%NOTFOUND;ENDLOOP;CLOSEC_USER;•2009/07/09•尚洋信德存储过程2.不带参数的游标CURSORC_USERISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPENC_USER;LOOPFETCHC_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;WHILEcounter6LOOPcounter:=counter+1;ENDLOOP;•2009/07/09•尚洋信德存储过程存储过程if控制IFstr1str2thenresult:=1;ELSIFstr2str1THENresult:=-1;ELSEresult:=0;ENDIF;•2009/07/09•尚洋信德存储过程存储过程if条件表达式比较符\\=\=\=\\!=ANDORNOT(NOT)BETWEENAND(NOT)IN(,,,,)IS(NOT)NULL(NOT)LIKE•2009/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);Loopfetchc_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_testISSELECTp_number,p_name,p_manager,p_clientFROMprojectWHEREp_nameLIKE'S%';BEGININSERTINTOprojectVALUES(v_number,v_name,v_manager,v_client);FORrecINcur_testLOOP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);ENDLOOP;END;•2009/07/09•尚洋信德存储过程游标的使用3.while循环.cursorc_postype(ainvarchar2)isselectbid,bidnamefromdeptwheredname=a;openc_postype(a);beginfetchc_postypeintov_postype,v_description;whilec_postype%foundloopfetchc_postypeintov_postype,v_description;endloop;closec_postype;end;•2009/07/09•尚洋信德存储过程储存过程嵌套createorreplaceprocedureinnerBlock(p1invarchar2)aso1varchar2(10):='out1';inner1varchar2(20);Begindbms_output.put_line(‘………’);begininner1:='inner1';exceptionwhenothersthennull;end;end;•2009/07/09•尚洋信德存储过程储存过程中建表createorreplaceprocedureskeletonasbeginexecuteimmediate'createtabletable1(idnumber,namevarchar2(20))';End;•2009/07/09•尚洋信德存储过程存储过程的返回值Oracle存储过程的返回值,必须在创建一个存储过程时定义返回值.•2009/07/09•尚洋信德存储过程存储过程的执行Executexxx;Executexxx(值1,值2);Callxxx(值1,值2,值3);Selectxxx(值1)f