比如: 这个存储过程, for i in 1..3 loop begin insert into DATA_COMPARE_ODS_TEMP select t.name,t.'para0'||i from USER t; end; end loop; commit; 我想得到这么个效果,但是上面的这个肯定是得不到的. 所以想请教下大家有没有什么好的办法.
create or replace procedure text isflag number; begin EXECUTE IMMEDIATE 'CREATE TABLE USER_TEMP SELECT T.*,ROWNUM RN FROM USER T' ;SELECT MAX(RN) INTO FLAG FROM DATA_COMPARE_ODS_TEMP; For i in 1..FLAG loop insert into DATA_COMPARE_ODS_TEMP select t.name, t.'para0'||i from USER_TEMPt WHERE RN = FLAG; commit; end loop;end
create or replace procedure text isflag number; begin EXECUTE IMMEDIATE 'CREATE TABLE USER_TEMP SELECT T.*,ROWNUM RN FROM USER T' ;SELECT MAX(RN) INTO FLAG FROM DATA_COMPARE_ODS_TEMP; For i in 1..FLAG loop insert into DATA_COMPARE_ODS_TEMP select t.name, rn from USER_TEMPt WHERE RN = FLAG; commit; end loop;end
大哥,你确定你的这个SQL没问题吗?大致看了下,就算是正确的话得出的结果也不是我想要的啊。
solution 1: insert into DATA_COMPARE_ODS_TEMP select t.name, t.para01 from user t
union all
select t.name, t.para02 from user t
union all
select t.name, t.para03 from user t ;
solution 2:declare v_sql varchar2(32767); begin for i in 1..3 loop v_sql := 'insert into DATA_COMPARE_ODS_TEMP ' || ' select t.name, t.para0' || i ' || ' from user t ' ; execute immediate v_sql; end loop; commit; end;
这个存储过程,
for i in 1..3 loop
begin
insert into DATA_COMPARE_ODS_TEMP
select t.name,t.'para0'||i
from USER t;
end;
end loop;
commit;
我想得到这么个效果,但是上面的这个肯定是得不到的.
所以想请教下大家有没有什么好的办法.
isflag number;
begin
EXECUTE IMMEDIATE 'CREATE TABLE USER_TEMP
SELECT T.*,ROWNUM RN FROM USER T' ;SELECT MAX(RN) INTO FLAG FROM DATA_COMPARE_ODS_TEMP;
For i in 1..FLAG loop
insert into DATA_COMPARE_ODS_TEMP
select t.name,
t.'para0'||i
from USER_TEMPt
WHERE RN = FLAG;
commit;
end loop;end
NAME CODE
U01 1
U01 2
U01 3
U01 4
U01 5
U02 1
U02 2
U02 3
U02 4
U02 5
isflag number;
begin
EXECUTE IMMEDIATE 'CREATE TABLE USER_TEMP
SELECT T.*,ROWNUM RN FROM USER T' ;SELECT MAX(RN) INTO FLAG FROM DATA_COMPARE_ODS_TEMP;
For i in 1..FLAG loop
insert into DATA_COMPARE_ODS_TEMP
select t.name,
rn
from USER_TEMPt
WHERE RN = FLAG;
commit;
end loop;end
insert into DATA_COMPARE_ODS_TEMP
select t.name, t.para01
from user t
union all
select t.name, t.para02
from user t
union all
select t.name, t.para03
from user t
;
v_sql varchar2(32767);
begin
for i in 1..3 loop
v_sql := 'insert into DATA_COMPARE_ODS_TEMP ' ||
' select t.name, t.para0' || i ' ||
' from user t '
;
execute immediate v_sql;
end loop;
commit;
end;