Oracle主要數(shù)據(jù)類型

概述：各種數(shù)據(jù)庫所支持的數(shù)據(jù)類型大同小異，與標(biāo)準(zhǔn)SQL語言中的數(shù)據(jù)類型可能略有出入

類型：varchar2：可以在聲明字段時設(shè)置它的長度上限。而且使用它之后，就不必再考慮空格的存在

若插入的字符串的長度低于長度上限，系統(tǒng)就會自動將其縮減為字符串的真實長度

number(m,n)：既可以用來表示整型，也可以表示浮點型。但m不可以超過38。如果n為0或者省略n，就代表它是整數(shù)

date：用來存放日期和時間

blob：通常是在應(yīng)用程序中使用到它，而不是在數(shù)據(jù)庫中利用SQL指令直接使用

比如通過JDBC技術(shù)訪問數(shù)據(jù)庫，讀寫blob或clob類型的字段，即讀寫長的字符串信息等等

char：它是一種定長的字符類型。在Oracle數(shù)據(jù)庫不區(qū)分字符和字符串，它們被統(tǒng)稱為字符型或文本型

所謂定長的字符型是指，插入的字符串若沒有達(dá)到約定的字段長度，系統(tǒng)就會在字符串尾部自動補空格

同樣，讀取時的字段長度永遠(yuǎn)是聲明時的字段長度。而且在比較字符串內(nèi)容的時候，也需要考慮到空格的過濾

nchar：它也是定長的字符串類型，它是SQL語言標(biāo)準(zhǔn)中規(guī)定的，通常采用Unicode編碼來保存不同國家或不同語言的字符

varchar：SQL標(biāo)準(zhǔn)在定義varchar時并沒有保證能夠向前和向后兼容，即有可能隨著語言標(biāo)準(zhǔn)的修改而產(chǎn)生不兼容的問題

所以O(shè)racle定義了同varchar型類似的varchar2型。就是為了在Oracle以后的版本中，都永遠(yuǎn)支持varchar2類型

Oracle這么做就是為了確保此類型向前后兼容，以達(dá)到能夠在Oracle系列數(shù)據(jù)庫中進(jìn)行數(shù)據(jù)的導(dǎo)入和導(dǎo)出的目的

long：它和varchar2的差別在于，它不支持對字符串內(nèi)容進(jìn)行檢索，即查詢時不可以對它的內(nèi)容進(jìn)行條件查詢

而varchar2和char、nchar型等等都可以在查詢的時候直接檢索字符串的內(nèi)容

補充：select * from v$nls_parameters;數(shù)據(jù)庫的配置信息以數(shù)據(jù)表的形式存在，通常稱其為關(guān)于數(shù)據(jù)的數(shù)據(jù)或數(shù)據(jù)字典

實際上它查詢的是數(shù)據(jù)字典中的一個視圖。其中NLS_CHARACTERSET對應(yīng)的是當(dāng)前的數(shù)據(jù)庫字符集

缺省均為使用數(shù)據(jù)庫字符集。教程中使用的是安裝時默認(rèn)的ZHS16GBK字符集，即漢字占2個字節(jié)，英文占1個字節(jié)

而NLS_NCHAR_CHARACTERSET對應(yīng)的是nchar或nvarchar2類型所采用的輔助字符集，即AL16UTF16字符集

實際上AL16UTF16是一種16位定長的Unicode編碼的字符集。而數(shù)據(jù)庫字符集以及這種國家字符集都可以修改

但數(shù)據(jù)庫字符集修改后可能會面臨很嚴(yán)重的后果，除非是數(shù)據(jù)庫管理員，普通用戶不必對這方面進(jìn)行深究

數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入導(dǎo)出的時候，如果源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫所采用的字符集不同，也很容易出問題

函數(shù)

概述：函數(shù)可以認(rèn)為是能夠完成相對獨立的功能的一段代碼的集合。Oracle函數(shù)相當(dāng)于其它語言中的方法或過程

Oracle函數(shù)可以分為單行函數(shù)和多行函數(shù)兩大類。Oracle函數(shù)都是有返回值的

所謂的單行函數(shù)是針對查詢結(jié)果中的每一行都起作用，都會返回一個結(jié)果

多行函數(shù)也就是所謂分組函數(shù)，是針對一組查詢的記錄，或者說多行，返回一個結(jié)果

單行：操作數(shù)據(jù)項，接受參數(shù)并返回處理結(jié)果，對每一返回行均起作用，可修改數(shù)據(jù)類型，可嵌套使用

單行函數(shù)分為字符函數(shù)，數(shù)值函數(shù)，日期函數(shù)，轉(zhuǎn)換函數(shù)，通用函數(shù)

多行：也稱分組函數(shù)，即對一組數(shù)據(jù)進(jìn)行運算，針對一組數(shù)據(jù)(多行記錄)只能返回一個結(jié)果

多行函數(shù)包括avg()、count()、max()、min()、sum()等

比如select avg(sal), max(sal), min(sal), sum(sal), max(hiredate), min(hiredate) from emp;

續(xù)一：使用Oracle的系統(tǒng)函數(shù)中的單行函數(shù)可實現(xiàn)諸多功能，如對數(shù)據(jù)進(jìn)行計算，控制數(shù)據(jù)的輸出格式

設(shè)置和改變?nèi)掌诘娘@示格式，進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換，使用NVL等函數(shù)處理空值，實現(xiàn)IF-THEN-ELSE多路分支邏輯等等

續(xù)二：轉(zhuǎn)換函數(shù)不會改變表中數(shù)據(jù)的字段類型和值。它就相當(dāng)于將數(shù)據(jù)復(fù)制了一份，所轉(zhuǎn)換的是復(fù)制之后的數(shù)據(jù)

數(shù)據(jù)類型轉(zhuǎn)換包括隱含轉(zhuǎn)換和顯式轉(zhuǎn)換兩種方式，建議使用顯式的數(shù)據(jù)類型轉(zhuǎn)換，確保SQL語句的可靠性

續(xù)三：通用函數(shù)適用于包括空值在內(nèi)的任何類型數(shù)據(jù)。通常用來實現(xiàn)空值的處理，空值的過濾或設(shè)置缺省值等

通用函數(shù)包括nvl()、nvl2()、nullif()、coalesce()、case表達(dá)式、decode()等

嵌套：單行函數(shù)可以嵌套使用，嵌套層次無限制。分組函數(shù)最多可嵌套兩層。嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外

單行比如select empno,lpad(initcap(trim(ename)),10,' ') 姓名, job, sal from emp;

多行比如select max(avg(sal)) from emp group by deptno;--其實這里再使用分組函數(shù)就沒有意義了

說明：通常數(shù)據(jù)庫層面提供的函數(shù)，只是進(jìn)行數(shù)據(jù)的簡單的處理，或者說是只能實現(xiàn)極為常規(guī)的功能

所以就不應(yīng)該，或者說是不要指望在數(shù)據(jù)庫查詢的層面來實現(xiàn)特別復(fù)雜的業(yè)務(wù)邏輯

如果應(yīng)用程序的邏輯跟數(shù)據(jù)庫混在一起的話，會不利于代碼的維護(hù)和更新

而且也不利于數(shù)據(jù)庫的管理，包括數(shù)據(jù)移植，數(shù)據(jù)庫導(dǎo)入導(dǎo)出等等

日期類型

概述：在計算機(jī)操作系統(tǒng)或者各種高級編程語言中，日期通常會被保存成一個長整數(shù)，通常記錄的是毫秒

Oracle內(nèi)部以數(shù)字格式存儲日期和時間信息：世紀(jì),年,月,日,小時,分鐘,秒

缺省的日期格式是DD—MON—YY。可使用sysdata函數(shù)獲取當(dāng)前系統(tǒng)日期和時間

運算：日期型數(shù)據(jù)可以直接加或減一個數(shù)值，結(jié)果認(rèn)為日期。約定的該數(shù)值代表的是相加減的天數(shù)

兩個日期型數(shù)據(jù)可以相減，結(jié)果為二者相差多少天。二者不能，因為日期相加是沒有意義的

NVL()函數(shù)

概述：它用于將空值null替換為指定的缺省值，適用于字符、數(shù)字、日期等類型數(shù)據(jù)

格式：NVL(exp1,exp2)--如果表達(dá)式exp1值為null，則返回exp2值，否則返回exp1值

舉例：select empno, ename, sal, comm, sal+nvl(comm,0) from emp;

select empno, ename, job, nvl(job, 'No job yet') from emp;

NVL2()函數(shù)

概述：它用于實現(xiàn)條件表達(dá)式功能

格式：NVL2(exp1,exp2,exp3)--如果表達(dá)式exp1值不為null，則返回exp2值，否則返回exp3值

舉例：select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) 總收入 from emp;

NULLIF()函數(shù)

概述：它用于數(shù)據(jù)等價性比較并根據(jù)比較結(jié)果返回null或其中一個被比較的數(shù)值。實際開發(fā)中應(yīng)用并不是很多

格式：nullif(exp1,exp2)--如果表達(dá)式exp1與exp2的值相等，則返回null，否則返回exp1的值

舉例：select name 原名, nullif(pen_name, name) 化名 from author;

COALESCE()函數(shù)

概述：它用于實現(xiàn)數(shù)據(jù)“接合”功能

格式：coalesec(exp1,exp2,...)--依次考察各參數(shù)表達(dá)式，遇到非null值即停止并返回該值

若表達(dá)式均為null值，則返回null。通常最后一個表達(dá)式都是能確保不是空值的字段

舉例：select empno, ename, sal, comm, coalesec(sal+comm,sal,0) 總收入 from emp;

CASE表達(dá)式

概述：它用于實現(xiàn)多路分支結(jié)構(gòu)

格式：case exp when comparison_exp1 then return_exp1

[when comparison_exp2 then return_exp2

when comparison_expn then return_expn

else else_exp]

end

舉例：select empno, ename, sal,

case deptno when 10 then '財務(wù)部'

when 20 then '研發(fā)部'

when 30 then '銷售部'

else '未知部門'

end 部門

from emp;

說明：CASE中的每一個表達(dá)式(如deptno，10，財務(wù)部等)都可以是復(fù)合而成的

這種對齊方式的書寫是為了增加可讀性，當(dāng)然也可以把代碼寫在同一行上

其中case到end之間的整體就相當(dāng)于普通查詢中的一個字段，end后面的“部門”是別名

DEDODE()函數(shù)

概述：和case表達(dá)式類似，它也用于實現(xiàn)多路分支結(jié)構(gòu)

格式：decode(col|expression, search1, result1

[, search2, result2,...,]

[, default])

舉例：select empno, ename, sal,

decode(deptno, 10, '財務(wù)部',

20, '研發(fā)部',

30, '銷售部',

'未知部門')

部門

from emp;

COUNT()函數(shù)

格式：count(*)返回組中總記錄數(shù)目

count(exp)返回表達(dá)式exp值非空的記錄

count(distinct(exp))返回表達(dá)式exp值不重復(fù)的、非空的記錄數(shù)目

舉例：select count(*) from emp;--缺省的情況下，整個表就是一組

select count(comm) from emp;--返回emp表中comm字段不為空的記錄(行)數(shù)目

select count(distinct(deptno)) from emp;--查找deptno值為非空且不重復(fù)的記錄數(shù)目

分組函數(shù)與空值

概述：分組函數(shù)省略列中的空值?？墒褂肗VL()函數(shù)強制分組函數(shù)處理空值

舉例：select avg(comm) from emp;--等價于sum(comm)/count(comm)

select sum(comm) from emp;--計算表中非空的comm值的總和

select avg(nvl(comm,0)) from emp;--等價于avg(nvl(comm,0))/count(*)

GROUP BY子句

概述：它用于將表中數(shù)據(jù)分成若干小組

格式：select column, group_function(column)

from table

[where condition]

[group by group_by_expression]

[order by column];

舉例：select deptno, avg(sal) from emp group by deptno;

說明：出現(xiàn)在SELECT列表中的字段，如果不是包含在組函數(shù)中，那么該字段必須同時在GROUP BY子句中出現(xiàn)

包含在GROPY BY子句中的字段則不必須出現(xiàn)在SELECT列表中。子句執(zhí)行順序是where→group by→order by缺省按升序排列

補充：select deptno, job, avg(sal) from emp group by deptno, job order by deptno desc;--基于多個字段的分組

select deptno, avg(sal) from emp;--非法

注意：如果沒有GROUP BY子句，SELECT列表中不允許出現(xiàn)字段(單行函數(shù))與分組函數(shù)混用的情況

WHERE中不允許使用分組函數(shù)，如select deptno, avg(sal) from emp where avg(sal)>2000 group by deptno;--非法

這跟子句執(zhí)行的順序有關(guān)。where子句最先執(zhí)行，在執(zhí)行where子句的時候還沒有執(zhí)行過group by子句

于是程序不知道這是在分組，也不曾計算過avg(sal)的組內(nèi)平均工資，所以在where子句中不允許使用分組函數(shù)

由于還沒有執(zhí)行過group by子句，所以此時就不確定如何怎么分組以及分多少個組

所以where子句中只能進(jìn)行初級過濾。此時可以使用HAVING子句實現(xiàn)對平均工資的過濾

HAVING子句

概述：它用于過濾分組

格式：select column, group_function(column)

from table

[where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

舉例：select deptno, job, avg(sal)

from emp

where hiredate >= to_date('1981-05-01','yyyy-mm-dd')

group by by deptno, job

having avg(sal) > 1200

order by deptno, job;

更多信息請查看IT技術(shù)專欄