DBMS_SQL

DBMS_SQL 패키지의 기본 개념과 패키지 내의 프러시저를 사용하는 방법을 설명합니다.

개요

DBMS_SQL은 PSM으로 데이터 조작어(DML: Data Manipulation Language, 이하 DML), 데이터 정의어 (DDL: Data Definition Language) 등 Dynamic SQL 문장을 사용하기 위한 패키지입니다.

Dynamic SQL 문장은 PSM의 소스 안에 SQL 문장을 직접 사용하지 않고 나중에 PSM을 실행할 때, 문자 열로 전달하여 파싱되는 SQL 문장을 말합니다. 따라서 PSM을 생성할 때 존재하지 않았던 테이블에 대해 SELECT를 실행할 수도 있고, CREATE TABLE, DROP TABLE 등의 DDL 문장도 수행할 수 있습니다.

DBMS_SQL 패키지로 Dynamic SQL 문장을 실행하는 과정은 다음과 같습니다. 1. OPEN_CURSOR 함수를 이용하여 커서를 열고, 커서 ID를 얻습니다.

커서를 구분하기 위해 앞에서 얻은 커서 ID를 다른 함수의 파라미터로 전달합니다.
Dynamic SQL 문장을 파싱하기 위해 PARSE 함수를 호출합니다. 만약 실행할 Dynamic SQL 문장 안에 바인드해야 할 변수가 있는 경우 BIND_VARIABLE 함수를 호출해 변수를 바인드합니다.
실행 결과를 가져오기 전에 결과 컬럼의 타입을 정의하기 위해 DEFINE_COLUMN 함수를 호출합니다.
EXECUTE 함수를 이용하여 파싱된 SQL 문장을 실행합니다.
실행한 결과를 fetch하기 위해 FETCH_ROWS 함수를 실행합니다. fetch한 결과를 원하는 변수로 가져오 기 위해서는 COLUMN_VALUE 함수를 호출합니다.
모든 수행을 끝낸 후 커서를 종료하기 위해 CLOSE_CURSOR 함수를 실행합니다.

타입

본 절에서는 DBMS_SQL 패키지에 제공하는 별도 정의된 타입들을 알파벳 순으로 설명합니다.

DESC_REC

DESCRIBE_COLUMNS 프러시저를 통해서 컬럼 정보들을 가져올 때 사용되는 DESC_TAB 타입의 구성 요소들의 타입입니다.

DESC_REC 타입의 세부 내용은 다음과 같습니다.

프로토타입

TYPE DESC_REC IS RECORD
(
    col_type               BINARY_Integer,
    col_max_len            BINARY_Integer,
    col_name               VARCHAR2(32) ,
    col_name_len           BINARY_Integer,
    col_schema_name        VARCHAR2(32) ,
    col_schema_name_len    BINARY_Integer,
    col_precision          BINARY_Integer,
    col_scale              BINARY_Integer,
    col_charsetid          BINARY_Integer,
    col_charsetform        BINARY_Integer,
    col_null_ok            BOOLEAN
);

DESC_REC2

DESCRIBE_COLUMNS2 프러시저를 통해서 컬럼 정보들을 가져올 때 사용되는 DESC_TAB2 타입의 구성 요소들의 타입입니다.

DESC_REC2 타입의 세부 내용은 다음과 같습니다.

프로토타입

TYPE DESC_REC2 IS RECORD
(
    col_type                BINARY_INTEGER,
    col_max_len             BINARY_INTEGER,
    col_name                VARCHAR2(32767),
    col_name_len            BINARY_INTEGER,
    col_schema_name         VARCHAR2(32),
    col_schema_name_len     BINARY_INTEGER,
    col_precision           BINARY_INTEGER,
    col_scale               BINARY_INTEGER,
    col_charsetid           BINARY_INTEGER,
    col_charsetform         BINARY_INTEGER,
    col_null_ok             BOOLEAN);
);

DESC_TAB

DESCRIBE_COLUMNS 프러시저를 통해서 컬럼 정보들을 가져올 때 사용되는 배열 타입입니다.

DESC_TAB 타입의 세부 내용은 다음과 같습니다.

프로토타입

TYPE DESC_TAB IS TABLE OF DESC_REC INDEX BY BINARY_INTEGER;

DESC_TAB2

DESCRIBE_COLUMNS2 프러시저를 통해서 컬럼 정보들을 가져올 때 사용되는 배열 타입입니다.

DESC_TAB2 타입의 세부 내용은 다음과 같습니다.

프로토타입

TYPE DESC_TAB2 IS TABLE OF DESC_REC2 INDEX BY BINARY_INTEGER;

VARCHAR2A

VARCHAR2 타입 데이터로 구성된 테이블 타입입니다. DBMS_SQL.PARSE 프러시저에서 긴 SQL문장을 넣을때 사용됩니다.

VARCHAR2A 타입의 세부 내용은 다음과 같습니다.

프로토타입

TYPE VARCHAR2A IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

DATE_TABLE

DATE 타입 데이터로 구성된 테이블 타입입니다.

DATE_TABLE 타입의 세부 내용은 다음과 같습니다.

프로토타입

TYPE DATE_TABLE IS TABLE OF DATE INDEX BY BINARY_INTEGER;

NUMBER_TABLE

NUMBER 타입 데이터로 구성된 테이블 타입입니다.

NUMBER_TABLE 타입의 세부 내용은 다음과 같습니다.

프로토타입

TYPE NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

VARCHAR2_TABLE

VARCHAR2 타입 데이터로 구성된 테이블 타입입니다.

VARCHAR2_TABLE 타입의 세부 내용은 다음과 같습니다.

프로토타입

TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

프러시저

본 절에서는 DBMS_SQL 패키지에서 제공하는 프러시저를 알파벳 순으로 설명합니다.

BIND_ARRAY

주어진 커서의 SQL 문장에서 콜론(:)으로 시작하는 변수에 값들의 집합을 설정해주는 프러시저입니다. 변수의 값이 모두 설정되지 않은 상태로 EXECUTE 함수를 호출하면 예외가 발생합니다. 변수는 이름으로 찾을 수 있으므로, SQL 문장 내의 변수이름과 파라미터로 주어진 이름을 동일하게 호출해야 합니다.

BIND_ARRAY 프러시저는 테이블에 동시에 여러 로우를 삽입, 삭제, 변경하는 용도로 활용됩니다.

BIND_ARRAY 프러시저의 세부 내용은 다음과 같습니다.

프로토타입 BIND_ARRAY 프러시저는 설정할 변수의 타입에 따라 중복으로 선언(overloading)되어 있습니다. 해당되는 타입으로는 NUMBER, VARCHAR2, DATE의 인덱스 기반 테이블 타입이 있습니다.
NUMBER_TABLE

PROCEDURE BIND_ARRAY
(
    c        IN     INTEGER, 
    name     IN     VARCHAR2, 
    n_tab    IN     NUMBER_TABLE [, 
             index1   IN     PLS_INTEGER, 
             index2   IN     PLS_INTEGER]
);

– VARCHAR2_TABLE

PROCEDURE BIND_ARRAY
(
    c        IN     INTEGER, 
    name     IN     VARCHAR2, 
    n_tab    IN     VARCHAR2_TABLE [, 
             index1   IN     PLS_INTEGER, 
             index2   IN     PLS_INTEGER]
);

– DATE_TABLE

PROCEDURE BIND_ARRAY
(
     c        IN     INTEGER, 
     name     IN     VARCHAR2, 
     n_tab    IN     DATE_TABLE [, 
              index1   IN     PLS_INTEGER, 
              index2   IN     PLS_INTEGER]
);

파라미터

파라미터

설명

대상 커서

name

SQL 문장 내의 변수의 이름
이름에서 첫 문자 콜론(:)은 생략해도 됨

n_tab

SQL 문장 내의 변수에 설정할 값들의 집합 변수

index1

이 파라미터를 사용할 경우 인덱스 기반 테이블 내 바인딩할 영역의 시작점을 지정 ( ≥ 1)

index2

이 파라미터를 사용할 경우 인덱스 기반 테이블 내 바인딩할 영역의 끝점을 지정( ≥ index1)

예제

CREATE TABLE DEPT (
    DEPTNO NUMBER,
    DNAME VARCHAR(14)
);

DECLARE
    stmt VARCHAR2(200);
    deptno_array DBMS_SQL.NUMBER_TABLE;
    name_array DBMS_SQL.VARCHAR2_TABLE;
    c NUMBER;
    dummy NUMBER;
BEGIN
    deptno_array(1) := 10;
    deptno_array(2) := 20;
    deptno_array(3) := 30;
    deptno_array(4) := 40;
    deptno_array(5) := 50;
    deptno_array(6) := 60;
    name_array(1) := 'Architecture';
    name_array(2) := 'Frontend';
    name_array(3) := 'Core';
    name_array(4) := 'Management';
    name_array(5) := 'QMS';
    name_array(6) := 'Technical Support';

    stmt := 'insert into dept values(:deptno, :name)';
    c := DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_ARRAY(c, ':deptno', deptno_array);
    DBMS_SQL.BIND_ARRAY(c, ':name', name_array, 2, 5);
    dummy := DBMS_SQL.EXECUTE(c);

    DBMS_SQL.CLOSE_CURSOR(c);
END;
/

PSM completed.

select * from dept order by deptno;

    DEPTNO DNAME
---------- --------------
        10 Frontend
        20 Core
        30 Management
        40 QMS

4 rows selected.

BIND_VARIABLE

주어진 커서의 SQL 문장에서 콜론(:)으로 시작하는 변수에 값을 설정해주는 프러시저입니다. 변수의 값이 모두 설정되지 않은 상태로 EXECUTE 함수를 호출하면 예외가 발생합니다. 변수는 이름으로 찾을 수 있으므로, SQL 문장 내의 변수이름과 파라미터로 주어진 이름을 동일하게 호출해야 합니다.

BIND_VARIABLE 프러시저의 세부 내니다.

프로토타입 BIND_VARIABLE 프러시저는 설정할 변수의 타입에 따라 중복으로 선언(overloading)되어 있습니다. 해당되는 타입으로는 NUMBER, VARCHAR2, DATE, TIMESTAMP, INTERVAL, BINARY_FLOAT, BI NARY_DOUBLE, CLOB, BLOB이 있습니다.

– NUMBER

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   NUMBER
);

– VARCHAR2

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   VARCHAR2[, OUT_VALUE_SIZE IN INTEGER]
);

– DATE

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   DATE
);

– TIMESTAMP

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   TIMESTAMP_UNCONSTRAINED
);

– INTERVAL

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   YMINTERVAL_UNCONSTRAINED
);

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   DSINTERVAL_UNCONSTRAINED
);

– BINARY_FLOAT

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   BINARY_FLOAT
);

– BINARY_DOUBLE

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   BINARY_DOUBLE
);

– CLOB

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   CLOB
);

BLOB

PROCEDURE BIND_VARIABLE
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   BLOB
);

CHAR, RAW, ROWID 타입을 위해서는 별도의 프러시저를 사용해야 합니다.

CHAR

PROCEDURE BIND_VARIABLE_CHAR
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   CHAR[, out_value_size IN INTEGER]
);

– RAW

PROCEDURE BIND_VARIABLE_RAW
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   RAW[, out_value_size IN INTEGER]
);

– ROWID

PROCEDURE BIND_VARIABLE_ROWID
(
    c       IN   INTEGER,
    name    IN   VARCHAR2,
    value   IN   ROWID
);

파라미터

파라미터

설명

대상 커서

name

SQL 문장 내의 변수의 이름
이름에서 첫 문자 콜론(:)은 생략해도 됨

value

SQL 문장 내의 변수에 설정할 변수

out_value_size

지원되지 않는 기능이므로 값을 무시

예제

DECLARE
   csr INTEGER;
   x NUMBER := 1;
BEGIN
   csr := DBMS_SQL.OPEN_CURSOR();

   DBMS_SQL.PARSE(csr, 'select :var from dual', dbms_sql.native);

   DBMS_SQL.BIND_VARIABLE(csr, 'var', x);

   DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

CLOSE_CURSOR

주어진 커서를 닫고, NULL로 설정하는 프러시저입니다.

CLOSE_CURSOR 프러시저의 세부 내용은 다음과 같습니다.

프로토타입

PROCEDURE CLOSE_CURSOR
(
    c  IN OUT INTEGER
);

파라미터

파라미터

설명

대상 커서를 닫은 후에 NULL로 설정

예제

DECLARE
   csr INTEGER := DBMS_SQL.OPEN_CURSOR();
BEGIN
   DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

COLUMN_VALUE

주어진 커서에서 fetch한 컬럼의 값을 원하는 변수로 가져오는 프러시저입니다. 실제 fetch는 FETCH_ROWS 함수에서 일어나고, COLUMN_VALUE 프러시저는 fetch한 후 데이터를 가져오는 데 사용됩니다.

이때 DEFINE_COLUMN의 column 파라미터와 COLUMN_VALUE의 value 파라미터가 동일한 타입을 가 지도록 함수를 호출해야 합니다. 예를 들어 column 파라미터가 NUMBER인 DEFINE_COLUMN 프러시저를 호출했다면, COLUMN_VALUE 또한 value 파라미터의 타입이 NUMBER인 프러시저를 호출해야 합니다.

동일한 이름으로 타입에 따라 중복으로 선언된 프러시저가 많으므로, 암시적 타입 변환을 고려하여 주의깊게 사용해야 합니다. 이는 변수의 실제 타입이 같아야 하는 것이 아니라, 호출된 함수의 파라미터의 타입이 동일하게 맞춰져야 합니다는 의미입니다. 예를 들어 DEFINE_COLUMN_CHAR를 사용했다면, COLUMN_VAL UE_CHAR를 호출해야 합니다.

COLUMN_VALUE 프러시저의 세부 내용은 다음과 같습니다.

프로토타입 COLUMN_VALUE 프러시저는 값을 저장할 변수의 타입에 따라 중복으로 선언되어 있습니다. 해당되는 타입으로는 NUMBER, VARCHAR2, DATE, TIMESTAMP, INTERVAL, BINARY_FLOAT, BI NARY_DOUBLE, CLOB, BLOB이 있습니다.

– NUMBER

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   NUMBER
);

– VARCHAR2

PROCEDURE COLUMN_VALUE(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   VARCHAR2
);

– DATE

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   DATE
);

– TIMESTAMP

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   TIMESTAMP_UNCONSTRAINED
);

– INTERVAL

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   YMINTERVAL_UNCONSTRAINED
);

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   DSINTERVAL_UNCONSTRAINED
);

– BINARY_FLOAT

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   BINARY_FLOAT
);

– BINARY_DOUBLE

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   BINARY_DOUBLE
);

– BLOB

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   BLOB
);

CLOB

PROCEDURE COLUMN_VALUE(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   CLOB
);

CHAR, RAW, ROWID 타입을 위해서는 별도의 프러시저를 사용해야 합니다.

CHAR

PROCEDURE COLUMN_VALUE
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   CHAR[, column_error OUT NUMBER,
                           actual_length OUT INTEGER]
);

– RAW

PROCEDURE COLUMN_VALUE_RAW
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   RAW [, column_error OUT NUMBER,
                           actual\_length OUT INTEGER]
);

– ROWID

PROCEDURE COLUMN_VALUE_ROWID
(
   c           IN    INTEGER,
   position    IN    INTEGER,
   value       OUT   ROWID [, column_error OUT NUMBER]
);

파라미터

파라미터

설명

대상 커서

position

fetch할 컬럼 목록에서 상대적 위치 (1 이상)

value

가져올 컬럼을 저장할 변수

column_error

값이 NULL인 경우 1405
값이 절삭된 경우 1406

actual_length

실제로 가져온 컬럼의 길이

예제

CREATE TABLE PSM_TABLE (A VARCHAR2(3));
INSERT INTO PSM_TABLE VALUES('111');

DECLARE
    csr INTEGER;
    col VARCHAR2(2);
    val VARCHAR2(2);
    ret INTEGER;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();
    DBMS_SQL.PARSE(csr, 'select * from psm_table', dbms_sql.native);

    /* result value is truncated */
    DBMS_SQL.DEFINE_COLUMN(csr, 1, col, 2);

    ret := DBMS_SQL.EXECUTE(csr);
    ret := DBMS_SQL.FETCH_ROWS(csr);

    DBMS_SQL.COLUMN_VALUE(csr, 1, val);

    DBMS_OUTPUT.PUT_LINE('val=' || val || '.')

    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

val=11.

DEFINE_COLUMN

주어진 커서에서 fetch될 컬럼의 타입을 정의하는 프러시저입니다. 이 프러시저는 SELECT 문장에만 사용 할 수 있습니다.

DEFINE_COLUMN 프러시저의 세부 내용은 다음과 같습니다.

프로토타입

DEFINE_COLUMN 프러시저는 설정할 변수의 타입에 따라 중복으로 선언되어 있습니다. 해당되는 타입으로는 NUMBER, VARCHAR2, DATE, TIMESTAMP, INTERVAL, BINARY_FLOAT, BI NARY_DOUBLE, CLOB, BLOB이 있습니다.

NUMBER

PROCEDURE DEFINE_COLUMN
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    NUMBER
);

VARCHAR2

PROCEDURE DEFINE_COLUMN
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    VARCHAR2
   column_size   IN    INTEGER,
);

– DATE

PROCEDURE DEFINE_COLUMN
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    DATE
);

– TIMESTAMP

PROCEDURE DEFINE_COLUMN
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    TIMESTAMP_UNCONSTRAINED
);

– INTERVAL

PROCEDURE DEFINE_COLUMN
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    YMINTERVAL_UNCONSTRAINED
);

PROCEDURE DEFINE_COLUMN
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    DSINTERVAL_UNCONSTRAINED
);

– BINARY_FLOAT

PROCEDURE DEFINE_COLUMN 
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    BINARY_FLOAT
);

– BINARY_DOUBLE

PROCEDURE DEFINE_COLUMN 
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    BINARY_DOUBLE
);

– CLOB

PROCEDURE DEFINE_COLUMN 
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    CLOB 
);

BLOB

PROCEDURE DEFINE_COLUMN
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    BLOB 
);

CHAR, RAW, ROWID 타입을 위해서는 별도의 프러시저를 사용해야 합니다.

CHAR

PROCEDURE DEFINE_COLUMN_CHAR
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    CHAR
   column\_size   IN    INTEGER
);

PROCEDURE DEFINE_COLUMN_RAW
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    RAW
   column\_size   IN    INTEGER
);

ROWID

PROCEDURE DEFINE_COLUMN_ROWID
(
   c              IN    INTEGER,
   position       IN    INTEGER,
   column         IN    ROWID
);

파라미터

파라미터

설명

대상 커서

position

SELECT를 실행할 컬럼 목록에서의 상대적 위치
문장의 컬럼에서 첫 번째 위치를 1로 하며, 1 이상의 값을 입력

column

정의할 컬럼의 변수로, 타입만 참조하며 값은 관계없음

column_size

VARCHAR2, CHAR 타입에 대해 SELECT를 실행할 컬럼 값의 최대 길이. 단, 최대 길이를 초과한 결과 컬럼은 절삭하여 가져옴.

예제

CREATE TABLE PSM_TABLE (A VARCHAR2(3));
INSERT INTO PSM_TABLE VALUES('abc');

DECLARE
    csr INTEGER;
    v VARCHAR2(1);

BEGIN

    csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'select * from psm_table', dbms_sql.native);
    
    DBMS_SQL.DEFINE_COLUMN(csr, 1, v, 1);
    
    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

DESCRIBE_COLUMNS

주어진 열린 커서에서 결과 테이블의 컬럼들의 타입을 알려주는 프러시저입니다. 이 프러시저는 SELECT 문장에만 사용할 수 있으며, 반드시 PARSE 프러시저로 SELECT 문장을 명시한 후 사용해야 합니다.

DESCRIBE_COLUMNS 프러시저의 세부 내용은 다음과 같습니다.

프로토타입

PROCEDURE DESCRIBE_COLUMNS
(
   c              IN     INTEGER,
   col_cnt        OUT    INTEGER,
   desc_t         OUT    DESC_TAB
);

파라미터

파라미터

설명

대상 커서

col_cnt

SELECT 결과 테이블의 컬럼 개수

desc_t

각 컬럼의 정보들(desc_rec)의 리스트(desc_tab)

예제

CREATE TABLE PSM_TABLE (A VARCHAR2(3));
INSERT INTO PSM_TABLE VALUES('abc');

DECLARE
    csr INTEGER;
    col_cnt INTEGER;
    rec_tab DBMS_SQL.DESC_TAB;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'select * from psm_table', dbms_sql.native);

    DBMS_SQL.DESCRIBE_COLUMNS(csr, col_cnt, rec_tab);

    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

DESCRIBE_COLUMNS2

DESCRIBE_COLUMNS에서는 정보들 중 컬럼 이름을 최대 32bytes 밖에 수용하지 못하지만, 이 프러시 저는 32767bytes까지 수용하므로, 이 함수를 사용할 것을 권장합니다.

DESCRIBE_COLUMNS2 프러시저의 세부 내용은 다음과 같습니다.

프로토타입

PROCEDURE DESCRIBE_COLUMNS2
(
   c              IN     INTEGER,
   col_cnt        OUT    INTEGER,
   desc_t         OUT    DESC_TAB2
);

파라미터

파라미터

설명

대상 커서

col_cnt

SELECT 결과 테이블의 컬럼 개수

desc_t

각 컬럼의 정보들(desc_rec2)의 리스트(desc_tab2)

예제

CREATE TABLE PSM_TABLE (A VARCHAR2(3));
INSERT INTO PSM_TABLE VALUES('abc');

DECLARE

    csr INTEGER;
    col_cnt INTEGER;
    rec_tab DBMS_SQL.DESC_TAB2;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'select * from psm_table', dbms_sql.native);

    DBMS_SQL.DESCRIBE_COLUMNS2(csr, col_cnt, rec_tab);

    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

PARSE

주어진 SQL 문장을 파싱하는 프러시저입니다. 이때 DDL 문장인 경우 즉시 수행됩니다. 기존에 열려 있는 SQL 문장이 있다면 닫고 새로 입력된 SQL 문장을 파싱합니다.

일반적으로 VARCHAR2 타입의 문자열로 SQL문장을 실행하지만, 문자열 길이가 길 경우 CLOB 또는 VARCHAR2A 타입으로 작성할 수 있습니다.

PARSE 프러시저의 세부 내용은 다음과 같습니다.

프로토타입

– VARCHAR2

PROCEDURE PARSE
(
   c               IN     INTEGER,
   statement       IN     VARCHAR2,
   language_flag   IN     INTEGER DEFAULT NULL
);

– CLOB

PROCEDURE PARSE
(
   c               IN     INTEGER,
   statement       IN     CLOB,
   language_flag   IN     INTEGER DEFAULT NULL
);

– VARCHAR2A

PROCEDURE PARSE
(
   c               IN     INTEGER,
   statement       IN     CLOB,
   lb              IN     INTEGER,
   ub              IN     INTEGER,
   lfflg           IN     BOOLEAN,             
   language\_flag  IN     INTEGER DEFAULT NULL 
);

파라미터

파라미터

설명

대상 커서

statement

파싱할 대상이 되는 SQL 문장
PSM 문장과 달리 SQL 문장에서는 마지막 세미콜론(;)은 입력하지 않아도 됨

VARCHAR2A 구성 문자열들 중 하계값

VARCHAR2A 구성 문자열들 중 상계값

lfflg

TRUE이면 VARCHAR2A의 각 구성 문자열 끝마다 newline 문자가 추가됨

language_flag

지원되지 않는 기능이므로 값을 무시함

예제

CREATE TABLE PSM_TABLE (A VARCHAR2(3));

DECLARE
    csr INTEGER;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'select * from psm_table', dbms_sql.native);

    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

CREATE TABLE PSM_TABLE (A VARCHAR2(3));

DECLARE

    csr INTEGER;

    sql_arr DBMS_SQL.VARCHAR2A;
    r number;
    trec PSM_TABLE%ROWTYPE;
    lb number;
    ub number;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();

    sql_arr(1) := 'insert ';
    sql_arr(2) := 'into ';
    sql_arr(3) := 'PSM_TABLE ';
    sql_arr(4) := 'values';
    sql_arr(5) := '(';
    sql_arr(6) := '''abc''';
    sql_arr(7) := ')';

    lb := 1;
    ub := 7;
    DBMS_SQL.PARSE(csr, sql_arr, lb, ub, true, dbms_sql.native);

    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

함수

본 절에서는 DBMS_SQL 패키지에서 제공하는 함수를 알파벳 순으로 설명합니다.

EXECUTE

주어진 커서를 실행하는 함수입니다. INSERT, UPDATE, DELETE 문장인 경우 처리된 로우의 수가 반환됩니다.

위 문장이 아닐 경우 0이 반환된다.

EXECUTE 함수의 세부 내용은 다음과 같습니다.

프로토타입

FUNCTION EXECUTE
(
    c    IN   INTEGER
)
RETURN INTEGER;

파라미터

파라미터

설명

대상 커서

반환값

반환값

설명

INTEGER

INSERT, UPDATE, DELETE 문장인 경우 처리한 로우의 수를 반환

INSERT, UPDATE, DELETE 문장 이외의 경우에 반환

예제

CREATE TABLE PSM_TABLE (A VARCHAR(3));

DECLARE
    csr INTEGER;
    ret INTEGER;

BEGIN
     csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'insert into psm_table values(''abc'')', dbms_sql.native);

    ret := DBMS_SQL.EXECUTE(csr);

    DBMS_OUTPUT.PUT_LINE('affected row cnt = ' || ret || '.');

    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/
affected row cnt = 1.

EXECUTE_AND_FETCH

주어진 커서에 EXECUTE 와 FETCH_ROWS 함수를 연속해서 호출하는 것과 동일한 함수입니다. 반환되는값은 FETCH_ROWS와 동일합니다. 즉, fetch가 성공한 경우 1을, 그렇지 않으면 0을 반환합니다.

EXECUTE_AND_FETCH 함수의 세부 내용은 다음과 같습니다.

프로토타입

FUNCTION EXECUTE_AND_FETCH
(
    c       IN    INTEGER,
    exact   IN    BOOLEAN DEFAULT FALSE
)
RETURN INTEGER;

파라미터

파라미터

설명

대상 커서

exact

TRUE : 정확히 하나의 로우가 fetch되지 않으면 예외가 발생
FALSE : 여러 개의 ROW가 fetch되어도 예외가 발생하지 않음

반환값

반환값

설명

fetch할 로우가 더 이상 없는 경우에 반환

fetch에 성공한 경우에 반환

예제

CREATE TABLE PSM_TABLE (A VARCHAR(3));
INSERT INTO PSM_TABLE VALUES('abc');

DECLARE
    csr INTEGER;
    ret INTEGER;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'SELECT * FROM PSM_TABLE', dbms_sql.native);

    ret := DBMS_SQL.EXECUTE_AND_FETCH(csr);
    DBMS_OUTPUT.PUT_LINE('fetched row cnt = ' || ret || '.');

    LOOP
      ret := DBMS_SQL.FETCH_ROWS(csr);
      DBMS_OUTPUT.PUT_LINE('fetched row cnt = ' || ret || '.');
      exit when ret = 0;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/
fetched row cnt = 1.
fetched row cnt = 0.

FETCH_ROWS

주어진 커서에서 로우를 fetch하는 함수입니다. fetch가 성공한 경우 1을, 그렇지 않으면 0을 반환합니다. 더 이상 fetch할 로우가 없는데 이 함수를 계속 호출하게 되면 예외가 발생합니다.

FETCH_ROWS 함수의 세부 내용은 다음과 같습니다.

프로토타입

FUNCTION FETCH_ROWS
(
    c    IN    INTEGER
)
RETURN INTEGER;

파라미터

파라미터

설명

대상 커서

반환값

반환값

설명

fetch할 로우가 더 이상 없는 경우에 반환.

fetch에 성공한 경우에 반환

예제

CREATE TABLE PSM_TABLE (A VARCHAR2(3));
INSERT INTO PSM_TABLE VALUES('abc');

DECLARE
    csr INTEGER;
    ret INTEGER;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'SELECT * FROM PSM_TABLE', dbms_sql.native);

    ret := DBMS_SQL.EXECUTE(csr);
    LOOP
        ret := DBMS_SQL.FETCH_ROWS(csr);
        DBMS_OUTPUT.PUT_LINE('fetched row cnt = ' || ret || '.');
        exit when ret = 0;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/

fetched row cnt = 1.
fetched row cnt = 0.

OPEN_CURSOR

SQL 문장을 수행하기 위한 커서를 새로 하나 여는 함수입니다. 열린 커서는 사용 후에 반드시 close_cursor를 호출해야 리소스가 서버에 반납됩니다.

한번 열린 커서는 동일한 SQL 문장을 반복해서 수행할 수도 있고, 다른 SQL 문장을 실행하는 데 사용할 수도 있습니다. 커서를 여는 데 성공하면, 커서 ID를 INTEGER 타입으로 반환합니다. 이 값을 이후에 DBMS_SQL 패키지의 커서 파라미터로 사용하면 됩니다.

OPEN_CURSOR 함수의 세부 내용은 다음과 같습니다.

프로토타입

FUNCTION OPEN_CURSOR()
RETURN INTEGER;

예제

DECLARE
    csr INTEGER;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();
END;
/

IS_OPEN

주어진 커서가 DBMS_SQL.open_cursor 함수로 열릴 수 있는 커서인지 검사하는 함수입니다.

IS_OPEN 함수의 세부 내용은 다음과 같습니다.

프로토타입

FUNCTION IS_OPEN
(
   c    IN    INTEGER
)
RETURN BOOLEAN;

파라미터

파라미터

설명

대상 커서

반환값

반환값

설명

true

open 상태인 경우에 반환

false

open 상태가 아닌 경우에 반환

예제

DECLARE
    csr INTEGER := DBMS_SQL.OPEN_CURSOR();
    is_open BOOLEAN;
BEGIN
    is_open := DBMS_SQL.IS_OPEN(csr);

    if is_open then
        DBMS_OUTPUT.PUT_LINE('opened.');
    end if;
END;
/
opened.

LAST_ERROR_POSITION

파싱을 할 때 발생한 에러의 위치를 찾아주는 함수입니다. 단, 이 함수는 PARSE 프러시저를 호출한 직후에 사용해야 올바른 결과를 얻을 수 있습니다.

LAST_ERROR_POSITION 함수의 세부 내용은 다음과 같습니다.

프로토타입

FUNCTION LAST_ERROR_POSITION return INTEGER;

반환값

반환값

설명

INTEGER

파싱을 할 때 에러가 발생한 오프셋을 반환 (0부터 시작)

예제

DECLARE
    csr INTEGER;
    position INTEGER;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'select * from psm_not_exist_table', dbms_sql.native);

EXCEPTION
WHEN OTHERS THEN

    position := DBMS_SQL.LAST_ERROR_POSITION;

    DBMS_OUTPUT.PUT_LINE('error position = ' || position);
    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/
error position = 14

LAST_ROW_COUNT

현재 수행 중인 문장의 fetch된 로우 수의 총합을 반환하는 함수입니다. 단, 이 함수는 PARSE 프러시저를 호출한 직후에 사용해야 올바른 결과를 얻을 수 있습니다.

LAST_ROW_COUNT 함수의 세부 내용은 다음과 같습니다.

프로토타입

FUNCTION LAST\_ROW\_COUNT return INTEGER;

반환값

반환값

설명

INTEGER

현재 수행 중인 문장의 fetch된 로우 수의 총합 반환

예제

CREATE TABLE PSM_TABLE (A VARCHAR2(3));
INSERT INTO PSM_TABLE VALUES('111');
INSERT INTO PSM_TABLE VALUES('222');

DECLARE
    csr INTEGER;
    ret INTEGER;
    cnt INTEGER;
BEGIN
    csr := DBMS_SQL.OPEN_CURSOR();

    DBMS_SQL.PARSE(csr, 'SELECT * FROM PSM_TABLE', dbms_sql.native);

    ret := DBMS_SQL.EXECUTE(csr);
    LOOP
        ret := DBMS_SQL.FETCH_ROWS(csr);
        exit when ret = 0;
    END LOOP;

    cnt := DBMS_SQL.LAST_ROW_COUNT;

    DBMS_OUTPUT.PUT_LINE('last row count = ' || cnt);
    DBMS_SQL.CLOSE_CURSOR(csr);
END;
/
last row count = 2

PreviousDBMS_SPM NextDBMS_SQLTUNE

Last updated 15 days ago

hashtag개요

hashtag타입

hashtagDESC_REC

hashtagDESC_REC2

hashtagDESC_TAB

hashtagDESC_TAB2

hashtagVARCHAR2A

hashtagDATE_TABLE

hashtagNUMBER_TABLE

hashtagVARCHAR2_TABLE

hashtag프러시저

hashtagBIND_ARRAY

hashtagBIND_VARIABLE

hashtagCLOSE_CURSOR

hashtagCOLUMN_VALUE

hashtagDEFINE_COLUMN

hashtagDESCRIBE_COLUMNS

hashtagDESCRIBE_COLUMNS2

hashtagPARSE

hashtag함수

hashtagEXECUTE

hashtagEXECUTE_AND_FETCH

hashtagFETCH_ROWS

hashtagOPEN_CURSOR

hashtagIS_OPEN

hashtagLAST_ERROR_POSITION

hashtagLAST_ROW_COUNT

개요

타입

DESC_REC

DESC_REC2

DESC_TAB

DESC_TAB2

VARCHAR2A

DATE_TABLE

NUMBER_TABLE

VARCHAR2_TABLE

프러시저

BIND_ARRAY

BIND_VARIABLE

CLOSE_CURSOR

COLUMN_VALUE

DEFINE_COLUMN

DESCRIBE_COLUMNS

DESCRIBE_COLUMNS2

PARSE

함수

EXECUTE

EXECUTE_AND_FETCH

FETCH_ROWS

OPEN_CURSOR

IS_OPEN

LAST_ERROR_POSITION

LAST_ROW_COUNT