oracle會話函數(shù)

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

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

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

4、35 - 1 = 34 *100(2-1) 3400 <數(shù)字3> 57 - 1 = 56 *100(2-2) 56 <數(shù)字4> 79 - 1 = 78 *100(2-3) .78 <數(shù)字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算法：<指數(shù)> 62 - 60 = 2(最高位是0，代表為負數(shù)) <數(shù)字1> 101 - 89 = 12 *10

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

6、可見，如果不在最后加上102，在排序時會出現(xiàn)-123456.789<-123456.78901的情況。greatest(exp1,exp2,exp3,expn)【功能】返回表達式列表中值最大的一個。如果表達式類型不同，會隱含轉(zhuǎn)換為第一個表達式類型?！緟?shù)】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)【功能】返回表達式列表中值最小的一個。如果表達式類型不同，會隱含轉(zhuǎn)換為第一個表達式類型。【參數(shù)】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會轉(zhuǎn)換為expr2的類型user【功能】返回當前會話對應(yīng)的數(shù)據(jù)庫用戶名?！緟?shù)】無 【返回】字符型uid【功能】返回當前會話所對應(yīng)的用戶id號?！緟?shù)】無 【返回】字符型userenv(parameter)【功能】返回當前會話上下文屬性?！緟?shù)】Parameter是參數(shù)，可以用以下參數(shù)代替：Isdba:若用戶具有dba權(quán)限，則返回true,否則返回false.Lan

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

10、n,翻譯值n,缺省值)【功能】根據(jù)條件返回相應(yīng)值【參數(shù)】c1, c2, .,cn,字符型/數(shù)值型/日期型，必須類型相同或null注：值1n 不能為條件表達式,這種情況只能用case when then end解決·含義解釋：decode(條件,值1,翻譯值1,值2,翻譯值2,.值n,翻譯值n,缺省值)該函數(shù)的含義如下：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()函數(shù)根據(jù)某個值是0、正數(shù)還是負數(shù)，分別返回0、1、-1例如：變量1=10，變量2=20則sign(變量1-變量2)返回-1，decode解碼結(jié)果為“變量1”，達到了取較小值的目的。2、表、視圖結(jié)構(gòu)轉(zhuǎn)化現(xiàn)有一個商品銷售表sale，表結(jié)構(gòu)為：month char(6) -

12、月份sellnumber(10,2)-月銷售金額現(xiàn)有數(shù)據(jù)為：2000011000200002110020000312002000041300200005140020000615002000071600200101110020020212002003011300想要轉(zhuǎn)化為以下結(jié)構(gòu)的數(shù)據(jù)：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月銷售金額結(jié)構(gòu)轉(zhuǎn)化的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個空值【參數(shù)】c1, c2, .,cn,字符型/數(shù)值型/日期型，必須類型相同或null【返回】同參數(shù)類型【說明】從Oracle 9i版開始，COALESCE函數(shù)在很多情況下就成為替代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【功能】返回當前行號【參數(shù)】無 【返回】數(shù)值型BFILENAME(dir,file)【功能】函數(shù)返回一個空的BFILE位置值指示符，函數(shù)用于初始化BFILE變量或者是B

18、FILE列。【參數(shù)】dir是一個directory類型的對象，file為一文件名。 insert into lobdemo(key,bfile_col) values (-1,biflename('utils','file1');VSIZE(X)【功能】返回X的大小(字節(jié))數(shù)【參數(shù)】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> 時，返回對應(yīng) <滿足條件時返回值1n> 當<表達式條件值1n>不為條件表達式時，與函數(shù)decode()相同，decode(<表達式>,<表達式條件值1>,<滿足條件時返回值1>,<表達式條件值2>,<滿足條件時返回值2&

21、gt; ,<不滿足上述條件時返回值>)【參數(shù)】<表達式> 默認為true (邏輯型)<表達式條件值1n> 類型要與<表達式>類型一致，若<表達式>為字符型，則<表達式條件值1n>也要為字符型【注意點】1、以CASE開頭，以END結(jié)尾2、分支中WHEN 后跟條件，THEN為顯示結(jié)果3、ELSE 為除此之外的默認情況，類似于高級語言程序中switch case的default，可以不加4、END 后跟別名5、只返回第一個符合條件的值,剩下的when部分將會被自動忽略，得注意條件先后順序【示例】建立環(huán)境：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;查詢結(jié)果：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()【功能】生產(chǎn)32位的隨機數(shù)，不過中間包括一些大寫的英文字母?！痉祷亍块L度為32位的字符串，包括09和大寫AF【示例】select sys_guid() from dual【語法】SYS_CONTEXT(c1,c2)【功能】返回系統(tǒng)c1對應(yīng)的c2的值?？梢允褂迷赟QL/PLSQL中，但不可以用在并行查詢或者RAC環(huán)境中【參數(shù)】c1，'USEREN

26、V'c2,參數(shù)表，詳見示例【返回】字符串【示例】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是一個可以生成隨機數(shù)值或者字符串的程序包。這個包有initialize()、seed()、terminate()、value()、normal()、random()、string()等幾個函數(shù)，但value()是最常用的，value

33、()的用法一般有兩個種，第一 function value return number; 這種用法沒有參數(shù)，會返回一個具有38位精度的數(shù)值，范圍從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帶有兩個參數(shù)，第

34、一個指下限，第二個指上限，將會生成下限到上限之間的數(shù)字，但不包含上限，“學無止境”兄說的就是第二種，如下： 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 方法某些用戶管理程序可能需要為用戶創(chuàng)建隨機的密碼。使用10G下的dbms_random.string 可以實現(xiàn)這樣的功能。例如：S

35、QL> select dbms_random.string('P',8 ) from dual ;DBMS_RANDOM.STRING('P',8)-3q<M"yf第一個參數(shù)的含義： '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，即字符串由任意可打印字符構(gòu)成而第二個參數(shù)表示返回的字符串長度。3. dbms_random.ra