oracle会话函数.docx
- 文档编号:10826860
- 上传时间:2023-02-23
- 格式:DOCX
- 页数:20
- 大小:20.81KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 会话 函数