oracle 存储过程

  

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

Oracle存储过程可以有无参数存储过程和带参数存储过程。

一、无参数存储过程语法：

 create or replace procedure noPramePro

 as ...;

 begin

  ...;

 exception

  ...;

 end;

二、带参存储过程实例

 create or replace procedure runbyparmeters  (isal in emp.sal%type,
                            sname out varchar,sjob in out varchar)
   as icount number;
   begin      

   select count(*) into icount from emp where sal>isal and job=sjob;
   if icount=1 then
    select emp.ename,emp.job into sname,sjob from emp where sal>isal and job = sjob;

   else

     raise  too_many_rows;
   end if;
   exception

     when too_many_rows then
   DBMS_OUTPUT.PUT_LINE('返回值多于1行');
     when others then
   DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
   end;

三、oracle存储过程调用

方式一、

declare
 realsal emp.sal%type;
 realname varchar(40);
 realjob varchar(40);
 begin   //存储过程调用开始
 realsal:=1100;
 realname:='';
 realjob:='CLERK';
 runbyparmeters(realsal,realname,realjob);     －－必须按顺序
 DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
 END;  //过程调用结束

方式二、

declare
 realsal emp.sal%type;
 realname varchar(40);
 realjob varchar(40);
 begin   //存储过程调用开始
 realsal:=1100;
 realname:='';
 realjob:='CLERK';
 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);     －－顺序可变
 DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
 END;  //过程调用结束

四、java调用存储过程
1、无返回值的调用：

CallableStatement proc = null;

      proc = conn.prepareCall("{ call prodName(?,?) }");

      proc.setString(1, "100");

      proc.setString(2, "TestOne");

      proc.execute();

 2、有返回值的调用

    CallableStatement proc = null;

      proc = conn.prepareCall("{ call prodName(?,?) }");//第一个表示输入in 第一个表示输出out

      proc.setString(1, "100");

      proc.registerOutParameter(2, Types.VARCHAR);

      proc.execute();

      String testPrint = proc.getString(2);

      System.out.println("=testPrint=is="+testPrint);

这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。

 

五、程序包package

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，

1，  建一个程序包。如下：

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS

 TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

2，建立存储过程，存储过程为：

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS

BEGIN

    OPEN p_CURSOR FOR SELECT * FROM tabName;

END TESTC;

可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。

对应的java调用如下：

CallableStatement proc = null;

      proc = conn.prepareCall("{ call testc(?) }");

      proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

      proc.execute();

      rs = (ResultSet)proc.getObject(1);

      while(rs.next())

      {

          System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");

      }