Transcript PowerPoint 프레젠테이션

IBM Software Group
DB2 UDB
Stored Procedure Guide
using SQL/PL
DB2 Post Support Team
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
C O N T E N T S
SP 기본 구조
SP 예제 코드
주석문
SP 이름과 매개 변수 선언
SPECIFIC 옵션
RESULT SETS 옵션
LANGUAGE 옵션
COMPOUND SQL 블록
지역 변수 선언 및 초기화
반환용 변수 선언 및 초기화
CONDITION 선언
CURSOR 선언
HANDLER 선언
NOT ATOMIC 옵션
2
ATOMIC 옵션
COMMIT, ROLLBACK, SAVEPOINT 문
변수 선언, 초기화, 값 할당
SQLCODE, SQLSTATE, 반환용 변수 선언
오류 CONDITION 선언
CURSOR 선언
오류 HANDLER 처리 로직 정의
LOGIC FLOW CONTROL 문
DYNAMIC SQL 문
GET DIAGNOSTICS 문
SIGNAL 문
TEMPORARY TABLE 정의
SECURITY 제어
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
SP 기본 구조
------------------------------------ 주석문
------------------------------------ 프로그램ID : INST01.SP01
-- 업 무 명 : 계약정보관리
------------------------------------ SP 및 매개 변수 선언
----------------------------------CREATE PROCEDURE inst01.sp01
( IN
p_deptno
CHAR(3),
OUT p_message
VARCHAR(100) )
------------------------------------ SPECIFIC NAME 설정
----------------------------------SPECIFIC DELETE_DEPT
------------------------------------ RESULT SET 개수 설정
----------------------------------DYNAMIC RESULT SETS 1
------------------------------------ SP 작성 언어 설정
----------------------------------LANGUAGE SQL
------------------------------------ 본문 시작
----------------------------------BEGIN --첫번재 BEGIN
------------------------------------ 반환용 변수 선언 및 초기화
----------------------------------DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE v_ret_value INT DEFAULT 0;
------------------------------------ CONDITION 선언
-----------------------------------------------------DECLARE c_EMP_child_rows_exist CONDITION FOR SQLSTATE '99001';
------------------------------------ CURSOR 선언
----------------------------------DECLARE C1 CURSOR FOR SELECT문;
------------------------------------ HANDLER 선언
----------------------------------DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET p_message = 'Unknown error, SQLSTATE: "' || SQLSTATE ||
'", SQLCODE=' || CHAR(SQLCODE);
SET v_ret_value = -1;
END;
------------------------------------ 로직 구현 (SQL구성 및 실행)
------------------------------------ Logic 처리
END --첫번째 END
------------------------------------ 로컬 변수 선언 및 초기화
----------------------------------DECLARE v_num_rows INT DEFAULT 0;
3
------------------------------------ 명령 종료 문자
----------------------------------@
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
SP 예제 코드
-- 이것은 SP 샘플 코드입니다.
CREATE PROCEDURE inst01.sp01
( IN p_deptno
OUT p_message
-- 사용자 정의 핸들러 선언
DECLARE EXIT HANDLER FOR c_EMP_child_rows_exist
BEGIN
SET p_message = ‘child EMPLOYEE rows exist.';
SET v_ret_value = -1;
END;
CHAR(3),
VARCHAR(100) )
SPECIFIC delete_dept
DYNAMIC RESULT SET 1
LANGUAGE SQL
-- Child table: EMPLOYEE
SELECT COUNT(1) INTO v_num_rows
FROM employee
WHERE workdept = p_deptno;
BEGIN
-- SQLCODE, SQLSTATE, 반환용 변수 선언
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_return INT DEFAULT 0;
-- EMPLOYEE 에 데이터 존재하는 경우, SIGNAL 발생
IF v_num_rows <> 0 THEN
SIGNAL c_EMP_child_rows_exist;
END IF;
-- MAIN 시작
body:BEGIN
-- DEPARTMENT 데이터 삭제
DELETE FROM department WHERE deptno = p_deptno;
-- 삭제된 ROW_COUNT 검사
GET DIAGNOSTICS v_num_rows = ROW_COUNT;
-- 지역 변수 선언
DECLARE v_num_rows INT DEFAULT 0;
-- CONDITION 선언
DECLARE c_EMP_child_rows_exist CONDITION FOR
SQLSTATE '99001';
-- CURSOR 선언
DECLARE c1 CURSOR FOR
SELECT * FROM department WHERE deptno = p_deptno;
-- 시스템 핸들러 선언
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET p_message = ‘오류 : SQLSTATE: ‘ ||
SQLSTATE ||’,’|| CHAR(SQLCODE);
SET v_ret_value = -1;
END;
4
-- 반환 메시지 지정
IF v_num_rows = 0 THEN
BEGIN
SET v_ret_value = 1;
SET p_message = ‘데이터가 없습니다 : ‘ || p_deptno;
END;
ELSE
SET p_message = p_deptno || ‘가 삭제되었습니다.’ ;
END IF;
END body;
OPEN c1;
RETURN v_ret_value;
END
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
주석문
여러 행에 걸친 주석문은 /* -- */ 를 이용하며, 한 행에 해당하는 주석문은 –- 를 이용하여 지정해도 됩니다.
/*********************************************************************************************************************************
** 프로그램ID
: inst01_sp01
** 업무명
: Sample Stored Procedure
** 프로그램명
: inst01_sp01.db2
** 최초 작성일 : 2004-06-08
** 최종 작성일 : 2004-06-08
** 최종 수정일 : 2004-06-08
** 개발자
: Kim Eun Sook
** 수정자
: Kim Eun Sook
** 입력값
: p_deptno (부서 코드)
** 출력값
: p_message (오류 메시지)
** 참고사항
: 없음
** 변경이력
: 없음
** 실행예제
: call inst01,sp01(‘E01’,?)
********************************************************************************************************************************/
CREATE PROCEDURE inst01.sp01 ( IN p_deptno
OUT p_message
SPECIFIC delete_dept
DYNAMIC RESULT SET 1
LANGUAGE SQL
CHAR(3),
VARCHAR(100) )
BEGIN
-- SQLCODE, SQLSTATE, 반환용 변수 선언
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_return INT DEFAULT 0;
-- SQLCODE 변수
-- SQLSTATE 변수
-- SP 실행 상태 변수
body:BEGIN
-- 지역 변수 선언
DECLARE v_num_rows INT DEFAULT 0;
5
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
SP 이름과 매개 변수 선언
SP 이름을 지정할 때는 스키마명을 명시하도록 합니다.
CREATE PROCEDURE TEST.SP01
(IN
IN
P_EMPID
INTEGER,
P_PERCENTINCR
DECIMAL(4,2),
OUT P_UPDATED_SALARY
INT )
스키마명이 생략된 경우 기본 스키마명은 레지스터리 변수인 “CURRENT SCHEMA” 에 의해 결정됩니다. CURRENT SCHEMA 가 지정되어 있지 않은
경우에는 현재 접속한 사용자의 ID 를 기본값으로 제공합니다. 사용자명이 INST01 이라면 다음의 경우에 INST01.SP01 로 해석됩니다.
CREATE PROCEDURE SP01
(IN
IN
P_EMPID
INTEGER,
P_PERCENTINCR
DECIMAL(4,2),
OUT P_UPDATED_SALARY
INT )
매개 변수는 모드, 변수 이름, 데이터 유형 등의 세 가지 부분으로 구성됩니다. 매개 변수의 모드에는 IN, OUT, INOUT의 세 가지가 있습니다.
CREATE PROCEDURE TEST.SP01
(IN
IN
P_EMPID
INTEGER,
P_PERCENTINCR
DECIMAL(4,2),
OUT P_UPDATED_SALARY
INT )
매개 변수가 없는 경우에는 () 만 입력하면 됩니다.
CREATE PROCEDURE TEST.SP02 ()
6
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
SPECIFIC 옵션
동일한 SP 이름을 가지고 있으나, 매개 변수의 개수가 다른 SP를 여러 개 생성할 수 있습니다. 이를 “PROCEDURE OVERLOADING”이라 합니다.
이 경우에는 SPECIFIC절을 지정하며, 이 이름을 이용하여 SP를 DROP 합니다. 지정되는 이름은 한 데이터베이스내에서 고유해야 합니다.
 매개 변수가 3개인 SP
CREATE PROCEDURE sum(IN p_a INTEGER,IN p_b INTEGER,OUT p_s INTEGER)
SPECIFIC sum_ab
LANGUAGE SQL
BEGIN
SET p_s = p_a + p_b;
END
 매개 변수가 4개인 SP
CREATE PROCEDURE sum(IN p_a INTEGER,IN p_b INTEGER,IN p_c INTEGER,OUT p_s INTEGER)
SPECIFIC sum_abc
LANGUAGE SQL
BEGIN
SET p_s = p_a + p_b + p_c;
END
CLP 를 이용하는 경우 다음과 같이 호출합니다.
CALL sum(100,200,?)
CALL sum(100,200,300,?)
7
CLP 를 이용하는 경우 다음과 같이 SPECIFIC NAME 을 이용하여 DROP 합니다.
DROP PROCEDURE sum(INTEGER,INTEGER,INTEGER)
DROP PROCEDURE sum_abc
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
RESULT SETS 옵션
다음과 같은 방법으로 한 개 이상의 Result Set을 반환할 수 있습니다. SP, CLP, java 등을 이용하여 반환된 결과 집합을 처리할 수 있습니다.



CREATE PROCEDURE문장 내에 DYNAMIC RESULT SETS 절을 기술합니다.
WITH RETURN절과 함께 커서를 선언합니다.
커서를 오픈한 채로 SP를 종료합니다.
CREATE PROCEDURE read_emp_multi()
SPECIFIC read_emp_multi
DYNAMIC RESULT SETS 2
LANGUAGE SQL
re: BEGIN
DECLARE v_comm DEC(9,2) DEFALUT 0.0;
DECLARE c_salary CURSOR WITH RETURN FOR
SELECT salary FROM employee;
DECLARE c_bonus CURSOR WITH RETURN FOR
SELECT bonus FROM employee;
DECLARE c_comm CURSOR
SELECT comm FROM employee;
CREATE PROCEDURE receive_multi (IN
p_dept CHAR(3),
OUT
p_names VARCHAR(100),
OUT
p_total
DECIMAL(9,2) )
SPECIFIC receive_multi
LANGUAGE SQL
rm: BEGIN
DECLARE v_fname VARCHAR(12) DEFAULT '';
DECLARE v_lname VARCHAR(15) DEFAULT '';
DECLARE v_salary DECIMAL(9,2) DEFAULT 0.0;
DECLARE v_rs1, v_rs2, v_rs3 RESULT_SET_LOCATOR VARYING;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
CALL emp_multi(p_dept);
ASSOCIATE RESULT SET LOCATOR (v_rs1, v_rs2) WITH PROCEDURE emp_multi;
ALLOCATE v_rsCur1 CURSOR FOR RESULT SET v_rs1;
ALLOCATE v_rsCur2 CURSOR FOR RESULT SET v_rs2;
SET p_names = 'The employees are:';
OPEN c_comm;
FETCH c_comm INTO v_comm;
WHILE ( SQLSTATE = '00000' ) DO
SET p_total = p_total + v_comm;
FETCH c_comm INTO v_comm;
END WHILE;
OPEN c_salary;
OPEN c_bonus;
RETURN p_total;
END re
8
WHILE (SQLSTATE = '00000') DO
SET p_names = p_names || v_fname || ' ' || v_lname || ' ';
FETCH FROM v_rsCur1 INTO v_fname;
FETCH FROM v_rsCur2 INTO v_lname;
END WHILE;
SET p_total = 0;
WHILE ( SQLSTATE = '00000' ) DO
SET p_total = p_total + v_salary;
FETCH FROM v_rsCur3 INTO v_salary;
END WHILE;
END rm
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
LANGUAGE 옵션
SP 를 작성하는 방법은 여러 가지가 있습니다.




SQL/PL
C, COBOL, FOTRAN, …
JAVA
OLE
SQL/PL 을 이용하여 SP를 개발하는 경우에는 LANGUAGE SQL 이라고 명시합니다.
CREATE PROCEDURE inst01.sp01
( IN p_deptno
OUT p_message
CHAR(3),
VARCHAR(100) )
SPECIFIC delete_dept
DYNAMIC RESULT SET 1
LANGUAGE SQL
BEGIN
-- SQLCODE, SQLSTATE, 반환용 변수 선언
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_return INT DEFAULT 0;
-- MAIN 시작
body:BEGIN
-- 지역 변수 선언
DECLARE v_num_rows INT DEFAULT 0;
-- CONDITION 선언
DECLARE c_EMP_child_rows_exist CONDITION FOR SQLSTATE '99001';
-- CURSOR 선언
DECLARE c1 CURSOR FOR SELECT * FROM department WHERE deptno = p_deptno;
9
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
COMPOUND SQL 블럭
SP의 본문은 한 개 이상의 COMPOUND SQL 블럭으로 구성됩니다. 각 블록의 형식은 다음과 같습니다. 연관된 SQL 문들은 한 블럭안에 코딩합니다. 블
록 내부에 변수, CURSOR, CONDITION, HANDLER, Flow Control 등을 코딩할 수 있습니다. 한 블록 내에서의 코딩 순서는 아래와 같습니다.
한 개 이상의 Compound SQL 블럭을 중첩하여 정의할 수 있습니다. 외부 블록에서는 내부 블럭에 선언된 변수를 사용할 수 없지만, 내부 블록에서는 외
부 블럭에 선언된 변수를 사용할 수 있습니다.
-- 외부 블록
레이블명: BEGIN
변수 선언
컨디션 선언
반환용 변수 선언
커서 선언
핸들러 선언
SQL문 및 각종 로직 구현 문장
-- 내부 블록 1
레이블명: BEGIN
변수 선언
컨디션 선언
반환용 변수 선언
커서 선언
핸들러 선언
SQL문 및 각종 로직 구현 문장
END 레이블명
-- 내부 블록 2
레이블명: BEGIN
변수 선언
컨디션 선언
반환용 변수 선언
커서 선언
핸들러 선언
SQL문 및 각종 로직 구현 문장
END 레이블명
END 레이블명
10
-- 외부 블록 시작
BEGIN
-- 외부 블록 변수
DECLARE v_outer1 INT;
DECLARE v_outer2 INT;
DECLARE v_outer3 INT;
-- 내부 블록 시작
BEGIN
-- 내부 블럭 변수
DECLARE v_inner1 INT;
DECLARE v_inner2 INT;
DECLARE v_inner3 INT;
-- 변수 값 할당
SET v_outer1 = 100;
SET v_inner1 = 200;
-- 외부 블록 변수 참조
-- 내부 블록 종료
END;
-- 변수 값 할당
SET v_outer2 = 300;
SET v_inner2 = 400;
-- 내부 블록 변수 참조 (오류 발생)
-- 외부 블록 종료
END
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
NOT ATOMIC 옵션
Compound 블록에 ATOMIC 옵션을 기술하지 않으면 기본적으로 NOT AUTOMIC 모드로 정의됩니다. NOT AUTOMIC 절은 선택적으로 사용할 수 있으나,
SQL 코드의 모호성을 피하기 위해 명시적으로 사용하는 것이 권장됩니다. NOT ATOMIC 옵션을 지정하면, 블록 내의 모든 SQL문은 독립적으로 간주되
므로, 오류가 발생한 SQL문은 무시됩니다. COMMIT / ROLLBACK 을 이용하여 제어할 수 있습니다.
CREATE PROCEDURE not_atomic_proc ()
SPECIFIC not_atomic_proc
LANGUAGE SQL
첫 번째 insert 문이 commit 된 후, 오류가
BEGIN NOT ATOMIC
발생하였으므로 atomic_test 테이블에는
DECLARE v_job VARCHAR(8);
한 건의 데이터가 이미 입력되었습니다.
INSERT INTO atomic_test(proc, res)
VALUES ('Not_Atomic_Proc','Before error test');
COMMIT;
$ db2 “delete from atomic_test”
SIGNAL SQLSTATE '70000';
PROC
RES
-------------------- --------------------
INSERT INTO atomic_test(proc, res)
VALUES ('Not_Atomic_Proc','After error test');
END
$ db2 "select * from atomic_test"
0 레코드가 선택됨.
$ db2 "call not_atomic_proc()“
SQL0438N 응용프로그램이 진단 텍스트 ""과(와) 함께 오류를 표시했습니다.
SQLSTATE=70000
CREATE TABLE ATOMIC_TEST
( PROC VARCHAR(20),
RES VARCHAR(20) )
$ db2 "select * from atomic_test"
PROC
RES
-------------------- -------------------Not_Atomic_Proc
Before error test
1 레코드가 선택됨.
11
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
ATOMIC 옵션
ATOMIC 옵션을 사용하면 Compound 블록 내의 문장들이 단일 문장으로 처리됩니다. 만약 블록 내의 임의의 문장을 실행하다가 오류가 발생하면 블록
내에서 이미 실행된 모든 문장들이 롤백 처리됩니다. ATOMIC 옵션을 가진 Compound 블록 내에서는 COMMIT, ROLLBACK, SAVEPOINTS 문장과
중첩된 ATOMIC Compound 블럭을 기술 할 수 없습니다.
CREATE PROCEDURE atomic_proc ()
SPECIFIC atomic_proc
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_job VARCHAR(8);
두 개의 insert 문이 모두 rollback 되므로
atomic_test 테이블에는 데이터가 한 건도
없습니다.
INSERT INTO atomic_test(proc, res)
VALUES ('Atomic_Proc','Before error test');
$ db2 “delete from atomic_test”
SIGNAL SQLSTATE '70000';
$ db2 "select * from atomic_test"
INSERT INTO atomic_test(proc, res)
VALUES ('Atomic_Proc','After error test');
PROC
RES
-------------------- --------------------
END
0 레코드가 선택됨.
$ db2 "call atomic_proc()“
SQL0438N 응용프로그램이 진단 텍스트 ""과(와) 함께 오류를 표시했습니다.
SQLSTATE=70000
$ db2 "select * from atomic_test"
PROC
RES
-------------------- -------------------0 레코드가 선택됨
12
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
COMMIT, ROLLBACK, SAVEPOINT 문
ROLLBACK문은 실행 중에 오류가 발생하면 한 UOW 단위로 그 실행을 취소하게 합니다. ROLLBACK TO SAVEPOINT 문을 이용하면 한 UOW 내에서
SAVEPOINT가 지정된 시점부터 ROLLBACK 이 요청된 시간 사이의 변경 부분만 실행을 취소할 수 있습니다. ROLLBACK 과 COMMIT 문은 ATOMIC
Compound 블럭내에서는 사용할 수 없습니다. SAVEPOINT문, ROLLBACK TO SAVEPOINT문, RELESE SAVEPOINT 문 등을 이용합니다
CREATE PROCEDURE bonus_incr ()
SPECIFIC bonus_incr
LANGUAGE SQL
bi: BEGIN
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
v_dept, v_actdept CHAR(3);
v_bonus DECIMAL(9,2);
v_deptbonus DECIMAL(9,2);
v_newbonus DECIMAL(9,2);
v_empno CHAR(6);
v_atend SMALLINT DEFAULT 0;
DECLARE c_sales CURSOR WITH HOLD FOR
SELECT workdept, bonus, empno
FROM employee
ORDER BY workdept;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_atend=1;
SAVEPOINT svpt_bonus_incr ON ROLLBACK RETAIN CURSORS;
SET v_actdept = v_dept;
SET v_deptbonus = 0;
WHILE ( v_actdept = v_dept ) AND ( v_atend = 0 ) DO
SET v_newbonus = v_bonus * 1.1;
UPDATE employee
SET bonus = v_newbonus
WHERE empno = v_empno;
SET v_deptbonus = v_deptbonus + v_newbonus;
FETCH c_sales INTO v_dept, v_bonus, v_empno;
END WHILE;
IF v_deptbonus <= 3000.00 THEN
COMMIT;
ELSE
ROLLBACK TO SAVEPOINT svpt_bonus_incr;
RELEASE SAVEPOINT svpt_bonus_incr;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET v_atend=1;
END IF;
OPEN c_sales;
GOTO nextdept;
FETCH c_sales INTO v_dept, v_bonus, v_empno;
nextdept:BEGIN
13
IF v_atend = 0 THEN
END IF;
END nextdept;
END bi
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
변수 선언, 초기화, 값 할당
DECLARE 문을 사용하여 변수를 정의할 수 있습니다. 각 변수의 정의는 변수 이름, 데이터 유형 및 기본값으로 구성됩니다. 변수는 반드시 BEGIN~END
사이의 블록에서 첫 부분에 선언되어야 합니다. 변수 이름은 SP에 사용된 테이블의 컬럼명과 구별되는 것이 좋습니다. 다음과 같이 접두어를 구별하여 매
개 변수인지 일반 변수인지를 구별하는 것도 좋습니다.


v_
p_
: 일반 변수 접두어
: 매개 변수 접두어
변수가 선언되면 NULL로 초기화 되며, DEFAULT절을 이용하여 특정 값으로 초기화할 수 있습니다.
CREATE PROCEDURE proc_with_variables
(IN p_empno CHAR(4),
OUT p_msg VARCHAR(100))
SPECIFIC proc_with_vars
LANGUAGE SQL
BEGIN
DECLARE
DECLARE
DECLARE
DECLARE
v_rcount INTEGER;
v_name CHAR(10) DEFAULT ‘’;
v_adate,v_another DATE;
v_total INTEGER DEFAULT 0;
-- Default값을 이용
SET v_total = v_total + 1;
SET v_name = ‘KIM’;
-- SET을 이용
SELECT MAX(EMPNO) INTO v_max FROM PLAYERS;
-- Select를 이용
VALUES CURRENT DATE INTO v_adate;
-- VALUES를 이용
VALUES CURRENT DATE, CURRENT DATE INTO v_adate,v_another;
-- 레지스터를 이용
DELETE FROM EMPLOYEE WHERE EMPNO = ‘000100’;
GET DIAGNOSTICS rcount = ROW_COUNT;
14
-- GET DIAGNOSTICS을 이용
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
SQLCODE, SQLSTATE, 반환용 변수 선언
SQLCODE와 SQLSTATE는 SQL문이 실행될 때마다 변경되는 값입니다. SQL문의 실행 결과의 성공 여부를 확인하기 위해 SQLCODE, SQLSTATE를 이
용하려면 그 값을 저장할 로컬 변수를 선언합니다.
SQLSTATE : ISO/ANSI SQL92 표준의 5자리 문자를 반환합니다.
00000, 00 : Successful completion
01
: Warning
02
: not found condition
SQLCODE : Database Product별 CODE 를 반환합니다.
0
+
-
: Executed successfully
: Successful completion but warning message
: Error Occurred
RETURN문장을 만나면 SP는 종료됩니다. OUT 모드로 선언된 매개 변수를 이용하여 값을 반환하려면 RETURN 문이 실행되기 전에 출력용 변수에 값을
할당해야 합니다. OUT 모드의 변수가 없어도 RETURN 문을 이용하여 기본적으로 한 개의 값을 반환할 수 있습니다. 명시적인 RETURN 문을 사용하지
않고, SP가 정상적으로 종료되면 0 이 반환됩니다. RETURN 문과 함께 반환될 변수를 선언합니다.
CREATE PROCEDURE simple_error
(IN p_empno CHAR(6),
OUT p_msg
VARCHAR(100))
SPECIFIC simple_error
LANGUAGE SQL
se: BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE v_lastname VARCHAR(15);
DECLARE v_ret INTEGER DEFAULT 1;
UPDATE employee SET midinit = ‘xx’
WHERE empno = p_empno;
SELECT lastname INTO v_lastname FROM EMPLOYEE
WHERE empno = p_empno;
IF (SQLCODE <> 0) THEN
SET p_msg = “update 오류”;
END IF;
SET p_msg = “update 성공”;
IF v_lastname = p_emplastname THEN
SET v_ret = 2;
END IF;
END se
15
CREATE PROCEDURE return_test ( IN p_empno
CHAR(6),
IN p_emplastname VARCHAR(15) )
SPECIFIC return_test
LANGUAGE SQL
rt: BEGIN
RETURN v_ret ;
END rt
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
오류 CONDITION 선언
오류 처리를 위해 SP에서 사용할 수 있는 미리 정의된 시스템 Condition은 다음의 세 가지가 있습니다.
 SQLEXCEPTION
: SQLSTATE의 첫번째 두 자리가 00,01,02가 아닌 경우
 SQLWARNING
: SQLSTATE의 첫번째 두 자리가 01 또는 SQLCODE가 양수인 경우( +100 제외)
 NOT FOUND
: SQLSTATE의 첫번째 두 자리가 02 또는 SQLCODE가 +100인 경우
DECLARE ~ CONDITION 문을 이용하여 사용자가 특정 SQLSTATE 에 대한 Condition을 정의할 수도 있습니다.
 시스템 사용 SQLSTATE : ‘0’ ~ ‘6’ or ‘A’ ~ ‘H’ 로 시작하는 Class code와 subclass code
 사용자 정의 SQLSTATE : ‘7’ ~ ‘9’ or ‘I’ ~ ‘Z’ 로 시작하는 Class code와 subclass code
시스템에 정의 Condition 또는 사용자 정의 Condition은 DECLARE~HANDLER문, SIGNAL문 등과 함께 사용되어 오류 발생시 처리 내역을 지정합니다,
CREATE PROCEDURE insert_update_department ()
SPECIFIC ins_upd_dept
LANGUAGE SQL
iud: BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE v_duplicate INT DEFAULT 0;
DECLARE v_num_rows INT DEFAULT 0;
DECLARE c_duplicate CONDITION FOR SQLSTATE '23505';
DECLARE c_too_many_rows CONDITION FOR SQLSTATE '99001';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE,SQLCODE INTO p_sqlstate_out,p_sqlcode_out FROM sysibm.sysdummy1;
DECLARE CONTINUE HANDLER FOR c_duplicate
SET v_duplicate = 1;
16
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
CURSOR 선언
한 건 이상의 데이터를 조회한 결과를 보관하기 위해 커서를 선언합니다. COMMIT 이후에도 커서의 포인터를 유지하려면 WITH HOLD 옵션을 이용하면
됩니다. POSITIONED UPDATE와 DELETE 를 지원합니다.
CURSOR 선언문에 CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP 레지스터를 포함한 경우, 모든 FETCH문에 동일한 값을 반환합니다. 레
레지스터의 값은 OPEN CURSOR 에서 결정됩니다.
CREATE PROCEDURE total_raise
(
IN
p_min
DEC(4,2),
IN
p_max
DEC(4,2),
OUT
p_total
DEC(9,2) )
SPECIFIC total_raise
LANGUAGE SQL
tr: BEGIN
DECLARE v_salary DEC(9,2);
DECLARE v_bonus DEC(9,2);
DECLARE v_comm DEC(9,2);
DECLARE v_raise DEC(4,2);
DECLARE v_job VARCHAR(15) DEFAULT 'PRES';
DECLARE SQLSTATE CHAR(5);
DECLARE c_emp CURSOR FOR
SELECT salary, bonus, comm
FROM employee
WHERE job != v_job;
OPEN c_emp;
SET p_total = 0;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;
WHILE ( SQLSTATE = '00000' ) DO
SET v_raise = p_min;
IF ( v_comm < 2000 ) THEN
SET v_raise = v_raise + 0.03;
ELSEIF ( v_comm < 3000 ) THEN
SET v_raise = v_raise + 0.02;
ELSE
SET v_raise = v_raise + 0.01;
END IF;
IF ( v_raise > p_max ) THEN
SET v_raise = p_max;
END IF;
SET p_total = p_total + v_salary * v_raise;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;
END WHILE;
CLOSE c_emp;
END tr
하나 이상의 행을 반환하는 경우에는 반드시 커서를 이용해야 합니다. 다음의 문장은 오류를 발생시킵니다. 첫 번째 한 건만 원하는 경우에는 커서를
정의하지 말고, FETCH FIRST 옵션을 이용하여 SELECT 하는 것이 좋습니다.
SET v_c1 = (SELECT c1 FROM t1); -- 한 건 이상인 경우 오류 발생.
SET v_c1 = (SELECT c1 FROM t1 FETCH FIRST 1 ROW ONLY); -- 한 건만 FETCH 하는 경우 효율적.
17
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
오류 HANDLER 처리 로직 정의
SP 내부에 SQL문을 실행하면서 발생되는 예외 사항에 대한 처리를 명시하는 방법입니다. 시스템에 미리 정의된 Condition 및 사용자가 정의한 Condition
에 대해 Handler 유형을 지정하고, 상황에 대한 처리 방법을 정의할 수 있습니다. Handler 로직을 다중 문장으로 처리하는 경우에는 BEGIN ~ END 로 표시
되는 Compound 블록을 사용합니다. Handler의 유형으로는 다음의 세 가지가 있습니다.
 EXIT
: Handler 내부의 SQL문을 실행하고, Compound 블럭의 끝부분을 실행합니다.
 CONTINUE : Handler 내부의 SQL문을 실행하고, Exception이 발생한 다음 문장부터 실행을 계속합니다.
 UNDO
: Handler 내부의 SQL문을 실행하고, Compound 블럭내에서 실행된 모든 문장을 롤백한 후, Compound 블럭의 끝부분을 실행합니다.
CREATE PROCEDURE simple_error (IN p_midinit CHAR, IN p_empno CHAR(6), OUT p_sqlstate_out CHAR(5), OUT p_sqlcode_out INT )
SPECIFIC simple_error
LANGUAGE SQL
se: BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE v_duplicate INT DEFAULT 0;
DECLARE c_duplicate CONDITION FOR SQLSTATE '23505';
DECLARE c_too_many_rows CONDITION FOR SQLSTATE '99001';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE,SQLCODE INTO p_sqlstate_out,p_sqlcode_out FROM sysibm.sysdummy1;
DECLARE CONTINUE HANDLER FOR c_duplicate
SET v_duplicate = 1;
VALUES (SQLSTATE, SQLCODE) INTO p_sqlstate_out, p_sqlcode_out;
UPDATE employee SET midinit = p_midinit WHERE empno = p_empno;
END se
18
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
Logic Flow Control 문
SP 본문의 로직을 구현하기 위해 사용되는 IF, CASE, GO TO, RETURN, WHILE, FOR, LOOP, ITERATE, LEAVE 문의 기본적인 사용 예는 다음과 같습니다.
-- IF 문
IF rating = 1 THEN
SET salary = salary * 1.10;
ELSEIF rating = 2 THEN
SET salary = salary * 1.05;
ELSE
SET salary = salary * 1.03;
END IF;
-- CASE 문
CASE rating
WHEN 1 THEN
SET SALARY = SALARY *1.10;
WHEN 2 THEN
SET SALARY = SALARY *1.05;
ELSE
SET SALARY = SALARY *1.03;
END CASE;
-- GO TO 문
IF v_service > (CURRENT DATE - 1 year) THEN
GOTO exit;
END IF;
SET v_new_salary = v_new_salary + 10;
exit:
SET p_adjusted_salary = v_new_salary;
-- WHILE 문
WHILE (v_current <= p_end) DO
SET v_temp = v_temp + v_current;
SET v_current = v_current + 1;
END WHILE;
19
-- LOOP 문
L1: LOOP
SELECT 1 INTO v_tmp FROM employee WHERE empno = v_current_id;
SET v_salary = p_salary * 2
IF (SQLCODE <> 0) THEN
LEAVE L1;
END IF;
END LOOP L1;
-- FOR 문
SET fullname=‘’;
FOR vl AS SELECT firstnme, midinit, lastname FROM employee DO
SET fullname = lastname || ',' || firstnme ||' ' || midinit;
INSERT INTO tnames VALUE (fullname);
END FOR
-- ITERATE / LEAVE 문
ins_loop: LOOP
FETCH c1 INTO v_dept, v_deptname, v_admdept;
IF at_end = 1 THEN
LEAVE ins_loop;
ELSEIF v_dept = 'D11' THEN
ITERATE ins_loop;
END IF;
INSERT INTO department VALUES ('NEW', v_deptname, v_admdept);
END LOOP ins_loop;
-- RETURN 문
SELECT manager INTO v_manager FROM org WHERE empno = p_empno;
IF v_manager = p_manager THEN
RETURN 1;
ELSE
RETURN -1;
END IF;
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
DYNAMIC SQL문
EXECUTE IMMEDIATE, PREPARE ~ EXECUTE 등의 동적 SQL문을 사용할 수 있습니다. 매개 변수 표시 문자인 ? 를 이용하여 PREPPARE와 EXECUTE
를 분리시키면 동일한 SQL문을 다시 PREPARE 하지 않으므로 성능을 향상시킬 수 있습니다.
동적 SQL에 대한 보안 및 인증 관계는 실행시에 평가됩니다. 동적 SQL을 사용하기 위해 적절한 권한이 필요합니다.
CREATE PROCEDURE change_mgr_bonus (IN p_bonus_increase DECIMAL, OUT p_num_changes INT )
SPECIFIC change_mgr_bonus
LANGUAGE SQL
cmb: BEGIN
DECLARE v_dynSQL VARCHAR(200);
DECLARE v_new_bonus DECIMAL;
DECLARE v_no_data SMALLINT DEFAULT 0;
DECLARE v_mgrno CHAR(6);
DECLARE v_bonus DECIMAL;
DECLARE v_stmt1 STATEMENT;
DECLARE c_managers CURSOR FOR SELECT e.empno, e.bonus FROM EMPLOYEE e, DEPARTMENT d WHERE e.empno=d.mgrno;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_data=1;
SET v_dynSQL = 'UPDATE EMPLOYEE SET BONUS=? WHERE EMPNO=?';
SET p_num_changes=0;
PREPARE v_stmt1 FROM v_dynSQL;
OPEN c_managers;
FETCH c_managers INTO v_mgrno, v_bonus;
WHILE (v_no_data=0) DO
SET p_num_changes = p_num_changes + 1;
SET v_new_bonus = v_bonus + p_bonus_increase;
EXECUTE v_stmt1 USING v_new_bonus, v_mgrno;
FETCH c_managers INTO v_mgrno, v_bonus;
END WHILE;
CLOSE c_managers;
END cmb
20
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
GET DIAGNOSTICS 문
GET DIAGNOSTICS 문은 실행한 SQL 문장과 연관된 정보를 수집하는데 사용합니다. ROW_COUNT 변수는 SELECT,INSERT,UPDATE,DELETE 등의 실
행 후 관련된 레코드 건수를 반환하며, MESSAGE_TEXT는 오류 메시지를 반환합니다. RETURN_STATUS 를 이용하면 SP를 실행한 후의 반환 코드를 확
인할 수 있습니다.
CREATE PROCEDURE get_diag (IN p_empno CHAR(6))
SPECIFIC get_diag
LANGUAGE SQL
gd: BEGIN
DECLARE v_rows INT DEFAULT -1;
DELETE FROM employee WHERE empno like p_empno || '%';
GET DIAGNOSTICS v_rows = ROW_COUNT;
RETURN v_rows;
END gd
CREATE PROCEDURE simple_error_message
( IN
IN
OUT
SPECIFIC simple_error_msg
LANGUAGE SQL
sem: BEGIN
CREATE PROCEDURE TESTIT (IN p_empno VARCHAR(6))
LANGUAGE SQL
BEGIN
DECLARE v_retval INTEGER DEFAULT 0;
DECLARE v_note VARCHAR(20);
CALL get_diag (p_empno);
GET DIAGNOSTICS v_retval = RETURN_STATUS;
SET v_note = EMPNO || ':[' || CHAR(v_retval) || ']';
INSERT INTO NOTE VALUES (v_note);
END
p_midinit
CHAR(10),
p_empno
CHAR(6),
p_error_message VARCHAR(300) )
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
GET DIAGNOSTICS EXCEPTION 1 p_error_message = MESSAGE_TEXT;
SET p_error_message = '';
UPDATE employee
SET midinit = p_midinit
WHERE empno = p_empno;
END sem
21
SQL0727N An error occurred during
implicit system action type "1".
Information returned for the error
includes SQLCODE "-204", SQLSTATE
"42704" and message tokens
"DB2ADMIN.EMPLOYEE".
SQLSTATE=56098
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
SIGNAL 문
시스템이 제공하는 SQL 오류 이외에 프로그램에서 사용자가 정의한 오류 Condition에 대한 Handler를 실행해야 하는 경우가 있습니다. SIGNAL 문을 이
용하여 사용자가 정의한 Condition 또는 SQLSTATE에 해당하는 처리 내역을 지정할 수 있습니다.
CREATE PROCEDURE insert_update_department (
IN
p_deptno
CHAR(3),
IN
p_deptname
VARCHAR(29),
IN
p_mgrno
CHAR(6),
IN
p_admrdept
CHAR(3),
IN
p_location
CHAR(16),
OUT p_sqlstate_out CHAR(5),
OUT p_sqlcode_out INT )
SPECIFIC ins_upd_dept
LANGUAGE SQL
iud: BEGIN
-- Declare variables
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE v_duplicate INT DEFAULT 0;
DECLARE v_num_rows INT DEFAULT 0;
-- Declare condition
DECLARE c_duplicate CONDITION FOR
SQLSTATE '23505';
DECLARE c_too_many_rows CONDITION FOR
SQLSTATE '99001';
-- Declare handlers
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE,SQLCODE
INTO p_sqlstate_out,p_sqlcode_out
FROM sysibm.sysdummy1;
DECLARE CONTINUE HANDLER FOR c_duplicate
SET v_duplicate = 1;
22
-- Initialize output parms
VALUES (SQLSTATE, SQLCODE)
INTO p_sqlstate_out,p_sqlcode_out;
-- See how many rows are already in the DEPARTMENT table
SELECT COUNT(1) INTO v_num_rows
FROM department;
-- Signal an error if more than 10 rows exist
IF v_num_rows > 10 THEN
SIGNAL c_too_many_rows
SET MESSAGE_TEXT = 'DEPARTMENT 에 데이터가 너무 많습니다.';
END IF;
-- Try insert, if duplicate, then update
INSERT INTO department
( deptno,deptname,mgrno,admrdept,location )
VALUES
( p_deptno,p_deptname,p_mgrno,p_admrdept,p_location);
IF v_duplicate = 1 THEN
-- only update if non-null value provided as input parameter
UPDATE department
SET deptname = coalesce(p_deptname, deptname)
,mgrno = coalesce(p_mgrno, mgrno)
,admrdept = coalesce(p_admrdept, admrdept)
,location = coalesce(p_location, location)
WHERE deptno = p_deptno;
END IF;
END iud
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
TEMPORARY TABLE
Temporary Table은 한 세션 동안에만 존재하는 임시 테이블입니다. Connection이 종료되면 선언된 임시 테이블은 자동으로 제거됩니다. Temporary
Table을 선언하기 위해서는 User Temporary Tablespace 가 필요합니다.
Temporary Table은 특정 Session 에서만 액세스할 수 있습니다. 동일한 이름을 가진 Temporary Table을 여러 세션에서 동시에 독립적으로 액세스합니
다. 테이블의 스키마명은 세션이나 사용자에 관계없이 “SESSION”을 사용합니다.
CREATE PROCEDURE temp_table_insert (IN parm1 INTEGER, IN parm2 INTEGER )
LANGUAGE SQL
BEGIN
DECLARE v_column1 INTEGER DEFAULT 0;
DECLARE v_column2 INTEGER DEFAULT 0;
CREATE USER TEMPORARY TABLESPACE usertempspace
MANAGED BY SYSTEM USING (‘/sqlsp/usertempspace')
IF (1 = 0) THEN
DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT) NOT LOGGED;
END IF;
INSERT INTO session.ttt(column1, column2) VALUES ( parm1+3, parm2+3);
INSERT INTO session.ttt(column1, column2) VALUES ( parm1+4, parm2+4);
BEGIN
DECLARE cursor1 CURSOR FOR SELECT * FROM session.ttt;
OPEN cur1 ;
FETCH FROM cursor1 INTO v_column1, v_column2;
WHILE (result_set_end = 0) DO
INSERT INTO note99 VALUES ( v_column1, v_column2 );
FETCH FROM cursor1 INTO v_column1, v_column2;
END WHILE;
CLOSE cursor1;
END;
END
23
CREATE TABLE NOTE99 ( F1 int, F2 int)
CALL temp_table_insert (10,20)
SELECT * FROM note99
F1
----13
14
F2
----23
24
© 2004 IBM Corporation
DB2 UDB
Stored Procedure Guide
SECURITY 제어
특정 SP에 대한 제거 및 실행 권한은 기본적으로 SP를 생성한 사용자, DBADM, SYSADM 에게 있습니다. 다른 사용자가 해당 SP를 실행하게 하려면 그
ROUTINE 에 대한 EXECUTE 권한을 사용자 또는 그룹별로 부여해야 합니다.
$ db2 connect to smaple user inst01 using inst01
$ db2 "call inst01.sp01('000340')“
리턴 상태 = 1
$ db2 connect to smaple user user01 using user01
$ db2 "call inst01.sp01('000340')"
SQL0551N "USER01"에는 오브젝트 "INST01.SP01"에서 조작 "EXECUTE"을(를) 수행할 수 있는 특권이 없습니다. SQLSTATE=42501
$ db2 "select grantor, grantee, executeauth from syscat.routineauth where specificname = 'SP01'"
GRANTOR
GRANTEE
EXECUTEAUTH
---------------------------------------------------------------------------SYSIBM
YURIMAMA
G
$ db2 connect to smaple user inst01 using inst01
$ db2 grant execute on procedure inst01.sp01 to user01
$ db2 "select grantor, grantee, executeauth from syscat.routineauth where specificname = 'SP01'"
GRANTOR
GRANTEE
EXECUTEAUTH
---------------------------------------------------------------------------SYSIBM
INST01
G
INST01
USER01
Y
$ db2 connect to smaple user user01 using user01
$ db2 "call inst01.sp01('000340')"
리턴 상태 = 1
24
© 2004 IBM Corporation