Oracle compilation of stored procedures with array parameters

[SQL] www.2cto.com -- function: This function uses a combination of stored procedures and types to Perform Batch operations, saving system overhead and improving efficiency. -- CREATE Type bodies create or replace type TYPE_ARRAY as object (id number (10), REMARK VARCHAR2 (10 )) -- CREATE 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 (SELEC T 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 PROC_ARRAY_PARAM; -- create or replace package PKG_PARAM as type ARRAY_PARAMS is table of VARCHAR2 (20) index by BINARY_INTEGER; -- first define the PACKAGE, which IS equivalent to an array PROCEDURE PROC_PARAM (params in ARRAY_PARAMS ); END PKG_PARAM; -- create or re the package Place package body PKG_PARAM as procedure PROC_PARAM (params in ARRAY_PARAMS) as I number: = 1; -- this can be left blank with 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 call stored procedure: www.2cto.com [java] package com. test. oracle; import java. SQL. connection; imp Ort java. SQL. driverManager; import java. SQL. preparedStatement;/** call the Stored Procedure */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; // because the table has an index 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.exe cuteUpdate ();} catch (Exception e) {e. printStackTrace ();}}}