存储过程，使用二维数组参数【简单整理】

业务场景：能过API获取某系统数据，将数据同步到别的数据库表格中。已存在的数据更新操作，不存在的新增。每天都要同步，且数据量较大。使用 JAVA + JDBC

分析：若单条数据进行处理，需循环、频繁的与数据库进行交互。故使用存储过程，按自定义打包数据，比如1000条调用一次存储过程(下面创建type时会指定，java代码调用存储过程时1000/次)。

Table：

假设有表 tab1 ，字段 id 、name 、 num，类型varchar2(50)

sql：

1.创建Type（如需在java中调用存储过程，type需独立定义，在package中定义的会提示错误、无法找到）

--** 创建对象类型，属性名称自定义，属性个数与需同步的参数保持一致即可

create or replace type type_xx_obj as object
(
id varchar2(50),
name varchar2(50),

num varchar2(50)

);

-- ** 创建type_xx_obj对象的二维数组，最大容量1000

create or replace type type_xx_array as VARRAY(1000) of type_agilesyncdata_obj;

2. 新建package及body （procedure ）

create or replace package pkg_agilesyncdata is
procedure pro_syncdata(v_array_2din in type_xx_array, v_out out varchar2);
end pkg_agilesyncdata;

create or replace package body pkg_syncdata is

flag integer;
procedure pro_agilesyncdata(v_array_2din in type_xx_array, v_out out varchar2) is
begin

for i in v_array_2din.first .. v_array_2din.last loop
begin

--检查数据是否存在
select count(1) into flag from tab1 where id = v_array_2din(i).id;
if(flag =1) then
update tab1 t set t.name = v_array_2din(i).name ,t.num=v_array_2din(i).num where t.id=v_array_2din(i).id ;
else
insert into ....<omit>.....;
end if;
exception

--对遍历过程中的每次异常进行信息收集
when others then
v_out := v_out || ' id[' || v_array_2din(i).id || '],' || SQLERRM || ';' ;
end;
end loop;

commit;
exception
when others then
v_out :='异常 : '||SQLERRM;

--return(<Result>);

end;

--begin
-- Initialization
--<Statement>;
end pkg_syncdata;

3. procedure测试（如何定义并初始化测试时需要的二维参数）

存储过程，使用二维数组参数【简单整理】

4. java代码中调用二维数组入参的存储过程（个人使用的ojdbc6.jar）

用到的类声明：

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

核心代码示意：

conn=DBAccess.getConnectionForWindchill();
callStmt = (OracleCallableStatement) conn.prepareCall("{call pkg_syncdata.pro_syncdata(?,?)}");
callStmt.registerOutParameter(2, OracleTypes.VARCHAR);
StructDescriptor structdesc = new StructDescriptor("数据库用户名.TYPE_xx_OBJ", conn);

//假设已有数据源list<bean> 且符合设置的1000条要求（若不符合，自行拆分或将1000调大），此处根据个人实际情况进行数据填充，明白填充机制即可

STRUCT[] structs = new STRUCT[list.size()];

Object[] obj = new Object[3]; // 与参数个数匹配

// 遍历 list 并将单条数据放到obj中，将obj添加到structs

for(int i=0;i<length;i++){

bean = list.get(i);

// 注意顺序与定义的type obj对象对应

obj[0] = bean.getid();

obj[1] = bean.getname();

obj[2] = bean.getnum();

// 组装 structs

structs[i] = new STRUCT(structdesc, conn, obj);

}

ArrayDescriptor des=ArrayDescriptor.createDescriptor("数据库用户名.TYPE_xx_ARRAY",conn);
ARRAY array_to_pass = new ARRAY(des,conn,structs);
callStmt.setObject(1, array_to_pass);

callStmt.execute();
String rsStr = callStmt.getString(2);

if(rsStr!=null){
//TODO 处理返回值
}

存储过程，使用二维数组参数【简单整理】

相关推荐