Java中调用参数是数组的存储过程

 Java中调用参数是数组的存储过程

1. 存储过程以及类型定义如下：

--The array in oracle

CREATE OR REPLACE TYPE idArray AS TABLE OF VARCHAR2(20);

 

--package header

CREATE OR REPLACE PACKAGE Lib_Package AS
 

 PROCEDURE Book_Check_Procedure(ids IN idArray, exist OUT NUMBER);
 

END Lib_Package;

 

--package body

CREATE OR REPLACE PACKAGE BODY Lib_Package AS

 

PROCEDURE Book_Check_Procedure(
 ids IN idArray, 
 exist OUT NUMBER) AS
 v_Index BINARY_INTEGER;
BEGIN
 v_Index:= ids.FIRST;
 LOOP
  SELECT COUNT(*) INTO exist FROM Lib_Duplicate WHERE status='Lent'
    AND book_id=ids(v_Index);
  EXIT WHEN v_Index=ids.LAST OR exist>0;
  v_Index:= ids.NEXT(v_Index);
 END LOOP;
END Book_Check_Procedure;

END Lib_Package;

 

2.在Java中调用上面的存储过程

(1) 在Oracle中定义数组类型idArray
(2) 在java构造数组并转换成Oracle中定义的数组类型，调用存储过程

 

/**
  * 当要删除图书时，检查是否仍然有图书复本处于借出状态
  */
 public boolean checkBookStatus(String[] bookIds) throws DataAccessException {

  boolean flag = false;
  Connection conn = null;
  OracleCallableStatement cstmt = null;
  ArrayDescriptor desc = null;
  ARRAY bookIdArray = null;
  int count = 0;
  String sql = "{call LIB_PACKAGE.Book_Check_Procedure(?,?)}";

  DbDriverManager dbManager = DbDriverManager.getInstance();
  conn = dbManager.getConnection(Constants.DATABASE);

  try {
   cstmt = (OracleCallableStatement) conn.prepareCall(sql);

   //定义oracle中的数组类型
   desc = ArrayDescriptor.createDescriptor("IDARRAY", conn);
   bookIdArray = new ARRAY(desc, conn, bookIds);

   cstmt.setObject(1, bookIdArray, oracle.jdbc.OracleTypes.ARRAY);
   cstmt.registerOutParameter(2, Types.INTEGER);
   cstmt.execute();
   count = cstmt.getInt(2);

   log.info(this.getClass() + ".checkBookStatus: count = " + count);

   DbOperHelp.closeStatement(this.getClass(), cstmt);
   DbOperHelp.closeConnection(this.getClass(), conn);
  } catch (SQLException e) {

   log.error(this.getClass() + ".checkBookStatus-->SQLException: "
     + e.getMessage());
   DbOperHelp.closeStatement(this.getClass(), cstmt);
   DbOperHelp.closeConnection(this.getClass(), conn);
   throw new DataAccessException(
     "When check the books, there is a SQLException: "
       + e.getMessage(), e.getCause());
  }

  if (count > 0) {

   flag = true;
  }

  return flag;
 }

 

3.注意的情况

问题：

      存储过程的参数中含有VARRAY、TABLE类型，目的是为了传入一批数据，前端用java，但是java怎么设置这两种类型参数呢？？？

解决方法：

     在Oracle安装目录发现例子，安装目录下/ora92/jdbc/demo/demo/samples/jdbcoci下的PLSQLIndexTab。可能遇到的问题：连接为oci连接，thin连接不行，增加nls_charset12.jar包，不然会出现java.sql.SQLException: Non supported character set: oracle-character-set-852异常。