11. 저장 프로시저 처리 SQL문#
저장 프로시저 처리 SQL문#
저장 프로시저 또는 저장 함수 관련 내장 SQL문을 저장 프로시저 처리 SQL문이라고 한다.
응용 프로그램 안에서 저장 프로시저 또는 저장 함수를 생성하고 실행할 수 있다.
CREATE#
저장 프로시저 또는 저장 함수를 생성한다.
구문#
저장 프로시저#
EXEC SQL CREATE [ OR REPLACE ] PROCEDURE
<procedure_name> ( [ <parameter_declaration_list> ] ) ]
AS | IS
[ <declaration_section> ]
BEGIN <statement>
[ EXCEPTION <exception_handler> ]
END
[ <procedure_name> ] ;
END-EXEC;
저장 함수#
EXEC SQL CREATE [ OR REPLACE ] FUNCTION
<function_name> [ ( [ <parameter_declaration_list> ] ) ]
RETURN <data_type>
AS | IS
[ <declaration_section> ]
BEGIN <statement>
[ EXCEPTION <exception_handler> ]
END
[ <function_name> ] ;
END-EXEC;
인자#
- <procedure_name>: 저장 프로시저의 이름
- <function_name>: 저장 함수의 이름
- <parameter_declaration_list>: Stored Procedures Manual 참조
- <declaration_section>: Stored Procedures Manual 참조
- <statement>: Stored Procedures Manual 참조
- <exception_handler>: Stored Procedures Manual 참조
- <data_type>: Stored Procedures Manual 참조
예제#
아래는 저장 프로시저 또는 저장 함수를 생성하는 예를 보여준다.
[예제 1] 다음은 저장 프로시저를 생성하는 예제이다.
ONO 칼럼 값이 ORDER_PROC의 파라미터인 s_ono와 같은 레코드를 검색하여 0건이면 지정한 변수 값들을 이용하여 ORDERS 테이블에 새로운 레코드를 삽입하고, 1건이 검색되면 검색된 레코드의 PROCESSING칼럼 값을 'P'로 변경한다.
< 예제 프로그램 : psm1.sc >
EXEC SQL CREATE OR REPLACE PROCEDURE ORDER_PROC
(s_ono in bigint)
AS
p_order_date date;
p_eno integer;
p_cno bigint;
p_gno char(10);
p_qty integer;
BEGIN
SELECT ORDER_DATE, ENO, CNO, GNO, QTY
INTO p_order_date, p_eno, p_cno, p_gno, p_qty
FROM ORDERS
WHERE ONO = s_ono;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_order_date := SYSDATE;
p_eno := 13;
p_cno := BIGINT'7610011000001';
p_gno := 'E111100013';
p_qty := 4580;
INSERT INTO ORDERS
(ONO, ORDER_DATE, ENO, CNO, GNO, QTY)
VALUES
(s_ono, p_order_date, p_eno, p_cno, p_gno, p_qty);
WHEN OTHERS THEN
UPDATE ORDERS
SET PROCESSING = 'P'
WHERE ONO = s_ono;
END;
END-EXEC;
[예제 2] 다음은 저장 함수를 생성하는 예제이다.
ORDERS 테이블에 새로운 레코드를 삽입하고, ORDERS 테이블의 전체 레코드 건수를 검색하여 그 건수를 결과로 반환한다.
< 예제 프로그램 : psm2.sc >
EXEC SQL CREATE OR REPLACE FUNCTION ORDER_FUNC(
s_ono in bigint, s_order_date in date,
s_eno in integer, s_cno in char(13),
s_gno in char(10), s_qty in integer)
RETURN INTEGER
AS
p_cnt integer;
BEGIN
INSERT INTO ORDERS
(ONO, ORDER_DATE, ENO, CNO, GNO, QTY)
VALUES
(s_ono, s_order_date, s_eno, s_cno, s_gno, s_qty);
SELECT COUNT(*) INTO p_cnt FROM ORDERS;
RETURN p_cnt;
END;
END-EXEC;
ALTER#
저장 프로시저 또는 저장 함수를 재 컴파일 한다.
구문#
저장 프로시저#
EXEC SQL ALTER PROCEDURE <procedure_name> COMPILE;
저장 함수#
EXEC SQL ALTER FUNCTION <function_name> COMPILE;
인자#
-
<procedure_name>: 저장 프로시저의 이름
-
<function_name>: 저장 함수의 이름
설명#
저장 프로시저나 저장 함수를 재컴파일하여 유효한 상태로 만들어준다.
예제#
저장 프로시저 또는 저장 함수를 재컴파일 하는 예를 보여준다.
[예제 1] 다음은 ORDER_PROD 저장 프로시저를 재컴파일하는 예를 보여준다
EXEC SQL ALTER PROCEDURE ORDER_PROC COMPILE;
[예제 2] 다음은 ORDER_FUNC 저장 함수를 재컴파일하는 예를 보여준다.
EXEC SQL ALTER FUNCTION ORDER_FUNC COMPILE;
DROP#
저장 프로시저 또는 저장 함수를 삭제한다.
구문#
저장 프로시저#
EXEC SQL DROP PROCEDURE <procedure_name>;
저장 함수#
EXEC SQL DROP FUNCTION <function_name>;
인자#
-
<procedure_name>: 저장 프로시저의 이름
-
<function_name>: 저장 함수의 이름
예제#
저장 프로시저 또는 저장 함수를 삭제하는 예를 보여준다.
[예제 1] 다음은 ORDER_FUNC 저장 함수를 삭제하는 예를 보여준다.
< 예제 프로그램 : psm1.sc >
EXEC SQL DROP PROCEDURE ORDER_PROC;
[예제 2] 다음은 ORDER_FUNC 저장 함수를 삭제하는 예를 보여준다.
< 예제 프로그램 : psm2.sc >
EXEC SQL DROP FUNCTION ORDER_FUNC;
EXECUTE#
저장 프로시저 또는 저장 함수를 실행한다.
구문#
저장 프로시저#
EXEC SQL EXECUTE BEGIN
<procedure_name>
[ ( [ <:host_var> [ IN | OUT | IN OUT ] [ , … ] ] ) ];
END;
END-EXEC;
저장 함수#
EXEC SQL EXECUTE BEGIN
<:host_var> := <function_name>
[ ( [ <:host_var> [ IN | OUT | IN OUT ] [ , … ] ] ) ];
END;
END-EXEC;
인자#
-
<procedure_name>: 저장 프로시저의 이름
-
<function_name>: 저장 함수의 이름
-
<:host_var>
저장 프로시저 또는 함수 실행 시 필요한 IN, OUT 또는 IN/OUT 파라미터 (즉, 입력 또는 출력 호스트 변수 사용 가능) 저장 함수의 결과 값을 저장하기 위한 출력 호스트 변수
예제#
저장 프로시저 또는 저장 함수를 실행하는 예를 보여준다.
[예제 1] 다음은 저장 프로시저 ORDER_PROC를 실행하는 예를 보여준다.
< 예제 프로그램 : psm1.sc >
EXEC SQL BEGIN DECLARE SECTION;
long long s_ono;
EXEC SQL END DECLARE SECTION;
s_ono = 111111;
EXEC SQL EXECUTE
BEGIN
ORDER_PROC(:s_ono in);
END;
END-EXEC;
[예제 2] 다음은 저장 함수 ORDER_FUNC를 실행하는 예를 보여준다.
< 예제 프로그램 : psm2.sc >
EXEC SQL BEGIN DECLARE SECTION;
long long s_ono;
char s_order_date[19+1];
int s_eno;
char s_cno[13+1];
char s_gno[10+1];
int s_qty;
int s_cnt;
EXEC SQL END DECLARE SECTION;
s_ono = 200000001;
s_eno = 20;
s_qty = 2300;
strcpy(s_order_date, "19-May-03");
strcpy(s_cno, "7111111431202");
strcpy(s_gno, "C111100001");
EXEC SQL EXECUTE
BEGIN
:s_cnt := ORDER_FUNC(:s_ono in, :s_order_date in,
:s_eno in, :s_cno in, :s_gno in, :s_qty in);
END;
END-EXEC;
ANONYMOUS BLOCK#
저장 프로시저를 생성하지 않고 바로 실행할 수 있는 SQL문이다.
EXEC SQL EXECUTE
<<LABEL_NAME_OPTION>>
[DECLARE]
[ <declaration_section> ]
BEGIN
<statement>
[ EXCEPTION <exception_handler> ]
END
[ <label_name> ] ;
END-EXEC;
예제#
anonymous block을 사용하는 예제를 보여준다.
< 예제 프로그램 : anon.sc >
/* declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
char usr[64];
char pwd[64];
char conn_opt[1024];
long long s_ono;
EXEC SQL END DECLARE SECTION;
s_ono = 999999;
/* execute anonymous block */
EXEC SQL EXECUTE
<<LABEL1>>
DECLARE
p_order_date date;
p_eno integer;
p_cno bigint;
p_gno char(10);
p_qty integer;
BEGIN
SELECT ORDER_DATE, ENO, CNO, GNO, QTY
INTO p_order_date, p_eno, p_cno, p_gno, p_qty
FROM ORDERS
WHERE ONO = :s_ono;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_order_date := SYSDATE;
p_eno := 13;
p_cno := BIGINT'7610011000001';
p_gno := 'E111100013';
p_qty := 4580;
INSERT INTO ORDERS (ONO, ORDER_DATE, ENO, CNO, GNO, QTY)
VALUES (:s_ono, p_order_date, p_eno, p_cno, p_gno, p_qty);
WHEN OTHERS THEN
UPDATE ORDERS
SET ORDER_DATE = p_order_date,
ENO = p_eno,
CNO = p_cno,
GNO = p_gno,
QTY = p_qty
WHERE ONO = :s_ono;
END;
END-EXEC;
EXECUTE 문에서 배열 호스트 변수 사용#
EXECUTE 문으로 저장 프로시저를 실행할 때 배열 호스트 변수를 사용할 수 있다. 이는 하나의 EXECUTE 문으로 여러 개의 EXECUTE 문을 실행하는 효과를 주어 저장 프로시저의 실행 성능을 높일 수 있다.
EXECUTE 문에서 배열 호스트 변수를 사용하는 방법은 저장 프로시저 인자의 데이터 타입이 배열인지 아닌지에 따라 다르다.
저장 프로시저 인자의 데이터 타입이 배열이 아닐 때#
먼저, 저장 프로시저 인자의 데이터 타입이 배열이 아닐 때 EXECUTE 문에서 사용할 수 있는 배열 호스트 변수의 데이터형과 제한 사항에 대한 설명이다.
배열 호스트 변수로 사용할 수 있는 데이터형#
- 숫자형 타입의 배열
- 문자형 타입의 배열
- 구성 요소가 배열인 구조체
제한 사항#
-
입력 인자(IN)에서만 배열 호스트 변수를 사용할 수 있다.
-
출력 인자(OUT) 또는 입출력 공용 인자(IN/OUT)에는 배열 호스트 변수를 사용할 수 없다.
EXEC SQL BEGIN DECLARE SECTION; int var1[10]; int var2[10]; int var3[10]; EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE BEGIN PROC1(:var1 in, :var2 out, :var3 in out); // 에러 발생. OUT 인자에 배열 호스트 변수를 사용한 예 END; END-EXEC; -
저장 함수의 반환 값을 배열 호스트 변수에 저장할 수 없다.
EXEC SQL BEGIN DECLARE SECTION; int var1[10]; int var2[10]; int var3[10]; EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE BEGIN :var1 = FUNC1(:var2 in, :var3 in); // 에러 발생. 배열 호스트 변수에 저장 함수의 반환 값을 저장한 예 END; END-EXEC; -
배열 호스트 변수와 배열이 아닌 호스트 변수를 함께 사용할 수 없다.
EXEC SQL BEGIN DECLARE SECTION; int var1; int var2; int var3[10]; EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE BEGIN PROC1(:var1 in, :var2 in, :var3 in); // 에러 발생. 배열 호스트 변수와 배열이 아닌 호스트 변수를 함께 사용한 예 END; END-EXEC; -
위의 마지막 2 가지 제한 사항에 의해 저장 함수를 실행하는 EXECUTE 문에 배열 호스트 변수를 사용할 수 없다.
예제#
저장 프로시저의 입력 인자에 배열 호스트 변수를 사용하여 EXECUTE 문을 수행하는 예제이다.
예제 프로그램 : arrays2.sc
EXEC SQL BEGIN DECLARE SECTION;
char a_gno[3][10+1];
char a_gname[3][20+1];
EXEC SQL END DECLARE SECTION;
strcpy(a_gno[0], "G111100001");
strcpy(a_gno[1], "G111100002");
strcpy(a_gno[2], "G111100003");
strcpy(a_gname[0], "AG-100");
strcpy(a_gname[1], "AG-200");
strcpy(a_gname[2], "AG-300");
EXEC SQL EXECUTE
BEGIN
GOODS_PROC(:a_gno in, :a_gname in);
END;
END-EXEC;
저장 프로시저 인자의 데이터 타입이 배열일 때#
다음은 인자의 데이터 타입을 배열로 선언한 저장 프로시저를 EXECUTE 문에서 실행할 때, 배열 호스트 변수로 사용할 수 있는 데이터형과 제한 사항에 대한 설명이다.
인자의 데이터 타입을 배열로 선언한 저장 프로시저는 아래와 같은 경우를 말한다.
CREATE OR REPLACE PACKAGE PSM_PKG
AS
TYPE PSM_SCOL IS TABLE OF SMALLINT INDEX BY INTEGER;
TYPE PSM_ICOL IS TABLE OF INTEGER INDEX BY INTEGER;
TYPE PSM_LCOL IS TABLE OF BIGINT INDEX BY INTEGER;
TYPE PSM_RCOL IS TABLE OF REAL INDEX BY INTEGER;
TYPE PSM_DCOL IS TABLE OF DOUBLE INDEX BY INTEGER;
END;
/
CREATE OR REPLACE PROCEDURE PSM3_1(scol in PSM_PKG.PSM_SCOL,
icol in PSM_PKG.PSM_ICOL,
lcol in PSM_PKG.PSM_LCOL,
rcol in PSM_PKG.PSM_RCOL,
dcol in PSM_PKG.PSM_DCOL)
AS
i integer;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE PSM_TABLE';
i := scol.first();
LOOP
IF i IS null
THEN
exit;
ELSE
INSERT INTO PSM_TABLE (SCOL, ICOL, LCOL, RCOL, DCOL)
VALUES (scol(i), icol(i), lcol(i), rcol(i), dcol(i));
i := scol.next(i);
END IF;
END LOOP;
END;
/
배열 호스트 변수로 사용할 수 있는 데이터형#
| C 데이터형 | SQL 데이터형 |
|---|---|
| short | SQL_SMALLINT |
| int | SQL_INTEGER |
| long | SQL_BIGINT |
| long long | SQL_BIGINT |
| float | SQL_REAL |
| double | SQL_DOUBLE |
제한 사항#
-
윈도우즈와 32비트 리눅스 환경에서 BIGINT 배열의 인자를 사용하는 저장프로시저의 경우, 응용프로그램에서 배열 호스트 변수의 데이터 타입은 long이 아닌 long long을 사용해야 한다. 왜냐하면, long의 경우 윈도우즈와 32비트 리눅스 환경에서는 4바이트이기 때문이다.
-
C 데이터 타입과 SQL 데이터 타입이 서로 다른 경우, 에러가 발생하거나 잘못된 형변환이 이루어져 원하지 않는 동작이 발생할 수 있다.
CREATE OR REPLACE PACKAGE pkg1 AS TYPE type1 IS TABLE OF SMALLINT INDEX BY INTEGER; END; / // 저장 프로시저 출력 인자의 데이터 타입이 SQL_SMALLINT CREATE OR REPLACE PROCEDURE proc1( a OUT pkg1.type1 ) RETURN INTEGER AS BEGIN SELECT * BULK COLLECT INTO a FROM t1 ORDER BY c1; END; / EXEC SQL BEGIN DECLARE SECTION; char usr[10]; char pwd[10]; char conn_opt[1024]; double array1[array_size]; EXEC SQL END DECLARE SECTION; // EXECUTE 문에서 사용한 배열 호스트 변수는 double 형 EXEC SQL EXECUTE BEGIN proc1(:array1 out); END; END-EXEC; // 결과 Error : [-331900] The apre type and psm array type do not match. -
호스트 변수가 구조체이면 EXECUTE 문에서 배열 호스트 변수로 사용할 수 없다.
typedef struct argx { float c1; float c2; } argx; EXEC SQL BEGIN DECLARE SECTION; argx args2[10]; EXEC SQL END DECLARE SECTION; // EXECUTE 문에서 구조체인 배열 호스트 변수를 사용할 수 없다. EXEC SQL EXECUTE BEGIN proc1(:args2); END; END-EXEC; -
호스트 변수가 2차원 배열이면 EXECUTE 문에서 배열 호스트 변수로 사용할 수 없다.
EXEC SQL BEGIN DECLARE SECTION; float args2[10][10]; EXEC SQL END DECLARE SECTION; // EXECUTE 문에서 2차열 배열인 배열 호스트 변수를 사용할 수 없다. EXEC SQL EXECUTE BEGIN proc1(:args2); END; END-EXEC;
예제#
아래 예제를 위한 스키마이다.
schema.sql
CREATE OR REPLACE PACKAGE PSM_PKG
AS
TYPE PSM_SCOL IS TABLE OF SMALLINT INDEX BY INTEGER;
TYPE PSM_ICOL IS TABLE OF INTEGER INDEX BY INTEGER;
TYPE PSM_LCOL IS TABLE OF BIGINT INDEX BY INTEGER;
TYPE PSM_RCOL IS TABLE OF REAL INDEX BY INTEGER;
TYPE PSM_DCOL IS TABLE OF DOUBLE INDEX BY INTEGER;
END;
/
CREATE OR REPLACE PROCEDURE PSM3_1(scol in PSM_PKG.PSM_SCOL,
icol in PSM_PKG.PSM_ICOL,
lcol in PSM_PKG.PSM_LCOL,
rcol in PSM_PKG.PSM_RCOL,
dcol in PSM_PKG.PSM_DCOL)
AS
i integer;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE PSM_TABLE';
i := scol.first();
LOOP
IF i IS null
THEN
exit;
ELSE
INSERT INTO PSM_TABLE (SCOL, ICOL, LCOL, RCOL, DCOL)
VALUES (scol(i), icol(i), lcol(i), rcol(i), dcol(i));
i := scol.next(i);
END IF;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE PSM4(scol out PSM_PKG.PSM_SCOL)
AS
BEGIN
scol(1) := 1;
scol(2) := 2;
scol(3) := null;
scol(4) := 3;
scol(5) := 4;
END;
/
예제 프로그램 : psm3.sc
저장 프로시저 인자의 데이터 타입이 배열일 때, 배열 호스트 변수를 사용하여 EXECUTE 문을 수행하는 일반적인 예제이다.
/* declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
char usr[10];
char pwd[10];
char conn_opt[1024];
short sCol[ARRAY_SIZE];
int iCol[ARRAY_SIZE];
long lCol[ARRAY_SIZE];
float fCol[ARRAY_SIZE];
double dCol[ARRAY_SIZE];
EXEC SQL END DECLARE SECTION;
for (i = 0; i < ARRAY_SIZE; i++ )
{
sCol[i] = i + 1;
iCol[i] = i + 10;
lCol[i] = i + 100;
fCol[i] = i + 1.1;
dCol[i] = i + 1.01;
}
/* execute procedure */
EXEC SQL EXECUTE
BEGIN
PSM3_1(:sCol in,
:iCol in,
:lCol in,
:fCol in,
:dCol in);
END;
END-EXEC;
예제 프로그램 : psm4.sc
저장 프로시저 출력 인자가 널을 가진 배열일 때, 널을 확인하는 예제이다.
/* declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
char usr[10];
char pwd[10];
char conn_opt[1024];
short sCol[ARRAY_SIZE];
EXEC SQL END DECLARE SECTION;
for (i = 0; i < ARRAY_SIZE; i++ )
{
sCol[i] = 0;
}
/* execute procedure */
EXEC SQL EXECUTE
BEGIN
PSM4(:sCol out);
END;
END-EXEC;
if( SQLCODE == (int)-331898)
{
printf("sCol \n");
for(i=0; i < ARRAY_SIZE; i++)
{
if( APRE_SHORT_IS_NULL(sCol[i]) == true )
{
printf("NULL\n");
}
else
{
printf("%d\n", sCol[i]);
}
}
}
예제 프로그램#
psm1.sc#
$ALTIBASE_HOME/sample/APRE/psm1.sc 참고
실행 결과#
$ is -f schema/schema.sql
$ make psm1
$ ./psm1
<SQL/PSM 1>
------------------------------------------------------
[Create Procedure]
------------------------------------------------------
Success create procedure
------------------------------------------------------
[Execute Procedure]
------------------------------------------------------
Success execute procedure
------------------------------------------------------
[Drop Procedure]
------------------------------------------------------
Success drop procedure
psm2.sc#
$ALTIBASE_HOME/sample/APRE/psm2.sc 참고
실행 결과#
$ is -f schema/schema.sql
$ make psm2
$ ./psm2
<SQL/PSM 2>
------------------------------------------------------
[Create Function]
------------------------------------------------------
Success create function
------------------------------------------------------
[Execute Function]
------------------------------------------------------
31 rows selected
------------------------------------------------------
[Drop Function]
------------------------------------------------------
Success drop function
psm3.sc#
$ALTIBASE_HOME/sample/APRE/psm3.sc 참고
실행 결과#
$ is -f schema/schema.sql
$ make psm3
$ ./psm3
<SQL/PSM 3>
------------------------------------------------------------------
[Execute Procedure]
------------------------------------------------------------------
Success execute procedure PSM3_1
------------------------------------------------------------------
[Execute Procedure]
------------------------------------------------------------------
Success execute procedure PSM3_2
sCol iCol lCol fCol dCol
1 10 100 1.100000 1.010000
2 11 101 2.100000 2.010000
3 12 102 3.100000 3.010000
4 13 103 4.100000 4.010000
5 14 104 5.100000 5.010000
psm4.sc#
$ALTIBASE_HOME/sample/APRE/psm4.sc 참고
실행 결과#
$ is -f schema/schema.sql
$ make psm4
$./psm4
<SQL/PSM 4>
------------------------------------------------------------------
[Execute Procedure]
------------------------------------------------------------------
Error : [-331898] The fetched result contains a NULL value. or Fetch column value is NULL.
sCol
1
2
NULL
3
4