数组　作为oracle 存储过程参数的　实践

今天做了一个关于短消息批量删除的功能，　刚开始准备在后台用循环来逐条删除，后来因为其它功能模块也会用到大批量删除数据。　所以就决定用　数组作为存储过程的参数进行传值，在存储过程中批量删除。说实话这个选择有点得不偿失，本来以为上午就能搞定，谁知道从摸索到实现，居然发了大半天时间。

rem 删除选中短信息

CREATE OR REPLACE PACKAGE MSGPAG 
AS 
TYPE MSG_ARRAY IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;--先定义包，这个就相当于一个数组
PROCEDURE DELMSG(M_ARRAY IN MSG_ARRAY);
END MSGPAG;
/
CREATE OR REPLACE PACKAGE BODY MSGPAG 
AS 
PROCEDURE DELMSG(M_ARRAY IN MSG_ARRAY) 
AS 
I NUMBER:=1; --这个可以不写
BEGIN 
   SAVEPOINT SP1;
   FOR  I IN 1..M_ARRAY.COUNT LOOP 
         DELETE FROM T_MESSAGE WHERE FID = TO_NUMBER(M_ARRAY(I));
   END LOOP;
COMMIT;
EXCEPTION 
WHEN OTHERS THEN 
ROLLBACK TO SAVEPOINT SP1;
END DELMSG;
END MSGPAG;

短短的这些，着实让我走了不少弯路。　最后有一个小时是花在TO_NUMBER(M_ARRAY(I))上的，我想把TO_NUMBER(M_ARRAY(I))赋给一个NUMBER　型变量，但就是执行不到　DELETE 句处，呵呵，　最后才明白是怎么回事。

后台的调用执行语句就这么多：

connection.Open();
Oracle.DataAccess.Client.OracleCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procedureName;
Oracle.DataAccess.Client.OracleParameter parameter = new Oracle.DataAccess.Client.OracleParameter(@"M_ARRAY", Oracle.DataAccess.Client.OracleDbType.Varchar2);// @"M_ARRAY"　是存储过程的IN 参数
parameter.Direction = ParameterDirection.Input;
parameter.Value = charArray;
parameter.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
 cmd.Parameters.Add(parameter);
cmd.ExecuteNonQuery();

JAVA客户端传递对象数组到Oracle存储过程做大数据量插入

最近在项目中用到了JAVA客户端传递对象数组到Oracle存储过程做大数据量插入,比如10万级别.
下面做一个插入10万条记录的示例步骤,,为了容易理解,表的结果很简单.
1,假设表结构如下:

[java] view plaincopy

2,在数据库建立一个type,对应JAVA端要传入的对象结构:

3,为了数组传输,建立一个数组类型的type:
CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_TAB AS TABLE OF BUT_UKBNOV_CTC_ORDER_REC
4,建立存储过程做插入工作:

[java] view plaincopy

5,建立JAVA端java bean对象,(为节省版面,下面的get set方法省略,)

在JAVA端访问存储过程插入数据,需要做JAVA数据类型和存储过程类型type的映射,下面的StructDescriptor是mapping Oracle端AS OBJECT类型,
tabDesc 是mapping Oracle端数组 AS TABLE OF类型的.

[java] view plaincopy

6,如果每次调用都需要做Java bean的到Oracle的"AS OBJECT"类型的mapping,则很繁琐,可以然Java bean实现oracle.sql.ORAData,这样就不用在调用时候在做mapping了.
java bean对象如下,为节省版面get set方法省略.

[java] view plaincopy

调用的时候不需要再做Java bean 到Oracle "AS OBJECT"数据类型的mapping,只需要做数组类型的mapping,如下:

上面的示例在插入10万条记录只用了5秒(当然也和这里的表结构字段少有关系).

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

Oracle编写带数组参数的存储过程 [sql] --功能：采用存储过程、type组合来实现批量操作，以节省系统开销，提高效率。 --创建 Type bodies CREATE OR REPLACE TYPE TYPE_ARRAY AS OBJECT （ ID NUMBER（10）， REMARK VARCHAR2（10） ） --创建 Types CREATE

　　Oracle编写带数组参数的存储过程
　　[sql]
　　--功能：采用存储过程、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调用存储过程： [java] package com.test.oracle; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; /** 调用存储过程 */ public class ProcTest { public sta

Java调用存储过程：　　[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（）；
　　}
　　}
　　}