Oracle實用教程_04章_Oracle數(shù)據(jù)類型和函數(shù)

1、工作 室第四章 oracle 數(shù)據(jù)類型和函數(shù) 4.1 oracle 數(shù)據(jù)類型 4.1.1 概述 前文中我們已明確了“信息”、“數(shù)據(jù)”和“數(shù)據(jù)處理”等相關(guān)基本概念（參見本書第1.1.1節(jié)），這里再講一下什么數(shù)據(jù)類型，以便于學習者對后文的深入理解，。數(shù)據(jù)類型就是一組性質(zhì)相同的值的集合以及定義于這個值集合上的一組操作的總稱。例如整型就是由所有整數(shù)值（- , +）組成的集合，并且在該集合上可以進行加、減、乘、除等數(shù)學運算操作。人類對數(shù)據(jù)類型的使用和研究是早于計算機技術(shù)的出現(xiàn)的，使用不同的數(shù)據(jù)類型是為了保存和處理現(xiàn)實生活中不同性質(zhì)信息的需要，例如統(tǒng)計人數(shù)可以用整型數(shù)據(jù)、計算有整有零的工資和利息使用浮點數(shù)

2、、而要記錄人的姓名就需要用到文本類型（字符或字符串類型）數(shù)據(jù)了。相應(yīng)地，在數(shù)據(jù)庫應(yīng)用中，創(chuàng)建數(shù)據(jù)表時我們必須根據(jù)其所存儲信息的特性定義表中所有字段的數(shù)據(jù)類型。為滿足實際應(yīng)用需要，oracle 數(shù)據(jù)庫支持多種不同的數(shù)據(jù)類型，甚至允許用戶自定義類型，但最常用的還是其基本數(shù)據(jù)類型，大致可分為字符型、數(shù)值型、 日期型和其它類型等幾類，oracle9i的數(shù)據(jù)類型體系具體如圖4-1 所示。oracle 數(shù)據(jù)類型字符類型char 、 nchar varchar2 、 nv archar2 long number數(shù)值類型number日期類型timestampraw、long raw 其它類型lobbfilex

3、ml type用戶自定義類型圖 4-1 oracle9i 數(shù)據(jù)類型體系1 工作 室下文中我們將對oracle 各基本 /常用數(shù)據(jù)類型分別做介紹。4.1.2 字符數(shù)據(jù)類型 char char 型數(shù)據(jù)存儲的是固定長度的字符串，其字段長度為12000 字節(jié)，具體在使用時由用戶設(shè)定，如果沒有顯式規(guī)定其長度，則默認長度設(shè)置為1 個字節(jié)。如果對某個char 類型字段 /變量賦值，其長度小于規(guī)定的長度，那么oracle 自動用空格填充；如果大于規(guī)定的長度，則出錯。char 類型使用格式為char(n) ，其中參數(shù)n 允許取值范圍12000（字節(jié)），例如：create table users(name cha

4、r(6), sex char(1); insert into users v alues(tom,m); insert into users v alues( 張仲景 ,m); insert into users v alues(jessica,f); insert into users v alues(nancy, 女); 其中，最后兩條語句在運行時會出錯，因為在向數(shù)據(jù)表中插入新記錄時，其字符串長度超出了所在字段的長度限制（字符占用的存儲空間長度與所在數(shù)據(jù)庫系統(tǒng)所采用的編碼方式有關(guān)，通常， ascii 碼字符集中包含的字符占一個字節(jié)，漢字占兩個字節(jié)），具體報錯信息如圖4-2 所示。圖 4-2

5、 插入的值超出字段長度限制通常在數(shù)據(jù)庫表中字段值長度變化范圍不大、且其內(nèi)容頻繁變化時，可采用char 數(shù)據(jù)類型以提高效率，比如記錄人的姓名信息。需要小心的是，如果采用了char 型字段記錄文本信息，將來進行查詢操作時，從數(shù)據(jù)庫中再讀出的數(shù)據(jù)值均變成定長的字符串。比如使用char （6）類型字段記錄姓名信息時，插入記錄時人的名字叫 tom ，在數(shù)據(jù)庫中其尾部將被自動追加三個空格符（補足6 個字節(jié)），這樣將來再讀取出來的就變成了tom ，很可能會影響到應(yīng)用程序的業(yè)務(wù)邏輯，這種情況可以在取出數(shù)據(jù)后先過濾其頭尾的連續(xù)空格再使用，實際開發(fā)中應(yīng)特別注意。此外， oracle 數(shù)據(jù)庫在進行char 型數(shù)據(jù)值

6、的等價性比較時，會自動過濾調(diào)的條件查詢時，會varchar2 varchar2型數(shù)據(jù)存儲的是可變長的字符串，具體定義時指明其最大長度，實際使用時，如果數(shù)據(jù)長度沒有達到規(guī)定的字段長度最大值時，oracle 系統(tǒng)會根據(jù)數(shù)據(jù)的實際實際長度為其分配2 3 工作 室存儲空間（自動調(diào)節(jié)字段長度），如果大于規(guī)定的長度，則出錯。oracle 推薦使用varchar2型保存文本信息。varchar2類型使用格式為varchar2(n) ，其中參數(shù)n 指定對該變量賦值的最大長度而非實際賦值長度，其允許取值范圍14000（字節(jié)），例如：create table users(name v archar2(10), s

7、ex v archar2(4); insert into users v alues(tom,m); insert into users v alues( 張仲景 ,男); insert into users v alues(john simth, 男); 由于 varchar2類型數(shù)據(jù)只存儲有效的字符信息（不會追加空格符），所以會比char 型節(jié)省存儲空間。通常在數(shù)據(jù)庫表中字段值長度變化范圍較大、且其內(nèi)容不頻繁變化時（比如留言信息），可采用varchar2數(shù)據(jù)類型以節(jié)省存儲開銷。在實際應(yīng)用開發(fā)中，varchar2是最常用的字符數(shù)據(jù)類型。nchar 和 nvarchar2 nchar 和 nv

8、archar2類型數(shù)據(jù)分別存儲固定長度與可變長度的字符串，但是它們使用的是和數(shù)據(jù)庫其他類型不同的字符集。在創(chuàng)建數(shù)據(jù)庫時，需要指定所使用的字符集，以便對數(shù)據(jù)中數(shù)據(jù)進行編碼。還可以指定一個輔助的字符集（national language support，nls）。 nchar和nvarchar2類型的字段使用輔助字符集進行編碼和存儲。此外，需要注意的是， 在 oracle 9i 中， 以字符而不是字節(jié)為單位指定nchar 和 nvarchar2類型的長度參數(shù)，例如：create table users(name nchar(6), sex nchar(1); insert into users v

9、 alues( 醫(yī)圣張仲景 ,男 ); insert into users v alues(jessica,f); 上述代碼中建表時指定的name、sex 字段長度單位均為字符，之后添加的第一行記錄未超出其各字段的長度限制（醫(yī)圣張仲景 、男兩個字符串的長度分別為5 個、 1 個字符），因此添加操作成功， 而第二行記錄則添加失敗（jessica 長度為 7 個字符， 超出了 name 字段最大長度6 字符的限制）。nvarchar2的情況類似，讀者可自行測試并體會。long long 型數(shù)據(jù)存儲的也是可變長度的字符串，其最大長度限制是2gb（ 2g 個字節(jié)），不支持對字符串的內(nèi)容進行檢索（例如條

10、件查詢中比較字段值或模糊查詢），如要進行字符串內(nèi)容檢索該使用 varchar2類型。long是一種較老的數(shù)據(jù)類型（它是從早期版本中繼承下來的），在表和sql 語句中使用long 類型存在諸多限制，比如一個表中最多只能有一個long 型的字段，現(xiàn)已不提倡使用。如要存儲大容量的字符數(shù)據(jù)，oracle 推薦使用clob 和 nclob 數(shù)據(jù)類型。4.1.3 數(shù)值數(shù)據(jù)類型 工作 室number number 類型描述的可變長的數(shù)值，允許取 0、正值即負值， 其使用格式為 number(m,n)，下面具體說明一下其中兩個類型參數(shù)的含義：-參數(shù) m 用于設(shè)定數(shù)據(jù)精度（有效數(shù)字的位數(shù)），取值范圍138，默認

11、值為38；-參數(shù) n 用于設(shè)定小數(shù)點以后的位數(shù)，取值范圍 -84127，默認值與是否顯式設(shè)定了數(shù)據(jù)精度有關(guān)如果顯式設(shè)定了數(shù)據(jù)精度m 的值，則n 的默認值為0；否則 n 默認取其最大值 127；如果把n 設(shè)為負數(shù)，則oracle 將把該數(shù)字取舍到小數(shù)點左邊的指定位數(shù)。實際應(yīng)用中使用number 類型時，建議顯式指定合理的精度和小數(shù)點后有效位數(shù)值（m 和 n） ，以便于數(shù)據(jù)庫系統(tǒng)在數(shù)據(jù)輸入時幫助我們進行校驗，此外，也可以避免濫用默認的全精度所造成的不必要的空間開銷。例如下述語句：create table users(name v archar2(20),age number(3), salary

12、number(8,2); insert into users v alues(tom,34.8,5600.348); select * from users; 的返回結(jié)果如圖4-3 所示。圖 4-3 使用 number類型字段其中，創(chuàng)建表users 時顯式設(shè)定age字段精度為3 位有效數(shù)字，其小數(shù)點后保留有效位數(shù)默認為0位，因此實際保存的值為四舍五入到各位數(shù)的35，salary 字段的情況類似。為加深理解， 避免在實際應(yīng)用中出錯，表 4-1 中列舉中多種可能出現(xiàn)的情況，供讀者自行測試、分析并體會。表 4-1 number 類型使用格式聲明格式輸入值實際存儲值說明number(5,2) 21.4

13、896 21.49 最大能表示999.9949999. number(3,0) 214 214 最大能表示999.4999999. number(3,-1) 2148.96 2150 最大能表示9994.999999. number(3) 214.896 215 等價于 : number(3, 0) 2148.96 出錯，值大于此列指定的允許精確度0.0024896 0 number 214.896 215 等價于 : number(38, 127) 4 工作 室4.1.4 日期數(shù)據(jù)類型 oracle 中的日期數(shù)據(jù)類型包括date 和 timestamp兩種， 其中應(yīng)用最普遍的是date 類型

14、。date 類型數(shù)據(jù)記錄的日期/時間信息按世紀、年、月、日、小時、分鐘、秒鐘來表示，也獲取日期相應(yīng)的星期幾，其允許取值范圍從公元前4712 年 1 月 1 日到公元4712 年 12 月 31 日，默認為公元后（ ad ）。date 類型日期的默認格式是dd-mon-yy，其中 dd 表示日， mon 表示月份（例如中文的“ 4 月”，英文語言環(huán)境中則為月份英文單詞的簡寫形式、如“feb”）， yy 表示年份的最后兩位數(shù)字，默認為最近一個世紀范圍（目前為19502049）內(nèi)的年份，如希望使用非默認格式來輸入和獲取日期信息，則須借用相關(guān)的系統(tǒng)函數(shù)to_date 或 to_char 進行轉(zhuǎn)換，例如

15、：create table users(name v archar2(20), birth date); insert into users v alues( 張三 ,2-12 月 -07); insert into users v alues( 李四 ,28-5 月 -51); insert into users v alues( 王五 ,to_date(2004-10-16,yyyy-mm-dd); insert into users v alues( 趙六 , to_date(03/08/1876 15:23:55, mm/dd/yyyy hh24:mi:ss); select name

16、, birth from users; select name, to_char(birth, yyyy-mm-dd day hh24:mi:ss) from users; 上述語句的運行返回結(jié)果如圖4-4 所示，請讀者自己體會。圖 4-4 日期型數(shù)據(jù)顯式格式其中用到了日期型函數(shù)to_date()和 to_char()、以及相應(yīng)的日期/時間域格式符號，這里只是簡單展示一下，后文中會做詳細講解（參見本書第4.2 節(jié)），如讀者理解上有困難此處不必深究。如果是英文的oracle 語言環(huán)境中， 上述語句中date 類型數(shù)據(jù)的缺省格式則變?yōu)?8-may-51的形式，要了解oracle 當前語言環(huán)境中的日

17、期型常量缺省格式，可執(zhí)行如下語句：select sysdate from dual; 或者5 6 工作 室select * from v$nls_parameters where parameter=nls_date_format; 如果需要，可采用如下方式修改當前會話的語言、地域環(huán)境以及日期/時間的缺省顯式格式設(shè)置：-將語言改為英文alter session set nls_language=american; -將語言改回簡體中文alter session set nls_language=simplified chinese; -將地域改為美國alter session set nls_

18、territory=america; -將地域改為中國alter session set nls_territory=china; -修改日期 /時間的缺省顯式格式alter session set nls_date_format = yyyy-mm-dd; 上述修改僅在本次會話范圍內(nèi)有效，實際開發(fā)中并不提倡這樣的做法，而是應(yīng)該采用相應(yīng)的日期型函數(shù)（ to_char()或 to_date()等）進行轉(zhuǎn)換，其功能更強大而靈活。4.1.5 其它數(shù)據(jù)類型 raw 和 long raw raw 和 long raw數(shù)據(jù)類型用于保存變長二進制數(shù)據(jù)。raw 類型一般用于存儲有特定格式的對象（如位圖），單個

19、字段最多可占用2000 字節(jié)的空間，而long raw數(shù)據(jù)類型則最多可占用 2gb 的空間。二者均屬較老的數(shù)據(jù)類型，一個表中最多只能有一個long raw型的字段，現(xiàn)已逐漸被blob 、clob 、nclob 等大對象類型（見下文）所取代。lob lob（large object ，大型對象）類型用于保存大規(guī)模的信息（其單個字段最多能容納4gb 的數(shù)據(jù)）。由于lob 類型字段容量大，且一個表中可以出現(xiàn)多個lob 類型的字段，很靈活，適用于數(shù)據(jù)量非常大的業(yè)務(wù)領(lǐng)域（如圖象、檔案等）。相應(yīng)地，long 、long raw等類型的字段，雖然存儲容量也不小，但由于一個表中最多只能有一個這樣類型的字段的限

20、制，現(xiàn)在已很少使用了。lob 類型又可細分為三種：-clob 字符大對象類型，最大長度4gb -nclob 基于nls 國家字符集的字符字符大對象類型，最大長度4gb -blob 二進制大對象類型，最大長度4gb bfile bfile屬于特殊的大對象類型，該類型描述的是在數(shù)據(jù)庫外部保存的大型二進制對象文件，工作 室最大長度是4gb。這種外部的lob 類型，通過數(shù)據(jù)庫記錄變化情況，但是數(shù)據(jù)的具體保存是在數(shù)據(jù)庫外部進行的?？梢院唵卫斫鉃?，bfile 類型數(shù)據(jù)相當于指向存儲在oracle 數(shù)據(jù)庫以外的文件的指針。xml type 作為對 xml 支持的一部分，xmltype 是從 oracle 9

21、i 開始引入的一種的數(shù)據(jù)類型。該類型字段用于存儲一個xml 文檔，并提供了多種內(nèi)置的xml 文檔解析處理功能，比如可以從文檔中抽取單個節(jié)點、也可以在xml type文檔中對任何節(jié)點創(chuàng)建索引。4.2 單行函數(shù) 數(shù)據(jù)庫中的函數(shù)（function ）相當于其他計算機編程語言中的方法（method ）或子程序（subroutine），是用來完成相對獨立功能的一段代碼的集合。oracle 數(shù)據(jù)庫允許用戶自己定義函數(shù)以滿足其個性化的需要，并內(nèi)置了豐富的系統(tǒng)函數(shù)以提供各種常規(guī)數(shù)據(jù)處理功能，這些函數(shù)功能強大、簡潔易用，可用于各種sql 語句。如圖 4-5 所示， oracle 函數(shù)可分為單行函數(shù)和多行函數(shù)兩大

22、類：7 單行函數(shù)是指那些每行輸入記錄都能得到一行處理結(jié)果（輸出一行結(jié)果）的函數(shù)，例如查詢員工信息表，根據(jù)其中工資字段計算每個雇員的應(yīng)納稅額，有多少行記錄就應(yīng)得到多少行處理結(jié)果；多行函數(shù)則是多行輸入記錄經(jīng)處理后只輸出一行結(jié)果，例如統(tǒng)計員工信息表中全部員工的平均工資、或工資總額，多行記錄（多個員工）只能得到一個統(tǒng)計結(jié)果。圖 4-5 oracle 函數(shù)分類按其所提供的功能劃分，單行函數(shù)又可分為字符函數(shù)、數(shù)值函數(shù)、日期函數(shù)、轉(zhuǎn)換函數(shù)和通用函數(shù)五類，本節(jié)將分別加以介紹。4.2.1 字符函數(shù) 字符函數(shù)提供字符型數(shù)據(jù)處理功能，包括字符串大小寫拼寫轉(zhuǎn)換、連接、截?。ㄈ∽哟⑦^濾空格、內(nèi)容檢索和替換等。ora

23、cle 常用字符函數(shù)如表4-2 所示。表 4-2 常用字符函數(shù)函數(shù)功能用法返回結(jié)果lower() 轉(zhuǎn)換為小寫lower(john smith) john smith upper() 轉(zhuǎn)換為大寫upper(john smith) john simth initcap() 單詞首字母大寫initcap(john smith) john smith 8 工作 室concat() 字符串連接concat(hello , world) hello world substr() 截取子串substr(helloworld,4,3) low length() 返回字符串長度length(hello worl

24、d) 11 instr() 定位子串instr(hello world,or) 8 lpad() 左側(cè)填充lpad(smith,10,*) *smith rpad() 右側(cè)填充rpad(smith,10,*) smith* trim() 過濾首尾字符trim( mr smith ) mr smith replace() 替換replace(mr smith and mr white,mr,mrs) mrs smith and mrs white 下面分別做簡要說明：lower(char) 將參數(shù)指定的字符串轉(zhuǎn)化為小寫格式返回，其參數(shù)可以是字符串類型的常量、查詢字段名 （相當于變量）或表達式，如

25、輸入?yún)?shù)值為null ，則返回結(jié)果為null 。upper(char) 將參數(shù)指定的字符串轉(zhuǎn)化為大寫格式返回，其余同函數(shù)lower() 。initcap(char) 將參數(shù)指定的字符串中每個單詞的首字母大寫，其它字母小寫，單詞間用空格和非字母字符分隔，如參數(shù)值為null ，則返回結(jié)果為null 。上述三個函數(shù)功能相近，均用于控制字符串的大小寫拼寫格式，在進行條件查詢時，經(jīng)常會遇到要查詢的文本信息大小寫拼寫不確定的情況此時就可以使用上述函數(shù)將文本信息轉(zhuǎn)換成指定的拼寫格式、再進行查詢。做為練習，請讀者自行測試下述語句的查詢結(jié)果并體會。select deptno, loc from dept; se

26、lect deptno, lower(loc) from dept; select deptno, upper(loc) from dept; select deptno, initcap(loc) from dept; select initcap(mr smith,morning!) from dual; select * from emp; select * from emp where ename=smith; select * from emp where lower(ename)=smith; select * from emp where ename like %am%; sel

27、ect * from emp where lower(ename) like %am%; concat(exp1, exp2) 將參數(shù)指定的兩個表達式的值以字符串形式連接后返回，其效果相當于使用字符串連接運算符“ |”。其中，參數(shù)表達式可以是任何類型（不必須是字符串類型），例如：工作 室select concat(dear: , ename), concat(sal, 元) from emp; select sal, comm, sal + comm, concat(sal, comm) from emp; select sal, comm, sal + comm, sal | comm fr

28、om emp; substr(char, start , size) 截取指定字符串的子串并返回。參數(shù)char 用于指定源字符串、start 用于指定子串的起始位置/字符下標（首字符下標為1）、 size 用于指定子串長度。如m=0，則從首字符開始截?。ㄏ喈斢趍=1）；如果m 值為負數(shù)，則從尾部開始截取。如果沒有足夠多的字符供截?。╯tart+size-1源字符串總長度）、或者參數(shù)end缺省時，默認截取到字符串結(jié)尾。例如：-截取源字符串中從第1 個字符開始的連續(xù)4 個字符select substr(hello,world,1,4) from dual; -截取從第8 個字符開始的連續(xù)2 個字符

29、select substr(hello,world,8,2) from dual; -截取從第8 個字符開始的連續(xù)10 個字符（可用字符不足，實際只返回4 個字符）select substr(hello,world,8,10) from dual; -截取從第8 個字符開始、直到源字符串結(jié)尾的連續(xù)多個字符select substr(hello,world,8) from dual; -截取從第首字符開始的連續(xù)4 個字符select substr(hello,world,0,4) from dual; -截取從源字符串倒數(shù)第4 個字符開始的連續(xù)2 個字符select substr(hello,w

30、orld,-4,2) from dual; 請讀者自行測試其運行結(jié)果并體會。length(char) 返回字符串的長度，即字符串中所包含字符的個數(shù)。如果如輸入?yún)?shù)值為null ，則返回結(jié)果為 null 。需要注意的是，如果輸入字符串為char 型，則其長度包含了可能存在的后綴空格，例如下述語句：drop table users; create table users(name char(10), info varchar2(20); insert into users values(tom, haha); insert into users values( 中國人 , 長沙太守醫(yī)圣); sel

31、ect name, length(name), info, length(info) from users; 的運行返回結(jié)果如圖4-6 所示。圖 4-6 函數(shù) length()測試結(jié)果9 工作 室說明：在數(shù)據(jù)庫中，第一行記錄的name 字段被自動追加了7 個空格符（其有效內(nèi)容tom占 3個字節(jié)，后綴的每個空格符占1 個字節(jié)，共10 個字節(jié)），而第二行記錄的name 字段則被自動追加了 4 個空格符（其有效內(nèi)容中國人 占 6 個字節(jié)），這樣二者中包含的字符數(shù)目分別為10 個和 7個。instr(char1, char2, , start , n) 返回指定的子串在母串（源字符串）中出現(xiàn)的位置。參

32、數(shù)char1 用于指定母串、char2 用于指定子串，可選參數(shù)start 用于指定起始搜索位置（默認值為1），可選參數(shù)n 用于指定搜索的是子串第幾次出現(xiàn)的位置（默認值為1）。如果輸入?yún)?shù)值存在null ，則返回結(jié)果為null ，例如：select instr(hello,v512 is coming, welcome to v512, v512) from dual; select instr(hello,v512 is coming, welcome to v512, v512, 10) from dual; select instr(hello,v512 is coming, welcom

33、e to v512, v512, 3, 2) from dual; select instr(hello,v512 is coming, welcome to v512, jobedu) from dual; select instr(null, v512) from dual; 請讀者自行測試其運行結(jié)果并體會。lpad(char1, n, char2) 在指定的字符串左側(cè)填充字符、將之擴充到指定的長度。參數(shù)char1 用于指定源字符串（將要被擴充的字符串）、n 用于指定填充后的長度、char2 用于指定所使用的填充符。如果輸入?yún)?shù)值存在 null ，則返回結(jié)果為null 。需要注意的是，如果

34、源字符串char1 的長度已經(jīng)超過了n，則直接返回char1的前 n 個字符（“擴充”變成了“截斷”）。例如下述語句：select empno, ename, sal, lpad(ename,5, ) | - | rpad(sal,10, ) as 薪水 from emp where deptno=10; 的運行返回結(jié)果如圖4-7 所示。圖 4-7 函數(shù) lpad()測試結(jié)果rpad(char1, n, char2) 在指定的字符串右側(cè)填充字符、將之擴充到指定的長度，其余同函數(shù)lpad()。trim( leading | trailing | both char from source) 過濾

35、字符串開頭和/或結(jié)尾的連續(xù)0多個指定字符，返回過濾后的結(jié)果。參數(shù)source 用于指定源字符串、 char 用于指定要過濾掉的字符，可選的關(guān)鍵字leading指定過濾源字符串頭部字符、 trailing指定過濾其尾部字符、both 指定同時過濾頭部和尾部字符（默認值為both ）。10 11 工作 室如未顯式指定參數(shù)char，則缺省過濾空格符 。例如：select trim(ename) from emp; select trim( mr smith ) from dual; select trim( from mr smith ) from dual; select trim(both fr

36、om mr smith ) from dual; select trim(leading from mr smith ) from dual; select trim(trailing from mr smith ) from dual; select trim(trailing - from mr smith-) from dual; trim() 函數(shù)在實際開發(fā)中經(jīng)常用到，比如在進行用戶注冊、或登錄驗證時，應(yīng)首先對用戶提交的用戶名 /密碼信息進行trim() 處理過濾掉其首尾可能存在的空格符，再保存到數(shù)據(jù)庫、 或是與數(shù)據(jù)庫中存儲的相應(yīng)信息進行比較。replace(source, char1

37、, char2) 替換源字符串中的子串內(nèi)容，返回替換后的結(jié)果。參數(shù)source用于指定源字符串、char1 用于指定被替換子串、char2 用于指定替換成的子串。例如：select replace(mr smith and mr white,mr,mrs) from dual; 4.2.2 數(shù)值函數(shù) 數(shù)值函數(shù)提供常用的數(shù)學運算功能，包括數(shù)據(jù)截斷、取絕對值、三角函數(shù)、冪運算及對數(shù)運算等等。 oracle 常用數(shù)值函數(shù)如表4-3 所示。表 4-3 常用數(shù)值函數(shù)函數(shù)功能用法返回結(jié)果abs() 取絕對值abs(-3.14) -3.14 round() 四舍五入round(3.1415) round(3

38、.1415, 3) round(314.1592, -2) 3 3.142 300 trunc() 截斷trunc(3.1415, 3) 3.141 ceil() 向上取整ceil(3.14) 4 floor() 向下取整floor(3.14) 3 sign() 判斷數(shù)值正負sign(-3.14) -1 sin() 三角函數(shù)sin(3.14) .001592653 power() 冪運算power(4.5, 2) power(4.5,2) sqrt() 開平方根sqrt(9) 3 12 工作 室mod() 取模mod(10, 3) 1 exp() 基數(shù)為 e 的冪運算exp(1) 2.7182

39、8183 log() 對數(shù)運算log(4, 16.0) 2 ln() 自然對數(shù)運算ln(7) 1.94591015 數(shù)值函數(shù)格式簡單，易于理解和掌握，這里只是對其使用格式方面做簡單說明：abs(n) 返回參數(shù)n 所指定數(shù)值的絕對值（如果參數(shù)值為null ，則返回結(jié)果為null ，下同）。round(n, m) 返回對參數(shù)n 進行四舍五入處理后的結(jié)果。參數(shù)n 可以是任意數(shù)字、m 則必須為整數(shù)。如果省略參數(shù)m，則四舍五入到整數(shù)位；如 m 為正數(shù)， 則將 n 四舍五入到小數(shù)點后的第m 位；如 m 為負數(shù)，則將n 四舍五入到小數(shù)點左側(cè)的第m 位。trunc(n, m) 返回對參數(shù)n 進行截斷處理后的結(jié)

40、果。參數(shù)n 可以是任意數(shù)字、m 則必須為整數(shù)。如果省略參數(shù) m，則將數(shù)字n 的小數(shù)部分截掉；如 m 為正數(shù)， 則將 n 截斷到小數(shù)點后的第m 位；如 m 為負數(shù)，則將 n 截斷到小數(shù)點左側(cè)的第m 位。ceil(n) 向上取整，返回不小于n 的最小整數(shù)。floor(n) 向下取整，返回不大于n 的最大整數(shù)。sign(n) 判斷數(shù)字值的正負。如果參數(shù)n 的值小于0 則返回 -1；如果 n 大于 0 則返回 1；如果 n 等于 0則返回結(jié)果為0。sin(n) 正弦三角函數(shù)，返回（以弧度制表示的）角度n 的正弦值。類似的三角函數(shù)有多個，這里不再一一列舉。power(m , n) 返回參數(shù)m 的 n 次

41、冪。參數(shù)m 和 n 可以是任意數(shù)字，但如果m 值為 0，則 n 必須為正數(shù)（否則會出現(xiàn)數(shù)字溢出錯誤）。sqrt(n) 返回參數(shù)n 的平方根。 n 必須為大于等于0的數(shù)字。mod(m , n) 取模運算，返回參數(shù)m 除以 n 所得的余數(shù)。參數(shù)m 和 n 可以是任意數(shù)字，返回結(jié)果的數(shù)據(jù)符號（正 /負）與被除數(shù)m 保持一致，如果n 的值等于0，則直接返回m 的值。13 工作 室exp(n) 冪運算，返回自然對數(shù)的底數(shù)e 的 n 次冪（ e=2.71828.）。log(m , n) 對數(shù)運算，返回以m 為底的 n 的對數(shù)。參數(shù)m 可以是除0 和 1 以外的任意正數(shù)，參數(shù)n 可以是任意正數(shù)。ln(n)

42、自然對數(shù)運算，返回參數(shù)n 的自然對數(shù)（以e 為底 n 的對數(shù)），參數(shù)n可以是任意正數(shù)。4.2.3 日期函數(shù) 日期函數(shù)提供了常用的日期/時間信息處理功能，比如截斷、計算時間差等。oracle 常用日期函數(shù)如表4-4 所示。表 4-4 常用日期函數(shù)函數(shù)功能用法add_months(x, y) 計算在日期x 基礎(chǔ)上增加y 個月后的日期add_months(sysdate,2) last_day(x) 返回日期 x 當月最后一天的日期last_day(sysdate) months_between(x, y) 返回日期 x 和 y 之間相差的月數(shù)months_between(sysdate,hired

43、ate) round(x, y) 將日期 x 四舍五入到y(tǒng) 所指定的日期單位（月或年）的第一天round(sysdate,month) trunc(x, y) 將日期 x 截斷到 y 所指定的日期單位（月或年）的第一天round(sysdate,year) next_day(x, y) 計算指定日期x 后的第一個星期幾（由參數(shù)y 指定）對應(yīng)的日期trunc(sysdate,month) 下面分別做簡要說明：add_months(x, y) 返回在日期 /時間 x 基礎(chǔ)上增加y 個月后的時間。參數(shù)x 用于指定起始時間，可以是日期型的常量、字段名或表達式，參數(shù)y 可以是任意整數(shù)。如y 值為負數(shù)，則

44、返回在日期x 基礎(chǔ)上減去y個月后的日期 /時間； 如 y 值為浮點數(shù)， 則先將 y 截斷到整數(shù)位再進行運算。因此返回值只有年度、月份值可能會發(fā)生變化，小時、分、秒的值是不變的。例如：select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) t0, to_char(add_months(sysdate, 2),yyyy-mm-dd hh24:mi:ss) t1, to_char(add_months(sysdate, 2.5),yyyy-mm-dd hh24:mi:ss) t2 from dual; 的運行返回結(jié)果如圖4-8 所示。工作 室圖 4-8 add_m

45、onths()函數(shù)測試結(jié)果從中應(yīng)可看出后兩條語句的返回相同，這里還用到了前文提到過的轉(zhuǎn)換函數(shù)，只是為了將時間信息現(xiàn)實的更相近一些（缺省格式只能顯示出年、月、日的信息）。如輸入?yún)?shù)值（x 或 y 的值）出現(xiàn)null ，則返回結(jié)果為null ，下同。last_day(x) 返回日期 /時間 x 當月最后一天的日期/時間，小時、分及秒等時間信息不變，例如select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) t0, to_char(last_day(sysdate),yyyy-mm-dd hh24:mi:ss) t1 from dual; months_betwe

46、en(x, y) 返回日期x 和 y 之間相差的月數(shù)。返回值的符號確定原則：如果x 小于（早于）y，則返回負數(shù)；如果x 大于 y，則返回正數(shù)；如果 x 等于 y，則返回0。時間差值的計算規(guī)則：如果x 和 y 的“日”相同（比如都是14 日）、或者均為月底（比如分別為“ 3 月 31 日”和“ 4 月 30 日”），則返回整數(shù)；否則oracle 是以 31 天為基準，計算結(jié)果的小數(shù)部分，且忽略x 和 y 在小時、分、秒層面的差異。例如：select months_between(sysdate, hiredate) 工齡月數(shù) from emp; select months_between(to_

47、date(2008-12-23 01:23:45,yyyy-mm-dd hh24:mi:ss), to_date(2008-12-23 15:23:45,yyyy-mm-dd hh24:mi:ss) t0 from dual; select months_between(to_date(2008-12-23 01:23:45,yyyy-mm-dd hh24:mi:ss), to_date(2008-12-22 23:23:45,yyyy-mm-dd hh24:mi:ss) t0 from dual; 請讀者自行測試其查詢結(jié)果并體會。round(x, y) 對日期進行四舍五入操作。參數(shù)x 用于指

48、定原始日期/時間，參數(shù)y 用于指定四舍五入的方式如果y 的值為 year ，則四舍五入到年度第一天（以7 月 1 日為分界線）；如果y 的值為month ，則四舍五入到月份第一天（以16 日為分界線）；如果y 的值為 day ，則四舍五入到天的開始（零點零分零秒，以中午12:00 為分界線）。例如：select sysdate , to_char(round(sysdate, day),yyyy-mm-dd hh24:mi:ss), round(sysdate, month), round(sysdate, year) from dual; 請讀者自行測試其查詢結(jié)果并體會。trunc(x, y

49、) 對日期 /時間進行進行截斷操作，其邏輯類似于數(shù)值處理中的向下取整。參數(shù)x 用于指定原始日期 /時間，參數(shù)y 用于指定截斷的方式如果y 的值為 year ，則截斷到當前年度的第一天；如果 y 的值為 month ，則截斷到當前月份的第一天；如果y 的值為 day ，則截斷到當天的開始14 工作 室（零點零分零秒）。next_day(x, y) 返回指定日期/時間之后的第一個“星期幾”所對應(yīng)的日期/時間（其小時、分、秒數(shù)值不變）。參數(shù) x 用于指定原始日期/時間，參數(shù) y 用于指定星期幾， 其值可以是整數(shù)17 （依次對應(yīng)星期日星期六）、或字符型的星期一 星期日 （中文語言環(huán)境）、或者monsu

50、n（英文語言環(huán)境）。例如：select sysdate, to_char(next_day(sysdate,4),yyyy-mm-dd hh24:mi:ss) from dual; -將當前會話語言環(huán)境改為簡體中文alter session set nls_language=simplified chinese; select sysdate, to_char(next_day(sysdate, 星期三 ),yyyy-mm-dd hh24:mi:ss) from dual; -將當前會話語言環(huán)境改為英文alter session set nls_language=american; selec

51、t sysdate, to_char(next_day(sysdate,wed),yyyy-mm-dd hh24:mi:ss) from dual; 4.2.4 轉(zhuǎn)換函數(shù) 轉(zhuǎn)換函數(shù)用于實現(xiàn)數(shù)據(jù)值的類型轉(zhuǎn)換，比如將字符型的數(shù)據(jù)值轉(zhuǎn)換為數(shù)值型或日期/時間類型（字符串解析），oracle 數(shù)據(jù)庫提供了多種轉(zhuǎn)換函數(shù)，其中常用的幾種如圖4-2 所示：圖 4-2 oracle 常用類型轉(zhuǎn)換函數(shù)下面分別作介紹，日期型 -字符型轉(zhuǎn)換函數(shù)to_char(d ,fmt) 函數(shù) to_char(d ,fmt) 用于將日期型數(shù)值轉(zhuǎn)換為字符串（varchar2 類型），其中參數(shù)d 用于指定日期值， fmt 用于指定要轉(zhuǎn)

52、換成的字符串的格式，也稱模式串。如不給出參數(shù)fmt 則系統(tǒng)會缺省采用與所在語言環(huán)境相關(guān)的默認格式。模式串中的字母（az, az ）也稱日期模式符，被保留使用相當于關(guān)鍵字，其中大部分被賦予特定含義，在對日期型數(shù)字的轉(zhuǎn)換/解析過程中這些模式符將被替換成相應(yīng)的時間域信息，表4-5 列出了常用格式符及其含義。15 16 工作 室表 4-5 常用日期模式符格式符說明舉例yyyy 年份2008 mm 用數(shù)字顯示月份02 dd 在當月中是第幾天28 day 星期幾星期五am / pm 顯示上午 /下午上午hh / hh12 / hh24 小時 / 12 小時制 / 24 小時制2:30 14:30 mi 分

53、鐘30 ss 秒鐘46 除上述格式符外，日期模式串中還可直接出現(xiàn)如下字符- : ; / ，如要顯示其它文本字符則需使用雙引號括起來；也可在模式串的開頭使用fm 標記以去掉數(shù)字前面的零。例如：select empno, hiredate, to_char(hiredate), to_char(hiredate,yyyy-mm-dd) from emp; select to_char(sysdate,yyyy-mm-dd hh:mi:ss) from dual; select to_char(sysdate,yyyy/mm/dd hh:mi:ss) from dual; select to_cha

54、r(sysdate,fmyyyy/mm/dd hh:mi:ss) from dual; select to_char(sysdate,yyyy年mm 月dd 日 day am hh:mi:ss) from dual; 請讀者自行測試其查詢結(jié)果并體會。字符型 -日期型轉(zhuǎn)換函數(shù)to_date(char ,fmt) 和前面的to_char()函數(shù)相對應(yīng)，函數(shù)to_char(char,fmt) 用于將字符串型數(shù)值轉(zhuǎn)換為日期型（date 類型），其中參數(shù)char 用于指定匹配于日期數(shù)據(jù)的字符串，模式串fmt 用于指定日期模式、即按照何種模式解析前面的字符串，如不給出參數(shù)fmt 則會按照當前語言環(huán)境相關(guān)的

55、默認格式進行解析。例如：drop table users; create table users(name v archar2(20), birth date); insert into users v alues( 張三 , 24-2 月-2009); insert into users v alues( 張三 , to_date(24-2 月 -2009); insert into users v alues( 李四 , to_date(2016-08-30, yyyy-mm-dd ); 其中，前兩條 insert 語句完全等價， 第一條語句中也是隱含調(diào)用了to_date()函數(shù)先將匹配系

56、統(tǒng)默認日期模式的字符串24-2 月-2009轉(zhuǎn)換為date 類型、再執(zhí)行新數(shù)據(jù)記錄的插入操作。但如果字符串的格式不匹配則會出錯，做為練習請讀者自行嘗試。數(shù)值型 -字符型轉(zhuǎn)換函數(shù)to_char(n ,fmt) 函數(shù) to_char(n ,fmt) 可將數(shù)字值轉(zhuǎn)換為varchar2型數(shù)據(jù)，通常用于數(shù)字值的格式化顯示。其中參數(shù)n 用于指定數(shù)字值，參數(shù)fmt 用于指定轉(zhuǎn)換成的格式，其性質(zhì)及用法與前面的日期模式工作 室串類似，具體如表4-6 所示。表 4-6 數(shù)字模式符格式符說明9 代表一位數(shù)字，忽略前導(dǎo)的0 0 代表一位數(shù)字，強制顯示0（如果位數(shù)不足，則用0 補齊）$ 放置一個美圓符$ l 放置一個本

57、地貨幣符c 放置一個國際貨幣符號. 小數(shù)點, 千位指示符如果不給出參數(shù)化fmt ，則系統(tǒng)將指定數(shù)字值原樣轉(zhuǎn)換為字符串，例如：select 12345.67 , to_char(12345.67) as c1, to_char(12345.67, $999,999.0000) as c2, to_char(12345.67, $000,000.0000) as c3 from dual; 上述語句的查詢返回結(jié)果如圖4-3 所示。圖 4-3 數(shù)字值格式化效果字符型 -數(shù)值型轉(zhuǎn)換函數(shù)to_number(char ,fmt) 與前述的to_char(n ,fmt) 相對應(yīng)， 函數(shù) to_number(

58、char ,fmt) 的功能則是將字符串型數(shù)值轉(zhuǎn)換/解析為數(shù)值型（number類型）。其中參數(shù)char 用于指定要解析的字符串，參數(shù)fmt 用于指定按照字符串的格式。如不給出參數(shù)fmt 則會按照當前語言環(huán)境相關(guān)的默認格式進行解析。例如：select to_number(12345.678901) from dual; select to_number($12,345.6789, $99,999.0000) from dual; drop table t1; create table t1(name v archar2(20), sal number); insert into t1 value

59、s( 王五 , to_number($12,345.6789, $99,999.0000); select * from t1; 請讀者自行測試其查詢結(jié)果并體會。17 18 工作 室4.2.5 通用函數(shù) 通用函數(shù)適用于任何類型數(shù)據(jù)（包括空值），一般用于實現(xiàn)空值處理、條件運算和多路分支結(jié)果，下面介紹其中常用的幾種：nvl(exp1, exp2) 函數(shù) nvl(exp1, exp2) 用于將空值轉(zhuǎn)換為指定的具體值先計算參數(shù)/表達式 exp1 的值，如果其值 null ，則返回exp2 的值，否則返回exp1 的值。參數(shù)exp1 和 exp2 可以是任意類型的常量、字段或復(fù)合表達式，但二者的數(shù)據(jù)類型

60、必須匹配。例如：select empno, ename, sal, comm, sal + nvl(comm, 0) from emp; select empno, ename, hiredate, nvl(hiredate, sysdate) from emp; select empno, ename, job, nvl(job, no job yet) from emp; 在數(shù)據(jù)統(tǒng)計中適當?shù)厥褂每罩缔D(zhuǎn)換函數(shù)nvl()可以避免空值的影響（參見第本書4.3 節(jié)）。nvl2(exp1, exp2, exp3) 函數(shù) nvl2(exp1, exp2, exp3) 用于實現(xiàn)條件表達式功能如果表達式e