首页新闻找找看学习计划

关于java调用存储过程问题,下边是我写的存储过程,总是编译出错,着急发版,求帮助

0
悬赏园豆:30 [待解决问题]

实现功能:导入excel,如果数据存在则更新,不存在测插入数据库,
问题:因数据量过多时,select count(1) from table;这个语句会执行很多次,效率太低。

在网上找到的方法使用存储过程解决:

/由于要导入多个excel,所以表名和列名都是动态的/
table_name /表名/
col_sql /列名/
condition_list /查询条件数组/
val_list in /值数组/
user_name /操作人/
today_time /操作时间/

先创建两个数组:
CREATE TYPE val_array AS TABLE OF VARCHAR2(50);
CREATE TYPE condition_array AS TABLE OF VARCHAR2(50);
如下是存储过程:
create or replace procedure insert_exists_device(table_name in VARCHAR2, col_sql in VARCHAR2, condition_list in condition_array, val_list in val_array, user_name in VARCHAR2, today_time in VARCHAR2)
is recordnum NUMBER;
BEGIN
for i in 1 .. condition_list.count loop
select count(1) into recordnum FROM table_name WHERE condition_list(i);
IF recordnum = 0 THEN
insert into table_name (id,col_sql,CREATE_PRSN,CREATE_TIME) values(sys_guid(),val_list(i),user_name,to_date(today_time,'yyyy-MM-dd hh24:mi:ss'));
ELSE
update table_name set (col_sql,MDFY_PRSON,MDFY_TIME) = (select val_list(i),user_name,to_date(today_time,'yyyy-MM-dd hh24:mi:ss') from dual) where condition_list(i);
END IF;
commit;
end loop;
END insert_exists_device;

菜鸟程序杜的主页 菜鸟程序杜 | 初学一级 | 园豆:65
提问于:2019-06-16 14:27
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册