카테고리 없음

PL/SQL Programming 기록

JangGeonWu 2022. 8. 17. 12:10

PL/SQL의 제어문, Exception(예외처리), Cursor(커서)를 정리하고자 한다.

제어문

IF문

if문의 구조는 아래와 같다.

IF 조건 THEN
    문장1;
    ...
[ELSIF 조건 THEN
    문장2;
    ...

    ...
ELSE [조건 THEN]
    문장n;
]
END IF;

이때, 여러 개의 ELSIF절은 쓸 수 있지만 ELSE절은 한 번만 사용이 가능하다.
조건에 맞으면 THEN 이하의 문장을 실행, 계속 안맞으면 아래로 내려감. 어느 경우에나 END IF 다음의 문장으로 넘어감.

LOOP 문

일반 LOOP문

LOOP문의 구조는 다음과 같다.

BASIC LOOP문

LOOP
    문장들;
    ...
    EXIT [WHEN 조건]
END LOOP;

EXIT는 (무한 루프)를 탈출하는 역할을 수행, 조건에 따라 LOOP를 탈출할 수 있도록 WHEN절을 기술한다.

FOR LOOP문

EXIT절이 없는 루프문으로 FOR ~ LOOP문이 존재한다.

FOR 인덱스 IN [REVERSE] 하한 ... 상한 LOOP
    문장들;
    ...
END LOOP;

FOR LOOP에서 인덱스는 정수로 자동 선언되고, 상한에 도달할 때까지 1씩 증가
REVERSE를 추가하면 1씩 감소
증감치를 따로 지정할 수는 없다. 증감치를 따로 지정하고 싶다면 위의 일반 LOOP문을 이용하거나, 아래의 WHILE LOOP문을 이용해야 한다.

WHILE LOOP문

다른 언어에서 사용하는 제어문 WHILE과 유사하다.

WHILE 조건 LOOP
    문장들;
END LOOP;

제어조건이 TRUE인 경우에만 문장을 반복한다. 처음 시작부터 조건이 FALSE이면 LOOP를 실행하지 않는다.

Exception

Exception은 PL/SQL에서 발생하는 에러로, 각각 자신만의 Error Message를 가진다.
일반적으로 Oracle Server가 에러를 판단하여 Exception을 발생시키는 경우가 다반사이나, 사용자가 블록에서 'RAISE'문을 사용하여 명시적으로 Exception을 발생시킬 수 있다.

Exception 처리 구문

EXCEPTION
    WHEN exception_name [exception_names ...] THEN
        문장들;
    [WHEN .... THEN ...]
    ...
    [WHEN OTHERS THEN ...] -- ELSE와 비슷한 형태
    ...

WHEN OTHERS 구문은 EXCEPTION 처리의 마지막 구문으로, 미리 선언되지 못한 모든 경우의 예외 처리를 수행한다.

이제 exception_name을 파악해야 한다.

Case of Exception

Predefined Exception

오라클이 미리 정의해둔 Exception.

다음과 같은 Exception이 존재한다.

Exception_Name	설명
NO_DATA_FOUND	데이터를 반환하지 못한 SELECT문
TOO_MANY_ROWS	두 개 이상을 반환한 SELECT문
INVALID_CURSOR	잘못된 CURSOR 연산 발생
ZERO_DIVIDE	0으로 나누기
DUP_VAL_ON_INDEX	UNIQUE COLUMN에 중복된 값을 입력할 때
CURSOR_ALREADY_OPEN	이미 열려있는 커서를 여는 경우
INVALID_NUMBER	문자열을 숫자로 전환하지 못한 경우
LOGIN_DENIED	유효하지 않은 사용자로 LOG-ON 시도
NOT_LOGGED_ON	PL/SQL 프로그램이 오라클에 연결되지 않은 상태서 호출
PROGRAM_ERROR	PL/SQL 내부에 오류
STORAGE_ERROR	PL/SQL 메모리 부족
TIMEOUT_ON_RESOURCE	오라클이 자원을 기다리는 동안 시간 초과 발생
VALUE_ERROR	산술, 절단 등에서 크기가 다른 오류 발생

Non-Predefined Exception

사용자가 DECLARE 섹션에서 Exception 이름을 정의하고, Error 번호를 사용해 Error와 연결, 이후 Exception에서 예외처리를 진행한다.

DECLARE
        exception_name EXCEPTION;
        PRAGMA EXCEPTION_INIT(exception이름, 에러번호);
    BEGIN
        ...
    EXCEPTION
        WHEN exception_name THEN (이럴 때 exception이라 할거에요)
        ...
END;

Exception을 정의하는 예시를 보면서 파악해보자

CREATE OR REPLACE PROCEDURE def_product
    (  v_id IN s_product.id%TYPE  )
    IS
        fk_error EXCEPTION
        PRAGMA EXCEPTION_INIT(fk_error, -2292)
    BEGIN
        DELETE FROM s_product
        WHERE id = v_id
        COMMIT;
    EXCEPTION
        WHEN fk_error THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('참조되는 레코드가 있으므로 삭제 불가');
    END;
    /

위의 '-2292'는 'ORA-02292'로, 데이터 삭제할 때 참조 무결성 위반이 발생하면 나타나는 에러번호이다.
정확하게는, Exception에 별명을 붙인다고 생각하면 된다.

User Defined EXCEPTION
- 오라클 서버가 판단하는 에러가 아닌, 사용자가 정의한 조건이 만족되지 않을 때 에러를 발생시키는 방법이다.
- DECLARE 섹션에서 EXCEPTION 이름을 정의하고, BEGIN 섹션에서 RAISE문을 사용해 에러를 발생시킨다.
- DECLARE exception_name EXCEPTION; BEGIN RAISE exception_name; ... EXCEPTION WHEN exception_name THEN ... END;
- 구조는 아래와 같다.
- 위의 RAISE문 사용 방법은 아래와 같다.

... IS
   v_id s_emp.id%TYPE;
   lowsal_err EXCEPTION;
BEGIN
   ...
   IF v_sal >= 600 THEN
      INSERT INTO s_emp
      (id, name, salary, title, commission_pct, start_date) values(v_id, v_name, v_sal, v_title, v_comm, SYSDATE);    ELSE
     RAISE lowsal_err;
  END IF;
EXCEPTION
   WHEN lowsal_err THEN
      DBMS_OUTPUT.PUT_LINE('USER ERROR RAISED');
END; 
/

위처럼 EXCEPTION이 일어날 조건을 걸고, RAISE하여 바로 에러를 발생시킬 수 있다.

기타 EXCEPTION 유형

일반적인 에러 처리를 위하여 오라클에서 제공하는 함수인 SQLCODE, SQLERRM을 활용할 수 있다.

함수명	용도	사용방법
SQLCODE	오라클에서 지정된 에러 코드를 숫자로 리턴	v_code := SQLCODE;
SQLERRM	지정된 에러에 해당하는 에러 메시지를 리턴	v_msg := SQLERRM;

커서(Cursor)

오라클 서버는 SQL문을 실행하고, 처리한 정보를 저장하기 위해 'Private SQL Area'라는 작업영역을 이용한다.
이 영역에 이름을 부여하고 저장된 정보를 처리할 수 있게 해주는 것을 커서(Cursor)라고 한다.
묵시적인(Implicit, 시스템이 알아서 처리) 커서와 명시적인(Explict, 사용자가 정의) 커서가 있다.
PL/SQL에서 SELECT문은 한 개의 Row만을 검색할 수 있기 때문에, 여러개의 Row를 검색하기 위해선 Explict Cursor를 사용해야 한다.

Implicit 커서는 PL/SQL 블록의 BEGIN 섹션에 SQL문이 있으면, SQL이라는 이름을 Implicit 커서를 만들게 되는데, 시스템은 이 커서를 자동으로 관리하며 가장 최근의 Implicit 커서의 결과를 알려주기 위해 Attribute를 제공한다.
PL/SQL에서 Attribute를 함수처럼 사용할 수 있지만, 일반적인 SQL에서는 사용 불가능하다.
이 Attribute는 DML문의 수행결과를 알아보는데 유용하다.

Implicit Cursor

우선, Attribute의 이름과 내용을 정리한 표이다.

Attribute_name	내용
SQL%ROWCOUNT	가장 최근의 SQL문에 의해 처리된 ROW의 수
SQL%FOUND	가장 최근의 SQL문에 의해 처리된 ROW의 수가 있으면 True
SQL%NOTFOUND	가장 최근의 SQL문에 의해 처리된 ROW의 수가 없으면 True
SQL%ISOPEN	PL/SQL은 Implicit 커서를 사용한 후 즉시 닫으므로 항상 False

SQL%ISOPEN은 트랜잭션 관리할 때 주로 쓰인다.

Implicit 커서는 DECLARE, OPEN, FETCH, CLOSE 총 4단계의 명령에 의해 사용된다.

DECLARE 단계
- 커서에 이름을 부여하고, 커서 내에서 수행할 SELECT문을 정의함으로써 커서를 선언한다.
- SELECT 문장에 INTO절이 포함되지 않는다.
```
DECLARE
CURSOR cursor_name IS
  SELECT 문장;
```
OPEN 단계

OPEN cursor_name;

OPEN문은 참조되는 변수를 연결하고 SELECT문을 실행한다.
SELECT로 검색된 ROW들을 "ACTIVE SET"이라고 하며, FETCH 단계에서 사용할 수 있게 한다.
커서는 Active Set을 생성한 후, 첫 Row에 Pointer를 위치시켜 가리킨다. 이때 검색된 ROW가 없어도(SQL%NOTFOUND = TRUE) 에러를 발생하지 않는다.
FETCH를 한 후에 커서의 Attribute를 살펴서 알아볼 수 있다.

3. FETCH 단계(FETCH = 가져옴)

FETCH cursor_name INTO att1, att2, ...;

FETCH문은 커서로부터 포인터가 존재하는 레코드의 값을 변수에 전달해주고, 포인터는 Active Set의 다음 레코드로 이동한다.
커서를 선언할 때 사용된 SELECT문장의 컬럼과 같은 개수, 같은 데이터 타입의 변수를 FETCH문의 INTO절에 기술한다.
FETCH문에서는 FETCH 될 레코드가 없는 경우에도 에러를 발생하지 않으므로, 레코드를 포함하고 있는 지 확인해야 한다.

4. CLOSE 단계

CLOSE cursor_name;

CLOSE문은 레코드의 Active Set을 닫아 주고, 다시 새로운 Active Set을 만들어 OPEN할 수 있게 해준다.

Explicit Cursor

Explicit Cursor의 Attribute는 Implicit Cursor의 Attribute와 같다.
단, Implicit의 경우에는 '가장 최근의 SQL문에 의해 처리된' 기준이라면 Explicit은 'FETCH문에 의해 검색된 데이터' 기준이다.

Implicit VS Explicit

우선, Implicit 커서의 사용 예시를 보도록 하자.

CREATE OR REPLACE PROCEDIRE del_ord
(v_ord_id   s_item.ord_id%TYPE )
IS BEGIN
    DELETE FROM s_item
    WHERE       ord_id = v_ord_id;
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE(
            TO_CHAR(SQL%ROWCOUNT, '9,999') || '개의 행이 삭제됨'
        );
    ELSE
        DBMS_OUTPUT.PUT_LINE('내역없음');
    END IF
END;
/

SQL%FOUND, SQL%ROWCOUNT는 'delete from s_item where ~'에서 도출된 2개의 줄을 기준으로 한다.

SQL> EXECUTE del_ord(110)
2개의 행이 삭제됨

다음은 Explicit 커서의 사용 예시이다.

CREATE OR REPLACE PROCEDURE show_ordtotal
(v_ord_id IN s_item.ord_id%TYPE)
IS
    v_product_id    s_item.product_id%TYPE;
    v_item_total    NUMBER(11,2);
    v_total         NUMBER(11,2) := 0; -- 디폴트 값 부여
CURSOR ordtotal_cursor IS
    SELECT product_id, price * quantity
    FROM    s_item
    WHERE   ord_id = v_ord_id;
BEGIN
    OPEN    ordtotal_cursor;
    LOOP
        FETCH   ordtotal_cursor INTO v_product_id, v_item_total;
        EXIT    WHEN    ordtotal_cursor%NOTFOUND
        DBMS_OUTPUT.PUT_LINE(
            TO_CHAR(v_product_id, '999999') || ' ' || TO_CHAR(v_item_total, '9,999,999'));
        v_total := v_total + v_item_total;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('총금액:'||TO_CHAR(v_total, '999,999,999.99'));
    CLOSE ordtotal_cursor;
END;
/

- IS 섹션과 BEGIN 섹션 사이에 ordtotal_cursor라는 Explicit 커서를 생성하였다.

SQL> EXECUTE show_ordtotal(109);
10011 21,000
10012 105,000
총금액: 126,000.00

CURSOR FOR LOOP
- CURSOR FOR LOOP는 Explicit 커서 안에 있는 모든 행들을 처리한다. 따라서, 커서의 FETCH문에서 모든 데이터를 다 처리하기 위해 %NOTFOUND 속성을 이용해야 할 때 유용하다.
- 이 커서는 내부적으로는 open되고, fetch가 이루어지고 난 뒤 close되기 때문에 DECLARE절에서 선언만 하고 사용하면 된다.
- 커서의 데이터를 읽어 올 변수를 굳이 선언할 필요가 없다.

FOR record_name IN cursor_name LOOP
문장들;
...
END LOOP;

다음의 예시를 통해 CURSOR FOR LOOP가 어떤 식으로 사용되는지 파악해보자.

CREATE OR REPLACE PROCEDURE show_emp
IS CURSOR emp_cursor IS
SELECT name, salary, title
FROM s_emp;
BEGIN
    FOR emp_record IN (SELECT name, sal FROM s_emp) LOOP
    DBMS_OUTPUT.PUT_LINE(emp_record.name || ' ' || emp_record.salary);
    END LOOP;
END;
/
--

지금까지 제어문, 예외처리, 커서에 대한 내용을 정리하였다.