关于存储过程返回多个输出参数

wb8180013@163.com (lucky) — Sun, 19 Jul 2009 22:17:54 +0800

package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class TestJDBC {

   public static void main(String[] args) {
       String driver = "oracle.jdbc.driver.OracleDriver";
       String url = "jdbc:oracle:thin:@127.0.0.1:1521:gyp";

       Connection conn = null;

       try {

           // 加载Oracle驱动程序
           Class.forName(driver);

           // 获取数据库连接
           conn = DriverManager.getConnection(url, "wb001", "wb001");
           CallableStatement proc = null;

           // 调用存储过程
           proc = conn.prepareCall("{ call PROC_ORDER(?,?,?,?,?,?) }");

           // 设置输入参数
           proc.setString(1, "");
           proc.setString(2, "");
           proc.setString(3, "");

           // 注册输出参数
           proc.registerOutParameter(4, Types.VARCHAR);
           proc.registerOutParameter(5, Types.VARCHAR);
           proc.registerOutParameter(6, Types.INTEGER);

           proc.execute();

           // 输出参数1
           String out1 = proc.getString(4);
           System.out.println("out1="+out1);

           // 输出参数2
           String out2 = proc.getString(5);
           System.out.println("out2="+out2);

           // 输出参数3
           String out3 = proc.getString(6);
           System.out.println("out3="+out3);

       } catch (Exception e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
   }

}

--存储过程如下

create or replace procedure PROC_ORDER
(
   p_username in varchar2,
   p_bookid in varchar2,
   p_bookname in varchar2,
   p_uname out varchar2,
   p_bname out varchar2,
   p_return out integer
)
as
v_userid number(5);
v_uname   varchar2(20);
v_bname   varchar2(20);
v_bookid number(5);

BEGIN

   select userinfo_seq.nextval into v_userid from dual;
   -- 向用户表中插入数据
   insert into userinfo
   (
      userid,
      username,
      telephone,
      gender,
      address
   )
   values
   (
     v_userid,
     'wb',
     '13951776394',
     '1',
     'aaa'
   );

   select book_seq.nextval into v_bookid from dual;
   insert into book
   (
     id,
     bookid,
     bookname,
     price
   )
   values
   (
     v_bookid,
     'ISBN-001',
     'Thinking in Java',
     98
   );

   select userinfo.username into p_uname from userinfo where userinfo.userid=v_userid;
   select book.bookname into p_bname from book where book.id=v_bookid;
   p_return :=0;

EXCEPTION
WHEN OTHERS THEN
p_return:=-1;
ROLLBACK;
END;

--输出结果

out1=wb
out2=Thinking in Java
out3=0

--获得输出参数，一定要对应参数位置，并且要调用注册方法。

王波的博客 - 数据库

关于存储过程返回多个输出参数