Oracle编写带数组参数的存储过程

--功能：采用存储过程、type组合来实现批量操作，以节省系统开销，提高效率。              --创建 Type bodies  CREATE OR REPLACE TYPE TYPE_ARRAY AS OBJECT  (    ID     NUMBER(10),    REMARK VARCHAR2(10)  )  --创建 Types  CREATE OR REPLACE TYPE TYPE_ARRAY_TBL AS TABLE OF TYPE_ARRAY  --创建表  CREATE TABLE T_TEMP(ID NUMBER(10) NOT NULL, REMARK NUMBER(10))  --创建存储过程   CREATE OR REPLACE PROCEDURE PROC_ARRAY_PARAM(TYPE_OBJECT IN TYPE_ARRAY_TBL) IS      BEGIN   INSERT INTO T_TEMP     (ID, REMARK)     SELECT ID, REMARK       FROM THE (SELECT CAST(TYPE_OBJECT AS TYPE_ARRAY_TBL) FROM DUAL);        FOR I IN 1 .. TYPE_OBJECT.COUNT LOOP     DELETE FROM T_TEMP WHERE ID = TO_NUMBER(TYPE_OBJECT(I));   END LOOP;   COMMIT; END;      END PROC_ARRAY_PARAM;--创建包  CREATE OR REPLACE PACKAGE PKG_PARAM AS    TYPE ARRAY_PARAMS IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; --先定义包，这个就相当于一个数组    PROCEDURE PROC_PARAM(PARAMS IN ARRAY_PARAMS);  END PKG_PARAM;  --创建包体  CREATE OR REPLACE PACKAGE BODY PKG_PARAM AS    PROCEDURE PROC_PARAM(PARAMS IN ARRAY_PARAMS) AS      I NUMBER := 1; --这个可以不写    BEGIN      SAVEPOINT SP1;      FOR I IN 1 .. PARAMS.COUNT LOOP        DELETE FROM T_TEMP WHERE ID = TO_NUMBER(PARAMS(I));      END LOOP;      COMMIT;    EXCEPTION      WHEN OTHERS THEN        ROLLBACK TO SAVEPOINT SP1;    END PROC_PARAM;  END PKG_PARAM;  

Java调用存储过程：

 package com.test.oracle;  import java.sql.Connection;  import java.sql.DriverManager;  import java.sql.PreparedStatement;  /** 调用存储过程 */ public class ProcTest {          public static void main(String[] args) {          try {              Class.forName("oracle.jdbc.driver.OracleDriver");              String url = "jdbc:oracle:thin:@192.168.1.1:1521:orcl";              Connection con = DriverManager.getConnection(url, "sys", "sys");              PreparedStatement pstmt = null;              String sql = "{call PROC_ARRAY_PARAM(?)}";              pstmt = con.prepareCall(sql);              Object[][] object1 = new Object[10][5];              int max = 3615142;// 由于表有索引              for (int i = 0; i < 10; i++) {                  object1[i][0] = ++max;                  object1[i][1] = 222;                  object1[i][2] = 333;                  object1[i][3] = 444;                  object1[i][4] = 555;              }              oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("TYPE_ARRAY_TBL", con);              oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, object1);              pstmt.setArray(1, array);              pstmt.executeUpdate();          } catch (Exception e) {              e.printStackTrace();          }      } }