Java calls Oracle stored procedures return query result set

The data is based on an Oracle database Scott/tiger the EMP (employee) and dept (departmental) tables under the demo account:

CREATE TABLE DEPT

(

  DEPTNO number (2) NOT null primary key,

  dname  VARCHAR2 (),

  LOC    VARCHAR2 ( )

)

CREATE TABLE EMP

(

  EMPNO number      (4) NOT NULL  primary key,

  ename      VARCHAR2,

  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 Stored Procedure Code:

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;--is an exception number, Sqlcode is the details end sqlerrm of the exception

; 

Use the PL/SQL procedure statement to test the above stored procedure:

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 calling code:

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);//Set up the Reference Department number 20//register return type parameter.

　　　　The CURSOR type is not defined in Java.sql.Tyes, and St.registeroutparameter (2, Oracle.jdbc.driver.OracleTypes.CURSOR) is found in the driver package.

　　　　St.registeroutparameter (3, Types.varchar);

　　　　Boolean result = St.execute ();

　　　　Gets the return parameter RS = (ResultSet) st.getobject (2); STring msg = st.getstring (3); if (msg!= null) System.out.println (msg);//The Exception Information section is NULL to print System.out.println ("empno" + "" T "+" ename "+" "T" +

　　　　"Sal" + "" T "+" deptno ");

　　　　Output query Results 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 ();

 }

    }

  }

}

Note that the Scott demo account for the Oracle database is locked by default and needs to be unlocked before it is used:

Alter user Scott account unlock;

Then log on to the database server with Scott/tiger, prompting the password to expire and asking you to enter the new password immediately.