동적 SQL이란?

• 실행 시점 , 즉 런타임에 SQL문장이 달라지는 경우로 대표적으로 검색시, WHERE 절이 동적으로 추가되는 예시가 있다.

• 그 외에도 PL/SQL 블록 내에서 DDL 문을 실행하는 경우, 또는 PL/SQL 블록 내에서 ALTER SYSTEM/SESSION 명령어를 실행하여 세션별 파라미터 (ex) NLS_LANG : 언어 정보) 를 설정하는 경우에도 동적 SQL을 사용할 수 있다.

• PL/SQL에서 동적 SQL을 처리하는 방법은 2가지가 있다. 두 방법 모두 SQL문장 자체를 문자열 형태로 조합하여 실행한다.

1. Native Dynamic SQL (원시동적 SQL , NDS)
2. DBMS_SQL 시스템 패키지

NDS

• EXECUTE IMMEDIATE 문 : 가장 기본적인 동적 SQL 실행 형태이다.

• 구문은 다음과 같이 사용한다. INTO 다음에 결과값을 매핑할 OUT변수 , 동적으로 Binding 될 매개변수를 USING 절에 명시한다.

1
2
3

EXECUTE IMMEDIATE 'SQL문자열'
INTO  OUT변수 ...
USING 매개변수 ...

EXECUTE IMMEDIATE 실행 예제

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

DECLARE 
    /*출력값을 매핑할 변수*/
    vn_emp_id   employees.employee_id%TYPE;
    vs_emp_name employees.emp_name%TYPE;
    vs_job_id   employees.job_id%TYPE;
    
    vs_sql VARCHAR2(1000);
    
    /*Bind 변수 설정*/
    vs_job employees.job_id%TYPE         :='SA_REP';
    vn_sal employees.salary%TYPE         := 7000;
    vn_manager employees.manager_id%TYPE := 148;
BEGIN 
    vs_sql := '
                SELECT employee_id , emp_name , job_id 
                 FROM employees 
                WHERE job_id = :a
                  AND salary < :b
                  AND manager_id = :c
               ';
                         
    EXECUTE IMMEDIATE vs_sql 
    /*출력값을 매핑할 매개변수*/
    INTO vn_emp_id , vs_emp_name , vs_job_id
    /*조건전에 Binding 될 Bind 변수*/
    USING vs_job , vn_sal , vn_manager;

    DBMS_OUTPUT.PUT_LINE('emp_id : ' || vn_emp_id);
    DBMS_OUTPUT.PUT_LINE('emp_name: ' || vs_emp_name);
    DBMS_OUTPUT.PUT_LINE('job_id : ' || vs_job_id);
END;

Bind 변수

• Bind 변수를 사용하여 조건문을 런타임에 만들 수 있다.

• 상수를 사용하는 경우에는 오라클이 매번 실행 계획을 세운다. 만약 Bind 변수를 사용하는 경우에는 sql 구문이 변경되지 않으므로, 이전에 세웠던 실행 계획을 활용해 처리한다. 즉 성능적으로 더 빨라진다. 따라서 항상 Bind 변수를 사용하는게 좋다.

• 일반 SQL문에서는 순서와 타입으로만 매핑된다. 즉 이름은 가독성에만 영향을 끼치고 실제 실행은 USING절에 오는 순서와, 타입 기반으로 실행된다. ( 프로시저로 매핑할떄는 변수 이름까지 맞춰주어야 한다. )

1
2
3
4

WHERE test_condition1 = :바인드변수명1 
  AND test_condition2 = :바인드변수명2
..
USING 바인드변수1, 바인드변수2;

• 프로시저안에서 DDL문 실행은 불가능하지만, 동적 SQL을 사용하면 실행이 가능하다. (ALTER SESSION 명령문과 같이 세션 파라미터를 변경하고자 할때도 마찬가지이다. )

패키지

• 논리적 연관성이 있는 PL/SQL 타입 , 변수 , 상수 , 서브 프로그램 , 커서 , 예외 등의 항목을 묶어놓은 객체
• 컴파일 과정을 거쳐 DB 에 저장된다.
• 다른 프로그램에서 참조 가능하다. (WAR , JAR -> DB 패키지 호출 , DB 패키지내에서 다른 DB 패키지의 프로시저 호출 )
• 패키지의 하위 서브 프로그램 (Ex) 프로시저 ) 를 호출하면 해당 패키지 전체가 메모리에 올라간다.

패키지 구조

• 패키지는 선언부 (스펙) 과 본문 (바디) 두 부분으로 구성된다. 패키지는 선언부만 작성하고 컴파일하여 DB에 저장할 수 있다. 즉 본문은 나중에 작성하여도 된다.

패키지 선언부 구문

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE PACKAGE 패키지명 IS 
    TYPE_ 구문;
    상수명 CONSTANT 상수_타입;
    예외명 EXCEPTION
    변수명 변수_타입;
    커서 구문;

    FUNCTION 함수명(매개변수 IN 매개변수_타입 )
    RETURN 반환타입;

    FUNCTION 프로시저명(매개변수 [IN,OUT,INOUT]  매개변수_타입);
END 패키지명;

• 패키지 선언부는 데이터와 서브 프로그램 (프로시저,함수) 영역으로 나눌 수 있다.

패키지 본문 구문

• 패키지 바디는 선언부에 정의한 서브 프로그램 명세에 대한 구현체이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE PACKAGE BODY 패키지명 IS 
   TYPE_ 구문;
   상수명 CONSTANT 상수_타입;
   커서 구문;

   FUNCTION 함수명 (매개변수 IN 매개변수_타입) 
   RETURN 반환타입 IS 반환타입명 
   BEGIN 
   /*  함수 로직  */
   END 함수명;

   PROCEDURE 프로시저명 (매개변수 [IN,OUT,INOUT])
   IS
   BEGIN 
   /* 프로시저 로직 */
   END 프로시저명;
END 패키지명;  

Collection

• Record 와 같은 복합형 데이터 타입이며, 동시에 여러 로우에 해당되는 데이터를 가질 수 있다.
• 객체지향언어의 클래스와 유사하다. 클래스처럼 생성자를 통해 초기화를 할 수 있고 (연관배열은 불가능), built-in 함수와 procedure로 구성된 collection 메소드를 제공한다.
• method를 통해서 collection 내 값을 수정,삭제할 수 있다.

Collection의 종류

• Oracle에서 제공하는 Collection 타입은 구조에 따라 3가지로 나뉜다.

1. 연관 배열 (Associative Array)
2. VARRAY (Variable-Size Array)
3. 중첩 테이블

연관 배열 (Associative Array)

• 키-값 pair로 구성된 Collection. Key를 인덱스라고도 부르기 떄문에 index-by 테이블이라고도 한다.

• 연관 배열 선언 문법

1

TYPE 연관배열명 IS TABLE OF 값타입 INDEX BY 인덱스(키)타입;

주의할점은 값타입은 어떠한 타입도 올 수 있지만 , 인덱스(키) 타입은 문자형,PLS_INTEGER 타입만 올 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DECLARE
    -- 연관 배열 타입 선언 
    TYPE av_type IS TABLE OF VARCHAR2(40)
                    INDEX BY PLS_INTEGER;
    -- 연관 배열 변수 선언
    vav_test av_type;
BEGIN 
    -- 연관 배열에 값 할당
    vav_test(10) := 'value of key 10';
    vav_test(20) := 'value of key 20';
    
    -- 연관 배열의 값 조회
    DBMS_OUTPUT.PUT_LINE(vav_test(10));
    DBMS_OUTPUT.PUT_LINE(vav_test(20));
END;

• 위 예제에서 보다시피 연관 배열의 요소값은 연관배열변수명(키) 형태로 접근 가능하다.
• 연관배열에 값을 입력할때 마다 내부적으로는 인덱스 값을 기준으로 정렬된다.

VARRY (Variable Size Array , 가변 길이 배열)

• 연관 배열과 다르게 크기에 제한이 존재한다.
• 키 값을 개발자가 명시하는게 아니라 DB AUTO_INCREMENT처럼 자동으로 순번이 증가한다.
• VARRY 선언 문법

1

TYPE VARRY 명 IS VARRAY(최대요소개수) OF 값타입; -- ex) VARRY(10) : 최대 요소를 10개 가지는 가변 길이 배열 

만약 최대요수 개수보다 많은 요소를 선언하려고 하면 ORA-06532 오류가 터진다.

1
2
3
4
5
6
7
8

오류 보고 -
ORA-06532: Subscript outside of limit
ORA-06512: at line 1
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
           or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
           if necessary.

사용 예시를 보면 다음과 같다. 먼저 연관배열과 다르게 생성자로 VARRY 가변 길이 배열을 초기화하고 , 변수명(인덱스) 형태로 값을 조회 또는 값을 저장 할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

DECLARE 
    -- 5개의 문자형 값으로 이루진 VARRAY 선언
    TYPE va_type IS VARRAY(5) OF VARCHAR2(20);
    -- VARRY 변수 선언
    vva_test va_type;
    vn_cnt NUMBER := 0;
    
BEGIN 
    -- 생성자를 통해 VARRAY 가변 배열 초기화 
    vva_test := va_type('FIRST' , 'SECOND' , 'THIRD' ,'' ,''); -- 나머지 4,5번째 INDEX값은 NULL값이 된다. 
    
    LOOP
        vn_cnt := vn_cnt+1;
        IF vn_cnt > 5 THEN 
            EXIT;
        END IF;
        -- VARRAY 요소에 접근할때는 VARRAY변수명(인덱스) 로 가능하다.
        DBMS_OUTPUT.PUT_LINE(vva_test(vn_cnt));
    END LOOP;
    vva_test(1) := 'FIRST VALUE CHANGED';
    DBMS_OUTPUT.PUT_LINE(vva_test(1));
END;

중첩 테이블 (Nested Table)

• 요소 제한이 없으나 , 숫자형 인덱스만 사용 가능하다.

• 생성자를 사용하며 , 일반 테이블의 칼럼 타입으로 사용될 수 있다.

• 중첩 테이블 선언 문법

1

TYPE 중첩테이블명 IS TABLE OF 값타입; 

1
2
3
4
5
6
7
8
9
10
11

DECLARE 
    -- 중첩 테이블 선언
    TYPE nt_type IS TABLE OF VARCHAR2(10);
    
    -- 중첩 테이블 변수 선언
    vnt_test nt_type;
BEGIN
    -- 중첩 테이블 생성자 사용
    vnt_test := nt_type('FIRST','SECOND','THIRD','ETC');
    DBMS_OUTPUT.PUT_LINE(vnt_test(4));
END;

Record

• PL/SQL에서 제공하는 복합형 데이터 타입

사용자 정의형 레코드

• 레코드에서는 레코드를 이루고 있는 각 칼럼을 필드라고 부른다. 테이블과 유사하게 생겼지만 , 하나의 row만을 가질 수 있다.

  1 2 3 4

  TYPE 레코드명 IS RECORD ( 필드명 필드타입 [NOTNULL] [:= 디폴트값], 필드명 필드타입 [NOTNULL] [:= 디폴트값], )

• 실제 Record 타입을 선언하는 예시를 보면 다음과 같다. 이때 필드타입으로 %TYPE 문법을 사용할 수도 있다.

  1 2 3 4 5 6 7 8 9 10 11 12

  DECLARE -- 특정 레코드 타입 선언 TYPE depart_rect IS RECORD ( department_id NUMBER(6), --department_id departments.department_id%TYPE department_name VARCHAR2(80), parent_id NUMBER(6), manager_id NUMBER(6) ); -- 레코드 타입의 변수 선언 vr_dep depart_rect; BEGIN END;

• Record 타입 변수의 필드에 접근할때는 레코드타입변수.필드명으로 접근할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

DECLARE 
    -- 특정 레코드 타입 선언
    TYPE depart_rect IS RECORD (
        department_id   departments.department_id%TYPE,
        department_name departments.department_name%TYPE,
        parent_id       departments.parent_id%TYPE,
        manager_id      departments.manager_id%TYPE
    );
    -- 레코드 타입의 변수 선언
    vr_dep depart_rect; 
    vr_dep2 depart_rect;
BEGIN 
    -- 레코드 타입의 필드에 접근할때는 레코드타입변수.필드명으로 접근한다. 
    vr_dep.department_id   := 999;
    vr_dep.department_name := 'test department';
    vr_dep.parent_id       := 100;
    vr_dep.manager_id      := NULL;
    -- 레코드 타입 변수는 동일한 타입이면 다른 변수에 할당이 가능하다.
    vr_dep2                := vr_dep;

END;

• Record 타입 변수를 활용해 테이블에 INSERT 또는 UPDATE할 수 있다. 이떄 전제조건은 당연히 테이블의 칼럼 개수 , 타입과 레코드의 필드의 칼럼 개수, 타입이 동일해야한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE dep AS 
    SELECT 
        department_id ,
        department_name ,
        parent_id , 
        manager_id 
    FROM 
        DEPARTMENTS;

DECLARE 
    -- 특정 레코드 타입 선언
    TYPE depart_rect IS RECORD (
        department_id   departments.department_id%TYPE,
        department_name departments.department_name%TYPE,
        parent_id       departments.parent_id%TYPE,
        manager_id      departments.manager_id%TYPE
    );
    -- 레코드 타입의 변수 선언
    vr_dep depart_rect; 
BEGIN 
    -- 레코드 필드 값 초기화 
    -- 테이블에 레코드 타입으로 값 insert 
    INSERT INTO dep values vr_dep;
END;

Cursor

• 특정 SQL 문장을 처리한 결과를 담고 있는 영역 (PRIVATE SQL이라는 메모리 영역) 을 가르키는 일종의 포인터이다.

• 커서를 통해 처리된 SQL 문장의 결과 집합에 접근할 수 있다. 개별 로우에 순차적으로 접근하는 형태이다.

• 커서의 종류에는 묵시적 커서와 명시적 커서가 있는데, 묵시적 커서는 oracle 내부적으로 자동으로 생성되어 사용하는 커서이다. PL/SQL 블록에서 실행하는 SQL 문장이 실행될 때마다 자동으로 만들어져 사용된다. 명시적 커서는 개발자가 직접 정의해서 사용하는 커서를 뜻한다.

• 커서의 종류와 무관하게 Cursor의 라이프사이클은 open -> fetch -> close 3단계로 나누어 진행된다.

묵시적 Cursor

• 개발자가 Cursor의 동작에 관여할 수는 없지만, Cursor의 정보를 참조할 수는 있다.

1
2
3
4
5
6
7
8
9
10
11

SET SERVEROUTPUT ON;
DECLARE 
    vn_department_id employees.department_id%TYPE := 80;
BEGIN 
    UPDATE employees 
    SET emp_name = emp_name
    WHERE department_id = vn_department_id;
    
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); --SQL 커서의 ROWCOUNT 속성
    COMMIT;
END;

위 예제에서 보면 SQL 커서를 어디에도 선언하진 않았지만, 커서의 정보를 참조할 수는 있다.
기타 커서의 속성들은 다음과 같다.

• SQL%FOUND : 결과 집합의 행수가 1개 이상이면 TRUE 아니면 FALSE를 반환
• SQL%NOTFOUND : 결과 집합의 행수가 0개이면 TRUE 아니면 FALSE를 반환
• SQL%ROWCOUNT : 영향 받은 결과 집합의 행수를 반환 , 없으면 0을 반환

명시적 Cursor

1. Cursor 선언

1
2
3

CURSOR 커서명 (매개변수)
IS
SELECT 문장;

명시적 커서 선언은 위와 같이 가능하다. 이때 매개변수는 생략이 가능한데, 매개변수의 주 사용 목적은 SELECT Query의 WHERE 절에 들어갈 조건으로 사용된다고 한다.

2. Cursor Open

1

OPEN 커서명 (매개변수)

커서를 사용하려면 먼저 커서를 열어야 한다.

3. Cursor close

1

CLOSE 커서명;

커서를 닫는 다는 것은 말그대로 커서가 가르키는 결과 집합을 메모리에서 내린다는 뜻이다.

예외처리 구문

1
2
3
4

EXCEPTION WHEN 예외명1 THEN 예외처리 구문1
WHEN 예외명2 THEN 예외처리 구문2
...
WHEN OTHERS THEN 예외처리 구문n;

• case문과 같이 WHEN “처리할예외명” THEN “처리로직” 형태이다.
• 구체적인 예외명을 알 수 없을 떄는 OTHERS를 사용한다.

예외처리 예시

다음과 ORA-01476 예외가 터지는 프로시저 로직이 있다.

1
2
3
4
5
6
7

DECLARE
    vi_num NUMBER := 0;
BEGIN 
    vi_num := 10 /0;
    -- ORA-01476: divisor is equal to zero 
    DBMS_OUTPUT.PUT_LINE('Success!');
END;

이를 다음과 같이 예외 처리할 수 있다.

1
2
3
4
5
6
7
8
9

DECLARE
    vi_num NUMBER := 0;
BEGIN 
    vi_num := 10 /0;
    DBMS_OUTPUT.PUT_LINE('Success!');

EXCEPTION WHEN OTHERS THEN  -- ORA-01476예외를 잡아서 처리 
    DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다.');
END;

예외정보 참조

• 실제로는 ‘오류가 발생했습니다’와 같은 예외처리 문구는 예외를 해결하는데 아무런 도움도 주지 못할 것이다.

• 발생한 예외에 대한 정보가 필요하면 Oraclem에서는 SQLCODE , SQLERRM 이라는 내장함수를 제공한다.

• SQLCODE : 실행부에서 발생한 예외에 해당되는 코드를 반환한다. 즉 ORA-예외코드에서 예외 코드를 반환한다는 뜻이다.

• SQLERRM : 예외 메시지를 반환한다.

1
2
3
4
5

EXCEPTION WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE : ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE : ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;

• Oracle 내장 패키지 (DBMS_UTILITY) 에서 제공하는 FORMAT_ERROR_BACKTRACE라는 빌트인 함수를 사용하면 정확히 예외가 몇번쨰 줄에서 터졌는지도 알 수 있다.

1

DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

시스템 예외

• 예외처리시 OTHERS 외에도 시스템 예외명을 사용할 수 있다. 이렇게 미리 정의되어 있는 예외의 리스트는 다음과 같다.
  (https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/07_errs.htm)

• 따라서 위의 0으로 나눴을떄 터지는 예외도 미리 정의된 시스템 예외명으로 대체 가능하다.

사용자 정의 함수 생성

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE FUNCTION 함수 이름 (매개변수1,매개변수2, ...)
RETURN 데이터타입;
IS[AS]
    변수 선언
BEGIN 
    실행부
    RETURN 반환값;
[EXCEPTION 
    예외 처리부]
END [함수 이름];

사용자 정의 함수 예시

1
2
3
4
5
6
7
8
9
10
11

CREATE OR REPLACE FUNCTION my_mod (num1 NUMBER , num2 NUMBER )
    RETURN NUMBER
IS
    vn_remainder NUMBER:= 0; --나머지
    vn_quotient NUMBER:= 0; --몫;
BEGIN 
    vn_quotient  := FLOOR(num1/num2);
    vn_remainder := num1 - (num2*vn_quotient);
    RETURN vn_remainder;
END;
    

정의한 사용자 정의 함수를 사용할떄는 SELECT 절 또는 PL/SQL 블록 내에서 사용 가능하다. 다음은 SELECT 절에서 사용하는 예시이다.

1
2

SELECT my_mod(10,3) reminder 
    FROM DUAL;

프로시저 (Procedure)

• 함수의 경우 특정 연산을 수행한 뒤 값을 반환하지만 프로시저는 로직만 처리하고 값을 반환하지 않는다.
• 대표적인 사용예시는 테이블에서 데이터를 추출해 처리하고, 결과를 다른 테이블에 저장하거나 갱신하는 등 DML 작업을 수행한다.

• PL/SQL은 프로그래밍 언어에서 제공하는 다양한 처리문을 제공한다 (조건문,반복문 등)

IF문

• PL/SQL에서 제공하는 IF문의 구조는 다음과 같다.

1
2
3
4
5
6
7
8
9

-- 조건이 N개일 경우 
IF 조건1 THEN 
    조건 처리1;
ELSIF 조건2 THEN
    조건 처리2;
...
ELSE 
    조건 처리n;
END IF;

PL/SQL이란? (Procedural Language extension to sql)

• Oracle 에서 SQL을 확장하여 사용할 수 있도록 만든 절차적 프로그래밍 언어
• db에 직접 탑재되어 compile되고 실행된다.

기본 구조

기본 단위는 Block 으로 이루어진다.
Block은 선언부 , 실행부 , 예외처리부 로 구성되는데, 익명 블록과 이름이 있는 블록 (함수,프로시저,패키지) 로 나뉜다.

1
2
3
4
5
6
7

이름부 ## BLOCK의 명칭이 오는 자리 
IS (AS)
선언부 ## DECLARE로 시작되며, 실행부와 예외처리부에서 사용할 변수 정의
BEGIN 
실행부 ## 비즈니스 로직 
EXCEPTION
예외처리부 ## EXCEPTION 절로 시작되는 부분으로 비즈니스 로직을 처리하다가 오류가 생기면 실행되는 내용

• 기본구조에서 이름부와 예외처리부는 생략이 가능하며 , 이름부를 생략할 경우에는 익명 블록이라고 한다.

예제를 보면 다음과 같다.

1
2
3
4
5
6
7
8
9

SET SERVEROUT ON; -- 입출력 ON
SET TIMING ON; -- PL/SQL 블록 실행시간 출력 
DECLARE 
    vi_num NUMBER; --선언부
BEGIN 
    vi_num := 100; -- 실행부 
    DBMS_OUTPUT.PUT_LINE(vi_num);
END;