상세 컨텐츠

본문 제목

자바에 스토어드 프로시져 붙여서 사용하기

프로그래밍/JAVA

by 라제폰 2009. 1. 23. 17:08

본문

사용 데이터 베이스는 오라클

 

스토어드 프로시져는 프로시져를 만들어서 오라클에 올려 놓은 프로그램으로
자체적으로 수행을 하기도 하지만 다른 프로그램에서 사용할수 있는 프로그램으로
소스의 재사용성이라는 부분에서 매우 유용한 메카니즘을 제공한다.

여기서는 스토어드 프로시저를 만드는 방법은 하지 않는다.
이것에 관해서는 서적을 참고한다.
프로시져의 소스는 몇 개의 소스를 올려서 기본구조만을 보게 할 것이다.

 

중점으로 할 것은 자바에서 프로시져를 호출해서 사용하는 방법에 대해서 말한다.

프로시져소스, 함수소스, 내가사용한소스파일(jsp)

 

자바에서 프로시져 호출하는 부분

    cStmt_scoprd =conn.prepareCall("{call SP_CREATE_CUST_PRD_CD(?,?,?,?)}");
                    cStmt_scoprd.setString(1, se_enpri_cd);
                    cStmt_scoprd.setString(2, se_cust_plis_cd );
                    cStmt_scoprd.setString(3, new_prd_cd );
                    cStmt_scoprd.registerOutParameter(4, Types.VARCHAR);

                    cStmt_scoprd.execute();

                    se_flag = (String)cStmt_scoprd.getObject(4);

위 소스를 보면 prepareCall  이있는데 이부분은 다음을 참고한다.

prepareCall 설명

SP_CREATE_CUST_PRD_CD 는 프로시져이다.( 프로시져소스에 포함 )

그 다음의 물음표 ? 는 인자를 나타낸다.

즉 이 프로시져는 4개의 인자값을 받고 있다.
4번째 인자는 registerOutParameter 로 되어 있고 뒤에 받을 값의 형정의가 되어 있다.

이것은 스트링값을 반환한다는 의미며 실제 실행시킨 후 값을 받는 부분이 있다.

주의 사항 - 자바와 프로시져 호출 부분에서 가장 에러가 많이 나는 부분이 바로 형 변환 문제이다.
이것을 해결하기 위해서는 프로시져에서 리턴되어야 할 형들을 data base 형 정의가 아닌 varchar등을 써야 한다.
즉.. -- p_sco_prd_cd     OUT sco_prd_info.sco_prd_cd%TYPE) ->
 p_sco_prd_cd     IN OUT VARCHAR2 ) 이런 식으로 하면 해결이 된다.

 

자바에서 스토어드 함수 부르기

   cStmt_scoprd =conn.prepareCall("{? = call F_GET_PLIS_SEQ(?,?,?)}");
    cStmt_scoprd.registerOutParameter(1, Types.INTEGER);
    cStmt_scoprd.setString(2, se_plis_cd);       // 품목명
    cStmt_scoprd.setString(3, str_att );        //
    cStmt_scoprd.setString(4, se_plis_nm );        //

    cStmt_scoprd.execute();

    se_plis_seq =cStmt_scoprd.getInt(1);

이것이 프로시져와 다른 점은 리턴값을 call 앞에 위치 시킨다는 것이다.
소스는 위에 링크되어 있다.

그럼.. 끝

 

########################프로시져소스##########################

 

PROCEDURE          "SP_CREATE_SCO_PRD_CD"  -------------------------------------------------------
-- Usage          : 공급사 상품코드,분류,분류/품목연계를 만들어 준다.
-- Argument(IN)   : 상품코드,공급사코드,매입가격,공급사품목코드
-- Argument(OUT)  : 공급사상품코드
-- Update         : 2001.08.10  백종린  - 최초작성
-----------------------------------------------------

(p_prd_cd      IN prd_info.prd_cd%TYPE,
 p_sco_cd      IN sco_prd_info.sco_cd%type,
 p_buy_pri     IN sco_prd_info.buy_pri%type,
 p_supp_plis_cd IN sco_prd_info.supp_plis_cd%type,
-- p_sco_prd_cd  IN OUT sco_prd_info.sco_prd_cd%TYPE)
 p_sco_prd_cd  IN OUT VARCHAR2)
IS
 p_sco_cd2  sco_prd_info.sco_cd%type;
 p_buy_pri2  sco_prd_info.buy_pri%type;
 p_seq      sco_prd_info.seq%type;
 p_plis_cd  cate_plis.plis_cd%type;
 p_cate_cd  cate_plis.cate_cd%type;
 p_cate_cd2  cate_plis.cate_cd%type;
 err_seq number(5);
-- 공급사 상품코드를  만듬
BEGIN
     -- 상품순차생성
     select nvl(max(seq),0)+1
     into p_seq
     from sco_prd_info
     where prd_cd = p_prd_cd
     and   sco_cd = p_sco_cd;
     -- 공급사 상품코드 생성
     sel

ect nvl(max(sco_prd_cd),0)+1
     into p_sco_prd_cd
     from sco_prd_info;
     -- 분류코드를 찾는다
     select cate_cd
     into p_cate_cd
     from cate_plis
     where plis_cd = p_plis_cd
     and   rownum = 1;
     -- 품목코드를 찾는다
     select plis_cd
     into p_plis_cd
     from prd_info
     where prd_cd = p_prd_cd;
     -- 공급사 상품코드를 만듬
           INSERT INTO sco_prd_info(sco_prd_cd,prd_cd,sco_cd,seq,buy_pri,supp_plis_cd,
                                    stat_flag,REG_MAN,REG_DATI)
                     VALUES ( p_sco_prd_cd,p_prd_cd,p_sco_cd,p_seq,p_buy_pri,p_supp_plis_cd,
                              '001','00000133',sysdate);
     -- 공급사별 분류코드를 넣음
     sp_create_sco_cate(p_sco_cd,1,p_cate_cd);
     -- 공급사별 품목분류코드를 넣음

     begin
        select cate_cd
        into p_cate_cd2
        from sco_cate_join
        where enpri_cd = p_sco_cd
        and   busip_seq = 1
        and   cate_cd = p_cate_cd;
           exception
           when no_data_found then
           INSERT INTO sco_cate_join(enpri_cd,busip_seq,cate_cd,plis_cd,REG_MAN,REG_DATI)
                     VALUES ( p_sco_cd,1,p_cate_cd,p_plis_cd,'00000133',sysdate);
     end;

END sp_create_sco_prd_cd;

 

CREATE OR REPLACE PROCEDURE          "SP_CREATE_SCO_PRD2"        -----------------------------------------------------
-- Usage          : 동일 공급사 상품코드인 경우 공급사상품코드를 최소가격으로 대체.
-- Argument(IN)   : 상품코드,공급사코드,매입가격,공급사품목코드,공급사상품코드
--                  (공급사 상품코드가 없는 경우에는 ' '로 보내줘야 함)
-- Argument(OUT)  : 공급사상품코드
-- Update         : 2001.08.10  백종린  - 최초작성
-----------------------------------------------------

  (p_prd_cd      IN prd_info.prd_cd%TYPE,
 p_sco_cd      IN sco_prd_info.sco_cd%type,
 p_buy_pri     IN sco_prd_info.buy_pri%type,
 p_supp_plis_cd IN sco_prd_info.supp_plis_cd%type,
 p_sco_prd_cd  IN OUT sco_prd_info.sco_prd_cd%TYPE)
IS
 p_sco_cd2  sco_prd_info.sco_cd%type;
 p_seq      sco_prd_info.seq%type;
 p_buy_pri2  sco_prd_info.buy_pri%type;
 p_cate_cd  cate_plis.cate_cd%type;
 p_cate_cd2  cate_plis.cate_cd%type;
 err_seq number(5);

-- 공급사 상품코드를  만듬
BEGIN
  -- 이전에 공급사 상품코드 유무 검사

 

   if p_sco_prd_cd = ' ' then
        select sco_prd_cd
        into p_sco_prd_cd
        from sco_prd_info
        where sco_cd = p_sco_cd
        and   prd_cd = p_prd_cd
        and   rownum = 1;
   else
        select sco_prd_cd
        into p_sco_prd_cd
        from sco_prd_info
        where sco_prd_cd = p_sco_prd_cd;
   end if;
        select nvl(min(buy_pri),0)
        into p_buy_pri2
        from sco_prd_info
        where sco_cd = p_sco_cd
        and   prd_cd = p_prd_cd;
        if p_buy_pri2 > p_buy_pri then
           update sco_prd_info set buy_pri = p_buy_pri2
           where sco_prd_cd = p_sco_prd_cd;
        end if;
        --goto nomal_case;

  exception
  when no_data_found then
       sp_create_sco_prd_cd(p_prd_cd,p_sco_cd,p_buy_pri,p_supp_plis_cd,p_sco_prd_cd);

 

--<< nomal_case >>
 --null;

END sp_create_sco_prd2;
/

 

CREATE OR REPLACE PROCEDURE          "SP_CREATE_PRD_UNIT"    (p_plis_cd     IN prd_info.plis_cd%type,
 p_plis_seq     IN prd_info.plis_seq%TYPE,
 p_att_val     IN varchar2,
 p_maker       IN prd_info.make_co_nm%TYPE,
 p_brand       IN prd_info.tmk_hang_nm%TYPE,
 p_model       IN prd_info.mdl_hang_nm%TYPE,
 p_unit        IN prd_info.unit%TYPE,
 p_prd_doc     IN prd_info.prd_doc%TYPE,
 p_prd_cd      IN OUT prd_info.prd_cd%TYPE)
IS
 p_cnt number(5);
 p_prd_cd2     prd_info.prd_cd%TYPE;
 err_seq   number(5);
-- 상품을 만듬
BEGIN


    sp_check_prd_cd(p_plis_cd,p_plis_seq,p_maker,p_brand,p_model,
    p_unit,p_prd_doc,p_prd_cd2);
/*  err_seq := F_ERR_SEQ();
  insert into err_log(seq, err_LIST,err_date)
                                                   values (err_seq,'상품코드'||p_prd_cd2,sysdate); */

    if p_prd_cd2 is null then

     -- 상품코드를 만듬
     p_prd_cd := sf_prd_cd_crt('E');
           INSERT INTO PRD_INFO(PRD_CD,PLIS_CD,MAKE_CO_NM,TMK_HANG_NM,MDL_HANG_NM,SHRT_DOC,
                                UNIT,REG_MAN,REG_DATI,PRD_DOC,STAT_FLAG,PLIS_SEQ)
                     VALUES ( p_prd_cd,p_plis_cd,p_maker,p_brand,p_model,p_att_val,
                              p_unit,'00000133',sysdate,p_prd_doc,'001',p_plis_seq);
    else p_prd_cd := p_prd_cd2;
    end if;

END sp_create_prd_unit;
/

 

#########################함수 소스###########################

 

CREATE OR REPLACE FUNCTION          "F_GET_PLIS_SEQ" -----------------------------------------------------
-- Usage          : 품목에서 속성요약순차 값을 만든다.
-- Argument(IN)   : 품목코드,Concat된 속성명,concat된 속성값
-- Return value   : 새로 만들어진 품목속성요약순차
-- Update         : 2001.08.10  백종린  - 최초작성
-----------------------------------------------------  

                      ( p_plis_cd in plis.plis_cd%TYPE,
     p_att    in varchar2,
     p_att_val in varchar2)
    -- ,p_plis_seq in out prd_info.plis_seq%TYPE)

RETURN varchar2 IS
   CURSOR PLIS_ATT is
      select distinct(plis_seq)
      from plis_att
      where plis_cd = p_plis_cd;
      
p_plis_seq2 prd_info.plis_seq%TYPE;
p_chk   varchar2(10);
p_cnt number(5);
p_count number(5);
p_not_found char(1);
p_att2   varchar2(1000);
p_att_val2   varchar2(3000);
p_index number(5);
p_index2 number(5);
p_max_att_cd att_def.att_cd%TYPE;
p_max_att_seq att_val.att_seq%TYPE;
p_att_cd att_def.att_cd%TYPE;
p_att_pri att_val.att_pri%TYPE;
p_att_seq att_val.att_seq%TYPE;
err_seq number(5);

BEGIN
   
--    p_plis_seq := null;
    p_index := f_get_array_index(p_att,'#$');
    p_count := 0;
    --정의된 속성명과 속성값이 있는 지를 찾아서 없으면 입력
    WHILE p_count < p_index LOOP
           p_count := p_count + 1;
           p_cnt:=0;
           p_att2 := f_get_array_val(p_att,'#$',p_count);
           p_att_val2 := f_get_array_val(p_att_val,'#$',p_count);
           sp_get_att_cd(p_plis_cd,p_att2,p_att_cd);         
           if p_att_cd = 0 then
              p_max_att_cd := f_get_max_att_cd(p_plis_cd);           
              INSERT INTO ATT_DEF (PLIS_CD, ATT_CD,ATT_NM,ATT_FLAG,REG_MAN,REG_DATI)
                      VALUES ( p_plis_cd,p_max_att_cd,p_att2,'001','00000133',sysdate);
              INSERT INTO ATT_VAL (PLIS_CD, ATT_CD,ATT_SEQ,ATT_PRI,REG_MAN,REG_DATI)
           

           VALUES ( p_plis_cd,p_max_att_cd,1,p_att_val2,'00000133',sysdate);
           else
                 sp_get_att_pri(p_plis_cd,p_att_cd,p_att_val2,p_att_seq);         
                 if p_att_seq = 0 then
                    p_max_att_seq := f_get_max_att_seq(p_plis_cd,p_att_cd);           
                    INSERT INTO ATT_VAL (PLIS_CD, ATT_CD,ATT_SEQ,ATT_PRI,REG_MAN,REG_DATI)
                          VALUES ( p_plis_cd,p_att_cd,p_max_att_seq,p_att_val2,'00000133',sysdate);
                 end if;
           end if;
     END LOOP;
     OPEN PLIS_ATT;
     LOOP
              FETCH PLIS_ATT INTO p_plis_seq2;
              EXIT WHEN PLIS_ATT%notfound;
              if f_check_att_cd(p_plis_cd,p_plis_seq2,p_att,p_att_val) then
/*    err_seq := F_ERR_SEQ();
                 

 insert into err_log(seq, err_LIST,err_date)
                                    values (err_seq,'품목코드2'||p_plis_cd||p_plis_seq2,sysdate);  */
                        return p_plis_seq2;
              end if;
     end LOOP;
     CLOSE PLIS_ATT;
     sp_make_plis_seq(p_plis_cd,p_att,p_att_val,p_plis_seq2);
     return p_plis_seq2;
        
EXCEPTION
   when others then
       return(p_plis_seq2);

         
END F_GET_PLIS_SEQ;
/

 

#######################prepareCall 설명##########################

 

7. CallableStatement


7.1 개요

CallableStatement 객체는 모든 DBMS들에 대한 표준방법으로 저장 프로시져(stored procedure)를 호출하는 방법을 제공한다.
저장 프로시져를 데이터베이스에 저장하고, 저장 프로시져의 호출은 CallableStatement 객체를 사용한다.
이러한 호출은 두가지 형태중의 하나인 escape 문법으로 작성되어진다.
즉, 결과 매개변수를 가지는 형태와 결과 매개변수가 없는 형태.(escape 문법에 대한 내용은 4장 "Statement"를 찹조해라). OUT 매개변수의 종류인 결과 매개변수는 저장 프로시져에 대한 리턴값이다.
두 형태 모두 입력(IN 매개변수), 출력(OUT 매개변수) , 또는 둘다(INOUT 매개변수)를 위해 사용되어지는 매개변수의 변수 번호를 가질 것이다.
?는 매개변수의 위한 저장위치로 취급된다.

JDBC에서 저장 프로시져를 호출하기 위한 문법은 아래와 같다. "[ ]"내의 내용은 선택적이다. 

        {call procedure_name[(?, ?, ...)]}

결과 매개변수를 리턴하는 프로시져를 위한 문법은 다음과 같다.: 

        {? = call procedure_name[(?, ?, ...)]}

매개변수가 없는 저장 프로시져를 위한 문법은 다음과 같을 것이다. : 

        {call procedure_name}


보통, CallableStatement 객체를 생성하는 사람은 이미 사용하는 DBMS가 저장 프로시져를 지원하고 그러한 프로시져들이 있는 곳을 안다는 것이다.
그러나 검사할 필요가 있다면, 다양한 DatabaseMetaData 메쏘드들을 사용하여 정보를 얻는다.
예를 들어, supportsStoredProcedures 메쏘드는 DBMS가 저장 프로시져 호출을 지원하면 true를 리턴하며, getProdedures 메쏘드는 유효한 저장 프로시져의 설명을 리턴한다.

CallableStatement는 일반적으로 SQL문들을 처리하는 Statement 클래스를 상속받고, 또한 IN 매개변수를 처리하는 PreparedStatement 메쏘드들을 상속받는다.
CallableStatement에 정의된 모든 메쏘드들은 OUT 매개변수들이나 INOUT 매개변수의 출력쪽을 처리한다.
즉, 이러한 메쏘드들은 OUT 매개변수의 SQL형들을 등록하거나, 그것들로부터 값들을 검색하거나, 리턴된 값이 SQL NULL 인지를 검사한다.


 

7.1.1. CallableStatement 객체 생성하기

 

CallableStatement 객체는 Connection의 prepareCall 메쏘드에 의해 생성된다.
아래의 예제는 저장 프로시져 getTestData의 호출을 포함하는 CallableStatement의 인스턴스를 만든다.
이것은 두 개의 인자들을 가지고 있고 결과 매개변수는 없다.: 

        CallableStatement cstmt = con.prepareCall("{call getTestData(?, ?)}");

? 위치가 IN, OUT, 또는 INOUT 매개변수인지는 저장 프로시져 getTestData에 의존한다.

7.1.2 IN/OUT 매개변수들

CallableStatement객체로 IN 매개변수를 넘겨주는 것은 PreparedStatement로부터 상속받은 setXXX 메쏘드이다.
넘겨질 값의 데이터형은 사용할 setXXX 메쏘드를 결정한다.(setFloat는 float 값을 넘겨준다.등등)

만약 저장 프로시져가 OUT 매개변수를 리턴한다면, 각 OUT 매개변수의 SQL형은 CallableStatement 객체를 실행할 수 있기 전에 등록되어져야 한다.(이것은 몇몇 DBMS들이 SQL형을 필요로 하기 때문에 필요하다.)
SQL형을 등록하는 것은 registerOutParameter 메쏘드를 사용한다.
그리고나서 SQL문이 실행되어진 다음에, CallableStatement의 getXXX 메쏘드는 매개변수 값을 검색한다.
사용하는 정확한 getXXX 메쏘드는 그 매개변수를 위해 등록되어진 SQL에 상응하는 자바형이다.(SQL형들을 자바형들로의 표준 매핑은 8.5.1장의 테이블에서 보여준다.)
바꾸어말하면, registerOutParameter는 SQL형을 사용하고(데이터베이스가 리턴할 SQL형과 매칭하기위해서), getXXX는 이것을 자바형으로 캐스트(cast)한다.

다음의 코드는 OUT 매개변수들을 등록하고, cstmt에 의해 호출되는 저장 프로시져를 실행하고, 그런다음 OUT 매개변수들내에서 리턴된 값을 검색한다.
getByte 메쏘드는 첫 번째 OUT 매개변수로부터 자바 byte를 검색하고, getBigDecimal은 두 번째 OUT 매개변수로부터 (소수점 뒤에 세 개의 숫자를 가진) BigDecimal 객체를 검색한다.: 

        CallableStatement cstmt = con.prepareCall("{call getTestData(?, ?)}"); 
        cstmt.registerOutparameter(1, java.sql.Types.TINYINT); 
        cstmt.registerOutparameter(2, java.sql.Types.DECIMAL, 3); 
        cstmt.executeQuery(); 
        byte x = cstmt.getByte(1); 
        java.math.BigDecimal n = cstmt.getBigDecimal(2, 3);

ResultSet와는 달리, CallableStatement는 점진적으로 큰 OUT값들을 검색하기 위한 특별한 메카니즘을 제공하지 않는다.

7.1.3 INOUT 매개변수들

출력을 받아들이고 입력도 공급하는 매개변수(INOUT 매개변수)는 PreparedStatement로부터 상속받은 적당한 setXXX메소드의 호출외에 registerOutParamter 메쏘드의 호출을 요구한다.
setXXX메쏘드는 매개변수의 값을 입력 매개변수로 설정하고 registerOutParameter는 그것의 SQL형을 출력 매개변수로 등록한다.
setXXX 메쏘드는 드라이버가 SQL 값으로 변환된후에 데이터베이스로 전송할 자바 값을 제공한다.
이 IN값의 SQL형과 registerOutParameter 메쏘드에 공급되어지는 값은 같아야만한다.
그런다음 출력값을 검색하기위해서, 상응하는 getXXX 메쏘드를 사용한다.
예를 들어, 자바형이 byte인 매개변수는 입력값을 할당하기위해서 setByte 메쏘드를 사용하고, TINYINT를 registerOutParameter의 SQL형으로 공급하고, 출력값을 검색하기위해서 getByte를 사용해야한다.
(자세한 내용은 8장 "SQL과 자바형들을 매핑하기"를 참조해라)

다음의 예제는 유일한 매개변수로 INOUT 매개변수를 가지고 있는 저장 프로시져 reviseTotal이 있다는 것을 가정한다.
setByte 메쏘드는 매개변수를 25로 설정하며, 이것은 드라이버가 SQL TINYINT로써 데이터베이스에 전송할 것이다.
그런다음 registerOutParameter는 SQL TINYINT로써 매개변수를 등록한다.
저장 프로시져가 실행된 후에, 새로운 SQL TINYINT 값을 리턴하고, getByte 메쏘드는 이 새로운 값을 자바 byte로 검색할 것이다. 

        CallableStatement cstmt = con.prepareCall("{call reviseTotal(?)}"); 
        cstmt.setByte(1, 25); 
        cstmt.registerOutparameter(1, java.sql.Types.TINYINT); 
        cstmt.executeUpdate(); 
        byte x = cstmt.getByte(1);


7.1.4 결과후에 OUT Parameter 검색

몇몇 DBMS들에 있는 제한 때문에, 최대이식성을 위해서 CallableStatement 객체의 실행에 의해 생성된 모든 결과들은 OUT 매개변수들이 CallableStatement.getXXX 메쏘드를 사용하여 검색되기전에 검색되어져야한다.

만약 CallableStatement 객체가 다중 ResultSet 객체들을 리턴한다면(execute 메쏘드 호출을 이용하여), 모든 결과들은 OUT 매개변수를 검색하기전에 검색되어질 것이다. 이러한 경우에, 모든 결과들이 접근되어지는 것을 확인하기위해서, Statement 메쏘드들 getResultSet, getUpdateCount, 그리고 getMoreResults는 더 이상의 결과가 없을때까지 호출되어져야한다.

이것을 실행한후에, OUT 매개변수들로부터의 값들은 CallableStatement.getXXX 메쏘드들을 사용하여 검색될 수 있다.


 

7.1.5 OUT 매개변수들로써 NULL 값들을 검색하기

 

OUT 매개변수로 리턴되는 값은 SQL NULL일 것이다. 이러한 것이 발생했을 때, SQL NULL 값은 getXXX 메쏘드에 의해 리턴된 값이 getXXX 메쏘드 형에 의존하여 null, 0 또는 false가 되도록 변환되어져야 한다.
ResultSet 객체에서 처럼, 0 또는 false값이 원래 SQL NULL인지 알 수 있는 유일한 방법은 wasNull 메쏘드를 가지고 검사하는 것이다.
wasNull 메쏘드는 getXXX 메쏘드에 의해 읽은 마지막 값이 SQL NULL 이면 true, 아니면 false를 리턴한다.
자세한 내용은 5장 "ResultSet"은 참조해라.


저작자표시

관련글 더보기

티스토리툴바