개발자인생

sqlplus ID/PASSWORD@IP주소:포트번호/SID

sqlplus ID/PASSWORD@IP주소:포트번호/SID << EOF

update .....  ;

delete ......   ;

insert ....    ;

commit;

exit

EOF

> SELECT  TO_CHAR(LAST_DAY(SYSDATE),'YYYYMMDD') MONTH_LAST_DAY FROM DUAL

MONTH_LAST_DAY

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

20140930

> SELECT  TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1),'YYYYMMDD') MONTH_FIRST_DAY

FROM    DUAL

MONTH_FIRST_DAY

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

20140901

* 이 방법이 더 단순하네요..

select  trunc(sysdate,'MM') from dual;

WITH T AS (

    SELECT  '2014125'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014455'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014532'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014234'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014765'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014321'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014012'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014056'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014089'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014022'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014044'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014055'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014011'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014442'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014232'  EMP_NO FROM DUAL

)

SELECT  EMP_NO

        , NTILE(3) OVER ( ORDER BY EMP_NO) GRP3

        , NTILE(4) OVER ( ORDER BY EMP_NO) GRP4

        , NTILE(5) OVER ( ORDER BY EMP_NO) GRP5

        , NTILE(6) OVER ( ORDER BY EMP_NO) GRP6

FROM    T

2014011 1 1 1 1

2014012 1 1 1 1

2014022 1 1 1 1

2014044 1 1 2 2

2014055 1 2 2 2

2014056 2 2 2 2

2014089 2 2 3 3

2014125 2 2 3 3

2014232 2 3 3 3

2014234 2 3 4 4

2014321 3 3 4 4

2014442 3 3 4 5

2014455 3 4 5 5

2014532 3 4 5 6

2014765 3 4 5 6

SELECT    COL1, COL2

FROM    TAB01

ORDER BY  COL1

LIMIT 5

--5개 ROW를 출력하라는 의미

SELECT    COL1, COL2

FROM    TAB01

ORDER BY  COL1

LIMIT 6 OFFSET 3

--3ROW를 건너뛰고 6개를 출력하라는 의미

WITH T AS (

    SELECT '20140717' DD FROM DUAL

)

SELECT  DD

        , SUBSTR(DD, 1, 4)||'-'||SUBSTR(DD, 5, 2)||'-'||SUBSTR(DD, 7) DD1

        , TO_CHAR(TO_DATE(DD, 'YYYYMMDD'), 'YYYY-MM-DD') DD2

        , REGEXP_REPLACE(DD, '(....)(..)(..)', '\1-\2-\3') DD3

FROM    T  

WITH T AS (

SELECT  2500 AS COL1 FROM DUAL UNION ALL

SELECT  NULL AS COL1 FROM DUAL UNION ALL

SELECT  1000 AS COL1 FROM DUAL UNION ALL

SELECT  23   AS COL1 FROM DUAL UNION ALL

SELECT  NULL AS COL1 FROM DUAL

)

SELECT  * FROM T

ORDER BY COL1

  23

1000

2500

NULL

NULL

SELECT  * FROM T

ORDER BY COL1 NULLS FIRST

NULL

NULL

23

1000

2500

WITH T(TYPE, NAME, CODE) AS

(

SELECT '과일', '사과', '0' FROM dual

UNION ALL SELECT '과일', '레몬', '1' FROM dual

UNION ALL SELECT '과일', '포도', '2' FROM dual

UNION ALL SELECT '과일', '참외', '3' FROM dual

UNION ALL SELECT '채소', '오이', '0' FROM dual

UNION ALL SELECT '채소', '당근', '1' FROM dual

UNION ALL SELECT '채소', '호박', '2' FROM dual

)

SELECT type

     , SUBSTR(XMLAGG(XMLELEMENT(X, ',', NAME) ORDER BY CODE).EXTRACT('//text()'), 2) NAME_9I

     , WM_CONCAT(NAME) NAME_10G

     , LISTAGG(NAME, ',') WITHIN GROUP(ORDER BY CODE) NAME_11G

  FROM t

 GROUP BY type

 ORDER BY type

;

SELECT type

     , group_concat(name ORDER BY code) name_MySQL

  FROM t

 GROUP BY type

 ORDER BY type

SELECT type

     , STUFF((SELECT ',' + name

                FROM t

               WHERE type = a.type

               ORDER BY code

                 FOR XML PATH('')

              ), 1, 1, '') name_MSSQL

  FROM t a

 GROUP BY type

 ORDER BY type

WW : 해당해의 1일부터 7일단위로 끊어서 주차를 생성

IW   : 날짜 해당한 주가 하루라도 다음해인 경우에는 그해의 첫주가 됨.

W : 해당월의 주차를 구함

UPDATE TAB1 A 

SET     COL1 = ( SELECT COL2 

                         FROM TAB2

                         WHERE   KEY = A.KEY );

UPDATE  /*+ BYPASS_UJVC  */

(

    SELECT  A.COL1   COL1

                , B.COL2   COL2

    FROM   TAB1 A

               , TAB2 B

    WHERE   A.KEY = B.KEY

)

SET COL1 = COL2

UPDATE  TAB1

SET        COL1 = B.COL2

FROM     TAB1 A INNER JOIN TAB2 B ON A.KEY = B.KEY