熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle函數列表速查

2013-11-13 22:22:42  來源: Oracle 

  PL/SQL單行函數和組函數詳解
  
  函數是一種有零個或多個參數並且有一個返回值的程序在SQL中Oracle內建了一系列函數這些函數都可被稱為SQL或PL/SQL語句函數主要分為兩大類
  
  單行函數
  
  組函數
  
  本文將討論如何利用單行函數以及使用規則
  
  SQL中的單行函數
  
  SQL和PL/SQL中自帶很多類型的函數有字符數字日期轉換和混合型等多種函數用於處理單行數據因此這些都可被統稱為單行函數這些函數均可用於SELECTWHEREORDER BY等子句中例如下面的例子中就包含了TO_CHARUPPERSOUNDEX等單行函數
  SELECT enameTO_CHAR(hiredatedayDDMonYYYY)FROM empWhere UPPER(ename) Like AL%ORDER BY SOUNDEX(ename)
  
  單行函數也可以在其他語句中使用如update的SET子句INSERT的VALUES子句DELET的WHERE子句認證考試特別注意在SELECT語句中使用這些函數所以我們的注意力也集中在SELECT語句中
  
  NULL和單行函數
  
  在如何理解NULL上開始是很困難的就算是一個很有經驗的人依然對此感到困惑NULL值表示一個未知數據或者一個空值算術操作符的任何一個操作數為NULL值結果均為提個NULL值這個規則也適合很多函數只有CONCATDECODEDUMPNVLREPLACE在調用了NULL參數時能夠返回非NULL值在這些中NVL函數時最重要的因為他能直接處理NULL值NVL有兩個參數NVL(xx)x和x都式表達式當x為null時返回X否則返回x
  
  下面我們看看emp數據表它包含了薪水獎金兩項需要計算總的補償
  column name emp_id salary bonuskey type pk nulls/unique nnu nnfk table datatype number number numberlength
  
  不是簡單的將薪水和獎金加起來就可以了如果某一行是null值那麼結果就將是null比如下面的例子
  update empset salary=(salary+bonus)*
  
  這個語句中雇員的工資和獎金都將更新為一個新的值但是如果沒有獎金即 salary + null那麼就會得出錯誤的結論這個時候就要使用nvl函數來排除null值的影響
  
  所以正確的語句是
  update empset salary=(salary+nvl(bonus)*
  
  單行字符串函數
  
  單行字符串函數用於操作字符串數據他們大多數有一個或多個參數其中絕大多數返回字符串
  
  ASCII()
  c是一字符串返回c第一個字母的ASCII碼他的逆函數是CHR()
  SELECT ASCII(A) BIG_AASCII(z) BIG_z FROM empBIG_A BIG_z
  
  CHR(<i>)[NCHAR_CS]
  
  i是一個數字函數返回十進制表示的字符
  
  select CHR()CHR()CHR() FROM empCHR CHR CHRA z B
  
  CONCAT()
  cc均為字符串函數將c連接到c的後面如果c為null將返回c如果c為null則返回c如果cc都為null則返回null他和操作符||返回的結果相同
  select concat(slobo Svoboda) username from dualusernameslobo Syoboda
  
  INITCAP()
  c為一字符串函數將每個單詞的第一個字母大寫其它字母小寫返回單詞由空格控制字符標點符號限制
  select INITCAP(venivedivici) Ceasar from dualCeasarVeniVediVici
  
  INSTR([<i>[]])
  cc均為字符串ij為整數函數返回c在c中第j次出現的位置搜索從c的第i個字符開始當沒有發現需要的字符時返回如果i為負數那麼搜索將從右到左進行但是位置的計算還是從左到右i和j的缺省值為
  select INSTR(Mississippii) from dualINSTR(MISSISSIPPII)select INSTR(Mississippii) from dualINSTR(MISSISSIPPII)
  
  INSTRB([i[j])
  與INSTR()函數一樣只是他返回的是字節對於單字節INSTRB()等於INSTR()
  
  LENGTH()
  c為字符串返回c的長度如果c為null那麼將返回null值
  select LENGTH(Ipso Facto) ergo from dualergo
  
  LENGTHb()
  與LENGTH()一樣返回字節
  
  lower()
  返回c的小寫字符經常出現在where子串中
  select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE %white%COLORNAMEWinterwhite
  
  LPAD(<i>[])
  cc均為字符串i為整數在c的左側用c字符串補足致長度i可多次重復如果i小於c的長度那麼只返回i那麼長的c字符其他的將被截去c的缺省值為單空格參見RPAD
  select LPAD(answer) paddedanswer unpadded from question;PADDED UNPADDED Yes YesNO NOMaybe maybe
  
  LTRIM()
  把c中最左邊的字符去掉使其第一個字符不在c如果沒有c那麼c就不會改變
  select LTRIM(MississippiMis) from dualLTRppi
  
  RPAD(<i>[])
  在c的右側用c字符串補足致長度i可多次重復如果i小於c的長度那麼只返回i那麼長的c字符其他的將被截去c的缺省值為單空格其他與LPAD相似
  
  RTRIM()
  把c中最右邊的字符去掉使其第後一個字符不在c如果沒有c那麼c就不會改變
  
  REPLACE([])
  ccc都是字符串函數用c代替出現在c中的c後返回
  select REPLACE(uptownupdown) from dualREPLACEdowntown
  
  STBSTR(<i>[])
  c為一字符串ij為整數從c的第i位開始返回長度為j的子字符串如果j為空則直到串的尾部
  select SUBSTR(Message) from dualSUBSMess
  
  SUBSTRB(<i>[])
  與SUBSTR大致相同只是IJ是以字節計算
  
  SOUNDEX()
  返回與c發音相似的詞
  select SOUNDEX(dawes) Dawes SOUNDEX(daws) Daws SOUNDEX(dawson) from dualDawes Daws DawsonD D D
  
  TRANSLATE()
  將c中與c相同的字符以c代替
  select TRANSLATE(fumbleufar) test from dualTEXTramble
  
  TRIM([[]] from c)
  將c串中的第一個最後一個或者都刪除
  select TRIM( space padded ) trim from dual TRIMspace padded
  
  UPPER()
  返回c的大寫常出現where子串中
  select name from dual where UPPER(name) LIKE KI%NAMEKING
  
  單行數字函數
  
  單行數字函數操作數字數據執行數學和算術運算所有函數都有數字參數並返回數字值所有三角函數的操作數和值都是弧度而不是角度oracle沒有提供內建的弧度和角度的轉換函數
  
  ABS()
  返回n的絕對值
  
  ACOS()
  反余玄函數返回之間的數n表示弧度
  select ACOS() piACOS() ZERO FROM dualPI ZERO
  
  ASIN()
  反正玄函數返回n表示弧度
  
  ATAN()
  反正切函數返回n的反正切值n表示弧度
  
  CEIL()
  返回大於或等於n的最小整數
  
  COS()
  返回n的余玄值n為弧度
  
  COSH()
  返回n的雙曲余玄值n 為數字
  select COSH(<1.4>) FROM dualCOSH()
  
  EXP()
  返回e的n次冪e=
  
  FLOOR()
  返回小於等於N的最大整數
  
  LN()
  返回N的自然對數N必須大於
  
  LOG()
  返回以n為底n的對數
  
  MOD()
  返回n除以n的余數
  
  POWER()
  返回n的n次方
  
  ROUND()
  返回捨入小數點右邊n位的n的值n的缺省值為這回將小數點最接近的整數如果n為負數就捨入到小數點左邊相應的位上n必須是整數
  select ROUND()ROUND() FROM dualROUND() ROUND()
  
  SIGN()
  如果n為負數返回如果n為正數返回如果n=返回
  
  SIN()
  返回n的正玄值n為弧度
  
  SINH()
  返回n的雙曲正玄值n為弧度
  
  SQRT()
  返回n的平方根n為弧度
  
  TAN()
  返回n的正切值n為弧度
  
  TANH()
  返回n的雙曲正切值n為弧度
  
  TRUNC()
  返回截尾到n位小數的n的值n缺省設置為當n為缺省設置時會將n截尾為整數如果n為負值就截尾在小數點左邊相應的位上
  
  單行日期函數
  
  單行日期函數操作DATA數據類型絕大多數都有DATA數據類型的參數絕大多數返回的也是DATA數據類型的值
  
  ADD_MONTHS(<i>)
  返回日期d加上i個月後的結果i可以使任意整數如果i是一個小數那麼數據庫將隱式的他轉換成整數將會截去小數點後面的部分
  
  LAST_DAY()
  函數返回包含日期d的月份的最後一天
  
  MONTHS_BETWEEN()
  返回d和d之間月的數目如果d和d的日的日期都相同或者都使該月的最後一天那麼將返回一個整數否則會返回的結果將包含一個分數
  
  NEW_TIME()
  d是一個日期數據類型當時區tz中的日期和時間是d時返回時區tz中的日期和時間tz和tz時字符串
  
  NEXT_DAY()
  返回日期d後由dow給出的條件的第一天dow使用當前會話中給出的語言指定了一周中的某一天返回的時間分量與d的時間分量相同
  select NEXT_DAY(JanMonday) st MondayNEXT_DAY(NovTuesday)+ nd Tuesday) from dual;st Monday nd TuesdayJan Nov
  
  ROUND([])
  將日期d按照fmt指定的格式捨入fmt為字符串
  
  SYADATE
  函數沒有參數返回當前日期和時間
  
  TRUNC([])
  返回由fmt指定的單位的日期d
  
  單行轉換函數
  
  單行轉換函數用於操作多數據類型在數據類型之間進行轉換
  
  CHARTORWID()
  c 使一個字符串函數將c轉換為RWID數據類型
  SELECT test_id from test_case where rowid=CHARTORWID(AAAASAACAAAALiAAA)
  
  CONVERT([])
  c尾字符串dsetsset是兩個字符集函數將字符串c由sset字符集轉換為dset字符集sset的缺省設置為數據庫的字符集
  
  HEXTORAW()
  x為進制的字符串函數將進制的x轉換為RAW數據類型
  
  RAWTOHEX()
  x是RAW數據類型字符串函數將RAW數據類轉換為進制的數據類型
  
  ROWIDTOCHAR()
  函數將ROWID數據類型轉換為CHAR數據類型
  
  TO_CHAR([[)
  x是一個data或number數據類型函數將x轉換成fmt指定格式的char數據類型如果x為日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的語言如果x為數字nlsparm=NLS_NUMERIC_CHARACTERS 用來指定小數位和千分位的分隔符以及貨幣符號
  NLS_NUMERIC_CHARACTERS =dg NLS_CURRENCY=string
  
  TO_DATE([[
  c表示字符串fmt表示一種特殊格式的字符串返回按照fmt格式顯示的cnlsparm表示使用的語言函數將字符串c轉換成date數據類型
  
  TO_MULTI_BYTE()
  c表示一個字符串函數將c的擔子截字符轉換成多字節字符
  
  TO_NUMBER([[)
  c表示字符串fmt表示一個特殊格式的字符串函數返回值按照fmt指定的格式顯示nlsparm表示語言函數將返回c代表的數字
  
  TO_SINGLE_BYTE()
  將字符串c中得多字節字符轉化成等價的單字節字符該函數僅當數據庫字符集同時包含單字節和多字節字符時才使用
  
  其它單行函數
  
  BFILENAME(
  )
  dir是一個directory類型的對象file為一文件名函數返回一個空的BFILE位置值指示符函數用於初始化BFILE變量或者是BFILE列
  
  DECODE([[])
  x是一個表達式m是一個匹配表達式x與m比較如果m等於x那麼返回r否則x與m比較依次類推mmm直到有返回結果
  
  DUMP([[[]]])
  x是一個表達式或字符fmt表示進制進制進制或則單字符函數返回包含了有關x的內部表示信息的VARCHAR類型的值如果指定了nn那麼從n開始的長度為n的字節將被返回
  
  EMPTY_BLOB()
  該函數沒有參數函數返回 一個空的BLOB位置指示符函數用於初始化一個BLOB變量或BLOB列
  
  EMPTY_CLOB()
  該函數沒有參數函數返回 一個空的CLOB位置指示符函數用於初始化一個CLOB變量或CLOB列
  
  GREATEST()
  exp_list是一列表達式返回其中最大的表達式每個表達式都被隱含的轉換第一個表達式的數據類型如果第一個表達式是字符串數據類型中的任何一個那麼返回的結果是varchar數據類型同時使用的比較是非填充空格類型的比較
  
  LEAST()
  exp_list是一列表達式返回其中最小的表達式每個表達式都被隱含的轉換第一個表達式的數據類型如果第一個表達式是字符串數據類型中的任何一個將返回的結果是varchar數據類型同時使用的比較是非填充空格類型的比較
  
  UID
  該函數沒有參數返回唯一標示當前數據庫用戶的整數
  
  USER
  返回當前用戶的用戶名
  
  USERENV()
  基於opt返回包含當前會話信息opt的可選值為
  
  ISDBA    會話中SYSDBA腳色響應返回TRUE
  SESSIONID  返回審計會話標示符
  ENTRYID   返回可用的審計項標示符
  INSTANCE  在會話連接後返回實例標示符該值只用於運行Parallel 服務器並且有 多個實例的情況下使用
  LANGUAGE  返回語言地域數據庫設置的字符集
  LANG    返回語言名稱的ISO縮寫
  TERMINAL  為當前會話使用的終端或計算機返回操作系統的標示符
  
  VSIZE()
  x是一個表達式返回x內部表示的字節數
  
  SQL中的組函數
  
  組函數也叫集合函數返回基於多個行的單一結果行的准確數量無法確定除非查詢被執行並且所有的結果都被包含在內與單行函數不同的是在解析時所有的行都是已知的由於這種差別使組函數與單行函數有在要求和行為上有微小的差異
  
  組(多行)函數
  
  與單行函數相比oracle提供了豐富的基於組的多行的函數這些函數可以在select或select的having子句中使用當用於select子串時常常都和GROUP BY一起使用
  
  AVG([{DISYINCT|ALL}])
  返回數值的平均值缺省設置為ALL
  SELECT AVG(sal)AVG(ALL sal)AVG(DISTINCT sal) FROM scottempAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)
  
  COUNT({*|DISTINCT|ALL} )
  返回查詢中行的數目缺省設置是ALL*表示返回所有的行
  
  MAX([{DISTINCT|ALL}])
  返回選擇列表項目的最大值如果x是字符串數據類型他返回一個VARCHAR數據類型如果X是一個DATA數據類型返回一個日期如果X是numeric數據類型返回一個數字注意distinct和all不起作用應為最大值與這兩種設置是相同的
  
  MIN([{DISTINCT|ALL}])
  返回選擇列表項目的最小值
  
  STDDEV([{DISTINCT|ALL}])
  返回選者的列表項目的標准差所謂標准差是方差的平方根
  
  SUM([{DISTINCT|ALL}])
  返回選擇列表項目的數值的總和
  
  VARIANCE([{DISTINCT|ALL}])
  返回選擇列表項目的統計方差
  
  用GROUP BY給數據分組
  
  正如題目暗示的那樣組函數就是操作那些已經分好組的數據我們告訴數據庫用GROUP BY怎樣給數據分組或者分類當我們在SELECT語句的SELECT子句中使用組函數時我們必須把為分組或非常數列放置在GROUP BY子句中如果沒有用group by進行專門處理那麼缺省的分類是將整個結果設為一類
  select statcounter(*) zip_count from zip_codes GROUP BY state;ST ZIP_COUNT AK AL AR AZ CA
  
  在這個例子中我們用state字段分類如果我們要將結果按照zip_codes排序可以用ORDER BY語句ORDER BY子句可以使用列或組函數
  select statcounter(*) zip_count from zip_codes GROUP BY state ORDER BY COUNT(*) DESC;ST COUNT(*) NY PA TX CA
  
  用HAVING子句限制分組數據
  
  現在你已經知道了在查詢的SELECT語句和ORDER BY子句中使用主函數組函數只能用於兩個子串中組函數不能用於WHERE子串中例如下面的查詢是錯誤的
  
  錯誤SELECT sales_clerkSUN(sale_amount) FROM gross_sales WHERE sales_dept=OUTSIDE AND SUM(sale_amount)> GROUP BY sales_clerk
  
  這個語句中數據庫不知道SUM()是什麼當我們需要指示數據庫對行分組然後限制分組後的行的輸出時正確的方法是使用HAVING語句
  SELECT sales_clerkSUN(sale_amount) FROM gross_sales WHERE sales_dept=OUTSIDE GROUP BY sales_clerkHAVING SUM(sale_amount)>;
  
  嵌套函數
  
  函數可以嵌套一個函數的輸出可以是另一個函數的輸入操作數有一個可繼承的執行過程但函數的優先權只是基於位置函數遵循由內到外由左到右的原則嵌套技術一般用於象DECODE這樣的能被用於邏輯判斷語句IFTHENELSE的函數
  
  嵌套函數可以包括在組函數中嵌套單行函數或者組函數嵌套入單行函數或組函數中比如下面的例子
  SELECT deptno GREATEST(COUNT(DISTINCT job)COUNT(DISTINCT mgr) cntCOUNT(DISTINCT job) jobsCOUNT(DISTINCT mgr) mgrsFROM empGROUP BY deptno;DEPTNO CNT JOBS MGRS
From:http://tw.wingwit.com/Article/program/Oracle/201311/18932.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.