解决方案 »
- oracle+hibernate4.0release连接数据库
- 求助!ORALCE回滚脚本如何写
- 关于webservice发布与oracle9i冲突的问题
- 急:在Oracle下的SQL函数replace可以支持long型(<2G字符)吗?
- oracle过程返回数据集报错问题,急!
- 一个Sql语句的疑惑,希望得到你们的帮助!
- oracle物理恢复
- 求在java中浮点型数据与oracle9i中数据类型的对应关系?
- 在库有几个表的数据无法读取,都返回空记录
- 插入字符串时出现错误:oracle ora-01704 (字符串过长),如何解决?
- 下面有五个表,如何进行要求的删和改操作?
- oracle启动错误
create or replace procedure test1 is
l_count number := 0;
begin
for c1 in (select mobile, col1,...sysdate inputtime, source from tb2) loop
select count(*) into l_count from tgp_funds where fnd_id = c1.fnd_id;
if l_count > 0 then
update tgp_funds tb1
set tb1.col1 = nvl(c1.col1, tb1.col1),
tb1.col2 = nvl(c1.col2, tb1.col2),
.. . inputtime = sysdate,
source = source || ',' || c1.source
where tb1.fnd_id = c1.fnd_id;
else
insert into tgp_funds values c1;
end if;
end loop;
commit;
exception
when others then
dbms_output.put_line('Error happened!!');
end test1;
create or replace procedure test1 is
l_count number := 0;
begin
for c1 in (select mobile, col1,...sysdate inputtime, source from tb2) loop
select count(*) into l_count from tb1 where mobile = c1.mobile;
if l_count > 0 then
update tb1
set tb1.col1 = nvl(c1.col1, tb1.col1),
tb1.col2 = nvl(c1.col2, tb1.col2),
.. . inputtime = sysdate,
source = source || ',' || c1.source
where tb1.mobile = c1.mobile;
else
insert into tb1 values c1;
end if;
end loop;
commit;
exception
when others then
dbms_output.put_line('Error happened!!');
end test1;
l_count number := 0;
begin
merge into tb1
using tb2
on tb1.mobile = tb2.mobile
when matched then
update set
tb1.col1 = nvl(tb2.col1, tb1.col1),
tb1.col2 = nvl(tb2.col2, tb1.col2),
.. . inputtime = sysdate,
source = source || ',' || tb2.source
where tb1.mobile = tb2.mobile
;
when not matched then
insert (
mobile,
col1,
col2,
.
.
.
inputtime,
source
)
values(
mobile,
col1,
col2,
.
.
.
sysdate,
source
)
;
commit;
end;
l_count number := 0;
begin
merge into tb1
using tb2
on tb1.mobile = tb2.mobile
when matched then
update set
tb1.col1 = nvl(tb2.col1, tb1.col1),
tb1.col2 = nvl(tb2.col2, tb1.col2),
.. . inputtime = sysdate,
source = source || ',' || tb2.source
where tb1.mobile = tb2.mobile
;
when not matched then
insert (
mobile,
col1,
col2,
.
.
.
inputtime,
source
)
values(
mobile,
col1,
col2,
.
.
.
sysdate,
source
)
;
commit;
end;先谢了!