有一个sql server 的存储过程,转到oracle里怎么也运行不了,请求帮助。CREATE PROCEDURE up_data AS
if exists(select 1 from TABLEA)
begin
truncate table TABLEB;
insert into TABLEB(字段1,字段2) select 字段1,字段2 from TABLEA;
end
else
return
GO
if exists(select 1 from TABLEA)
begin
truncate table TABLEB;
insert into TABLEB(字段1,字段2) select 字段1,字段2 from TABLEA;
end
else
return
GO
execute immediate 'truncate table TABLEB';
v_i number;
begin
select count(*) into v_i from TABLEA;
if v_i>0 then
delete from TABLEB;
insert into TABLEB(字段1,字段2) select 字段1,字段2 from TABLEA;
end if;end;
/
v_exist number :=0;
begin
select count(*)
into v_i from TABLEA;
if v_i>0 then
execute immediate 'truncate table TABLEB' ;
else null;
end if;
insert into TABLEB(字段1,字段2) select 字段1,字段2 from TABLEA; end; 思路是:
一:判断表是否存在数据,如果存在,清空其中的数据,不存在,不做任何处理
二:对表中插入新数据
备注:用execute immediate 'truncate table TABLEB' 比 delete from TABLEB会快很多,
特别是在数据量比较大的时候,其差距非常的明显
v_i number;
begin
select count(*) into v_i from TABLEA;
if v_i>0 then
execute immediate 'truncate table TABLEB' ;
end if;
insert into TABLEB(字段1,字段2) select 字段1,字段2 from TABLEA; end;
/