본문 바로가기

Oracle

[Oracle] Window함수, 누적합계 SELECT SEQ , VAL , SUM(VAL) OVER(ORDER BY SEQ ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ACCU1 , SUM(VAL) OVER(ORDER BY SEQ ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ACCU2 , SUM(VAL) OVER(ORDER BY SEQ ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ACCU3 , SUM(VAL) OVER(ORDER BY SEQ ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ACCU4 FROM ( SELECT.. 더보기
[Oracle] ORDER BY에서 NULL 위치 [Oracle] ORDER BY에서 NULL 위치 ORDER BY [COL] [DESC] NULLS FIRST => NULL 값을 먼저 나오도록 함. ORDER BY [COL] [DESC] NULLS LAST => NULL 값을 뒤에 나오도록 함. 더보기
[Oracle] SPLIT -- ORACLE SPLIT QUERY -- 01. 문자 SELECT CODE_ARR FROM ( SELECT SUBSTRB( :AS_LIST , INSTRB(','||:AS_LIST, ',', 1, ROWNUM) , INSTRB(:AS_LIST||',', ',', LEVEL) - INSTRB(','||:AS_LIST, ',', 1, ROWNUM) ) AS CODE_ARR FROM DUAL WHERE 1=1 AND REPLACE(:AS_LIST,',','') IS NOT NULL AND INSTRB(:AS_LIST||',', ',', LEVEL) = LEVEL CONNECT BY LEVEL 더보기
Quiz04. [Oracle] 출력값은??? Quiz 04. 출력값은??? 1. 조건 DECLARE V_NAME VARCHAR2(100) := 'NONE'; BEGIN WITH TBL AS ( SELECT '홍길동' AS NM FROM DUAL UNION ALL SELECT '신사임당' AS NM FROM DUAL ) SELECT NM INTO V_NAME FROM TBL; DBMS_OUTPUT.PUT_LINE( '이름 : ' || V_NAME ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( '이름 : ' || V_NAME ); END; 2. 정답은??? 1) NONE 2) 홍길동 3) 신사임당 대박.... [출처] 구루비 http://www.gurubee.net/article/60882 더보기
Quiz03. [Oracle] 제외 기간 구하기 Quiz 03. 제외 기간 구하기 1. 조건WITH TBL AS ( SELECT 1 AS SEQ, TO_DATE('20140201') AS FROM_DT, TO_DATE('20140430') AS TO_DT FROM DUAL UNION ALL SELECT 2 AS SEQ, TO_DATE('20140601') AS FROM_DT, TO_DATE('20140630') AS TO_DT FROM DUAL UNION ALL SELECT 3 AS SEQ, TO_DATE('20140701') AS FROM_DT, TO_DATE('20140831') AS TO_DT FROM DUAL UNION ALL SELECT 4 AS SEQ, TO_DATE('20140901') AS FROM_DT, TO_DATE('20141130.. 더보기

티스토리툴바