一、创建oracle 需要保存的数据类型type和存储过程produce
create type "al01type" as object
(
-- 描述 : 档案批量转出
-- 作者 : dt
-- 时间 : 2021-05-10
-- 版本 ：dev-1.0.1
aac003 nvarchar2(100),
aac002 nvarchar2(50),
aat001 nvarchar2(50),
aat002 nvarchar2(50),
aat013 nvarchar2(20),
aae011 nvarchar2(20),
aae036 nvarchar2(20),
aah002 nvarchar2(100)
);
create type al01typelist as table of al01type;
-- auto-generated definition
create procedure sp_hfszhda_douploadal01(list in al01typelist,
po_message out varchar) is
--描述：档案转出excel上传
--作者：dt
--时间：2021-05-10
--版本：dev-1.0.1
v_object al01type;
le_error exception;
p_renum number(20);
v_aah002 varchar(100);
ls_count number;
ls_aaf025 varchar(50);
begin
p_renum := 0; --初始化
for i in 1 .. list.count loop
p_renum := 1 + p_renum;
v_object := list(i);
select replace(utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_object.aah002)),unistr('
create type "al01type" as object
(
-- 描述 : 档案批量转出
-- 作者 : dt
-- 时间 : 2021-05-10
-- 版本 ：dev-1.0.1
aac003 nvarchar2(100),
aac002 nvarchar2(50),
aat001 nvarchar2(50),
aat002 nvarchar2(50),
aat013 nvarchar2(20),
aae011 nvarchar2(20),
aae036 nvarchar2(20),
aah002 nvarchar2(100)
);
create type al01typelist as table of al01type;
-- auto-generated definition
create procedure sp_hfszhda_douploadal01(list in al01typelist,
po_message out varchar) is
--描述：档案转出excel上传
--作者：dt
--时间：2021-05-10
--版本：dev-1.0.1
v_object al01type;
le_error exception;
p_renum number(20);
v_aah002 varchar(100);
ls_count number;
ls_aaf025 varchar(50);
begin
p_renum := 0; --初始化
for i in 1 .. list.count loop
p_renum := 1 + p_renum;
v_object := list(i);
select replace(utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_object.aah002)),unistr('\0000')) into v_aah002 from dual;
begin
select count(0) into ls_count from az03 where aat001 = v_object.aat001 and aat012 = '1';
if ls_count=0 then
po_message := '号：'||v_object.aat001||' 状态异常请核对后再上传！';
raise le_error;
end if;
select count(0) into ls_count from al01 where aaj022='1' and aat001=v_object.aat001 and aah002=v_aah002;
if ls_count =0 then
-- 开始插入信息
select sq_aaf025.nextval into ls_aaf025 from dual;
insert into al01(
aaf025,
aat012,
aat001,
aac003,
aac002,
aat002,
aat013,
aaj022,
aaj026,
aae011,
aae036,
aah002)values(
ls_aaf025,
'1',
v_object.aat001,
v_object.aac003 ,
v_object.aac002 ,
v_object.aat002 ,
v_object.aat013,
'1',
'excel上传数据',
v_object.aae011,
v_object.aae036,
v_aah002
);
end if;
if p_renum >1000 then
commit;
p_renum:=0;
end if;
po_message :='ok';
exception
when le_error then
null;
when others then
rollback;
po_message := '上传失败' || sqlcode || sqlerrm;
end;
end loop;
commit;
end sp_hfszhda_douploadal01;00')) into v_aah002 from dual;
begin
select count(0) into ls_count from az03 where aat001 = v_object.aat001 and aat012 = '1';
if ls_count=0 then
po_message := '号：'||v_object.aat001||' 状态异常请核对后再上传！';
raise le_error;
end if;
select count(0) into ls_count from al01 where aaj022='1' and aat001=v_object.aat001 and aah002=v_aah002;
if ls_count =0 then
-- 开始插入信息
select sq_aaf025.nextval into ls_aaf025 from dual;
insert into al01(
aaf025,
aat012,
aat001,
aac003,
aac002,
aat002,
aat013,
aaj022,
aaj026,
aae011,
aae036,
aah002)values(
ls_aaf025,
'1',
v_object.aat001,
v_object.aac003 ,
v_object.aac002 ,
v_object.aat002 ,
v_object.aat013,
'1',
'excel上传数据',
v_object.aae011,
v_object.aae036,
v_aah002
);
end if;
if p_renum >1000 then
commit;
p_renum:=0;
end if;
po_message :='ok';
exception
when le_error then
null;
when others then
rollback;
po_message := '上传失败' || sqlcode || sqlerrm;
end;
end loop;
commit;
end sp_hfszhda_douploadal01;
二、通过过程上传list
package com.cominfo.elecfile.utils;
import oracle.jdbc.oracleconnection;
import oracle.sql.array;
import oracle.sql.arraydescriptor;
import oracle.sql.struct;
import oracle.sql.structdescriptor;
import org.springframework.jdbc.support.nativejdbc.c3p0nativejdbcextractor;
import java.sql.connection;
import java.util.list;
/**
* 描述
*
* @auther: dt
* @date: 2021/5/10 0027 09:00
*/
public class oracleutil {
/**
* 根据数据库中你的type将list组装成array
* @param con
* @param oracleobj
* @param oraclelist
* @param objlist
* @return
* @throws exception
*/
public static array getarray(connection con, string oracleobj, string oraclelist, list<object[]> objlist) throws exception {
array array=null;
c3p0nativejdbcextractor cp30nativejdbcextractor = new c3p0nativejdbcextractor();
oracleconnection connection = (oracleconnection) cp30nativejdbcextractor.getnativeconnection(con);
if (objlist != null && objlist.size() > 0) {
structdescriptor structdesc = new structdescriptor(oracleobj, connection);
struct[] structs = new struct[objlist.size()];
for (int i = 0; i < objlist.size(); i++) {
object[] result= (object[]) objlist.get(i);
structs[i] = new struct(structdesc, connection, result);
}
arraydescriptor desc = arraydescriptor.createdescriptor(oraclelist,connection);
array = new array(desc, connection, structs);
}
return array;
}
}
list<object[]> arrlist = new arraylist<>();
//解析数据datamap
for (map<string, string> datamap : datamaps) {
//创建保存对象
object[] objects =new object[]{
datamap.get("aac003"),
datamap.get("aac002"),
datamap.get("aat001").trim(),
datamap.get("aat002"),
datamap.get("aat013"),
'admin',
dateutil.getcurrenttimestr(),
'ec-20210510-wcdedgk2091',
};
arrlist.add(objects);
}
//开始调用过程
long starttime=system.currenttimemillis();
connection connection = null;
callablestatement sqlres = null;
string sql = "call sp_hfszhda_douploadal01(?,?)";
string msg = "";
try {
connection = datasource.getconnection();
array paramarr = oracleutil.getarray(connection,"al01type","al01typelist",arrlist);
sqlres = connection.preparecall(sql);
sqlres.setarray(1, paramarr);
sqlres.registeroutparameter(2, types.varchar);
sqlres.execute();
msg = sqlres.getstring(2);
long endtime=system.currenttimemillis()-starttime;
system.out.println("上传后获取的返回参数为："+msg+"||耗时："+endtime/1000+"秒");
} catch (sqlexception e) {
e.printstacktrace();
} catch (exception e) {
e.printstacktrace();
}finally {
try {
if (sqlres != null) {
sqlres.close();
}
if (connection != null) {
connection.close();
}
} catch (sqlexception e) {
e.printstacktrace();
}
if (!"ok".equals(msg)){
throw new businessexception("上传失败！"+msg);
}
}
到此这篇关于oracle通过存储过程上传list保存功能的文章就介绍到这了,更多相关oracle保存list内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！