触发器内的代码打包-数据库教程-爱易网页

触发器内的代码打包

日期：2014-05-16　浏览次数：20509 次

触发器内的代码封装

实验准备：

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，并做如下配置：

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的实验：

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实验：

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 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 = 159
  3*        AND s.STATISTIC#=n.STATISTIC# AND n.NAME

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

触发器内的代码打包

相关资料更多>

推荐阅读更多>