你的过程里面加上异常处理,捕获后继续执行下一个创建。
加上红色部分。
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;
谢谢给位,但是你们试试就知道了,如果违反顺序,好像执行不成功的。
我用的是下列语句,各位看是否正确:
---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;