oracle会话函数重点讲义

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