oracle会话函数.docx

oracle会话函数.docx

《oracle会话函数.docx》由会员分享，可在线阅读，更多相关《oracle会话函数.docx（20页珍藏版）》请在冰豆网上搜索。

oracle会话函数

DUMP（w[,x[,y[,z]]]）

【功能】返回数据类型、字节长度和在内部的存储位置.

【参数】

w为各种类型的字符串（如字符型、数值型、日期型……）

x为返回位置用什么方式表达，可为:

8,10,16或17，分别表示：

8/10/16进制和字符型，默认为10。

y和z决定了内部参数位置

【返回】类型<[长度]>，符号/指数位[数字1，数字2，数字3，......，数字20]

如：

Typ=2Len=7:

60,89,67,45,23,11,102

SELECTDUMP（'ABC',1016）FROMdual;　

返回结果为：

Typ=96Len=3CharacterSet=ZHS16GBK:

41,42,43

　　代码数据类型

　　0对应VARCHAR2

　　1对应NUMBER

　　8对应LONG

　　12对应DATE

　　23对应RAW

　　24对应LONGRAW

　　69对应ROWID

　　96对应CHAR

　　106对应MSSLABEL

各位的含义如下:

1.类型:

Number型,Type=2（类型代码可以从Oracle的文档上查到）

2.长度:

指存储的字节数

3.符号/指数位

在存储上,Oracle对正数和负数分别进行存储转换:

正数：

加1存储（为了避免Null）

负数：

被101减,如果总长度小于21个字节，最后加一个102（是为了排序的需要）

指数位换算:

正数：

指数=符号/指数位-193（最高位为1是代表正数）

负数：

指数=62-第一字节

4.从<数字1>开始是有效的数据位

从<数字1>开始是最高有效位,所存储的数值计算方法为：

将下面计算的结果加起来：

每个<数字位>乘以100^（指数-N）（N是有效位数的顺序位，第一个有效位的N=0）

5、举例说明

SQL>selectdump（123456.789）fromdual;

返回：

Typ=2Len=6:

195,13,35,57,79,91

<指数>:

195-193=2

<数字1>13-1=12*100^（2-0）120000

<数字2>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>selectdump（-123456.789）fromdual;

返回：

Typ=2Len=7:

60,89,67,45,23,11,102

算法：

<指数>62-60=2（最高位是0，代表为负数）

<数字1>101-89=12*100^（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

可见，如果不在最后加上102，在排序时会出现-123456.789<-123456.78901的情况。

greatest（exp1,exp2,exp3,……,expn）

【功能】返回表达式列表中值最大的一个。

如果表达式类型不同，会隐含转换为第一个表达式类型。

【参数】exp1……n，各类型表达式

【返回】exp1类型

【示例】

SELECTgreatest（10,32,'123','2006'）FROMdual;

SELECTgreatest（'kdnf','dfd','a','206'）FROMdual;

least（exp1,exp2,exp3,……,expn）

【功能】返回表达式列表中值最小的一个。

如果表达式类型不同，会隐含转换为第一个表达式类型。

【参数】exp1……n，各类型表达式

【返回】exp1类型

【示例】

SELECTleast（10,32,'123','2006'）FROMdual;

SELECTleast（'kdnf','dfd','a','206'）FROMdual;

【语法】NVL（expr1,expr2）

【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回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.

Language:

返回当前会话对应的语言、地区和字符集。

LANG:

返回当前环境的语言的缩写

Terminal:

返回当前会话所在终端的操作系统标识符。

Sessionid:

返回正在使用的审计会话号.

Client_info:

返回用户会话信息，若没有则返回null.

【返回】根据参数不同则类型不同

【示例】

Selectuserenv（'isdba'）,userenv（'Language'）,userenv（'Terminal'）,userenv（'Client_info'）fromdual

decode（条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）

【功能】根据条件返回相应值

【参数】c1,c2,...,cn,字符型/数值型/日期型，必须类型相同或null

注：

值1……n不能为条件表达式,这种情况只能用casewhenthenend解决

·含义解释：

　　decode（条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）　　

　　该函数的含义如下：

　　IF条件=值1THEN

　　RETURN（翻译值1）

　　ELSIF条件=值2THEN

　　RETURN（翻译值2）

　　......

　　ELSIF条件=值nTHEN

　　RETURN（翻译值n）　　

　　ELSE

　　RETURN（缺省值）

　　ENDIF

或：

　　whencase条件=值1THEN

　　RETURN（翻译值1）

　　ElseCase条件=值2THEN

　　RETURN（翻译值2）

　　......

　　ElseCase条件=值nTHEN

　　RETURN（翻译值n）　　

　　ELSE

　　RETURN（缺省值）

　　END

【示例】

　　·使用方法：

　　1、比较大小　　

　　selectdecode（sign（变量1-变量2）,-1,变量1,变量2）fromdual;--取较小值

　　sign（）函数根据某个值是0、正数还是负数，分别返回0、1、-1　　

　　例如：

　　变量1=10，变量2=20

　　则sign（变量1-变量2）返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

　　2、表、视图结构转化　　

　　现有一个商品销售表sale，表结构为：

　　month　　　char（6）　　　　　--月份

　　sell　　　　number（10,2）　　　--月销售金额　　

　　现有数据为：

　　200001　　1000

　　200002　　1100

　　200003　　1200

　　200004　　1300

　　200005　　1400

　　200006　　1500

　　200007　　1600

　　200101　　1100

　　200202　　1200

　　200301　　1300

　　想要转化为以下结构的数据：

　　year　　　char（4）　　　　　--年份

　　month1　　number（10,2）　　　--1月销售金额

　　month2　　number（10,2）　　　--2月销售金额

　　month3　　number（10,2）　　　--3月销售金额

　　month4　　number（10,2）　　　--4月销售金额

　　month5　　number（10,2）　　　--5月销售金额

　　month6　　number（10,2）　　　--6月销售金额

　　month7　　number（10,2）　　　--7月销售金额

　　month8　　number（10,2）　　　--8月销售金额

　　month9　　number（10,2）　　　--9月销售金额

　　month10　　number（10,2）　　　--10月销售金额

　　month11　　number（10,2）　　　--11月销售金额

　　month12　　number（10,2）　　　--12月销售金额

　　结构转化的SQL语句为：

　　createorreplaceview

　　v_sale（year,month1,month2,month3,month4,month5,month6,　　

　　month7,month8,month9,month10,month11,month12）

　　as

　　select

　　substrb（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（month,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（substrb（month,5,2）,'12',sell,0））

　　fromsale

　　groupbysubstrb（month,1,4）;

【语法】NULLIF（expr1,expr2）

【功能】expr1和expr2相等返回NULL，不相等返回expr1

COALESCE（c1,c2,...,cn）

【功能】返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值

【参数】c1,c2,...,cn,字符型/数值型/日期型，必须类型相同或null

【返回】同参数类型

【说明】从Oracle9i版开始，COALESCE函数在很多情况下就成为替代CASE语句的一条捷径

【示例】

selectCOALESCE（null,3*5,44）hzfromdual;返回15

selectCOALESCE（0,3*5,44）hzfromdual;返回0

selectCOALESCE（null,'','AAA'）hzfromdual;返回AAA

selectCOALESCE（'','AAA'）hzfromdual;返回AAA

rownum

【功能】返回当前行号

【参数】无

【返回】数值型

BFILENAME（dir,file）

【功能】函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。

【参数】dir是一个directory类型的对象，file为一文件名。

insertintolobdemo（key,bfile_col）values（-1,biflename（'utils','file1'））;

VSIZE（X）

【功能】返回X的大小（字节）数

【参数】x

selectvsize（user）,userfromdual;

返回：

6asdied

　　selectlength（'adfad合理'）"bytesLengthIs"fromdual--7

　　selectlengthb（'adfad'）"bytesLengthIs"fromdual--5

　　selectlengthb（'adfad合理'）"bytesLengthIs"fromdual--9

　　selectvsize（'adfad合理'）"bytesLengthIs"fromdual--9

　　selectlengthc（'adfad合理'）"bytesLengthIs"fromdual--7

　　lengthb=vsize

　　lengthc=length

case[<表达式>]

when<表达式条件值1>then<满足条件时返回值1>

[when<表达式条件值2>then<满足条件时返回值2>

……

[else<不满足上述条件时返回值>]]

end

【功能】当：

<表达式>＝<表达式条件值1……n>时，返回对应<满足条件时返回值1……n>

当<表达式条件值1……n>不为条件表达式时，与函数decode（）相同，

decode（<表达式>,<表达式条件值1>,<满足条件时返回值1>,<表达式条件值2>,<满足条件时返回值2>……,<不满足上述条件时返回值>）

【参数】

<表达式>默认为true（逻辑型）

<表达式条件值1……n>类型要与<表达式>类型一致，

若<表达式>为字符型，则<表达式条件值1……n>也要为字符型

【注意点】

1、以CASE开头，以END结尾

2、分支中WHEN后跟条件，THEN为显示结果

3、ELSE为除此之外的默认情况，类似于高级语言程序中switchcase的default，可以不加

4、END后跟别名

5、只返回第一个符合条件的值,剩下的when部分将会被自动忽略，得注意条件先后顺序

【示例】

建立环境：

createtablexqb

（xqnnumber（1,0））;

insertintoxqbxqnvalues

（1）;

insertintoxqbxqnvalues

（2）;

insertintoxqbxqnvalues（3）;

insertintoxqbxqnvalues（4）;

insertintoxqbxqnvalues（5）;

insertintoxqbxqnvalues（6）;

insertintoxqbxqnvalues（7）;

commit;

查询结果：

SELECTxqn,

CASE

WHENxqn=1THEN'星期一'

WHENxqn=2THEN'星期二'

WHENxqn=3THEN'星期三'

else'星期三以后'

END星期

FROMxqb

另类写法

SELECTxqn,

CASExqn

WHEN1THEN'星期一'

WHEN2THEN'星期二'

WHEN3THEN'星期三'

else'星期三以后'

END星期

FROMxqb

decode正确表达：

SELECTxqn,

decode（xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后'）星期

FROMxqb

decode错误表达：

SELECTxqn,

decode（TRUE,xqn=1,'星期一',xqn=2,'星期二',xqn=3,'星期三','星期三以后'）星期

FROMxqb

组合条件表达：

SELECTxqn,

CASE

WHENxqn<=1THEN'星期一'

WHENxqn<=2THEN'星期二'--条件同：

not（xqn<=1）andxqn<=2

WHENxqn<=3THEN'星期三'--条件同：

not（xqn<=1andxqn<=2）andxqn<=3

else'星期三以后'

END星期

FROMxqb

【语法】sys_guid（）

【功能】生产32位的随机数，不过中间包括一些大写的英文字母。

【返回】长度为32位的字符串，包括0－9和大写A－F

【示例】

selectsys_guid（）fromdual

【语法】SYS_CONTEXT（c1,c2）

【功能】返回系统c1对应的c2的值。

可以使用在SQL/PLSQL中，但不可以用在并行查询或者RAC环境中

【参数】

c1，'USERENV'

c2,参数表，详见示例

【返回】字符串

【示例】

select

SYS_CONTEXT（'USERENV','TERMINAL'）terminal,

SYS_CONTEXT（'USERENV','LANGUAGE'）language,

SYS_CONTEXT（'USERENV','SESSIONID'）sessionid,

SYS_CONTEXT（'USERENV','INSTANCE'）instance,

SYS_CONTEXT（'USERENV','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（'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','CURRENT_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,

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_CONTEXT（'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','AUTHENTICATION_TYPE'）authentication_type,

SYS_CONTEXT（'USERENV','AUTHENTICATION_DATA'）authentication_data

fromdual

Oracledbms_random包的用法

from:

1.dbms_random.value方法

dbms_random是一个可以生成随机数值或者字符串的程序包。

这个包有initialize（）、seed（）、terminate（）、value（）、normal（）、random（）、string（）等几个函数，但value（）是最常用的，value（）的用法一般有两个种，第一

functionvaluereturnnumber;

这种用法没有参数，会返回一个具有38位精度的数值，范围从0.0到1.0，但不包括1.0，如下示例：

SQL>setserverouton

SQL>begin

2foriin1..10loop

3dbms_output.put_line（round（dbms_random.value*100））;

4endloop;

5end;

6/

46

19

45

37

33

57

61

20

82

8

PL/SQL过程已成功完成。

SQL>

第二种value带有两个参数，第一个指下限，第二个指上限，将会生成下限到上限之间的数字，但不包含上限，“学无止境”兄说的就是第二种，如下：

SQL>begin

2foriin1..10loop

3