2013年11月18日 星期一

MERGE INTO

MERGE INTO命令是Oracle9i /SQL SERVER 2008開始提供的新語法,用以合併UPDATEINSERT命令,UPSERT功能,當資料表中無此資料時(即無法匹配)可執行INSERT,反之則執行UPDATEDELETE10g以上功能)。語法如下:
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN
<update_clause>
WHEN NOT MATCHED THEN
<insert_clause>
MERGE INTO指令在MATCHED情況下可以進行資料異動(UPDATE),可利用此項特性於UPDATE WITH JOIN上, MSSQL可在UPDATE語法中使用FROM子句,因此撰寫UPDATE WITH JOIN幾乎與SELECT語法類似,ORACLE則否,UPDATE WITH JOIN語法上並不直覺問題,如對UPDATE WITH JOIN不熟悉則建議以MERGE INTO代替。測試資料如下,依資料來源是否為資料表分為2個測試案例,如下:

EMP 








DEPT 








測試1: (UPSERT)
增加韓大夫(新增)資料,並將邁爵士調薪為60,000(異動)。由於參考資料並非源自其他資料表時(如輸入),則可使用查詢語法以產生衍生資料表(Derived Table)方式,如下:

SQL:
MERGE INTO #EMP U
USING
      (
      SELECT 'A05' EMP_NO, '韓大夫' EMP_NAME, 80000 SALARY, 'IT' DETP_NO
      --FROM DUAL
      UNION ALL
      SELECT 'A03' EMP_NO, '邁爵士' EMP_NAME, 60000 SALARY, 'MA' DETP_NO
      --FROM DUAL
      ) S
      ON (
         U.EMP_NO = S.EMP_NO
         )
WHEN MATCHED THEN      --資料[存在]-執行UPDATEA03已存在,薪水異動。
  UPDATE
     SET U.SALARY = S.SALARY          
WHEN NOT MATCHED THEN  --資料[不存在]-執行INSERTEA05不存在,執行新增。
INSERT (EMP_NO, EMP_NAME, SALARY, DETP_NO) 
         VALUES (S.EMP_NO, S.EMP_NAME, S.SALARY, S.DETP_NO) ;










測試2: (UPDATE With Join)
將資訊部所有人員調薪20%(異動)SQL及執行結果如下:
MERGE INTO #EMP U
USING
      (
      SELECT DETP_NO
      FROM #DEPT_H
      WHERE 1=1
            AND DETP_NO ='IT'
      ) S
      ON (
         U.DETP_NO = S.DETP_NO
         )
WHEN MATCHED THEN --資料存在-執行UPDATE
  UPDATE
     SET U.SALARY =  U.SALARY * 1.2;









測試2之語法ORACLE 9i將發生錯誤,9iMATCHED / NOT MATCHED兩部分必須同時存在才可,因此在來源與目標連結中,須進資料限縮以促使僅可符合MATHCD部分;而另外NOT MATHCH之必要語法中,則刻意INSERT完全不合理之資料值即可,可自行嘗試。

2013年11月11日 星期一

[ORACLE] LISTAGG函數之應用

ORACLE 11g R2提供一組新的分析函數/彙總函數,可將同一群組內之字串串連之函數類似MySQL所提供的GROUP_CONCAT函數功能之LISTAGG』,於彙總運算中使用,如依照部門將其所屬員工姓名串連起來LISTAGG函數語法如下:
LISTAGG(expr [, 'delimiter'])
  WITHIN GROUP (order_by_clause) [OVER partition_by_clause]
expr:用於字串連結之欄位(Column)或運算(expression)(必要參數)。
delimiter:字串連結之分隔符號(選擇參數)。
order_by_clause:字串連結之順序(必要參數)。

使用時語法/應用時請注意以下幾點:
ü   必須於彙總運算
否則將出現ORA-00937:不是單一群組的群組函數。不論是single-set aggregategroup-set aggregate形式均可。
ü   須有WITH GROUP關鍵字
ü   須有組內ORDEER BY子句

將建立2010-01-01~2013-12-31日期資料為測試資料,SQL如下:
CREATE TABLE Calendar
AS
SELECT TO_CHAR(DATE'2010-01-01' + (LEVEL-1), 'YYYY-MM-DD') DT
FROM DUAL
CONNECT BY DATE'2010-01-01' + (LEVEL-1) < DATE'2014-01-01'

將利用前述所產生之日曆資料,以[年月]及[年]建立群組下之日期字串,SQL及結果如下所示。就以[年月]群組可順利執行,資料為該月份之01~31日期字串;[年]的部分,由於是將整年度自01-0112-31日期之日期連結,字串長度過長將出現ORA-01489之錯誤,可改用xmlagg函數,但傳回結果型態為CLOB

SQL
執行結果
SELECT SUBSTR(DT, 1, 7) YrMs
  , LISTAGG(DT, ',')
      WITHIN GROUP (ORDER BY DT) AS DT_LIST
FROM   Calendar
GROUP BY SUBSTR(DT, 1, 7)

SELECT SUBSTR(DT, 1, 4) Yr
  , LISTAGG(DT, ',')
       WITHIN GROUP (ORDER BY DT) AS DT_LIST
FROM   Calendar
GROUP BY SUBSTR(DT, 1, 4)
ORA-01489: result of string concatenation is too long

xmlagg函數應用
以下為應用xmlagg函數之SQL及結果如下:
SELECT SUBSTR(DT, 1, 4) Yr
 , TRIM(TRAILING ',' FROM
xmlserialize(content xmlagg(xmlelement(c, DT || ',') ORDER BY DT).extract('//text()') as clob)
    ) as DT_LIST
FROM  Calendar
GROUP BY  SUBSTR(DT, 1, 4)

執行結果:








CLOB結果:





















請特別注意xmlagg函數使用時,需額外針對日期進行排序,否則日期排列非如預期。

對於群組字串連結,ORACLE 11g R2可用LISTAGG函數、10g則可使用未公佈的指令wmsys.wm_concat,而9i以上可以用User-Defined Aggreagte Function或使用xmlagg函數,xmlagg之運算結果為CLOB型態,可使用TO_CHAR轉換為varchar2,即可提供一般所使用,如下。
SELECT SUBSTR(DT, 1, 7) YrMS
 , TO_CHAR(TRIM(TRAILING ',' FROM
   xmlserialize(content xmlagg(xmlelement(c, DT || ',') ORDER BY DT).extract('//text()') as clob))
        ) as DT_LIST
FROM  Calendar
GROUP BY  SUBSTR(DT, 1, 7)

2013年10月29日 星期二

取得具有相同數字之數值(2)

本範例將查詢具有相同數字之數值(如:11122888等),此範例將利用2組數值數列產生111111222….之數列,以 Table Join方式進行篩選,即可將符合相同數字之數值保留。SQL及結果如下:

SQL
MSSQL
WITH Filter
AS
(
--產生, 11, 111, 1111...數列
SELECT N.number
       , REPLICATE(CAST(N.number AS VARCHAR), D.number) Val_C
       , CAST(REPLICATE(CAST(N.number AS VARCHAR), D.number) AS BIGINT) Val
FROM master.dbo.spt_values N  -- 產生1~9數值數列
, master.dbo.spt_values D  -- 產生1~D(位數)數列
, (--重點.以取得最大數值,以為底取LOG即可得最大數值之位數(ex: 222為位數)
        SELECT CEILING(LOG10(MAX(Val))) Digits   -- 3. LOG值進位
                , LOG10(MAX(Val)) "Digits(LOG10)"   -- 2.最大數值以10為底取LOG
              , MAX(Val)          MaxVal           -- 1.取得最大數值
          FROM #Data
        ) U
WHERE 1=1
       AND N.name IS NULL
       AND D.name IS NULL
       AND N.number BETWEEN 1 AND 9        -- 產生~9數值數列
       AND D.number BETWEEN 1 AND U.Digits -- 產生~D(位數)數列
--ORDER BY N.number, D.number
)
------------
SELECT D.*
FROM #Data D, Filter F
WHERE D.Val = F.Val

: 重點在於虛線位置,以產生111111222….
ORACLE
WITH Filter
AS
(
--產生, 11, 111, 1111...數列
SELECT N.NUM
       , D.Digits
       , RPAD(N.NUM , D.Digits , N.NUM) VAL
FROM
    (
    SELECT LEVEL NUM
    FROM DUAL
    CONNECT BY LEVEL <=9
     ) N,
     (
    SELECT LEVEL Digits
    FROM DUAL,
         (
         SELECT CEIL(LOG(10, MAX(Val))) Digits
             , MAX(Val) MaxVal
         FROM TEST
         )
    CONNECT BY LEVEL <= Digits
     ) D
)
SELECT D.*
FROM TEST D, Filter F
WHERE D.Val = F.Val

執行結果如下:
             











SQL /說明
說明
MSSSQL
--產生, 11, 111, 1111...數列
SELECT N.number
,REPLICATE(CAST(N.number AS VARCHAR), D.number) Val_C
,CAST(REPLICATE(CAST(N.number AS VARCHAR), D.number) AS INT) Val
FROM master.dbo.spt_values N  -- 產生1~9數值數列
, master.dbo.spt_values D  -- 產生1~D(位數)數列
, (--重點.以取得最大數值,以為底取LOG即可得最大數值之位數(ex: 222為位數)
 SELECT CEILING(LOG10(MAX(Val))) Digits -- 3. LOG值進位
    , LOG10(MAX(Val)) "Digits(LOG10)"   -- 2.最大數值以10為底取LOG
       , MAX(Val)          MaxVal            -- 1.取得最大數值
    FROM #Data
    ) U
WHERE 1=1
       AND N.name IS NULL
       AND D.name IS NULL
       AND N.number BETWEEN 1 AND 9        -- 產生~9數值數列
       AND D.number BETWEEN 1 AND U.Digits -- 產生~D(位數)數列
--ORDER BY N.number, D.number
ü   利用2組數值數列產生111111222…
ü   1組數列為1~9;第2組為1~{最大位數}
ü   最大位數是以
- 取得最大數值
- 最大數值取LOG10
- 再以CEILING函數無條件進位,即為最大位數。
ü   REPLICATE函數為將字串值重複指定的次數。以產生重複數字數值字串。
ü   數值字串轉型為數字。

ORACLE
--產生, 11, 111, 1111...數列
SELECT N.NUM
       , D.Digits
       , RPAD(N.NUM , D.Digits , N.NUM) VAL
FROM
    (
    SELECT LEVEL NUM
    FROM DUAL
    CONNECT BY LEVEL <=9
     ) N,
     (
    SELECT LEVEL Digits
    FROM DUAL,
         (
         SELECT CEIL(LOG(10, MAX(Val))) Digits
             , MAX(Val) MaxVal
         FROM TEST
         )
    CONNECT BY LEVEL <= Digits
     ) D
ü   ORACLEREPLICATE函數,可使用RPAD函數替代。
ü   其餘概念與MSSQL同,略。
此方法虛線部分執行結果如下(9*3 =27筆):