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 9199 게시물 읽기
No. 9199
ORACLE -> Postgre 프로시저 관련 질문 드려봅니다.
작성자
강명준
작성일
2012-12-17 11:23ⓒ
2012-12-17 12:16ⓜ
조회수
13,326

안녕하십니까.

가끔 들려서 좋은 Q&A 글들 보고 가는 웹 개발자입니다.

다름이 아니구요.

Spring + 아이바티스 + Oracle을 사용한 기존의 솔루션에서 DB만 PostgreSql 8.3버전으로 포팅하는 중인데요. 

SELECT 문은 모두 완료를 하였는데, 나머지 INSERT, DELETE, UPDATE 구문은 전부 오라클 프로시저를 사용했기 때문에 에로 사항이 있습니다.

일단 아이바티스 쪽을 보면..

<parameterMap id="spDeptPrcMap" class="java.util.Map">
    <parameter property="r_code"          javaType="java.lang.String" jdbcType="VARCHAR"   mode="OUT" />
    <parameter property="r_msg"           javaType="java.lang.String" jdbcType="VARCHAR"   mode="OUT" />
 
    <parameter property="flag"                javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" />
    <parameter property="dept_cd"        javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" />
    <parameter property="dept_nm"      javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" /> 
    <parameter property="s_user_cd"   javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" />
    <parameter property="s_user_ip"    javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" />
</parameterMap>
    
<procedure id="spDeptPrc" resultClass="java.util.List" parameterMap="spDeptPrcMap">
    { call SP_EZ_DEPT_PRC (  ?, ?, ?, ?, ?, ?, ? ) }
 </procedure>

이렇게 호출을 하구요.

실제 오라클 프로시저를 보면..

CREATE OR REPLACE PROCEDURE SP_EZ_DEPT_PRC
(
 r_code  out       varchar2
 ,r_msg  out       varchar2
 
 ,p_flag               varchar2
 ,p_dept_cd       varchar2
 ,p_dept_nm     varchar2
 
 ,p_s_user_cd  varchar2
 ,p_s_user_ip   varchar2
) is
 
 v_chk_cnt         number;
 v_max_cnt        number;
 
 v_ERROR        EXCEPTION;

BEGIN
  
  if p_flag ='ins' then
    begin   
      select nvl(max(dept_cd),0) + 1
           into v_max_cnt
         from ez_dept;
   
       insert into ez_dept  (
         dept_cd
         ,dept_nm    
         ,ins_date
         ,ins_user_cd
         ,ins_user_ip )
      values (
        v_max_cnt
        ,p_dept_nm    
        ,sysdate
        ,p_s_user_cd
        ,p_s_user_ip );
   
     if SQL%ROWCOUNT < 1 then
       begin
         r_code := '-1';
         r_msg := '에러입니다.';
         RAISE v_ERROR;
       end;
     end if;
   
  end;

end if;
 
 r_code := '1';
 r_msg := '등록완료입니다.';
 return;
 

EXCEPTION
 WHEN v_ERROR THEN
  ROLLBACK;
 WHEN OTHERS THEN
  r_code := '-1';
  r_msg := '에러입니다.'; 
 ROLLBACK;   
 
END;

 

이렇게 되는데요..

자료를 찾아봐도 이런 식의 호출을 PostgreSql로 포팅하는 예제나 샘플이 없더라구요..

혹시 불가능한건지..

가능하면 간단하게 힌트라도 알려주시면 테스트 해보고 적용하도록 하겠습니다.

몇일 하다가 지쳐서 혹시나 하는 마음에 고수분들에게 여쭤봅니다.

그럼 오늘도 즐프 하시구요.

좋은 하루 보내세요.

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

 오라클의 프로시져를 plpgsql 함수로 포팅하는 방법에 대한 자세한 설명은 

http://www.postgresql.org/docs/9.2/static/plpgsql-porting.html

문서를 참조하시면 되고요. 

 

그 문서를 기반으로 윗 오라클 프로시져를 plpgsql 함수로 바꾼 것은 다음과 같습니다. 

CREATE OR REPLACE function SP_EZ_DEPT_PRC
(
 r_code  out       varchar
 ,r_msg  out       varchar

 ,p_flag               varchar
 ,p_dept_cd       varchar
 ,p_dept_nm     varchar

 ,p_s_user_cd  varchar
 ,p_s_user_ip   varchar
) as
$body$
declare
 v_chk_cnt         numeric;
 v_max_cnt        numeric;
BEGIN
  if p_flag ='ins' then
      select coalesce(max(dept_cd),0) + 1
           into v_max_cnt
         from ez_dept;

    begin
       insert into ez_dept  (
         dept_cd
         ,dept_nm
         ,ins_date
         ,ins_user_cd
         ,ins_user_ip )
      values (
        v_max_cnt
        ,p_dept_nm
        ,current_timestamp
        ,p_s_user_cd
        ,p_s_user_ip );

       if not found then
         r_code := '-1';
         r_msg := '에러입니다.';
         return;
       end if;

       r_code := '1';
       r_msg := '등록완료입니다.';

       EXCEPTION WHEN OTHERS THEN
         r_code := '-1';
         r_msg := '에러입니다.';
    end;
  end if;
END;
$body$
language plpgsql;

 plpgsql 에서는 예외처리를 등록하고 그것을 사용할 방법이 없기 때문에, insert not found에 대한 구문이 바로 return 되어야합니다.

nvl, sysdate 같은 것들은 PostgreSQL 구문으로 바꾸었으며, exception 처리는 적당하게 했습니다. 

(실 코드에서 적당하게 또 수정해야할 것 같네요.)

 

이렇게 만들어진 함수는 다음과 같이 테스트 하면 됩니다. 

$ psql
psql (9.2.2)
Type "help" for help.

ioseph=# select * from SP_EZ_DEPT_PRC('ins','0','a','1','1');
 r_code |      r_msg
--------+-----------------
 1      | 등록완료입니다.
(1 row)

ioseph=# select * from SP_EZ_DEPT_PRC('ins','0','a','1','1');
 r_code |    r_msg
--------+-------------
 -1     | 에러입니다.
(1 row)

 오류 테스트를 위해서, user_cd 칼럼에 unique index를 만들어 주었습니다. 

한번은 정상적으로 들어갔고 다음 자료는 못 들어갔습니다. 

함수가 의도된 대로 움직임을 확인 했으면 이제 java에서 이 함수를 기존 오라클과 같이 프로시져 호출 방법으로 사용하는 방법 

그 코드는 core jdbc api로만 작성했습니다. 

 

class CallTest {
        public static void main(String[] args){
                try {
                        Class.forName("org.postgresql.Driver");
                        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:7432/ioseph");
                        // SP_EZ_DEPT_PRC('ins','0','a','2','1')
                        CallableStatement cstmt = conn.prepareCall("{call SP_EZ_DEPT_PRC(?,?,?,?,?,?,?)}");
                        cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
                        cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
                        cstmt.setString(3, "ins");
                        cstmt.setString(4, "0");
                        cstmt.setString(5, "a");
                        cstmt.setString(6, "2");
                        cstmt.setString(7, "1");
                        cstmt.execute();
                        System.out.println(cstmt.getString(1));
                        System.out.println(cstmt.getString(2));
                        cstmt.close();
                        conn.close();
                }
                catch(java.lang.ClassNotFoundException ex) {
                }
                catch(SQLException ex){
                        System.out.println(ex);
                }
        }
}

여기서는 주의할 것인 이  callable statment 객체는 executequery 와 같은 method로 호출하는 것이 아니라, 그냥 execute로 호출한다는 것입니다. 왜냐하면, 이놈은 단일 output 파라미터를 가지기 때문입니다. 

 

$ java -cp ./postgresql-9.2-1002.jdbc4.jar:. CallTest
1
등록완료입니다.
$ java -cp ./postgresql-9.2-1002.jdbc4.jar:. CallTest
-1
에러입니다.
$

 이놈도 의도된 대로 움직이네요.

문제는 ibatis에서의 사용법인데, 이 부분에 대해서는 직접 문제를 해결하셔야할 것 같습니다. 

여느 output 매개변수가 있는 프로시져의 사용법과, 그 프로시져를 사용하는 java의 사용법도 크게 틀리지 않습니다. 단지, 고생하는 것은 오라클용 프로시져를 PostgreSQL용 함수로 만드는 일이 좀 손이 많이 가는 일이긴 합니다. 

 

김상기(ioseph)님이 2012-12-24 16:00에 작성한 댓글입니다.

김상기님 정말 감사합니다.

 

댓글 잘 보고 잘 적용해 보도록 하겠습니다^^

강명준님이 2012-12-28 15:26에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
9213로우를 일렬로 나눠담고 싶은데요 -ㅂ- 쿼리 해결방법좀 [3]
김현진
2013-01-02
9265
9201pgsql 의 event Scheduler 가 정상적으로 돌지가 않네요 ㅡㅜ [2]
박명회
2012-12-24
9944
9200BEGIN is not allowed in a SQL function.... [1]
sdef
2012-12-21
9110
9199ORACLE -> Postgre 프로시저 관련 질문 드려봅니다. [2]
강명준
2012-12-17
13326
9119기간 query를 하고 싶은데 도무지 방법이 떠오르지 않습니다.ㅜㅜ [1]
ㅜㅜ
2012-12-03
9115
9065view 테이블 update 가 궁금 합니다. [1]
김수지
2012-11-23
8977
9064밑에 답변 주신분 감사합니다.
송기헌
2012-11-23
8770
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.055초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다