CREATE OR REPLACE Procedure P_T_PS_Material_EXP
AS
cursor c1 is select explanner.T_PS_Material.PRODUCT_ITEM_CD from explanner.T_PS_Material group by explanner.T_PS_Material.PRODUCT_ITEM_CD;
ls_name explanner.T_PS_Material.PRODUCT_ITEM_CD%TYPE;
ls_sql varchar2(1000);
begin
ls_sql:='create OR REPLACE view V_PS_Material as select explanner.T_PS_Material.item_cd ';
open c1;
fetch c1 into ls_name;
loop
EXIT WHEN c1%NOTFOUND;
ls_sql:=ls_sql||',sum(DECODE(explanner.T_PS_Material.PRODUCT_ITEM_CD,'''||ls_name||''',explanner.T_PS_Material.qty,0)) '||ls_name;
fetch c1 into ls_name;
end loop;
ls_sql:=ls_sql||' from explanner.T_PS_Material GROUP BY explanner.T_PS_Material.item_cd ';
execute immediate ls_sql;
end;
AS
cursor c1 is select explanner.T_PS_Material.PRODUCT_ITEM_CD from explanner.T_PS_Material group by explanner.T_PS_Material.PRODUCT_ITEM_CD;
ls_name explanner.T_PS_Material.PRODUCT_ITEM_CD%TYPE;
ls_sql varchar2(1000);
begin
ls_sql:='create OR REPLACE view V_PS_Material as select explanner.T_PS_Material.item_cd ';
open c1;
fetch c1 into ls_name;
loop
EXIT WHEN c1%NOTFOUND;
ls_sql:=ls_sql||',sum(DECODE(explanner.T_PS_Material.PRODUCT_ITEM_CD,'''||ls_name||''',explanner.T_PS_Material.qty,0)) '||ls_name;
fetch c1 into ls_name;
end loop;
ls_sql:=ls_sql||' from explanner.T_PS_Material GROUP BY explanner.T_PS_Material.item_cd ';
execute immediate ls_sql;
end;
根据你的修改的地方我一开始就试过了!
显示:
ERROR 位于第 1 行:
ORA-00923: 未找到预期 FROM 关键字
ORA-06512: 在"P_T_PS_MATERIAL_EXP", line 16
ORA-06512: 在line 1我发现是起别名时出现的问题 !
请问还有和好的建议!