现在的位置: 首页 > 综合 > 正文

java与存储过程

2013年07月30日 ⁄ 综合 ⁄ 共 11529字 ⁄ 字号 小 中 大 ⁄ 评论关闭

一：无返回值的存储过程

存储过程为：

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

BEGIN

   INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);

END TESTA;

然后呢，在java里调用时就用下面的代码：

package com.hyq.src;

import java.sql.*;

import java.sql.ResultSet;

public class TestProcedureOne {

 public TestProcedureOne() {

 }

 public static void main(String[] args ){

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

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

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    CallableStatement cstmt = null;

    try {

      Class.forName(driver);

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

      CallableStatement proc = null;

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

      proc.setString(1, "100");

      proc.setString(2, "TestOne");

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

      }

    }

 }

}

当然了，这就先要求要建张表TESTTB,里面两个字段（I_ID，I_NAME）。

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

存储过程为：

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

BEGIN

   SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;

END TESTB;

在java里调用时就用下面的代码：

package com.hyq.src;

public class TestProcedureTWO {

 public TestProcedureTWO() {

 }

 public static void main(String[] args ){

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

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

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    try {

      Class.forName(driver);

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

      CallableStatement proc = null;

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

      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列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。

三：返回列表

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，

1， 建一个程序包。如下：

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS

 TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

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

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

BEGIN

    OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;

END TESTC;

可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。

在java里调用时就用下面的代码：

package com.hyq.src;

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:hyq";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    try {

      Class.forName(driver);

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

      CallableStatement proc = null;

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

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

      }

    }

 }

}

在这里要注意，在执行前一定要先把oracle的驱动包放到class路径里，否则会报错的。

 

-----------------------------------------------------------------------------------------------------

 

这是总结以前使用spring调用Oracle存储过程，并用cursor返回结果集的一个完整实例，希望能对大家有帮助。

1. 创建表：

代码
  1. create table TEST_USERS    
  2. (    
  3.   USER_ID  VARCHAR2(10) not null,    
  4.   NAME     VARCHAR2(10) not null,    
  5.   PASSWORD VARCHAR2(20) not null    
  6. )  

 

2. 创建存储过程：

代码
  1. create or replace package display_users_package is    
  2.      type search_results is ref cursor;    
  3.      procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type);    
  4. end display_users_package;    
  5.   
  6. create or replace package body display_users_package is    
  7.      procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type)    
  8.           is    
  9.           begin    
  10.           if userId is not null then    
  11.               open results_out for select * from test_users where user_id like userId || '%';    
  12.           else    
  13.               open results_out for  select * from test_users;    
  14.           end if;    
  15.       end display_users_proc;    
  16. end display_users_package;  

 

这个results_out是一个游标类型，用来返回查找的结果集。

3.　完整实现代码：

代码

  1. import java.sql.CallableStatement;   
  2. import java.sql.Connection;   
  3. import java.sql.ResultSet;   
  4. import java.sql.SQLException;   
  5. import java.util.ArrayList;   
  6. import java.util.HashMap;   
  7. import java.util.List;   
  8. import java.util.Map;   
  9.   
  10. import javax.sql.DataSource;   
  11.   
  12. import oracle.jdbc.OracleTypes;   
  13.   
  14. import org.springframework.dao.DataAccessException;   
  15. import org.springframework.jdbc.core.CallableStatementCallback;   
  16. import org.springframework.jdbc.core.CallableStatementCreator;   
  17. import org.springframework.jdbc.core.JdbcTemplate;   
  18.   
  19. import com.spring.stored.procedure.util.DataContextUtil;   
  20.   
  21. /**  
  22.  * @author Jane Jiao  
  23.  *  
  24.  */  
  25. public class SpringStoredProce {   
  26.        
  27.     public List<Map> execute(String storedProc, String params){   
  28.         List<Map> resultList = null;   
  29.         try{   
  30.             final DataSource ds = DataContextUtil.getInstance().getDataSource();   
  31.             final JdbcTemplate template = new JdbcTemplate(ds);   
  32.             resultList = (List<Map>)template.execute(new ProcCallableStatementCreator(storedProc, params),   
  33.                                                      new ProcCallableStatementCallback());   
  34.         }catch(DataAccessException e){   
  35.             throw new RuntimeException("execute method error : DataAccessException " + e.getMessage());   
  36.         }   
  37.          return resultList;   
  38.     }   
  39.        
  40.        
  41.     /**  
  42.      * Create a callable statement in this connection.  
  43.      */  
  44.     private class ProcCallableStatementCreator implements CallableStatementCreator {   
  45.         private String storedProc;   
  46.         private String params;   
  47.            
  48.        
  49.         /**  
  50.          * Constructs a callable statement.  
  51.          * @param storedProc                  The stored procedure's name.  
  52.          * @param params                      Input parameters.  
  53.          * @param outResultCount              count of output result set.  
  54.          */  
  55.         public ProcCallableStatementCreator(String storedProc, String params) {   
  56.             this.params = params;   
  57.             this.storedProc = storedProc;   
  58.         }   
  59.            
  60.         /**  
  61.          * Returns a callable statement  
  62.          * @param conn          Connection to use to create statement  
  63.          * @return cs           A callable statement  
  64.          */  
  65.         public CallableStatement createCallableStatement(Connection conn) {   
  66.             StringBuffer storedProcName = new StringBuffer("call ");   
  67.             storedProcName.append(storedProc + "(");   
  68.             //set output parameters   
  69.             storedProcName.append("?");   
  70.             storedProcName.append(", ");   
  71.                
  72.             //set input parameters   
  73.             storedProcName.append("?");   
  74.             storedProcName.append(")");   
  75.   
  76.             CallableStatement cs = null;   
  77.             try {   
  78.                 // set the first parameter is OracleTyep.CURSOR for oracel stored procedure   
  79.                 cs = conn.prepareCall(storedProcName.toString());   
  80.                 cs.registerOutParameter (1, OracleTypes.CURSOR);   
  81.                // set the sencond paramter   
  82.                 cs.setObject(2, params);   
  83.             } catch (SQLException e) {   
  84.                 throw new RuntimeException("createCallableStatement method Error : SQLException " + e.getMessage());   
  85.             }   
  86.             return cs;   
  87.         }   
  88.            
  89.     }   
  90.        
  91.     /**  
  92.      *   
  93.      * The ProcCallableStatementCallback return a result object,   
  94.      * for example a collection of domain objects.  
  95.      *  
  96.      */  
  97.     private class ProcCallableStatementCallback implements CallableStatementCallback {   
  98.            
  99.         /**  
  100.          * Constructs a ProcCallableStatementCallback.  
  101.          */  
  102.         public ProcCallableStatementCallback() {   
  103.         }   
  104.   
  105.         /**  
  106.          * Returns a List(Map) collection.  
  107.          * @param cs                       object that can create a CallableStatement given a Connection  
  108.          * @return resultsList             a result object returned by the action, or null  
  109.          */  
  110.         public Object doInCallableStatement(CallableStatement cs){   
  111.             List<Map> resultsMap =  new ArrayList<Map>();   
  112.             try {   
  113.                 cs.execute();    
  114.                 ResultSet rs = (ResultSet) cs.getObject(1);   
  115.                 while (rs.next()) {   
  116.                     Map<String, String> rowMap = new HashMap<String, String>();   
  117.                     rowMap.put("userId", rs.getString("USER_ID"));   
  118.                     rowMap.put("name", rs.getString("NAME"));   
  119.                     rowMap.put("password", rs.getString("PASSWORD"));   
  120.                     resultsMap.add(rowMap);   
  121.                 }      
  122.                 rs.close();   
  123.             }catch(SQLException e) {   
  124.                 throw new RuntimeException("doInCallableStatement method error : SQLException " + e.getMessage());   
  125.             }   
  126.             return resultsMap;   
  127.        }   
  128.     }   
  129. }   
  130. ------------------------------------------------------------------------------

  131. 内容或简介：
    /**
    调用数据库里的一个函数
    一个函数本质上一个返回一个结果的存储过程，这个例子示范了怎么调用有in、out和in/out参数的函数
    ***********************************/
    CallableStatement cs;
    try {
    // 调用一个没有参数的函数; 函数返回 a VARCHAR
    // 预处理callable语句

      cs = connection.prepareCall("{? = call myfunc}");

    // 注册返回值类型
    cs.registerOutParameter(1, i);

    // Execute and retrieve the returned value
    cs.execute();
    String retValue = cs.getString(1);

    // 调用有一个in参数的函数; the function returns a VARCHAR
    cs = connection.prepareCall("{? = call myfuncin(?)}");

    // Register the type of the return value
    cs.registerOutParameter(1, Types.VARCHAR);

    // Set the value for the IN parameter
    cs.setString(2, "a string");

    // Execute and retrieve the returned value
    cs.execute();
    retValue = cs.getString(1);

    // 调用有一个out参数的函数; the function returns a VARCHAR
    cs = connection.prepareCall("{? = call myfuncout(?)}");

    // Register the types of the return value and OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.registerOutParameter(2, Types.VARCHAR);

    // Execute and retrieve the returned values
    cs.execute();
    retValue = cs.getString(1);           // return value
    String outParam = cs.getString(2);    // OUT parameter

    // 调用有一个in/out参数的函数; the function returns a VARCHAR
    cs = connection.prepareCall("{? = call myfuncinout(?)}");

    // Register the types of the return value and OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.registerOutParameter(2, Types.VARCHAR);

    // Set the value for the IN/OUT parameter
    cs.setString(2, "a string");

    // Execute and retrieve the returned values
    cs.execute();
    retValue = cs.getString(1);           // return value
    outParam = cs.getString(2);           // IN/OUT parameter
    } catch (SQLException e) {
    }

返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）
必威体育
必威电竞