萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> oracle教程 >> Oracle數據庫入門之函數/類型

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值的總和

<
copyright © 萬盛學電腦網 all rights reserved