[Oracle] 알아두면 좋은 쿼리

최근 수정 2020-02-27

 

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

* 오라클 SQL 날짜형변환

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

SELECT REGEXP_REPLACE('20180823', '(.{4})(.{2})(.{2})', '\1-\2-\3') FROM DUAL;

SELECT TO_CHAR('20180823','YYYY/MM/DD') FROM DUAL;

SELECT TO_CHAR(TO_DATE('20191127', 'YYYYMMDD'), 'YYYY/MM/DD') FROM DUAL;

 

#1-1

/*SP, 함수 등등에서 NLS_DATE_LANGUAGE 단어찾기*/

SELECT *

FROM USER_SOURCE

WHERE UPPER(TEXT) LIKE '%'||UPPER('NLS_DATE_LANGUAGE')||'%'

;

 

#1-2

/*DB 간 테이블 복사(WHERE 1=2 구조만)*/

CREATE TABLE [생성할 테이블명] AS SELECT * FROM [복사할 테이블명] WHERE 1=2

 

#1-3

/*테이블별 ROW COUNT 조회*/

SELECT TABLE_NAME, NUM_ROWS

 FROM USER_TABLES

 WHERE TABLE_NAME like 'DL%'

 ;

#1-4

/**/

TO_CHAR(SYSDATE, 'YYYYMMDD')

 

#1-5

/*테이블 코멘트 옮기기*/

SELECT

    'COMMENT ON COLUMN ' || B.TABLE_NAME || '.' || B.COLUMN_NAME || ' IS ''' || B.COMMENTS || ''';'

FROM   ALL_TABLES A

,user_col_comments B

WHERE  A.TABLE_NAME = B.TABLE_NAME

AND A.TABLE_NAME LIKE 'DL%'

AND B.COMMENTS IS NOT NULL

ORDER BY A.TABLE_NAME;

 

#1-6

/*테이블 PK 스크립트 만들기*/

WITH  A  AS (

SELECT A.TABLE_NAME AS TABLE_NAME,

       B.CONSTRAINT_NAME AS CONSTRAINT_NAME,

       LISTAGG(A.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY A.TABLE_NAME, B.CONSTRAINT_NAME)  AS COLUMN_NAME

FROM USER_IND_COLUMNS A, USER_CONSTRAINTS B

WHERE 1=1

  --AND A.TABLE_NAME = 'DLAT_CONTRACTOR'

  AND (A.TABLE_NAME LIKE 'DL%' OR A.TABLE_NAME LIKE 'TOJI%')

  AND A.INDEX_NAME = B.INDEX_NAME

  AND A.TABLE_NAME = B.TABLE_NAME

  AND CONSTRAINT_TYPE = 'P'

GROUP BY A.TABLE_NAME, B.CONSTRAINT_NAME

)

SELECT 'ALTER TABLE'||' '||TABLE_NAME||' '||'ADD CONSTRAINT'||' '||CONSTRAINT_NAME||' '||'PRIMARY KEY'||'('||COLUMN_NAME||') ;'

FROM A

ORDER BY 1

;