subinto의 개발노트

--전체 계정 전체 테이블 조회

SELECT * FROM ALL_TABLES;

--사용자계정 전체 테이블 조회

SELECT * FROM ALL_TABLES WHERE OWNER = 'SUBINTO';

 --사용자계정 전체 테이블 커멘트 조회

 SELECT * FROM ALL_TAB_COMMENTS WHERE OWNER = 'SUBINTO';

--사용자계정 전체 컬럼 조회

 SELECT * FROM ALL_TAB_COLUMNS WHERE OWNER = 'SUBINTO';

 --사용자계정 전체 컬럼 커멘트 조회

 SELECT * FROM ALL_COL_COMMENTS WHERE OWNER = 'SUBINTO';

--쿼리 생성 쿼리(SELECT)

SELECT DECODE(ROWNUM, 1, 'SELECT ' ||  CHR(13), ', ')

    || UPPER(COLUMN_NAME) || ' AS ' ||LOWER(SUBSTR(COLUMN_NAME, 1,1))||SUBSTR(REPLACE(INITCAP(LOWER(COLUMN_NAME)), '_',''), 2)    

    || DECODE(ROWNUM, (COUNT(1) OVER()), CHR(13) || '  FROM '|| TABLE_NAME ||';', '') AS "SELECT Query"

 FROM ALL_TAB_COLUMNS

WHERE OWNER = 'SUBINTO'

  AND TABLE_NAME = UPPER('TB_MENU');

UPDATE USER_INFO A
    SET (USER_TYPE, USER_NM) = (SELECT LOGIN_TYPE, LOGIN_NM

FROM LOGIN_INFO

    WHERE USER_ID = A.USER_ID)

WHERE (ETC_TYPE, ETC_DIV) IN (SELECT ETC_TYPE, ETC_DIV

    FROM LOGIN_INFO

  WHERE USE_YN = 'Y')

SELECT 
            '메뉴ID' AS MENU_ID
          , 'NO' AS NO
          , '1 Depth' AS DEPTH_1
          , '2 Depth' AS DEPTH_2
          , '3 Depth' AS DEPTH_3
          , '4 Depth' AS DEPTH_4
          , '5 Depth' AS DEPTH_5
          , 'Url' AS MENU_URL
          , (SELECT USER_GROUP_NM FROM TB_USER_GROUP WHERE USER_GROUP_ID = '001') AS GROUP_001
          , (SELECT USER_GROUP_NM FROM TB_USER_GROUP WHERE USER_GROUP_ID = '002') AS GROUP_002
          , (SELECT USER_GROUP_NM FROM TB_USER_GROUP WHERE USER_GROUP_ID = '003') AS GROUP_003
          , (SELECT USER_GROUP_NM FROM TB_USER_GROUP WHERE USER_GROUP_ID = '004') AS GROUP_004
FROM DUAL
UNION ALL
SELECT
            A.MENU_ID
          , ROWNUM||'' AS NO
          , CASE WHEN A.MENU_LEVEL = '1' THEN A.MENU_NAME ELSE NULL END AS DEPTH_1
          , CASE WHEN A.MENU_LEVEL = '2' THEN A.MENU_NAME ELSE NULL END AS DEPTH_2
          , CASE WHEN A.MENU_LEVEL = '3' THEN A.MENU_NAME ELSE NULL END AS DEPTH_3
          , CASE WHEN A.MENU_LEVEL = '4' THEN A.MENU_NAME ELSE NULL END AS DEPTH_4
          , CASE WHEN A.MENU_LEVEL = '5' THEN A.MENU_NAME ELSE NULL END AS DEPTH_5
          , A.MENU_URL
          , GROUP_001
          , GROUP_002
          , GROUP_003
          , GROUP_004
FROM TB_MENU_INFO A
        , (
            WITH TUG AS (
                SELECT USER_GROUP_ID
                  FROM TB_USR_GRP
                WHERE USE_AT = 'Y'
            )
            SELECT
                        MENU_ID
                      , MAX(CASE WHEN GROUP_001 = USER_GROUP_ID THEN 'O' ELSE NULL END)  AS GROUP_001
                      , MAX(CASE WHEN GROUP_002 = USER_GROUP_ID THEN 'O' ELSE NULL END)  AS GROUP_002
                      , MAX(CASE WHEN GROUP_003 = USER_GROUP_ID THEN 'O' ELSE NULL END)  AS GROUP_003
                      , MAX(CASE WHEN GROUP_004 = USER_GROUP_ID THEN 'O' ELSE NULL END)  AS GROUP_004
              FROM TUG
              PIVOT(MAX(USER_GROUP_ID) FOR USER_GROUP_ID IN ('001' AS GROUP_001, '002' AS GROUP_002, '003' AS GROUP_003, '004' AS GROUP_004) )
                    , TB_GRP_MENU TGM
            WHERE TGM.USE_AT = 'Y'
            GROUP BY MENU_ID
          ) B
WHERE A.USE_YN = 'Y'
AND A.DEL_YN = 'N'
AND A.MENU_ID = B.MENU_ID
START WITH A.MENU_UPPER_ID IS NULL
CONNECT BY PRIOR A.MENU_ID = A.MENU_UPPER_ID

SELECT
            LAG(A.MENU_ID) OVER(ORDER BY A.MENU_ID ASC) AS PRE_MENU_ID --이전 메뉴ID
          , A.MENU_ID --현재 메뉴ID
          , LEAD(A.MENU_ID) OVER(ORDER BY A.MENU_ID ASC) AS NEXT_MENU_ID --다음 메뉴ID
FROM TB_SF_MENU_INFO A
ORDER BY MENU_ID ASC

--다중 LIKE 검색(오라클 10g부터 가능)

SELECT  * 
  FROM TB_MENU_INFO 
 WHERE  REGEXP_LIKE(MENU_NM, '참여업체|수집안내|메뉴정보')
 
--세번째 변수에 'i'를 추가하면 대소문자 구분하지 않고 검색
SELECT  * 
  FROM TB_MENU_INFO 
 WHERE  REGEXP_LIKE(MENU_URL, 'MYPAGE|user', 'i')

--시작하는 문자가 "전 or 지 or 스 or 마" 로 시작하는 데이터 검색
SELECT  * 
  FROM TB_MENU_INFO 
 WHERE  REGEXP_LIKE(MENU_NM, '^[전지스마]')
 
--"분석"으로 끝나는 데이터 검색
SELECT  * 
  FROM TB_MENU_INFO 
 WHERE  REGEXP_LIKE(MENU_NM, '분석$')

SELECT
ORA_ROWSCN AS SCN
, TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DD HH24:MI:SS') AS TM
, MENU_ID
FROM TB_MENU_INFO;

SELECT  * 
  FROM TB_MENU_INFO  AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '10' MINUTE) 
 WHERE  MENU_ID LIKE 'SC_%'

--특정 날짜 시간 데이터 조회
SELECT  * 
  FROM TB_MENU_INFO  AS OF TIMESTAMP(TO_TIMESTAMP('2018-11-12 08:30:10', 'YYYY-MM-DD HH24:MI:SS')) 
 WHERE  MENU_ID LIKE 'SC_%'

/*
 * MENU_ID가 PK임
 * MENU_CATE데이터가 'B', 'I', 'P' 인것의 MENU_TYPE별 개수 구하기
 */
WITH PV AS (
    SELECT MENU_ID
              , MENU_CATE
              , MENU_TYPE
      FROM TB_SF_MENU_INFO
)
SELECT *
  FROM PV
  PIVOT(COUNT(MENU_ID) FOR MENU_TYPE IN ('B' AS TYPE_B, 'I' AS TYPE_I, 'P' AS TYPE_P));

--Oracle version : 11g
SELECT LISTAGG(CODE, ',') WITHIN GROUP(ORDER BY CODE DESC) AS PLUS_COLUMN 
  FROM COM_CODE
WHERE ROWNUM < 5;

--Oracle version : 9i
SELECT SUBSTR(XMLAGG(XMLELEMENT(X, ',', CODE) ORDER BY CODE).EXTRACT('//text()'), 2) AS PLUS_COLUMN
  FROM COM_CODE
WHERE ROWNUM < 5;

--캐릭터셋 조회
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

--CONVERT( '대상 문자열', '타겟 인코딩', '소스 인코딩')
SELECT COMMENTS
          , CONVERT(COMMENTS, 'AL32UTF8', 'KO16KSC5601') AS COMMENTS_CONVERT
    FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'COM_CODE';

--1시간전
SELECT * FROM TABLE_NAME AS OF TIMESTAMP((SYSDATE-1/24));

--특정시간
SELECT * FROM TABLE_NAME AS OF TIMESTAMP(TO_DATE('20181101140000', 'YYYYMMDDHH24MISS'));

SELECT * FROM v$version WHERE banner LIKE 'Oracle%';