DBMS SQL

DBMS_SQL

DBMS_SQL 패키지는 동적 SQL을 사용하는 프로시저와 함수를 아래와 같이 제공한다.

프로시저 및 함수	설명
OPEN_CURSOR	커서를 오픈한다. PSM_CURSOR_OPEN_LIMIT 프로퍼티에서 오픈할 수 있는 커서의 최대 개수를 설정할 수 있다(기본값: 32개).
IS_OPEN	커서가 열려있는지 여부를 확인하여 결과를 반환한다.
PARSE	SQL 구문을 파싱한다.
BIND_VARIABLE	SQL 구문에 포함된 변수를 바인딩한다.
EXECUTE_CURSOR	커서를 실행한다.
DEFINE_COLUMN	커서에서 fetch될 칼럼의 타입을 정의한다. SELECT 구문에서만 사용한다.
FETCH_ROWS	커서에서 fetch하려는 행을 가져온다. SELECT 구문에서만 사용한다.
COLUMN_VALUE	커서에서 변수에 해당하는 칼럼의 값을 가져온다. SELECT 구문에서만 사용한다.
CLOSE_CURSOR	커서를 닫는다.
LAST_ERROR_POSITION	파싱할 때 발생한 에러 위치를 반환한다.

관련 프로퍼티

DBMS_SQL 패키지 관련 프로퍼티를 altibase.properties에 설정할 수 있다.

• PSM_CURSOR_OPEN_LIMIT

더 자세한 정보는 General Reference를 참고한다.

BIND_VARIABLE

SQL 구문에 포함된 변수를 바인딩한다.

구문

DBMS_SQL.BIND_VARIABLE(c, name, value);

파라미터

이름	입출력	데이터 타입	설명
c	IN	INTEGER	커서 번호
name	IN	VARCHAR2(128)	콜론(:)으로 시작하는 변수 이름
value	IN	VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE	언어 옵션(지원되지 않으므로, 어떤 값을 지정하여도 내부적으로 무시)

결과값

저장 프로시저이므로 결과값을 반환하지 않는다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( :b1 )', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success.

CLOSE_CURSOR

커서를 닫는다. 커서가 닫히지 않은 경우, 세션이 종료될 때 자동으로 닫힌다.

구문

DBMS_SQL.CLOSE_CURSOR(c);

파라미터

이름	입출력	데이터 타입	설명
c	IN	INTEGER	커서 번호

결과값

저장 프로시저이므로 결과값을 반환하지 않는다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.close_cursor( c );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success.

COLUMN_VALUE

커서에서 바인드 변수에 해당하는 칼럼의 값을 가져온다. SELECT 구문에서만 사용한다.

구문

DBMS_SQL.COLUMN_VALUE(c, position, column_value);

파라미터

이름	입출력	데이터 타입	설명
c	IN	INTEGER	커서 번호
position	IN	INTEGER	fetch할 칼럼의 상대적 위치. 1부터 시작한다.
column_value	OUT	VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE	칼럼의 값을 저장

결과값

저장 프로시저이므로 결과값을 반환하지 않는다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
loop
exit when dbms_sql.fetch_rows( c ) = 0;
dbms_sql.column_value(c, 1, c1);
println( 'fetch -> ' || c1 );
end loop;
end;
/
Create success.

iSQL> exec proc1;
0
fetch -> 999
Execute success.

DEFINE_COLUMN

커서에서 FETCH될 칼럼의 타입을 정의한다. SELECT 구문에서만 사용한다.

구문

DBMS_SQL.DEFINE_COLUMN(c, position, column_value);

파라미터

이름	입출력	데이터 타입	설명
c	IN	INTEGER	커서 번호
position	IN	INTEGER	칼럼의 위치. 1부터 시작한다.
column_value	IN	VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE	칼럼의 타입을 정의

결과값

저장 프로시저이므로 결과값을 반환하지 않는다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success.

EXECUTE_CURSOR

커서를 실행한다.

구문

BIGINT variable:=DBMS_SQL.EXECUTE_CURSOR(c);

파라미터

이름	입출력	데이터 타입	설명
c	IN	INTEGER	커서 번호

결과값

커서를 실행하여 처리된 레코드의 개수를 반환한다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( :b1 )', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
println( rc );
end;
/
Create success.

iSQL> exec proc1;
0
1
Execute success.

FETCH_ROWS

커서에서 fetch하려는 행(row)을 가져온다. SELECT 구문에서만 사용할 수 있다.

구문

INTEGER variable:=DBMS_SQL.FETCH_ROWS(c);

파라미터

이름	입출력	데이터 타입	설명
c	IN	INTEGER	커서 번호

결과값

fetch할 행(row)이 없으면 0을 반환하고, 있으면 1을 반환한다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
rc := dbms_sql.fetch_rows( c );
println( rc );
end;
/
Create success.

iSQL> exec proc1;
0
1
Execute success.

IS_OPEN

커서가 열려있는지를 확인하여 결과를 반환한다.

구문

BOOLEAN variable:=DBMS_SQL.IS_OPEN(c);

파라미터

이름	입출력	데이터 타입	설명
c	IN	INTEGER	커서 번호

결과값

커서가 오픈되었으면 TRUE, 오픈되지 않았으면 FALSE를 반환한다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
if dbms_sql.is_open( c ) = TRUE
then
println( 'cursor opened' );
else
println( 'invalid cursor' );
end if;
end;
/
Create success.

iSQL> exec proc1;
0
cursor opened
Execute success.

LAST_ERROR_POSITION

파싱할 때 발생한 에러의 위치를 반환한다.

이 함수는 PARSE 프로시저를 호출한 직후에 사용해야 올바른 결과를 얻을 수 있다.

구문

DBMS_SQL.LAST_ERROR_POSITION;

결과값

에러 위치를 반환한다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1( a varchar(128) )
as
c integer;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse( c, a, dbms_sql.native );
exception
 when others
  then
    println( dbms_sql.last_error_position );
    dbms_sql.close_cursor( c );
end;
/
Create success.
iSQL> exec proc1( 'select empno, ^a from emp' );
14
Execute success.

OPEN_CURSOR

커서를 연다.

구문

INTEGER variable:=DBMS_SQL.OPEN_CURSOR;

결과값

성공적으로 수행할 경우 커서의 번호를 반환한다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success

PARSE

SQL 구문을 파싱한다.

구문

DBMS_SQL.PARSE(c, sql, language_flag);

파라미터

이름	입출력	데이터 타입	설명
c	IN	INTEGER	커서 번호
sql	IN	VARCHAR2(32000)	파싱할 SQL
language_flag	IN	INTEGER	언어 옵션(지원되지 않으므로, 어떤 값을 지정하여도 내부적으로 무시)

결과값

저장 프로시저이므로 결과값을 반환하지 않는다.

예외

예외를 발생시키지 않는다.

예제

iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( 1 )', dbms_sql.native );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success.