# [SQL] Oracle
[TOC]
## :triangular_flag_on_post: 獲取日期 trunc (time, format)
假設現在時間是2018-11-28 11:11:11
| 需求 | sql語法 | 輸出 |
|-|-|-|
|當月第一天|select trunc(sysdate, ‘mm’) from dual|2018-11-1
|當年第一天|select trunc(sysdate,‘yy’) from dual|2018-1-1
|當前年月日|select trunc(sysdate,‘dd’) from dual|2018-11-28
|當年第一天|select trunc(sysdate,‘yyyy’) from dual|2018-1-1
|當前星期的第一天(也就是星期天)|select trunc(sysdate,‘d’) from dual|2018-11-28
|當前日期|select trunc(sysdate) from dual|2018-11-28
|當前時間(準確到小時)|select trunc(sysdate, ‘hh’) from dual|2018-11-28 11:00:00
|當前時間(準確到分鐘)|select to_char(trunc(sysdate, ‘mi’),‘yyyy-MM-dd HH:mm:ss’) from dual|2018-11-28 11:11:00
|前一天的日期|select trunc(sysdate - 1) from dual|2018-11-27
|前一個月的日期|select add_months(trunc(sysdate),-1) from dual|2018-10-28
|後一個月的日期|select add_months(trunc(sysdate),1) from dual|2018-12-28
|本月最後一天|select to_char(last_day(sysdate), ‘yyyy-mm-dd’) from dual|2018-11-30
## :triangular_flag_on_post: 若有null...
### NVL
Oracle 獨有的函數，最多接受兩個參數。
* 狀況一
expr1 為 NULL, 返回 expr2; 不為 NULL, 返回 expr1。**注意！二者的類型要一致**
```=sql
select
NVL(expr1, expr2) as column_name_b
from table_name
```
* 狀況二
expr1 不為 NULL, 返回expr2; 為 NULL, 返回 expr3。
expr2 和 expr3 類型不同的話, expr3 會轉換為 expr2 的類型。
```=sql
select
NVL2(expr1, expr2, expr3) as column_name_a,
NVL2(expr1, 'yes', 'no') as column_name_b
from table_name
```
* 狀況三
相等返回 NULL，不相等返回 expr1。
```=sql
select
NULLIF(expr1, expr2) as column_name
from table_name
```
### COALESCE
標準的sql函數，可接受一個或多個參數，最終返回第一個非null的值，若都是null則返回null。
```=sql
select
coalesce(expr1, expr2, 'default') as column_name
from table_name
```
## :triangular_flag_on_post: 排序時對 Null 值的處理
在 Oracle 預設的處理上，null 會被當成最大值。
> **ASC** 時，null 會排在**最後面**
> **DESC** 時，null 會排在**最前面**
當在排序時有需要對 null 做不同的排序。Oracle 有針對此情況支援 order by 相關的語法，如下：
- **nulls first** - 將 null 值排在最前面
```=sql
SELECT * FROM <TableName> ORDER BY id NULLS FIRST;
```
- **nulls last** - 將 null 值排在最後面
```=sql
SELECT * FROM <TableName> ORDER BY id NULLS LAST;
```
## :triangular_flag_on_post: 使用正則表達式
### 函式：REGEXP_LIKE
尋找符合的字串
>REGEXP_LIKE(string, pattern [,match_option])
>REGEXP_LIKE(<搜尋值>, <正則表達式> [,<>])
範例：
查詢某欄位是否包含連結的字串
```=sql!
select *
from table_name
where regexp_like(column_name, '(http|https|ftp)://[a-zA-Z0-9./?#=_-]+/')
```
### 函式：REGEXP_INSTR
尋找符合的字串並回傳字串位置
>REGEXP_INSTR(<搜尋值>, <正則表達式> [, <搜尋的起始位置> [, occurrence [, return_option [, match_option]]]])
範例：
要搜尋這些產品名稱以找到第一個非字母字元（無論它是大寫還是小寫）的位置
```=sql!
COLUMN product_name FORMAT a45
COLUMN non_alpha FORMAT 9999999999
SELECT product_name, REGEXP_INSTR(product_name, '[^[:alpha:]]') non_alpha
FROM oe.product_information
```
補充：
* `[^[:<class>:]]` 表示一個字元，並匹配任何字元
* `[:alpha:]` 匹配任何字母字元
* `^` 對該表達式取非
### 函式：REGEXP_REPLACE
尋找符合的字串並取代
>REGEXP_REPLACE(srcstr, pattern [,replacestr [, position [, occurrence [, match_option]]]])
範例：
查詢符合網址的字串並取代
```=sql!
SELECT UNIQUE REGEXP_REPLACE (catalog_url, 'http://([^/]+).*', '\1')
FROM oe.product_information ;
```
補充：
* `http://` 無特殊字元
* `([^/]+)` 不包含`/`的所有字元
* `.*`
* `\1`
### 函式：REGEXP_SUBSTR
尋找符合的字串並回傳字串
>REGEXP_SUBSTR(srcstr, pattern [, position [, occurrence [, match_option]]])
範例：
僅搜尋包含「SWITZERLAND」字串的電子郵件，並取得域名
```
SELECT REGEXP_SUBSTR(cust_email, '[^@]+')
FROM oe.customers
WHERE nls_territory = 'SWITZERLAND' ;
```
參考資料：
[在 Oracle 数据库 10g 中使用正则表达式](https://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/228911_zhs.htm)
## 分析函數
在查詢結果內進行分析和計算的函數，與聚合函數(sum、avg、count)不同。分析函數可以不破壞查詢結果來進行分組、排序和計算，用於查詢的欄位(select語句)
```=sql
函數(參數) over(
partition by 子句1, 子句2, ...
[order by 子句1, 子句2, ... [asc|desc] [nulls first|nulls last]]
[windowing 子句]
)
```
* 函數
用在統計，如max、min、avg
* partition by
為查詢結果分組，類似group by
* order by
可略，為查詢結果排序
* windowing
可略，
### 常見的分析函數
#### ROW_NUMBER()
賦予查詢結果集分配連續的編號
#### RANK()
賦予查詢結果集排名的數字
#### DENSE_RANK()
還沒用過不清楚
#### SUM()、AVG()、COUNT()
聚合函數也可以用在分析函數
參考資料：
[SQL Fundamentals: 子查询 || 分析函数(PARTITION BY,ORDER BY, WINDOWING)](https://www.cnblogs.com/thescentedpath/p/analysisfunction.html)
## 階層式查詢 start with ... connect by prior ...
```=sql!
SELECT
type_id,
type_name,
SYS_CONNECT_BY_PATH(type_name, '/') as path,
Level
FROM table_name
START WITH type_parent_id = 2
CONNECT BY PRIOR type_id = type_parent_id
```
* `START WITH [起始點]`
* `CONNECT BY PRIOR [column1 = column2]` 表示將 column1 = column2 的資料列視為父資料列
* `SYS_CONNECT_BY_PATH(columnName, '/')` 用於串接欄位字串，會依階層( level )上至下(即 Parent > Child > GrandChild )串接該欄位。
參考資料：
[Oracle SQL: Hierarchical queries / START WITH … CONNECT BY](https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/select/hierarchical-queries/start-with_connect-by/index)
[Oracle官方文件 - Hierarchical Queries](https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm)
[ORACLE筆記-使用 CONNECT BY 呈現階層化資料](https://blog.darkthread.net/blog/oracle-connect-by/)
Sign in with Wallet
Connect another wallet