Oracle Single-Row Functions Commands in SQL

Single-Row Function 的指令

Function: 將0個或多個參數並回傳單數值

Single function  v.s. Group function 

Single: 在從Table擷取資料之前，知道有多少參數需要處理

Group: 直到資料已經被截取和組成category後，才知道有多少參數需要處理

SINGLE FUNTION 的使用位置示意

SELECT ename, TO_CHAR(hiredate,’Day, DD-Mon-YYYY’)

FROM emp

WHERE UPPER(ename) LIKE ‘AL%’

ORDER BY SOUNDEX(ename)

INSTR (原字串,欲找字串,起始位置,第幾個符合)  

意義: 從string1中第n個字元開始尋找第m次遇到string2的位置。 

SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) FROM DUAL

                                                           ↑               ↑從右邊第一個開始找起

                                                           14  (position仍是左到右開始數)

Result: 14

LENGTH(String1)

意義: 計算String1的長度。

SELECT LENGTH('Ariel Shao') FROM DUAL  //空白也算是一個字元

Result: 10

 SUBSTRB(String1,integeri[,intergerj])

SELECT SUBSTRB('Ariel',2,4) FROM DUAL;

Result: riel ?????

SELECT SUBSTRB('Ariel',1,4) FROM DUAL;

Result: Arie

SELECT SUBSTRB('Ariel',2) FROM DUAL;

Result: riel

SELECT SUBSTRB('Ariel',2,-1) FROM DUAL;   // integerj為負數則回傳空白

Result:

SELECT SUBSTRB('Ariel',-3,2) FROM DUAL;  //從右數第3個(i)開始數兩個數(ie)

Result:ie

TRANSLATE(String1, 要被抽換的字元, 更改的字元)

SELECT TRANSLATE('MyexgirlfriendisArielShao','Ariel','Peggy') FROM DUAL; // A換成P、r換成e

Result:  MygxggeyfeggndgsPeggyShao

RTRIM(string[,set]) // 從字串右邊去除所有set字元，set預設空白

SELECT RTRIM('Mississippi','ip') FROM DUAL;
Result: Mississpi

CASE 條件判斷  有兩種寫法，一種適合某欄位"單值比對"，一種適合"條件比對"

http://mark-freefox.blogspot.tw/2011/08/oracle_12.html



SELECT

http://webdesign.kerthis.com/sql/sql_case

【DECODE(條件,值1,回傳值1,值2,回傳值2,...值n,回傳值n,缺省值)】//就是if..else if..else


該函數的含義如下：
IF 條件=值1 THEN
　　　　RETURN(翻譯值1)
ELSIF 條件=值2 THEN
　　　　RETURN(翻譯值2)
　　　　......
ELSIF 條件=值n THEN
　　　　RETURN(翻譯值n)

ELSE
　　　　RETURN(缺省值)
END IF

e.g.
SELECT empno,ename,salary,grade
FROM emp e,grades g
WHERE e.salary BETWEEN LOW_SALARY AND DECODE(g.grade,'PI','999999',g.high_salary)

【EXISTS】 用來測試內查詢subquery有無產生結果。有則執行外查詢SQL; 無，整個SQL都不會產生結果

語法:
SELECT 欄位1,欄位2
FROM   表格名
WHERE EXISTS (SELECT * FROM 表格名2 WHERE 條件句)

NOT EXISTS 子查詢判斷為False才會執行外查詢

注意:內查詢若產生了超過一筆的資料，則 EXISTS 的條件成立，所以外查詢會被執行，內查詢跟外查詢基本上是獨立關係，子查詢結果不會成為外查詢的Input。

【Special Characters】

'%' 代替任何自元
'_' 代替一個字元

'\' ESCAPE 跳脫字元，使跟在後面的保留字成為一般字元，ex:  LIKE 'A\_%' →  A_....  ex: 'LIKE 'A\_\%'→  A_%
'desc' 跟在某個column後面，能夠使排序呈現遞減狀態
'||'

【Group Functions】

1.COUNT()/SUM()/MIN()/MAX()/AVG()/STDDEV()/VARIANCE()

2.常搭配使用GROUP BY群組資料

3.限制資料使用HAVING取代WHERE(HAVING有包含Aggregate function)

4.注意子句順序(1.WHERE 2.GROUP 3.HAVING)

直到query執行時和值都截取後才知道inputs數量的function叫做Group Function

注意: 不能處理NULL 值，不能使用在WHERE函數

不可以針對Group Data使用Programmer-written functions 

使用地點: SELECT or HAVING (因為Group function不能搭配WHERE使用 ex: SUM()、AVG()..)，HAVING 子句是用來取代 WHERE 搭配聚合函數 (Aggregate Function) 進行"條件查詢"

AVG(DISTINCT|ALL <number>)

COUNT(* )  傳回整個table所有row的數量(部分資料包含NULL也算)
COUNT(Field) 注意含有NULL值的Field則不會被計算進去

【GROUP BY】-通常搭配Group Function使用,例如依照每個銷售員的"銷售總額"，以銷售員做為分類基準

1.GROUP BY不可以取代別名(Alias name)
.如SELECT中有一欄非聚合函數必用Group By   ex: SELECT state,count(*)

3.未搭配聚合函數【SUM() AVG() MAX() MIN()】時，GROUP BY子句後面必須包含所有的欄位，否則有問題

ORDER BY搭配聚合函數

SELECT zip,count(*)
 FROM  AMS_2G_ONAIR_SITE_BY_ZIP
GROUP BY zip 
ORDER BY COUNT(*) DESC;

【Limiting Grouped Data with HAVING(HAVING)-HAVING代替WHERE搭配Aggregate Function進行條件查詢，過濾資料，WHERE無法搭配聚合函數】

注意語法的搭配順序
1.HAVING 其實是對 aggregate method 之結果再做條件篩選， aggregate method 可搭配2.GROUP BY 使用; HAVING 也可以出現在沒有 GROUP BY 的查詢中

範例:
想要查詢每位銷售員的平均銷售，並且屬於outside sales department，並只

回傳銷售超過100,000的銷售員(HAVING):

SELECT sales_clerk, SUM(sale_amount)

FROM gross_sales

1.WHERE sales_dept='OUTSIDE'

2.GROUP BY sales_clerk

3.HAVING SUM(sale_amount)>10000

特別注意，HAVING跟WHERE可以同時出現，注意Grouping前就要先以WHERE篩選,HAVING

要在GROUPING之後

【Nesting Function】

槽狀的 Function可以使得內部Function的Output成為外部Function的Input

SELECT ,deptno 部門號碼, MAX(COUNT(DISTINCT job))  //觀念，MAX()會交代出一個數字，不要管裡面會有幾筆數字 

FROM emp

GROUP BY deptno;