DELPHI ODAC, Table of Object TYPE 변수이용 ORACLE Procedure 저장! 마지막

앞에 object-type과 table-type을 이용한 저장 방법을 설명드렸습니다. 이번에 하려 하는 것은 table(테이블) 타입이긴 하지만 어떻게 보면 실무에서 정말 필요한 방식이라고 생각합니다.

 

한꺼번에 이차원 배열 형태의 데이터를 하나의 파라미터로 처리할 수 있기 때문입니다. 이런 타입이 지원됨으로 인해 프로시저를 loop를 돌려가며 값을 처리하는 방식을 획기적으로 단순화시킬 수 있으며 항목이 추가될 때마다 입력 파라미터를 수정해주고 오류에 대한 이슈 때문에 새로운 프로시저를 만들어야 하는 과정을 줄일 수 있습니다!

 

핵심은 타입 변수를 두 번 선언해 주는 것입니다.

 

create or replace Type Ty_200_OBJ as Object
(
       o_D001 VARCHAR2(4000)
      ,o_D002 VARCHAR2(4000)
      ,o_D003 VARCHAR2(4000)
      ,o_D004 VARCHAR2(4000)
      ,o_D005 VARCHAR2(4000)
      ,o_D006 VARCHAR2(4000)
      ,o_D007 date
      ,o_D008 number
      ,o_D009 blob
      ,o_D010 clob
      ....(생략)...
 );
 
 
 create or replace TYPE TB_200_OBJ AS TABLE OF TY_200_OBJ;

 

보시다시피 여러 개의 값을 갖는 object-type변수를 선언하고.. 그 변수를 기반으로 한 table-type 변수를 선언하는 것입니다.

 

varchar2, date, number, clob, blob 다 됩니다. 범용성을 생각하여 넉넉하게 만들어 두시면 여러모로 쓸모가 있습니다.

 

테이블 타입의 변수이기 때문에 배열처럼 사이즈 문제를 걱정할 필요가 없습니다.  몇 만 줄 이러면 모르겠습니다. 테스트를 거기까지 해보진 않았습니다.

 

 

테스트를 진행할 프로시저를 만들어보겠습니다. 지난번에 해 놓은걸 복사해서 조금 수정해 준비했습니다.

 

CREATE OR REPLACE Procedure pc_test_procedure
 (
      I_GBN           IN VARCHAR2
     ,I_TB_200_OBJ    IN TB_200_OBJ
     
     ,OUT_RC1         OUT SYS_REFCURSOR
     ,O_RSLT_MSG_DT   OUT NOCOPY VARCHAR2
 )
 
   AUTHID CURRENT_USER as

begin

    if I_GBN = 'TEST_INSERT_OBJECT_TYPE2' then           
         begin
              for rec1 in ( select o_D001
                                  ,o_D002
                                  ,o_D003
                                  ,o_D004
                                  ,o_D005
                              from table(I_TB_200_OBJ) ) 
              loop
                  insert into test1 ( WKPERS,NM,STAT,WK_DTE,GRP,DUTY )
                             values ( rec1.o_D001
                                     ,rec1.o_D002
                                     ,rec1.o_D003
                                     ,sysdate
                                     ,rec1.o_D004
                                     ,rec1.o_D005
                                     );   
              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;  --|| SQLCODE || ' ' ||SQLERRM;
                   rollback;   ---- insrt rollback..  
                   return;     ---- 여기 까지만 실행하고.. 프로시저를 끝내버림...
              
         end;
         
         O_RSLT_MSG_DT := '성공!';
         commit;           
                
   end if;
end pc_test_procedure;

 

for in loop를 돌려서 프로시저 내부에서 입력을 할 수 있습니다. 테이블과 똑같은 형태의 데이터라면.. loop문을 제거하고 BULK INSERT처럼 처리해도 무방합니다.

 

 

이것은 델파이쪽에서 table-type변수에 데이터 입력과 프로시저를 호출하여 처리하는 부분입니다.

 

procedure TForm1.Button5Click(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_OBJECT_TYPE2';


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

              WITH OraStoredProc1.ParamByName('I_TB_200_OBJ' ).AsTable do
              begin
                   AllocObject(OraSession1.OCISvcCtx, 'TB_200_OBJ');

                   for i := 0 to 9 do
                   begin
                       ItemAsObject[i].AttrAsString['o_D001'] := inttostr(i);
                       ItemAsObject[i].AttrAsString['o_D002'] := 'NM_'+inttostr(i);
                       ItemAsObject[i].AttrAsString['o_D003'] := 'STAT_'+inttostr(i);
                       ItemAsObject[i].AttrAsString['o_D004'] := 'GRP_'+inttostr(i);
                       ItemAsObject[i].AttrAsString['o_D005'] := inttostr(i);
                   end;

              end;

              OraStoredProc1.Execute;


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


         finally

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


end;

 

보시면 아시겠지만.. loop를 10번돌려 값을 입력하는 것입니다. 이것을 필요에 맞게 조정하여 사용하시면 되겠습니다.

 

프로그램을 컴파일하고 실행시켜보면.. 아래처럼 잘 작동하는 것을 확인할 수 있습니다.

 

delphi-odac procedure table of object type save

 

이 정도면.. 실무에서 사용하기 매우 편하지 않을까 생각합니다. 한꺼번에 데이터를 입력할 수 있으며 게다가 1개의 변수로..  불필요하게 반복하여 프로시저를 호출할 필요가 없으니 소스를 간결하게.. 성능을 높일 수 있습니다.

그리고 Procedure이니.. 소설을 쓸 수 있어 DB 데이터 작업이 훨씬 편리해질 것입니다. 감사합니다.