DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
트리거 타입별로 여러 차례에 걸쳐 실행되는 트리거, 동일한 타입의 트리거의 실행 순서 정의, 새로운 CONTINUE 구문 등 PL/SQL 프로그래밍 작업의 효율성을 개선하는데 도움이 되는 새로운 기능들을 소개합니다. PL/SQL은 처음 소개된 이후로 오라클 데이터베이스 환경의 가장 기본을 이루는 프로그래밍 언어로 인정 받아 왔습니다. 오랜 세월 동안, 우리는 PL/SQL이 보다 적은 코딩 작업으로 보다 많은 기능을 제공하는 포괄적인 개발 플랫폼으로 진화되는 과정을 지켜 볼 수 있었습니다. Oracle Database 11g는 프로그래머들을 위해 한층 효율적인 PL/SQL 코딩 환경을 제공하고 있습니다. 이번 연재에서는 몇 가지 예제들을 통해 PL/SQL에 관련된 새로운 기능들을 살펴 보기로 하겠습니다. 한 호텔에서 관리하는 데이터베이스에, 호텔 룸의 예약 상황이 BOOKINGS라는 이름의 테이블에 저장되어 있습니다. 이제 이 테이블의 변경 사항을 감사 용도의 별도 테이블에 저장하고 테이블을 트랜잭션 기반으로 관리하고자 합니다. 이런 목적이라면 트리거가 가장 적격입니다. DBA는 간단한 "after-update row" 트리거를 이용해서 기존의 값과 새로운 값, 그리고 변경 작업을 실행한 사용자 정보를 BOOKINGS_HIST 테이블에 저장합니다. 지금까지는 작업에 별다른 문제가 없습니다. 하지만 한 가지 작은 문제가 발생했습니다. "after-update row" 트리거는 모든 로우에 대해 동작합니다. 하지만 단체 예약과 같은 경우에는 한 번의 트랜잭션으로 수백 개의 로우가 업데이트되기도 합니다. 이 경우 개별 로우에 대해 "after-update row" 트리거가 동작하고 트리거가 한 번 실행될 때마다 bookings_hist 테이블에 레코드가 삽입되어야 합니다. 따라서 성능이 저하될 수 밖에 없습니다. 좀 더 나은 대안으로 INSERT 작업을 배치 처리해서 한꺼번에 bookings_hist 테이블에 입력하는 방법이 있습니다. 복잡한 형태의 트리거를 한꺼번에 실행하면 이러한 작업이 가능합니다. 로우 트리거에서 업데이트될 값들을 컬렉션에 저장한 다음, "after-update-statement" 트리거에서 컬렉션의 데이터를 로드해서 bookings_history 테이블에 한 번에 저장하는 방법입니다. 실제 INSERT 작업은 단 한 차례만 실행되므로 개별 로우를 INSERT 하는 것보다는 성능이 좋습니다. 하지만 두 개의 트리거를 별도로 작성해야 한다는 문제가 남습니다. 하나의 트리거에서 다른 트리거로 컬렉션 변수를 전달하기 위해서는 VARRAY 또는 PL/SQL TABLE과 같은 컬렉션 변수를 포함하는 패키지를 생성하고, 이 데이터를 "after-update row" 트리거로 입력한 뒤 다른 "after-statement" 트리거에서 읽어 와야 합니다. 쉬운 작업이 아닙니다. 모든 트리거 작업을 하나의 코드에 저장할 수 있다면 훨씬 간단해지지 않을까요 Oracle Database 11g의 컴파운드 트리거(compound trigger)를 이용하면 가능합니다. 컴파운드 트리거는 4가지 서로 다른 트리거를 하나의 트리거 안에 정의하고 있습니다. 예를 들어 UPDATE 컴파운드 트리거는 "before statement", "before row", "after statement", "after row" 트리거를 하나의 컴파운드 트리거 안에 포함합니다. 코드는 하나만 존재하며 따라서 다른 PL/SQL 코드들과 마찬가지 방법으로 변수를 전달하는 것이 가능합니다. 한 가지 예를 들어 설명해 보겠습니다. 설명을 위해 코드에 라인 넘버를 추가했습니다. 트리거의 동작 방식을 이해하기 위해 4개의 로우를 업데이트해 보기로 합시다. 실행 결과가 아래와 같습니다. 컴파운드 트리거가 동작하는 방식에 주목하시기 바랍니다. 컴파운드 트리거는 4개의 섹션으로 구분됩니다. 위에서 볼 수 있듯, 코드는 하나이지만 각 섹션은 서로 다른 시점에 실행됩니다. 앞의 예제에서 dbms_output 구문을 여러 시점에서 실행하도록 함으로써 각각의 섹션이 어떤 시점에 실행되는지 확인할 수 있도록 했습니다. 그리고 bookings_ids 값이 100, 101, 102, 103인 4개의 로우를 업데이트했습니다. 이 과정에서 "before-statement", "after-statement" 트리거가 한 차례씩, 그리고 로우 트리거(before, after)들은 로우별로 한 차례씩 실행되었습니다. (앞의 예제에서는 "before-statement", "before-row" 트리거가 필요하지 않지만, 기능에 대한 이해를 돕기 위해 추가되었습니다.) 이제 bookings_hist 테이블을 살펴보면 4개의 레코드가 추가되었음을 확인할 수 있습니다. 4개의 레코드는 구문 실행의 마지막 단계에서 한꺼번에 업데이트되었으며, 로우 단위로 업데이트되지 않았습니다. 컴파운드 트리거는 트리거가 동작할 때 PL/SQL 코드 내에서 변수, 패키지와 같은 "stateful" 오브젝트가 사용되고 트리거 종료 시점에서 그 정보가 삭제되는 경우에 매우 유용합니다. 위의 예제에서, 필자는 컬렉션을 초기화하거나 컬렉션의 컨텐트를 삭제하는 작업을 별도로 수행하지 않았습니다. 이러한 작업은 데이터베이스에 의해 자동으로 수행됩니다. 동일한 타입의 다수 트리거를 하나의 테이블에 정의하는 기능은 Oracle8부터 제공되어 왔습니다. 예를 들어 동일한 테이블에서 로우가 INSERT될 때마다 동작하는 2개의 트리거를 함께 구현하는 것이 가능합니다. 기본적으로 트리거의 타입은 실행 순서를 결정하며, "before statement", "before row", "after statement", "after row"의 순서로 실행됩니다. 하지만 두 개의 "after-row" 트리거가 존재한다면 어느 것이 먼저 실행될까요 동일한 타입의 트리거 실행 순서는 무작위에 가깝다고 볼 수 있으며, 일정한 패턴을 따른다는 보장이 불가능합니다. 이게 과연 문제가 될까요 아래의 PAYMENT 테이블 예제를 통해 살펴 봅시다. 위 테이블에서 RISK_RATING 컬럼의 값은 PAY_MODE, AMOUNT, STORE 컬럼으로부터 계산됩니다. 아래와 같은 간단한 "before update row" 트리거를 이용하면 RISK_RATING 컬럼 값을 쉽게 얻을 수 있습니다. 이제 새로운 요구 사항이 추가됩니다. RISK_RATING, PAY_MODE를 기준으로 한 계산 결과 후속 작업이 필요하다고 판단되는 경우, FOLLOW_UP이라는 새로운 컬럼에 플래그를 삽입해야 합니다. 물론 위의 트리거를 수정할 수도 있습니다. 하지만 기존 코드를 그대로 두고 동일한 타입의 새로운 트리거를 구현하는 것이 일반적으로 바람직한 방법으로 권장됩니다. (트리거의 동작 순서를 확인하기 위해 코드에 dbms_output 구문을 넣어 두었습니다.) 이제 테이블을 업데이트해 봅시다: 어떻게 되었을까요 RISK_RATING 컬럼 값이 HIGH이고 PAY_MODE 컬럼 값이 "C"이므로 FOLLOW_UP 컬럼은 "Y"가 되어야 합니다. 하지만 여기서는 "N'이 되었습니다. 그 이유가 무엇일까요 트리거가 동작한 순서를 살펴 봅시다. tr_pay_follow_up 트리거가 tr_pay_risk_rating 트리거보다 먼저 실행되었습니다. tr_pay_risk_rating 트리거는 RISK_RATING 컬럼을 "HIGH"로 설정합니다. tr_pay_follow_up 트리거가 먼저 실행되었을 때에는 RISK_RATING 컬럼이 null(또는 "LOW")이므로 FOLLOW_UP 컬럼이 "N"으로 설정된 것입니다. 이러한 경우라면 트리거의 실행 순서가 매우 중요할 것입니다. tr_pay_risk_rating 트리거가 먼저 실행되지 않는다면 변수 값이 올바르게 설정되지 않을 것이며, 따라서 원하는 결과를 얻을 수 없습니다. 결국 유일한 방법은 모든 로직을 하나의 코드에 넣고 순서대로 실행하도록 하는 것 밖에 없습니다. Oracle Database 11g에서는 트리거 생성 스크립트에 트리거의 실행 순서를 정의하는 구문을 삽입하는 것이 가능합니다. 트리거 실행 순서 정의 부분을 포함하는 트리거 코드의 일부가 아래와 같습니다: 여에 해당 트리거가 실행되도록 정의하게 됩니다. 앞에서 예로 든 업데이트 스크립트를 가지고 테스트해 봅시다. FOLLOW_UP 컬럼은 기대했던 대로 "Y"로 업데이트 되었습니다. 트리거의 실행 순서 정의 기능은 올바른 순서로 트리거가 실행되게 할 뿐 아니라, 코드의 모듈화를 가능하게 한다는 이점을 제공합니다. PL/SQL은 매우 강력한 언어이지만 그 동안 누락되었던 중요한 요소가 한 가지 있었습니다. 바로 아무 것도 하지 않고 루프의 마지막 부분으로 이동하는 명령입니다. Oracle Database 11g의 PL/SQL에는 CONTINUE라는 새로운 컨스트럭트가 포함되었습니다. 루프 내에서 사용 가능한 CONTINUE 구문은, 로직이 루프의 마지막 부분으로 이동하여 다시 루프의 처음으로 돌아가게 합니다. Counter가 10의 배수가 아닌 경우 루프의 마지막 부분으로 이동하도록 구현된 간단한 코드 예제가 아래와 같습니다. 실행 결과가 아래와 같습니다. 이번에는 Loop Name을 이용한 CONTINUE의 구현 예제입니다. 실행 결과가 아래와 같습니다. mod(inner, 3)와 같은 정적인 컨스트럭트를 사용하는 대신, 계산 작업을 위한 함수를 활용하는 것도 가능합니다. <>말할 필요도 없는 얘기겠지만, CONTINUE는 루프 내에서 의미 있는 형태로만 사용이 가능합니다. 루프 밖에 CONTINUE를 사용하면 컴파일러 에러가 발생할 것입니다. 기존에는 PL/SQL 프로그램에 시퀀스를 추가하려면 SELECT .NEXTVAL INTO FROM DUAL와 같은 컨스트럭트를 사용해야 했습니다 하지만 이제는 굳이 그럴 필요가 없습니다. 시퀀스의 다음 값을 변수에 직접 할당하는 것이 가능합니다: 덕분에 코드가 훨씬 단순해졌습니다. 많은 PL/SQL 프로그래머들이 OTHERS 구문을 매우 위험한 방식으로 활용하곤 합니다. 위 구문은 "에러가 발생하면 아무 것도 하지 말고, 마치 에러가 발생하지 않은 것처럼 행동하라"는 의미를 갖습니다. 세상이 그렇게 간단하게 돌아갈 수 있으면 얼마나 좋을까요! 이러한 관행은 코드의 안정성을 저하시키는 역할만 할 뿐입니다. 이러한 상황에서 Oracle Database 11g의 새로운 기능이 도움이 될 수 있습니다. Oracle Database 11g는 PLW-06009라는 이름의 새로운 경고(warning)를 통해, 컴파일 타임에서 이러한 문제를 미리 경고해 줍니다. 그 예가 아래와 같습니다. 이 프로시저를 컴파일하면, 아무런 경고 없이 정상적으로 컴파일이 완료될 것입니다. 경고를 활성화하려면 먼저 세션 매개변수를 설정해 주어야 합니다. 컴파일 과정에서 새로운 경고(PLW-06009)가 발생했음을 확인할 수 있습니다. 이것은 단순한 경고일 뿐이고 컴파일은 정상적으로 완료됩니다. 따라서 프로시저를 실행하는 것은 가능하지만, 경고된 내용에 항상 주의할 필요가 있습니다. 고가용성을 요구하는 운영 시스템에서는 변경 작업을 실행할 수 있는 시간 여유를 충분히 확보하기가 쉽지 않습니다. 아래와 같은 일상적이면서도 난감한 상황을 생각해 봅시다. DBA가 테이블에 새로 추가할 트리거를 모두 작성해 두었습니다. 하지만 변경 작업이 허용된 시간 대에 트리거를 생성하려 시도하는 순간, 시덥지 않은 문제 때문에 컴파일 에러가 발생하였습니다. 변경 작업 시간 이전에 트리거를 미리 생성해 둘 수 있다면 좋겠지만, 트리거를 생성하는 순간 바로 활성화 되어 버리는 것이 문제입니다. 어떻게 해야 할까요 Oracle Database 11g에서라면 더 이상 문제될 것이 없습니다. 트리거를 비활성화된 상태로 생성하고 컴파일 에러를 미리 확인할 수 있습니다. 그런 다음 변경 작업이 허용된 시간대에 바로 활성화하는 것이 가능합니다. 비활성화된 트리거를 생성하는 방법이 아래와 같습니다: 트리거를 비활성화된 상태로 생성하는 경우에도 에러는 없어야 합니다. 에러를 포함하는 트리거를 생성하려 시도하면 (예를 들어 트리거가 존재하지 않는 테이블 "M"에 접근하는 경우) 아래와 같은 결과를 얻게 됩니다. 이 기능은 변경 관리 목적으로 매우 유용하게 활용됩니다. 또 특정 시점에 트리거를 활성화하는 용도로도 사용될 수 있습니다. 한 예로, 트리거를 이용한 감사 솔루션을 구현하려고 하지만 audit_table에 기존의 레코드가 아직 삭제비활성화된 상태로 생성한 뒤 테이블이 준비되는 대로 활성화할 수 있습니다. 아래와 같은 간단한 함수가 있습니다: 이 함수는 아주 간단하지만 개념을 설명하기에는 충분합니다. 함수에는 두 개의 매개변수가 존재하며, 아래와 같이 매개변수를 "positional value"로 전달하는 방법으로 함수를 호출할 수 있습니다: 2010-02-18 myfunc (1,2) 또는 네임드 매개변수(named parameter)를 사용할 수도 있습니다: 하지만 후자의 방법을 SELECT 구문에서 사용하는 경우 문제가 될 수 있습니다. Oracle Database 10g에서 아래와 같은 구문을 실행하는 경우: SQL> select myfunc (p_param1=>1,p_param2=>1) from dual; 아래와 같은 에러가 발생합니다: 보다시피 정상적으로 실행되었습니다. 네임드 매개변수는 마지막 부분에 설정할 수 있습니다. 첫 번째는 반드시 "positional"로 설정되어야 합니다. 예를 들어, 아래의 구문은 실행 가능합니다. p_param1 매개변수가 1로 설정되어 있기 때문입니다. 하지만 아래의 경우는 "positional" 매개변수가 뒷부분에 정의되고 있으므로 실행되지 않습니다. Native Dynamic Cursor의 유용성에 대해서는 모두들 잘 알고 계시리라 생각합니다. Native Dynamic Cursor는 특히 쿼리를 실행하기 전에 정확히 어떤 내용을 조회하는지 모르는 경우 유용합니다. 또 DBMS_SQL을 이용한 Dynamic PL/SQL도 사용해 보셨을 것입니다. 두 가지 방법 모두 나름의 장점을 갖고 있습니다. 하지만 두 가지 중 하나의 방법을 선택하여 프로그램을 개발하고 난 뒤, 나중에 다른 방법으로 전환하려 한다면 어떻게 해야 할까요 Oracle Database 11g에서는 쉽게 해결이 가능합니다. 기본 제공되는 DBMS_SQL 패키지에 새로 추가된 TO_REFCURSOR 함수는 DBMS_SQL 다이내믹 커서를 REF 커서로 변환해 줍니다. 변환 작업의 예가 아래와 같습니다. 컴파일 시점에 조회 대상 컬럼 목록을 미리 파악하지 못한 상태에서 제너릭한 프로시저를 작성해야 하는 경우를 가정해 봅시다. 바로 이런 경우에 네이티브 다이내믹 SQL이 유용하게 활용됩니다. 이를 위해 REF 커서를 정의할 수 있습니다. 여기에 더해서, 바인드 변수도 미리 알고 있지 못하다고 가정해 봅시다. 이런 경우에는 dbms_sql이 좀 더 유용합니다. 최소한의 코딩만으로 이처럼 복잡한 요구 사항을 해결하려면 어떻게 해야 할까요 간단합니다. 바인딩 부분에서는 dbms_sql을 활용하고, 다른 부분을 실행할 때에는 REF 커서로 전환하면 됩니다. 마찬가지로 Native Dynamic SQL을 REF CURSOR로 변환할 수도 있습니다. 이를 위해 사용되는 함수가 TO_CURSOR_NUMBER입니다: 변수 c_ref_cur에 의해 정의된 REF 커서는 이 호출이 실행되기 이전에 오픈되어 있어야 합니다. 호출이 완료되면 REF 커서는 그 수명을 다하고, 이후에는 dbms_sql 커서에 의해서만 조작이 가능합니다. 컴파일 시점에 바인드 변수는 알고 있지만 컬럼 목록은 알고 있지 못하다고 가정해 봅시다. Native Dynamic SQL을 이용한 REF 커서를 일단 사용하고, 나중에 dbms_sql로 변경하여 커서로부터 컬럼 목록을 가져올 수 있습니다. 지금까지 알아 본 것처럼, Oracle Database 11g는 효율적인 포함하고 있습니다. "Oracle Database 11g: DBA와 개발자가 알고 있어야 하는 새로운 기능" 홈페이지로 돌아가기 Arup Nanda (arup@proligence.com)는 Starwood Hotel and Resorts의 데이터베이스 시스템 매니저로 12 년이 넘는 기간 동안 오라클 DBA로 활동해 왔으며 2003년에 오라클 매거진에 의해 "올해의 DBA"로 선정되었습니다. Arup은 오라클 관련 이벤트 및 저널의 발표자, 기고자로서 적극적으로 활동하고 있으며 뉴욕 오라클 사용자 그룹 실행 위원회 회원이자 오라클 ACE 디렉터입니다. 그는 를 포함하는 4권의 책을 공저하였습니다효율적인 PL/SQL 코딩
효율적인 PL/SQL 코딩
간략한 개요
컴파운드 트리거
1 create or replace trigger tr_bookings_track
2 for update of booking_dt
3 on bookings
4 compound trigger
5 type ty_bookings_hist is table of bookings_hist%rowtype
6 index by pls_integer;
7 coll_bookings_hist ty_bookings_hist;
8 ctr pls_integer := 0;
9 before statement is
10 begin
11 dbms_output.put_line('In before statement');
12 end before statement;
13 before each row is
14 begin
15 dbms_output.put_line('In before each row');
16 end before each row;
17 after each row is
18 begin
19 ctr := ctr + 1;
20 dbms_output.put_line('In after each row. booking_id='||:new.booking_id);
21 coll_bookings_hist(ctr).booking_id := :new.booking_id;
22 coll_bookings_hist(ctr).mod_dt := sysdate;
23 coll_bookings_hist(ctr).mod_user := user;
24 coll_bookings_hist(ctr).old_booking_dt := :old.booking_dt;
25 coll_bookings_hist(ctr).new_booking_dt := :new.booking_dt;
26 end after each row;
27 after statement is
28 begin
29 dbms_output.put_line('In after statement');
30 forall counter in 1..coll_bookings_hist.count()
31 insert into bookings_hist
32 values coll_bookings_hist(counter);
33 end after statement;
34 end tr_bookings_track;
br> update bookings
set booking_dt = sysdate
where booking_id between 100 and 103;
In before statement
In before each row
In after each row. booking_id=100
In before each row
In after each row. booking_id=101
In before each row
In after each row. booking_id=102
In before each row
In after each row. booking_id=103
In after statement
트리거의 순차화된 실행
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
follows tr_pay_risk_rating
begin
... and so on ...
CONTINUE
ctr=10
ctr=20
ctr=30
... and so on ..
-> outer=1
..-> inner=1
..-> inner=2
-> outer=2
..-> inner=1
..-> inner=2
-> outer=3
..-> inner=1
..-> inner=2
... and so on ...
시퀀스의 정의
declare
trans_id number(10);
begin
select myseq.nextval
into trans_id
from dual;
end;
declare
trans_id number(10);
begin
trans_id := myseq.nextval;
end;
/
When OTHERS Then Do Something
when OTHERS then
NULL;
비활성화된 트리거
create or replace trigger tr_t
after insert on t
for each row
disable
begin
insert into t1 (a) values (:new.col_a);
end;
/
이제 상태를 확인해 봅시다: SQL> select status 2> from user_triggers 3> where trigger_name = 'TR_T' 4> / STATUS -------- DISABLED
함수에서의 매개변수 이름 사용
create or replace function myfunc
(
p_param1 number,
p_param2 number
)
return number
is
begin
return p_param1 + p_param2;
end;
myfunc ( p_param1 => 1, p_param2 => 2)
select myfunc (1,p_param2=>2) from dualSQL> select myfunc (p_param1=>1,2) from dual;select myfunc (p_param1=>1,2) from dual
*
ERROR at line 1:
ORA-06553: PLS-312: a positional parameter association may not follow a named association
Dynamic Cursor와 REF CURSOR의 호환성
1 create or replace procedure list_trans_by_store
2 (
3 p_store_id number
4 )
5 is
6 type num_tab is table of number index by binary_integer;
7 type type_refcur is ref cursor;
8 c_ref_trans_cur type_refcur;
9 c_trans_cur number;
10 trans_id num_tab;
11 trans_amt num_tab;
12 ret integer;
13 l_stmt clob;
14 begin
15 c_trans_cur := dbms_sql.open_cursor;
16 l_stmt :=
17 'select trans_id, trans_amt from trans where store_id = :store_id';
18 dbms_sql.parse(c_trans_cur, l_stmt, dbms_sql.native);
19 dbms_sql.bind_variable(c_trans_cur, 'store_id', p_store_id);
20 ret := dbms_sql.execute(c_trans_cur);
21 c_ref_trans_cur := dbms_sql.to_refcursor(c_trans_cur);
22 fetch c_ref_trans_cur bulk collect into trans_id, trans_amt;
23 for ctr in 1 .. trans_id.count loop
24 dbms_output.put_line(trans_id(ctr) || ' ' || trans_amt(ctr));
25 end loop;
26 close c_ref_trans_cur;
27* end;
cur_handle := dbms_sql.to_cursor_number (c_ref_cur);결론