Java calls Oracle stored procedures return query result set

Source: Internet
Author: User

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.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.