자동 Insert/Update/Delete/Select PLSQL 만들어주는 Query

공통..PL-SQL 작성하는 거.....귀챦아서...그냥 만들어 봤음.

테이블한개에 대해서 Insert/Update/Delete PLSQL 만들어 주는 것임.

Select는 한개의 테이블만 하기 때문에 Join이 필요한 것은 아래 쿼리 실행후 만들어진 PL-SQL가지고 수정하면 됨

/*********************************************************

** AUTHOR           : 최두일

**

** PURPOSE          : 기본 IUD Procedure 기본 생성 SP

** How Using        : 아래 쿼리를 실행시키면 변수에 Table을 입력하면 자동으로 I/U/D SP가 생성됨 

**                  : 쿼리실행->POPUP창이 뜸->tname에 테이블명 입력->실행->Recored가 조회됨->복사해서 PLSQL창에서 컴파일

                    : 주의사항: Create or Replace 이므로 기존거 무조건 변경 하므로 주의요 

*********************************************************/

-------------------------------------

-- 1. 입력 Procedure Head 정보 생성

-------------------------------------

select ('CREATE OR REPLACE PROCEDURE UP_'||replace(:tname, 'ZHRA_','')||'_U1 (') as a1 from dual

 union all

select (lpad(' ',3)||'P_'||rpad(column_name, 32)||' IN '||decode(substr(data_type,1,9), 'TIMESTAMP','TIMESTAMP',data_type)||decode(rownum,irownum,'',',')) as a1

  from (select a.*, (select max(rownum) from user_tab_columns a where table_name = :tname)as irownum from user_tab_columns a where table_name = :tname order by column_id) a 

 union all

select (')'||' IS') as a1 from dual

 union all

select '' as a1 from dual

 union all

select '

/****************************************************************** 

** AUTHOR           : SYSTEM AUTO 

** CREATE DATE      : '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')||' 

** 

** SYSTEM           : 프로젝트 시스템명기록 

** SUBSYSTEM        :  

** PAGE             : 

** DESCRIPTION      : '|| (select table_name||' ('||comments||') 수정' from user_tab_comments a where a.table_name = :tname) ||'

******************************************************************* 

** CHANGE HISTORY  

** 최초작성         : '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')||' 

******************************************************************/ '

 from dual

 union all

select '' as a1 from dual

-------------------------------------

-- 2. 입력 Procedure Field Select 정보 생성

-------------------------------------

union all

select ('BEGIN ') as a1 from dual

union all

select ('') as a1 from dual

union all

select ('IF '||(select rpad('P_'||column_name,35)||' IS NULL OR' from user_tab_columns where table_name = :tname and nullable = 'N' and column_id = 1 ) ) as a1 from dual

union all

select (lpad(' ',3)||rpad('P_'||column_name,35)||' IS NULL'||decode(rownum,irownum,'',' OR')) as a1

  from (select a.*, (select max(rownum) from user_tab_columns a where table_name = :tname and nullable = 'N' and column_id > 1)as irownum  from user_tab_columns a where table_name = :tname and nullable = 'N' and column_id > 1 order by column_id) a

union all

select ('THEN ') as a1 from dual

union all

select ('   RAISE_APPLICATION_ERROR (-20001,'||'''필수값이 입력되지 않았습니다.'''||');') as a1 from dual

union all

select ('   RETURN; ') as a1 from dual

union all

select ('END IF;') as a1 from dual

union all

select ('') as a1 from dual

union all

select ('') as a1 from dual

union all

select (lpad('UPDATE ',15)||(select :tname from dual)) as a1 from dual

union all

select (lpad('SET ',15)) as a1 from dual

 union all

select (lpad(' ',15)||rpad(column_name,24) || '= '||'P_'||column_name||decode(rownum,irownum,'',',')) as a1

  from (select a.*, (select max(rownum) from user_tab_columns a where table_name = :tname and nullable = 'Y')as irownum from user_tab_columns a where table_name = :tname and nullable = 'Y' order by column_id) a

 union all

select (lpad('WHERE ',15)||(select rpad(column_name,24)||'= P_'||column_name from user_tab_columns where table_name = :tname and nullable = 'N' and column_id = 1 )) as a1 from dual

 union all

select (lpad('AND',14)||' '||rpad(column_name,24) || '= '||'P_'||column_name) as a1

  from (select * from user_tab_columns where table_name = :tname and nullable = 'N' and column_id > 1 order by column_id) a

 union all

select (lpad(';',10)) as a1 from dual

 union all

select ('') as a1 from dual

 union all

select ('EXCEPTION') as a1 from dual

 union all

select ('     WHEN OTHERS THEN') as a1 from dual

 union all

select ('          RAISE_APPLICATION_ERROR (-20001, ''UP_'||:tname|| '=''||SQLERRM);') as a1 from dual

 union all

select ('') as a1 from dual

 union all

select ('END UP_'||replace(:tname, 'ZHRA_','') ||'_U1;') as a1 from dual

 union all

select :aa from dual ;

 

------------------------insert

/*********************************************************

** AUTHOR           : 최두일

**

** PURPOSE          : 기본 IUD Procedure 기본 생성 SP

** How Using        : 아래 쿼리를 실행시키면 변수에 Table을 입력하면 자동으로 I/U/D SP가 생성됨 

**                  : 쿼리실행->POPUP창이 뜸->tname에 테이블명 입력->실행->Recored가 조회됨->복사해서 PLSQL창에서 컴파일

                    : 주의사항: Create or Replace 이므로 기존거 무조건 변경 하므로 주의요 

*********************************************************/

-------------------------------------

-- 1. 입력 Procedure Head 정보 생성

-------------------------------------

select ('CREATE OR REPLACE PROCEDURE UP_'||replace(:tname, 'ZHRA_','')||'_I1 (') as a1 from dual

 union all

select (lpad(' ',3)||'P_'||rpad(column_name, 32)||' IN '||decode(substr(data_type,1,9), 'TIMESTAMP','TIMESTAMP',data_type)||decode(rownum,irownum,'',',')) as a1

  from (select a.*, (select max(rownum) from user_tab_columns a where table_name = :tname)as irownum from user_tab_columns a where table_name = :tname order by column_id) a 

 union all

select (')'||' IS') as a1 from dual

 union all

select '' as a1 from dual

 union all

select '

/****************************************************************** 

** AUTHOR           : SYSTEM AUTO 

** CREATE DATE      : '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')||' 

** 

** SYSTEM           :  

** SUBSYSTEM        :  

** PAGE             : =>개발자 작성요망

** DESCRIPTION      : '|| (select table_name||' ('||comments||') 입력' from user_tab_comments a where a.table_name = :tname) ||'

******************************************************************* 

** CHANGE HISTORY  

** 최초작성         : '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')||' 

******************************************************************/ '

 from dual

 union all

select '' as a1 from dual

-------------------------------------

-- 2. 입력 Procedure Field Select 정보 생성

-------------------------------------

union all

select ('BEGIN ') as a1 from dual

union all

select ('') as a1 from dual

union all

select ('IF '||(select rpad('P_'||column_name,35)||' IS NULL OR' from user_tab_columns where table_name = :tname and nullable = 'N' and column_id = 1 ) ) as a1 from dual

union all

select (lpad(' ',3)||rpad('P_'||column_name,35)||' IS NULL'||decode(rownum,irownum,'',' OR')) as a1

  from (select a.*, (select max(rownum) from user_tab_columns a where table_name = :tname and nullable = 'N' and column_id > 1)as irownum  from user_tab_columns a where table_name = :tname and nullable = 'N' and column_id > 1 order by column_id) a

union all

select ('THEN ') as a1 from dual

union all

select ('   RAISE_APPLICATION_ERROR (-20001,'||'''필수값이 입력되지 않았습니다.'''||');') as a1 from dual

union all

select ('   RETURN; ') as a1 from dual

union all

select ('END IF;') as a1 from dual

union all

select ('') as a1 from dual

union all

select ('') as a1 from dual

union all

select (lpad('INSERT INTO',15)||' '||:tname) as a1 from dual

union all

select ('    (') as a1 from dual

 union all

select (lpad(' ',11)||rpad(a.column_name,24)||decode(a.column_id ,(select max(a.column_id) from user_tab_columns a where table_name = :tname) , ''||'  --  '||comments, ','||'   --  '||comments)) as a1

  from (select a.* , c.comments

          from user_tab_columns a, user_col_comments c

         where a.table_name = :tname

           and a.table_name = c.table_name

           and a.column_name = c.column_name

         order by a.column_id

       ) a

union all

select ('    ) VALUES') as a1 from dual

 union all

select ('    (') as a1 from dual

 union all

select (lpad(' P_',13)||rpad(a.column_name,24)||decode(a.column_id ,(select max(a.column_id) from user_tab_columns a where table_name = :tname) , ''||'  --  '||comments, ','||'   --  '||comments)) as a1

  from (select a.* , c.comments

          from user_tab_columns a, user_col_comments c

         where a.table_name = :tname

           and a.table_name = c.table_name

           and a.column_name = c.column_name

         order by a.column_id

       ) a

 union all

select ('    );') as a1 from dual

 union all

select ('') as a1 from dual

 union all

select ('EXCEPTION') as a1 from dual

 union all

select ('     WHEN OTHERS THEN') as a1 from dual

 union all

select ('          RAISE_APPLICATION_ERROR (-20001, ''UP_'||:tname|| '=''||SQLERRM);') as a1 from dual

 union all

select ('') as a1 from dual

 union all

select ('END UP_'||replace(:tname, 'ZHRA_','') ||'_I1;') as a1 from dual

 union all

select :aa from dual ;

 

------------------delete

/*********************************************************

** AUTHOR           : 최두일

**

** PURPOSE          : 기본 IUD Procedure 기본 생성 SP

** How Using        : 아래 쿼리를 실행시키면 변수에 Table을 입력하면 자동으로 I/U/D SP가 생성됨 

**                  : 쿼리실행->POPUP창이 뜸->tname에 테이블명 입력->실행->Recored가 조회됨->복사해서 PLSQL창에서 컴파일

                    : 주의사항: Create or Replace 이므로 기존거 무조건 변경 하므로 주의요 

*********************************************************/

-------------------------------------

-- 1. 삭제 Procedure Head 정보 생성

-------------------------------------

select ('CREATE OR REPLACE PROCEDURE UP_'||replace(:tname, 'ZHRA_','')||'_D1 (') as a1 from dual

 union all

select (lpad(' ',3)||'P_'||rpad(column_name, 32)||' IN '||decode(substr(data_type,1,9), 'TIMESTAMP','TIMESTAMP',data_type)||decode(rownum,irownum,'',',')) as a1

  from (select a.*, (select max(rownum) from user_tab_columns a where table_name = :tname And nullable = 'N' )as irownum from user_tab_columns a where table_name = :tname And nullable = 'N' order by column_id) a 

 union all

select (')'||' IS') as a1 from dual

 union all

select '' as a1 from dual

 union all

select '

/****************************************************************** 

** AUTHOR           : SYSTEM AUTO 

** CREATE DATE      : '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')||' 

** 

** SYSTEM           : 인사급여 

** SUBSYSTEM        : 출장관리 

** PAGE             : =>개발자 작성요망

** DESCRIPTION      : '|| (select table_name||' ('||comments||') 삭제' from user_tab_comments a where a.table_name = :tname) ||'

******************************************************************* 

** CHANGE HISTORY  

** 최초작성         : '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')||' 

******************************************************************/ '

 from dual

 union all

select '' as a1 from dual

-------------------------------------

-- 2. 삭제 Procedure Field Select 정보 생성

-------------------------------------

union all

select ('BEGIN ') as a1 from dual

union all

select ('') as a1 from dual

union all

select ('IF '||(select rpad('P_'||column_name,35)||' IS NULL OR' from user_tab_columns where table_name = :tname and nullable = 'N' and column_id = 1 ) ) as a1 from dual

union all

select (lpad(' ',3)||rpad('P_'||column_name,35)||' IS NULL'||decode(rownum,irownum,'',' OR')) as a1

  from (select a.*, (select max(rownum) from user_tab_columns a where table_name = :tname and nullable = 'N' and column_id > 1)as irownum  from user_tab_columns a where table_name = :tname and nullable = 'N' and column_id > 1 order by column_id) a

union all

select ('THEN ') as a1 from dual

union all

select ('   RAISE_APPLICATION_ERROR (-20001,'||'''필수값이 입력되지 않았습니다.'''||');') as a1 from dual

union all

select ('   RETURN; ') as a1 from dual

union all

select ('END IF;') as a1 from dual

union all

select ('') as a1 from dual

union all

select ('') as a1 from dual

union all

select (lpad('DELETE FROM ',15)||(select :tname from dual)) as a1 from dual

 union all

select (lpad('WHERE ',15)||(select rpad(column_name,24)||'= P_'||column_name from user_tab_columns where table_name = :tname and nullable = 'N' and column_id = 1 )) as a1 from dual

 union all

select (lpad('AND',14)||' '||rpad(column_name,24) || '= '||'P_'||column_name) as a1

  from (select * from user_tab_columns where table_name = :tname and nullable = 'N' and column_id > 1 order by column_id) a

 union all

select (lpad(';',10)) as a1 from dual

 union all

select ('') as a1 from dual

 union all

select ('EXCEPTION') as a1 from dual

 union all

select ('     WHEN OTHERS THEN') as a1 from dual

 union all

select ('          RAISE_APPLICATION_ERROR (-20001, ''UP_'||:tname|| '=''||SQLERRM);') as a1 from dual

 union all

select ('') as a1 from dual

 union all

select ('END UP_'||replace(:tname, 'ZHRA_','') ||'_D1;') as a1 from dual 

union all

select :aa from dual ;

------------select

/*********************************************************

** AUTHOR           : 최두일

**

** PURPOSE          : 기본 IUD Procedure 기본 생성 SP

** How Using        : 아래 쿼리를 실행시키면 변수에 Table을 입력하면 자동으로 I/U/D SP가 생성됨 

**                  : 쿼리실행->POPUP창이 뜸->tname에 테이블명 입력->실행->Recored가 조회됨->복사해서 PLSQL창에서 컴파일

                    : 주의사항: Create or Replace 이므로 기존거 무조건 변경 하므로 주의요 

*********************************************************/

-------------------------------------

-- 1. 조회 Procedure Head 정보 생성

-------------------------------------

select ('CREATE OR REPLACE PROCEDURE UP_'||replace(:tname, 'ZHRA_','')||'_S1 (') as a1 from dual

 union all

select (lpad(' ',3)||'P_'||rpad(column_name, 32)||' IN '||decode(substr(data_type,1,9), 'TIMESTAMP','TIMESTAMP',data_type)||',') as a1

  from (select * from user_tab_columns where table_name = :tname  and nullable = 'N' order by column_id) a

 union all

select '   RC1                               OUT SYS_REFCURSOR ' from dual

 union all

select (' )'||' IS') as a1 from dual

 union all

select '' as a1 from dual

 union all

select '

/****************************************************************** 

** AUTHOR           : SYSTEM AUTO 

** CREATE DATE      : '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')||' 

** 

** SYSTEM           : 

** SUBSYSTEM        : 

** PAGE             : =>개발자 작성요망

** DESCRIPTION      : '|| (select table_name||' ('||comments||') 조회' from user_tab_comments a where a.table_name = :tname) ||'

******************************************************************* 

** CHANGE HISTORY  

** 최초작성         : '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')||' 

******************************************************************/ '

 from dual

 union all

select '' as a1 from dual

-------------------------------------

-- 2. 조회 Procedure Field Select 정보 생성

-------------------------------------

union all

select ('BEGIN ') as a1 from dual

union all

select ('') as a1 from dual

union all

select ('IF '||(select rpad('P_'||column_name,35)||' IS NULL OR' from user_tab_columns where table_name = :tname and nullable = 'N' and column_id = 1 ) ) as a1 from dual

union all

select (lpad(' ',3)||rpad('P_'||column_name,35)||' IS NULL'||decode(rownum,irownum,'',' OR')) as a1

  from (select a.*, (select max(rownum) from user_tab_columns a where table_name = :tname and nullable = 'N' and column_id > 1)as irownum  from user_tab_columns a where table_name = :tname and nullable = 'N' and column_id > 1 order by column_id) a

union all

select ('THEN ') as a1 from dual

union all

select ('   RAISE_APPLICATION_ERROR (-20001,'||'''필수값이 입력되지 않았습니다.'''||');') as a1 from dual

union all

select ('   RETURN; ') as a1 from dual

union all

select ('END IF;') as a1 from dual

union all

select ('') as a1 from dual

union all

select ('OPEN RC1 FOR') as a1 from dual

union all

select ('') as a1 from dual

union all

select (lpad('SELECT ',15)) as a1 from dual

 union all

select (lpad(' ',15)||rpad(a.column_name,24)||decode(a.column_id ,(select max(a.column_id) from user_tab_columns a where table_name = :tname) , ''||'  --  '||comments, ','||'   --  '||comments)) as a1

  from (select a.* , c.comments

          from user_tab_columns a, user_col_comments c

         where a.table_name = :tname

           and a.table_name = c.table_name

           and a.column_name = c.column_name

         order by a.column_id

       ) a

 union all

select (lpad('FROM',14)||' '||:tname ) as a1 from dual

 union all

select (lpad('WHERE ',15)||(select rpad(column_name,24)||'= P_'||column_name from user_tab_columns where table_name = :tname and nullable = 'N' and column_id = 1 )) as a1 from dual

 union all

select (lpad('AND',14)||' '||rpad(column_name,24) || '= '||'P_'||column_name) as a1

  from (select * from user_tab_columns where table_name = :tname and nullable = 'N' and column_id > 1 order by column_id) a

 union all

select (lpad(';',10)) as a1 from dual

 union all

select ('') as a1 from dual

 union all

select ('EXCEPTION') as a1 from dual

 union all

select ('     WHEN OTHERS THEN') as a1 from dual

 union all

select ('          RAISE_APPLICATION_ERROR (-20001, ''UP_'||:tname|| '=''||SQLERRM);') as a1 from dual

 union all

select ('') as a1 from dual

 union all

select ('END UP_'||replace(:tname, 'ZHRA_','') ||'_S1;') as a1 from dual

 union all

select :aa from dual ;