Neo's Programming

트리거는 INSERT, UPDATE, DELETE가 실행 될 경우 TABLE에 대해 묵시적으로 실행되는 프로시저이다.

이는 테이블과 별도로 데이터 베이스에 저장이 되고 뷰에 대해서는 정의가 불가능하다.

또한 행과 문장 트리거 두가지로 이루어지는데,

행 트리거는 컬럼 각각의 행의 데이터 변화가 생길 때 마다 실행되고 행의 실제 값을 제어하며

문장 트리거는 단 한번만 실행되면서 컬럼의 데이터 행을 제어가 가능하다.

트리거의 문법은 다음과 같은 형태이다.

CREATE OR REPLACE TRIGGER test_trigger

BEFORE|AFTER

trigger_event ON table

[FOR EACH ROW]

[WHEN (condition)]

위에서 보면 BEFORE, AFTER, trigger_event, FOR EACH ROW를 볼 수 있다.

이의 설명은 다음과 같다. 

다음은 사용 방법이다.

CREATE OR REPLACE TRIGGER test_trigger

BEFORE

UPDATE ON TABLE

FOR EACH ROW

DBMS_OUTPUT.PUT_LINE(old.NO);

DBMS_OUTPUT.PUT_LINE(new.NO);

BEGIN

END;

위와 같이 처리하고 업데이트 문을 실행시키면 변경 전과 변경 후의 데이터가 나타 날 것이다.

또한 OR 키워드를 이용하여 UPDATE OR INSERT OR DELETE 를 이용하면 각기 문장이 일어 났을 경우에도 그 처리 된 내용을 확인 할 수 있을 것이다.

패키지는 특정 주제에 맞게 작성된 프로시저와 함수들의 집합체이다.

이는 크게 두가지 부분으로 나누어 진다. 바로 선언과 본문이다.

먼저 선언을 보자.

CREATE OR REPLACE PACKAGE INFO AS

PROCEDURE copy_info;

PROCEDURE emp_info;

END INFO;

선언부에서는 본문 내에 정의되는 내용들의 선언이다. 말 그대로 변수들의 집합과 같은 느낌을 받는다.

위에서는 단지 프로시저만 선언을 했지만, 변수, 커서, 예외, 프로시저, 함수 들을 선언 할 수 있다. 여기에 선언 된 것들은 모두 PUBLIC 한정자라 생각하면 된다.

본문은 다음과 같은 형식이다.

CREATE OR REPLACE PACKAGE BODY INFO AS

PROCEDURE copy_info

IS

BEGIN

END;

PROCEDURE emp_info

IS

BEGIN

END;

위와 같은 형식으로 정의를 하고 난 다음 실행은 EXEC INFO.프로시저명 을 하면 실행이 된다.

SQLCODE와 SQLERRM을 이용하면 EXCEPTIN에서 OTHERS에 걸리는 실제 오류 코드와 설명을 확인을 할 수 있다.

SQLCODE의 경우 성공적인 경우는 오류번호 0, 아닐 경우 해당 오류 코드를 반환한다.

SQLERRM의 오류 번호는 다음과 같다. 

EXCEPTION을 발생 시켰을 경우 예를 들면 다음과 같다.

SQLCODE : -1422
SQLERRM : ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다

예외는 다들 알고 있는 개념일 것이다.

어떠한 내용이 동작을 하다가 예상치 못한 경우들이 생겼을 때 예외처리를 해 준다.

JAVA나 C#에서 보면 TRY-CATCH 구문이다. 

다음은 오류의 종류이다.

미리 정의된 예외 종류는 http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm 에 보면 나와 있다.

몇가지 예를 들어서 보면 다음과 같다. 

정의 되지 않은 경우는 먼저 예외의 이름을 선언하며, PRAAGMA EXCEPTION_INIT을 이용하여 오류 번호를 결합한 다음 EXCEPTION 부분에서 처리를 해야한다.

다음은 그 예이다.

CREATE OR REPLACE PROCEDURE TEST

IS

test_exception EXCEPTION;

PRAGMA EXCEPTION_INIT(test_exception,오류 번호);

BEGIN

EXCEPTION

WHEN test_exception THEN 처리 내용

END;

위에서 처리 번호는 오라클 서버 에러에 대해 어느정도 참조하는 것이 좋다.

ROWID를 이용하지 않고 커서의 WHERE CURRENT OF 를 이용하여 업데이트와 삭제가 일어나도록 하는 작업이다.

이는 반드시 참조 커서가 있어야하며 FOR문 안에 업데이트 또는 삭제 쿼리가 존재해야한다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST

IS

CURSOR v_cursor IS

SELECT * FROM TABLE;

BEGIN

FOR list IN v_cursor LOOP

UPDATE TABLE

SET NO = 1

WHERE CURRENT OF v_cursor;

END LOOP;

EXCEPTION

WHEN OTHERS THEN 작업 내용

END;

FOR문에서의 커서는 자동적으로 OPEN, FETCH, CLOSE가 일어난다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST

IS

CURSOR v_cursor IS

SELECT * FROM TABLE;

BEGIN

FOR list IN v_cursor LOOP

처리 내용

END LOOP;

EXCEPTION

WHEN OTHERS THEN 처리 내용

END;

커서 상에서 인자 값도 당연히 넣을 수가 있다.

v_cursor(인자) 설정을 하고 FOR문 상에서도 v_cursor(인자) 로 적용시켜서 동작시키면 된다.

개발자에 의해 정의되는 커서이다.

커서의 순서로는 OPEN->FETCH->CLOSE 순으로 일어난다.

먼저 예제를 보자.

CREATE OR REPLACE PROCEDURE TEST

IS

CURSOR v_cursor IS

SELECT * FROM TABLE;

v_no TABLE.NO%TYPE;

BEGIN

OPEN v_cursor;

FETCH v_cursor INTO v_no;

CLOSE v_cursor;

EXCEPTION WHEN OTHERS THEN 처리내용

END;

위에서 OPEN은 커서를 열어주는 역할을 한다. 커서 내부에서 검색이 일어나고 아무런 결과를 얻지 못다허라도 에러는 발생하지 않는다.

FETCH는 현재 데이터 행을 OUTPUT 변수에 반환하며 커서의 SELECT의 컬럼 수와 OUTPUT 변수 수가 동일해야하며 타입 역시 마찮가지이다. 이들은 한 ROW씩 데이터를 FETCH한다.

CLOSE는 마지막으로 커서를 닫아주는 역할을 한다.

암시적 커서는 오라클 또는 PL/SQL 실행에 의해 처리되는 SQL 작업소라고 생각을 하면 된다.

이들은 자동적으로 커서가 OPEN과 CLOSE가 일어나며 속성은 다음과 같다.

사용하는 방법은 다음 예제를 보면 알 수 있다.

CREATE OR REPLACE PROCEDURE TEST

IS

v_data TABLE.NO%TYPE;

BEGIN

SELECT NO

INTO v_data

FROM TABLE

WHERE NO = 1

IF 속성 THEN

처리내용

END IF

END;

암시적 속성의 경우 사용자가 직접 커서를 컨트롤 하는 것이 아니기 때문에 위와 같이 속성 값을 이용하여 체크가 가능하다.

반복문에는 FOR, LOOP, WHILE 세가지가 있다.

반복문 형태를 보면 다음과 같다.

FOR list IN (SELECT * FROM TABLE) LOOP

END LOOP;

FOR의 경우 list와 같은 부분의 인덱스는 자동 선언이 됨으로 신경 쓸 것은 되지 않는다.

LOOP

EXIT WHEN 조건;

END LOOP;

LOOP의 경우 FOR과는 달리 해당하는 마지막 조건이 없다. 그래서 꼭 EXIT WHEN을 써줘야한다.

WHILE 조건 LOOP

EXIT WHEN 조건;

END LOOP;

위와 같이 세가지 형태로 쓸 수 있다.거의 형태를 보면 VB에서 많이 본 것과 별반 다를 것이 없다.

조건문의 경우도 프로그램 코드상과 거의 유사하다.

SELECT NO FROM TABLE

IF NO = 1 THEN

ELSIF THEN

ELSE

END IF;

이러한 형태로 이용을 한다.

TABLE OF RECORD는 TABLE과 RECORD의 복합적인 기능을 하는 것이다.

선언은 TABLE변수 선언과 비슷하면서 데이터 타입은 %ROWTYPE으로 선언하면 된다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST(data IN TABLE.NO%TYPE)

IS

TYPE v_table IS TABLE OF TABLE%ROWTYPE

INDEX BY BINARY_INTEGER;

i BINARY_INTEGER := 0;

table v_table;

BEGIN

FOR list IN (SELECT * FROM TABLE WHERE NO = data) LOOP

i := i+1;

table(i).NO = list.NO;

END LOOP;

END;