Oracle编写带数组参数的存储过程
[sql] www.zzzyk.com
--功能：采用存储过程、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调用存储过程： www.zzzyk.com
[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();
}
}
}
