odac, Table-type변수를 이용한 데이터 저장(oracle, procedure, delphi)

이번에는 devart-odac로 오라클 프로시저 (oracle procedure )에  파라미터 타입(parameter-type)을 table-type의 형태로 넘겨 처리하는 걸 해보겠습니다.

 

오라클 procedure에 하나의 값을 파라미터로 전달하는 게 아니라.. 마치 배열과 같은 형태의 값 전달이라고 보시면 되겠습니다. 하지만 오라클의 배열형의 경우 선언할 때 반드시 사이즈를 지정해야 하므로 가변 배열 형태의 값을 전달하기 번거롭겠죠.. 배열의 빈 공간을 체크하며 처리해야 하니까요..

 

이런 불편함을 해결해 줄 변수가 table-type변수입니다.

create or replace type nest_varchar2_4000 is table of varchar2(4000);

저는 테스트를 위해 4000byte의 공간을 갖는 1개의 열을 가진 테이블 타입의 변수를 만들었습니다.

 

10개나 100개 내가 원하는 만큼의 값을 집어 넣어 처리할 수 있어 한꺼번에 저장하는 형태의 프로그램 개발에 매우 효과적입니다.

 

테스트를 진행 할 프로시저를 간단하게 만들었습니다.

CREATE OR REPLACE Procedure pc_test_procedure
 (
      I_GBN     IN VARCHAR2
     ,o_nest_varchar2_4000 in out nest_varchar2_4000
    
     ,OUT_RC1              OUT SYS_REFCURSOR
     ,O_RSLT_MSG_DT        OUT NOCOPY VARCHAR2
 )
    AUTHID CURRENT_USER as

begin    
   if I_GBN = 'TEST_INSERT_TABLE_TYPE' then           
         begin
              for rec1 in ( select * from table(o_nest_varchar2_4000) ) 
              loop
                  insert into test1 ( WKPERS,NM,STAT,WK_DTE,GRP,DUTY )
                             values ( rec1.column_value 
                                     -- 1개 열을 갖는 TABLE-TYPE의 컬럼이름은 column_value
                                     ,trunc(dbms_random.value(0,100))
                                     ,'C'
                                     ,sysdate
                                     ,'B'
                                     ,t_cnt
                                     );   
              end loop;
              
         exception
              when others then  
                   O_RSLT_MSG_DT := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || 
                                    DBMS_UTILITY.FORMAT_ERROR_STACK     || 
                                    ' '                                 || 
                                    DBMS_UTILITY.FORMAT_CALL_STACK;
                   rollback;   ---- insrt rollback..  
                   return;     ---- 여기 까지만 실행하고.. 프로시저를 끝내버림...
              
         end;
         
         O_RSLT_MSG_DT := '성공!';
         commit;
         
         
                
   end if;
   
end pc_test_procedure;

 

살펴보자면.. 

테이블 타입의 변수인 o_nest_varchar2_4000에 담아 온 값들을 loop를 돌려가며 test1 테이블에 저장하는 것입니다.

 

delphi-odac-procedure 테스트를 위해 버튼추가

 

델파이 폼에 버튼을 하나 올려서 클릭 시 아래와 같이 3개의 값을 table-type 변수인 o_nest_varchar2_4000 전달합니다.

 

중요한 것은 해당 파라미터의 타입을 아래 소스처럼 지정해 줘야 합니다.

이 파라미터가 string인지 datetime인지 table인지 object인지 말이죠.. 

procedure TForm1.Button2Click(Sender: TObject);
var
    OraSession1: TOraSession;
    OraStoredProc1: TOraStoredProc;

    i : integer;
begin

         try

              OraSession1               := TOraSession.Create(nil);
              OraStoredProc1            := TOraStoredProc.Create(nil);

              OraSession1.ConnectString := '계정/비번@서버:포트/SERVICENAME';
              OraSession1.Options.Direct:= true;
              OraSession1.AutoCommit    := false;  //기본은 true임..

              OraSession1.Pooling       := false;
              OraSession1.Connect;

              OraStoredProc1.Session    := OraSession1;

              OraStoredProc1.StoredProcName := 'pc_test_procedure';
              OraStoredProc1.PrepareSQL;

              OraStoredProc1.ParamByName('I_GBN' ).AsString := 'TEST_INSERT_TABLE_TYPE';


              OraStoredProc1.ParamByName('o_nest_varchar2_4000' ).DataType := ftTable;

              WITH OraStoredProc1.ParamByName('o_nest_varchar2_4000' ).AsTable do
              begin
                   AllocObject(OraSession1.OCISvcCtx, 'nest_varchar2_4000');

                   AttrAsString['[0]'] := '11111';
                   AttrAsString['[1]'] := '22222';
                   AttrAsString['[2]'] := '33333';
              end;

              OraStoredProc1.Execute;


              showmessage(
                  OraStoredProc1.ParamByName('O_RSLT_MSG_DT').AsString
              );


         finally

              OraSession1.Disconnect;
              OraStoredProc1.Free;
              OraSession1.Free;
         end;

end;

 

소스를 컴파일하고 실행하여 버튼을 누르면 아래와 같이 한꺼번에 저장할 수 있습니다.

 

table-type변수를 이용한 저장, 해당 sql 확인

 

어떤가요? 물론 이것 만으론 모두 만족할 순 없지만 다른 형태 또한 포스팅하겠습니다. object형과 진정한 table-type형이 남아있네요! 감사합니다.