一、创建oracle 需要保存的数据类型type和存储过程produce create TYPE "AL01TYPE"
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;
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;
public class OracleUtil {
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内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!
--结束END--
本文标题: oracle通过存储过程上传list保存功能
本文链接: https://www.lsjlt.com/news/125694.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-03-01
2024-03-01
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
一口价域名售卖能注册吗?域名是网站的标识,简短且易于记忆,为在线用户提供了访问我们网站的简单路径。一口价是在域名交易中一种常见的模式,而这种通常是针对已经被注册的域名转售给其他人的一种方式。
一口价域名买卖的过程通常包括以下几个步骤:
1.寻找:买家需要在域名售卖平台上找到心仪的一口价域名。平台通常会为每个可售的域名提供详细的描述,包括价格、年龄、流
443px" 443px) https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294.jpg https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294-768x413.jpg 域名售卖 域名一口价售卖 游戏音频 赋值/切片 框架优势 评估指南 项目规模 安全指南 Osprey 游戏分析 游戏调试 游戏图形 游戏物理 开源库 魔方破解 游戏安全 反作弊 安全最佳实践 游戏逻辑
0