[PL/SQL] DBMS_SQL (1) : 처리순서

DBMS_SQL (1)

동적 SQL을 사용하는 또 다른 방법이다. NDS에 비해 많은 기능을 구현할 수 있지만, 사용법이 좀 복잡하다.

처리 순서

DBMS_SQL 패키지는 내부적으로 묵시적 커서를 이용해서 SQL문을 처리하는데, 총 8단계 과정도 커서를 처리하는 작업이라고 보면 된다.

1. 커서 열기 : OPEN_CURSOR

DBMS_SQL.OPEN_CURSOR (security_level IN INTEGER) RETURN INTEGER;

- security_level : 보안옵션. 생략이 가능하며 0, 1, 2가 올 수 있고 디폴트는 0

- 반환값 : 커서 ID값

2. 파싱

문자열 형태의 동적 sql문장을 파싱하는 과정.

PARSE 프로시저를 호출해 SQL문장을 이전 단계에서 열었던 커서ID 값과 연결하고 구문 검사를 수행한다.   

DBMS_SQL.PARSE
(
    C                           IN INTEGER,
    STATEMENT                   IN VARCHAR2,
    LANGUAGE_FLAG               IN INTEGER,
    EDITION                     IN VARCHAR2 DEFAULT NULL,
    APPLY_CROSSEDITION_TRIGGER  IN VARCHAR2 DEFUALT NULL,
    FIRE_APPLY_TRIGGER          IN BOOLEAN DEFAULT TRUE
);

- C : 파싱할 문장에 대한 커서ID로 OPEN_CURSOR 함수 반환값을 받는다

- STATEMENT : 동적 SQL문

- LANGUAGE_FLAG : DBMS_SQL 패키지에서 설정한 상수인 NATIVE(실제값1). 무조건 DBMS_SQL.NATIVE를 명시한다.

3. 바인드 변수 연결

바인드 변수가 없다면 이 단계는 건너 뛴다.

BIND_VARIABLE 프로시저와 BIND_ARRAY 프로시저를 호출하는 방법이 있는데 전자를 주로 사용한다.

DBMS_SQL.BIND_VARIABLE
(
    C       IN INTEGER,
    NAME    IN VARCHAR2,
    VALUE   IN <DATATYPE>
);

- C : 파싱할 문장에 대한 커서 ID로 OPEN_CURSOR 함수 반환값이 온다.

- NAME : 바인드 변수명

- VALUE : 바인드 변수의 실제 값

BIND_VARIABLE 프로시저는 EXECUTE IMMEDIATE 문에서 USING 다음에 기술하는 바인드 변수 역할을 하며, SQL문장에 기술한 바인드 변수 개수만큼 호출해야한다.        

DBMS_SQL.BIND_ARRAY
(
    C               IN INTEGER,
    NAME            IN VARCHAR2,
    <TABLE_VARIABLE>IN <DATATYPE>
    [INDEX1 IN INTEGER
     ,INDEX2 IN INTEGER]
);

- C : 파싱할 문장에 대한 커서 ID로 OPEN_CURSOR 함수 반환값이 온다.

- NAME : 바인드 변수명

- TABLE_VARIABLE : DBMS_SQL 패키지 내에 선언된 컬렉션 타입인 연관 배열 변수를 명시한다.

- INDEX1 : 컬렉션 타입인 TABLE_VARIABLE에 대한 최소 인덱스값

- INDEX2 : 컬렉션 타입인 TABLE_VARIABLE에 대한 최대 인덱스값

4. 결과 선택 컬럼 정의

받아올 SELECT의 컬럼 값에 해당하는 컬럼을 미리 정의해야하며, DEFINE_COLUMN 프로시저를 이용한다.

4-1. 컬럼 타입이 문자형이 아닐때      

DBMS_SQL.DEFINE_COLUMN
(
    C           IN INTEGER,
    POSITION    IN INTEGER,
    COLUMN      IN <DATATYPE>
);

4-2. 컬럼 타입이 문자형일때

DBMS_SQL.DEFINE_COLUMN
(
    C           IN INTEGER,
    POSITION    IN INTEGER,
    COLUMN      IN VARCHAR2 CHARACTER SET ANY_CS,
    COLUMN_SIZE IN INTEGER
);

- C : 파싱할 문장에 대한 커서 ID

- POSITION : SELECT 리스트의 컬럼 위치를 나타내는데 SELECT 키워드를 기준으로 왼쪽부터 1에서 시작.

- COLUMN : 컬럼 값을 받을 변수를 명시한다.

- COLUMN_SIZE : 문자형 컬럼이면 해당 컬럼의 문자열 길이를 명시한다.

5. 쿼리 실행      

DBMS_SQL.EXECUTE
(
    C   IN INTEGER
)
RETURN INTEGER;

- C : 파싱할 문장에 대한 커서 ID

- 반환값 : SQL문을 실행한 결과 로우 수를 반환. SELECT 의 결과가 5행이라면 5 반환

6. 결과 패치       

DBMS_SQL.FETCH_ROWS
(
    C   IN INTEGER
)
RETURN INTEGER;

- C : 파싱할 문장에 대한 커서 ID

- 반환값 : 전체 결과 로우 수가 아닌, 실제로 패치된 로우 수

7. 패치된 결과 값 받아오기

반드시 DEFINE_COLUMN을 호출했던 순서대롤 COLUMN_VALUE를 호출해야한다.     

DBMS_SQL.COLUMN_VALUE
(
    C           IN INTEGER,
    POSITION    IN INTEGER,
    VALUE       OUT <DATATYPE>
    [, COLUMN_ERROR OUT NUMBER]
    [, ACTUAL_LENGTH OUT NUMBER]
);

- C : 파싱할 문장에 대한 커서 ID

- POSITION : SELECT 리스트의 컬럼 위치를 나타내는데 SELECT 키워드를 기준으로 왼쪽부터 1에서 시작.

- VALUE : 컬럼 값을 받을 변수를 명시한다.

8. 커서 닫기        

DBMS_SQL.CLOSE_CURSOR
(
    C   IN OUT INTEGER
);

- C : 파싱할 문장에 대한 커서 ID