oracle存储过程学习经典.doc

1、Oracle 存储过程学习目录Oracle 存储过程 .1Oracle 存储过程基础知识 .1Oracle 存储过程的基本语法 .2关于 Oracle 存储过程的若干问题备忘 .41. 在 Oracle 中，数据表别名不能加 as。 .42. 在存储过程中，select 某一字段时，后面必须紧跟 into，如果 select 整个记录，利用游标的话就另当别论了。 .53. 在利用 select.into.语法时，必须先确保数据库中有该条记录，否则会报出“no data found“异常。 .54. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错 .55. 在存储

2、过程中，关于出现 null 的问题 .56. Hibernate 调用 Oracle 存储过程 .6用 Java 调用 Oracle 存储过程总结 .6一、 无返回值的存储过程 .6二、 有返回值的存储过程（非列表） .8三、 返回列表 .9在存储过程中做简单动态查询 .11一、 本地动态 SQL .12二、 使用 DBMS_SQL 包 .13Oracle 存储过程调用 Java 方法 .16Oracle 高效分页存储过程实例 .17Oracle 存储过程基础知识商业规则和业务逻辑可以通过程序存储在 Oracle 中，这个程序就是存储过程。 存储过程是 SQL, PL/SQL, Java 语句

3、的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。要创建一个过程对象（procedural object） ，必须有 CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户 schema 使用，那么你必须有 CREATE ANY PROCEDURE 权限。执行 procedure 的时候，可能需要 excute 权限。或者 EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示： grant execute on MY_PROCEDURE to Jelly调用一个存储过程的例子： e

4、xecute MY_PROCEDURE( ONE PARAMETER);存储过程(PROCEDURE) 和函数 (FUNCTION)的区别。 function 有返回值，并且可以直接在 Query 中引用 function 和或者使用 function 的返回值。本质上没有区别,都是 PL/SQL 程序,都可以有返回值。最根本的区别是： 存储过程是命令, 而函数是表达式的一部分。比如：select max(NAME) FROM但是不能 exec max(NAME) 如果此时 max 是函数。PACKAGE 是 function，procedure，variables 和 sql 语句的组合。p

5、ackage 允许多个procedure 使用同一个变量和游标。创建 procedure 的语法：CREATE OR REPLACE PROCEDURE schema.procedure(argument IN | OUT | IN OUT NO COPY datatype, argument IN | OUT | IN OUT NO COPY datatype.) authid current_user | definer is | as pl/sql_subprogram_body | language java name String | c name, name library lib

6、_nameSql 代码：CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS BEGIN UPDATE accounts SET balance = balance + amount WHERE account_id = acc_no; END; 可以使用 create or replace procedure 语句， 这个语句的用处在于，你之前赋予的excute 权限都将被保留。IN, OUT, IN OUT 用来修饰参数。IN 表示这个变量必须被调用者赋值然后传入到 PROCEDURE 进行处理。OUT 表示

7、 PRCEDURE 通过这个变量将值传回给调用者。IN OUT 则是这两种的组合。authid 代表两种权限：定义者权限（difiner right 默认） ，执行者权限（invoker right） 。定义者权限说明这个 procedure 中涉及的表，视图等对象所需要的权限只要定义者拥有权限的话就可以访问。执行者权限则需要调用这个 procedure 的用户拥有相关表和对象的权限。Oracle 存储过程的基本语法1. 基本结构CREATE OR REPLACE PROCEDURE 存储过程名字(参数 1 IN NUMBER,参数 2 IN NUMBER) AS变量 1 INTEGER :=

8、0;变量 2 DATE;BEGINEND 存储过程名字2. SELECT INTO STATEMENT将 select 查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出 NO_DATA_FOUND)例子：BEGINSELECT col1,col2 into 变量 1,变量 2 FROM typestruct where xxx;EXCEPTIONWHEN NO_DATA_FOUND THENxxxx;END;.3. IF 判断IF V_TEST=1 THENBEGIN do somethingEND;END IF;4. while 循环WH

9、ILE V_TEST=1 LOOPBEGINXXXXEND;END LOOP;5. 变量赋值V_TEST := 123;6. 用 for in 使用 cursor.ISCURSOR cur IS SELECT * FROM xxx;BEGINFOR cur_result in cur LOOPBEGINV_SUM :=cur_result.列名 1+cur_result.列名 2END;END LOOP;END;7. 带参数的 cursorCURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;OPEN C_U

10、SER(变量值);LOOPFETCH C_USER INTO V_NAME;EXIT FETCH C_USER%NOTFOUND;do somethingEND LOOP;CLOSE C_USER;8. 用 pl/sql developer debug连接数据库后建立一个 Test WINDOW在窗口输入调用 SP 的代码,F9 开始 debug,CTRL+N 单步调试9. Pl/Sql 中执行存储过程在 sql*plus 中：declare -必要的变量声明，视你的过程而定 begin execute yourprocudure(parameter1,parameter2,.); end /

11、 在SQL/PLUS中调用存储过程，显示结果： SQLset serveoutput on -打开输出SQLvar info1 number; -输出 1 SQLvar info2 number; -输出 2 SQLdeclare var1 varchar2(20); -输入 1 var2 varchar2(20); -输入 2 var3 varchar2(20); -输入 2 BEGIN pro(var1,var2,var3,:info1,:info2); END; / SQLprint info1; SQLprint info2;注：在 EXECUTE IMMEDIATE STR 语句是

12、SQLPLUS 中动态执行语句，它在执行中会自动提交，类似于 DP 中 FORMS_DDL 语句，在此语句中 str 是不能换行的, 只能通过连接字符“|“，或着在在换行时加上“-“连接字符。 关于 Oracle 存储过程的若干问题备忘1. 在 Oracle 中，数据表别名不能加 as。如：select a.appname from appinfo a;- 正确select a.appname from appinfo as a;- 错误也许，是怕和 Oracle 中的存储过程中的关键字 as 冲突的问题吧2. 在存储过程中，select 某一字段时，后面必须紧跟into，如果 select

13、整个记录，利用游标的话就另当别论了。select af.keynode into kn from APPFOUNDATION afwhere af.appid=aid and af.foundationid=fid; - 有 into，正确编译select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;- 没有 into，编译报错，提示：Compilation Error: PLS-00428: an INTO clause is expected in this SELECT state

14、ment3. 在利用 select.into.语法时，必须先确保数据库中有该条记录，否则会报出“no data found“异常。可以在该语法之前，先利用 select count(*) from 查看数据库中是否存在该记录，如果存在，再利用 select.into.4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;- 正确运行select af.keynode into kn from APPFOUND

15、ATION af where af.appid=appid and af.foundationid=foundationid;- 运行阶段报错，提示：ORA-01422:exact fetch returns more than requested number of rows5. 在存储过程中，关于出现 null 的问题假设有一个表 A，定义如下：create table A(id varchar2(50) primary key not null,vcount number(8) not null,bid varchar2(50) not null - 外键 );如果在存储过程中，使用如下

16、语句：select sum(vcount) into fcount from A where bid=xxxxxx;如果 A 表中不存在 bid=“xxxxxx“的记录，则 fcount=null(即使 fcount 定义时设置了默认值，如：fcount number(8):=0 依然无效，fcount 还是会变成 null)，这样以后使用 fcount 时就可能有问题，所以在这里最好先判断一下：if fcount is null thenfcount:=0;end if;这样就一切 ok 了。6. Hibernate 调用 Oracle 存储过程this.pnumberManager.get

17、HibernateTemplate().execute(new HibernateCallback() .public Object doInHibernate(Session session)throws HibernateException, SQLException .CallableStatement cs = session.connection().prepareCall(“call modifyapppnumber_remain(?)“);cs.setString(1, foundationid);cs.execute();return null;);用 Java 调用 Orac

18、le 存储过程总结 一、 无返回值的存储过程测试表:- Create tablecreate table TESTTB(ID VARCHAR2(30),NAME VARCHAR2(30)tablespace BOMpctfree 10initrans 1maxtrans 255storage(initial 64Kminextents 1maxextents unlimited);例: 存储过程为( 当然了，这就先要求要建张表 TESTTB,里面两个字段（I_ID ，I_NAME） 。)：CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,

19、PARA2 IN VARCHAR2) ASBEGININSERT INTO BOM.TESTTB(ID, NAME) VALUES (PARA1, PARA2);END TESTA;在 Java 里调用时就用下面的代码：package com.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import jav

20、a.sql.Statement;public class TestProcedureDemo1 public TestProcedureDemo1() public static void main(String args) String driver = “Oracle.jdbc.driver.OracleDriver“;String strUrl = “jdbc:Oracle:thin:10.20.30.30:1521:vasms“;Statement stmt = null;ResultSet rs = null;Connection conn = null;CallableStatem

21、ent proc = null;try Class.forName(driver);conn = DriverManager.getConnection(strUrl, “bom“, “bom“);proc = conn.prepareCall(“ call BOM.TESTA(?,?) “);proc.setString(1, “100“);proc.setString(2, “TestOne“);proc.execute(); catch (SQLException ex2) ex2.printStackTrace(); catch (Exception ex2) ex2.printSta

22、ckTrace(); finally try if (rs != null) rs.close();if (stmt != null) stmt.close();if (conn != null) conn.close(); catch (SQLException ex1) 二、 有返回值的存储过程（非列表）例：存储过程为：CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2) ASBEGINSELECT NAME INTO PARA2 FROM TESTTB WHERE ID = PARA1;END T

23、ESTB;在 Java 里调用时就用下面的代码：package com.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;public class TestProcedureDemo2 public static vo

24、id main(String args) String driver = “Oracle.jdbc.driver.OracleDriver“;String strUrl = “jdbc:Oracle:thin:10.20.30.30:1521:vasms“;Statement stmt = null;ResultSet rs = null;Connection conn = null;CallableStatement proc = null;try Class.forName(driver);conn = DriverManager.getConnection(strUrl, “bom“,

25、“bom“);proc = conn.prepareCall(“ call BOM.TESTB(?,?) “);proc.setString(1, “100“);proc.registerOutParameter(2, Types.VARCHAR);proc.execute();String testPrint = proc.getString(2);System.out.println(“=testPrint=is=“ + testPrint); catch (SQLException ex2) ex2.printStackTrace(); catch (Exception ex2) ex2

26、.printStackTrace(); finally try if (rs != null) rs.close();if (stmt != null) stmt.close();if (conn != null) conn.close(); catch (SQLException ex1) 注意，这里的 proc.getString(2)中的数值 2 并非任意的，而是和存储过程中的 out 列对应的，如果 out 是在第一个位置，那就是 proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个 out 参数了。

27、三、 返回列表由于 Oracle 存储过程没有返回值，它的所有返回值都是通过 out 参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用 pagkage 了.所以要分两部分，1. 建一个程序包。如下：CREATE OR REPLACE PACKAGE TESTPACKAGE ASTYPE TEST_CURSOR IS REF CURSOR;end TESTPACKAGE;2. 建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE TESTC(P_CURSOR out TESTPACKAGE.TEST_CURSOR) ISBEGINOPEN

28、P_CURSOR FORSELECT * FROM BOM.TESTTB;END TESTC;可以看到，它是把游标（可以理解为一个指针） ，作为一个 out 参数来返回值的。在 Java 里调用时就用下面的代码：在这里要注意，在执行前一定要先把 Oracle 的驱动包放到 class 路径里，否则会报错的。package com.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Resul

29、tSet;import java.sql.SQLException;import java.sql.Statement;public class TestProcedureDemo3 public static void main(String args) String driver = “Oracle.jdbc.driver.OracleDriver“;String strUrl = “jdbc:Oracle:thin:10.20.30.30:1521:vasms“;Statement stmt = null;ResultSet rs = null;Connection conn = nul

30、l;CallableStatement proc = null;try Class.forName(driver);conn = DriverManager.getConnection(strUrl, “bom“, “bom“);proc = conn.prepareCall(“ call bom.testc(?) “);proc.registerOutParameter(1, Oracle.jdbc.OracleTypes.CURSOR);proc.execute();rs = (ResultSet) proc.getObject(1);while (rs.next() System.out.println(“ + rs.getString(1) +