JDBC調用oracle存儲過程

1有一個返回值的例子

首先我們以一個簡單的例子開始，在這個例子中，只存在一個返回值。存儲過程如下

SQL> create or replace procedure lizhe (aa out varchar2)is
2 begin
3 select passWord into aa from passport where id='123111';
4 end lizhe;
5
6 /

補充說明：要注意的是這個存儲過程存在一個（aa out varchar2）這樣的標識，這是一個參數，out表示這是一個輸出參數，在select語句中將passWord的值“into”到這個參數裡

然後再Java端：

public static void runProcedure(){
   try {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    Connection con = DriverManager.getConnection("jdbc:Oracle:thin:@192.168.0.159:1521:TOPS","YAC3","YAC3");
    Java.sql.CallableStatement cs = con.prepareCall("{call lizhe(?)}");
    cs.registerOutParameter(1, Java.sql.Types.VARCHAR);
    cs.execute();
    System.out.print("value2:" + cs.getString(1)); //取出字符串——關鍵

   } catch (InstantiationException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } catch (IllegalAccessException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } catch (ClassNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
}

補充說明：cs.registerOutParameter(1, Java.sql.Types.VARCHAR);
重點只有這一句

2.多個返回值的

SQL> CREATE OR REPLACE PACKAGE TESTPACKAGE AS
2
3 TYPE Test_CURSOR IS REF CURSOR;
4
5 end TESTPACKAGE;
6 /

Package created

SQL> CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
2
3 BEGIN
4
5      OPEN p_CURSOR FOR 'SELECT * FROM TB';
6
7 END TESTC;
8 /

Procedure created

import Java.sql.*;

import Java.io.OutputStream;

import Java.io.Writer;

import Java.sql.PreparedStatement;

import Java.sql.ResultSet;

import Oracle.jdbc.driver.*;

public class TestProcedure {

public TestProcedure() {

public static void main(String[] args ){

    String driver = "oracle.jdbc.driver.OracleDriver";

    String strUrl = "jdbc:Oracle:thin:@192.168.0.159:1521:tops";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    try {

      Class.forName(driver);

      conn = DriverManager.getConnection(strUrl, "yac3", "yac3");

      CallableStatement proc = null;

      proc = conn.prepareCall("{call testc(?)}");

      proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

      proc.executeQuery();

      rs = (ResultSet)proc.getObject(1);

      while(rs.next())

      {

         System.out.println(rs.getString("VALUE"));

      }

    }

    catch (SQLException ex2) {

      ex2.printStackTrace();

    }

    catch (Exception ex2) {

      ex2.printStackTrace();

    }

    finally{

      try {

        if(rs != null){

          rs.close();

          if(stmt!=null){

            stmt.close();

          }

          if(conn!=null){

            conn.close();

          }

        }

      }

      catch (SQLException ex1) {

      }

    }