java调用存储过程

一：无返回值的存储过程

存储过程为：

CREATE OR REPLACE PROCEDURE zst(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)  AS

BEGIN

   INSERT INTO tablename (value1,value2) VALUES (PARA1, PARA2);

END zst;

 

java调用下面代码：

 

import java.sql.*;

import java.sql.ResultSet;

 

public class TestProcedureNoBack {

  public TestProcedureNoBack () {

  }

  public static void main(String[] args ){

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

    String strUrl = "";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    CallableStatement cstmt = null;

 

    try {

      Class.forName(driver);

      conn =  DriverManager.getConnection(strUrl, " usename", " password");

      CallableStatement proc = null;

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

      proc.setString(1, "10");

      proc.setString(2, "Two");

      proc.execute();

    }

    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) {

      }

    }

  }

 

二：有返回值的存储过程（非列表）

存储过程为：

CREATE OR REPLACE PROCEDURE backValue(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2)  AS

BEGIN

   SELECT colname  INTO PARA2 FROM tablename WHERE value1= PARA1;

END backValue;

java调用代码：

 

public class TestProcedureBack {

  public TestProcedureBack () {

  }

  public static void main(String[] args ){

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

    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:sid";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    try {

      Class.forName(driver);

      conn =  DriverManager.getConnection(strUrl, "usename ", "password ");

      CallableStatement proc = null;

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

      proc.setString(1, "100");

      proc.registerOutParameter(2, Types.VARCHAR);

      proc.execute();

      String testPrint = proc.getString(2);

      System.out.println("=testPrint=is="+testPrint);

    }

    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) {

      }

    }

  }

}

 

}

这里的proc.getString(2)中的数值2是和存储过程中的out列对应的，ｊａｖａ代码里注册时也需要匹配。

 

三：返回列表

由于oracle存储过程返回值都是通过out参数来实现的，对于列表不例外，由于是集合，就必须要用pagkage了.所以在建立存储过程之前要有相应的包存在：

1，  建一个程序包：

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS

 TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

2，建立存储过程，存储过程为：

CREATE OR REPLACE PROCEDURE TEST(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS

BEGIN

    OPEN p_CURSOR FOR SELECT * FROM ｔａｂｌｅ;

END TEST;

java调用代码：

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 TestProcedureTHREE {

  public TestProcedureTHREE() {

  }

  public static void main(String[] args ){

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

    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:ｓｉｄ";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

 

    try {

      Class.forName(driver);

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

 

      CallableStatement proc = null;

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

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

      proc.execute();

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

 

      while(rs.next())

      {

          System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");

      }

    }

    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) {

      }

    }

  }

}