Bohemian life

  - BEFORE statement trigger

  - BEFORE row trigger

  - AFTER row trigger

    [...]

  - AFTER statement trigger

  * 아래 셋 중 하나

    - BEFORE

    - AFTER

    - INSTEAD OF: only for NOT modifiable view

  * Row Trigger 전용

    1) REFERENCING OLD as old NEW as new

    2) FOR EACH ROW

    3) WHEN ( condition )

  * MUST NOT design triggers

    - already built-in functionality (integrity constraints)

    - duplicate another trigger

  * be AWARE of

    - excessive use, recursive trigger, cascade trigger

    - lengthy trigger logic

  *┌ Application Trigger

   └ Database Trigger ┌ DML operations on Table                 ┬ data event

        ├ DML operations on View = INSTEAD OF trigger ┘

        └ DDL                 ─ system event

  * Database Trigger = System Trigger on┌ Database : for all users

                 └ Schema   : for the specific user

  * DML Triggers┌ Statement Trigger : only ONCE - fires once even if no rows are affected at all

└ Row Trigger : for EACH row - not executed if no rows are affected at all

- 트리거 생성시 FOR EACH ROW 절이 있으면 행 트리거, 없으면 문장 트리거

  * Events

    1) DML statements that modify data in a table (INSERT, UPDATE, or DELETE)

    2) DDL statements (CREATE, ALTER, or DROP)

    3) System events such as startup(STARTUP), shutdown(SHUTDOWN), DB_ROLE_CHANGE, and error  

        messages(SERVERERROR)                            

    4) User events such as logon(LOGON) and logoff(LOGOFF)

# http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#i12313

# http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1139

# Triggers

  : procedures stored in the database

  : be implicitly run, or fired, when something happens --> event

  * 대상: TABLE, VIEW, SCHEMA, DATABASE

# 교재 정리

  * LONG/LONG RAW vs. LOB(CLOB, BLOB, BFILE ...)

# 교재 정리

  * Local Depedencies

    - 의존성에 의해 재컴파일을 할지 안할지 결정

    - valid vs. invalid status of objects

  * Direct Dependencies vs. Indirect Dependencies

  * USER_DEPENDENCIES data dictionary view

  * Remote Dependencies

    - Time Stamp Mode

    - Signature Mode

# 교재 정리

  * Standardizing Constants & Exceptions

  * Local Subprograms

  * Bulk Binding

    - FORALL

    - BULK COLLECT

select ... bulk collect into ...

fetch ... bulk collect into ...

  * NOCOPY hint

    - procedure proc1 (name out NOCOPY empnametype)

    - NOCOPY의 효과

# 교재 정리

# DBMS_SCHEDULE package 교재 정리

ed pack_java.sql

create or replace package pack_java

is 

 procedure print_avg_sal;

 procedure p1 (p_empno number);

 procedure p1 (p_empno varchar2);

end;

/

ed pack_java_body.sql

create or replace package body pack_java

is 

 private_v_avg_sal number := 0;

 function f_emp_avg_sal return number;           -- forward declaration

 procedure p1 (p_empno number)                   -- overloading

 is

   v_sal number;

 begin

   select sal into v_sal

   from emp 

   where empno = p_empno;

   if v_sal > f_emp_avg_sal then

     p('괜찮은데!');

   end if;

 end;

 procedure p1 (p_empno varchar2)                 -- overloading

 is

 begin

   p('잘못된 데이터 타입입니다.');

 end;

 function f_emp_avg_sal return number

 is

 begin

   return private_v_avg_sal;

 end;

 procedure print_avg_sal

 is

 begin

   p(private_v_avg_sal);

 end;

 begin                                           -- initialization block (cf.one-time only procedure)

   select avg(sal) into private_v_avg_sal 

   from emp;

end;

/

select distinct name, type

from user_source

where type like 'PACKAGE%'

order by type;

exec pack_java.print_avg_sal

exec pack_java.p1(7788)

exec pack_java.p1(7900)

exec pack_java.p1('7788')

col name format a15

col text format a80

select name, type, text 

from user_source

where name = 'PACK_JAVA';

==> 패키지 본문의 소스가 다 공개됩니다

exit

C:\Documents and Settings\user\ora_java>wrap iname=pack_java_body.sql

PL/SQL Wrapper: Release 10.2.0.1.0- Production on 목 2월  23 14:46:09 2012

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing pack_java_body.sql to pack_java_body.plb

C:\Documents and Settings\user\ora_java>more pack_java.sql

C:\Documents and Settings\user\ora_java>more pack_java_body.plb

create or replace package body pack_java wrapped

a000000

34e

abcd

abcd

abcd

........

jMJv

   cf.) CALL SQL statement

exec p('오라클')

call p('오라클'); --> SQL 문이므로 ;을 반드시 붙여주어야 합니다.

        create or replace package pack2

is

   procedure proc1 (a number);

end;

/

create or replace package body pack2

is

   procedure proc1 (a number)

   is

   begin

     proc2(20);

   end;

   procedure proc2 (b number)

   is

   begin

     null;

   end;

end;

/

경고: 컴파일 오류와 함께 패키지 본문이 생성되었습니다.

SQL> show error

PACKAGE BODY PACK2에 대한 오류:

LINE/COL ERROR

-------- -----------------------------------------------------------------

8/7      PL/SQL: Statement ignored

8/7      PLS-00313: 범위 내에 'PROC2'(이)가 선언되지 않았습니다

↓ ↓ ↓ 

create or replace package body pack2

is

   procedure proc2 (b number); -- forward declaration

   procedure proc1 (a number)

   is

   begin

     proc2(20);

   end;

   procedure proc2 (b number)

   is

   begin

     null;

   end;

end;

/

  create or replace function to_char(a number) return varchar2

  is

  begin

    return a ||'입니다';

  end;

  /

  col ret format a20

  select empno, sal, to_char(sal), java30.to_char(sal) as ret

  from emp;

      -----------------

  create or replace package pack_overloading

  is

    procedure print(a number);

  end;

  / 

  create or replace package body pack_overloading

  is

    function to_char(a number) return varchar2

    is

    begin

      return a ||'입니다';

    end;

    procedure print(a number)

    is 

    begin

      p(to_char(a));

      p(standard.to_char(a));

    end;

  end;

  / 

  set serveroutput on

  exec pack_overloading.print(1000)

# 4-3

  create or replace procedure up_overloading (p_in number)

  is

    procedure print(a number)

    is 

    begin

      p('제시한 값 : '||a||'는 사용할 수 있습니다.');

    end;

    procedure print(a varchar2)

    is 

    begin

      p('제시한 값 : '||a||'는 사용할 수 없습니다.');

    end;

  begin

    if p_in < 1000 then

      print (to_char(p_in));

    else

      print (p_in);

    end if;

  end;

  /

  exec up_overloading(500)

  exec up_overloading(1500)