개발 공부 - Oracle 상위 부서명만 알 때 하위 부서 전체 조회 프로시저

create or replace PROCEDURE DEPT_NAME_SEARCH (
  V_Cursor    OUT SYS_REFCURSOR,   CDEPTNM IN VARCHAR2
)AS         tq                      varchar2(4000);    tempdeptid       varchar2(4000);
  tempdeptids     varchar2(4000);
  tempdeptnm    varchar2(4000);
  tempdeptlist     varchar2(4000);
  tempdeptlists    varchar2(4000);
  deptids              varchar2(4000);
  deptnms            varchar2(4000);    
  tempcode          varchar2(4000);    
  TMP_CNT           int;
     Cursor deptIdCursor IS   select dept_id, dept_nm into tempdeptid, tempdeptnm from cm_dept where dept_nm like  '%' || CDEPTNM || '%';
Cursor TMDCursor IS   select VALUETMD, LABELTMD from TMD;
    
BEGIN   select count(*) into TMP_CNT from user_tables where table_name='TMD';  
  if TMP_CNT > 0 then
    EXECUTE IMMEDIATE 'DROP TABLE TMD';
    select 0 into TMP_CNT from dual;
  end if; 
    
  if TMP_CNT = 0 then 
    tq  := 'CREATE GLOBAL TEMPORARY TABLE TMD (
      VALUETMD               VARCHAR2(1000),     --id
      LABELTMD                VARCHAR2(1000),      --name
      messageWordKo       VARCHAR2(1000),
      messageWordEn       VARCHAR2(1000),
      messageWordZh       VARCHAR2(1000),
      messageExplain        VARCHAR2(1000),
      messageId                 VARCHAR2(1000)     ) ON COMMIT DELETE ROWS';
    EXECUTE IMMEDIATE tq;   end if;
  open deptIdCursor;
  loop
    Fetch deptIdCursor into deptids, deptnms;  
      exit when deptIdCursor%notFound;            
    insert into TMD (VALUETMD,  LABELTMD , messageId ) values (deptids, deptnms, deptnms);   end loop; close deptIdCursor;
open TMDCursor;
  loop
    Fetch TMDCursor into  deptids, deptnms;  
      exit when TMDCursor%notFound;            
           select deptids into tempdeptids from dual;            
     select deptnms into tempdeptlists from dual;
           
       while tempdeptids != 'COMP001' LOOP
         select upper_dept_id into tempdeptids from cm_dept where dept_id = tempdeptids;
         select dept_nm into tempdeptlist from cm_dept where dept_id = tempdeptids;
         tempdeptlists := tempdeptlist || ' > ' || tempdeptlists;
       END LOOP;
     
       tq  := 'update TMD set LABELTMD =  ''' || tempdeptlists ||''' 
       ,messageWordKo =  ''' || tempdeptlists ||''' 
       ,messageWordEn =  ''' || tempdeptlists ||''' 
       ,messageWordZh =  ''' || tempdeptlists ||''' 
       ,messageExplain =  ''' || tempdeptlists ||''' 
       ,messageId =  ''' || tempdeptlists ||''' 
       where VALUETMD = ''' ||deptids|| ''' ' ;
       --DBMS_OUTPUT.PUT_LINE(tq);
       EXECUTE IMMEDIATE tq; 
   end loop;
 close TMDCursor;
tq := ' SELECT           
  VALUETMD as value,
  LABELTMD as label
  FROM TMD';
OPEN V_Cursor FOR tq;
END DEPT_NAME_SEARCH;