触发器调用带有commit的存储过程

Question

大家好！
今天遇到一个问题，很烦解决不掉！
我谢了一个触发器：
CREATE OR REPLACE TRIGGER createmyoprteq
AFTER INSERT OR UPDATE OR DELETE ON QIXIANG.OPRT_EQ
BEGIN
        proc('oprtEq','oprt_psn_division','oprt_eq_model','oprt_eq_count','myoprteq');
END;在这个触发器中调用一个存储过程：
create or replace procedure proc(tabname in varchar2,
                                  col1 in varchar2,
                                  col2 in varchar2,
                                  col3 in varchar2,
                                  viewname in varchar2 default 'v_tmp')
as
  sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' ';
  c1 sys_refcursor;
  v1 varchar2(100);
begin
  open c1 for 'select distinct to_char('||col2||') from '||tabname;
  loop
    fetch c1 into v1;
    exit when c1%notfound;
    sqlstr:=sqlstr||'
  ,max(decode('||col2||','''||v1||''','||col3||'))"'||v1||'"';
  end loop;
  close c1;
  sqlstr:=sqlstr||' from '||tabname||' group by '||col1;
  execute immediate sqlstr;
end proc;这个存储过程是把我的一个表进行行列转换，生成一个视图！因为触发器里面不能有commit关键字，所有不能执行！
不知道有什么办法可以解决！从网上查到可以使用自治事务：我把存储过程改成如下：create or replace procedure proc(tabname in varchar2,
                                  col1 in varchar2,
                                  col2 in varchar2,
                                  col3 in varchar2,
                                  viewname in varchar2 default 'v_tmp')
as
  PRAGMA AUTONOMOUS_TRANSACTION; //这是我加的部分！
  sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' ';
  c1 sys_refcursor;
  v1 varchar2(100);begin
  open c1 for 'select distinct to_char('||col2||') from '||tabname;
  loop
    fetch c1 into v1;
    exit when c1%notfound;
    sqlstr:=sqlstr||'
  ,max(decode('||col2||','''||v1||''','||col3||'))"'||v1||'"';
  end loop;
  close c1;
  sqlstr:=sqlstr||' from '||tabname||' group by '||col1;
  execute immediate sqlstr;
  commit;
end proc;执行没有错误了！可是好像并不能重新生成我的视图！但是我手动执行存储过程可以生成我需要的视图。
求高手帮忙呀！

Answer 1

在这个procedure中sqlstr查询了触发器触发的表OPRT_EQ

Answer 2

CREATE OR REPLACE TRIGGER createmyoprteq 
AFTER INSERT OR UPDATE OR DELETE ON QIXIANG.OPRT_EQ 
BEGIN 
        proc('oprtEq','oprt_psn_division','oprt_eq_model','oprt_eq_count','myoprteq'); 
commit;
END; 
好高兴，我的过程派上用场了～

Answer 3

看错了
是trigger里加自治事务
CREATE OR REPLACE TRIGGER createmyoprteq 
AFTER INSERT OR UPDATE OR DELETE ON QIXIANG.OPRT_EQ 
declare
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN 
        proc('oprtEq','oprt_psn_division','oprt_eq_model','oprt_eq_count','myoprteq'); 
commit; 
END; 过程不要改

Answer 4

昨天直接用了，忘记谢谢了！
谢谢2楼！
你存储过程解决了我两天没有解决的问题！不太会写存储过程！
因为触发器里面不允许使用commit，所以不知道怎么调用这个存储过程！

Answer 5

你把存储过程里加的那句PRAGMA AUTONOMOUS_TRANSACTION;去掉
在触发器里面加
就像我在3楼里写的那样，触发器的end前别忘了commit
再试试

Answer 6

不会吧，重新编译了吗
create or replace procedure proc(tabname in varchar2,
                                  col1 in varchar2,
                                  col2 in varchar2,
                                  col3 in varchar2,
                                  viewname in varchar2 default 'v_tmp')
as
  sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' ';
  c1 sys_refcursor;
  v1 varchar2(100);
begin
  open c1 for 'select distinct to_char('||col2||') from '||tabname;
  loop
    fetch c1 into v1;
    exit when c1%notfound;
    sqlstr:=sqlstr||'
  ,max(decode('||col2||','''||v1||''','||col3||'))"'||v1||'"';
  end loop;
  close c1;
  sqlstr:=sqlstr||' from '||tabname||' group by '||col1;
  execute immediate sqlstr;
end proc;--测试表
create table tab as select 36 counter,'英语' subject,'一班' class from dual
  union all select 44,'英语','二班' from dual
  union all select 44,'数学','二班' from dual
  union all select 33,'语文','一班' from dual
  union all select 39,'语文','三班' from dual; 
select * from tab;--触发器
create or replace trigger tg_tab
after insert or delete or update on tab
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
proc('tab','subject','class','counter');
commit;
end;--测试
delete from tab where counter=44;
select * from v_tmp;SUBJECT 一班 三班 二班
英语 36
语文 33 39 rollback;
select * from v_tmp;SUBJECT 一班 三班 二班
数学 44
英语 36 44
语文 33 39

Answer 7

大概知道是怎么回事了..
自治事务允许你离开调用的事务上下文，执行一个独立的事务，然后返回调用的事务而不会影响到调用事务的状态。自治事务和调用事务不同，只有提交的事务才会在事务见共享。 
触发器被触发，执行过程的时候，由于事务尚未提交，自治事务里看不到修改后的结果，所以生成的视图还是修改前的
试试修改表之后，commit，再执行delete from TABLENAME where 1=2
使触发器再触发一次过程，然后看看视图是否正常
想想有没有更好的解决办法

Answer 8

trigger声明成事务触发器后 在存储过程里面也要加 commit吧