[phil-story] Always Fighting 

IT_Expert/DataBase | Posted by 낫기법필 2007. 10. 22. 20:42

[Oracle] 오라클에서 프로시져 사용할때 그 안에서 임시테이블 이용방법

이것은 프로시져 내에서는 create table이 사용될 수 없다.
하지만 다음과 같은 방법을 이용하면 프로시져 내에서 커서를 이용해서 검색한 내용을 임시로 테이블을 생성해서 리턴값으로 임시테이블 이름을 넘어준다.

사용자는 파라메터 값을 넘겨주면서 프로시져를  호출하면 넘겨받은 임시테이블 이름으로 select를 하면 자신이 알고싶은 결과 집합을 쉽게 알수있다.

주의할점은 결과 집합을 쿼리후에는 반드시 삭제해줘야한다. 왜냐면 임시테이블이 계속 생성되므로, 데이터베이스안에 계속누적될수 있기떄문이다.

[환경설정]
우선 로그인 계정이 create table의 권한이 있어야한다.
TBL_SX_TBLCNT 의 이름으로 시퀀스를 생성한다.
start with = 1
min value = 1
max value = 99999
increment by = 1
cycle = true
number to cache = 20
order = false

이렇게 시퀀스를 생성하고 나면 프로시져내에서 테이블을 생성하게 만들 패키지를 호출해야하는데 이 패키지의 이름은 STR_SX_COM 이다. 내용은

CREATE OR REPLACE PACKAGE str_sx_Com AS
TYPE Tblname_tbl     IS  TABLE OF varchar2(100)
     INDEX BY BINARY_INTEGER;
TYPE TblCnt_tbl      IS  TABLE OF number
     INDEX BY BINARY_INTEGER;
TYPE Fldname_tbl     IS  TABLE OF varchar2(50)
     INDEX BY BINARY_INTEGER;
TYPE SubProdCode_tbl IS  TABLE OF varchar2(6)
     INDEX BY BINARY_INTEGER;
TYPE NeedQty_tbl     IS  TABLE OF number
     INDEX BY BINARY_INTEGER;

    PROCEDURE sx_ComCreTbl                           -- Tbl 생성
       (o_Tblname       out      varchar2,
        i_Cnt           in       number,
        i_Fldname       in       Fldname_tbl);

    PROCEDURE sx_ComDelTblname                       -- 주어진 Table Drop
       (i_Tblname       in       varchar2,
        o_Cnt           out      number);

END str_sx_Com;
/

이렇게 패키지를 생성한 후에 이 패키지안에 들어갈 두개의 프로시져를 생성한다. 내용은

CREATE OR REPLACE PACKAGE BODY str_sx_Com AS
/*--------------------------------------------------------------------------*/
/* Create tbl                                                                                                */
/* grant create table to ace with grant option                                                   */
/*--------------------------------------------------------------------------*/
    PROCEDURE sx_ComCreTbl
       (o_Tblname       out      varchar2,
        i_Cnt           in       number,
        i_Fldname       in       Fldname_tbl)
    IS
    w_no             number(3);
    w_Tblname        varchar2(100);
    w_Sql            varchar2(1000);
    w_TblNo          number(7);
    w_Ignore         number := 0;
    cursor_name      number;
    w_FldTit         varchar2(27) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    w_FldAttr        varchar2(2);
    w_FldLen         varchar2(9);
    input_para_err   exception;
    begin
    dbms_output.ENABLE;
    if  i_Cnt > 26 then
        raise   input_para_err;
    end if;
/*
    select max(tname)
      into w_Tblname
      from tab
     where tname like 'TBL_ZZ%';
    if  substr(w_Tblname, 5, 2) = 'ZZ' then
        w_TblNo := to_number(substr(w_Tblname, 7, 5)) + 1;
    else
        w_TblNo := 1;
    end if;
*/
    --- ----------------------------------------------------------------------
    --- 화일 고유번호 생성
    --- ----------------------------------------------------------------------
    select  tbl_sx_tblcnt.nextval
      into  w_TblNo
      from  dual;
    --- ----------------------------------------------------------------------
    --- 화일 고유번호 생성
    --- ----------------------------------------------------------------------
    w_Tblname   := 'TBL_ZZ' || substr(to_char(w_TblNo, '09999'), 2, 5);
    o_Tblname   := w_Tblname;
    w_Sql := 'create table ' || w_Tblname || ' (';
    for w_n in 1..i_Cnt loop
        w_Sql := w_Sql || substr(w_FldTit, w_n, 1) || ' ';
        w_FldAttr := substr(i_Fldname(w_n), 1, 1);
        w_FldLen  := substr(i_Fldname(w_n), 2, length(i_Fldname(w_n)) -1);
        if    w_FldAttr = 'c' or w_FldAttr = 'C'  then
              w_Sql := w_Sql || ' char(';
        elsif w_FldAttr = 'v' or w_FldAttr = 'V'  then
              w_Sql := w_Sql || ' varchar2(';
        elsif w_FldAttr = 'n' or w_FldAttr = 'N'  then
              w_Sql := w_Sql || ' number(';
        else
              raise   input_para_err;
        end if;
        if  w_n = i_Cnt then
            w_Sql := w_Sql || w_FldLen || ')';
        else
            w_Sql := w_Sql || w_FldLen || '), ';
        end if;
    end loop;
    w_Sql := w_Sql || ')';
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name, w_Sql, dbms_sql.NATIVE);
    w_Ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
    exception
        when  input_para_err then
              dbms_output.put_line('[sql error. please check tbl_name, fld name]=>');
              dbms_output.put_line('input parameter error !!!');
              o_Tblname   := 'XXXXX';
        when  others then
              dbms_output.put_line('[sql error. please check tbl_name, fld name]=>');
              dbms_output.put_line('sqlerr =[' || sqlerrm || ']');
              dbms_output.put_line('sqlcode=[' || to_char(sqlcode) || ']');
              dbms_sql.close_cursor(cursor_name);
              o_Tblname   := 'XXXXX';
    end sx_ComCreTbl;
/*--------------------------------------------------------------------------*/
/* TABLE delete and del cnt RETURN procedure                                              */
/*--------------------------------------------------------------------------*/
    procedure sx_ComDelTblname
       (i_Tblname       in       varchar2,
        o_Cnt           out      number)
    is
    cursor_name     number;
    w_Cnt           number;
    w_Tbl           varchar2(21);
    w_Sql           varchar2(255);
    begin
    w_TBL := i_Tblname;
    w_Sql := 'drop table ' || w_TBL || ' CASCADE CONSTRAINTS';
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name, w_Sql, dbms_sql.V7);
    w_Cnt   := dbms_sql.execute(cursor_name);
    o_Cnt   := w_Cnt;
    dbms_sql.CLOSE_cursor(cursor_name);
    exception
       when others then
         dbms_output.put_line('[TBL NOT FND]');
         o_Cnt    := 1403;
         if dbms_sql.is_open(cursor_name) then
            dbms_sql.close_cursor(cursor_name);
         end if;
END sx_ComDelTblname;
END str_sx_Com;
/

이렇게 하면 환경설정은 끝난다.
다음은 내가 작성한 조회 패키지 예이다.물론 패키지라해봤자 프로시져 하나 있지만...^^;

CREATE OR REPLACE PACKAGE str_sn_ItemList  As
    PROCEDURE  sn_ItemList
               (i_MemberId      in       varchar2,
                o_TblName       out      varchar2);
END str_sn_ItemList;
/

CREATE OR REPLACE PACKAGE BODY str_sn_ItemList As
    PROCEDURE  sn_ItemList
               (i_MemberId      in       varchar2,
                o_TblName       out      varchar2)
    IS
    w_AuctionId                number(13)  := 0;
    w_ItemId                number(13)  := 0;
    w_SellerId                varchar2(20):='';
    w_MaxAmt                number(13)  := 0;
    w_BuyerId                varchar2(20):='';
    w_Proc                varchar2(10):='';
    w_item_form                varchar2(20):='';
    w_item_kind                varchar2(20):='';
    w_item_name                varchar2(40):='';
    w_startAmt                number(13)  := 0;
    w_endDate                varchar2(10):='';
--- ------------------------------------------------------------------------
--- declare variable from table create                                    --
--- ------------------------------------------------------------------------
    w_FldName        str_sx_com.FldName_tbl;
    w_TblName        varchar2(50);
    w_Result         number(4);
--- ------------------------------------------------------------------------
--- cursor define                                                         --
--- ------------------------------------------------------------------------
    cursor DataFetch1_Cursor is

        select 경매번호, 물품번호, 판매자, tender_amt, 구매자, okauction  
          from (select m.member_id 판매자, i.item_id 물품번호, a.okauction, t.member_id 구매자,
                       t.auction_id 경매번호, td.tender_amt
                  from member m, item i, auction a, tender t, tender_detail td
                 where  m.member_id  = i.member_id  and
                        i.item_id    = a.item_id    and
                        a.auction_id = t.auction_id and
                        t.tender_id  = td.tender_id and
                        m.member_id  = 'longlee') t
         where tender_amt = (select max(tender_amt)
                               from tender_detail, tender
                              where tender.tender_id  = tender_detail.tender_id and
                                    tender.auction_id = t.경매번호);


    BEGIN
--- ------------------------------------------------------------------------
--- create table                                                          --
--- ------------------------------------------------------------------------
    w_FldName(1)   := 'N13'; -- 경매번호
    w_FldName(2)   := 'N13'; -- 물품번호
    w_FldName(3)   := 'V20'; -- 물품형태
    w_FldName(4)   := 'V20'; -- 물품종류
    w_FldName(5)   := 'V40'; -- 물품이름
    w_FldName(6)   := 'N13'; -- 시작금액
    w_FldName(7)   := 'V15'; -- 종료일
    w_FldName(8)   := 'N13'; -- 최고금액
    w_FldName(9)   := 'V20'; -- 낙찰자
    w_FldName(10)  := 'V10'; -- 진행여부

    str_sx_Com.sx_ComCreTbl(w_TblName, 10, w_FldName);
    dbms_output.enable(100000);
--dbms_output.put_line(' Start ');
    o_TblName       := w_TblName;

--dbms_output.put_line(' o_TblName=>'||o_TblName||'<');

    OPEN DataFetch1_Cursor;
    LOOP
        FETCH DataFetch1_Cursor INTO
                w_AuctionId, w_ItemId, w_SellerId, w_MaxAmt, w_BuyerId, w_Proc;
        EXIT WHEN DataFetch1_Cursor%NOTFOUND;
    --- 물품형태,물품종류,물품이름
        begin
            select  item_form,item_kind,item_name  into  w_item_form,w_item_kind,w_item_name
              from  item
             where  item_id = w_itemId;
         exception
              when  no_data_found then
                    w_item_form :='';
                    w_item_kind :='';
                    w_item_name :='';
        end;

    --- 시작금액,종료일
        begin
            select  startAmt, endDate  into  w_startAmt, w_endDate
              from  auction
             where  auction_id = w_AuctionId;
         exception
              when  no_data_found then
                    w_startAmt := 0;
                    w_endDate  := '';
        end;
    end Loop;
    CLOSE DataFetch1_Cursor;

--- ------------------------------------------------------------------------
--- insert table                                                          --
--- ------------------------------------------------------------------------
    w_Result := str_InsTblF(w_TblName,
                            w_AuctionId,
                            w_ItemId,
                            w_item_form,
                            w_item_kind,
                            w_item_name,
                            w_startAmt,
                            w_endDate,
                            w_MaxAmt,
                            w_BuyerId,
                            w_Proc);

    commit;

END sn_ItemList;
END str_sn_ItemList;
/

만일 위에서 오라클에 카탈도중 에러가 발생한다면. 프로시져 테스트 프로그램이 없으므로

sql>set serveroutput on

을 하고나서 프로시져 바디안에

dbms.output_put.line('출력할 내용 기술');

을 사용하면 디버깅에 이용할 수 있다

패키지 실행을 시키기 위해서는 다음처럼 실행한다.
sql> var g_name varchar2
sql> execute str_sn_itemlist.sn_itemlist('longlee',:g_name);

sql> print g_name
이렇게 하면 리턴된 임시테이블 명이 나올것이다.
tbl_99999  <--이런식으로.........

그럼

select * from tbl_99999 로 원하던 데이터의 값을 확인 할 수 있다.


이상...........................끝
패키지 실행을 시키기 위해서는 다음처럼 실행한다.
sql> var g_name varchar2
sql> execute str_sn_itemlist.sn_itemlist('longlee',:g_name);

sql> print g_name
이렇게 하면 리턴된 임시테이블 명이 나올것이다.
tbl_99999  <--이런식으로.........

그럼

select * from tbl_99999 로 원하던 데이터의 값을 확인 할 수 있다.


이상...........................끝




[펌] http://blog.naver.com/mijugari/120031432859

티스토리툴바