1、DUMP(w,x,y,z)【功能】返回數據類型、字節長度和在內部的存儲位置.【參數】 w為各種類型的字符串（如字符型、數值型、日期型） x為返回位置用什么方式表達，可為:8,10,16或17，分別表示：8/10/16進制和字符型，默認為10。 y和z決定了內部參數位置【返回】類型 <長度>，符號/指數位 數字1，數字2，數字3，.，數字20如：Typ=2 Len=7: 60,89,67,45,23,11,102SELECT DUMP('ABC',1016) FROM dual; 返回結果為：Typ=96 Len=3 CharacterSet=ZHS16GBK: 41

2、,42,43 代碼 數據類型0 對應 VARCHAR21 對應 NUMBER8 對應 LONG12 對應 DATE23 對應 RAW24 對應 LONG RAW69 對應 ROWID96 對應 CHAR106 對應 MSSLABEL各位的含義如下:1.類型: Number型,Type=2 (類型代碼可以從Oracle的文檔上查到)2.長度:指存儲的字節數3.符號/指數位在存儲上,Oracle對正數和負數分別進行存儲轉換:正數：加1存儲(為了避免Null)負數：被101減,如果總長度小于21個字節，最后加一個102(是為了排序的需要)指數位換算:正數：指數=符號/指數位 - 193 (最高位為1

3、是代表正數) 負數：指數=62 - 第一字節4.從<數字1>開始是有效的數據位從<數字1>開始是最高有效位,所存儲的數值計算方法為：將下面計算的結果加起來：每個<數字位>乘以100(指數-N) (N是有效位數的順序位，第一個有效位的N=0)5、舉例說明SQL> select dump(123456.789) from dual;返回：Typ=2 Len=6: 195,13,35,57,79,91 <指數>: 195 - 193 = 2 <數字1> 13 - 1 = 12 *100(2-0) 120000 <數字2>

4、35 - 1 = 34 *100(2-1) 3400 <數字3> 57 - 1 = 56 *100(2-2) 56 <數字4> 79 - 1 = 78 *100(2-3) .78 <數字5> 91 - 1 = 90 *100(2-4) .009 123456.789 SQL> select dump(-123456.789) from dual;返回：Typ=2 Len=7: 60,89,67,45,23,11,102算法：<指數> 62 - 60 = 2(最高位是0，代表為負數) <數字1> 101 - 89 = 12 *10

5、0(2-0) 120000 <數字2> 101 - 67 = 34 *100(2-1) 3400 <數字3> 101 - 45 = 56 *100(2-2) 56 <數字4> 101 - 23 = 78 *100(2-3) .78 <數字5> 101 - 11 = 90 *100(2-4) .009 123456.789(-) 現在再考慮一下為什么在最后加102是為了排序的需要，-123456.789在數據庫中實際存儲為60,89,67,45,23,11 而-123456.78901在數據庫中實際存儲為 60,89,67,45,23,11,91

6、可見，如果不在最后加上102，在排序時會出現-123456.789<-123456.78901的情況。greatest(exp1,exp2,exp3,expn)【功能】返回表達式列表中值最大的一個。如果表達式類型不同，會隱含轉換為第一個表達式類型。【參數】exp1n，各類型表達式【返回】exp1類型【示例】 SELECT greatest(10,32,'123','2006') FROM dual; SELECT greatest('kdnf','dfd','a','206') FROM du

7、al;least(exp1,exp2,exp3,expn)【功能】返回表達式列表中值最小的一個。如果表達式類型不同，會隱含轉換為第一個表達式類型。【參數】exp1n，各類型表達式【返回】exp1類型【示例】 SELECT least(10,32,'123','2006') FROM dual;SELECT least('kdnf','dfd','a','206') FROM dual;【語法】NVL (expr1, expr2)【功能】若expr1為NULL，返回expr2；expr1不為NULL，

8、返回expr1。注意兩者的類型要一致 【語法】NVL2 (expr1, expr2, expr3) 【功能】expr1不為NULL，返回expr2；expr2為NULL，返回expr3。expr2和expr3類型不同的話，expr3會轉換為expr2的類型user【功能】返回當前會話對應的數據庫用戶名。【參數】無 【返回】字符型uid【功能】返回當前會話所對應的用戶id號。【參數】無 【返回】字符型userenv(parameter)【功能】返回當前會話上下文屬性。【參數】Parameter是參數，可以用以下參數代替：Isdba:若用戶具有dba權限，則返回true,否則返回false.Lan

9、guage:返回當前會話對應的語言、地區和字符集。LANG:返回當前環境的語言的縮寫Terminal:返回當前會話所在終端的操作系統標識符。Sessionid:返回正在使用的審計會話號.Client_info:返回用戶會話信息，若沒有則返回null.【返回】根據參數不同則類型不同【示例】Select userenv('isdba'),userenv('Language'),userenv('Terminal'),userenv('Client_info') from dualdecode(條件,值1,翻譯值1,值2,翻譯值2,.值

10、n,翻譯值n,缺省值)【功能】根據條件返回相應值【參數】c1, c2, .,cn,字符型/數值型/日期型，必須類型相同或null注：值1n 不能為條件表達式,這種情況只能用case when then end解決·含義解釋：decode(條件,值1,翻譯值1,值2,翻譯值2,.值n,翻譯值n,缺省值)該函數的含義如下：IF 條件=值1 THENRETURN(翻譯值1)ELSIF 條件=值2 THENRETURN(翻譯值2).ELSIF 條件=值n THENRETURN(翻譯值n)ELSERETURN(缺省值)END IF或：when case 條件=值1 THENRETURN(翻譯值

11、1)ElseCase 條件=值2 THENRETURN(翻譯值2).ElseCase 條件=值n THENRETURN(翻譯值n)ELSERETURN(缺省值)END【示例】·使用方法：1、比較大小select decode(sign(變量1-變量2),-1,變量1,變量2) from dual; -取較小值sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1例如：變量1=10，變量2=20則sign(變量1-變量2)返回-1，decode解碼結果為“變量1”，達到了取較小值的目的。2、表、視圖結構轉化現有一個商品銷售表sale，表結構為：month char(6) -

12、月份sellnumber(10,2)-月銷售金額現有數據為：2000011000200002110020000312002000041300200005140020000615002000071600200101110020020212002003011300想要轉化為以下結構的數據：yearchar(4) -年份month1number(10,2)-1月銷售金額month2number(10,2)-2月銷售金額month3number(10,2)-3月銷售金額month4number(10,2)-4月銷售金額month5number(10,2)-5月銷售金額month6number(10,2

13、)-6月銷售金額month7number(10,2)-7月銷售金額month8number(10,2)-8月銷售金額month9number(10,2)-9月銷售金額month10number(10,2)-10月銷售金額month11number(10,2)-11月銷售金額month12number(10,2)-12月銷售金額結構轉化的SQL語句為：create or replace viewv_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)ass

14、electsubstrb(month,1,4),sum(decode(substrb(month,5,2),'01',sell,0),sum(decode(substrb(month,5,2),'02',sell,0),sum(decode(substrb(month,5,2),'03',sell,0),sum(decode(substrb(month,5,2),'04',sell,0),sum(decode(substrb(month,5,2),'05',sell,0),sum(decode(substrb(mo

15、nth,5,2),'06',sell,0),sum(decode(substrb(month,5,2),'07',sell,0),sum(decode(substrb(month,5,2),'08',sell,0),sum(decode(substrb(month,5,2),'09',sell,0),sum(decode(substrb(month,5,2),'10',sell,0),sum(decode(substrb(month,5,2),'11',sell,0),sum(decode(subs

16、trb(month,5,2),'12',sell,0)from salegroup by substrb(month,1,4);【語法】NULLIF (expr1, expr2)【功能】expr1和expr2相等返回NULL，不相等返回expr1COALESCE(c1, c2, .,cn)【功能】返回列表中第一個非空的表達式，如果所有表達式都為空值則返回1個空值【參數】c1, c2, .,cn,字符型/數值型/日期型，必須類型相同或null【返回】同參數類型【說明】從Oracle 9i版開始，COALESCE函數在很多情況下就成為替代CASE語句的一條捷徑【示例】select

17、COALESCE(null,3*5,44) hz from dual; 返回15select COALESCE(0,3*5,44) hz from dual; 返回0select COALESCE(null,'','AAA') hz from dual; 返回AAAselect COALESCE('','AAA') hz from dual; 返回AAArownum【功能】返回當前行號【參數】無 【返回】數值型BFILENAME(dir,file)【功能】函數返回一個空的BFILE位置值指示符，函數用于初始化BFILE變量或者是B

18、FILE列。【參數】dir是一個directory類型的對象，file為一文件名。 insert into lobdemo(key,bfile_col) values (-1,biflename('utils','file1');VSIZE(X)【功能】返回X的大小(字節)數【參數】x select vsize(user),user from dual;返回：6 asdiedselect length('adfad合理') "bytesLengthIs" from dual -7select lengthb('adfa

19、d') "bytesLengthIs" from dual -5select lengthb('adfad合理') "bytesLengthIs" from dual -9select vsize('adfad合理') "bytesLengthIs" from dual -9select lengthc('adfad合理')"bytesLengthIs" from dual -7lengthb=vsizelengthc=lengthcase <表達式&g

20、t;when <表達式條件值1> then <滿足條件時返回值1> when <表達式條件值2> then <滿足條件時返回值2> else <不滿足上述條件時返回值>end【功能】當：<表達式><表達式條件值1n> 時，返回對應 <滿足條件時返回值1n> 當<表達式條件值1n>不為條件表達式時，與函數decode()相同，decode(<表達式>,<表達式條件值1>,<滿足條件時返回值1>,<表達式條件值2>,<滿足條件時返回值2&

21、gt; ,<不滿足上述條件時返回值>)【參數】<表達式> 默認為true (邏輯型)<表達式條件值1n> 類型要與<表達式>類型一致，若<表達式>為字符型，則<表達式條件值1n>也要為字符型【注意點】1、以CASE開頭，以END結尾2、分支中WHEN 后跟條件，THEN為顯示結果3、ELSE 為除此之外的默認情況，類似于高級語言程序中switch case的default，可以不加4、END 后跟別名5、只返回第一個符合條件的值,剩下的when部分將會被自動忽略，得注意條件先后順序【示例】建立環境：create table

22、 xqb(xqn number(1,0);insert into xqb xqn values(1);insert into xqb xqn values(2);insert into xqb xqn values(3);insert into xqb xqn values(4);insert into xqb xqn values(5);insert into xqb xqn values(6);insert into xqb xqn values(7);commit;查詢結果：SELECT xqn, CASE WHEN xqn = 1 THEN '星期一' WHEN xqn

23、 = 2 THEN '星期二' WHEN xqn = 3 THEN '星期三' else '星期三以后' END 星期FROM xqb另類寫法SELECT xqn, CASE xqn WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三' else '星期三以后' END 星期FROM xqbdecode正確表達：SELECT xqn, decode(xqn,1,'星期一',2,'星期二',3,

24、9;星期三','星期三以后') 星期FROM xqbdecode錯誤表達：SELECT xqn, decode(TRUE,xqn=1,'星期一',xqn=2,'星期二',xqn=3,'星期三','星期三以后') 星期FROM xqb組合條件表達：SELECT xqn, CASE WHEN xqn <= 1 THEN '星期一' WHEN xqn <= 2 THEN '星期二' -條件同：not(xqn<=1) and xqn<=2 WHEN xqn &

25、lt;= 3 THEN '星期三' -條件同：not(xqn<=1 and xqn<=2) and xqn<=3 else '星期三以后' END 星期FROM xqb【語法】sys_guid()【功能】生產32位的隨機數，不過中間包括一些大寫的英文字母。【返回】長度為32位的字符串，包括09和大寫AF【示例】select sys_guid() from dual【語法】SYS_CONTEXT(c1,c2)【功能】返回系統c1對應的c2的值。可以使用在SQL/PLSQL中，但不可以用在并行查詢或者RAC環境中【參數】c1，'USEREN

26、V'c2,參數表，詳見示例【返回】字符串【示例】selectSYS_CONTEXT('USERENV','TERMINAL') terminal,SYS_CONTEXT('USERENV','LANGUAGE') language,SYS_CONTEXT('USERENV','SESSIONID') sessionid,SYS_CONTEXT('USERENV','INSTANCE') instance,SYS_CONTEXT('USERENV'

27、;,'ENTRYID') entryid,SYS_CONTEXT('USERENV','ISDBA') isdba,SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,SYS_CONTEXT(

28、9;USERENV','NLS_DATE_FORMAT') nls_date_format,SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,SYS_CONTEXT('USERENV','CURRENT_USER') current_user,SYS_CONTEXT('USERENV','CURR

29、ENT_USERID') current_userid,SYS_CONTEXT('USERENV','SESSION_USER') session_user,SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,

30、SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,SYS_CONTEXT('USERENV','DB_NAME') db_name,SYS_CONTEXT('USERENV','HOST') host,SYS_CONTEXT('USERENV','OS_USER') os_user,SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,SYS_C

31、ONTEXT('USERENV','IP_ADDRESS') ip_address,SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,SYS_CONTEXT('USERENV','AUTHENTICA

32、TION_TYPE') authentication_type,SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_datafrom dualOracle dbms_random包的用法from:1.dbms_random.value方法dbms_random是一個可以生成隨機數值或者字符串的程序包。這個包有initialize()、seed()、terminate()、value()、normal()、random()、string()等幾個函數，但value()是最常用的，value

33、()的用法一般有兩個種，第一 function value return number; 這種用法沒有參數，會返回一個具有38位精度的數值，范圍從0.0到1.0，但不包括1.0，如下示例： SQL> set serverout on SQL> begin 2 for i in 1.10 loop 3 dbms_output.put_line(round(dbms_random.value*100); 4 end loop; 5 end; 6 / 46 19 45 37 33 57 61 20 82 8 PL/SQL 過程已成功完成。 SQL> 第二種value帶有兩個參數，第

34、一個指下限，第二個指上限，將會生成下限到上限之間的數字，但不包含上限，“學無止境”兄說的就是第二種，如下： SQL> begin 2 for i in 1.10 loop 3 dbms_output.put_line(trunc(dbms_random.value(1,101); 4 end loop; 5 end; 6 / 97 77 13 86 68 16 55 36 54 46 PL/SQL 過程已成功完成。 2. dbms_random.string 方法某些用戶管理程序可能需要為用戶創建隨機的密碼。使用10G下的dbms_random.string 可以實現這樣的功能。例如：S

35、QL> select dbms_random.string('P',8 ) from dual ;DBMS_RANDOM.STRING('P',8)-3q<M"yf第一個參數的含義： 'u', 'U' - returning string in uppercase alpha characters 'l', 'L' - returning string in lowercase alpha characters 'a', 'A' - return

36、ing string in mixed case alpha characters 'x', 'X' - returning string in uppercase alpha-numericcharacters 'p', 'P' - returning string in any printable characters.Otherwise the returning string is in uppercase alphacharacters.P 表示 printable，即字符串由任意可打印字符構成而第二個參數表示返回的字符串長度。3. dbms_random.ra