informatica Pre sql for loop
今天遇到个问题 ,一段代码在pl/sql中可以正常执行,但放到 pre sql中 for loop 这段就没被执行,不知道 哪位遇到过类似的情况,请指点下:
declare
v_par varchar2(6)\;
v_par2 varchar2(9)\;
v_sql varchar2(50)\;
begin
begin v_sql := 'drop table PRC_WB_KPI_GQ_TMP'\;
execute immediate v_sql\;
Exception When Others Then Null\;End\;
execute immediate '
create table PRC_WB_KPI_GQ_TMP (ROOT_PROD_INST_ID VArchar2(30))' \;
FOR v_par IN
(
SELECT distinct substr(PROD_XXX_CODE,4,3) GQ FROM XXXXXXX )
LOOP
begin
v_par2:='par_'||v_par.GQ \;
execute immediate '
insert into PRC_WB_KPI_GQ_TMP
select distinct ROOT_PROD_INST_ID from XXXXXXX partition('||v_par2||')' \;
commit \;
end \;
insert into PRC_WB_KPI_GQ_TMP
values(v_par2)\;
commit\;
END LOOP\;
end \;
declare
v_par varchar2(6)\;
v_par2 varchar2(9)\;
v_sql varchar2(50)\;
begin
begin v_sql := 'drop table PRC_WB_KPI_GQ_TMP'\;
execute immediate v_sql\;
Exception When Others Then Null\;End\;
execute immediate '
create table PRC_WB_KPI_GQ_TMP (ROOT_PROD_INST_ID VArchar2(30))' \;
FOR v_par IN
(
SELECT distinct substr(PROD_XXX_CODE,4,3) GQ FROM XXXXXXX )
LOOP
begin
v_par2:='par_'||v_par.GQ \;
execute immediate '
insert into PRC_WB_KPI_GQ_TMP
select distinct ROOT_PROD_INST_ID from XXXXXXX partition('||v_par2||')' \;
commit \;
end \;
insert into PRC_WB_KPI_GQ_TMP
values(v_par2)\;
commit\;
END LOOP\;
end \;
作者: wyj243 发布时间: 2011-03-29
封个Procedure来调用吧!
作者: Hero--008 发布时间: 2011-03-29