Oracle資料庫入門之函數/類型

　　Oracle主要資料類型

　　概述：各種資料庫所支援的資料類型大同小異，與標準SQL語言中的資料類型可能略有出入

　　類型：varchar2：可以在聲明欄位時設定它的長度上限。而且使用它之後，就不必再考慮空格的存在

　　若插入的字串的長度低於長度上限，系統就會自動將其縮減為字串的真實長度

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

　　date：用來存放日期和時間

　　blob：通常是在應用程式中使用到它，而不是在資料庫中利用SQL指令直接使用

　　比如通過JDBC技術訪問資料庫，讀寫blob或clob類型的欄位，即讀寫長的字串資訊等等

　　char：它是一種定長的字元類型。在Oracle資料庫不區分字元和字串，它們被統稱為字元型或文本型

　　所謂定長的字元型是指，插入的字串若沒有達到約定的欄位長度，系統就會在字串尾部自動補空格

　　同樣，讀取時的欄位長度永遠是聲明時的欄位長度。而且在比較字串內容的時候，也需要考慮到空格的過濾

　　nchar：它也是定長的字串類型，它是SQL語言標準中規定的，通常採用Unicode編碼來儲存不同國家或不同語言的字元

　　varchar：SQL標準在定義varchar時並沒有保證能夠向前和向後相容，即有可能隨著語言標準的修改而產生不相容的問題

　　所以Oracle定義了同varchar型類似的varchar2型。就是為了在Oracle以後的版本中，都永遠支援varchar2類型

　　Oracle這麼做就是為了確保此類型向前後相容，以達到能夠在Oracle系列資料庫中進行資料的匯入和匯出的目的

　　long：它和varchar2的差別在於，它不支援對字串內容進行檢索，即查詢時不可以對它的內容進行條件查詢

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

　　補充：select * from v$nls_parameters;資料庫的配置資訊以資料表的形式存在，通常稱其為關於資料的資料或資料字典

　　實際上它查詢的是資料字典中的一個視圖。其中NLS_CHARACTERSET對應的是當前的資料庫字元集

　　預設均為使用資料庫字元集。教程中使用的是安裝時預設的ZHS16GBK字元集，即漢字佔2個位元組，英文佔1個位元組

　　而NLS_NCHAR_CHARACTERSET對應的是nchar或nvarchar2類型所採用的輔助字元集，即AL16UTF16字元集

　　實際上AL16UTF16是一種16位定長的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等函數處理空值，實現IF-THEN-ELSE多路分支邏輯等等

　　續二：轉換函式不會改變表中資料的欄位類型和值。它就相當於將資料複製了一份，所轉換的是複製之後的資料

　　資料類型轉換包括隱含轉換和顯式轉換兩種方式，建議使用顯式的資料類型轉換，確保SQL語句的可靠性

　　續三：通用函數適用於包括空值在內的任何類型資料。通常用來實現空值的處理，空值的過濾或設定預設值等

　　通用函數包括nvl()、nvl2()、nullif()、coalesce()、case運算式、decode()等

　　嵌套：單行函數可以嵌套使用，嵌套層次無限制。分組函數最多可嵌套兩層。嵌套函數的執行順序是由內到外

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

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

　　說明：通常資料庫層面提供的函數，只是進行資料的簡單的處理，或者說是只能實現極為常規的功能

　　所以就不應該，或者說是不要指望在資料庫查詢的層面來實現特別複雜的商務邏輯

　　如果應用程式的邏輯跟資料庫混在一起的話，會不利於代碼的維護和更新

　　而且也不利於資料庫的管理，包括資料移植，資料庫匯入匯出等等

　　日期類型

　　概述：在電腦作業系統或者各種進階程式設計語言中，日期通常會被儲存成一個長整數，通常記錄的是毫秒

　　Oracle內部以數字格式儲存日期和時間資訊：世紀,年,月,日,小時,分鐘,秒

　　預設的日期格式是DD—MON—YY。可使用sysdata函數擷取當前系統日期和時間

　　運算：日期型資料可以直接加或減一個數值，結果認為日期。約定的該數值代表的是相加減的天數

　　兩個日期型資料可以相減，結果為二者相差多少天。二者不能，因為日期相加是沒有意義的

　　NVL()函數

　　概述：它用於將空值null替換為指定的預設值，適用於字元、數字、日期等類型資料

　　格式：NVL(exp1,exp2)--如果運算式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()函數

　　概述：它用於實現條件運算式功能

　　格式：NVL2(exp1,exp2,exp3)--如果運算式exp1值不為null，則返回exp2值，否則返回exp3值

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

　　NULLIF()函數

　　概述：它用於資料等價性比較並根據比較結果返回null或其中一個被比較的數值。實際開發中應用並不是很多

　　格式：nullif(exp1,exp2)--如果運算式exp1與exp2的值相等，則返回null，否則返回exp1的值

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

　　COALESCE()函數

　　概述：它用於實現資料“接合”功能

　　格式：coalesec(exp1,exp2,...)--依次考察各參數運算式，遇到非null值即停止並返回該值

　　若運算式均為null值，則返回null。通常最後一個運算式都是能確保不是空值的欄位

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

　　CASE運算式

　　概述：它用於實現多路分支結構

　　格式：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 '財務部'

　　when 20 then '研發部'

　　when 30 then '銷售部'

　　else '未知部門'

　　end 部門

　　from emp;

　　說明：CASE中的每一個運算式(如deptno，10，財務部等)都可以是複合而成的

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

　　其中case到end之間的整體就相當於普通查詢中的一個欄位，end後面的“部門”是別名

　　DEDODE()函數

　　概述：和case運算式類似，它也用於實現多路分支結構

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

　　[, search2, result2,...,]

　　[, default])

　　舉例：select empno, ename, sal,

　　decode(deptno, 10, '財務部',

　　20, '研發部',

　　30, '銷售部',

　　'未知部門')

　　部門

　　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,0)) from emp;--等價於avg(nvl(comm,0))/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)>2000 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('1981-05-01','yyyy-mm-dd')

　　group by by deptno, job

　　having avg(sal) > 1200

　　order by deptno, job;