Java调用Oracle存储过程返回查询结果集

 

数据采用oracle数据库scott/tiger示范账户下的emp(员工)dept(部门)表:

create table DEPT

(

  DEPTNO NUMBER(2) not null primary key,

  DNAME  VARCHAR2(14),

  LOC    VARCHAR2(13)

)


 

create table EMP

(

  EMPNO      NUMBER(4) not null  primary key,

  ENAME      VARCHAR2(10),

  JOB        VARCHAR2(9),

  MGR        NUMBER(4),

  HIREDATE   DATE,

  SAL        NUMBER(7,2),

  COMM       NUMBER(7,2),

  DEPTNO     NUMBER(2),

  constraint FK_DEPTNO foreign key (DEPTNO)references DEPT (DEPTNO)

)


 

Oracle存储过程代码:

Create or replace PROCEDURE searchEmpByDept(

       indeptno IN NUMBER,

       empcur OUT sys_refcursor,

       errorMsg OUT varchar)

IS

BEGIN

    errorMsg:='';

    OPEN empcur FOR

      SELECT *

      FROM emp

      WHERE deptno = indeptno

      ORDER BY empno;

  EXCEPTION

    WHEN OTHERS THEN

      errorMsg:= sqlerrm;-- sqlcode是异常编号,sqlerrm是异常的详细信息

END searchEmpByDept; 

使用 pl/sql 过程语句测试上面的存储过程:

declare 

errorMsg  varchar(1000);

empcur sys_refcursor;

emp scott.emp%rowtype; 

begin

   searchEmpByDept(10,empcur, errorMsg);

   if errorMsg is not null then

      dbms_output.put_line(errorMsg);

   end if;

   

   loop

      fetch empcur into emp;

      EXIT WHEN empcur%notfound ; 

      dbms_output.put_line(emp.ename);

   end loop;

   

   close  empcur;

end;


 

Java调用代码:

public class DBHelper  {

    private Connection conn = null;

    public Connection getConnection() throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        conn = DriverManager.getConnection(

            "jdbc:oracle:thin:@192.168.1.6:1521:ntcsoft", 

            "scott",

            "tiger");

        return conn;

    }

}




public class CallOracleProcedure {

  public static void main(String args[]) {

    ResultSet rs = null;

    CallableStatement st = null;

    Connection con =  null;

    try {

    con = new DBHelper().getConnection();

    String sql = "call searchEmpByDept(?,?,?)";

    st = con.prepareCall(sql);

    st.setInt(1, 20);//设置入参部门编号20

    //注册返回类型参数。CURSOR类型在java.sql.Tyes中没有定义,在驱动程序包中找到了

    st.registerOutParameter(2, oracle.jdbc.driver.OracleTypes.CURSOR);

    st.registerOutParameter(3, Types.VARCHAR);

    boolean result = st.execute();

    //获取返回参数

    rs = (ResultSet) st.getObject(2);

    String msg = st.getString(3);

    if(msg != null)

       System.out.println(msg);//异常信息部为null则打印

    System.out.println("empno" + ""t" + "ename" + ""t" + "sal" + ""t"+ "deptno");

    //输出查询结果

    StringBuilder output = new StringBuilder();

    while (rs.next()) {

        output.append(rs.getInt("empno"))

        .append(""t")

        .append(rs.getString("ename"))

        .append(""t")

        .append(rs.getDouble("sal"))

        .append(""t")

        .append(rs.getInt("deptno"));

        System.out.println(output.toString());

        output.delete(0, output.length());

    }

    output = null;

    }catch (Exception e) {

       e.printStackTrace();

    }finally{

    try {

       if(rs!=null) rs.close();

       if(st!=null) st.close();

       if(con!=null) con.close();

    } catch (SQLException e) {

        e.printStackTrace();

    }

    }

  }

}


 

注意,oracle数据库的scott示范账户默认是被锁定的,在使用之前需要解锁:

alter user scott account unlock;

然后以scott/tiger 登陆数据库服务器,会提示密码已过期,并要求你立即输入新密码。


 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值