Oracle主要數據類型
概述各種數據庫所支持的數據類型大同小異與標准SQL語言中的數據類型可能略有出入
類型varchar可以在聲明字段時設置它的長度上限而且使用它之後就不必再考慮空格的存在
若插入的字符串的長度低於長度上限系統就會自動將其縮減為字符串的真實長度
number(mn)既可以用來表示整型也可以表示浮點型但m不可以超過如果n為或者省略n就代表它是整數
date用來存放日期和時間
blob通常是在應用程序中使用到它而不是在數據庫中利用SQL指令直接使用
比如通過JDBC技術訪問數據庫讀寫blob或clob類型的字段即讀寫長的字符串信息等等
char它是一種定長的字符類型在Oracle數據庫不區分字符和字符串它們被統稱為字符型或文本型
所謂定長的字符型是指插入的字符串若沒有達到約定的字段長度系統就會在字符串尾部自動補空格
同樣讀取時的字段長度永遠是聲明時的字段長度而且在比較字符串內容的時候也需要考慮到空格的過濾
nchar它也是定長的字符串類型它是SQL語言標准中規定的通常采用Unicode編碼來保存不同國家或不同語言的字符
varcharSQL標准在定義varchar時並沒有保證能夠向前和向後兼容即有可能隨著語言標准的修改而產生不兼容的問題
所以Oracle定義了同varchar型類似的varchar型就是為了在Oracle以後的版本中都永遠支持varchar類型
Oracle這麼做就是為了確保此類型向前後兼容以達到能夠在Oracle系列數據庫中進行數據的導入和導出的目的
long它和varchar的差別在於它不支持對字符串內容進行檢索即查詢時不可以對它的內容進行條件查詢
而varchar和charnchar型等等都可以在查詢的時候直接檢索字符串的內容
補充select * from v$nls_parameters;數據庫的配置信息以數據表的形式存在通常稱其為關於數據的數據或數據字典
實際上它查詢的是數據字典中的一個視圖其中NLS_CHARACTERSET對應的是當前的數據庫字符集
缺省均為使用數據庫字符集教程中使用的是安裝時默認的ZHSGBK字符集即漢字占個字節英文占個字節
而NLS_NCHAR_CHARACTERSET對應的是nchar或nvarchar類型所采用的輔助字符集即ALUTF字符集
實際上ALUTF是一種位定長的Unicode編碼的字符集而數據庫字符集以及這種國家字符集都可以修改
但數據庫字符集修改後可能會面臨很嚴重的後果除非是數據庫管理員普通用戶不必對這方面進行深究
數據庫中的數據導入導出的時候如果源數據庫和目標數據庫所采用的字符集不同也很容易出問題
函數
概述函數可以認為是能夠完成相對獨立的功能的一段代碼的集合Oracle函數相當於其它語言中的方法或過程
Oracle函數可以分為單行函數和多行函數兩大類Oracle函數都是有返回值的
所謂的單行函數是針對查詢結果中的每一行都起作用都會返回一個結果
多行函數也就是所謂分組函數是針對一組查詢的記錄或者說多行返回一個結果
單行操作數據項接受參數並返回處理結果對每一返回行均起作用可修改數據類型可嵌套使用
單行函數分為字符函數數值函數日期函數轉換函數通用函數
多行也稱分組函數即對一組數據進行運算針對一組數據(多行記錄)只能返回一個結果
多行函數包括avg()count()max()min()sum()等
比如select avg(sal) max(sal) min(sal) sum(sal) max(hiredate) min(hiredate) from emp;
續一使用Oracle的系統函數中的單行函數可實現諸多功能如對數據進行計算控制數據的輸出格式
設置和改變日期的顯示格式進行數據類型轉換使用NVL等函數處理空值實現IFTHENELSE多路分支邏輯等等
續二轉換函數不會改變表中數據的字段類型和值它就相當於將數據復制了一份所轉換的是復制之後的數據
數據類型轉換包括隱含轉換和顯式轉換兩種方式建議使用顯式的數據類型轉換確保SQL語句的可靠性
續三通用函數適用於包括空值在內的任何類型數據通常用來實現空值的處理空值的過濾或設置缺省值等
通用函數包括nvl()nvl()nullif()coalesce()case表達式decode()等
嵌套單行函數可以嵌套使用嵌套層次無限制分組函數最多可嵌套兩層嵌套函數的執行順序是由內到外
單行比如select empnolpad(initcap(trim(ename)) ) 姓名 job sal from emp;
多行比如select max(avg(sal)) from emp group by deptno;其實這裡再使用分組函數就沒有意義了
說明通常數據庫層面提供的函數只是進行數據的簡單的處理或者說是只能實現極為常規的功能
所以就不應該或者說是不要指望在數據庫查詢的層面來實現特別復雜的業務邏輯
如果應用程序的邏輯跟數據庫混在一起的話會不利於代碼的維護和更新
而且也不利於數據庫的管理包括數據移植數據庫導入導出等等
日期類型
概述在計算機操作系統或者各種高級編程語言中日期通常會被保存成一個長整數通常記錄的是毫秒
Oracle內部以數字格式存儲日期和時間信息世紀年月日小時分鐘秒
缺省的日期格式是DD—MON—YY可使用sysdata函數獲取當前系統日期和時間
運算日期型數據可以直接加或減一個數值結果認為日期約定的該數值代表的是相加減的天數
兩個日期型數據可以相減結果為二者相差多少天二者不能因為日期相加是沒有意義的
NVL()函數
概述它用於將空值null替換為指定的缺省值適用於字符數字日期等類型數據
格式NVL(expexp)如果表達式exp值為null則返回exp值否則返回exp值
舉例select empno ename sal comm sal+nvl(comm) from emp;
select empno ename job nvl(job No job yet) from emp;
NVL()函數
概述它用於實現條件表達式功能
格式NVL(expexpexp)如果表達式exp值不為null則返回exp值否則返回exp值
舉例select empno ename sal comm nvl(comm sal+comm sal) 總收入 from emp;
NULLIF()函數
概述它用於數據等價性比較並根據比較結果返回null或其中一個被比較的數值實際開發中應用並不是很多
格式nullif(expexp)如果表達式exp與exp的值相等則返回null否則返回exp的值
舉例select name 原名 nullif(pen_name name) 化名 from author;
COALESCE()函數
概述它用於實現數據“接合”功能
格式coalesec(expexp)依次考察各參數表達式遇到非null值即停止並返回該值
若表達式均為null值則返回null通常最後一個表達式都是能確保不是空值的字段
舉例select empno ename sal comm coalesec(sal+commsal) 總收入 from emp;
CASE表達式
概述它用於實現多路分支結構
格式case exp when comparison_exp then return_exp
[when comparison_exp then return_exp
when comparison_expn then return_expn
else else_exp]
end
舉例select empno ename sal
case deptno when then 財務部
when then 研發部
when then 銷售部
else 未知部門
end 部門
from emp;
說明CASE中的每一個表達式(如deptno財務部等)都可以是復合而成的
這種對齊方式的書寫是為了增加可讀性當然也可以把代碼寫在同一行上
其中case到end之間的整體就相當於普通查詢中的一個字段end後面的“部門”是別名
DEDODE()函數
概述和case表達式類似它也用於實現多路分支結構
格式decode(col|expression search result
[ search result]
[ default])
舉例select empno ename sal
decode(deptno 財務部
研發部
銷售部
未知部門)
部門
from emp;
COUNT()函數
格式count(*)返回組中總記錄數目
count(exp)返回表達式exp值非空的記錄
count(distinct(exp))返回表達式exp值不重復的非空的記錄數目
舉例select count(*) from emp;缺省的情況下整個表就是一組
select count(comm) from emp;返回emp表中comm字段不為空的記錄(行)數目
select count(distinct(deptno)) from emp;查找deptno值為非空且不重復的記錄數目
分組函數與空值
概述分組函數省略列中的空值可使用NVL()函數強制分組函數處理空值
舉例select avg(comm) from emp;等價於sum(comm)/count(comm)
select sum(comm) from emp;計算表中非空的comm值的總和
select avg(nvl(comm)) from emp;等價於avg(nvl(comm))/count(*)
GROUP BY子句
概述它用於將表中數據分成若干小組
格式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;
說明出現在SELECT列表中的字段如果不是包含在組函數中那麼該字段必須同時在GROUP BY子句中出現
包含在GROPY BY子句中的字段則不必須出現在SELECT列表中子句執行順序是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列表中不允許出現字段(單行函數)與分組函數混用的情況
WHERE中不允許使用分組函數如select deptno avg(sal) from emp where avg(sal)> group by deptno;非法
這跟子句執行的順序有關where子句最先執行在執行where子句的時候還沒有執行過group by子句
於是程序不知道這是在分組也不曾計算過avg(sal)的組內平均工資所以在where子句中不允許使用分組函數
由於還沒有執行過group by子句所以此時就不確定如何怎麼分組以及分多少個組
所以where子句中只能進行初級過濾此時可以使用HAVING子句實現對平均工資的過濾
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(yyyymmdd)
group by by deptno job
having avg(sal) >
order by deptno job;
From:http://tw.wingwit.com/Article/program/Oracle/201311/19087.html
