Oracle 笔记
2020-04-03 01:57:05
### 查 Oracle 版本
SQL 脚本及结果：
```
SELECT * FROM V$VERSION;
```
| BANNER |
| :------ |
| Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production |
| PL/SQL Release 11.2.0.4.0 - Production |
| CORE 11.2.0.4.0 Production |
| TNS for Linux: Version 11.2.0.4.0 - Production |
| NLSRTL Version 11.2.0.4.0 - Production |
### LISTAGG()
Oracle的行转列函数：LISTAGG()
在`Oracle Database 11g Release 2 (11.2)`版本中推出了此函数
基础的用法：
`LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)`
普通 SQL 脚本及执行效果：
```
WITH TEMP AS (
SELECT '8888' AS GID,'0103' AS CODE FROM DUAL UNION ALL
SELECT '8888' AS GID,'0101' AS CODE FROM DUAL UNION ALL
SELECT '8888' AS GID,'0102' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0201' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0202' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0203' AS CODE FROM DUAL
)
SELECT GID,CODE FROM TEMP;
```
| GID | CODE |
| --- | --- |
| 8888 | 0103 |
| 8888 | 0101 |
| 8888 | 0102 |
| 6666 | 0201 |
| 6666 | 0202 |
| 6666 | 0203 |
使用 LISTAGG 的 SQL 脚本及执行结果：
```
WITH TEMP AS (
SELECT '8888' AS GID,'0103' AS CODE FROM DUAL UNION ALL
SELECT '8888' AS GID,'0101' AS CODE FROM DUAL UNION ALL
SELECT '8888' AS GID,'0102' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0201' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0202' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0203' AS CODE FROM DUAL
)
SELECT
GID,
LISTAGG(CODE,',') WITHIN GROUP(ORDER BY CODE) AS CODES
FROM TEMP
GROUP BY GID;
```
| GID | CODES |
| --- | --- |
| 6666 | 0201,0202,0203 |
| 8888 | 0101,0102,0103 |
### WM_CONCAT()
Oracle 行转列函数 `WM_CONCAT()`
此函数为未对外公开的内部函数，建议使用 `LISTAGG()` 代替
脚本示例及结果：
```
WITH TEMP AS (
SELECT '8888' AS GID,'0103' AS CODE FROM DUAL UNION ALL
SELECT '8888' AS GID,'0101' AS CODE FROM DUAL UNION ALL
SELECT '8888' AS GID,'0102' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0201' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0202' AS CODE FROM DUAL UNION ALL
SELECT '6666' AS GID,'0203' AS CODE FROM DUAL
)
SELECT
GID,
WM_CONCAT(CODE) AS CODES
FROM TEMP
GROUP BY GID;
```
| GID| CODES |
| --- | --- |
| 6666 | 0201,0203,0202 |
| 8888 | 0103,0102,0101 |
### DECODE()
DECODE 是 Oracle 公司独家提供的一个功能强大的函数， 在它的语法中，类似 If – Then – Else 进行逻辑判断。
语法如下：
`DECODE( value, if1, then1, if2, then2, if3, then3, . . . else )`
value 代表某个表的任意列或一个计算表达式
如果 value 的值为 if1 时 Decode 函数的结果为 then1
如果 value 的值为 if2 时 Decode 函数的结果为 then2 等等
可以给出多对 if / then 。如果 value 结果不等于给出的任何配对时，Decode 结果就返回 else
需要注意的是，这里的 if、then 及 else 都可以是函数或计算表达式。
SQL 脚本及结果：
```
WITH TEMP AS (
SELECT '1' AS GID,'0101' AS CODE FROM DUAL UNION ALL
SELECT '2' AS GID,'0102' AS CODE FROM DUAL UNION ALL
SELECT '3' AS GID,'0103' AS CODE FROM DUAL
)
SELECT CODE,DECODE( CODE,0101,'ID=1',0102,'ID=2','ID=Other' ) AS IDS FROM TEMP;
```
| CODE | IDS |
| --- | --- |
| 0101 | ID=1 |
| 0102 | ID=2 |
| 0103 | ID=Other |
### NVL / NVL2()
NVL 是 Oracle PL/SQL 中的一个函数。它的格式为 `NVL( str, replace )`。 如果 str 为 NULL，则 NVL 函数返回 replace，否则返回str，如果两个参数都为 NULL，则返回 NULL。
Oracle 在 NVL 函数的功能上扩展，提供了 NVL2 函数。格式为 `NVL2( E1, E2, E3 )`。 如果 E1 为 NULL，则函数返回 E3，否则返回 E2。
NVL() 函数的 SQL 脚本及结果：
```
WITH TEMP AS (
SELECT '1' AS GID,'0101' AS CODE FROM DUAL UNION ALL
SELECT '2' AS GID,'0102' AS CODE FROM DUAL UNION ALL
SELECT '3' AS GID,'' AS CODE FROM DUAL
)
SELECT GID,NVL( CODE,'IsNull' ) AS IDS FROM TEMP;
```
| GID | IDS |
| --- | --- |
| 1 | 0101 |
| 2 | 0102 |
| 3 | IsNull |
NVL2() 函数的 SQL 脚本及结果：
```
WITH TEMP AS (
SELECT '1' AS GID,'0101' AS CODE FROM DUAL UNION ALL
SELECT '2' AS GID,'0102' AS CODE FROM DUAL UNION ALL
SELECT '3' AS GID,'' AS CODE FROM DUAL
)
SELECT GID,NVL2( CODE,'NotNull','IsNull' ) AS IDS FROM TEMP;
```
| GID | IDS |
| --- | --- |
| 1 | NotNull |
| 2 | NotNull |
| 3 | IsNull |
### START WITH...
START WITH…CONNECT BY 常用于结构化查询中
基本语法 `SELECT * FROM TEMP START WITH 条件1 CONNECT BY 条件2;`
条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
条件2 是连接条件，其中用 PRIOR 表示上一条记录，比如 `CONNECT BY PRIOR GID = PARENT_GID`；就是说上一条记录的 GID 是本条记录的 PARENT_GID，即本记录的父亲是上一条记录。
使用 LEVEL：
此伪列表示节点深度，由于节点所处的层次位置不同，每行记录都有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为 1，根节点的子节点为 2，依此类推。
SQL脚本及结果：
```
WITH TEMP AS (
SELECT '01' AS GID,'' AS PID FROM DUAL UNION ALL
SELECT '02' AS GID,'01' AS PID FROM DUAL UNION ALL
SELECT '03' AS GID,'01' AS PID FROM DUAL UNION ALL
SELECT '04' AS GID,'02' AS PID FROM DUAL UNION ALL
SELECT '05' AS GID,'03' AS PID FROM DUAL UNION ALL
SELECT '06' AS GID,'05' AS PID FROM DUAL
)
SELECT GID, PID, LEVEL FROM TEMP START WITH PID IS NULL CONNECT BY PRIOR GID=PID;
```
| GID | PID | LEVEL |
| --- | --- | --- |
| 01 | (null) | 1 |
| 02 | 01 | 2 |
| 04 | 02 | 3 |
| 03 | 01 | 2 |
| 05 | 03 | 3 |
| 06 | 05 | 4 |
使用 `CONNECT_BY_ROOT()`：
返回当前节点的最顶端节点
使用 `CONNECT_BY_ISLEAF`：
判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点。返回 1 或 0 ，1 为叶子节点。
SQL 脚本及结果：
```
WITH TEMP AS (
SELECT '01' AS GID,'' AS PID FROM DUAL UNION ALL
SELECT '02' AS GID,'01' AS PID FROM DUAL UNION ALL
SELECT '03' AS GID,'01' AS PID FROM DUAL UNION ALL
SELECT '04' AS GID,'02' AS PID FROM DUAL UNION ALL
SELECT '05' AS GID,'03' AS PID FROM DUAL UNION ALL
SELECT '06' AS GID,'05' AS PID FROM DUAL
)
SELECT GID, PID, CONNECT_BY_ROOT(GID) AS ROOT_GID, CONNECT_BY_ISLEAF AS ISLEAF FROM TEMP START WITH PID='01' CONNECT BY PRIOR GID=PID;
```
| GID | PID | ROOT_GID | ISLEAF |
| --- | --- | --- | --- |
| 02 | 01 | 02 | 0 |
| 04 | 02 | 02 | 1 |
| 03 | 01 | 03 | 0 |
| 05 | 03 | 03 | 0 |
| 06 | 05 | 03 | 1 |
### CASE-WHEN
在 SQL 中判断类似非 A 即 B 的表达式时，可以用 CASE-WHEN 句式来判断。
CASE 子句可以用于任何表达式可以有效存在的地方，如可以使用在 SELECT、WHERE 及 GROUP BY 之后。
其语法为：
`CASE WHEN xx=xx THEN xx WHEN xx=xx THEN xx ELSE xx END`
`CASE xx WHEN xx THEN xx WHEN xx THEN xx ELSE xx END`
SQL 脚本：
```
-- 简单 CASE 函数
WITH TEMP AS (
SELECT '1' AS GID,'0101' AS CODE FROM DUAL UNION ALL
SELECT '2' AS GID,'0102' AS CODE FROM DUAL UNION ALL
SELECT '3' AS GID,'0103' AS CODE FROM DUAL
)
SELECT CODE, CASE WHEN CODE='0101' THEN 'ID=1' WHEN CODE='0102' THEN 'ID=2' ELSE 'ID=Other' END AS IDS FROM TEMP;
-- CASE 搜索函数
WITH TEMP AS (
SELECT '1' AS GID,'0101' AS CODE FROM DUAL UNION ALL
SELECT '2' AS GID,'0102' AS CODE FROM DUAL UNION ALL
SELECT '3' AS GID,'0103' AS CODE FROM DUAL
)
SELECT CODE, CASE CODE WHEN '0101' THEN 'ID=1' WHEN '0102' THEN 'ID=2' ELSE 'ID=Other' END AS IDS FROM TEMP;
```
执行结果：
| CODE | IDS |
| --- | --- |
| 0101 | ID=1 |
| 0102 | ID=2 |
| 0103 | ID=Other |
### INSTR()
可以使用 INSTR 函数对某个字符串进行判断其是否含有指定的字符，并返回被查找到的指定的字符的位置。
函数语法：
`instr(sourceString, destString, start, appearPosition)`
sourceString：为源字符串
destString：为目标字符串
start：为起始检索位置（参数可选，默认为 1 ，如为负数，则从右往左查找，但位置仍从左向右计算）
appearPosition：为第几次出现的位置（参数可选，默认为 1）
SQL 示例：
```
-- 返回结果为 3
SELECT INSTR('abcdeabcde','cd') FROM DUAL;
-- 返回结果为 8
SELECT INSTR('abcdeabcde','cd','1','2') FROM DUAL;
```
**INSTR 与 LIKE：**
下列用法效果相同
`INSTR(AUTHOR, 'TOM') > 0`
`AUTHOR LIKE '%TOM%'`
关于性能有种说法，提及 INSTR 会走索引，而 '%TOM%' 不会。
而通过执行计划观察，貌似并非如此，暂无大数据进行测试。`FIXME`
### SUBSTR()
从给定的字符表达式或字段中返回一个子字符串，其语法：
`SUBSTR(cExpression, nStartPosition, nCharactersReturned)`
cExpression：给定的字符串或表达式或字段
nStartPosition：指定子字符串的起始位置。如为负数，则从右往左计起始位置，字符串方向仍从左向右
nCharactersReturned：指定返回字符串的个数，为可选参数，默认返回从起始位置到末尾所有的字符
SQL 示例：
```
-- 返回结果为 cded
SELECT SUBSTR('abcdedcba',3,4) FROM DUAL;
-- 返回结果为 edcb
SELECT SUBSTR('abcdedcba',-5,4) FROM DUAL;
-- 返回结果为 ba
SELECT SUBSTR('abcdedcba',-2,3) FROM DUAL;
-- 返回结果为 cdedcba
SELECT SUBSTR('abcdedcba',3) FROM DUAL;
```
### REPLACE()
REPLACE 为字符串替换函数
语法：
`REPLACE(char, search_string, replacement_string)`
char：等待替换的字符串
search_string：需要替换的字符串（如果为 null，则直接返回 char）
replacement_string：替换字符串（可选参数，如不提供此参数或参数为 null，则移除所有 search_string）
SQL 示例：
```
-- 返回 "axye"
SELECT REPLACE('abcde','bcd','xy') AS CONTENT FROM DUAL;
-- 以下均返回 "ade"
SELECT REPLACE('abcde','bc','') AS CONTENT FROM DUAL;
SELECT REPLACE('abcde','bc',NULL) AS CONTENT FROM DUAL;
SELECT REPLACE('abcde','bc') AS CONTENT FROM DUAL;
```
### TRANSLATE()
语法：
`TRANSLATE(string, from_str, to_str)`
作用：
将 string 中所有在 from_str 中出现的字符都替换成 to_str 中对应位置的字符。
TRANSLATE 是 REPLACE 所提供的功能的一个超集。
如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中删除，因为它们没有相应的替换字符。to_str 不能为空。Oracle 将空字符串解释为 NULL，并且如果 TRANSLATE 中的任何参数为 NULL，那么结果也是 NULL。
SQL 示例：
```
-- 返回 "加1减2乘3除4完"
SELECT TRANSLATE('加+减-乘*除/完','+-*/','1234') AS CONTENT FROM DUAL;
-- 返回 "加1减2乘除完"
SELECT TRANSLATE('加+减-乘*除/完','+-*/','12') AS CONTENT FROM DUAL;
-- 返回 "加1减2乘*除/完"
SELECT TRANSLATE('加+减-乘*除/完','+-','1234') AS CONTENT FROM DUAL;
-- 返回 "(null)"
SELECT TRANSLATE('加+减-完','+-*/','') AS CONTENT FROM DUAL;
-- 返回 "(null)"
SELECT TRANSLATE('加+减-乘*除/完','','1234') AS CONTENT FROM DUAL;
```
**TRANSLATE 与 REPLACE 的区别：**
REPLACE 是字符串级别的替换
TRANSLATE 是字符级别的替换
`FIXME`
### WITH AS
WITH AS 用法可以对大批量的 SQL 语句起到优化的作用，方便维护和阅读。
关于此用法与普通子查询的性能及效率问题，待搜集到权威资料后补充。`FIXME`
语法：
```
WITH TABLE_A AS (
SELECT xx FROM xx
),
TABLE_B AS (
SELECT xx FROM xx
)
SELECT xx FROM TABLE_A, TABLE_B;
```
SQL 示例脚本及结果：
```
WITH TABLE_A AS (
SELECT '1' AS GID,'0101' AS CODE FROM DUAL
),
TABLE_B AS (
SELECT '2' AS GID,'0202' AS CODE FROM DUAL
),
TABLE_C AS (
SELECT '3' AS GID,'0303' AS CODE FROM DUAL
)
SELECT * FROM TABLE_A, TABLE_B, TABLE_C;
```
| GID | CODE | GID_1 | CODE_1 | GID_2 | CODE_2 |
| --- | --- | --- | --- | --- | --- |
| 1 | 0101 | 2 | 0202 | 3 | 0303 |
### PIVOT()
PIVOT() 是 Oracle Database 11g 中新增的函数，用于实现将列转换成标题行
其基本语法为：
`SELECT xx FROM xx PIVOT( Aggregate_Function(xx) FOR xx IN(xx,xx…));`
其中 Aggregate_Function(xx) 为聚合函数，如 SUM()、COUNT() 等
案例解析：
先造一段测试数据
```
WITH TEMP AS (
SELECT 1 AS GID, 'CN' AS COUNTRY, 1 AS SEX, 12 AS SALAR FROM DUAL UNION ALL
SELECT 2 AS GID, 'CN' AS COUNTRY, 1 AS SEX, 32 AS SALAR FROM DUAL UNION ALL
SELECT 3 AS GID, 'CN' AS COUNTRY, 0 AS SEX, 42 AS SALAR FROM DUAL UNION ALL
SELECT 4 AS GID, 'US' AS COUNTRY, 1 AS SEX, 11 AS SALAR FROM DUAL UNION ALL
SELECT 5 AS GID, 'US' AS COUNTRY, 0 AS SEX, 21 AS SALAR FROM DUAL UNION ALL
SELECT 6 AS GID, 'JP' AS COUNTRY, 0 AS SEX, 34 AS SALAR FROM DUAL UNION ALL
SELECT 7 AS GID, 'JP' AS COUNTRY, 1 AS SEX, 24 AS SALAR FROM DUAL UNION ALL
SELECT 8 AS GID, 'JP' AS COUNTRY, 0 AS SEX, 55 AS SALAR FROM DUAL
)
SELECT * FROM TEMP;
```
| GID | COUNTRY | SEX | SALAR |
| --- | --- | --- | --- |
| 1 | CN | 1 | 12 |
| 2 | CN | 1 | 32 |
| 3 | CN | 0 | 42 |
| 4 | US | 1 | 11 |
| 5 | US | 0 | 21 |
| 6 | JP | 0 | 34 |
| 7 | JP | 1 | 24 |
| 8 | JP | 0 | 55 |
对应字段含义分别为：编号、国家、性别、收入
现在有一个需求：按国家统计不同性别的总收入
先来一段一般做法，通过分组来实现：
```
SELECT COUNTRY, SEX, SUM(SALAR) FROM TEMP GROUP BY COUNTRY, SEX ORDER BY COUNTRY;
```
| COUNTRY | SEX | SUM(SALAR) |
| --- | --- | --- |
| CN | 0 | 42 |
| CN | 1 | 44 |
| JP | 0 | 89 |
| JP | 1 | 24 |
| US | 0 | 21 |
| US | 1 | 11 |
通过 PIVOT() 函数来实现同样需求，但是将性别从列转换为标题行：
```
SELECT * FROM (SELECT COUNTRY, SEX, SALAR FROM TEMP)
PIVOT(SUM(SALAR) AS SALARS FOR SEX IN(0, 1));
```
| COUNTRY | 0_SALARS | 1_SALARS |
| --- | --- | --- |
| US | 21 | 11 |
| CN | 42 | 44 |
| JP | 89 | 24 |
PIVOT() 函数的扩展用法：字段别名、排序
```
SELECT * FROM (SELECT COUNTRY, SEX, SALAR FROM TEMP)
PIVOT(SUM(SALAR) FOR SEX IN(0 AS "SALARS_FEMALE",1 AS "SALARS_MALE"))
ORDER BY COUNTRY;
```
| COUNTRY | SALARS_FEMALE | SALARS_MALE |
| --- | --- | --- |
| CN | 42 | 44 |
| JP | 89 | 24 |
| US | 21 | 11 |
本函数目的在于将列转标题行，相当于将列逆时针旋转 90 度，旋转需要支点，本例中，支点为 SUM(SALAR) 表达式，而 FOR SEX IN(0, 1) 为限制查询条件，该值是必须的。
有 PIVOT() 就有反向操作的函数 UNPIVOT() ，具体使用及需求日后再补充。`FIXME`
### GROUPING / ROLLUP
通常用于报表统计（小计、合计）
先准备数据如表 `TEST_DEMO`
| A | B | C |
| --- | --- | --- |
| a1 | b2 | 1 |
| a2 | b2 | 2 |
| a2 | b3 | 3 |
| a3 | b3 | 4 |
| a3 | b3 | 5 |
| a3 | b1 | 6 |
执行SQL：
```
SELECT A, B, SUM(C), GROUPING(A), GROUPING(B)
FROM TEST_DEMO
GROUP BY ROLLUP (A, B);
```
结果：
| A | B | SUM(C) | GROUPING(A) | GROUPING(B) |
| :--: | :--: | :--: | :--: | :--: |
| a1 | b2 | 1 | 0 | 0 |
| a1 | null | 1 | 0 | 1 |
| a2 | b2 | 2 | 0 | 0 |
| a2 | b3 | 3 | 0 | 0 |
| a2 | null | 5 | 0 | 1 |
| a3 | b1 | 6 | 0 | 0 |
| a3 | b3 | 9 | 0 | 0 |
| a3 | null | 15 | 0 | 1 |
| null | null | 21 | 1 | 1 |
GROUPING 在指定字段为 null 时返回 1， 否则返回 0 ，可用于判断是否为小计或合计。
其他关键字 `GROUPING_ID` `CUBE` 功能类似。
分类：编程 关键词：oracle
评论（0） 浏览（669）