database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 10012 게시물 읽기
No. 10012
oracle to postgres convert 작업 중입니다.
작성자
초보입니당(wkdskfk9)
작성일
2018-10-04 15:31:00
조회수
175

tpc-c 관련해서 프로시저를 하나 만들고 있는데 생각보다 작업이 어렵네요 


CREATE OR REPLACE PROCEDURE Delivery 

    IN_mW_ID          IN  INTEGER, 
    IN_mO_CARRIER_ID  IN  INTEGER, 
    OUT_Result        OUT VARCHAR(4000), 
    OUT_mSuccess      OUT NATIVE_INTEGER 

IS 
    sNativeError    INTEGER; 

    DV_w_id          INTEGER; 
    DV_o_carrier_id  INTEGER; 
    DV_d_id          INTEGER; 
    DV_c_id          INTEGER; 
    DV_no_o_id      INTEGER; 
    DV_o_total      NUMERIC(15,2); 

    sBuffer          VARCHAR(4000); 

    PROCEDURE SETERR 
    IS 
    BEGIN 
        sNativeError := SQLCODE; 
    END; 
BEGIN 

    --------------------------------------------- 
    -- INPUT ARGS, Initialize Variable 
    --------------------------------------------- 
    DV_w_id        := IN_mW_ID; 
    DV_o_carrier_id := IN_mO_CARRIER_ID; 

    << RAMP_RETRY >> 

    sNativeError    := 0; 
    sBuffer        := ''; 

    --------------------------------------------- 
    -- GET OUTPUT 
    --------------------------------------------- 
    FOR DV_d_id IN 1 .. 10 
    LOOP 
        BEGIN 
            SELECT no_o_id INTO DV_no_o_id 
            FROM new_order 
            WHERE no_w_id = DV_w_id AND 
                  no_d_id = DV_d_id 
            ORDER BY no_o_id 
            FETCH 1 ; 
        EXCEPTION WHEN NO_DATA_FOUND THEN sBuffer := sBuffer || -1 || '|' || DV_d_id || '|'; 
                                          CONTINUE; 
                  WHEN OTHERS THEN SETERR;goto SQL_FINISH; 
        END; 

        BEGIN 
            DELETE FROM new_order 
            WHERE no_o_id = DV_no_o_id AND 
                  no_d_id = DV_d_id AND 
                  no_w_id = DV_w_id; 
        EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH; 
        END; 

        BEGIN 
            SELECT o_c_id INTO DV_c_id 
            FROM orders 
            WHERE o_id  = DV_no_o_id AND 
                  o_d_id = DV_d_id AND 
                  o_w_id = DV_w_id; 
        EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH; 
        END; 

        BEGIN 
            UPDATE orders 
            SET o_carrier_id = DV_o_carrier_id 
            WHERE o_id  = DV_no_o_id AND 
                  o_d_id = DV_d_id AND 
                  o_w_id = DV_w_id ; 
        EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH; 
        END; 

        BEGIN 
            UPDATE order_line 
            SET ol_delivery_d = sysdate 
            WHERE ol_o_id = DV_no_o_id AND 
                  ol_d_id = DV_d_id AND 
                  ol_w_id = DV_w_id; 
        EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH; 
        END; 

        BEGIN 
            SELECT SUM(ol_amount) INTO DV_o_total 
            FROM order_line 
            WHERE ol_o_id = DV_no_o_id AND 
                  ol_d_id = DV_d_id AND 
                  ol_w_id = DV_w_id; 
        EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH; 
        END; 

        BEGIN 
            UPDATE customer 
            SET c_balance = c_balance + DV_o_total, 
                c_delivery_cnt = c_delivery_cnt + 1 
            WHERE c_id  = DV_c_id AND 
                  c_d_id = DV_d_id AND 
                  c_w_id = DV_w_id; 
        EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH; 
        END; 

        ------------------------------------------- 
        -- Make Output-String 
        ------------------------------------------- 
        sBuffer := sBuffer || DV_no_o_id || '|' || DV_d_id || '|'; 
    END LOOP; 


    ------------------------------------------- 
    -- Output Param 
    ------------------------------------------- 
    OUT_Result  := sBuffer; 
    OUT_mSuccess := 1; 

    COMMIT; 

    goto END_STEP; 

    << SQL_FINISH >> 

    ROLLBACK; 

    IF ( (sNativeError = -14007) OR (sNativeError = -14032) ) 
    THEN 
        goto RAMP_RETRY; 
    END IF; 

    OUT_mSuccess := 0; 

    << END_STEP >> 
    NULL; 
END; 



오라클 프로시저문인데 postgresql로 변경하고 싶은데 잘 안되네요 혹시 잘 아시는분 있으면 

 

이 글에 대한 댓글이 총 10건 있습니다.

이 질문은 java 언어로 된 class 파일 하나 보여주면서, 

python 모듈로 어떻게 만들어요? 라는 질문과 같은 격입니다.  답변은 '잘 옮기면 됩니다' 외에는 

더 좋은 대답을 기대하기는 힘들 것 같습니다. 

 

PostgreSQL을 꼭 써야하는 상황이라면, 

plpgsql (http://postgresql.kr/docs/current/plpgsql.html)을 새로 배우셔야합니다. 

이 때, 오라클에서는 이렇게 했는데, PostgreSQL에서는 이렇게 하는구나 하면서 

하나씩 비교해 가면서 익혀야 옮기는 작업이 쉽겠죠. 

 

위 프로시져라면, 오라클 plsql 개발을 어느 정도 하셨던 분이라면, 배우는데 하루면 충분합니다.

 

PostgreSQL을 꼭 안 쓰고, 돈을 좀 써도 괜찮다면, 

EnterpriseDB사의 Postgres Advanced Server 제품을 이용하세요. 그럼 위 프로시져는 그대로 사용할 수 있을 것 같네요. 

이 제품 문의는 한국 EnterpriseDB 지사에 문의하시면 되고요.

 

김상기(ioseph)님이 2018-10-04 16:14:38에 작성한 댓글입니다.

postgresql에서 goto문은 어떻게 대체가 가능한가요? 

 

Postgres Advanced Server에서는 작업이 가능하다고 봤습니다.

 

근데 지금은 postgresql에서 작업만 하려고 합니다. 

 

혹시 방법을 알고 계시면 답변 부탁드리겠습니다...

초보입니당(wkdskfk9)님이 2018-10-05 15:34:27에 작성한 댓글입니다.

 윗 구문 상 goto 문은 예외처리에 해당 합니다. 

plpgsql 구문으로 예외처리를 어떻게 하는지 살펴보면 됩니다. 

 

문제는 

sql_finish 블럭의 retry 인데, 저 ora-14007, ora-14032 이 부분은 파티션 범위 관련 오류로 보이는데, 이 부분은 복합적인 부분인지라, 잘못 구현하면 무한루프에 빠질 가능성이 있어, (실제로는 재귀호출 스택 오버플로우가 일어날 것으로 예상하지만) 

전반적으로 다시 생각해봐야할 부분 같네요. 

 

김상기(ioseph)님이 2018-10-08 10:33:37에 작성한 댓글입니다.

모든 PL/SQL 소스가 저런식이면 정말 쉽지 않을 것 같습니다. 

1) 

최신 건순으로 패치하여 delete, update 하는 로직의 처리 같습니다. 

오라클 pl/sql 의 << >> 레이블 처리 방식을 exception 구문으로 바꾸어 

최대한 오라클의 문법을 배제하여 바꾼 후 테스트 

(goto 도 exception 으로 바꿀 수는 있을 것 같긴 하네요. ) 

 

2) 

파티션 관련 에러 처리를 하는걸로 봐선, 파티션 키값을 벗어나지 않은 선에서 

RAMP_RETRY 를 반복하려는 것 같은데 

우선적으로 postgresql 의 일반테이블로 만들어놓고 

커서 루핑 부분이 잘 되는지 

테스트 해볼 것을 추천드립니다. 

 

 

 

지나가다님이 2018-10-08 17:10:21에 작성한 댓글입니다.
이 댓글은 2018-10-08 17:14:01에 마지막으로 수정되었습니다. Edit

 댓글 항상 감사합니다.

 

하나씩 배워가면서 변경중인데 시간은 없고 발전이 그렇게 많이 없네요.

 

귀찮게 해서 정말 죄송합니다.

 

혹시 괜찮으시면 

 

[    PROCEDURE SETERR 

    IS 
    BEGIN 
        sNativeError := SQLCODE; 
    END; 

 

 WHEN OTHERS THEN SETERR;

]

 

[

EXCEPTION WHEN NO_DATA_FOUND THEN sBuffer := sBuffer || -1 || '|' || DV_d_id || '|'; 

]

이렇게 두 문단만 혹시 postgresql로 변경을 해서 어떻게 표현하는지 가르쳐 주실 수 있으신가요.?

 

며칠동안 계속 구글링하면서 작업중인데 저런 구문은 찾기가 힘드네요..

 

ㅠㅠ

초보입니당(wkdskfk9)님이 2018-10-10 16:51:22에 작성한 댓글입니다.

PostgreSQL에서는 지역 변수로 프로시져를 지정할 수 없습니다.

PROCEDURE SETERR 이게 그건데요.

그런데, 가만히 보면, 그 seterr 가 하는 일이라고는 SQLCODE 값을 sNativeError 지역 변수에 넣는 일만해요.

그냥 plpgsql 예외처리로 하시면 됩니다. 그래서, 예외처리 방법을 새롭게 공부해야 한다고 알려드린 것입니다.

 

다음, 윗 경우는 NO_DATA_FOUND 는 plpgsql에서는 예외가 아니라, 전역 변수입니다. 이 부분은 "PostgreSQL Oracle no_data_found" 이런식으로 검색하면 좋은 외국 문서들이 많습니다. 국내 문서로도 있지 않을까싶네요. (저는 쓴 적이 없습니다. 게을러서)

잠깐 찾아본 문서로는 http://postgresqldbnews.blogspot.com/2007/10/best-way-to-handle-no-data-found-in.html 이런 좋은 문서가 있네요.

 

김상기(ioseph)님이 2018-10-10 18:33:17에 작성한 댓글입니다.

 크...정말 감사합니다!!!!!

ㅠㅠㅠㅠ 엄청 많은 도움이 되었습니다

 

감사합니다!!

초보입니당(wkdskfk9)님이 2018-10-11 15:12:59에 작성한 댓글입니다.

 오 저도 postgresql에 goto문 찾고 있던 중인데요

 

질문하나 해도 괜찮을까요?

 

순서대로 진행하다가 예를들어

1. <<ramp_retery>>  

 

2. a --------쿼리문

 

3. b----------쿼리문

 

4. c-----------쿼리문

goto <<ramp_retery>> 

 

5. <<sql_finish>>

 

6. <<end_step>>

다시 1번의 <<ramp_retery>>로 돌아가는건지..?

그리고 다시 1번부터 6번까지 순차적으로 진행이 되는건가요?

 

공부중님이 2018-10-12 14:01:56에 작성한 댓글입니다. Edit

덕분에 예외처리 부분은 해결을 했습니다.

 

GOTO 부분이 참 힘드네요

 

질문이 있는데, GOTO구문이 왜 예외처리라고 하시는지 잘 이해가 가질 않습니다.

 

1 ) GOTO SQL_FINISH 구문에서 

 

<<SQL_FINISH>> 라벨에 <<RAMP_RETERY>>로 가면 다시 처음으로 돌아가는것이 아닌가요?

 

2) 저 부분을 IF THEN 으로 변경을 하려는데 도저히 제 머리로는 구현이 안되네요.......

 

혹시 간단한 힌트라도 알 수 있을까요?

 

질문이 많아 죄송합니다.

초보입니당(wkdskfk9)님이 2018-10-13 19:34:54에 작성한 댓글입니다.

프로시저 / 프로그램 작성시 goto 문은 피하지 않는지요? 

게다가 저렇게 레이블 구문을 남발하게 되면 차후 소스 유지보수가 힘들 것입니다. 

지금 최초의 소스에서 goto 는 예외처리 처럼 쓰여서 그렇게 말씀드린겁니다. 

저렇게 예외처리를 하면 안됩니다. exception 절로 그 이하에 단계단계 구분하여 

처리하는게 정석입니다. 

제가 가본 곳 중 정말 규약이 심한 사이트 같은 경우는 

커서 구문 조차도 못쓰게 한 곳도 있었습니다. 

오라클과 postgresql 이 구문적으로 그나마 유사하기는 하지만, 

전혀 다른 DB 입니다. 

 

 

지나가다님이 2018-10-15 16:22:08에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
10013PGDay.Seoul 2018 행사에 초대합니다
김상기
2018-10-08
82
10012oracle to postgres convert 작업 중입니다. [10]
초보입니당
2018-10-04
175
10011PgPool - Postgresql HA 적용할 경우 질문 [2]
신동평
2018-09-19
311
10010PostgreSQL PgPool slave -> master [4]
신동평
2018-09-14
370
10009postgres .s.pgsql.5432 파일을 삭제해버렸습니다. [1]
부탁해요
2018-09-10
395
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2018 DSN, All rights reserved.
작업시간: 0.075초, 이곳 서비스는
	PostgreSQL v10.4로 자료를 관리합니다