close

Trust Me!! Trust You!!


  • Blog
  • Local Log
  • Tag Cloud
  • Key Log
  • Guestbook
  • RSS Feed
  • Write a Post
  • Admin

혹시 블로그 스킨이 깨져 보이시나요? 최신버전의 Internet Explorer(Windows용), Opera, Firefox를 사용해보세요.

Found 1 article(s) for '저장프로시저'.

  1. 2015/06/04 DB2 9.7 저장 프로시저 적용 예제

DB2 9.7 저장 프로시저 적용 예제

웹 프로그래밍
2015/06/04 20:47
 
이 문서에서는 이기종 DBMS 개발자들에게 도움이 될만한 DB2 저장 프로시저를 샘플 위주로 정리해 보여준다. 처음으로 저장 프로시저를 만들어보는 경우라면 내용이 조금 어려울 수도 있겠지만 이미 다른 DBMS 저장 프로시저 개발을 경험해본 독자에게는 이 문서에서 정리된 DB2 저장 프로시저 프로그램 소스를 살펴보는 것만으로 보다 쉽게 DB2 프로그래밍에 접근할 수 있는 기회가 될 수 있을 것이다.

들어가기 전에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

오후 5:54:04

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

 LOOP

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

 

      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: LOOP

    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

이올린에 북마크하기
TAG DB2, DB2 procedure, 저장프로시저
No received trackback. / No comment.

Trackback Address :: http://viper150.cafe24.com/trackback/255

You can also say.

Prev 1 Next
블로그 이미지
이것저것 불펌금지도 퍼다가 담습니다. 외부에 비공개된 페이지 입니다. By. 어른왕자

카테고리

  • 전체 (298)
    • 사는 이야기 (115)
    • 웹 프로그래밍 (102)
    • App 프로그래밍 (22)
    • IT 뉴스&기타 (22)
    • 박한별 (4)
    • 역사&기타지식 (9)

태그목록

  • 전산직
  • tomcat
  • 아니스티
  • 김희선
  • 생산성
  • 조선
  • CIA
  • 귀여움
  • 비사
  • 황금레시피
  • 피로
  • 게임중독
  • Loading
  • List
  • RISS
  • 갤럭시
  • 직장의신
  • 스프링
  • pixlr
  • CGI
  • 히든메뉴
  • TV 보기
  • 삼성
  • 토렌트
  • 잃은것
  • Boy
  • tab
  • Log
  • 집안일
  • A4용지

최근에 올라온 글

  • 보험사의 조정신청 대응방법.
  • 어느 천재의 앞선 시선.
  • [병맛더빙] 누구게..... (1)
  • 韓경제 `회색 코뿔소` 상황...
  • SVN Connector 설치 URL.
  • 군대를 가지 않는 서울대생.
  • “운은 하늘의 귀여움 받는...
  • 목장에서 알바하다가 캐스...
  • [펌]믿고 거르는 관상.
  • 하루에 1세트씩 하면 좋다...

최근에 달린 댓글

  • 대;한;민;국 초.대.남/스-와-... 소라 04/21
  • 여성흥분제팝니다 24시간 전... 정력제 04/18
  • 유벤투스의 스타 공격수, 크리... 호날두 04/18
  • 저도 칭구덕에 이젠 큰 부자가... 가현 04/15
  • 저도 칭구덕에 이젠 큰 부자가... 가현 04/12

최근에 받은 트랙백

  • Solace Salts Bold Tobacco. Solace Salts Bold Tobacco 03/29
  • read this post from Bookie 7. read this post from Bookie 7 02/28
  • công ty may đồng phục. công ty may đồng phục 01/08
  • Israelnightclub`s recent blo... Israelnightclub`s recent blo.. 01/06
  • Suggested Browsing. Suggested Browsing 01/06

글 보관함

  • 2019/03 (1)
  • 2018/12 (1)
  • 2018/09 (1)
  • 2018/08 (1)
  • 2018/02 (1)

달력

«   2021/04   »
일 월 화 수 목 금 토
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  

링크

  • Total : 265990
  • Today : 54
  • Yesterday : 38
Tattertools
Eolin
rss

어른왕자's blog is powered byTattertools1.1.2.2 : Animato