谢谢你提供的连接 我还想请教下, 下面这句是如果存在则执行更新,那我如果不执行更新,直接跳过是怎么写的when matched then update set a.redefine_severity=b.redefine_severity
如果数据库版本为10g或更高,可以不写when matched部分。如果是9i,没办法..
9i没什么好办法 可以用 insert into newtab a select * from oldtab where not exists(select 1 from a where col1=a.col1 and col2=a.col2 and col3=a.col3)效率上可能不如merge into
用not exists,这个效率比merge into 效率高
比如 declare v_count number; begin for cur in ( select * from b)loop begin select count(1) into v_count from a where col1=cur.col1; if v_count=0 then insert into a values(cur.col1,cur.col2); commit; end if; exception when others then rollback; end;end loop; end; 如果出现异常,会继续插入下一条数据。 我举的这个例子不会出现违反约束..
参考下,看看loop里面的begin end包含起来的部分的exception的处理,就是内部块的异常处理。create or replace procedure proc_move_timeout as var_record_im sms_confirm_im_today%ROWTYPE; cr_sms_confirm_im SYS_REFCURSOR;begin open cr_sms_confirm_im for select send_id, sms_id, req_seq, msg_id, task_id, temp_id, user_id, user_group_id, sender_num, mobile_no, contents, send_time, confirm_time, sms_status from sms_confirm_im_today where to_char(sysdate ,'yyyyMMddHH24MISS') >= to_char(sms_confirm_im_today.send_time + 60/60/60/24,'yyyyMMddHH24MISS') and (msg_id is null or msg_id='' or length(msg_id)<20) and sms_status = 4; loop fetch cr_sms_confirm_im into var_record_im; exit when cr_sms_confirm_im%NOTFOUND; begin insert into sms_timeout( send_id, sms_id, req_seq, msg_id, task_id, temp_id, user_id, user_group_id, sender_num, mobile_no, contents, send_time, confirm_time, sms_status, send_amount, source_table_flag) values( var_record_im.send_id, var_record_im.sms_id, var_record_im.req_seq, var_record_im.msg_id, var_record_im.task_id, var_record_im.temp_id, var_record_im.user_id, var_record_im.user_group_id, var_record_im.sender_num, var_record_im.mobile_no, var_record_im.contents, var_record_im.send_time, var_record_im.confirm_time, var_record_im.sms_status, 1, 2 ); exception when others then null; --如果插入出错,则什么也不做,或者插入日志表。 end; end loop; close cr_sms_confirm_im; commit;
exception when others then raise; end proc_move_timeout; /
alter table table1 add constraint constname primary key|unique(col1,col2..);
插入数据到新表可以使用merge into
参考下
可以用
insert into newtab a
select * from oldtab
where not exists(select 1 from a
where col1=a.col1 and col2=a.col2 and col3=a.col3)效率上可能不如merge into
用not exists,这个效率比merge into 效率高
declare
v_count number;
begin
for cur in ( select * from b)loop begin
select count(1) into v_count from a where col1=cur.col1;
if v_count=0 then
insert into a values(cur.col1,cur.col2);
commit;
end if;
exception
when others then
rollback;
end;end loop;
end;
如果出现异常,会继续插入下一条数据。
我举的这个例子不会出现违反约束..
参考下,看看loop里面的begin end包含起来的部分的exception的处理,就是内部块的异常处理。create or replace procedure proc_move_timeout
as
var_record_im sms_confirm_im_today%ROWTYPE;
cr_sms_confirm_im SYS_REFCURSOR;begin
open cr_sms_confirm_im for
select send_id,
sms_id,
req_seq,
msg_id,
task_id,
temp_id,
user_id,
user_group_id,
sender_num,
mobile_no,
contents,
send_time,
confirm_time,
sms_status
from sms_confirm_im_today
where to_char(sysdate ,'yyyyMMddHH24MISS') >= to_char(sms_confirm_im_today.send_time + 60/60/60/24,'yyyyMMddHH24MISS')
and (msg_id is null or msg_id='' or length(msg_id)<20)
and sms_status = 4; loop
fetch cr_sms_confirm_im into var_record_im;
exit when cr_sms_confirm_im%NOTFOUND; begin
insert into sms_timeout(
send_id,
sms_id,
req_seq,
msg_id,
task_id,
temp_id,
user_id,
user_group_id,
sender_num,
mobile_no,
contents,
send_time,
confirm_time,
sms_status,
send_amount,
source_table_flag)
values(
var_record_im.send_id,
var_record_im.sms_id,
var_record_im.req_seq,
var_record_im.msg_id,
var_record_im.task_id,
var_record_im.temp_id,
var_record_im.user_id,
var_record_im.user_group_id,
var_record_im.sender_num,
var_record_im.mobile_no,
var_record_im.contents,
var_record_im.send_time,
var_record_im.confirm_time,
var_record_im.sms_status,
1,
2
); exception when others then
null; --如果插入出错,则什么也不做,或者插入日志表。
end;
end loop; close cr_sms_confirm_im;
commit;
exception when others then
raise;
end proc_move_timeout;
/