Thë 굽은ㄴr무™

출처 : http://database.sarang.net/?inc=read&aid=25686&criteria=oracle&subcrit=&id=&limit=20&page=3

 

MONTHS_BETWEEN 과 ADD_MONTHS 함수는 잘 이해하고 사용하셔야 합니다.

오라클 매뉴얼을 보시면 아시겠지만...

위의 경우를 놓고 설명하자면,

 

1. 첫번째 v_a_dt : 20020228

두번째 v_b_dt : 20011128

-> 위의 경우와 같이 뒤의 일자부분이 동일한 경우에, MONTHS_BETWEEN 함수는 두 일자의 월간격 즉, 3을 리턴합니다.

 

3. 첫번째 v_a_dt : 20020228

두번째 v_b_dt : 20011130

-> 이 경우는, 달력을 보시면 아시겠지만, 두 날짜 모두 그 날짜가 속한 달의 말일입니다. 이 경우에도 위의 1의 경우와 마찬가지로 두 일자의 월간격 3을 리턴합니다.

 

2. 첫번째 v_a_dt : 20020228

두번째 v_b_dt : 20011129

-> 마지막으로 1번도 3번도 해당하지 않는 경우에는 다음과 같이 계산됩니다.

2001년 11월 29일 + 1달 = 2001년 12월 29일

2001년 11월 29일 + 2달 = 2002년 01월 29일

2001년 11월 29일 + 3달 = 2002년 02월 29일 인데, 28일까지 밖에 없으므로 3달 보다는 적은 2.xxxxxxx 달이 될 것입니다.

그러면 뒤에 소수부분은 어떻게 계산할까요?

2002년 01월 29일과 2002년 02월 28일은 30일의 차이가 나므로, 30일을 무조건 31로 나눈 30/31이 됩니다.

따라서 2번의 경우에는 MONTHS_BETWEEN 함수는 2+30/31을 리턴합니다.

계산해 보시면 아시겠지만, 확실히 30/31=0.96774193548387096774193548387096774194....... 인 값이 나옵니다.

 

이해가 되셨는지 모르겠네요.

 

-- SQL 쿼리 질문은 SQL 까페에서... http://cafe.daum.net/oraclesqltuning

 

WITH T AS
(
SELECT TO_DATE('20120229', 'YYYYMMDD') SDT, TO_DATE('20120329', 'YYYYMMDD') EDT FROM DUAL
UNION ALL SELECT TO_DATE('20120229', 'YYYYMMDD') , TO_DATE('20120330', 'YYYYMMDD')  FROM DUAL
UNION ALL SELECT TO_DATE('20120129', 'YYYYMMDD'), TO_DATE('20120228', 'YYYYMMDD') FROM DUAL
UNION ALL SELECT TO_DATE('20120130', 'YYYYMMDD'), TO_DATE('20120228', 'YYYYMMDD') FROM DUAL
UNION ALL SELECT TO_DATE('20120201', 'YYYYMMDD'), TO_DATE('20120229', 'YYYYMMDD') FROM DUAL
UNION ALL SELECT TO_DATE('20120131', 'YYYYMMDD'), TO_DATE('20120229', 'YYYYMMDD') FROM DUAL
UNION ALL SELECT TO_DATE('20111130', 'YYYYMMDD'), TO_DATE('20120331', 'YYYYMMDD') FROM DUAL
)
SELECT SDT
   , EDT
   , SMM
   , EMM
   , MM
         , CEIL(MONTHS_BETWEEN (EDT+1, SDT)) MM2
         , CEIL(MONTHS_BETWEEN (EDT, SDT)) MM3
   , D1
   , D2
   , DD
   , MM + ROUND (DD / 30) RESULT
         , MM + CEIL (DD / 30) RESULT2
 FROM (SELECT SDT
       , EDT
       , SMM
       , EMM
       , MONTHS_BETWEEN (EMM, SMM) MM
       , (SMM - SDT) D1
       , (EDT - EMM + 1) D2
       , (SMM - SDT) + (EDT - EMM + 1) DD
     FROM (SELECT SDT
           , EDT
           , TRUNC (ADD_MONTHS (SDT - 1, 1), 'MM') SMM
           , TRUNC (EDT + 1, 'MM') EMM
         FROM T))

 

=======================================================================================================

오라클의 MONTHS_BETWEEN과 비슷하게 처리한 예제

출처 : http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=60334&sca=%BD%BA%C5%A9%B8%B3%C6%AE&page=17

오라클의 add_months, months_between 함수를 자바스크립트로 만들일이 있어서 구현한 것입니다.

처음엔 만들기 귀찮아서.. 스쿨에서 비슷한 함수를 찾아봤는데 제가 못찾아서 그런건지 원래부터 없어서 그런건지 결국 못찾았습니다

간단히 코드 동작에 대해서 설명하자면....

add_months 함수의 경우
2008.01.31에 한달을 더하면 2008.02.29 가 나옵니다.
2008.04.30에 한달을 더하면 2008.05.31 이 나오고요
한달 더 한다고 단순히 기존 날짜에 30일을 더한 것이 아니라
마지막 날짜 여부까지 체크해줍니다.

months_between 함수의 경우
몇개월 차이인가 구하는 함수인데 여기저기 검색해보니
시작날짜, 종료날짜가 몇일인지 구한 후 단순히 30일로 나눠서 몇개월인지 구합니다.
이 경우 몇년 정도의 날짜 차이를 구하면 괜찮지만 몇십년 단위로
날짜 차이를 두면 당연히 몇개월의 오차가 생깁니다.(31일 날도 있어서)
그래서 이런 오차를 없애고자 1개월씩 더해서 루프 돌렸습니다 -_-
자세한 건 코드 보심 될듯싶습니다.

 

<script language="javascript" type="text/javascript">
/*
    오라클의 개월수 더하는 함수와 동일
    ex) add_months('2008.04.04',10);
*/
function add_months(pdate, diff_m) {
    var add_m;
    var lastDay;    // 마지막 날(30,31..)
    var pyear, pmonth, pday;
   
    pdate = makeDateFormat(pdate); // javascript 날짜형변수로 변환
    if (pdate == "") return "";

    pyear = pdate.getYear();
    pmonth= pdate.getMonth() + 1;
    pday  = pdate.getDate();
   
    add_m = new Date(pyear, pmonth + diff_m, 1);    // 더해진 달의 첫날로 셋팅

    lastDay = new Date(pyear, pmonth, 0).getDate(); // 현재월의 마지막 날짜를 가져온다.
    if (lastDay == pday) {  // 현재월의 마지막 일자라면 더해진 월도 마지막 일자로
        pday = new Date(add_m.getYear(), add_m.getMonth(), 0).getDate();
    }

    add_m = new Date(add_m.getYear(), add_m.getMonth()-1, pday);

    return add_m;
}

/*
    오라클의 개월수 차이 구하는 함수와 동일
    ex) months_between('20080404','20060101');
*/
function months_between(edate, sdate) {
    var syear, smonth, sday;
    var eyear, emonth, eday;
    var diff_month = 1;

    sdate = makeDateFormat(sdate); // javascript 날짜형변수로 변환
    edate = makeDateFormat(edate); // javascript 날짜형변수로 변환

    if (sdate == "") return "";
    if (edate == "") return "";

    syear = sdate.getYear();
    eyear = edate.getYear();
    smonth= sdate.getMonth() + 1;
    emonth= edate.getMonth() + 1;
    sday  = sdate.getDate();
    eday  = edate.getDate();

    while (sdate < edate) { // 한달씩 더해서 몇개월 차이 생기는지 검사
        sdate = new Date(syear, smonth - 1 + diff_month, 0);
        diff_month++;
    }

    if (sday > eday) diff_month--;

    diff_month = diff_month - 2;
   
    return diff_month;
}

/* yyyymmdd, yyyy-mm-dd, yyyy.mm.dd 를 javascript 날짜형 변수로 변환 */
function makeDateFormat(pdate) {
    var yy, mm, dd, yymmdd;
    var ar;
    if (pdate.indexOf(".") > -1) {  // yyyy.mm.dd
        ar = pdate.split(".");
        yy = ar[0];
        mm = ar[1];
        dd = ar[2];

        if (mm < 10) mm = "0" + mm;
        if (dd < 10) dd = "0" + dd;
    } else if (pdate.indexOf("-") > -1) {// yyyy-mm-dd
        ar = pdate.split("-");
        yy = ar[0];
        mm = ar[1];
        dd = ar[2];

        if (mm < 10) mm = "0" + mm;
        if (dd < 10) dd = "0" + dd;
    } else if (pdate.length == 8) {
        yy = pdate.substr(0,4);
        mm = pdate.substr(4,2);
        dd = pdate.substr(6,2);
    }

    yymmdd = yy+"/"+mm+"/"+dd;

    yymmdd = new Date(yymmdd);
   
    if (isNaN(yymmdd)) {
        return false;
    }

    return yymmdd;
}

// 4월30일에 1개월 더하기 => 5월31일
alert(add_months('2008.04.30',1));

// 몇개월 차이인지 구하기 => 1104 개월 차이
alert(months_between('2100.01.01','2008.01.01'));

</script>

 

출처 : http://acidsound.blogspot.com/2007/10/oracle10g-xmlagg-group-by-query.html

C군의 질문

A | B
------
A | 가
B | 가
C | 가
a | 나
B | 나
C | 다

일때

가 | ABC
나 | aB
다 | C

를 뽑아낼 수 없을까?

라고 하여 요렇게 조렇게 실험해봤음.
일종의 String Aggregation 인데 http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php 이런 방법도 있지만 왠지 복잡하고 10g 기능을 사용하고 싶어서 뒤져보기 시작.

(10g 가 아니라면 sys_connect_by_path 를 사용하는 방법이 무난. 비용이 좀 든다는 단점이 있다고 한다.)
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


XMLAGG 로 GROUP BY를 할 수 있다는 사실을 발견

SELECT B, XMLAGG(A) FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

하지만 이렇게 하면 오류가 난다.
타입이 맞지 않기 때문이다. 인자를 XML형으로 바꾸기 위해 XMLELEMENT를 사용한다.
컬럼에 C라는 Tag 명을 붙여서 보자.

SELECT B, XMLAGG(XMLELEMENT(C,A)) A FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

결과는 나오지만 충분하지 않다. 실제로 Application 에서 사용하려면 XML 타입이라 맞지 않기 때문이다.
GETSTRINGVAL()을 붙여서 형변환을 해준다.

SELECT B, XMLAGG(XMLELEMENT(C,A)).GETSTRINGVAL() A
FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

B | A
가 | ABC
나 | aB
다 | C

원하는 결과가 나왔다.
Tag 을 제거하자 기왕 xml도 건든 김에 정규표현식으로 제거해보자.


SELECT B, REGEXP_REPLACE(XMLAGG(XMLELEMENT(C,A)).GETSTRINGVAL(), '<[C/]+>', '') A
FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

B | A
------
가 | ABC
나 | aB
다 | C


정규식을 사용하지 않고 태그 제거하기
WITH
TABLE AS (
SELECT 이름 FROM 유저목록
)
SELECT
LTRIM(SUBSTR(XMLAGG(XMLELEMENT(이름,'|'||이름 )).EXTRACT('//text()').GetStringVal(), 2))
FROM TABLE)

======================================================================================================

출처 : http://database.sarang.net/?inc=read&aid=38771&criteria=oracle&subcrit=&id=&limit=20&keyword=SYS_CONNECT_BY_PATH&page=1

 

with tbl as ( select 1 seq , '20110801' ymd, 't' gubun, 'input1' title from dual union select 2 seq , '20110802' ymd, 't' gubun, 'input2' title from dual union select 3 seq , '20110802' ymd, 't' gubun, 'input3' title from dual union select 4 seq , '20110802' ymd, 't' gubun, 'input4' title from dual union select 5 seq , '20110802' ymd, 't' gubun, 'input5' title from dual union select 6 seq , '20110804' ymd, 'c' gubun, 'input6' title from dual ) select ymd,wm_concat(seq||'||'||title) as seq_title from tbl group by ymd

제가 할려는게 같은 날짜의 title 를 seq 순서대로 뽑아내는건대요

위에 쿼리를 돌려보면

20110801 1||input1
20110802 2||input2,4||input4,5||input5,3||input3 <== 여기가 문제임
20110804 6||input6

이런식으로 나옵니다. 즉 2,3,4,5 이런순으로 나와야 하는대 엉뚱하게 3이 젤 나중에 나와버립니다.

어떻게 해야 할까요? 도움 주시면 감사하겠습니다...

 

wm_concat 간단하면서도 강력한 기능 정말 좋은데... 한가지 아쉬운 점이 정렬 기능이 없다는 거죠... XmlAgg(9i) 나 ListAgg(11g) 를 이용하시면 됩니다. SELECT ymd , SUBSTR(XMLAGG(XMLELEMENT(x, ',', seq, '||', title) ORDER BY seq) .EXTRACT('//text()'), 2) v_9i , wm_concat(seq||'||'||title) v_10g , LISTAGG(seq||'||'||title, ',') WITHIN GROUP(ORDER BY seq) v_11g FROM tbl GROUP BY ymd ORDER BY ymd ; wm_concat를 이용해 정렬하고자 한다면.. 인라인뷰에서 분석함수의 정렬기능을 이용하신후 밖에서 걸러내시면 됩니다. SELECT ymd , seq_title FROM (SELECT ymd, seq , wm_concat(seq||'||'||title) OVER(PARTITION BY ymd ORDER BY seq) seq_title , MAX(seq) OVER(PARTITION BY ymd) max_seq FROM tbl ) WHERE seq = max_seq ORDER BY ymd ; Connect_By_Path(9i)를 이용하는 방법도 있는데 더 복잡하고 성능도 안좋아요. SELECT ymd , SUBSTR(SYS_CONNECT_BY_PATH(seq||'||'||title, ','), 2) seq_title FROM (SELECT ymd, seq, title , ROW_NUMBER() OVER(PARTITION BY ymd ORDER BY seq) rn , COUNT(*) OVER(PARTITION BY ymd) cnt FROM tbl ) WHERE rn = cnt START WITH rn = 1 CONNECT BY PRIOR ymd = ymd AND PRIOR rn + 1 = rn ;

 

 

출처 : http://blog.daum.net/coschao/16
         http://database.sarang.net/?inc=read&aid=38441&criteria=oracle&subcrit=&id=&limit=20&keyword=%C8%DE%C0%CF&page=1



1. 입력일 포함 다음 영업일 구하기 (휴일 테이블 사용)

휴일정보테이블(HOLIDAY_INFO_TABLE)은
    HOLIDAY_YMD   VARCHAR2(8 BYTE)  NOT NULL, /* 휴일날짜(yyyyMMdd) */
    HOLIDAY_NM    VARCHAR2(30 BYTE) NOT NULL, /* 휴일명 */

    .... 기타 추가정보

로 구성되어 있다고 가정됨.

 

아래에서 IN_YMD 는 'yyyyMMdd' 형식의 입력되는 변수(펑션의 변수 처럼..)

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

    select b.YMD /* into RT_VALUE -- (펑션이라면 반환값) */
      from (
            select a.YMD, s.HOLIDAY_NM || case TO_CHAR(TO_DATE(a.YMD, 'YYYYMMDD'), 'D')
                                          when '1' then '일'
                                          when '7' then '토'
                                          else '' end as HOLI_DOW_TXT
              from (
                        select to_char(x.YMD_DATE - 1 + level, 'YYYYMMDD') as YMD
                          from (select to_date(IN_YMD, 'YYYYMMDD') as YMD_DATE from dual) x
                         where (x.YMD_DATE - 1 + level) <= last_day(x.YMD_DATE)
                       connect by level<=15 /* 입력일 이후 15일간 줄 세워봄. 15일간 연짱 휴일일리는 없겠지... */
                 ) a, HOLIDAY_INFO_TABLE s
             where a.YMD = s.HOLIDAY_YMD(+)
             order by a.YMD
        ) b
    where b.HOLI_DOW_TXT is null
      and rownum = 1

2. 두 기간 사이의 영업일 건수 구하기

WITH off_day AS
(
SELECT '20110301' dt, '삼일절' cmt FROM dual
)
SELECT COUNT(*) cnt
  FROM (SELECT TO_CHAR(sdt + LEVEL - 1, 'yyyymmdd') dt
             , TO_CHAR(sdt + LEVEL - 1, 'd') d
          FROM (SELECT TO_DATE('20110311', 'yyyymmdd') sdt
                     , TO_DATE('20110315', 'yyyymmdd') edt
                  FROM dual)
        CONNECT BY LEVEL <= edt - sdt + 1
        ) a
     , off_day b
 WHERE a.dt = b.dt(+)
   AND a.d NOT IN ('1', '7')
   AND b.dt IS NULL




3. 도시별 휴일 제외한 영업일 구하기 

메인테이블 일자 영업일 도시코드 20120321 2 01 20120322 5 02 20120323 4 03 휴일테이블 도시코드 휴일일자 01 20120322 01 20120323 02 20120323 03 20120326 메인테이블의 일자에 영업일을 더해서 다음 영업일자를 구해야 합니다. 다음 영업일은 토/일은 기본으로 제외시키고 휴일테이블의 해당 도시에 해당하는 휴일도 제외하여 계산되어야 하는데요. 예를 들면 20120321 +2 영업일은 22,23(휴일), 24,25(토,일) 이므로 20120327일이 나와야 합니다. Function등을 사용하지 않고 쿼리로만 사용할 수 있는 방법이 있을까요? 오라클 11G 기준 재귀쿼리입니다. 이하 버전에서는 안돌아 갑니다. WITH t1 AS ( SELECT '20120321' dt, 2 dy, '01' ct FROM dual UNION ALL SELECT '20120322', 5, '02' FROM dual UNION ALL SELECT '20120323', 4, '03' FROM dual ) , t2 AS ( SELECT '01' ct, '20120322' dt FROM dual UNION ALL SELECT '01', '20120323' FROM dual UNION ALL SELECT '02', '20120323' FROM dual UNION ALL SELECT '03', '20120326' FROM dual ) , t3(dt, dy, ct) AS ( SELECT * FROM t1 UNION ALL SELECT TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'yyyymmdd') dt , a.dy - CASE WHEN b.dt IS NOT NULL THEN 0 WHEN TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'd') IN ('1','7') THEN 0 ELSE 1 END AS dy , a.ct FROM t3 a LEFT JOIN t2 b ON a.ct = b.ct AND b.dt = TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'yyyymmdd') WHERE a.dy > 0 ) SELECT ct, dt FROM t3 WHERE dy = 0 ORDER BY ct, dt ;

 

출처 : http://stove99.tistory.com/115

다운로드 : ftp://ftp.ca.com/pub/erwin/ServicePacks/


ERWin 을 이용해 논리, 물리 ERD 를 작성했으면 이제 맹근 ERD 를 바탕으로 데이터베이스에 테이블을 생성해야 한다.

요 테이블을 생성하기 위해서는 뭐 ERWin 에서 데이터베이스에 직접 접속해서 테이블들을 생성까지 해주는 기능이 있지만,


왠지 번거롭게 느껴져서 그냥 테이블 생성 스크립트만 생성해서, 생성된 스크립트를 다른 디비접속툴을 사용해 뽓 실행해 주는것이 더 간편해서 일단

테이블 스크립트만 ERWin 상에서 생성해 보자.



먼저 테스트로 맹글어본 ERD 는 간단히 테이블 하나만 있다. 뭐 테이블이 수없이 많이 있어도 하는방법은 똑같으니깐~

테이블은 논리, 물리 요렇게 되 있다.

빨간색 박스친 부분을 보면 논리모델과 물리모델에 있는 컬럼 순서가 다르게 되 있다.

보통 ERD를 작성할때 당연히 논리모델을 쭉 다 맹글고 물리 모델을 대충 나중에 만든다. 그런데 논리 모델을 맹글면서 컬럼을 순서대로 추가하면 죠런 현상이 없지만,

뭐 그렇게 하기가 힘드니 문득문득 생각나는걸 또 추가하고 추가하고 해서 순서만 살짝살짝 바꿔준다.

그렇게 하다보니 논리모델은 순서가 내가 원하는 순서로 되있지만, 물리모델은 논리모델에서 컬럼을 추가한 순서가 그대로 유지되 있다.

뭐 물론 이상태에서 스크립트를 생성해도 크게 문제는 없지만(생성된 스크립트를 보면 물리모델에 보이는 컬럼순서대로 스크립트가 생성됨)

좋은게 좋은거라고 이왕이면 논리적으로 생각하는 순서와 똑같이 맹글고 싶다.



고걸 하기 위해서는 물리 ERD 작성하는 VIEW로 바꾼다음에 아무테이블 위에서 따블클릭을 하면 요런창이 뽁 뜨는데 요렇게 설정해 주면 논리모델과 순서를 똑같이 맞춰준다.

이제 물리모델의 컬럼 순서까지 보기 좋게 싹 맞춰 줬으니 테이블 생성 스크립트를 생성해 보자.

생성하기전에 살짝 해줄게 하나 있다. 테이블을 생성할때 딸랑 테이블만 생성해도 되지만 이왕이면 논리모델에서 작성한 테이블 이름과 컬럼이름을 코맨트로 달아서

다른 디비접속툴에서도 이 컬럼이 뭐하는 컬럼인지 쉽게 알 수 있게 해주면 좋다.

고런걸 자동으로 해주면 좋겠지만, 아직까진 그렇진 않기 때문에 테이블 생성 스크립트에 코맨트를 추가해주도록 ERWin에서 쓰는 스크립트를 하나 추가해야 한다.


스크립트 추가하기

메뉴 > Database > Pre & Post Scripts > Model-Level...

New 버튼을 클릭해서 적당한 이름으로 하나 추가하고 하단의 스크립트 Code 입력란에 각 디비에 맞는 스크립트를 입력해 준다음 OK 버튼을 클릭하면 된다.

※ 7.3.11 버전
1. 메뉴 > Database > Pre & PostScripts... 메뉴 선택
2. "New" 아이콘 클릭
    - Name : Comment 생성
    - Code 탭에 아래 스크립트 추가




※ 각 데이터베이스별 코맨트 생성 스크립트

MySQL

%ForEachTable() {     ALTER TABLE %TableName COMMENT = '%EntityName';     %ForEachColumn()     {         ALTER TABLE %TableName CHANGE COLUMN %ColName %ColName %AttDatatype %AttNullOption COMMENT '%AttName';     } }

==================================================================================================================

설정


* Forward Engineer - Schema Generation 옵션
  - Column > Physical Order : 체크 해제, 필드 순서를 보이는대로 script 생성
  - Trigger > ERWin Generated : 체크 해제, 불필요한 Trigger 생성 제거
  - Schema > Post-Script : 체크, Comment 스크립트 추가
               > Create > User Defined Type : 체크해제, Domains 생성 스크립트 제거
  - Referential Integrity > Foreign Key (FK) : 체크 해제, Foreign key 생성 제거
  - Other Options > Constraint Name : Script 생성 시 Constraint 생성 여부
  - Table > Drop > Table : Drop Table script 최상단 추가


* Physical 에서 datatype 보기
- Format > Table Display > Column Datatype 선택


* Constraint Key명 변경
- Tools > Names > Model Naming Options 메뉴 선택 > Name Mapping 탭 선택
- Key Group to Index 항목 편집 : 기본값 (%KeyName)

- 예제 테이블명 TB_USR
    PK 룰 : PK_TB_USR
    AK 룰 (Unique) : UX_TB_USR_1
    IE 룰 (Index) : IX_TB_USR_1

%If(%==(%Substr(%KeyType,1,2),IE)){IX}%If(%==(%Substr(%KeyType,1,2),AK)){UX}%If(%==(%KeyType,PK)){PK}_%TableName%If(%Not(%==(%KeyType,PK))){_%Substr(%KeyType,3,1)}

- 예제 테이블명 TB_USR
    PK 룰 : TB_USR_PK
    AK 룰 (Unique) : TB_USR_UX1
    IE 룰 (Index) : TB_USR_IX1

%TableName_%If(%==(%Substr(%KeyType,1,2),IE)){IX}%If(%==(%Substr(%KeyType,1,2),AK)){UX}%If(%==(%KeyType,PK)){PK}%If(%Not(%==(%KeyType,PK))){_%Substr(%KeyType,3,1)}

- %IndexType, Else 샘플

%If(%==(%IndexType,PK)){PK_%TableName}%Else{%IF(%==(%substr(%IndexType,1,2),IF)) {%TableName_%substr(%IndexType,2)}}

* 연결선(관계) 모양 변경
Model > Model Properties : Notation 탭에서 Logical Notation => IE, Physical Notation => IE 선택



==================================================================================================================

Image Export


1. Tools > Report Template Builder > Report Builder 선택

2. New 버튼 클릭

3. 왼쪽의 G Graphical > Picture 선택 후 우측이동 버튼 클릭

4. 저장 버튼 클릭하여 rtb 파일 저장 후 해당 팝업 (New 클릭 후 열린 팝업) 닫기

5. Report Builder 팝업에서 Available Templates 영역에서 저장한 rtb 파일 선택
   Output Type : PDF 선택
   Browse... 버튼을 클릭하여 저장 위치 지정
   Run 버튼 클릭하여 이미지 생성

6. Browse... 에서 지정한 경로에 가면 images 폴더 아래에 이미지가 저장되어 있음.

참고 : http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries003.htm


1. 샘플 데이터 생성

CREATE TABLE EMPLOYEES
(
  EMPLOYEE_ID     NUMBER(6),
  FIRST_NAME      VARCHAR2(20 BYTE),
  LAST_NAME       VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
  EMAIL           VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
  PHONE_NUMBER    VARCHAR2(20 BYTE),
  HIRE_DATE       DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
  JOB_ID          VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
  SALARY          NUMBER(8,2),
  COMMISSION_PCT  NUMBER(2,2),
  MANAGER_ID      NUMBER(6),
  DEPARTMENT_ID   NUMBER(4)
);
CREATE INDEX EMP_DEPARTMENT_IX ON EMPLOYEES(DEPARTMENT_ID);
CREATE UNIQUE INDEX EMP_EMAIL_UK ON EMPLOYEES(EMAIL);
CREATE UNIQUE INDEX EMP_EMP_ID_PK ON EMPLOYEES(EMPLOYEE_ID);
CREATE INDEX EMP_JOB_IX ON EMPLOYEES(JOB_ID);
CREATE INDEX EMP_MANAGER_IX ON EMPLOYEES(MANAGER_ID);
CREATE INDEX EMP_NAME_IX ON EMPLOYEES(LAST_NAME, FIRST_NAME);
ALTER TABLE EMPLOYEES ADD (
  CONSTRAINT EMP_SALARY_MIN
  CHECK (salary > 0),
  CONSTRAINT EMP_EMP_ID_PK
  PRIMARY KEY
  (EMPLOYEE_ID)
  USING INDEX EMP_EMP_ID_PK,
  CONSTRAINT EMP_EMAIL_UK
  UNIQUE (EMAIL)
  USING INDEX EMP_EMAIL_UK);
ALTER TABLE EMPLOYEES ADD (
  CONSTRAINT EMP_MANAGER_FK
  FOREIGN KEY (MANAGER_ID)
  REFERENCES EMPLOYEES (EMPLOYEE_ID));
INSERT INTO EMPLOYEES
SELECT *
  FROM HR.EMPLOYEES;
SELECT * FROM EMPLOYEES;

2. 테스트 쿼리

-- SYS_CONNECT_BY_PATH
    SELECT EMPLOYEE_ID
         , LAST_NAME
         , MANAGER_ID
         , LEVEL
         , LTRIM(SYS_CONNECT_BY_PATH(LAST_NAME, '/'), '/') "PATH"
         , CONNECT_BY_ROOT LAST_NAME ROOT_LAST_NAME
         , CONNECT_BY_ISLEAF
      FROM EMPLOYEES
  START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY LAST_NAME

-- START WITH가 없을 경우 LEVEL의 값에 따라 ROW 중복 생성 (LEVEL = 2일 경우 LEVEL 값만 1,2로 다르고 값은 똑같은 ROW가 2개 생김)
    SELECT EMPLOYEE_ID
         , LAST_NAME
         , MANAGER_ID
         , LEVEL
      FROM EMPLOYEES
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY EMPLOYEE_ID

-- DEPARTMENT_ID = 110 의 사원별로 자신의 상관 검색
    SELECT EMPLOYEE_ID
         , LAST_NAME
         , MANAGER_ID
         , SYS_CONNECT_BY_PATH (LAST_NAME, '/') "PATH"
         , CONNECT_BY_ROOT (LAST_NAME) ROOT_LAST_NAME
         , LEVEL LVL
      FROM EMPLOYEES
     WHERE DEPARTMENT_ID = 110
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
  ORDER BY EMPLOYEE_ID, MANAGER_ID, LVL DESC, "PATH"

-- 사원부터 자신의 상관 검색
    SELECT EMPLOYEE_ID
         , LAST_NAME
         , MANAGER_ID
         , SYS_CONNECT_BY_PATH (LAST_NAME, '/') "PATH"
         , SYS_CONNECT_BY_PATH (LAST_NAME, CHR(27)) "PATH2"
         , CONNECT_BY_ROOT (LAST_NAME) ROOT_LAST_NAME
         , LEVEL LVL
      FROM EMPLOYEES
START WITH EMPLOYEE_ID = 103
CONNECT BY EMPLOYEE_ID = PRIOR MANAGER_ID
  ORDER BY LVL DESC

-- LEVEL 별로 순번
  SELECT A.*
       , LPAD (' ', 5 * (LVL - 1), ' ') || NVL2 (MANAGER_ID, ROW_NUMBER () OVER (PARTITION BY MANAGER_ID ORDER BY LAST_NAME), NULL) TREE
       , COUNT(*) OVER(PARTITION BY MANAGER_ID) CNT
    FROM (           SELECT EMPLOYEE_ID
                          , LAST_NAME
                          , MANAGER_ID
                          , LEVEL LVL
                          , LTRIM (SYS_CONNECT_BY_PATH (LAST_NAME, '/'), '/') "PATH"
                          , SYS_CONNECT_BY_PATH (LAST_NAME, '/') PATH2
                       FROM EMPLOYEES
                 START WITH EMPLOYEE_ID = 100
                 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
          ORDER SIBLINGS BY LAST_NAME) A
ORDER BY ROWNUM


-- LEVEL 한단계 부모의 값 조회
  SELECT A.*
       , DECODE (LVL
               , 1, NULL
               , SUBSTR (PATH2, INSTR (PATH2, '/', 1, LVL - 1)+ 1
                                         , INSTR (PATH2, '/', 1, LVL) - INSTR (PATH2, '/', 1, LVL - 1) - 1)) LVL_PARENT
       , LPAD (' ', 5 * (LVL - 1), ' ') || NVL2 (MANAGER_ID, ROW_NUMBER () OVER (PARTITION BY MANAGER_ID ORDER BY ROWNUM), NULL) TREE
       , NVL2 (MANAGER_ID, ROW_NUMBER () OVER (PARTITION BY MANAGER_ID ORDER BY ROWNUM), NULL) TREE2
       , COUNT (*) OVER (PARTITION BY MANAGER_ID) CNT
    FROM (           SELECT EMPLOYEE_ID
                          , LAST_NAME
                          , MANAGER_ID
                          , LEVEL LVL
                          , LTRIM (SYS_CONNECT_BY_PATH (LAST_NAME, '/'), '/') "PATH"
                          , SYS_CONNECT_BY_PATH (LAST_NAME, '/') PATH2
                       FROM EMPLOYEES
                 START WITH EMPLOYEE_ID = 100
                 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
          ORDER SIBLINGS BY LAST_NAME) A
ORDER BY ROWNUM

3. Javascript에서 CHR(27) 구분하기

"메뉴1\u001b서브메뉴1".split(String.fromCharCode(27));

=======================================================================================


Recursive Subquery 사용으로 Connect by 처리

출처 : https://ukja.tistory.com/m/359


Oracle 11gR2부터 Recursive Subquery Factoring이라는 기능을 제공합니다. 이 기능을 이용하면 Connect By 구문을 대신할 수 있죠. 아래에 간단한 사용법이 있습니다.

SQL>  select lpad(' ', 2 * level - 2, ' ') || ename as ename   2        , empno   3        , mgr   4        , level   5     from emp   6  connect by mgr = prior empno   7    start with mgr is null   8  / 

SQL> with emps (ename,empno,mgr,lvl) as   2  ( select ename   3         , empno   4         , mgr   5         , 1   6      from emp   7     where mgr is null   8     union all   9    select emp.ename  10         , emp.empno  11         , emp.mgr  12         , emps.lvl + 1  13      from emp  14           join emps on (emp.mgr = emps.empno)  15  ) search depth first by empno set a  16  select lpad(' ', 2 * lvl - 2, ' ') || ename as ename  17       , empno  18       , mgr  19       , lvl  20    from emps  21    order by a  22  / 

굳이 Connect By로 잘 사용하고 있었던 것을 왜 다시 Recursive Subquery Factoring을 사용해야 하는지를 고민해보면 딱히 떠오르는 것이 없는데요. 다음과 같은 유형의 쿼리에서 유용하게 사용할 수 있을 것 같습니다.

1. 다음과 같은 두 개의 테이블 T_MATERIAL과 T_COMPOSE가 있습니다. 테이블 T_MATERIAL은 "약"(material_type=Med) 또는 "약의 성분"(material_type=Mat) 데이터를 가지고 있습니다. 테이블 T_COMPOSE는 하나의 약이 어떤 하위 성분과 하위 약으로 이루어져 있는지의 관계를 나타냅니다. 하위 약은 다시 하위 약 또는 하위 성분을 가지므로 계층 구조가 됩니다. 그리고 각 하위 약 또는 하위 성분이 몇 %를 구성하고 있는지의 정보(contain_pct)를 가지고 있습니다.

SQL> create table t_material(   2  	material_id		number,   3  	material_name	varchar2(10),   4  	material_type	varchar2(3) -- Med = medicine, Mat = material   5  );  Table created.  SQL>  SQL> create table t_compose (   2  	medicine_id			number,   3  	material_id			number,   4  	contain_pct			number   5  );  Table created. 

이제 다음과 같이 데이터를 생성합니다.

  SQL> begin   2  	insert into t_material values(1, 'medicine1', 'Med');   3  	insert into t_material values(2, 'medicine2', 'Med');   4  	insert into t_material values(3, 'material1', 'Mat');   5  	insert into t_material values(4, 'medicine3', 'Med');   6  	insert into t_material values(5, 'material2', 'Mat');   7  	insert into t_material values(6, 'medicine4', 'Med');   8  end;   9  /  PL/SQL procedure successfully completed.  SQL>  SQL> begin   2  	insert into t_compose values(1, 2, 0.3); -- Med   3  		insert into t_compose values(2, 6, 0.5);   4  			insert into t_compose values(6, 3, 0.8);   5  			insert into t_compose values(6, 5, 0.2);   6  		insert into t_compose values(2, 5, 0.5);   7  	insert into t_compose values(1, 3, 0.3); -- Mat   8  	insert into t_compose values(1, 4, 0.2); -- Med   9  		insert into t_compose values(4, 3, 0.7);  10  		insert into t_compose values(4, 5, 0.3);  11  	insert into t_compose values(1, 5, 0.2); -- Mat  12  end;  13  /  PL/SQL procedure successfully completed. 

1번 약은 (2번 약 30% + 3번 성분 30% + 4번 약 20% + 5번 성분 20%) 으로 이루어져있죠. 2번 약은 (6번 약 50% + 5번 약 50%)로 이루어져 있고, 6번 약은 (3번 성분 80% + 5번 성분 20%)로 이루어져 있습니다. 이런 식으로 계층 구조를 이루고 있습니다.

계층 구조를 지니면서 성분의 함량(contain_pct) 정보가 존재합니다. 여기서 이런 쿼리가 필요해집니다. 1번 약을 구성하는 각 성분의 함량은 어떻게 되는가? 즉, 1번 약을 구성하는 성분인 3번 성분(material1)과 5번 성분(material2)는 각각 몇 %인가?

위와 같은 쿼리가 까다로운 것은 계층 구조를 따라 모든 노드의 값(여기서는 contain_pct)를 알아야하기 때문입니다. 간단하게 계산해보면 3번 성분(material1)의 함량을 구하려면 계층 구조를 따라가면서 0.3*0.5*0.8 + 0.3 + 0.2*0.7 = 0.56 = 56%와 같은 계산이 필요합니다.

Connect By 구문에서는 현재 값과 이전 값(부모 값)만을 알 수 있습니다. 이 한계를 극복하기 위해 나온 것이 SYS_CONNECT_BY_PATH같은 함수죠. 아래와 같이 각 노드의 모든 함량 정보를 얻을 수 있습니다.

SQL> col pholder format a10 SQL> col pct_path format a20 SQL> select   2  	lpad('-',level,'-') as pholder,   3  	medicine_id,   4  	material_id,   5  	contain_pct,   6  	sys_connect_by_path(contain_pct,'/') as pct_path   7  from   8  	t_compose   9  connect by medicine_id = prior material_id  10  start with medicine_id = 1  11  ;  PHOLDER    MEDICINE_ID MATERIAL_ID CONTAIN_PCT PCT_PATH ---------- ----------- ----------- ----------- -------------------- -                    1           2          .3 /.3 --                   2           5          .5 /.3/.5 --                   2           6          .5 /.3/.5 ---                  6           3          .8 /.3/.5/.8 ---                  6           5          .2 /.3/.5/.2 -                    1           3          .3 /.3 -                    1           4          .2 /.2 --                   4           3          .7 /.2/.7 --                   4           5          .3 /.2/.3 -                    1           5          .2 /.2  10 rows selected. 

위의 값을 실제로 계산하려면 다음과 같이 별도의 함수를 이용한 로직이 필요하게 됩니다.

SQL> create or replace function get_total_pct(pct_path in varchar2)   2  return number   3  is   4  	v_idx1		number := 0;   5  	v_idx2		number;   6  	v_temp		number;   7  	v_total		number := 1;   8  begin   9  	v_idx1 := instr(pct_path, '/');  10    11  	loop  12    13  		v_idx2 := instr(pct_path, '/', v_idx1+1);  14  		if v_idx2 = 0 then  15  			v_idx2 := length(pct_path)+1;  16  		end if;  17    18  		v_temp := to_number(substr(pct_path, v_idx1+1, v_idx2-v_idx1-1));  19  		v_total := v_total * v_temp;  20    21  		v_idx1 := v_idx2;  22    23  		exit when v_idx1 > length(pct_path);  24    25  	end loop;  26    27  	return v_total;  28  end;  29  /  Function created. 

CONNECT BY 구문과 SYS_CONNECT_BY_PATH 함수, 그리고 위에서 정의한 함수 GET_TOTAL_PCT를 이용하면 다음과 같이 원하는 값을 얻을 수 있습니다.

SQL> with c as (   2  	select   3  		material_id,   4  		get_total_pct(sys_connect_by_path(contain_pct,'/')) as comp_pct   5  	from   6  		t_compose   7  	connect by medicine_id = prior material_id   8  	start with medicine_id = 1   9  )  10  select  11  	m.material_name,  12  	sum(c.comp_pct) as total_pct  13  from  14  	c,  15  	t_material m  16  where  17  	c.material_id = m.material_id  18  	and m.material_type = 'Mat'  19  group by  20  	m.material_name  21  ;  MATERIAL_N  TOTAL_PCT ---------- ---------- material1         .56 material2         .44 

(음... 더 멋진 방법이 있을 듯... )

Recursive Subquery Factoring에서는 위의 작업을 보다 직관적으로 처리할 수 있습니다. 다음과 같이 부모의 값을 받아서 함량(contain_pct)을 계속 곱해가면 최종 자식 노드의 함량을 알 수 있죠. 그 값을 SUM 하면 함량의 합이 됩니다. 즉, Recursive Subquery Factoring의 장점은 SYS_CONNECT_BY_PATH 같은 함수의 도움을 빌리지 않아도 모든 모드의 값을 이용할 수 있다는 것입니다.

SQL> with recur_mat(comp_pct, material_id)   2  as (   3  	select   4  		contain_pct,   5  		material_id   6  	from   7  		t_compose   8  	where   9  		medicine_id = 1  10  	union all  11  	select  12  		p.comp_pct * c.contain_pct,  -- 부모 * 현재   13  		c.material_id  14  	from  15  		recur_mat p,  16  		t_compose c  17  	where  18  		c.medicine_id = p.material_id  19  )  20  select  21  	m.material_name,  22  	sum(r.comp_pct) as total_pct  23  from  24  	recur_mat r,  25  	t_material m  26  where  27  	r.material_id = m.material_id  28  	and m.material_type = 'Mat'  29  group by  30  	m.material_name  31  ;  MATERIAL_N  TOTAL_PCT ---------- ---------- material1         .56 material2         .44 

아... 설명이 좀 구질구질했는데요. Recursive Subquery Factoring을 이용함으로써 좀 더 작업이 간편해지는 몇 안되는 예제 중 하나라서 올려봅니다.

앞으로 11gR2가 본격적으로 사용되면 활용 예제가 더 많이 소개될 것으로 기대합니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

참고 : http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm


테이블에서 어느 조건을 만족하는 데이터(행)가 있다면 Update 없으면 Insert 하는 구문을 한번에 해결 할 수 있다.

Merge Into 사용전 방법 : 
    레코드 존재유무 조회(Select) -> 있음 -> Update
                                             -> 없음 -> Delete

샘플.

MERGE INTO bonuses D    USING (SELECT employee_id, salary, department_id FROM employees    WHERE department_id = 80) S    ON (D.employee_id = S.employee_id)    WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01      DELETE WHERE (S.salary > 8000)    WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)      VALUES (S.employee_id, S.salary*.01)      WHERE (S.salary <= 8000);

MERGE INTO 테이블(뷰) : 대상 테이블(뷰). Update, Delete, Insert 를 하게 될 테이블(뷰)
USING 테이블(뷰/서브쿼리) : 비교 테이블(뷰/서브쿼리).
ON : 비교 조건
WHEN MATCHED THEN UPDATE [DELETE] : 비교조건에 만족하는 레코드가 있을 경우 해당 레코드에 대해 실행
     10G부터는 DELETE도 포함되었으며 UPDATE문 없이 단독으로 사용하지 못한다.
WHEN NOT MATCHED THEN INSERT : 비교조건에 만족하지 않았을 경우 실행