oracle函式大全

oracle函式大全

1． 第一講單行函式和組函式詳解

pl/sql單行函式和組函式詳解

函式是一種有零個或多個引數並且有乙個返回值的程式。在sql中oracle內建了一系列函式，這些函式都可被稱為sql或pl/sql語句，函式主要分為兩大類：

單行函式

組函式本文將討論如何利用單行函式以及使用規則。

sql中的單行函式

sql和pl/sql中自帶很多態別的函式，有字元、數字、日期、轉換、和混合型等多種函式用於處理單行資料，因此這些都可被統稱為單行函式。這些函式均可用於select,where、order by等子句中，例如下面的例子中就包含了to_char,upper,soundex等單行函式。

select ename,to_char(hiredate,'day,dd-mon-yyyy')from empwhere upper(ename) like 'al%'order by soundex(ename)

單行函式也可以在其他語句中使用，如update的set子句，insert的values子句，delet的where子句,認證考試特別注意在select語句中使用這些函式，所以我們的注意力也集中在select語句中。

null和單行函式

在如何理解null上開始是很困難的，就算是乙個很有經驗的人依然對此感到困惑。null值表示乙個未知資料或者乙個空值，算術操作符的任何乙個運算元為null值，結果均為提個null值,這個規則也適合很多函式，只有concat,decode,dump,nvl,replace在呼叫了null引數時能夠返回非null值。在這些中nvl函式時最重要的，因為他能直接處理null值，nvl有兩個引數：

nvl(x1,x2),x1和x2都式表示式，當x1為null時返回x2,否則返回x1。

下面我們看看emp資料表它包含了薪水、獎金兩項，需要計算總的補償

column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2

不是簡單的將薪水和獎金加起來就可以了，如果某一行是null值那麼結果就將是null，比如下面的例子：

update empset salary=(salary+bonus)*1.1

這個語句中，雇員的工資和獎金都將更新為乙個新的值，但是如果沒有獎金，即 salary + null,那麼就會得出錯誤的結論，這個時候就要使用nvl函式來排除null值的影響。所以正確的語句是：

update empset salary=(salary+nvl(bonus,0)*1.1

2． 第二講單行字串函式

單行字串函式

單行字串函式用於操作字串資料，他們大多數有乙個或多個引數，其中絕大多數返回字串

ascii()

c1是一字串，返回c1第乙個字母的ascii碼，他的逆函式是chr()

select ascii('a') big_a,ascii('z') big_z from empbig_a big_z65 122

chr(＜i＞)[nchar_cs]

i是乙個數字，函式返回十進位制表示的字元

select chr(65),chr(122),chr(223) from empchr65 chr122 chr223a z b

concat(,)

c1,c2均為字串，函式將c2連線到c1的後面，如果c1為null,將返回c2.如果c2為null,則返回c1，如果c1、c2都為null，則返回null。他和操作符||返回的結果相同

select concat('slobo ','svoboda') username from dualusernameslobo syoboda

initcap()

c1為一字串。函式將每個單詞的第乙個字母大寫其它字母小寫返回。單詞由空格，控制字元，標點符號限制。

select initcap('veni,vedi,vici') ceasar from dualceasarveni,vedi,vici

instr(,[,＜i＞[,]])

c1,c2均為字串，i,j為整數。函式返回c2在c1中第j次出現的位置，搜尋從c1的第i個字元開始。當沒有發現需要的字元時返回0,如果i為負數，那麼搜尋將從右到左進行，但是位置的計算還是從左到右，i和j的預設值為1.

select instr('mississippi','i',3,3) from dualinstr('mississippi','i',3,3)11select instr('mississippi','i',-2,3) from dualinstr('mississippi','i',3,3)2

instrb(,[,i[,j])

與instr（）函式一樣，只是他返回的是位元組，對於單位元組instrb()等於instr()

length()

c1為字串，返回c1的長度，如果c1為null，那麼將返回null值。

select length('ipso facto') ergo from dualergo10

lengthb()

與length()一樣，返回位元組。

lower()

返回c的小寫字元，經常出現在where子串中

select lower(colorname) from itemdetail where lower(colorname) like '%white%'colornamewinterwhite

lpad(,＜i＞[,])

c1,c2均為字串，i為整數。在c1的左側用c2字串補足致長度i,可多次重複，如果i小於c1的長度，那麼只返回i那麼長的c1字元，其他的將被截去。c2的預設值為單空格，參見rpad。

select lpad(answer,7,'') padded,answer unpadded from question;padded unpadded yes yesno nomaybe maybe

ltrim(,)

把c1中最左邊的字元去掉，使其第乙個字元不在c2中，如果沒有c2，那麼c1就不會改變。

select ltrim('mississippi','mis') from dualltrppi

rpad(,＜i＞[,])

在c1的右側用c2字串補足致長度i,可多次重複，如果i小於c1的長度，那麼只返回i那麼長的c1字元，其他的將被截去。c2的預設值為單空格,其他與lpad相似

rtrim(,)

把c1中最右邊的字元去掉，使其第後乙個字元不在c2中，如果沒有c2，那麼c1就不會改變。

replace(,[,])

c1,c2,c3都是字串，函式用c3代替出現在c1中的c2後返回。

select replace('uptown','up','down') from dualreplacedowntown

stbstr(,＜i＞[,])

c1為一字串，i,j為整數，從c1的第i位開始返回長度為j的子字串，如果j為空，則直到串的尾部。

select substr('message',1,4) from dualsub**ess

substrb(,＜i＞[,])

與substr大致相同，只是i,j是以位元組計算。

soundex()

返回與c1發音相似的詞

select soundex('dawes') dawes soundex('daws') daws, soundex('dawson') from dualdawes daws dawsond200 d200 d250

translate(,,)

將c1中與c2相同的字元以c3代替

select translate('fumble','uf','ar') test from dualtextramble

trim([] from c3)

將c3串中的第乙個，最後乙個，或者都刪除。

select trim(' space padded ') trim from dual trimspace padded

upper()

返回c1的大寫，常出現where子串中select name from dual where upper(name) like 'ki%'nameking

3． 第三講單行數字函式

單行數字函式

單行數字函式運算元字資料，執行數學和算術運算。所有函式都有數字引數並返回數字值。所有三角函式的運算元和值都是弧度而不是角度，oracle沒有提供內建的弧度和角度的轉換函式。

abs()

返回n的絕對值

acos()

反餘玄函式，返回-1到1之間的數。n表示弧度

select acos(-1) pi,acos(1) zero from dualpi zero3.14159265 0

asin()

反正玄函式，返回-1到1，n表示弧度

atan()

反正切函式，返回n的反正切值，n表示弧度。

ceil()

返回大於或等於n的最小整數。

cos()

返回n的餘玄值，n為弧度

cosh()

返回n的雙曲餘玄值，n 為數字。

select cosh(<1.4>) from dualcosh(1.4)2.15089847

exp()

返回e的n次冪，e=2.71828183.

floor()

返回小於等於n的最大整數。

ln()

返回n的自然對數，n必須大於0

log(,)

返回以n1為底n2的對數

mod()

返回n1除以n2的餘數，

power(,)

oracle許可權大全

oracle許可權筆記 一 系統的預設使用者 1.sys 系統管理員，擁有最高許可權 2.system 本地管理員，次高許可權 3.scott 普通使用者，密碼預設為tiger,預設未解鎖 二 登陸 1.sqlplus assysdba 登陸sys帳戶 2.sqlplussysassysdba 同上...

oracle常用函式總結

返回與指定的字元對應的十進位制數 sql select ascii a a,ascii a a,ascii 0 zero,ascii space from dual a a zero space 65 97 48 32 給出整數,返回對應的字元 sql select chr 54740 zhao,c...

Oracle分析函式使用總結

1.使用評級函式 評級函式 ranking function 用於計算等級 百分點 n分片等等，下面是幾個常用到的評級函式 rank 返回資料項在分組中的排名。特點 在排名相等的情況下會在名次中留下空位dense rank 與rank不同的是它在排名相等的情況下不會在名次中留下空位cume dist...