[ORACLE] 쿼리 출력하기 (INSERT, SELECT, UPDATE, DELETE)

테이블 CRUD 입력 쿼리 조회 구문입니다.

상황에 맞추어 수정하여 사용하면 됩니다.

 

[INSERT]

   SELECT 'INSERT INTO' || TABLE_NAME || '( /*' || COMMENTS || ' */'
     FROM ALL_TAB_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL 
   SELECT CASE WHEN ROWNUM = 1 THEN '' ELSE ', ' END || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL
   SELECT ') VALUES ('
     FROM DUAL
UNION ALL
   SELECT CASE WHEN ROWNUM = 1 THEN '' ELSE ', ' END || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL
   SELECT ')'
     FROM DUAL;

 

[SELECT]

   SELECT CASE WHEN ROWNUM = 1 THEN 'SELECT ' ELSE ', ' END || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL
   SELECT 'FROM ' || TABLE_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_TAB_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME;

 

[UPDATE]

   SELECT 'UPDATE ' || TABLE_NAME || ' /* ' || COMMENTS || ' */' 
     FROM ALL_TAB_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME 
UNION ALL     
   SELECT CASE WHEN ROWNUM = 1 THEN 'SET ' ELSE ', ' END || COLUMN_NAME || ' = ' || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL
   SELECT CASE WHEN ROWNUM = 1 THEN 'WHERE ' ELSE 'AND ' END || COLUMN_NAME || ' = ' || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME;

 

[DELETE]

   SELECT 'DELETE FROM ' || TABLE_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_TAB_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME;
UNION ALL
   SELECT CASE WHEN ROWNUM = 1 THEN 'WHERE ' ELSE 'AND ' || COLUMN_NAME || ' = ' || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME;