Oracle 10g資料庫基礎之基本查詢語句-中-函數

--資料參考：張烈張建中《資料庫管理者教育訓練講義》

函數：

使用函數的目的是為了操作資料

将輸入的變量處理，傳回一個結果。

變量可以有好多。

傳入的變量可以是列的值，也可以是表達式。

函數可以嵌套。

内層函數的結果是外層函數的變量。

單行函數：每一行都有一個傳回值，但可以有多個變量。

多行函數：多行有一個傳回值。

單行函數的分類：

字元操作函數

數字操作函數

日期操作函數

資料類型轉換函數

綜合資料類型函數

字元操作函數：

大小寫操作函數

Lower,upper,initcap 小寫大寫首字母大寫

字元串操作函數

Concat,length,substr,instr,trim,replace,lpad,rpad

實驗12：操作字元串的函數

大小寫操作函數

Lower,upper,initcap 小寫大寫首字母大寫

字元串操作函數

Concat,length,substr,instr,trim,replace,lpad,rpad

該實驗的目的是掌握常用的字元串操作的函數.

字元串的大小寫操作

SQL> Select lower(ename),upper(ename),initcap(ename) from emp;

SQL>Select lower(ename) 小寫字母,upper(ename) 大寫字母,initcap(ename) 首字母大寫 from emp;

SQL> select lower(' mf TR') 小寫字母, upper('mf TR') 大寫字母, initcap('mf TR') 首字母大寫 from dual;

Dual是虛表，讓我們用表的形式來通路函數的值。

其它字元串操作函數

concat将兩個字元連接配接到一起

select ename,job,concat(ename,job) from emp;

求字元串的長度

下面三句話是求字元串的長度，字元串要單引。

select length('張三') from dual;--按照字

select lengthb('張三') from dual;--按位元組

select lengthc('張三') from dual;--unicode的長度

substr截取字元串

substr(字元串,m,n),m是從第幾個字元開始，如果為負的意思是從後邊的第幾個開始。N是數多少個，如果不說就是一直到字元串的結尾

SQL> select ename,substr(ename,1,1) "第一個字母",substr(ename,-2) "最後兩個字母"from emp;

求子串在父串中的位置，0表示沒有在父串中找到該子串。

SQL> select ename,instr(ename,'A') "A在第幾位" from emp;

trim截斷字元串和添加字元串的函數

Trim函數是截掉頭或者尾連續的字元，一般我們的用途是去掉空格。

SQL> select trim(leading 'a' from 'aaabababaaa') from dual;

截掉連續的前置的a

SQL> select trim(trailing 'a' from 'aaabababaaa') from dual;

截掉連續的後置的a

SQL> select trim(both 'a' from 'aaabababaaa') from dual;

截掉連續的前置和後置的a

SQL> select trim('a' from 'aaabababaaa') from dual;

如果不說明是前置還是後置就是both全截斷。

Lpad和rpad字元填充

SQL> select lpad(ename,20,'*') ename,rpad(ename,20,'。') ename from emp;

左填充和右填充，20是總共填充到多少位，*和。是要填充的字元串。

Lpad左填充，rpad右填充，一般的用途是美化輸出的結果。

如果位數不足，按照截取後的結果顯示，不報錯。

select lpad(sal,18,'*')左填充18位星号,rpad(sal,25,'。')右填充25位句号,sal from emp;

select lpad(sal,30,' ')左填充30位空格,rpad(sal,30,' ')右填充30位空格,sal from emp;

REPLACE替換字元

SQL> SELECT REPLACE('JACK and JUE','J','xj_') FROM DUAL;

将字元串中的J全部替換位xj_

實驗13：操作數字的函數

該實驗的目的是掌握常用的關于數字操作的函數.

 ROUND，TRUNC數字操作函數

以小數點位核心，2是小數點後兩位，0可以不寫，表示取整，-1表示小數點前一位

ROUND是四舍五入：

SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;

TRUNC是截斷，全部舍棄：

SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;

ceil取整，上進位，和trunc全部去掉正好相反

SQL> select ceil(45.001) from dual;

取絕對值

SQL> select abs(-23.00) from dual;

取餘數

SQL> select mod(8.88,2) from dual;

實驗14：操作日期的函數

該實驗的目的是掌握常用的關于日期操作的函數.

系統日期的操作

日期是很特殊的資料類型，用好了可以提高資料庫的性能，而使用不當往往是錯誤的根源，如果你使用字元型資料來存儲日期，就放棄日期特有的計算功能。

函數SYSDATE求目前資料庫的時間。

SQL> select sysdate from dual;

日期的顯示格式和用戶端的配置相關。

檢視目前的日期顯示格式

SQL> select * from nls_session_parameters

where parameter='NLS_DATE_FORMAT';

col value for a20

代表的含義是凡是列的名稱是value的，都按照20個寬度來顯示，你想取消該列的定義

col value clear，其中col是column的縮寫。你想檢視幫助help column即可

alter session set NLS_DATE_FORMAT='yyyy/mm/dd:hh24:mi:ss';

重新設定為我們想要的格式。

select sysdate from dual;

SYSDATE

-------------------

2012/5/16007/05/01:16:32:54

檢視系統時間，資料庫本身沒有時間，它有scn号，和我們的時間不同。

alter session set NLS_DATE_FORMAT='DD-MON-RR';

設定為預設的顯示格式

再次檢視，我們發現日期的顯示随着用戶端的格式變化而變化。

日期的内部存儲都是以yyyymmddhh24miss存在資料庫中

日期的操作函數

SQL> select round(sysdate-hiredate) days,sysdate,hiredate from emp;

兩個日期相減的結果機關為天，往往是帶小數點。我們通過函數可以取整。

取兩個日期的月間隔

SQL> select months_between(sysdate,hiredate) 兩個日期的月間隔,sysdate,hiredate from emp;

六個月過後是哪天?

SQL> select sysdate 今天, add_months(sysdate,6) 六個月後 from dual;

SQL> select next_day(hiredate,5) ,hiredate from emp;

目前的日期算起，下一個星期五是哪一天，這句話你可能運作失敗，因為日期和用戶端的字元集設定有關系，如果你是英文的用戶端，就的用Friday來表達，日期是格式和字元集敏感的。如果你是中文的用戶端，就的用‘星期五’來表達。

該日期的月底是哪一天。

日期的進位和截取

select hiredate,round(hiredate,'mm') ,round(hiredate,'month') from emp;

select hiredate,round(hiredate,'yyyy') ,round(hiredate,'year') from emp;

select hiredate,trunc(hiredate,'mm') ,trunc(hiredate,'month') from emp;

select hiredate,trunc(hiredate,'yyyy') ,trunc(hiredate,'year') from emp;

數字的進位和截取是以小數點為中心，我們取小數點前或後的值，而日期的進位和截取是以年，月，日，時，分，秒為中心。

資料類型的隐式轉換

字元串可以轉化為數字和日期。

數字要合法，日期要格式比對。

select ename,empno from emp where empno='7900';

數字和日期在指派的時候也可以轉為字元串，但在表達式的時候不可以轉換。

select ename,empno from emp where ename='123';

select ename,empno from emp where ename=123;

資料類型的顯式轉換

To_char,to_date,to_number

日期轉化為字元串，請說明字元串的格式。

SQL> select ename,to_char(hiredate,'yyyy/mm/dd') from emp;

FM消除前置的零和空格。

SQL> select ename,to_char(hiredate,'fmyyyy/mm/dd') from emp;

其他格式：year,month,mon,day,dy,am,ddsp,ddspth

格式内加入字元串請雙引。

SQL> select to_char(hiredate,'fmyyyy "年" mm "月" dd "日"') from emp;

目前距離零點的秒數.

select sysdate,to_char(sysdate,'sssss') ss from dual;

數字轉為字元串

格式為9,0,$,l,.

col salary for a30

SQL> select ename,to_char(sal,'9999.000') salary from emp;

SQL> select ename,to_char(sal,'$00099999000.00' ) salary from emp;

SQL> select ename,to_char(sal,'l99,999.000') salary from emp;

SQL> select ename,TO_char(sal,'9G999D99') salary from emp;

9是代表有多少寬度，如果不足會顯示成######,0代表強制顯示0，但不會改變你的結果。G是千分符，D是小數點。

在資料庫中16進制的表達是按照字元串來描述的，是以你想将十進制的數轉換為十六進制的數使用to_char函數。

SQL> select to_char(321,'xxxxx') from dual;

其中xxxxx的位數要足夠，不然報錯，你就多寫幾個，足夠大就可以。

 To_number,to_date

如果你想将十六進制的數轉換為十進制的數請使用to_number函數。

SQL> select to_number('abc32','xxxxxxxx') from dual;

日期是格式和語言敏感的，切記！

SQL> select TO_NUMBER('100.00', '9G999D99') from dual;

G為千分符，D為小數點

RR和yy日期資料類型

select to_char(sysdate,'yyyy') "目前",

to_char(to_date('98','yy'),'yyyy') "yy98",

to_char(to_date('08','yy'),'yyyy') "yy08",

to_char(to_date('98','rr'),'yyyy') "rr98",

to_char(to_date('08','rr'),'yyyy') "rr08" from dual;

結果為

yy是兩位來表示年，世紀永遠和說話者的目前世紀相同。

RR比較靈活，它将世紀分為上半世紀和下半世紀。如果你處于上半世紀，描述的是0-49，那麼就和目前世紀相同，描述的是50-99就是上世紀。如果你處于下半世紀，描述的是0-49，那麼是下個世紀，描述的是50-99就是目前世紀。進而可以看出，RR的設計完全為了1990年到2010之間我們的思維習慣而設計的。當我們時間到2050前後，使用起來就非常的别扭。

實驗15：操作資料為null的函數

該實驗的目的是掌握常用的關于NULL值操作的函數.

綜合資料類型函數

NVL (expr1, expr2)

如果expr1為非空，就傳回expr1, 如果expr1為空傳回expr2，兩個表達式的資料類型一定要相同。

NVL2 (expr1, expr2, expr3)

如果expr1為非空，就傳回expr2, 如果expr1為空傳回expr3

NULLIF (expr1, expr2)

如果expr1和 expr2相同就傳回空，否則傳回expr1

COALESCE (expr1, expr2, ..., exprn)

傳回括号内第一個非空的值。

SQL> select ename,comm,nvl(comm,0) from emp;

有獎金就傳回獎金，獎金為空就傳回0。

select ename 姓名,sal 工資,comm 獎金,sal+nvl(comm,0) 總工資,nvl2(comm,'工資加獎金','純工資') "收入類别" from emp;

有獎金就傳回‘工資加獎金‘，獎金為空就傳回‘純工資’。

SQL> select ename,nullif(ename,'KING') from emp;

如果員工的名稱為king就傳回空，否則傳回自己的名字。

SQL> select ename 姓名,COALESCE(comm,0) "獎金" from emp;

如果有獎金就傳回獎金，如果沒有獎金就傳回0，起個别名叫做"獎金"。

實驗16：分支的函數

該實驗的目的是掌握分支操作的函數.

 Case語句

9I以後才支援的新特性，說叫語句其實是函數。目的是為了分支。

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

例子：

例題：判别job,不同工作的人賦予不同的工資，除了CLERK，SALESMAN，ANALYST以外，其它的人工資不變，将函數的值起一個别名為"處理後資料"。

語句：

SELECT ename, job, sal,

CASE job WHEN 'CLERK' THEN 1.1111*sal

WHEN 'SALESMAN' THEN 2.2222*sal

WHEN 'ANALYST' THEN 3.3333*sal

ELSE sal END "處理後資料"

FROM emp;

Decode函數：

和CASE語句一樣都是分支語句，但Decode函數是ORACLE自己定義的，其它資料庫可能不支援。

文法如下：

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

例題：判别job,不同工作的人賦予不同的工資，除了CLERK，SALESMAN，ANALYST以外，其它的人工資不變，将函數的值起一個别名為"處理後資料"。

decode(job ,'CLERK' , 1.11*sal

,'SALESMAN' , 3.33*sal

,'ANALYST' , 8.88*sal

, sal ) "處理後資料"

下面的例題是求稅率：

不同工資上的稅率不同。每2000一個台階，8000以上一律40%的稅。

SQL>SELECT ename, sal,

DECODE (TRUNC(sal/1000, 0),

0, 0.00,

1, 0.09,

2, 0.20,

3, 0.30

, 0.40

) 稅率

不管CASE語句還是DECODE函數，他們都是單行函數，每一行都有一個傳回值。從ORACLE角度來講，DECODE更好，因為各個版本的資料庫都支援，橫向來說，CASE語句更好，因為它是國标，不同的資料庫間都認可。

實驗17：分組統計函數

該實驗的目的是掌握常用的組函數.了解group by的操作.

需要掌握的知識點：

1。組函數

2。分組統計

3。NULL值在組函數中的作用

4。HAVING的過濾作用

5。組函數的嵌套

 組函數

這種函數每次處理多行，給出一個傳回值

Avg平均

Sum求和

Max最大

Min最小

Count計數

所有組函數，除了count(*)以外，都忽略null值，count是計數，檢視有多少行，count(列)是檢視該列有多少非空的行。

Group by 子句

Having是在結果中再次篩選。Having一定得出現在group by 子句得後面。不能獨立存在。

組函數的嵌套注意要使用GROUP BY子句。

巧用DECODE函數，改變排版方式

SQL>select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",

sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",

sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",

sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987",

count(ename) "總人數" from emp;

《完》

本文轉自xjzhujunjie 51CTO部落格，原文連結:http://blog.51cto.com/xjzhujunjie/866373

Oracle 10g資料庫基礎之基本查詢語句-中-函數

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List