프로토타입

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
);

프로토타입

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);
);

프로토타입

TYPE DESC_TAB IS TABLE OF DESC_REC INDEX BY BINARY_INTEGER;

프로토타입

TYPE DESC_TAB2 IS TABLE OF DESC_REC2 INDEX BY BINARY_INTEGER;

프로토타입

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

프로토타입

TYPE DATE_TABLE IS TABLE OF DATE INDEX BY BINARY_INTEGER;

프로토타입

TYPE NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

프로토타입

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

프로토타입

BIND_ARRAY 프러시저는 설정할 변수의 타입에 따라 중복으로 선언(overloading)되어 있다.

해당되는 타입으로는 NUMBER, VARCHAR2, DATE의 인덱스 기반 테이블 타입이 있다.

파라미터

예제

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;
/

프로토타입

BIND_VARIABLE 프러시저는 설정할 변수의 타입에 따라 중복으로 선언(overloading)되어 있다.

해당되는 타입으로는 NUMBER, VARCHAR2, DATE, TIMESTAMP, INTERVAL, BINARY_FLOAT, BINARY_DOUBLE, CLOB, BLOB이 있다.

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

파라미터

예제

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; 
/

프로토타입

PROCEDURE CLOSE_CURSOR
(
    c IN OUT INTEGER
);

파라미터

예제

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

프로토타입

COLUMN_VALUE 프러시저는 값을 저장할 변수의 타입에 따라 중복으로 선언되어 있다.

해당되는 타입으로는 NUMBER, VARCHAR2, DATE, TIMESTAMP, INTERVAL, BINARY_FLOAT, BINARY_DOUBLE, CLOB, BLOB이 있다.

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

파라미터

예제

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 프러시저는 설정할 변수의 타입에 따라 중복으로 선언되어 있다.

해당되는 타입으로는 NUMBER, VARCHAR2, DATE, TIMESTAMP, INTERVAL, BINARY_FLOAT, BINARY_DOUBLE, CLOB, BLOB이 있다.

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

파라미터

예제

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; 
/

프로토타입

PROCEDURE DESCRIBE_COLUMNS
(
    c              IN    INTEGER, 
    col_cnt        OUT   INTEGER,
    desc_t         OUT   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; 
/

프로토타입

PROCEDURE DESCRIBE_COLUMNS2
(
    c              IN    INTEGER, 
    col_cnt        OUT   INTEGER,
    desc_t         OUT   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; 
/

프로토타입

파라미터

예제

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;
/

프로토타입

FUNCTION EXECUTE
(
    c    IN    INTEGER
) 
RETURN INTEGER;

파라미터

반환값

예제

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.

프로토타입

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

파라미터

반환값

예제

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.

프로토타입

FUNCTION FETCH_ROWS
(
    c   IN   INTEGER
) 
RETURN INTEGER;

파라미터

반환값

예제

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.

프로토타입

FUNCTION OPEN_CURSOR() 
RETURN INTEGER;

예제

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

프로토타입

FUNCTION IS_OPEN
(
    c   IN   INTEGER
) 
RETURN BOOLEAN;

파라미터

반환값

예제

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.

프로토타입

FUNCTION LAST_ERROR_POSITION return INTEGER;

반환값

예제

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

프로토타입

FUNCTION LAST_ROW_COUNT return INTEGER;

반환값

예제

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