Oracle存储过程

nullnullOracle存储过程 基础 目录目录1、我们为什么要用存储过程？ 2、存储过程是如何定义和维护的？ 3、我们如何调用存储过程？ 4、存储过程中常用的复合数据处理方式及CTE 5、存储过程如何进行异常处理？ 6、存储过程如何进行事务处理？ 7、我们应如何优化存储过程？ 1、我们为什么要用存储过程？1、我们为什么要用存储过程？存储过程是指用于特定操作的PL/SQL块，是由流控制和SQL语句书写的过程。 存储过程经编译和SQL优化后存储在数据库服务器中，使用时只要调用即可。在Oracle数据库中，若干个有联系的存储过程，可以组合在一起构成包。 1、我们为什么要用存储过程？1、我们为什么要用存储过程？存储过程具有如下特点： 1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率； 2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多； 3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据； 1、我们为什么要用存储过程？1、我们为什么要用存储过程？4）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制； 5）使用存储过程，可以实现存储过程和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可； 6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。2、存储过程是如何进行定义和维护的？2、存储过程是如何进行定义和维护的？存储过程的定义： CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [model] datatype1,parameter2 [model] datatype2 ...)] IS [AS] BEGIN PL/SQL Block; END [procedure_name]; 其中： procedure_name是存储过程的名字，parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型， IS [AS]用于开始PL/SQL代码块。 注：当定义存储过程的参数时，只能指定数据类型，不能指定数据长度 2、存储过程是如何进行定义和维护的？2、存储过程是如何进行定义和维护的？1）建立存储过程时，既可以指定存储过程的参数，也可以不提供任何参数。 2）存储过程的参数主要有三种类型：输入参数(IN)、输出参数(OUT)、输入输出参数(IN OUT)，其中IN用于接收调用环境的输入参数，OUT用于将输出数据传递到调用环境，IN OUT不仅要接收数据，而且要输出数据到调用环境。 3）在建立存储过程时，输入参数的IN可以省略。 2、存储过程是如何进行定义和维护的？2、存储过程是如何进行定义和维护的？CREATE OR REPLACE PROCEDURE USP_OutTime IS BEGIN DBMS_OUTPUT.PUT_LINE(SYSDATE); END USP_OutTime; 2、存储过程是如何进行定义和维护的？2、存储过程是如何进行定义和维护的？CREATE OR REPLACE PROCEDURE USP_Learing ( p_para1 varchar2 := '参数一', p_para2 nvarchar2 default '参数二', p_para3 out varchar2, p_para4 in out varchar2 ) IS BEGIN DECLARE v_para5 varchar2(20); BEGIN v_para5 := '输入输出：'||p_para4; p_para3 := '输出：'||p_para1||p_para2; p_para4 := v_para5; END; END USP_Learing; 2、存储过程是如何进行定义和维护的？2、存储过程是如何进行定义和维护的？存储过程的维护： 1）删除存储过程 DROP PROCEDURE procedure_name; 2）编译存储过程 ALTER PROCEDURE procedure_name COMPILE; 3）与存储过程相关的几个查询 --查看无效的存储过程 SELECT object_name FROM USER_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='PROCEDURE' --查看存储过程的代码 SELECT TEXT FROM USER_SOURCE WHERE NAME= procedure_name 其中： procedure_name是存储过程的名字 3、如何调用存储过程3、如何调用存储过程当在SQL*PLUS中调用存储过程时，需要使用CALL或EXECUTE命令，而在PL/SQL块中可以直接引用。当调用存储过程时，如果无参数，那么直接引用存储过程名；如果存储过程带有输入参数，那么需要为输入参数提供数据值；如果存储过程带有输出参数，那么需要使用变量接收输出结果；如果存储过程带有输入输出参数，那么在调用时需要使用具有输入值的变量。 当为参数传递变量或者数据时，可以采用位置传递、名称传递和组合传递三种方法。 3、如何调用存储过程3、如何调用存储过程调用无参存储过程 EXEC USP_OutTime; 调用带有输入输出参数的存储过程 declare v_para1 varchar2(10); v_para2 nvarchar2(10); v_para3 varchar2(30); v_para4 varchar2(30); begin -- Call the procedure v_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、存储过程中常用的复合数据类型、CTE4、存储过程中常用的复合数据类型、CTEPL/SQL记录(RECORD)，单行多列 PL/SQL 表(TABLE)，多行多列 PL/SQL嵌套表(TABLE)，多行多列 变长数组(VARRY)，多行单列 Common Table Expression（CTE） PL/SQL记录（RECORD）PL/SQL记录（RECORD）PL/SQL记录(record)主要用于处理单行多列数据。当使用RECORD时，既可以自定义记录的类型和变量，也可以使用%ROWTYPE属性定义记录变量。 自定义记录变量 TYPE type_name IS RECORD ( field_declaration,... ); identifier type_name; 使用%ROWTYPE属性定义记录变量 identifier [table_name | view_name]%ROWTYPE; type_name用于指定记录类型的名称； field_declaration用于定义记录成员； identifier用于指定记录变量的名称； table_name用于指定表名； view_name用于指定视图名。 PL/SQL记录（RECORD）PL/SQL记录（RECORD）declare type type_dz_record is record ( v_xh rx_dz_nc.xh%type,--序号 v_dz rx_dz_nc.dz%type,--地址串 v_xsbj char(1) --虚实标记 ); dz_record type_dz_record; begin select xh,dz,xsbj into dz_record from rx_dz_nc where xh = &xh; dbms_output.put_line(dz_record.v_xh); dbms_output.put_line(dz_record.v_dz); end; PL/SQL记录（RECORD）PL/SQL记录（RECORD）declare dz_record rx_dz_nc%rowtype; begin select xh,dz,xsbj into dz_record from rx_dz_nc where xh = &xh; dbms_output.put_line(dz_record.XH); dbms_output.put_line(dz_record.DZ); end; PL/SQL表（TABLE）PL/SQL表（TABLE）PL/SQL 表是Oracle早期版本用于处理PL/SQL集合的数据类型，表的下标可以为负值，并且元素个数无限制，不可以作为表列的数据类型使用。 TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type; identifier type_name; type_name用于指定表类型的名称；element_type用于指定表的数据类型；NOT NULL表示不允许引用NULL元素；key_type用于指定表下标的数据类型(BINARY_INTEGER、PLS_INTEGER或VARCHAR2); identifier用于定义表变量的名称。 PL/SQL表（TABLE）PL/SQL表（TABLE）declare type dz_table_type is table of rx_dz_nc%rowtype index by binary_integer; dz_table dz_table_type; begin select xh,dz,xsbj bulk collect into dz_table from rx_dz_nc; dbms_output.put_line('地址：'||dz_table(1).dz); end; PL/SQL表（TABLE）PL/SQL表（TABLE）从Oracle DataTabse9i开始，允许使用varchar2定义表的下标。当使用varchar2定义下标时，会按照下标值的升序方式确定元素顺序。 declare type dz_table_type is table of nvarchar2(30) index by varchar2(20); dz_table dz_table_type; begin dz_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嵌套表（TABLE）PL/SQL 嵌套表用于处理PL/SQL集合的数据类型，表的下标以1开始，并且元素个数无限制，可以作为表列的数据类型使用。 TYPE type_name IS TABLE OF element_type; identifier type_name; type_name用于指定嵌套表类型的名称；element_type用于指定嵌套表的数据类型；identifier用于定义嵌套表变量的名称。 使用嵌套表时，需要使用其构造方法初始化嵌套表变量。 declare type dz_table_type is table of rx_dz_nc%rowtype; dz_table dz_table_type; begin select xh,dz,xsbj bulk collect into dz_table from rx_dz_nc; dbms_output.put_line('地址：'||dz_table(1).dz); end; 变长数组（VARRAY）变长数组（VARRAY）VARRAY用于处理PL/SQL集合的数据类型，表的下标以1开始，并且元素个数有限制，可以作为表列的数据类型使用。 TYPE type_name IS VARRAR(size_limit) OF element_type [NOT NULL]; identifier type_name; type_name用于指定VARRAY类型的名称；size_limit用于指定VARRAY 元素的最大个数；element_type用于指定元素的数据类型；identifier用于定义VARRAY变量的名称。 使用VARRAY时，需要使用其构造方法初始化VARRAY元素。 变长数组（VARRAY）变长数组（VARRAY）declare type dz_array_type is varray(20) of rx_dz_nc.dz%type; dz_array dz_array_type:=dz_array_type('123','12321'); begin select dz into dz_array(1) from rx_dz_nc where xh=&xh; dbms_output.put_line('地址1：'||dz_array(1)); dbms_output.put_line('地址2：'||dz_array(2)); end; declare type dz_array_type is varray(20) of rx_dz_nc.dz%type; dz_array dz_array_type; begin select dz bulk collect into dz_array from rx_dz_nc where rownum<=20; dbms_output.put_line('地址1：'||dz_array(1)); dbms_output.put_line('地址2：'||dz_array(2)); end; COMMON TABLE EXPRESSION（CTE）COMMON TABLE EXPRESSION（CTE）Common Table Expression（CTE）兼具视图（view）和派生数据表（derived table）的能力，可以称为临时的视图，或是在同一批子查询语法中可重复使用的派生数据表。 WITH AS ( ) SELECT <列名称列表> FROM ; 举例：WITH tempDZ AS ( SELECT XH,DZ FROM RX_DZ_NC ) SELECT XH,DZ FROM tempDZ; 5、存储过程中异常处理5、存储过程中异常处理为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。 异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常； 预定义异常是指由PL/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。 RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在－20000～－20999之间。 5、存储过程中异常处理5、存储过程中异常处理CREATE OR REPLACE PROCEDURE USP_Exception ( p_pcid integer, --批次ID p_fm number, --分母 p_fz number, --分子 p_result out number --结果 ) IS v_raise EXCEPTION; --异常处理 type type_table_pcmx is table of t_bl_pcmx%rowtype; table_pcmx type_table_pcmx; BEGIN if p_fz = 0 then RAISE v_raise; end if; p_result := p_fm/p_fz; select ID,PCID,XMID,ZJXMID,BZ,CZSJ,CJSJ bulk collect into table_pcmx from t_bl_pcmx where pcid = p_pcid; EXCEPTION WHEN v_raise THEN RAISE_APPLICATION_ERROR(-20010,'ERROR：分子为零！'); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20011,'ERROR：批次明细不存在！'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20012,'ERROR：数据错误！'); END; 6、存储过程中事务处理6、存储过程中事务处理事务用于确保数据的一致性，由一组相关的DML语句组成，该组DML语句所执行的操作要么全部确认，要么全部取消。 当执行事务操作(DML)时，Oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加行锁，以防止其他事务在相应行上执行DML操作。 当执行事务提交或事务回滚时，Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁。 6、存储过程中事务处理6、存储过程中事务处理提交事务（COMMIT）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变。 保存点（SAVEPOINT）在当前事务中，标记事务的保存点。 回滚事务（ROLLBACK）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决的数据改变。 回滚事务到指定的保存点（ROLLBACK TO SAVEPOINT）回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。 6、存储过程中事务处理6、存储过程中事务处理当执行DDL、DCL语句，或退出SQL*PLUS时，会自动提交事务； 事务期间应避免与使用者互动； 查询数据期间，尽量不要启动事务； 尽可能让事务持续地越短越好； 在事务中尽可能存取最少的数据量。 6、存储过程中事务处理6、存储过程中事务处理create or replace procedure usp_shiwu is begin INSERT INTO table_test VALUES(1,'2009042201','2009042201'); COMMIT; SAVEPOINT savepoint1; INSERT INTO table_test VALUES(2,'2009042201','2009042202'); DBMS_TRANSACTION.savepoint('savepoint2'); UPDATE table_test SET vCode = '2009042202' WHERE iID=2; COMMIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO SAVEPOINT savepoint1; --DBMS_TRANSACTION.rollback_savepoint(savepoint1); RAISE_APPLICATION_ERROR(-20010,'ERROR：违反唯一索引约束！'); WHEN OTHERS THEN ROLLBACK; --DBMS_TRANSACTION.rollback; end usp_shiwu; 7、存储过程的优化7、存储过程的优化SQL语句的优化 索引的优化 游标的优化 SQL语句的优化SQL语句的优化SELECT语句的执行顺序 (8)SELECT (9) [DISTINCT] (11) 传回结果列表[INTO 新数据表名称] (1) FROM 数据表 (3) [INNER | LFT | RIGHT] JOIN 数据表 (2) ON <数据表JOIN的条件> (4) [WHERE <过滤条件>] (5) [GROUP BY <群组语法>] (6) [WITH {CUBE | ROLLUP} (7) [HAVING <过滤条件>] (10) ORDER BY <排序列表> [ASC | DESC]] SQL使用时应注意的地方SQL使用时应注意的地方当使用SELECT子句查询数据时，应尽量避免使用万用字符(*)，传回所有数据行。尽量利用WHERE子句进一步限制查询结果，以确保所得的数据是有用的数据，降低传送过多数据所造成的负荷； 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接； 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作； 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小； 不要在where子句的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引； 注意存储过程中参数和数据类型的关系，并注意表之间连接的数据类型，避免不同数据类型之间的连接； 尽可能的使用索引字段作为查询条件，尤其是聚簇索引。 课程回顾课程回顾1、存储过程的特点 2、存储过程的定义、维护及调用方式 3、存储过程中的复合数据类型及CTE 4、存储过程中的异常处理 5、存储过程中的事务处理 6、存储过程中的注意事项 THE END 谢 谢THE END 谢 谢