SQL QnA

2014年5月4日星期日

[MSSQL] 取出局部字串-利用ParseName函數

本範例將介紹利用PARSENAME函數可擷取sysname物件中部分物件名稱之特性(四段式命名)，用以取得特定格式字串之子字串。
以下應用於擷取電腦IP網址第3段子網段為例，先以常用SUBSTRING取得並列SQL及結果如下：

SQL	執行結果
SELECT IP , SUBSTRING(IP, 9, CHARINDEX('.', IP, 9)-9) "Sub" , CHARINDEX('.', IP, 9)-9 "Len" FROM (VALUES ('192.168.10.1') , ('192.168.1.32') , ('192.168.2.23') , ('192.168.1.15') , ('192.168.2.12') ) AS IPs (IP)

再來嘗試以PARSENAME達成前述相同目的，PARSENAME函數可擷取sysname物件的物件名稱（Object name）、擁有者名稱（Schema name）、資料庫名稱（Database name）及伺服器名稱（Server name）等四個部分，PARSENAME 函數並不會指出指定名稱的物件是否存在，只會傳回指定物件名稱的指定部份，函數語法如下 :

PARSENAME ( 'object_name' , object_piece )

object_name:擷取指定物件部分的物件名稱。

object_name 是 sysname，此參數是一個選擇性限定的物件名稱，物件名稱應包含（非絕對）四個部分：伺服器名稱、資料庫名稱、擁有者名稱和物件名稱。

object_piece: 指定傳回的物件部分。 object_piece 的類型是 int，值及所取得之物件說明如下：

1 = 物件名稱

2 = 結構描述名稱

3 = 資料庫名稱

4 = 伺服器名稱

以下利用PARSENAME函數取得master.dbo.spt_values四段之部分物件，前逑sysname字串中只含有三段，未包含伺服器名稱（Server name），語法中取得第4部分之傳回值為NULL，由函數定義或回傳結果可得知，函數之物件擷取是由小（1 : 物件名稱）而大（4：伺服器名稱），也代表字串由右至左 (\)進行擷取，即物件字串不足時，則自動捨去並回傳NULL。

資料庫物件	PARSENAME函數用法/結果
	SELECT PARSENAME('master.dbo.spt_values', 1) , PARSENAME('master.dbo.spt_values', 2) , PARSENAME('master.dbo.spt_values', 3) , PARSENAME('master.dbo.spt_values', 4)

以下將應用於截取第3、4段子網段，第3段子網段是代表由右向左計數之第2部分，第4段子網段代表右側之第1部分，SQL及結果如下:

SQL	執行結果
SELECT * , PARSENAME(IP, 2) "Sec3 (P:2)" , PARSENAME(IP, 1) "Sec4 (P:1)" FROM (VALUES ('192.168.10.1') , ('192.168.1.32') , ('192.168.2.23') , ('192.168.1.15') ) AS IPs (IP) ORDER BY CAST(PARSENAME(IP, 2) AS INT) , CAST(PARSENAME(IP, 1) AS INT)

以下範例將分析PARSENAME函數特性分析，SQL及結果如下:

SQL	執行結果
SELECT * , PARSENAME(Val, 1) "-1" , PARSENAME(Val, 2)　"-2" , PARSENAME(Val, 3)　"-3" , PARSENAME(Val, 4)　"-4" FROM (VALUES ('A1.A2.A3.A4') , ('A1-A2-A3-A4') , ('B1.B2.B3') , ('C1.C2.C3.C4.C5') ) AS Test(Val) WHERE 1=1

由以上結果可得以下結論

1.小數點（.）為分隔符號

2.由右向左取得子字串

3. 最多取得個4個子段結果

ü 超過4段

傳回一律為NULL值

ü 小於4段

由右至左開始擷取，不足則回傳NULL。

以下使用PARSENAME查詢電話號碼為行動電話之資料，如下：

SELECT *

, '0' + PARSENAME(REPLACE(Phone, '-', '.'), 3)

+ STUFF(PARSENAME(REPLACE(Phone, '-', '.'), 2), 3, 0, '-')

+ STUFF(PARSENAME(REPLACE(Phone, '-', '.'), 1), 2, 0, '-')

FROM (VALUES('886-2-2222-0001')

, ('+886-9-1234-0002')

, ('886-9-1234-0003')

) AS Phones(Phone)

WHERE 1=1

AND PARSENAME(REPLACE(Phone, '-', '.'), 3) = '9'

2013年11月18日星期一

MERGE INTO

MERGE INTO命令是Oracle9i /SQL SERVER 2008開始提供的新語法，用以合併UPDATE及INSERT命令，即UPSERT功能，當資料表中無此資料時（即無法匹配）可執行INSERT，反之則執行UPDATE或DELETE（10g以上功能）。語法如下:

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 --資料[存在]-執行UPDATE；A03已存在，薪水異動。

UPDATE

SET U.SALARY = S.SALARY

WHEN NOT MATCHED THEN --資料[不存在]-執行INSERTE；A05不存在，執行新增。

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將發生錯誤，9i中MATCHED / 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 aggregate或group-set aggregate形式均可。

ü 須有WITH GROUP關鍵字

ü 須有組內ORDEER BY子句

將建立2010-01-01~2013-12-31日期資料為測試資料，SQL如下：

CREATE TABLE Calendar

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-01至12-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)

2014年5月4日 星期日