oracle存储过程介绍解读课件

1、Oracle存储过程介绍*第1页，共53页。存储过程介绍存储过程概念介绍PL/SQL基础游标存储过程的用户接口JAVA调用数据库存储过程存储过程的异常处理存储过程的包第2页，共53页。存储过程的概念（1）过程就是高级程序设计语言中的模块的概念,将一些内部联系的命令组成一个个过程,通过参数在过程之间传递数据是模块化设计思想的重要内容.存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，

2、可以被有权用户在任何需要的地方调用。在 oracle数据库中，存储过程是Oracle的一种第3页，共53页。存储过程的概念（2）对象，是一种带名的PL/SQL过程程序块。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。第4页，共53页。存储过程优点（1）1）提高效率：存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率；2）减少网络流量：存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；3）安全性高：使用

3、存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据;第5页，共53页。存储过程优点（2）4）重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。5）灵活：使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可。第6页，共53页。存储过程缺点（1）1）移植性差：使用存储过程封装业务逻辑将限制应用程序的可移植性；2）维护成本高：如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。第7页，共53

4、页。pl/sql基础第8页，共53页。PL/SQL块的基本结构（1）基本的PL/SQL块由定义部分,执行部分,异常处理部分组成: DECLARE 定义部分 BEGIN 执行部分 EXCEPTION 异常处理部分 END第9页，共53页。PL/SQL块的基本结构（2）定义部分: 定义在程序执行部分使用的常量,变量,游标和异常处理名称可执行部分 包括数据库操作语句和PL/SQL块控制语句异常处理部分 对执行部分的所有PL/SQL语句的执行进行监控,如执行发生异常,则程序跳到该部分执行第10页，共53页。定义变量（1） 在PLSQL中所使用的变量必须在变量定义部分明确定义.变量定义部分是包括在关键字

5、DECLARE和BEGIN之间的部分,每条语句后用(;)结束.定义格式: 变量标示符 CONSTANT 数据类型 NOT NULL :=缺省值或PLSQL表达式; 变量标示符命名规则应遵循SQL实体命名规则第11页，共53页。定义变量（2）数据类型简单数据类型(标量数据类型): NUMBER(m,n) 数字类型 m为总长度,n为小数长度 CHAR(m) 字符型 m为变量长度 VARCHAR2(m) 可变长字符型 m为最大长度 DATE 日期型 LONG 长型 BOONEAN 布尔型 值为TRUE FALSE NULL第12页，共53页。赋值变量（1）变量赋值时需使用PLSQL变量赋值操作符(:

6、=) 常量赋值: 变量名 := 常量 变量赋值: 变量名 := 同类型变量 表达式赋值: 变量名:=表达式或函数第13页，共53页。PLSQL中使用的SQL语句在PL/SQL块中,所有对数据库的访问和操作还是要经由SQL语言进行,在PL/SQL块中可以使用数据查询语言,数据操纵语言和数据控制语言,但不能使用数据定义语言具体地说可以使用select,insert,update,delete,commit, rollback,但不能使用create,alter,drop, grant,revoke.第14页，共53页。 PL/SQL流程控制PL/SQL具有与高级语言类似的流程控制语句.PL/SQL

7、主要控制语句有: 条件控制语句 循环控制语句 跳转控制语句第15页，共53页。条件控制语句(1)IF_THEN语句语法: IF 条件 THEN 语句;END IF;条件可为IS NULL或NOT IS NULL以及AND, OR, NOT,逻辑运算符第16页，共53页。条件控制语句(2)IF_THEN_ELSE语句语法: IF 条件 THEN 语句;ELSE语句;END IF;条件可为IS NULL或NOT IS NULL以及AND, OR, NOT,逻辑运算符第17页，共53页。条件控制语句(3)IF_THAN_ELSIF语句:语法:IF 条件 THEN语句;ELSIF 条件 THEN语句;

8、ELSIF 条件 THEN 语句;ELSE 语句;END IF第18页，共53页。循环控制语句(1)LOOP循环:语法:LOOP语句;EXIT WHEN 条件; END LOOP第19页，共53页。循环控制语句(2)for循环:语法:FOR 计数器 IN REVERSE 下界上界 LOOP 语句; END LOOP第20页，共53页。循环控制语句(3)WHILE循环:语法:WHILE 条件 LOOP 语句; END LOOP第21页，共53页。跳转控制语句语法: 标号 其他语句； GOTO 标号;说明:（1）跳转语句可在同一块语句间跳转（2）跳转语句可从子块跳转倒父块中，但不能从父块跳转到子块

9、中（3）跳转语句不能在IF语句体外跳到IF体内（4）跳转语句不能从循环体外跳到循环体内第22页，共53页。游标（cursor）第23页，共53页。为什么要使用游标（1）SQL语言与主语言具有不同数据处理方式SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录主语言是面向记录的，一组主变量一次只能存放一条记录第24页，共53页。为什么要使用游标（2）仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求引入游标的概念，用来协调这两种不同的处理方式 第25页，共53页。游标游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果每个游标区都有一个名字用户可以用SQL语句逐

10、一从游标中获取记录，并赋给主变量，交由主语言进一步处理第26页，共53页。定义游标语法: CURSOR 游标名称 ISSELECT 语句;定义游标应写在PL/SQL语句的DECLARE变量定义部分定义游标时SELECT语句中不可有INTO子语句在SELECT语句中使用的变量必须在定义游标前定义第27页，共53页。打开游标语法: OPEN 游标名;在BEGIN语句之后,可以打开游标,在打开游标之前,必须对游标所涉及到的变量赋值第28页，共53页。利用游标提取数据语法: FETCH 游标名 INTO 变量1,变量2,.;游标每次只能取到一条数据,同时游标指针下移,等待取下一条数据.该条语句变量列表

11、应与定义游标时的参数列表一致第29页，共53页。关闭游标语法: CLOSE 游标名;关闭游标,释放资源,游标关闭后不能再提取数据.第30页，共53页。存储过程的用户接口创建存储过程执行存储过程删除存储过程第31页，共53页。创建存储过程创建存储过程语法：CREATE Procedure 过程名（参数1，参数2，.） AS/IS；过程名：数据库服务器合法的对象标识参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数也可以定义输入参数、输出参数或输入/输出参数。默认为输入参数。过程体：是一个。包括声明部分和可执行语句部分 ;不用 declare 语句 第32页，共53页。创建存储过

12、程(2)例子：例1 利用存储过程来实现下面的应用: 从一个账户转指定数额的款项到另一个账户中。 CREATE PROCEDURE TRANSFER(inAccount INT， outAccount INT， amount FLOAT) AS totalDeposit FLOAT; BEGIN /* 检查转出账户的余额 */ SELECT total INTO totalDeposit FROM ACCOUNT WHERE ACCOUNTNUM=outAccount; IF totalDeposit IS NULL THEN /* 账户不存在或账户中没有存款 */ ROLLBACK; RETU

13、RN; END IF; 第33页，共53页。创建存储过程(3) IF totalDeposit amount THEN /* 账户账户存款不足 */ ROLLBACK; RETURN;END IF; UPDATE account SET total=total-amount WHERE ACCOUNTNUM=outAccount; /* 修改转出账户，减去转出额 */ UPDATE account SET total=total + amount WHERE ACCOUNTNUM=inAccount; /* 修改转入账户，增加转出额 */COMMIT; /* 提交转账事务 */END;第34页

14、，共53页。执行存储过程执行存储过程语法：CALL/PERFORM Procedure 过程名(参数1，参数2，.)；在PL/SQL中，数据库服务器支持在过程体中调用其他存储过程使用CALL或者PERFORM等方式激活存储过程的执行。调用时”()”是不可少的，无论是有参数还是无参数。第35页，共53页。执行存储过程（2）例子：例2从账户01003815868转一万元到01003813828账户中。 CALL Procedure TRANSFER(01003813828，01003815868，10000)； 第36页，共53页。删除存储过程删除存储过程语法：DROP PROCEDURE 过程名

15、；第37页，共53页。JAVA调用数据库存储过程前面我们已经讲述了有关oracle数据库的存储过程的相关知识，下面我将根据上面存储过程的实例来举出JAVA对oracle存储过程的调用第38页，共53页。仅有返回值的过程：public static void main(String args) Connection conn=BBConnection.getConnection();String sql=call stu_proc2(?);try CallableStatement statement=conn.prepareCall(sql);statement.registerOutPara

16、meter(1,Types.VARCHAR);statement.execute();String pname=statement.getString(1);System.out.println(pname); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();JAVA调用实例第39页，共53页。既有输入参数又有输出参数的过程public static void main(String args) Connection conn=BBConnection.getConnection();Str

17、ing sql=call stu_proc3(?,?);try CallableStatement statement=conn.prepareCall(sql);statement.setInt(1, 1);statement.registerOutParameter(2,Types.VARCHAR);statement.execute();String pname=statement.getString(2);System.out.println(pname); catch (SQLException e) / TODO Auto-generated catch blocke.printS

18、tackTrace();JAVA调用实例第40页，共53页。下面将举出无out参数的调用实例这种参数不适于用在查询语句上，因为查询语句需要有返回值才能被JAVA调用返回到 OUT 参数中的值可能会是JDBC NULL。当出现这种情形时，将对 JDBC NULL 值进行转换以使 getXXX 方法所返回的值为 null、0 或 false，这取决于getXXX 方法类型。对于 ResultSet 对象，要知道0或false是否源于JDBCNULL的唯一方法，是用方法wasNull进行检测。如果 getXXX 方法读取的最后一个值是 JDBC NULL，则该方法返回 true，否则返回 flase

19、 JAVA调用实例第41页，共53页。仅有参数的过程：public static void main(String args) Connection conn=BBConnection.getConnection();String sql=call stu_proc1(?);try CallableStatement statement=conn.prepareCall(sql);statement.setInt(1, 1);statement.execute();System.out.println(statement.execute(); catch (SQLException e) /

20、TODO Auto-generated catch blocke.printStackTrace();finallytry conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();JAVA调用实例第42页，共53页。无参数过程：public static void main(String args) Connection conn=BBConnection.getConnection();String sql=call stu_proc();try CallableSta

21、tement statement=conn.prepareCall(sql);statement.execute();System.out.println(statement.execute(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();finallytry conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();JAVA调用实例第43页，共53页。由于orac

22、le存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分， 1. 建一个程序包。如下： CREATE OR REPLACE PACKAGE MYPACKAGE AS TYPE MY_CURSOR IS REF CURSOR; end MYPACKAGE; 2. 建立存储过程，如下： CREATE OR REPLACE PROCEDURE PRO_3(p_CURSOR out MYPACKAGE.MY_CURSOR) IS BEGIN OPEN p_CURSOR FOR SELECT * FR

23、OM DBOEMP; END PRO_3; 可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。可以用sys_refcursor替换MYPACKAGE.MY_CURSOR ；但sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集。JAVA调用实例(返回结果集-1)第44页，共53页。Java代码：public static void main(String args) String driver = oracle.jdbc.driver.OracleDriver; String url = jdbc:oracle:t

24、hin::1521:orcl; String user = admin; String pwd = password; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; try Class.forName(driver); conn = DriverManager.getConnection(url, user, pwd); cs = conn.prepareCall( call DBO.PRO_3(?) ); cs.registerOutParameter(1, oracle.

25、jdbc.OracleTypes.CURSOR); cs.execute(); rs = (ResultSet) cs.getObject(1); while (rs.next() System.out.println(t + rs.getString(1) + t + rs.getString(2) + t); catch (SQLException e) e.printStackTrace();JAVA调用实例(返回结果集-2)第45页，共53页。 catch (Exception e) e.printStackTrace(); finally try if (rs != null) rs

26、.close(); if (cs != null) cs.close(); if (conn != null) conn.close(); catch (SQLException e) JAVA调用实例(返回结果集-3)第46页，共53页。存储过程的异常处理为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；预定义异常是指由PL/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。R

27、AISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在2000020999之间第47页，共53页。Oracle数据库中提供了一些异常处理的方法，下面通过一个实例来说明create or replace procedure stu_proc6(pno in student.sno%type,pname out student.sname%type) is begin select sname into pname from student where sno=pno; EXCEPTION when NO_DATA_FOUND then RAISE_APPLICATION

28、_ERROR(-20011,ERROR：不存在！);end;存储过程的异常处理实例第48页，共53页。 命名的系统异常 产生原因 ACCESS_INTO_NULL 未定义对象 CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置 COLLECTION_IS_NULL 集合元素未初始化 CURSER_ALREADY_OPEN 游标已经打开 DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值 INVALID_CURSOR 在不合法的游标上进行操作 INVALID_NUMBER内嵌的 SQL 语句不能将字符转换为数字 NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的 TOO_MANY_ROWS 执行 select into 时，结果集超过一行 ZERO_DIVIDE 除数为 0 SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值 SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数 VALUE_ERROR 赋值时，变量长度不足以容纳实际数据 LOGIN_DENIED PL/SQL 应用程序连接到