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
相關文章
- spring心得8--AOP各種通知案例講解.docSpring
- Oracle分析函式七——分析函式案例Oracle函式
- Oracle單行函式詳解Oracle函式
- ORACLE單行函式與多行函式之一Oracle函式
- Oracle經典sql語句總結@sql-plus重點函式串講與sql語句案例@中文排序詳講）.docOracleSQL函式排序
- ORACLE單行函式與多行函式之二：字元函式示例Oracle函式字元
- ORACLE單行函式與多行函式之三：數值函式Oracle函式
- ORACLE單行函式與多行函式之四：日期函式示例Oracle函式
- ORACLE單行函式與多行函式之六：通用函式示例Oracle函式
- ORACLE單行函式與多行函式之五：轉換函式示例Oracle函式
- 常用分析函式開窗講解函式
- ORACLE單行函式與多行函式之七：多行函式之分組函式示例Oracle函式
- Oracle分析函式與視窗函式Oracle函式
- Oracle 分析函式詳解Oracle函式
- R-Drop論文復現與理論講解
- oracle心得3--多表查詢@分組查詢@子查詢講解與案例分析@經典練習題Oracle
- PHP：案例2--商品價格計算（案例分析+設計思路+知識點講解+原始碼+結果展示）PHP原始碼
- ORACLE中的單行函式Oracle函式
- spring心得9--自動代理知識點講解及案例分析Spring
- DRM 分析及案例講解
- oracle心得5--oracle的增刪改查@oracle的事務處理@案例分析Oracle
- ORACLE中的單行函式 (2)Oracle函式
- oracle中的單行函式（三）Oracle函式
- oracle中的單行函式（二）Oracle函式
- oracle中的單行函式（一）Oracle函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- spring心得6--自動裝配知識點講解及案例分析Spring
- Golang閉包案例分析與普通函式對比Golang函式
- oracle分析函式,keep and over解說Oracle函式
- Oracle聚合函式/分析函式Oracle函式
- 運維注意事項及案例講解（個人心得）運維
- Oracle 分析函式Oracle函式
- Oracle分析函式Oracle函式
- IOCP 系列函式講解函式
- 豪傑選單處理函式分析和研究函式
- PL/SQL單行函式和組函式詳解(轉)SQL函式
- Oracle中利用函式索引處理資料傾斜案例Oracle函式索引
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式