들어가기 전에SQL에 친숙한 사람이라면 저장 프로시저(SP: Stored Procedure)를 이용한 프로그래밍은 그리 어렵지 않을 것이다. 그런데 다른 DBMS를 사용하다가 처음 DB2 개발 업무를 맡게 된 개발자들은 익숙치 못해 어려움을 겪는 경우가 있다. DB2 저장 프로시저 자체가 어려워서라기보다는DB2 프로시저 기반 개발이 처음이거나 타 DB보다는 DB2 프로시저 관련 자료를 쉽게 접하지 못해서 큰 그림을 그리지 못하는 경우가 많은 것 같다. 사실은 DB2 저장 프로시저 프로그램의 로직을 보면 타DBMS의 저장 프로시저와 문법만 약간 다를 뿐 기본적인 패턴은 상당히 유사하다. 이 문서에서는 이런 어려움을 겪는 개발자들에게 도움이 될만한 DB2 저장 프로시저를 샘플 위주로 정리해보았다. 처음으로 저장 프로시저를 만들어보는 경우라면 내용이조금 어려울 수도 있겠지만 이미 다른 DBMS 저장 프로시저 개발을 경험해본 독자에게는 이 문서에서 정리된 DB2 저장 프로시저 프로그램 소스를 살펴보는 것만으로 보다 쉽게 DB2 프로그래밍에 접근할 수 있는 기회가 될 수 있을 것이다.
목차
1. DB2 9.7 저장 프로시저 적용 예제 1
- DB2 vs MS SQL, Oracle 프로시저 비교
2. DB2 9.7 저장 프로시저 적용 예제 2
- FOR LOOP문
- 커서로 선언한 값에 대하여 loop 돌리면서 insert 작업을 수행할 때
- 원장 테이블 데이터를 작업용 테이블로 옮겨보자
- Declare 선언 시 테이블 타입 속성 정보 가져오기
- 트랜잭션 처리 예시
3. DB2 9.7 저장 프로시저 적용 예제 3
- 시그널을 사용해 보세요
- 메인 프로시저에서 조건에 따라서 서브 프로시저를 호출
- 배치 프로시저 작성/ 프로시저 실행 후 작업 내역이 궁금해 질 때 (시간,건수)
4. DB2 9.7 저장 프로시저 적용 예제 4
- 어플리케이션 툴 RESULT창에서 테이블 결과값 조회
- 서브 테이블 함수 사용 방법
사용한 DB 환경
1) DB2 9.7 (오라클 호환성 설정 모드 à DB2_COMPATIBILITY_VECTOR=ORA)
2) MS SQL Server 2008
3) Oracle 10g
1. DB2 vs. MS SQL, Oracle 프로시저 비교
오라클 호환성 모드를 설정한 DB2 9.7은 오라클 형식의 SQL 문을 처리할 수 있는 컴파일러를 내장하고 있어서 수정 없이 오라클 프로그래밍을 그대로DB2dp로 활용할 수 있다. 따라서 DB2 9.7에서는 오라클 소스를 바로 DB2로 컴파일 할 수 있겠지만 이 기능은 DB2 9.5 이하 버전에서는 지원하지 않으므로 Native DB2 프로시저로도 한번 바꿔보는 일도 필요하겠다. MS SQL Server의 저장 프로시저 경우는 DB2에서는 컴파일이 안되고 마이그레이션 툴을 사용하거나 아니면 수작업으로 일일이 변경 작업을 해야 한다. 변수 처리 및 일부 제어문의 차이점을 미리 확인해보는 작업이 필요하다.
[중요 확인 사항]
1) MS SQL Server: 변수사용법> @변수 è DB2에서는 @변수 형식을 사용하지 않음
2) 오라클: 변수값 대입 방법> := è DB2에서는 등호부호 사용
[Source 1A] MS SQL Server 형식의 채번 프로시저 샘플
Create PROC Get_InSlipNo @InDate smalldatetime, @SlipNo int OUTPUT AS BEGIN TRAN SELECT @SlipNo = ISNULL(MAX(SlipNo),0) + 1 FROM InSlipNo WITH (XLOCK) WHERE InDate = @InDate IF @SlipNo = 1 INSERT INTO InSlipNo VALUES( @InDate, @SlipNo) ELSE UPDATE InSlipNo SET SlipNo = @SlipNo WHERE InDate = @InDate COMMIT TRAN |
[Source 1B] 같은 로직을 DB2 문법으로 수행
Create or replace procedure Get_InSlipNo ( in p_InDate timestamp(0)) LANGUAGE SQL MODIFIES SQL DATA BEGIN DECLARE v_slipno int default 0; SELECT NVL(MAX(SlipNo),0) + 1 INTO v_slipno FROM InSlipNo WHERE InDate = p_InDate; IF v_SlipNo = 1 THEN INSERT INTO InSlipNo VALUES( p_InDate, v_slipno ) ; ELSE UPDATE InSlipNo SET SlipNo = v_slipno WHERE InDate = p_InDate; END IF; END |
*오라클 호환 모드설정이 된 DB2 9.7에서는 timestamp가 Native DB2와 약간 상이하다.
- 오라클 호환 모드설정이 된 DB2 9.7의 current timestamp format à 2009. 12. 14
- Native DB2의 current timestamp format à 2009-08-12-17.27.49.063484
아래 오라클 예제는 바로 오라클 호환모드가 설정된 DB2 9.7에서 바로 컴파일이 된다. 또한 자주 사용하는 오라클 패키지(오라클이 지원하는 모든 패키지는 아니다)를 DB2에도 사용할 수 있어서 최소의 비용과 시간을 가지고 오라클에서 DB2로 마이그레이션 하는 경우에는 DB2 9.7 버전 사용을 권장한다.
[Source 1C] 오라클 SP 예제
create or replace procedure empno_test ( p_deptno varchar) as DECLARE v_empno employees.employee_id%TYPE; v_ename employees.last_name%TYPE; v_salary employees.salary%TYPE; v_cur_date_char_start CHAR (20); v_cur_date_char_end CHAR (20); v_tbcount int :=0; v_tpcount int :=0; RCOUNT int :=0; CURSOR emp_cur IS SELECT employee_id, last_name, salary FROM employees WHERE department_id = p_deptno; BEGIN OPEN emp_cur; FETCH emp_cur INTO v_empno, v_ename, v_salary; EXIT WHEN emp_cur%NOTFOUND; select sysdate into v_cur_date_char_start from dual; IF v_salary > 50000 THEN delete EMP_LOG where empno = v_empno; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('데이타가 없어요'); v_tbcount := v_tbcount+1; END IF; ELSE insert into EMP_LOG ( empno, firstnme, workdept, salary) VALUES ( v_empno, v_ename, p_deptno, v_salary-333); v_tpcount := v_tpcount+1; END IF; rcount := rcount+1; END select sysdate into v_cur_date_char_end from dual; CLOSE emp_cur; DBMS_OUTPUT.PUT_LINE ('프로그램명:proc_orale'); DBMS_OUTPUT.PUT_LINE ('시작시간 :'||v_cur_date_char_start); DBMS_OUTPUT.PUT_LINE ('종료시간 :'||v_cur_date_char_end); DBMS_OUTPUT.PUT_LINE ('대상건수 :'||rcount ); DBMS_OUTPUT.PUT_LINE ('보너스적용건수 :'||v_tbcount||'패널티적용건수:'||to_char (v_tpcount)); END |
만약 DB2 9.7버전에서 오라클 문법과 DB2 문법을 혼용해서 저장 프로시저를 작성하면 컴파일시 (첫번째 라인에서 바로) 에러가 발생한다. 물론 DB2에서 추가해서 지원하는 예외처리나 함수 같은 기능은 사용 가능하므로 이 부분은 DB2 9.7 신기능에서 확인해 볼 필요가 있다. 따라서 아래 예시처럼DBMS_OUTPUT 같은 패키지(DB2에서는 모듈이라고 부른다)는 별도로 지원하는 DB2 9.7의 기능이라 저장 프로시저 문법 혼용 이슈로 컴파일 에러가발생하지는 않는다.
[추가 설명] LOOP문에서는 반복범위, 초기값 그리고 반복 조건에서 빠져나갈 수 있는 항목이 있어야 한다. 아래에서 LOOP문에서는 커서의 패치 작업시 더 이상의 작업할 데이터가 없다는 에러 핸들러 이벤트가 발생하면 그 값으로 LOOP을 빠져나갈 수 있다.
[추가 확인 사항]
1) LOOP … END LOOP è leave LOOP명으로 반복조건에서 exit 가능하다.
2) CONTINUE HANDLER FOR è 에러가 발생해도 프로그램을 비정상 종료하지 않고 계속 진행하게 함
3) IF SQLSTATE1 = '02000' THEN -- NO DATA FOUND
[Source 1D] 같은 로직으로 DB2 문법으로 수행
CREATE OR replace PROCEDURE proc1_1038( IN p_workdept varchar(3)) LANGUAGE SQL modifies SQL DATA SPECIFIC proc1_1038 BEGIN DECLARE SQLCODE INTEGER DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SQLSTATE1 CHAR(5) DEFAULT '00000'; DECLARE RCOUNT INTEGER DEFAULT 0; DECLARE v_cur_date_char_start CHAR (20); DECLARE v_cur_date_char_end CHAR (20); DECLARE v_empno varchar(6); DECLARE v_firstnme varchar(12); DECLARE v_workdept varchar(3); DECLARE v_birthdate date; DECLARE v_salary decimal(9,2); DECLARE v_bcount int default 0; DECLARE v_pcount int default 0; DECLARE v_tbcount int default 0; DECLARE v_tpcount int default 0; DECLARE c_cur cursor for select empno, firstnme, workdept, birthdate , salary from emp where workdept=p_workdept; --- declare 에러 핸들러는 declare문 맨 마지막에 선언 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET SQLSTATE1 = SQLSTATE; SELECT CAST(CURRENT TIMESTAMP AS CHAR(19)) INTO v_cur_date_char_start FROM DUAL; open c_cur; loop1: fetch c_cur into v_empno, v_firstnme, v_workdept, v_birthdate, v_salary; IF (SQLCODE = 100) THEN LEAVE loop1; END IF; IF v_salary >= 50000 THEN delete EMP_LOG where empno = v_empno; IF SQLSTATE1 = '02000' THEN -- NO DATA FOUND CALL DBMS_OUTPUT.PUT_LINE ('EMP_LOG 삭제작업시 오류가 발생했습니다'); LEAVE loop1; END IF; SET v_tbcount = v_tbcount+1; ELSE insert into EMP_LOG ( empno, firstnme, workdept, birthdate , salary) VALUES ( v_empno, v_firstnme, v_workdept, v_birthdate , v_salary-333); IF SQLSTATE1 = '02000' THEN -- NO DATA FOUND LEAVE loop1; END IF; SET v_tpcount = v_tpcount+1; END IF; SET rcount= rcount+1; END LOOP loop1; CLOSE c_cur; SELECT CAST(CURRENT TIMESTAMP AS CHAR(19)) INTO v_cur_date_char_end FROM DUAL; CALL DBMS_OUTPUT.PUT_LINE ('프로그램명:proc1_1038'); CALL DBMS_OUTPUT.PUT_LINE ('시작시간 :'||v_cur_date_char_start); CALL DBMS_OUTPUT.PUT_LINE ('종료시간 :'||v_cur_date_char_end); CALL DBMS_OUTPUT.PUT_LINE ('대상건수 :'||char(rcount) ); CALL DBMS_OUTPUT.PUT_LINE ('보너스적용건수:'||char(v_tbcount) ||' 패널티 적용건수 :'||char(v_tpcount)); END |
