動態建立檢視指令碼

create or replace procedure p_di_crtvw
is 
lvc_sql varchar2(500);
cursor cur is 
SELECT DISTINCT upper(viewsql) viewsql FROM 
(SELECT 'create or replace force view di_'||DMLX_DM ||' as select * from J1_DI.DI_GY_1JDM where dmlx_dm = ''' || DMLX_DM || '''' viewsql
  FROM J1_DI.DI_GY_1JDM
 WHERE DMLX_DM not in ('DM_SWJG','DM_GY_HY','DM_GY_ZSPM','DM_DJ_DJZCLX','DM_GY_SWRY','DM_GY_JDXZ','DM_GY_YSKM','DM_DJ_BGXM','DM_FP_FPZL','DM_GY_NSRZT','DM_GY_ZSXM','DM_FP_FP','DM_GY_GJHDQ','DM_GY_GK','DM_GY_JLDW','DM_GY_SWSX','DM_GY_XZQH','DM_GY_YHYYWD','DM_GY_DZBZDSZL') and DMLX_DM not like 'DM_XG%');
begin
for mycur in cur loop
begin
lvc_sql:=mycur.viewsql;
--dbms_output.put_line(lvc_sql);
execute immediate lvc_sql;
    exception
         when others then
           dbms_output.put_line('建立檢視失敗.'||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')||', sqlcode:'||to_char(sqlcode)||sqlerrm);
  end;
  end loop;
end;