您好,欢迎访问三七文档
当前位置:首页 > IT计算机/网络 > 数据库 > oracle存储过程
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、存储过程是如何进行定义和维护的?存储过程的定义:CREATE[ORREPLACE]PROCEDUREprocedure_name[(parameter1[model]datatype1,parameter2[model]datatype2...)]IS[AS]BEGINPL/SQLBlock;END[procedure_name];其中:procedure_name是存储过程的名字,parameter用于指定参数,model用于指定参数模式,datatype用于指定参数类型,IS[AS]用于开始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='INVALID'ANDOBJECT_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、存储过程中常用的复合数据类型、CTEPL/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属性定义记录变量identifier[table_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=&xh;dbms_output.put_line(dz_record.v_xh);dbms_output.put_line(dz_record.v_dz);end;PL/SQL记录(RECORD)declaredz_recordrx_dz_nc%rowtype;beginselectxh,dz,xsbjintodz_recordfromrx_dz_ncwherexh=&xh;dbms_output.put_line(dz_record.XH);dbms_output.put_line(dz_record.DZ);end;PL/SQL表(TABLE)PL/SQL表是Oracle早期版本用于处理PL/SQL集合的数据类型,表的下标可以为负值,并且元素个数无限制,不可以作为表列的数据类型使用。TYPEtype_nameISTABLEOFelement_type[NOTNULL]INDEXBYkey_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_type[NOTNULL];identifiertype_na
本文标题:oracle存储过程
链接地址:https://www.777doc.com/doc-11518 .html