JAVA中调用Oracle存储过程-优快云博客

本文介绍了一个Java类如何通过CallableStatement调用Oracle数据库中的存储过程。包括无返回值的插入操作、获取单个输出参数及使用游标返回记录集的示例。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/**
数据库表
CREATE TABLE TB_MONITOR (
   ID                   NUMBER(20)                      NOT NULL,
   MONITOR_OBJECT_CODE CHAR(10),
   MONITOR_OBJECT_NAME VARCHAR2(180),
   BRANCH_CODE          CHAR(10),
   SYSTEM_CODE          CHAR(10),
   DIREC_NAME           VARCHAR2(180),
   FILE_NUM             NUMBER,
   STATUS               CHAR(10),
   BEGIN_TIME           TIMESTAMP,
   END_TIME             TIMESTAMP,
   DATA_TIME            TIMESTAMP,
   MONITOR_TIME         TIMESTAMP,
   REMARK               VARCHAR2(180),
   CONSTRAINT PK_TB_MONITOR PRIMARY KEY (ID)
);
*/
public class OracleProcedureCall {

private static String driver = "oracle.jdbc.driver.OracleDriver";
private static String strUrl = "jdbc:oracle:thin:@192.168.1.90:1521:odsdb";
private static String userName = "odsdb";
private static String password = "ods";

private static Connection conn = null;

//获得数据库连接
public static Connection getConnection(){
   try{
    if(conn == null){
     Class.forName(driver);
     conn = DriverManager.getConnection(strUrl, userName, password);
    }
   }catch(ClassNotFoundException e){
    e.printStackTrace();
   }catch(SQLException e){
    e.printStackTrace();
   }

     return conn;
}

/*无返回值的存储过程
CREATE OR REPLACE PROCEDURE AddMonInfo
( 
n_id tb_monitor.id%TYPE, 
n_oc tb_monitor.monitor_object_code%TYPE, 
n_on tb_monitor.monitor_object_name%TYPE, 
n_bc tb_monitor.branch_code%TYPE,
n_sc tb_monitor.system_code%TYPE,
n_fn tb_monitor.file_num%TYPE,
n_st tb_monitor.status%TYPE,
n_rk tb_monitor.remark%TYPE
) 
AS 
BEGIN 
--向表中插入数据 
INSERT INTO tb_monitor(id,monitor_object_code,monitor_object_name,branch_code,system_code,file_num,status,remark) 
VALUES(n_id,n_oc,n_on,n_bc,n_sc,n_fn,n_st,n_rk); 
END AddMonInfo; 
*/
public void testInsert(){
     try {
     CallableStatement proc = getConnection().prepareCall("{ call odsdb.AddMonInfo(?,?,?,?,?,?,?,?) }");
     proc.setString(1, "100");
     proc.setString(2, "o_code");
     proc.setString(3, "o_name");
     proc.setString(4, "b_code");
     proc.setString(5, "s_code");
     proc.setString(6, "1");
     proc.setString(7, "status");
     proc.setString(8, "remark");

     proc.execute();
     }
     catch (SQLException ex2) {
     ex2.printStackTrace();
     }
     catch (Exception ex2) {
     ex2.printStackTrace();
     }
     finally{
     try {
        if(conn!=null){
         conn.close();
           }
     }
     catch (SQLException ex1) {
     }
     }
}

/*有返回值的存储过程（非列表）
CREATE OR REPLACE PROCEDURE QueryMonInfo
(
   n_id IN tb_monitor.id%TYPE, 
   n_oc OUT VARCHAR2, 
   n_on OUT VARCHAR2
) AS
BEGIN 
    SELECT monitor_object_code, monitor_object_name into n_oc,n_on FROM tb_monitor WHERE ID= n_id; 
END QueryMonInfo;
*/
public String[] testQueryArray(){

   String[] resultArr = null;

   try {
    CallableStatement proc = getConnection().prepareCall("{ call odsdb.QueryMonInfo(?,?,?) }");
    proc.setInt(1, 100);
    proc.registerOutParameter(2, Types.VARCHAR);
    proc.registerOutParameter(3, Types.VARCHAR);
    proc.execute();

    resultArr = new String[2];
    resultArr[0] = proc.getString(2);
    resultArr[1] = proc.getString(3);
    System.out.println("=code=is= "+resultArr[0]);
    System.out.println("=name=is= "+resultArr[1]);
   }
   catch (SQLException ex2) {
    ex2.printStackTrace();
   }
   catch (Exception ex2) {
    ex2.printStackTrace();
   }
   finally{
    try {
     if(conn!=null){
      conn.close();
     }
    }
    catch (SQLException ex1) {
     ex1.printStackTrace();
    }
   }
   return resultArr;
}

/*返回列表，需要使用package方式
先创建Package
CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
然后创建procedure
CREATE OR REPLACE PROCEDURE QueryMonResultSet(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS 
BEGIN
     OPEN p_CURSOR FOR SELECT * FROM tb_monitor;
END QueryMonResultSet;
*/
public ResultSet testQueryResultSet(){

   ResultSet rs = null;

     try {
     CallableStatement proc = getConnection().prepareCall("{ call odsdb.QueryMonResultSet(?) }");
     proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
     proc.execute();
     rs = (ResultSet)proc.getObject(1);

     while(rs.next()) {
        System.out.println("ID:" + rs.getString(1) + "\tCODE:"+rs.getString(2)+"");
     }
     }
     catch (SQLException ex2) {
     ex2.printStackTrace();
     }
     catch (Exception ex2) {
     ex2.printStackTrace();
     }
     finally{
     try {
        if(rs != null){
         rs.close();
         if(conn!=null){
          conn.close();
         }
        }
     }
     catch (SQLException ex1) {
     }
     }
   return rs;
}

public static void main(String[] args){
   OracleProcedureCall call = new OracleProcedureCall();
   call.testQueryResultSet();
}
}


//----2222222222222222------------------------------------------------------------------------------------------------------------------

import java.sql.*;

public class n {
public static void main(String args [])
{
   Connection myConnection = null; //与特定数据库的连接（会话）。在连接上下文中执行 SQL 语句并返回结果
   Statement myStatement = null; //用于执行静态 SQL 语句并返回它所生成结果的对象
   CallableStatement cs;
   try
   {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); //管理一组 JDBC 驱动程序的基本服务并向 DriverManager 注册给定驱动程序
    myConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hr", "hr"); // 试图建立到给定数据库 URL 的连接
    //myConnection.setAutoCommit(false); //将此连接的自动提交模式设置为给定状态
    cs = myConnection.prepareCall("{call sencond(?)}");

                       cs.registerOutParameter(1, Types.VARCHAR);
                        //cs.registerOutParameter(2, Types.VARCHAR);

                        cs.execute();

                        String retValue = cs.getString(1);
                         System.out.println(retValue);

      }
   catch(SQLException e)
   {
    System.out.println("Error code = " + e.getErrorCode());
    System.out.println("Error message = " + e.getMessage());
    System.out.println("Error state = " + e.getSQLState());
   }
   finally
   {
    try
    {
     if (myStatement != null)
     {
      myStatement.close(); //立即释放此 Statement 对象的数据库和 JDBC 资源，而不是等待该对象自动关闭时发生此操作
     }
     if (myConnection != null)
     {
      myConnection.close(); //立即释放此 Connection 对象的数据库和 JDBC 资源，而不是等待它们被自动释放
     }
    }
    catch(SQLException e)
    {
     e.printStackTrace();
    }
   }
}
}

核心部分：

CallableStatement cs;
cs = myConnection.prepareCall("{call sencond(?,?)}");

                       cs.registerOutParameter(1, Types.VARCHAR); //out
                        cs.setString(2, "a string");      //in

                        cs.execute();   //run

                        String retValue = cs.getString(1);
                         System.out.println(retValue);

过程如下：

create or replace procedure

sencond(name out varchar2,search in varchar2)

as

begin

select name from emp where name=search;

end;