DATOR


테이블명 입력으로 컬럼 목록, 코멘트가 추가된 select 쿼리를 출력하는 스크립트

인스턴스 차트를 만들때 유용하게 사용할 수 있는 스크립트입니다.
아래 코드는 코드 컬럼에 대해서 코드명을 찾는 스칼라 서브쿼리와 컬럼 코멘트가 추가된 select sql을 만들어 주는 코드입니다. 각자 환경에 맞게 조금만 수정하시면 유용하게 사용하실 수 있을 겁니다. 단 코드명을 찾는 스칼라 서브쿼리를 사용하기 위해서는 컬럼에 대한  표준화 작업이 되어있는 상태여야 합니다.
SET SERVEROUTPUT ON
spool select.sql
declare
   l_sql        varchar2 (30000);
   l_column     varchar2 (30000);
   l_where      varchar2 (1000);
   l_idx        number := 0;
   l_scheme     varchar2 (100) := 'SCOTT';
   l_tab_name   varchar2 (100) := '&tname';
   l_comma      varchar2(100);
  l_cd_grp_id  varchar2(10);
begin
  dbms_output.put_line ('--------');
   dbms_output.put ('select ');
   
   l_sql := 'select ';
   for l_tab in (select a.table_name, a.column_name, b.comments
                   from all_tab_cols a, all_col_comments b
                  where 1 = 1
                    and a.table_name = b.table_name
                    and a.column_name = b.column_name
                    and a.owner = upper(l_scheme)
                    and b.owner = upper(l_scheme)
                    and a.table_name = upper(l_tab_name)
                  order by a.column_id)
   loop
      if l_idx = 0
      then
        l_comma := '';         
      else         
         l_comma := '      ,';
      end if;
   if l_tab.column_name like '%CD' 
   then
     begin
     select cd_grp_id into l_cd_grp_id 
        from cd_grp_info 
      where cd_grp_nm = l_tab.comments 
        and efct_end_dt = TO_DATE ('99991231', 'YYYYMMDD');
    exception
     when others then 
      l_cd_grp_id := '0000';
    end;
  
       dbms_output.put_line (l_comma||'a.' || lower(l_tab.column_name)||'|| ');
    dbms_output.put_line ('''(''||'||'(select cd_nm from cd_info ');
    dbms_output.put_line ('where cd_id = a.'||lower(l_tab.column_name));
    dbms_output.put_line ('  and efct_end_dt = TO_DATE(''99991231'', ''YYYYMMDD'') ');
    dbms_output.put_line ('  and cd_grp_id = '''||nvl(l_cd_grp_id,'0000')||'''');
    dbms_output.put_line (')||'')'' '||lower(l_tab.column_name)||' -- '|| l_tab.comments);
   else 
       dbms_output.put_line (l_comma||rpad('a.' || lower(l_tab.column_name), 40, ' ') ||' -- ' || l_tab.comments);
   end if;
      
      l_idx := l_idx + 1;
   end loop;
   dbms_output.put_line ('  from ' || l_tab_name || ' a ');
   l_idx := 0;
   for l_cons in (  select b.table_name
                          ,b.constraint_name
                          ,a.column_name
                          ,a.position
                      from all_cons_columns a, all_constraints b
                     where 1 = 1
                       and a.constraint_name = b.constraint_name
            and a.owner = upper(l_scheme)
            and b.owner = upper(l_scheme)
                       and a.table_name = upper(l_tab_name)
                       and b.table_name = upper(l_tab_name)
                       and b.constraint_type = 'P'
                  order by 1, 2, 4)
   loop
      if l_idx = 0
      then
         dbms_output.put_line (' where 1 = 1');
      end if;
      dbms_output.put ('   and ');
      dbms_output.put_line (lower(l_cons.column_name) || ' = :' || lower(l_cons.column_name));
      l_idx := l_idx + 1;
   end loop;
   dbms_output.put_line (';');
end;
/
spool off
SET SERVEROUT OFF 

Leave Comments

댓글 쓰기 권한이 없습니다. 회원 가입후에 사용 가능합니다