Oracle存储过程.ppt

Oracle存储过程基础培训,目录,1、我们为什么要用存储过程？2、存储过程是如何定义和维护的？3、我们如何调用存储过程？4、存储过程中常用的复合数据处理方式及CTE5、存储过程如何进行异常处理？6、存储过程如何进行事务处理？7、我们应如何优化存储过程？,1、我们为什么要用存储过程？,存储过程是指用于特定操作的PL/SQL块，是由流控制和SQL语句书写的过程。存储过程经编译和SQL优化后存储在数据库服务器中，使用时只要调用即可。在Oracle数据库中，若干个有联系的存储过程，可以组合在一起构成包。,1、我们为什么要用存储过程？,存储过程具有如下特点：1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率；2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据；,1、我们为什么要用存储过程？,4）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制；5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。,2、存储过程是如何进行定义和维护的？,存储过程的定义：CREATEORREPLACEPROCEDUREprocedure_name(parameter1modeldatatype1,parameter2modeldatatype2.)ISASBEGINPL/SQLBlock;ENDprocedure_name;其中：procedure_name是存储过程的名字，parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型，ISAS用于开始PL/SQL代码块。注：当定义存储过程的参数时，只能指定数据类型，不能指定数据长度,2、存储过程是如何进行定义和维护的？,1）建立存储过程时，既可以指定存储过程的参数，也可以不提供任何参数。2）存储过程的参数主要有三种类型：输入参数(IN)、输出参数(OUT)、输入输出参数(INOUT)，其中IN用于接收调用环境的输入参数，OUT用于将输出数据传递到调用环境，INOUT不仅要接收数据，而且要输出数据到调用环境。3）在建立存储过程时，输入参数的IN可以省略。,2、存储过程是如何进行定义和维护的？,CREATEORREPLACEPROCEDUREUSP_OutTimeISBEGINDBMS_OUTPUT.PUT_LINE(SYSDATE);ENDUSP_OutTime;,2、存储过程是如何进行定义和维护的？,CREATEORREPLACEPROCEDUREUSP_Learing(p_para1varchar2:=参数一,p_para2nvarchar2default参数二,p_para3outvarchar2,p_para4inoutvarchar2)ISBEGINDECLAREv_para5varchar2(20);BEGINv_para5:=输入输出：|p_para4;p_para3:=输出：|p_para1|p_para2;p_para4:=v_para5;END;ENDUSP_Learing;,2、存储过程是如何进行定义和维护的？,存储过程的维护：1）删除存储过程DROPPROCEDUREprocedure_name;2）编译存储过程ALTERPROCEDUREprocedure_nameCOMPILE;3）与存储过程相关的几个查询-查看无效的存储过程SELECTobject_nameFROMUSER_OBJECTSWHERESTATUS=INVALIDANDOBJECT_TYPE=PROCEDURE-查看存储过程的代码SELECTTEXTFROMUSER_SOURCEWHERENAME=procedure_name其中：procedure_name是存储过程的名字,3、如何调用存储过程,当在SQL*PLUS中调用存储过程时，需要使用CALL或EXECUTE命令，而在PL/SQL块中可以直接引用。当调用存储过程时，如果无参数，那么直接引用存储过程名；如果存储过程带有输入参数，那么需要为输入参数提供数据值；如果存储过程带有输出参数，那么需要使用变量接收输出结果；如果存储过程带有输入输出参数，那么在调用时需要使用具有输入值的变量。当为参数传递变量或者数据时，可以采用位置传递、名称传递和组合传递三种方法。,3、如何调用存储过程,调用无参存储过程EXECUSP_OutTime;调用带有输入输出参数的存储过程declarev_para1varchar2(10);v_para2nvarchar2(10);v_para3varchar2(30);v_para4varchar2(30);begin-Calltheprocedurev_para1:=123;v_para2:=456;v_para4:=789;-位置传递USP_Learing(v_para1,v_para2,v_para3,v_para4);-值传递USP_Learing(p_para1=v_para1,p_para2=v_para2,p_para3=v_para3,p_para4=v_para4);-组合传递USP_Learing(v_para1,v_para2,p_para3=v_para3,p_para4=v_para4);dbms_output.put_line(v_para3);dbms_output.put_line(v_para4);end;,4、存储过程中常用的复合数据类型、CTE,PL/SQL记录(RECORD)，单行多列PL/SQL表(TABLE)，多行多列PL/SQL嵌套表(TABLE)，多行多列变长数组(VARRY)，多行单列CommonTableExpression（CTE）,PL/SQL记录（RECORD）,PL/SQL记录(record)主要用于处理单行多列数据。当使用RECORD时，既可以自定义记录的类型和变量，也可以使用%ROWTYPE属性定义记录变量。自定义记录变量TYPEtype_nameISRECORD(field_declaration,.);identifiertype_name;使用%ROWTYPE属性定义记录变量identifiertable_name|view_name%ROWTYPE;type_name用于指定记录类型的名称；field_declaration用于定义记录成员；identifier用于指定记录变量的名称；table_name用于指定表名；view_name用于指定视图名。,PL/SQL记录（RECORD）,declaretypetype_dz_recordisrecord(v_xhrx_dz_nc.xh%type,-序号v_dzrx_dz_nc.dz%type,-地址串v_xsbjchar(1)-虚实标记);dz_recordtype_dz_record;beginselectxh,dz,xsbjintodz_recordfromrx_dz_ncwherexh=,PL/SQL记录（RECORD）,declaredz_recordrx_dz_nc%rowtype;beginselectxh,dz,xsbjintodz_recordfromrx_dz_ncwherexh=,PL/SQL表（TABLE）,PL/SQL表是Oracle早期版本用于处理PL/SQL集合的数据类型，表的下标可以为负值，并且元素个数无限制，不可以作为表列的数据类型使用。TYPEtype_nameISTABLEOFelement_typeNOTNULLINDEXBYkey_type;identifiertype_name;type_name用于指定表类型的名称；element_type用于指定表的数据类型；NOTNULL表示不允许引用NULL元素；key_type用于指定表下标的数据类型(BINARY_INTEGER、PLS_INTEGER或VARCHAR2);identifier用于定义表变量的名称。,PL/SQL表（TABLE）,declaretypedz_table_typeistableofrx_dz_nc%rowtypeindexbybinary_integer;dz_tabledz_table_type;beginselectxh,dz,xsbjbulkcollectintodz_tablefromrx_dz_nc;dbms_output.put_line(地址：|dz_table(1).dz);end;,PL/SQL表（TABLE）,从OracleDataTabse9i开始，允许使用varchar2定义表的下标。当使用varchar2定义下标时，会按照下标值的升序方式确定元素顺序。declaretypedz_table_typeistableofnvarchar2(30)indexbyvarchar2(20);dz_tabledz_table_type;begindz_table(张三):=1;dz_table(李四):=2;dz_table(王五):=3;dz_table(赵六):=4;dbms_output.put_line(第一个元素：|dz_table.first);dbms_output.put_line(王五的前一个元素：|dz_table.prior(王五);dbms_output.put_line(李四的后一个元素：|dz_table.next(李四);dbms_output.put_line(最后一个元素：|dz_table.last);end;,PL/SQL嵌套表（TABLE）,PL/SQL嵌套表用于处理PL/SQL集合的数据类型，表的下标以1开始，并且元素个数无限制，可以作为表列的数据类型使用。TYPEtype_nameISTABLEOFelement_type;identifiertype_name;type_name用于指定嵌套表类型的名称；element_type用于指定嵌套表的数据类型；identifier用于定义嵌套表变量的名称。使用嵌套表时，需要使用其构造方法初始化嵌套表变量。declaretypedz_table_typeistableofrx_dz_nc%rowtype;dz_tabledz_table_type;beginselectxh,dz,xsbjbulkcollectintodz_tablefromrx_dz_nc;dbms_output.put_line(地址：|dz_table(1).dz);end;,变长数组（VARRAY）,VARRAY用于处理PL/SQL集合的数据类型，表的下标以1开始，并且元素个数有限制，可以作为表列的数据类型使用。TYPEtype_nameISVARRAR(size_limit)OFelement_typeNOTNULL;identifiertype_name;type_name用于指定VARRAY类型的名称；size_limit用于指定VARRAY元素的最大个数；element_type用于指定元素的数据类型；identifier用于定义VARRAY变量的名称。使用VARRAY时，需要使用其构造方法初始化VARRAY元素。,变长数组（VARRAY）,declaretypedz_array_typeisvarray(20)ofrx_dz_nc.dz%type;dz_arraydz_array_type:=dz_array_type(123,12321);beginselectdzintodz_array(1)fromrx_dz_ncwherexh=,COMMONTABLEEXPRESSION（CTE）,CommonTableExpression（CTE）兼具视图（view）和派生数据表（derivedtable）的能力，可以称为临时的视图，或是在同一批子查询语法中可重复使用的派生数据表。WITHAS()SELECTFROM;举例：WITHtempDZAS(SELECTXH,DZFROMRX_DZ_NC)SELECTXH,DZFROMtempDZ;,5、存储过程中异常处理,为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；预定义异常是指由PL/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在2000020999之间。,5、存储过程中异常处理,CREATEORREPLACEPROCEDUREUSP_Exception(p_pcidinteger,-批次IDp_fmnumber,-分母p_fznumber,-分子p_resultoutnumber-结果)ISv_raiseEXCEPTION;-异常处理typetype_table_pcmxistableoft_bl_pcmx%rowtype;table_pcmxtype_table_pcmx;BEGINifp_fz=0thenRAISEv_raise;endif;p_result:=p_fm/p_fz;selectID,PCID,XMID,ZJXMID,BZ,CZSJ,CJSJbulkcollectintotable_pcmxfromt_bl_pcmxwherepcid=p_pcid;EXCEPTIONWHENv_raiseTHENRAISE_APPLICATION_ERROR(-20010,ERROR：分子为零！);WHENNO_DATA_FOUNDTHENRAISE_APPLICATION_ERROR(-20011,ERROR：批次明细不存在！);WHENOTHERSTHENRAISE_APPLICATION_ERROR(-20012,ERROR：数据错误！);END;,6、存储过程中事务处理,事务用于确保数据的一致性，由一组相关的DML语句组成，该组DML语句所执行的操作要么全部确认，要么全部取消。当执行事务操作(DML)时，Oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加行锁，以防止其他事务在相应行上执行DML操作。当执行事务提交或事务回滚时，Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁。,6、存储过程中事务处理,提交事务（COMMIT）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变。保存点（SAVEPOINT）在当前事务中，标记事务的保存点。回滚事务（ROLLBACK）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决的数据改变。回滚事务到指定的保存点（ROLLBACKTOSAVEPOINT）回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。,6、存储过程中事务处理,当执行DDL、DCL语句，或退出SQL*PLUS时，会自动提交事务；事务期间应避免与使用者互动；查询数据期间，尽量不要启动事务；尽可能让事务持续地越短越好；在事务中尽可能存取最少的数据量。,6、存储过程中事务处理,createorreplaceprocedureusp_shiwuisbeginINSERTINTOtable_testVALUES(1,2009042201,2009042201);COMMIT;SAVEPOINTsavepoint1;INSERTINTOtable_testVALUES(2,2009042201,2009042202);DBMS_TRANSACTION.savepoint(savepoint2);UPDATEtable_testSETvCode=2009042202WHEREiID=2;COMMIT;EXCEPTIONWHENDUP_VAL_ON_INDEXTHENROLLBACKTOSAVEPOINTsavepoint1;-DBMS_TRANSACTION.rollback_savepoint(savepoint1);RAISE_APPLICATION_ERROR(-20010,ERROR：违反唯一索引约