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 登陸數據庫服務器，會提示密碼已過期，并要求你立即輸入新密碼。