谢谢给位,但是你们试试就知道了,如果违反顺序,好像执行不成功的。 我用的是下列语句,各位看是否正确: ---7.个性化视图处理 cmdstr := 'select v.VIEW_NAME from all_views v where v.OWNER=upper(:1) and v.VIEW_NAME like ''VW_FB_%'' AND NOT EXISTS (SELECT 1 FROM user_views u where u.VIEW_NAME=v.VIEW_NAME)'; open Reg_cur for cmdstr using pUser; loop fetch Reg_cur into vartab; exit when Reg_cur% notfound; select v.text into varSql from all_views v where v.OWNER = upper(pUser) and v.VIEW_NAME = upper(vartab); varSql:=upper(varSql); if len(trim(varSql)) <> 0 then cmdstr := 'create or replace force view ' || vartab || ' as ' || trim(replace(varSql,'PROJECT_','PRJ_')); execute immediate cmdstr; end if; end loop; close Reg_cur;
你的过程里面加上异常处理,捕获后继续执行下一个创建。 加上红色部分。 cmdstr := 'select v.VIEW_NAME from all_views v where v.OWNER=upper(:1) and v.VIEW_NAME like ''VW_FB_%'' AND NOT EXISTS (SELECT 1 FROM user_views u where u.VIEW_NAME=v.VIEW_NAME)'; open Reg_cur for cmdstr using pUser; loop fetch Reg_cur into vartab; exit when Reg_cur% notfound; select v.text into varSql from all_views v where v.OWNER = upper(pUser) and v.VIEW_NAME = upper(vartab); varSql:=upper(varSql); if len(trim(varSql)) <> 0 then cmdstr := 'create or replace force view ' || vartab || ' as ' || trim(replace(varSql,'PROJECT_','PRJ_')); begin execute immediate cmdstr; exception when others then null; end; end if; end loop; close Reg_cur;
怎么找出视图依赖于哪些其它对象?例如create view mydual select * from dual;从哪可以找出mydual依赖于dual?有没有相关的字典表可以查?
我用的是下列语句,各位看是否正确:
---7.个性化视图处理
cmdstr := 'select v.VIEW_NAME from all_views v
where v.OWNER=upper(:1) and v.VIEW_NAME like ''VW_FB_%''
AND NOT EXISTS (SELECT 1 FROM user_views u where u.VIEW_NAME=v.VIEW_NAME)';
open Reg_cur for cmdstr
using pUser;
loop
fetch Reg_cur
into vartab;
exit when Reg_cur% notfound;
select v.text
into varSql
from all_views v
where v.OWNER = upper(pUser)
and v.VIEW_NAME = upper(vartab);
varSql:=upper(varSql);
if len(trim(varSql)) <> 0 then
cmdstr := 'create or replace force view ' || vartab || ' as ' ||
trim(replace(varSql,'PROJECT_','PRJ_'));
execute immediate cmdstr;
end if;
end loop;
close Reg_cur;
例如A依赖B,B依赖C.
创建语句顺序A,B,C
这样第一次只有C成功;
第二次B成功,C被replace;
第三次A成功,B,C被replace。
加上红色部分。
cmdstr := 'select v.VIEW_NAME from all_views v
where v.OWNER=upper(:1) and v.VIEW_NAME like ''VW_FB_%''
AND NOT EXISTS (SELECT 1 FROM user_views u where u.VIEW_NAME=v.VIEW_NAME)';
open Reg_cur for cmdstr
using pUser;
loop
fetch Reg_cur
into vartab;
exit when Reg_cur% notfound;
select v.text
into varSql
from all_views v
where v.OWNER = upper(pUser)
and v.VIEW_NAME = upper(vartab);
varSql:=upper(varSql);
if len(trim(varSql)) <> 0 then
cmdstr := 'create or replace force view ' || vartab || ' as ' ||
trim(replace(varSql,'PROJECT_','PRJ_'));
begin execute immediate cmdstr;
exception
when others then null;
end;
end if;
end loop;
close Reg_cur;