Oracle內建SQL函式－分類整理大全(轉)

這些函式全都接收的是字元族型別的引數(CHR除外)並且返回字元值.除了特別說明的之外,這些函式大部分返回VARCHAR2型別的數值.字元函式的返回型別所受的限制和基本資料庫型別所受的限制是相同的,比如: VARCHAR2數值被限制為2000字元(ORACLE 8中為4000字元),而CHAR數值被限制為255字元(在ORACLE8中是2000).當在過程性語句中使用時,它們可被賦值給VARCHAR2或者CHAR型別的PL/SQL變數.

ASCII

語法：ascii(char c)

功能：返回一個字串的第一個字元的ASCII碼，他的逆函式是CHR()

使用位置：過程性語句和SQL語句。

select ascii('羅') from dual;
select chr(49886) from dual;

CHR

語法: chr(x)

功能：返回在資料庫字符集中與X擁有等價數值的字元。CHR和ASCII是一對反函式。經過CHR轉換後的字元再經過ASCII轉換又得到了原來的字元。

使用位置：過程性語句和SQL語句。

CONCAT

語法： CONCAT（c1,c2）

功能： c1,c2均為字串，函式將c2連線到c1的後面，如果c1為null,將返回c2.如果c2為null,則返回c1，如果c1、c2都為null，則返回null,他和運算子||返回的結果相同.

使用位置：過程性語句和SQL語句。

INITCAP

語法：INITCAP（string）

功能：返回字串的每個單詞的第一個字母大寫而單詞中的其他字母小寫的string。單詞是用.空格或給字母數字字元由空格，控制字元，標點符號進行分隔。不是字母的字元不變動。      

使用位置：過程性語句和SQL語句。

select INITCAP('luo,jia,you')from dual;

select INITCAP('luo jia you')from dual;

LTRIM

語法：LTRIM（string1,string2）

功能：返回刪除從左邊算起出現在string2中的字元的string1。String2被預設設定為單個的空格。資料庫將掃描string1，從最左邊開始。當遇到不在string2中的第一個字元，結果就被返回了。LTRIM的行為方式與RTRIM很相似。

使用位置：過程性語句和SQL語句。

LOWER

語法：LOWER(string)

功能：返回字串,並將所有的字元小寫

使用位置：過程性語句和SQL語句。

NLS_INITCAP

語法：NLS_INITCAP（string[,nlsparams]）

功能：返回字串每個單詞第一個字母大寫而單詞中的其他字母小寫的string，nlsparams

指定了不同於該會話預設值的不同排序序列。如果不指定引數，則功能和INITCAP相同。Nlsparams可以使用的形式是：

‘NLS_SORT=sort’

這裡sort制訂了一個語言排序序列。

使用位置：過程性語句和SQL語句。

NLS_LOWER

語法：NLS_LOWER（string[,nlsparams]）

功能：返回字串中的所有字母都是小寫形式的string。不是字母的字元不變。

      Nlsparams引數的形式與用途和NLS_INITCAP中的nlsparams引數是相同的。如果nlsparams沒有被包含，那麼NLS_LOWER所作的處理和LOWER相同。

使用位置；過程性語句和SQL語句。

NLS_UPPER

語法：nls_upper（string[,nlsparams]）

功能：返回字串中的所有字母都是大寫的形式的string。不是字母的字元不變。nlsparams引數的形式與用途和NLS_INITCAP中的相同。如果沒有設定引數，則NLS_UPPER功能和UPPER相同。

使用位置：過程性語句和SQL語句。

REPLACE

語法：REPLACE（string，search_str[,replace_str]）

功能：把string中的所有的子字串search_str用可選的replace_str替換，如果沒有指定replace_str，所有的string中的子字串search_str都將被刪除。REPLACE是TRANSLATE所提供的功能的一個子集。

使用位置：過程性語句和SQL語句。

RPAD

語法：RPAD（string1,x[,string2]）

功能：返回在X字元長度的位置上插入一個string2中的字元的string1。如果string2的長度要比X字元少，就按照需要進行復制。如果string2多於X字元，則僅string1前面的X各字元被使用。如果沒有指定string2，那麼使用空格進行填充。X是使用顯示長度可以比字串的實際長度要長。RPAD的行為方式與LPAD很相似，除了它是在右邊而不是在左邊進行填充。

使用位置：過程性語句和SQL語句。

LPAD

語法：LPAD（string1,x[,string2]）

功能：返回在X字元長度的位置上插入一個string2中的字元的string1。如果string2的長度要比X字元少，就按照需要進行復制。如果string2多於X字元，則僅string1前面的X各字元被使用。如果沒有指定string2，那麼使用空格進行填充。X是使用顯示長度可以比字串的實際長度要長。RPAD的行為方式與LPAD很相似，除了它是在右邊而不是在左邊進行填充。

String1,string2均為字串，x為整數。在string1的左側用string2字串補足致長度x,可多次重複，如果x小於string1的長度，那麼只返回string1中左側x個字元長的字串，其他的將被截去。String2的預設值為單空格

使用位置：過程性語句和SQL語句。

select LPAD('123',8,'0') from dual;   -- 00000123

select LPAD('123456789',8,'0') from dual;        --12345678

RTRIM

語法: RTRIM（string1,[,string2]）

功能: 返回刪除從右邊算起出現在string1中出現的字元string2. string2被預設設定為單個的空格.資料庫將掃描string1,從右邊開始.當遇到不在string2中的第一個字元,結果就被返回了RTRIM的行為方式與LTRIM很相似.

使用位置：過程性語句和SQL語句。

SOUNDEX

語法: SOUNDEX（string）

功能: 返回string的聲音表示形式.這對於比較兩個拼寫不同但是發音類似的單詞而言很有幫助. 返回與string發音相似的詞

使用位置：過程性語句和SQL語句。

SUBSTR

語法: SUBSTR（string,a[,b]）

功能: 返回從字母為值a開始b個字元長的string的一個子字串.如果a是0,那麼它就被認為從第一個字元開始.如果是正數,返回字元是從左邊向右邊進行計算的.如果b是負數,那麼返回的字元是從string的末尾開始從右向左進行計算的.如果b不存在,那麼它將預設的設定為整個字串.如果b小於1,那麼將返回NULL.如果a或b使用了浮點數,那麼該數值將在處理進行以前首先被卻為一個整數.

使用位置：過程性語句和SQL語句

SUBSTRB

語法: SUBSTRB（string,a[,b]）

功能: 與SUBSTR大致相同，只是a,b是以位元組計算

使用位置：過程性語句和SQL語句。

TRANSLATE

   語法: TRANSLATE(string,from_str,to_str)

   功能: 返回將所出現的from_str中的每個字元替換為to_str中的相應字元以後的string. TRANSLATE是REPLACE所提供的功能的一個超集.如果from_str比to_str長,那麼在from_str中而不在to_str中而外的字元將從string中被刪除,因為它們沒有相應的替換字元. to_str不能為空.Oracle把空字串認為是NULL,並且如果TRANSLATE中的任何引數為NULL,那麼結果也是NULL.

使用位置：過程性語句和SQL語句。

select TRANSLATE('fumble','umf','abc') test from dual; --cabble
select TRANSLATE('fumble','fu','abcdd') test from dual; --abmble

TRIM

   語法: TRIM(string)

   功能: 刪除string字串前後的空格

使用位置：過程性語句和SQL語句。

UPPER

語法: UPPER（string）

功能: 返回大寫的string.不是字母的字元不變.如果string是CHAR資料型別的,那麼結果也是CHAR型別的.如果string是VARCHAR2型別的,那麼結果也是VARCHAR2型別的.

使用位置: 過程性語句和SQL語句。

F.2字元函式——返回數字

這些函式接受字元引數回數字結果.引數可以是CHAR或者是VARCHAR2型別的.儘管實際下許多結果都是整數值,但是返回結果都是簡單的NUMBER型別的,沒有定義任何的精度或刻度範圍.

ASCII

語法: ASCII（string）

功能: 資料庫字符集返回string的第一個位元組的十進位制表示.請注意該函式仍然稱作為ASCII.儘管許多字符集不是7位ASCII.CHR和ASCII是互為相反的函式.CHR得到給定字元編碼的響應字元. ASCII得到給定字元的字元編碼.

使用位置: 過程性語句和SQL語句。

INSTR

語法: INSTR（string1, string2[a,b]）

功能:  得到在string1中包含string2的位置. string1時從左邊開始檢查的,開始的位置為a,如果a是一個負數,那麼string1是從右邊開始進行掃描的.第b次出現的位置將被返回. a和b都預設設定為1,這將會返回在string1中第一次出現string2的位置.如果string2在a和b的規定下沒有找到,那麼返回0.位置的計算是相對於string1的開始位置的,不管a和b的取值是多少.

使用位置: 過程性語句和SQL語句。

INSTRB

語法: INSTRB（string1, string2[a,[b]]）

功能:  和INSTR相同,只是操作的對引數字元使用的位置的是位元組.

使用位置: 過程性語句和SQL語句。

LENGTH

語法: LENGTH（string）

功能: 返回string的位元組單位的長度.CHAR數值是填充空格型別的,如果string由資料型別CHAR,它的結尾的空格都被計算到字串長度中間.如果string是NULL,返回結果是NULL,而不是0.

使用位置: 過程性語句和SQL語句。

LENGTHB

語法: LENGTHB（string）

功能: 返回以位元組為單位的string的長度.對於單位元組字符集LENGTHB和LENGTH是一樣的.

使用位置: 過程性語句和SQL語句。

NLSSORT

語法: NLSSORT（string[,nlsparams]）

功能: 得到用於排序string的字串位元組.所有的數值都被轉換為位元組字串,這樣在不同資料庫之間就保持了一致性. Nlsparams的作用和NLS_INITCAP中的相同.如果忽略引數,會話使用預設排序.

使用位置: 過程性語句和SQL語句。

 

F.3數字函式

函式接受NUMBER型別的引數並返回NUMBER型別的數值.超越函式和三角函式的返回值精確到36位.ACOS、ASIN、ATAN、ATAN2的結果精確到36位.

ABS

語法:   ABS(x)

功能:   得到x的絕對值.

使用位置: 過程性語言和SQL語句。

ACOS

語法: ACOS(x)

功能: 返回x的反餘弦值. x應該從0到1之間的數,結果在0到pi之間,以弧度為單位.

使用位置: 過程性語言和SQL語句。

ASIN

語法: ASIN(x)

功能: 計算x的反正弦值. X的範圍應該是－1到1之間,返回的結果在－pi/2到pi/2之間,以弧度為單位.

使用位置: 過程性語言和SQL語句。

ATAN

語法: ATAN(x)

功能: 計算x的反正切值.返回值在－pi/2到pi/2之間,單位是弧度.

使用位置: 過程性語言和SQL語句。

ATAN2

語法:  ATAN2(x,y)

功能: 計算x和y的反正切值.結果在負的pi/2到正的pi/2之間,單位是弧度.

使用位置: 過程性語言和SQL語句。

CEIL

語法: CEIL(x)

功能: 計算大於或等於x的最小整數值.

使用位置: 過程性語言和SQL語句。

COS

語法: COS(x)

功能: 返回x的餘弦值. X的單位是弧度.

使用位置: 過程性語言和SQL語句。

COSH

語法: COSH(x)

功能: 計算x的雙曲餘弦值.

EXP

語法: EXP(x)

功能: 計算e的x次冪. e為自然對數,約等於2.71828.

使用位置: 過程性語言和SQL語句。

FLOOR

語法: FLOOR(x)

功能: 返回小於等於x的最大整數值.

使用位置: 過程性語言和SQL語句。

LN

語法:  LN(x)

功能: 返回x的自然對數. x必須是正數,並且大於0

使用位置: 過程性語言和SQL語句。

LOG

語法: LOG(x,y)

功能: 計算以x為底的y的對數 .x必須大於0而且不等於1, y為任意正數.

使用位置: 過程性語言和SQL語句。

MOD

語法: MOD(x,y)

功能: 返回x除以y的餘數.如果y是0,則返回x

使用位置: 過程性語言和SQL語句。

POWER

語法: POWER(x,y)

功能: 計算x的y次冪.

使用位置: 過程性語言和SQL語句。

ROUND

語法: ROUND(x[,y])

功能: 計算保留到小數點右邊y位的x值. y預設設定為0,這會將x保留為最接近的整數.如果y小於0,保留到小數點左邊相應的位. Y必須是整數.進行四捨五入。

使用位置: 過程性語言和SQL語句。

SIGN

語法: SIGN(x)

功能: 獲得x的符號位標誌.如果x<0返回－1.如果x=0返回0.如果x>0返回1.

使用位置: 過程性語言和SQL語句。

SIN

語法:SIN(x)

功能:計算x的正弦值. X是一個以弧度表示的角度.

使用位置: 過程性語言和SQL語句。

SINH

語法:SINH(x)

功能:返回x的雙曲正弦值.

使用位置: 過程性語言和SQL語句。

SQRT

語法: SQRT(x)

功能: 返回x的平方根. x必須是正數.

使用位置: 過程性語言和SQL語句。

TAN

語法: TAN(x)

功能: 計算x的正切值, x是一個以弧度位單位的角度.

使用位置: 過程性語言和SQL語句。

TANH

語法: TANH(x)

功能: 計算x的雙曲正切值.

使用位置: 過程性語言和SQL語句。

TRUNC

語法: TRUNC(x[,y])

功能: 計算截尾到y位小數的x值. y預設為0,結果變為一個整數值.如果y是一個負數,那麼就截尾到小數點左邊對應的位上. 只是該函式不對指定小數前或後的部分做相應舍入選擇處理，而統統截去。

使用位置: 過程性語言和SQL語句。

 

F.4日期函式

日期函式接受DATE型別的引數.除了MONTHS_BETWEEN函式返回的是NUMBER型別的結果,所有其他的日期函式返回的都是DATE型別的數值.

ADD_MONTHS

語法:  ADD_MONTHS(d,x)

功能: 返回日期d加上x個月後的月份。x可以是任意整數。如果結果日期中的月份所包含的天數比d日期中的“日”分量要少。（即相加後的結果日期中的日分量資訊已經超過該月的最後一天，例如，8月31日加上一個月之後得到9月31日，而9月只能有30天）返回結果月份的最後一天。

使用位置: 過程性語言和SQL語句。

LAST_DAY

語法:LAST_DAY(d)

功能:計算包含日期的d的月份最後一天的日期.這個函式可以用來計算當月中剩餘天數.

使用位置: 過程性語言和SQL語句。

MONTHS_BETWEEN

語法: MONTHS_BETWEEN(date 1,date2)

功能: 計算date 1和date2之間月數.如果date 1,date2這兩個日期中日分量資訊是相同的,或者這兩個日期都分別是所在月的最後一天,那麼返回的結果是一個整數,否則包括一個小數,小數為富餘天數除以31.

使用位置: 過程性語言和SQL語句。

NEW_TIME

語法: NEW_TIME(d,zone1,zone2)

功能: 計算當時區zone1中的日期和時間是s時候,返回時區zone2中的日期和時間. zone1和zone2是字串.

使用位置: 過程性語言和SQL語句。

NEXT_DAY

語法: NEXT_DAY(d,string)

功能: 計算在日期d後滿足由string給出的條件的第一天. String使用位置;當前會話的語言指定了一週中的某一天.返回值的時間分量與d的時間分量是相同的. String的內容可以忽略大小寫.

使用位置: 過程性語言和SQL語句。

ROUND

語法: ROUND(d[,format])

功能: 將日期d按照由format指定的格式進行處理.如果沒有給format則使用預設設定`DD`.

使用位置: 過程性語言和SQL語句。

SYSDATE

語法: SYSDATE

功能: 取得當前的日期和時間,型別是DATE.它沒有引數.但在分散式SQL語句中使用時,SYSDATE返回本地資料庫的日期和時間.

使用位置: 過程性語言和SQL語句。

TRUNC

語法: TRUNC(d,format)

功能: 計算截尾到由format指定單位的日期d.可以使用位置:格式和效果.預設引數同ROUNG.

使用位置: 過程性語言和SQL語句。

 

F.5轉 換 函 數                   

轉換函式用於在PL/SQL資料型別之間進行轉換.PL/SQL儘可能地自動進行轉換,也就是採用隱含方式轉換.隱含轉換無法轉換格式資訊,並且有些型別的資料之間不支援隱含轉換,所以對這些可以採用顯示轉換.使用顯示轉換也是一種好的程式設計習慣

CHARTOROWID

語法: CHARTOROWID(string)

功能: 把包含外部格式的ROWID的CHAR或VARCHAR2數值轉換為內部的二進位制格式.引數string必須是包含外部格式的ROWID的18字元的字串.oracle7和 oracle8中的外部格式是不同的.CHARTOROWID是ROWIDTOCHAR的反函式.

使用位置: 過程性語言和SQL語句。

CONVERT

語法: CONVERT(string,dest_set[,source_set])

功能: 將字串string從source_set所表示的字符集轉換為由dest_set所表示的字符集.如果source_set沒有被指定,它預設的被設定為資料庫的字符集.

使用位置: 過程性語言和SQL語句。

HEXTORAW

語法:  HEXTORAW(string)

功能: 將由string表示的二進位制數值轉換為一個RAW數值. String應該包含一個十六進位制的數值. String中的每兩個字元表示了結果RAW中的一個位元組..HEXTORAW和RAWTOHEX為相反的兩個函式.

使用位置: 過程性語言和SQL語句。

RAWTOHEX

語法: RAWTOHEX(rawvalue)

功能:  將RAW類數值rawvalue轉換為一個相應的十六進位制表示的字串. rawvalue中的每個位元組都被轉換為一個雙位元組的字串. RAWTOHEX和HEXTORAW是兩個相反的函式.

使用位置: 過程性語言和SQL語句。

ROWIDTOCHAR

語法: ROWIDTOCHAR(rowid)

功能: 將ROWID型別的數值rowid轉換為其外部的18字元的字串表示,在oracle7和oracle8之間有些不一樣的地方. ROWIDTOCHAR和CHARTOROWID是兩個相反的函式.

使用位置: 過程性語言和SQL語句。

TO_CHAR(dates)

語法: TO_CHAR(d [,format[,nlsparams]])

功能: 將日期d轉換為一個VARCHAR2型別的字串.如果指定了format,那麼就使用位置:它控制結果的方式.格式字串是由格式元素構成的.第一個元素返回日期數值一個部份,例如日子.如果沒有給定format,使用的就是該會話的預設日期格式.如果指定了nlsparams,它就控制著返回字串的月份和日分量資訊所使用的語言. nlsparams的格式是:

“NLS_DATE_LANGUAGE”

使用位置: 過程性語言和SQL語句。

TO_CHAR(labels)

語法: TO_CHAR(labels[,format])

功能: 將MISLABEL的LABEL轉換為一個VARCHAR2型別的變數.

使用位置: 在trusted資料庫的過程性語句和SQL語句。

TO_CHAR(numbers)

語法: TO_CHAR(num[,format[,nlsparams]])

功能: 將NUMBER型別的引數num轉換為一個VARCHAR2型別的變數.如果指定了format,那麼它會控制這個轉換處理.表5-5列除了可以使用的數字格式.如果沒有指定format,它會控制這個轉換過程.下面列出了可以使用的數字格式.如果沒有指定format,那麼結果字串將包含和num中有效位的個數相同的字元. nlsparams用來指定小數點和千分位分隔符和貨幣符號.可以使用的格式:

`NLS_NUMERIC_CHARS=”dg”NLS_CURRENCY=”string”

d和g分別表示列小數點和千分位分隔符. String表示了貨幣的符號.例如,在美國小數點分隔符通常是一個句點(.),分組分隔符通常是一個逗號(,),而千分位符號通常是一個$.

使用位置: 過程性語言和SQL語句

TO_DATE

語法: TO_DATE(String[,format[,nlsparams]])

功能: 把CHAR或者VARCHAR2型別的String轉換為一個DATE型別的變數. format是一個日期格式字串.當不指定format的時候,使用該會話的預設日期格式.

使用位置: 過程性語言和SQL語句。

TO?_LABEL

語法: TO_LABEL(String[,format])

功能: 將String轉換為一個MLSLABEL型別的變數. String可以是VARCHAR2或者CHAR型別的引數.如果指定了format,那麼它就會被用在轉換中.如果沒有指定format,那麼使用預設的轉換格式.

使用位置: 過程性語言和SQL語句。

TO_MULTI_BYTE

語法: TO_MULTI_BYTE(String)

功能: 計算所有單位元組字元都替位換位等價的多位元組字元的String.該函式只有當資料庫字符集同時包含多位元組和單位元組的字元的時候有效.否則, String不會進行任何處理. TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的兩個函式.

使用位置: 過程性語言和SQL語句。

TO_NUMBER

語法: TO_NUMBER(String[,format[,nlsparams]])

功能: 將CHAR或者VARCHAR2型別的String轉換為一個NUMBER型別的數值.如果指定了format,那麼String應該遵循相應的數字格式. Nlsparams的行為方式和TO_CHAR中的完全相同.TO_NUMBER和TO_CHAR是兩個相反的函式.

使用位置: 過程性語言和SQL語句。

TO_SINGLE_BYTE

語法: TO_SINGLE_BYTE(String )

功能: 計算String中所有多位元組字元都替換為等價的單位元組字元.該函式只有當資料庫字符集同時包含多位元組和單位元組的字元的時候有效.否則, String不會進行任何處理.

TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的兩個函式.

使用位置: 過程性語言和SQL語句。

F.6分 組 函 數

 

分組函式返回基於多個行的單一結果,這和單行函式正好形成對比,後者是對單行返回一個結果.這些函式僅僅對於查詢的選擇列表和GROUP BY子句有效.

    這些函式大都可以接受對引數的修飾符.可以使用位置:的修飾符有DISTINCT和ALL.如果使用位置:了DISTINCT修飾符,那麼在處理中僅僅會考慮由查詢返回的不同的取值.ALL修飾符會使得該函式考慮由查詢返回的所有數值.如果沒有指定任何修飾符,那麼預設使用位置:的是ALL修飾符.

AVG

語法: AVG([DISTINCT| ALL]col)

功能: 返回一列資料的平均值.

使用位置: 查詢列表和GROUP BY子句.

COUNT

語法: COUNT(*| [DISTINCT| ALL] col)

功能: 得到查詢中行的數目.如果使用了*獲得行的總數.如果在引數中傳遞的是選擇列表,那麼計算的是非空數值.

 

GLB

獲得由label界定的最大下界.函式僅用於trusted oracle.GLB

語法: GLB ([DISTINCT| ALL]label)

功能: 獲得由label界定的最大下界.函式僅用於trusted oracle.

使用位置:trusted資料庫的選擇列表和GROUP BY子句.

 

LUB

語法: LUB ([DISTINCT| ALL]label)

功能: 獲得由label界定的最小上界.用於trusted oracle.資料庫.

使用位置: trusted資料庫的選擇列表和GROUP BY子句.

過程性語言和SQL語句

MAX

語法: MAX([DISTINCT| ALL]col)

功能: 獲得選擇列表專案的最大值.

使用位置: 僅用於查詢選擇和GROUP BY子句.

MIN

語法: MIN([DISTINCT| ALL]col)

功能: 獲得選擇列表的最小值.

使用位置: 僅用於查詢選擇和GROUP BY子句.

STDDEV

語法: STDDEV([DISTINCT| ALL]col)

功能: 獲得選擇列表的標準差.標準差為方差的平方根.

使用位置: 僅用於查詢選擇和GROUP BY子句.

SUM

語法:SUM([DISTINCT| ALL]col)

功能:返回選擇的數值和總和

使用位置: 僅用於查詢選擇和GROUP BY子句.

VARIANCE

語法: VARIANCE([DISTINCT| ALL]col)

功能:返回選擇列表專案的統計方差.

使用位置: 僅用於查詢選擇和GROUP BY子句.

 

F.7其 他 函 數

BFILENAME

語法: BFILENAME(directory,file_name)

功能: 獲得作業系統中與物理檔案file_name相關的BFILE位置指示符. directory必須是資料字典中的DIRECTORY型別的物件.

使用位置: 過程性語言和SQL語句

DECODE

語法:

DECODE(base_expr,comparel,valuel,

Compare2,value2,

…

default)

功能: 把base_expr與後面的每個compare (n) 進行比較,如果匹配返回相應的value (n) .如果沒有發生匹配,則返回default

使用位置: 過程性語言和SQL語句。

DUMP

語法:DUMP(expr[,number_format[,start_position][,length]])

功能:獲得有關expr的內部表示資訊的VARCHAR2型別的數值. number_format指定了按照下面返回數值的基數(base):

    number_format   結果

8                     八進位制表示

10         十進位制表示

16                  十六進位制表示

17                  單字元

預設的值是十進位制.

如果指定了start_position和length,那麼返回從start_position開始的長為length的位元組.預設返回全部.

資料型別按照下面規定的內部資料型別的編碼作為一個數字進行返回.

         程式碼             資料型別

1                                                     VARCHAR2

2                                                     NUMBER

8                                                     LONG

12              DATE

23                                                  RAW

69                                                  ROWID

96                                                  CHAR

106                                              MLSLABEL

使用位置: SQL語句.

EMPTY_CLOB/EMPTY_BLOB

語法: EMPTY_CLOB

       EMPTY_BLOB

功能: 獲得一個空的LOB提示符 (locator) .EMOTY_CLOB返回一個字元指示符,而 EMPTY_BLOB返回一個二進位制指示符.

使用位置: 過程性語言和SQL語句.

GREATEST

語法: GREATEST(expr1[,expr2]…)

功能: 計算引數中最大的表示式.所有表示式的比較型別以expr1為準.

       返回一組表示式中的最大值,即比較字元的編碼大小.

使用位置: 過程性語言和SQL語句.

          select greatest(’AA’,’AB’,’AC’) from dual;

select greatest(1,2,5) from dual;

GREATEST_LB

語法: GREATEST_LB(label1[,label2]…)

功能: 返回標籤(label)列表中最大的下界.每個標籤必須擁有資料型別MLSLABEL、RAWMLSLABEL或者是一個表因字串文字.函式只能用於truested oracle庫.

使用位置: 過程性語言和SQL語句.

LEAST

語法: LEAST(expr1[,:expr2]…)

功能: 獲得引數中最小的表示式.

使用位置: 過程性語言和SQL語句.

select least(’啊’,’安’,’天’) from dual;

select least(1,5,9) from dual;

LEAST_UB

語法: LEAST_UB(label1[,label2]…)

功能:  與GREATEST_UB函式相似,本函式返回標籤列表的最小上界.

使用位置: 過程性語言和SQL語句.

NVL

語法: NVL (expr1, expr2)

功能:  如果expr1是NULL,那麼返回expr2,否則返回expr1.

如果expr1不是字串,那麼返回值的資料型別和expr1是相同的,否則,返回值的資料型別是VARCHAR2.此函式對於檢查並確定查詢的活動集不包含NULL值十分有用.

使用位置: 過程性語言和SQL語句.

UID

語法:

功能:  獲得當前資料庫用的惟一標識,標識是一個整數.

使用位置: 過程性語言和SQL語句.

USER

語法:

功能: 取得當前oracle使用者的名字,返回的結果是一個VARCHAR2型字串.

使用位置: 過程性語言和SQL語句.

USERENV

語法: USERENV(option)

功能: 根據引數option,取得一個有關當前會話資訊的VARCHAR2數值.

使用位置: 過程性語言和SQL語句.

VSIZE

語法: VSIZE(value)

功能: 獲得value的內部表示的位元組數.如果value是NULL,結果是NULL.

使用位置: 過程性語言和SQL語句.

F.8 sqlplus常用命令

SPOOL將螢幕所有的輸出輸出到指定檔案

-- spool 檔案路徑名;

 spool g:"mysql.sql;

 --業務操作

--結束輸出

 spool off;

執行一個SQL指令碼檔案

我們可以將多條sql語句儲存在一個文字檔案中，這樣當要執行這個檔案中的所有的sql語句時，用上面的任一命令即可，這類似於dos中的批處理。

--start file_name

-- @ file_name

start g:"mysql.sql;

@ g:"mysql.sql;

對當前的輸入進行編輯

edit

ed

重新執行上一次執行的sql語句

/

顯示一個表的結構

 desc table_name ;

清屏

clear screen;

退出

exit;

置當前session是否對修改的資料進行自動提交

--SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}

set autocommit on;

在用start命令執行一個sql指令碼時，是否顯示指令碼中正在執行的SQL語句

-- SET ECHO {ON|OFF};

set echo on;

是否顯示當前sql語句查詢或修改的行數

--SET FEED[BACK] {6|n|ON|OFF}

-- 預設只有結果大於6行時才顯示結果的行數。如果set feedback 1 ，則不管查詢到多少行都返回。當為off時，一律不顯示查詢的行數

set feedback 1;

是否顯示列標題

--當set heading off 時，在每頁的上面不顯示列標題，而是以空白行代替

--SET HEA[DING] {ON|OFF}

set heading on;

設定一行可以容納的字元數

-- 如果一行的輸出內容大於設定的一行可容納的字元數，則折行顯示

--SET LIN[ESIZE] {80|n}

set linesize 100;

設定頁與頁之間的分隔

-- SET NEWP[AGE] {1|n|NONE}

--當set newpage 0 時，會在每頁的開頭有一個小的黑方框。

--當set newpage n 時，會在頁和頁之間隔著n個空行。

--當set newpage none 時，會在頁和頁之間沒有任何間隔

set newpage 1;

設定一頁有多少行數

--如果設為0，則所有的輸出內容為一頁並且不顯示列標題

--SET PAGES[IZE] {24|n}

set pagesize 20;

是否顯示用DBMS_OUTPUT.PUT_LINE包進行輸出的資訊。

--SET SERVEROUT[PUT] {ON|OFF} 

set serveroutput on;

是否在螢幕上顯示輸出的內容，主要用與SPOOL結合使用。

--在用spool命令將一個大表中的內容輸出到一個檔案中時，將內容輸出在螢幕上會耗費大量的時間，

--設定set termspool off後，則輸出的內容只會儲存在輸出檔案中，不會顯示在螢幕上，極大的提高了spool的速度

--SET TERM[OUT] {ON|OFF}

set termout off;

在dos裡連線oracle資料庫

CONNECT user_name/passwd@l_jiayou

在sql*plus中連線到指定的資料庫

CONNECT user_name/passwd@資料庫名稱

顯示當前使用者

show user;

顯示當前環境變數的值：

show all;

顯示當前在建立函式、儲存過程、觸發器、包等物件的錯誤資訊

Show error

顯示資料庫的版本：

--show REL[EASE]

show release

顯示SGA的大小

show SGA

顯示初始化引數的值：

--show PARAMETERS [parameter_name]

show parameters;

 

檢視當前使用者的預設表空間

select username,default_tablespace from user_users

檢視當前使用者的角色

select * from user_role_privs

檢視當前使用者的系統許可權和表級許可權

select * from user_sys_privs;

select * from user_tab_privs;

檢視使用者下所有的表

select * from user_tables

檢視名稱包含log字元的表

select object_name,object_id from user_objects where instr(object_name,'LOG')>0;

檢視某表的建立時間

select object_name,created from user_objects where object_name=upper('&table_name');

檢視某表的大小

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');

檢視放在ORACLE的記憶體區裡的表

select table_name,cache from user_tables where instr(cache,'Y')>0;

檢視索引個數和類別

select index_name,index_type,table_name from user_indexes order by table_name;

檢視索引被索引的欄位

select * from user_ind_columns where index_name=upper('&index_name');

檢視索引的大小

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');

檢視序列號，last_number是當前值

select * from user_sequences;

檢視檢視的名稱

--select view_name from user_views;

檢視建立檢視的select語句

select view_name,text_length from user_views;

set long 2000;                說明：可以根據檢視的text_length值設定set long 的大小

select text from user_views where view_name=upper('&view_name');

檢視同義詞的名稱

select * from user_synonyms

檢視某表的約束條件

select constraint_name, constraint_type,search_condition, r_constraint_name

                from user_constraints where table_name = upper('&table_name');

            

select c.constraint_name,c.constraint_type,cc.column_name

 from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')

 and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;

檢視函式和過程

select object_name,status from user_objects where object_type='FUNCTION';

select object_name,status from user_objects where object_type='PROCEDURE';

檢視函式和過程的原始碼

select text from all_source where owner=user and name=upper('&plsql_name');

檢視錶空間的名稱及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size

    from dba_tablespaces t, dba_data_files d

    where t.tablespace_name = d.tablespace_name

    group by t.tablespace_name;

檢視錶空間物理檔案的名稱及大小

    select tablespace_name, file_id, file_name,

    round(bytes/(1024*1024),0) total_space

    from dba_data_files

    order by tablespace_name;

檢視回滾段名稱及大小

    select segment_name, tablespace_name, r.status,

    (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

    max_extents, v.curext CurExtent

    From dba_rollback_segs r, v$rollstat v

    Where r.segment_id = v.usn(+)

    order by segment_name ;

檢視控制檔案

    select name from v$controlfile;

查看日誌檔案

    select member from v$logfile;

檢視錶空間的使用情況

 select sum(bytes)/(1024*1024) as free_space,tablespace_name

 from dba_free_space group by tablespace_name;

 SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

    (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

    FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

檢視資料庫庫物件

 select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

檢視資料庫的版本

 Select version FROM Product_component_version

    Where SUBSTR(PRODUCT,1,6)='Oracle';

檢視資料庫的建立日期和歸檔方式

 Select Created, Log_Mode, Log_Mode From V$Database;

用系統管理員，檢視當前資料庫有幾個使用者連線：

select username,sid,serial# from v$session;

如果要停某個連線用

 alter system kill session 'sid,serial#';

如果這命令不行,找它UNIX的程式數

select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;

--說明：21是某個連線的sid數,然後用 kill 命令殺此程式號。

例子：

表test122,有兩個欄位t_id varchar2(20),t_name varchar2(10);

要求t_id的值為當天日期加上0001,0002的形式遞加作為序列，如20070209_0001,200709_0002；

思路：查訊當天的t_id的最大值加1,然後生成序列；

insert into test122 values

(to_char(sysdate,'yyyymmdd')||'_'||(select lpad(to_number(ltrim(substr(max(t_id),length(max(t_id))-3),'0'))+1,4,0)

from test122 where substr(t_id,0,length(t_id)-5)=to_char(sysdate,'yyyymmdd')),'ok');

樹形遞迴查詢：Start with...Connect By

準備：

create table mymenu(tree_id varchar(10),tree_pid varchar(10),tree_lable varchar(50),tree_link varchar(100))

insert into mymenu values('1','0','蔬菜','')

insert into mymenu values('2','0','水果','')

insert into mymenu values('3','0','穀物','')

insert into mymenu values('4','0','肉類','')

insert into mymenu values('5','1','白菜','')

insert into mymenu values('6','1','茄子','htt://')

insert into mymenu values('7','5','四月白','')

insert into mymenu values('8','5','冬白菜','htt://')

insert into mymenu values('9','2','西瓜','')

insert into mymenu values('10','2','桔子','htt://')

insert into mymenu values('11','3','大米','')

insert into mymenu values('12','3','大豆','htt://')

insert into mymenu values('13','4','豬肉','')

insert into mymenu values('14','4','魚','')

insert into mymenu values('15','14','昌魚','')

insert into mymenu values('16','14','王八','htt://')

從根往樹末梢查詢：

select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid;//查詢所有

select * from mymenu start with tree_id='1' connect by prior tree_id=tree_pid; //查詢指定ID

從樹末梢向根查詢：

select * from mymenu start with tree_pid='0' connect by prior tree_pid=tree_id

select * from mymenu start with tree_id='8' connect by prior tree_pid=tree_id

如果還有其他條件用and 加在語句後面

select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid and tree_link is null

select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid and tree_link is not null

F.9 oracle客戶端連線的檔案配置：

oracle的目錄/network/ADMIN/tnsnames.ora

內容：

MIMI(客戶端連線的名稱) =

 (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = NTDB.RUNNER)

    )

 )

---------------------------------------end-------------------------------------------------

F.10修改表結構

alter table m_gl_gls3_history add (aaaaa varchar2(20),bbbbb varchar2(10))
alter table m_gl_gls3_history modify (aaaaa varchar2(10))
--要改變表中的欄位的型別或縮小欄位長度，該欄位的所有記錄值必須為空。

--如果改欄位存在記錄值，則該欄位長度只能擴大，不能縮小。

alter table m_gl_gls3_history drop (aaaaa , bbbbb )