oracle 动态view,动态创建视图脚本

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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29802484/viewspace-1850713/，如需转载，请注明出处，否则将追究法律责任。