触发器内的代码封装
实验准备： www.2cto.com
[sql]
hr@ORCL> drop table t purge;
Table dropped.
hr@ORCL> create table t (code number);
Table created.
hr@ORCL> create table t_audit (code number,ins_date date);
Table created.
hr@ORCL> create or replace trigger tri_audit_t
2 before insert
3 on t
4 for each row
5 begin
6 insert into t_audit values(:new.code,sysdate);
7 end;
8 /
同时打开两个session，并做如下配置： www.2cto.com
[sql]
hr@ORCL> alter session set sql_trace=true;
Session altered.
hr@ORCL> alter session set tracefile_identifier='linwaterbin_null'; --session_1
Session altered.
hr@ORCL> alter session set tracefile_identifier='linwaterbin'; --session_2
Session altered.
hr@ORCL> set feedback off
session_1的实验：
[sql]
hr@ORCL> ed
Wrote file afiedt.buf
1 SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 139
3* AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)')
hr@ORCL> /
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
139 304 parse count (total)
139 99 parse count (hard)
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> ed
Wrote file afiedt.buf
1 SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 139
3* AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)')
hr@ORCL> /
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
139 344 parse count (total)
139 101 parse count (hard)
344-304=40，这里做了40次解析调用
session_2实验：
[sql]
hr@ORCL> ed
Wrote file afiedt.buf
1 create or replace procedure pro_t_audit(p_code number)
2 is
3 begin
4 insert into t_audit values(p_code,sysdate);
5* end;
hr@ORCL> /
Procedure created.
hr@ORCL> ed
Wrote file afiedt.buf
1 create or replace trigger tri_audit_t
2 before insert
3 on t
4 for each row
5* call pro_t_audit(:new.code)
hr@ORCL> /
Trigger created.
hr@ORCL> ed
Wrote file afiedt.buf
1 SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 159
3* AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)')
hr@ORCL> /
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
159 414 parse count (total)
159 176 parse count (hard)
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t values(1);
hr@ORCL> insert into t value