oracle心得2--單行函式理論講解與案例分析.doc
Sql有兩種函式，單行函式和多行函式
1.單行函式
單行函式:運算元據物件、接受引數返回一個結果、只對一行進行變換、每行返回一個結果、可以轉換資料型別、可以巢狀、引數可以是一列或一個值
DUAL是一個‘偽表’，可以用來測試函式和表示式
2.字元函式
大小寫控制函式：這類函式改變字元的大小寫。
例子：
select lower(ename) from emp; LOWER(ENAM ---------- smith allen ward select upper(ename) from emp; UPPER(ENAM ---------- SMITH ALLEN WARD select initcap(ename) from emp; INITCAP(EN ---------- Smith Allen Ward
字元控制函式：
select concat('hello','word')from dual;
CONCAT('H
---------
helloword
select substr('helloword',1,3) from dual;
SUB
---
hel
select length('helloword') from dual;
LENGTH('HELLOWORD')
-------------------
9
select instr('helloworld','w') from dual;
INSTR('HELLOWORLD','W')
-----------------------
6
select lpad('hello',10,'*')from dual;
LPAD('HELL
----------
*****hello
select rpad('hello',10,'#')from dual;
RPAD('HELL
----------
hello#####
select trim(' hello ') from dual;
TRIM(
-----
hello
3.數字函式
ROUND:四捨五入
TRUNC: 截斷
MOD:求餘
Round 函式 :語法為ROUND(number，num_digits)；其中Number是需要進行四捨五入的數字；Num_digits為指定的位數，按此位數進行四捨五入，如果 num_digits 大於 0，則四捨五入到指定的小數位; Num_digits值為多少就到相應的小數點位置四捨五入，如果 num_digits等於 0，則四捨五入到最接近的整數，如果 num_digits 小於 0，則在小數點左側進行四捨五入；Num_digits值多少就到小數點左側的整數相應的位置四捨五入。
例如：
ROUND(2.149， 0) 將 2.149 四捨五入到一個整數結果為2。
ROUND(2.15， 1) 將 2.15 四捨五入到一個小數位，結果為2.2。
ROUND(2.149， 1) 將 2.149 四捨五入到一個小數位結果為2.1。
ROUND(-1.475， 2) 將 -1.475 四捨五入到兩小數位結果為-1.48)。
ROUND(21.5， -1) 將 21.5 四捨五入到小數點左側一位結果為20。
例子：
SQL> select round(45.926,2) from dual;
ROUND(45.926,2)
---------------
45.93
SQL> select round(45.926,-2) from dual;
ROUND(45.926,-2)
----------------
0
SQL> select round(55.926,-2) from dual;
ROUND(55.926,-2)
----------------
100
SQL> select round(50.926,-2) from dual;
ROUND(50.926,-2)
----------------
100
SQL> select round(150.926,-2) from dual;
ROUND(150.926,-2)
-----------------
200
SQL> select round(50.326,-2) from dual;
ROUND(50.326,-2)
----------------
100
SQL> select round(550.326,-2) from dual;
ROUND(550.326,-2)
-----------------
600
SQL> select trunc(45.926,2) from dual;
TRUNC(45.926,2)
---------------
45.92
SQL> select trunc(45.926,-2) from dual;
TRUNC(45.926,-2)
----------------
0
SQL> select trunc(55.926,-2) from dual;
TRUNC(55.926,-2)
----------------
0
SQL> select trunc(155.926,-2) from dual;
TRUNC(155.926,-2)
-----------------
100
SQL> select mod(1600,300) from dual;
MOD(1600,300)
-------------
100
SQL> select mod(13,3) from dual;
MOD(13,3)
----------
1
SQL> select round(45.926,-1) from dual;
ROUND(45.926,-1)
----------------
50
4.日期
Oracle中的日期型資料實際含有兩個值: 日期和時間。
預設的日期格式是 DD-MON-RR.函式SYSDATE 返回:日期、時間
在日期上加上或減去一個數字結果仍為日期。兩個日期相減返回日期之間相差的天數。可以用數字除24來向日期中加上或減去小時。
日期函式
注：日期轉換格式不支援轉換中文格式的日期
例子：
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;
TO_CHAR(SY
----------
2013-04-04
SQL> select to_char(sysdate,'yyyy/mm/dd') from dual;
TO_CHAR(SY
----------
2013/04/04
select to_char(sysdate,'YEAR-MONTH-DAY') from dual;
TO_CHAR(SYSDATE,'YEAR-MONTH-DAY')
-----------------------------------------------------------
TWENTY THIRTEEN-4月 -星期四
select to_date('1212-12-12','yyyy/mm/dd') from dual;
TO_DATE('1212-
--------------
12-12月-12
SQL> select to_date('1212-12-12','yyyy-mm-dd') from dual;
TO_DATE('1212-
--------------
12-12月-12
SQL> select to_char(sysdate,'dd month year') from dual;
TO_CHAR(SYSDATE,'DDMONTHYEAR')
----------------------------------------------------
04 4月 twenty thirteen
SQL> select to_char(sysdate,'dd month yyyy') from dual;
TO_CHAR(SYSDAT
--------------
04 4月 2013
5.轉換函式
隱式資料型別轉換：Oracle自動完成下列轉換：
TO_CHAR 函式對日期的轉換
格式:必須包含在單引號中而且大小寫敏感。可以包含任意的有效的日期格式。日期之間用逗號隔開。
例子：
select ename,to_char(sal,'$999,999.00') from emp;
ENAME TO_CHAR(SAL,
---------- ------------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
select ename,to_char(sal,'l999,999.00') from emp;
ENAME TO_CHAR(SAL,'L999,999
---------- ---------------------
SMITH ￥800.00
ALLEN ￥1,600.00
WARD ￥1,250.00
6. 通用函式這些函式適用於任何資料型別，同時也適用於空值：
NVL (expr1, expr2):將空值轉換成一個已知的值：可以使用的資料型別有日期、字元、數字。
函式的一般形式:
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
NVL2 (expr1, expr2, expr3) : expr1不為NULL，返回expr2；為NULL，返回expr3。相當於java中的三目運算子
NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
COALESCE (expr1, expr2, ..., exprn):COALESCE 與 NVL 相比的優點在於 COALESCE 可以同時處理交替的多個值。如果第一個表示式為空,則返回下一個表示式，對其他的引數進行COALESCE 。
例子：
SQL> select ename,nvl(comm,0) from emp;
ENAME NVL(COMM,0)
---------- -----------
SMITH 0
ALLEN 300
WARD 500
錯誤寫法，條件comm與0位置混亂
SQL> select ename,nvl2(comm,0,comm) from emp;
ENAME NVL2(COMM,0,COMM)
---------- -----------------
SMITH
ALLEN 0
WARD 0
JONES
正確寫法：
SQL> select ename,nvl2(comm,comm,0) from emp;
ENAME NVL2(COMM,COMM,0)
---------- -----------------
SMITH 0
ALLEN 300
WARD 500
SQL> select nullif(2,2) from dual;
NULLIF(2,2)
-----------
SQL> select nullif(2,1) from dual;
NULLIF(2,1)
-----------
2
SQL> select nullif(1,2) from dual;
NULLIF(1,2)
-----------
1
SQL> select ename,
2 coalesce(sal,comm) from emp;
ENAME COALESCE(SAL,COMM)
---------- ------------------
SMITH 800
ALLEN 1600
WARD 1250
COALESCE(COMM,SAL)
------------------
800
300
500
2975
7條件表示式
在 SQL 語句中使用IF-THEN-ELSE 邏輯；使用兩種方法:
CASE 表示式
DECODE 函式
例子:
SQL> select ename,job,sal,case
2 job when 'SALESMAN' then sal*1.2
3 when 'MANAGER' then sal*1.8
4 else sal end "revised_sal" from emp;
ENAME JOB SAL revised_sal
---------- --------- ----- -----------
SMITH CLERK 800 800
ALLEN SALESMAN 1600 1920
WARD SALESMAN 1250 1500
JONES MANAGER 2975 5355
SQL> select ename,job,sal,decode(
2 job,'SALESMAN',1.2*sal,
3 'MANAGER',1.8*sal,sal)
4 revised_sal from emp;
ENAME JOB SAL REVISED_SAL
---------- --------- ----- -----------
SMITH CLERK 800 800
ALLEN SALESMAN 1600 1920
WARD SALESMAN 1250 1500
8.巢狀函式
單行函式可以巢狀。巢狀函式的執行順序是由內到外。
例子:
SQL> select ename,job,nvl2(to_char(job),'manager','NO MANAGER') from emp;
ENAME JOB NVL2(TO_CH
---------- --------- ----------
SMITH CLERK manager
ALLEN SALESMAN manager
WARD SALESMAN manager
相關文章
- Oracle分析函式與視窗函式Oracle函式
- 常用分析函式開窗講解函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- PHP：案例2--商品價格計算（案例分析+設計思路+知識點講解+原始碼+結果展示）PHP原始碼
- R-Drop論文復現與理論講解
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式
- Golang閉包案例分析與普通函式對比Golang函式
- 【進階】數論函式求和（理論）函式
- python函式每日一講 - exec執行函式Python函式
- ORACLE分析函式手冊(轉)Oracle函式
- Oracle分散式事務典型案例處理Oracle分散式
- 常見函式之單行函式函式
- Oracle優化案例-自定義函式索引（五）Oracle優化函式索引
- ORACLE分析函式手冊二(轉)Oracle函式
- ELK日誌分析系統 超詳細！！理論+實操講解！！
- Oracle中pivot函式詳解Oracle函式
- 詳細講解函式呼叫原理函式
- Clickhouse SQL日期處理函式及案例分享SQL函式
- 【函式】Oracle12c 列轉行函式使用listagg函式Oracle
- Python函式每日一講 - help函式的使用與實踐Python函式
- 建構函式與解構函式函式
- vue3函式setUp和reactive函式詳細講解Vue函式React
- wm_concat函式與oracle版本函式Oracle
- 常用的資料分析方法及案例講解
- ORALCE函式：LAG()和LEAD() 分析函式詳解函式
- python的四大函式講解Python函式
- oracle 函式Oracle函式
- oracle or 函式Oracle函式
- Oracle 遷移到 OB 過程中的函式改造案例Oracle函式
- 內聯（inline）函式與虛擬函式（virtual）的討論inline函式
- Golang時間函式及測試函式執行時間案例Golang函式
- python函式每日一講 - float函式型別轉換詳解Python函式型別
- 【Oracle SQL】months_between與trunc函式OracleSQL函式
- python函式每日一講 - int()函式Python函式
- PHP 函式庫精講之類與物件PHP函式物件
- （未完成）APC函式的執行，分析 KiDeliverApc 函式函式IDE
- 粒子群最佳化函式--particleswarm函式的用法與討論函式Swarm
- 【SQL】Oracle sql語句 minus函式執行效率與join對比SQLOracle函式
- 分散式理論(二) - BASE理論分散式