database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 11306 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 11306
Oracle 기초강좌 (10)
작성자
정재익(advance)
작성일
2002-07-11 12:09
조회수
18,554

[Trigger]DataBase Trigger

 

 

1. Trigger란?

- 임의의 Table에 대해 Insert/Update/Delete 등의 SQL이 실행되면 암시적으로 실행되는 프로시저

- Trigger는 관련된 Table과는 별개로 DB내에 저장된다.

- Trigger는 뷰가 아닌 Table에 대해서만 정의가 가능하다. 그러나 View의 기본 Table에 있는 Trigger는 View에 대해 Insert/Update/Delete 가 발생하게 되면 동작한다.

 

** Oracle Forms에서의 Trigger와는 구분되어야 한다. Forms에서 애기되는 Trigger는 일반 Tools(VB,,,)의 Event와 같은 개념이다. 즉 Oracle Forms 응용프로그램의 한부분이며 특정 Trigger 지점이 특정 Forms 응용프로그램 내에서 실행될때만 수행된다. 그와반해 DB Trigger는 Table에 대해 Insert/Update/Delete시 할 행위를 정의하는 것이다.

 

 

2. Trigger의 구성

a. Triggering Event 또는 명령문

- Trigger를 실행시키는 SQL 명령

- 예를들면 아래와 같은것이 해당된다.

create trigger 다음의 bdfore update of amount on sales....

이는 sales Table에 대해 amount column의 변경될때 동작하는

Trigger를 만든다는 의미이다.

 

b. Trigger 제한사항

- 트리거 제한사항의 TRUE일때 Trigger는 동작한다.

- 각 행당 실행되는 트리거에 대해 사용가능한 Option으로

조건에 따라 Trigger 실행을 제어한다.

 

when (new.amount < old.amount)

 

즉 이같은 조건은 갱신되는 금액이 이전의 값보다 작을때

Trigger를 실행하라는 의미이다.

 

c. Trigger 작업

- PL/SQL Code를 포한하는 Trigger 처리부

 

d. Trigger 유형

- 행 트리거

에를들면 Update 명령이 Table의 여러행에 실행될때

행 트리거는 Update 명령에 의해 영향을 받는 각행에 한번씩

실행된다.

 

- 명령문 트리거

Table에 대해 명령에 영향을 받는 행에 관계없이 한번 실행된다.

 

 

e. Before대 after Trigger

- Before Trigger

명령문이 수행되기 전에 실행되는 Trigger

Insert또는 Update 명령문을 완료하기전에 특정 열의 값을

구할때 사용한다.

- After Trigger

명령문이 수행된 후에 실행되는 Trigger

 

 

f. Trigger예제

 

아래의 Trigger는 Statement Trigger임(Statement Trigger) Oracle Tip에 Trigger Mutation Error처리예가 있는데 아래의 Trigger도 처음 작성시 sa_contrct Table에 대해 Mutating Error가 발생하여 Package를 만들어 구성한 예이다. Mutate Error에 대해 궁굼하다면 Oracle Tip을 살펴보시면 설면해 놓았습니다.

 

/*  **********************************   */
/*        계약변경시 매출,입금원장 변경  */
/*  **********************************   */
create or replace trigger tr_sa_contrct
after insert or update on sa_contrct
declare
begin
 for i in 1..pkg_sa_contrct.sa_contrct_index loop
   if    inserting       then
       /************* 매출원장 생성 **************/
       insert into sa_ledger  
              (contrct_ilja,   emp_id,     cust_cd,     brand_cd,   adv_cd,
               contrct_gbn,    supply_amt, receipt_amt, regi_ilja,  modi_ilja, misu)
       values
              (pkg_sa_contrct.contrct_ilja_new(i),
               pkg_sa_contrct.emp_id_new(i),
               pkg_sa_contrct.cust_cd_new(i),
               pkg_sa_contrct.brand_cd_new(i),
               pkg_sa_contrct.adv_cd_new(i),
               pkg_sa_contrct.contrct_gbn_new(i),
               pkg_sa_contrct.amount_new(i),
               pkg_sa_contrct.contrct_amt_new(i),
               to_char(sysdate,'yyyymmdd'),
               to_char(sysdate,'yyyymmdd'),
               pkg_sa_contrct.amount_new(i) - pkg_sa_contrct.contrct_amt_new(i)      
              );                                                            
      
   end if;


   if    updating        then  
       update sa_ledger set contrct_ilja = pkg_sa_contrct.contrct_ilja_new(i) ,
                            emp_id       = pkg_sa_contrct.emp_id_new(i)       ,                             
                            cust_cd      = pkg_sa_contrct.cust_cd_new(i)      ,
                            brand_cd     = pkg_sa_contrct.brand_cd_new(i)     ,
                            adv_cd       = pkg_sa_contrct.adv_cd_new(i)       ,
                            contrct_gbn  = pkg_sa_contrct.contrct_gbn_new(i)  ,
                            supply_amt   = pkg_sa_contrct.amount_new(i)       ,
                            receipt_amt  = receipt_amt - 
                                           (pkg_sa_contrct.contrct_amt_old(i) - pkg_sa_contrct.contrct_amt_new(i)),
                            modi_ilja    = to_char(sysdate,'yyyymmdd')        ,
                            misu         = misu - (pkg_sa_contrct.amount_new(i) - pkg_sa_contrct.contrct_amt_old(i))
       where  contrct_ilja    = pkg_sa_contrct.contrct_ilja_old(i)
and    emp_id          = pkg_sa_contrct.emp_id_old(i)      
and    cust_cd         = pkg_sa_contrct.cust_cd_old(i)     
and    brand_cd        = pkg_sa_contrct.brand_cd_old(i)    
and    adv_cd          = pkg_sa_contrct.adv_cd_old(i)      
and    contrct_gbn     = pkg_sa_contrct.contrct_gbn_old(i); 


   end if;    

 end loop;
 pkg_sa_contrct.sa_contrct_index := 0;
end;
/



create or replace package pkg_sa_contrct as

      type contrct_ilja is table of sa_contrct.contrct_ilja%type
      index by binary_integer;

      type emp_id is table of sa_contrct.emp_id%type       
      index by binary_integer;

      type cust_cd is table of sa_contrct.cust_cd%type
      index by binary_integer;
      
      type brand_cd is table of sa_contrct.brand_cd%type
      index by binary_integer;

      type adv_cd is table of sa_contrct.adv_cd%type
      index by binary_integer;

      type contrct_gbn is table of sa_contrct.contrct_gbn%type
      index by binary_integer;

      type amount is table of sa_contrct.amount%type
      index by binary_integer;

      type contrct_amt is table of sa_contrct.contrct_amt%type
      index by binary_integer;
  
      type ipgum_cd is table of sa_contrct.ipgum_cd%type
      index by binary_integer;

      type card_gbn is table of sa_contrct.card_gbn%type
      index by binary_integer;

      type nurak_amt is table of sa_contrct.nurak_amt%type
      index by binary_integer;
      
      sa_contrct_index  binary_integer;

      contrct_ilja_old  contrct_ilja;
      emp_id_old        emp_id;
      cust_cd_old       cust_cd;    
      adv_cd_old        adv_cd;
      brand_cd_old      brand_cd;
      contrct_gbn_old   contrct_gbn;
      amount_old        amount;
      contrct_amt_old   contrct_amt;
      ipgum_cd_old      ipgum_cd;
      card_gbn_old      card_gbn;
      nurak_amt_old     nurak_amt;

      contrct_ilja_new  contrct_ilja;
      emp_id_new        emp_id;
      cust_cd_new       cust_cd;    
      adv_cd_new        adv_cd;
      brand_cd_new      brand_cd;
      contrct_gbn_new   contrct_gbn;
      amount_new        amount;
      contrct_amt_new   contrct_amt;
      ipgum_cd_new      ipgum_cd;
      card_gbn_new      card_gbn;
      nurak_amt_new     nurak_amt;

end pkg_sa_contrct;
/



create or replace trigger tr_aft_sa_contrct
after insert or update on sa_contrct
for each row
begin
  pkg_sa_contrct.sa_contrct_index := pkg_sa_contrct.sa_contrct_index + 1;
  pkg_sa_contrct.contrct_ilja_old(pkg_sa_contrct.sa_contrct_index) := :old.contrct_ilja;
  pkg_sa_contrct.contrct_ilja_new(pkg_sa_contrct.sa_contrct_index) := :new.contrct_ilja;
  pkg_sa_contrct.emp_id_old(pkg_sa_contrct.sa_contrct_index) := :old.emp_id;
  pkg_sa_contrct.emp_id_new(pkg_sa_contrct.sa_contrct_index) := :new.emp_id;
  pkg_sa_contrct.cust_cd_old(pkg_sa_contrct.sa_contrct_index) := :old.cust_cd;
  pkg_sa_contrct.cust_cd_new(pkg_sa_contrct.sa_contrct_index) := :new.cust_cd;
  pkg_sa_contrct.adv_cd_old(pkg_sa_contrct.sa_contrct_index) := :old.adv_cd;
  pkg_sa_contrct.adv_cd_new(pkg_sa_contrct.sa_contrct_index) := :new.adv_cd;
  pkg_sa_contrct.brand_cd_old(pkg_sa_contrct.sa_contrct_index) := :old.brand_cd;
  pkg_sa_contrct.brand_cd_new(pkg_sa_contrct.sa_contrct_index) := :new.brand_cd;
  pkg_sa_contrct.contrct_gbn_old(pkg_sa_contrct.sa_contrct_index) := :old.contrct_gbn;
  pkg_sa_contrct.contrct_gbn_new(pkg_sa_contrct.sa_contrct_index) := :new.contrct_gbn;

  pkg_sa_contrct.amount_old(pkg_sa_contrct.sa_contrct_index) := :old.amount;
  pkg_sa_contrct.amount_new(pkg_sa_contrct.sa_contrct_index) := :new.amount;
  pkg_sa_contrct.contrct_amt_old(pkg_sa_contrct.sa_contrct_index) := :old.contrct_amt;
  pkg_sa_contrct.contrct_amt_new(pkg_sa_contrct.sa_contrct_index) := :new.contrct_amt;

  pkg_sa_contrct.ipgum_cd_old(pkg_sa_contrct.sa_contrct_index) := :old.ipgum_cd;
  pkg_sa_contrct.ipgum_cd_new(pkg_sa_contrct.sa_contrct_index) := :new.ipgum_cd;
  pkg_sa_contrct.card_gbn_old(pkg_sa_contrct.sa_contrct_index) := :old.card_gbn;
  pkg_sa_contrct.card_gbn_new(pkg_sa_contrct.sa_contrct_index) := :new.card_gbn;

  pkg_sa_contrct.nurak_amt_old(pkg_sa_contrct.sa_contrct_index) := :old.nurak_amt;
  pkg_sa_contrct.nurak_amt_new(pkg_sa_contrct.sa_contrct_index) := :new.nurak_amt;
  
end;
/


create or replace trigger tr_bef_sa_contrct
before insert or update on sa_contrct
begin
  pkg_sa_contrct.sa_contrct_index := 0;
end;
/  

 

 

[Data Dictionary]데이타 딕셔너리

 

1. Data Dictionary 란?

- DB의 가장 중요한 부분의 하나로 Data Dictionat에는 연관된 DataBase 정보를 제공하는 일기전용 Table의 집합이다. 아래와 같은 정보들로 구성된다.

 

Oracle 사용자 이름

권한과 롤

Schema 개체이름(Table, View, Snapshot, index, cluster, Synonym,,,)

무결성제약 조건에 관한 정보

Column에 대한 기본값

DB의 개체에 대한 할당된 영역과 현재 사용중인 양

기타 일반적인 DataBase 정보

 

2. 구성

- 기본 Table과 사용자가 Access 할수있는 View로 구성된다.

- 기본 Table

연관된 DB의 정보를 저장하는 Table

Oracle 만이 Read/Write가 가능하며 사용자가 직접 Access할수 없다.

- 사용자 Access 가능 View

Dictionary 기본 Table의 내용을 요약하여 편리하게 보여줌

 

3. Dictionary의 소유자 SYS

- Oracle 사용자 SYS는 데이타 딕셔널리의 모든 기본 Table과 View를 소유한다.

- 기본 Dictionary Table의 내용을 변경하면 DataBase에 치명적인 악영향을 끼칠수 있다.

 

4. Dictionary 작용

- Oracle은 DDL 명령이 실행될때 마다 Data Dictionary를 Access한다.

- 모든 Oracle 사용자는 DB정보에 대한 읽기 전용 참조로 Data Dictionary

사용할 수 있다.

- DB작업동안 Oracle은 Data Dictionary를 읽어 개체의 존재여부와 사용자에게 적 합한 Access 권한이 있는지 확인한다. 또한 Oracle은 Data Dictionary를 계속 갱신하여 DataBAse 구조, 감사, 사용자권한,데이터등의 변경사항을 반영한다.

- DataBase 작업동안 Oracle은 항상 Data Dictionary에 Access하여 사용자 Access권한을 확인하고 개체 상태를 확인 하므로 Data Dictionary의 많은 정보 가 SGA에 저장된다. 모든 정보는 LRU(Least Recently Used) 알고니즘을 사용하여 메모리에 저장되며 Table과 열을 설명하는 Comments는 column을 자주 Access하지 않으면 저장되지 않는다.

- Data Dictionary에 새로운 Table이나 View를 추가할수 있는데 소유자는

SYSTEM이나 제3의 ORacle 사용자여야 하며 SYS사용자에게 속한 새로운 개체는 생성하면 안된다.

 

 

5. User 접두어를 가진 View

- 사용자와 밀접하게 관련된 View

- 사용자 자신의 전용환경을 참조

- ALL_ View의 모든 정보의 부분 집합이다.

 

- Public Synonym을 가질수 있다.

<예> 아래의 예는 Scott 사용자의 Table을 조회항 결과이다.

SQL> connect scott/tiger

Connected.

SQL> select table_name, tablespace_name from user_tables;

 

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
BONUS                          TOOLS
DEPT                           TOOLS
DUMMY                          TOOLS
EMP                            TOOLS
SALGRADE                       TOOLS

 

6. ALL 접두어를 가진 View

- 사용자가 소유한 개체와 공용 권한과 명시적으로 부여된 권한및 롤을 사용하여 사용자가 Access할수 있는 개체정보를 돌려준다.

<예>라애의 예는 Scott 사용자가 Access 가능한 DB Link를 보이는 예이다.

SQL> connect scott/tiger

Connected.

SQL> select owner, db_link from all_db_links;

 

OWNER

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

DB_LINK

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

PUBLIC

LINUX.WORLD

 

PUBLIC

SANAE.WORLD

 

PUBLIC

WINK.WORLD

 

7. DBS 접두어를 가진 View

- 전체 DB에 대한 전역 View를 제공

- DataBase 관리자만이 질의가 가능하며 select any table 권한이 있는

사용자 또한 질의가 가능하다.

- 이러한 View에 대한 동의어는 생성되지 않으며, 다른 사용자가 질의 하려면 앞에 sys.이라는 접두어를 붙여야 한다.

select owner, object_name from sys.dba_objects;

- select any table권한이 있는 사용자는 dba_sysnonyms.sql을 실행하여 자신의 계정에 DBA View에 대한 동의어를 생성할수 있다. 이를 사용하면 현재 사용자에게 해당되는 동의어가 생성된다.

 

8. DUAL Table

- Oracle과 사용자 작성 프로그램이 알려져 있는 결과를 보장하도록 참조할수 있는 작은 Table이며, 하나의 열과 하나의 행으로 구성되어 있다.

 

 

[Role]롤(Role)이란?

 

1. Role이란?

- 사용자에게 허가할수 있는 권한들의 집합

- 이 롤을 이용하여 권한 부여와 회수를 쉽게할수 있다.

- 한 사용자가 여러롤을 엑세스할수 있고 다른 여러사용자에게 같은 롤을 지정할수 있다.

 

2. 롤의 작성과 지정

- DBA가 role을 생성해야하며, 다음에 권한을 롤에 지정하고 사용자에게 롤을 부여한다.

 

SQL> create role myrole;

 

Role created.

 

SQL> grant create table, select any table to myrole;

 

Grant succeeded.

 

- 위의 예에서 myrole이라는 롤을 생성하고 해당 롤에는 create table 권한과 select any table이란 권한을 할당했다.

 

 

SQL> grant myrole to office;

 

Grant succeeded.

 

- 그런다음 2개의 권한이 있는 Role을 office라는 사용자에게 부여했다. 즉 office라는 사용자는 create table 권한과 select ant table 2개의 권한을 부여받은 것이다.

 

객체권한부여에 관하여

 

 

1. 개요

- DBA는 사용자가 특정 객체(Table , View, Sequence, Procedure, Snapshot)

에 대한 작업을 수행할수 있게 합니다.

- 객체에 대한 권한은 객체 유형에 따라 매우 다양합니다.

- 소유자(Owner) 객체에 관한 모든 권한을 가지고 있다.

- 소유자는 소유한 객체에 대해 특정권한을 다른 사용자에게 줄수 있다.

- 형식> Grant 객체권한 On 객체 to 사용자 [ With Grant Option ]

 

2. 예

SQL> grant select on test to des2;

 

Grant succeeded.

 

des2라는 사용자에게 test Table에 대한 select 권한을 부여한다.

 

SQL> desc test
Name                            Null?    Type
------------------------------- -------- ----
A                                        NUMBER(15)
B                                        VARCHAR2(2)

 

SQL> grant update(b) on test to des2;

 

Grant succeeded.

 

des2 사용자에게 test Table의 column b를 변경할수 있는 권한을 부여한다.

 

 

3. 기타

- 객체에 대한 권한을 주려면 자신이 생성한 개체이거나 with grant option

으로 객체권한을 받아야 한다.

- 객체의 소유자는 다른 사용자나 데이타베이스의 role에게 객체 권한을 부여 할수 있다.(with grant option)

- 객체의 소유자는 객체에 대한 모든 권한을 자동적으로 받는다.

- With Grant Option으로 권한을 받은 사용자는 다른 사용자나 role에게 그 권한을 다시 Grant 할수 있다.

- With Grant Option으로 허가된 객체 권한은 허가자의 권한이 취소되면 같이 취소된다.

 

SQL> connect office/office_pass;

Connected.

 

SQL> grant select on test to des2 with grant option;

Grant succeeded.

 

SQL> connect des2/des2_pass

Connected.

 

SQL> grant select on office.test to apt;

Grant succeeded.

 

- Public 키워드를 사용하면 시스템의 모든 사용자에게 권한 부여가 가능하다.

SQL>grant select on test to public;

 

test Table에대한 모든 사용자가 select가 가능하게 된다.

 

 

SQL*Plus에서 치환변수에 관해

 

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

- 치환변수(Substitution Variable) -

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

1. 일시적으로 값을 저장하기위해 SQL*Plus의 치환변수를 사용한다.

a. 단일 엠퍼센드(&)

- 사용자는 단일엠퍼센드가 앞에 붙은 치환변수를 써서 동적으로 행을 제한한다.

- 명령이 실행될때마다 사용자에게 입력을 요구한다.

 

SQL>select id, last_name, salary
    from s_emp
    where dept_id = &department_number;

    Enter value for department_number: 31


SQL>select id, &column_name
    from s_emp
    where &condition;

    Enter value for column_name : total
    Enter value for condition : payment_type = 'CASH'

 

b. 이중 엠퍼센드 치환변수(&&)

- 매번 사용자에게 Prompt하지않고 변수값을 재사용하려 할때 사용한다.

- 프롬프트는 한번만 나타남

- SQL*Plus에서는 입력된 값을 저장하고 해당 변수 이름을 참조할때마다 값을 재사용한다.

 

SQL>select id, &&column_name
    from s_ord
    order by &column_name;

    Enter value for column_name : total

 

c Set Verify명령

SQL문장상에 대한 변경사항을 확인 할려면 SQL*Plus의 SET VERIFY 명령을 사용한다. SET VERIFY를 ON으로 설정하면 SQL*Plus는 치환변수를 값으로 바꾸기 전후의 값을 출력한다.

 

SQL*Plus에서 사용자변수정의

 

 

1. 두개의 SQL명령을 사용하여 변수를 미리 정의한다.

- DEFINE : CHAR Data유형의 사용자 변수생성, UNDEFINE명령으로 해제함

- ACCEPT : 사용자의 입력을 읽어에 저장

- 두명령모두 변수가 존재하지 않을때는 변수를 생성하며,존재할경우엔 자동으로 변수값을 재정의한다.

- SQL*Plus는 이중 엠퍼센드에대해 DEFINE명령을 실행한다.

- DEFINE 사용시 한개의 공백이 필요하면 공백을 단일 따옴표로 묶어야 한다.

- ACCEPT 명령에서 치환변수를 참조할때 SQL*Plus 치환변수에 &를 붙이면 안된다.

 

a. ACCEPT

- 사용자로부터 입력값을 받아들일때 작성

ACCEPT p_dname PROMPT 'Provide the department name: '

 

- Number또는 Date DataType의 변수를 명시적으로 정의한다.

ACCEPT p_salary NUMBER PROMPT 'Salary Amount : '

 

- 보안상의 이유로 사용자의 입력을 숨길때

ACCEPT passwd CHAR PROMPT 'PassWord : ' HIDE

 

예>

SQL> select ename

2 from emp

3 where job like '%&myJob%';

Enter value for myjob: CLERK

old 3: where job like '%&myJob%'

new 3: where job like '%CLERK%'

 

ENAME

----------

SMITH

ADAMS

JAMES

MILLER

 

SQL> set echo off

SQL> accept myJob prompt ' Enter Job : '

Enter Job : CLERK

SQL> select ename

2 from emp

3 where job like '%&myJob%';

old 3: where job like '%&myJob%'

new 3: where job like '%CLERK%'

 

ENAME

----------

SMITH

ADAMS

JAMES

MILLER

 

SQL> set verify off

SQL> accept myJob prompt ' Enter Job : '

Enter Job : CLERK

SQL> select ename

2 from emp

3 where job like '%&myJob%';

 

ENAME

----------

SMITH

ADAMS

JAMES

MILLER

 

 

b. UNDEFINE을 실행하거나 SQL*Plus를 빠져나올때까지 존재

DEFINE명령으로 내용을 확인할수 있다.

세션마다 특정변수를 지정할려면 변수가 시스템 시동시에 생성될수 있게 login.sql 파일을 이용하면 된다.

 

예>

SQL> define myJob = CLERK

SQL> define dname

symbol dname is UNDEFINED

SQL> define myJob

DEFINE MYJOB = "CLERK" (CHAR)

SQL> select ename

2 from emp

3 where job like '%&myJob%';

 

ENAME

----------

SMITH

ADAMS

JAMES

MILLER

[Top]
No.
제목
작성자
작성일
조회
11321ORACLE 9I 에서의 NATIONAL CHARACTERSET("AL16UTF16" )
정재익
2002-07-12
12817
11320DBMS_JOB PACKAGE의 사용 방법과 예제
정재익
2002-07-12
8755
11307Oracle 기초강좌 (11) [2]
정재익
2002-07-11
17993
11306Oracle 기초강좌 (10)
정재익
2002-07-11
18554
11305Oracle 기초강좌 (9)
정재익
2002-07-11
11663
11304Oracle 기초강좌 (8)
정재익
2002-07-11
15298
11303Oracle 기초강좌 (7)
정재익
2002-07-11
21089
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.053초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다