Oracle常用函数大全(应有尽有)——其他函数

Oracle的函数很多,在这里整理一下,方便使用。

Oracle数值型函数

Oracle字符型函数

Oracle日期函数

Oracle转换函数

Oracle聚组函数

Oracle分析函数


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,102

SELECT DUMP('ABC',1016) FROM dual; 
返回结果为:Typ=96 Len=3 CharacterSet=ZHS16GBK: 41,42,43 

  代码 数据类型
  0 对应 VARCHAR2
  1 对应 NUMBER
  8 对应 LONG
  12 对应 DATE
  23 对应 RAW
  24 对应 LONG RAW
  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> 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>     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 *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类型

【示例】
  SELECT greatest(10,32,'123','2006') FROM dual;

   SELECT greatest('kdnf','dfd','a','206') FROM dual;


least(exp1,exp2,exp3,……,expn)
【功能】返回表达式列表中值最小的一个。如果表达式类型不同,会隐含转换为第一个表达式类型。
【参数】exp1……n,各类型表达式
【返回】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,返回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.
【返回】根据参数不同则类型不同

【示例】
Select userenv('isdba'),userenv('Language'),userenv('Terminal'),userenv('Client_info') from dual;


decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
【功能】根据条件返回相应值
【参数】c1, c2, ...,cn,字符型/数值型/日期型,必须类型相同或null
注:值1……n 不能为条件表达式,这种情况只能用case when then end解决

·含义解释:  
  decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)  
  该函数的含义如下:  
  IF 条件=值1 THEN
  RETURN(翻译值1)
  ELSIF 条件=值2 THEN
  RETURN(翻译值2)
  ......
  ELSIF 条件=值n THEN
  RETURN(翻译值n)  
  ELSE
  RETURN(缺省值)
  END IF

或:
  when case 条件=值1 THEN
  RETURN(翻译值1)
  ElseCase 条件=值2 THEN
  RETURN(翻译值2)
  ......
  ElseCase 条件=值n THEN
  RETURN(翻译值n)  
  ELSE
  RETURN(缺省值)
  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)      --月份
  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语句为:

  create or replace view
  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))
  from sale
  group by substrb(month,1,4);


【语法】NULLIF (expr1, expr2)
【功能】expr1和expr2相等返回NULL,不相等返回expr1


COALESCE(c1, c2, ...,cn)
【功能】返回列表中第一个非空的表达式,如果所有表达式都为空值则返回1个空值
【参数】c1, c2, ...,cn,字符型/数值型/日期型,必须类型相同或null
【返回】同参数类型
【说明】从Oracle 9i版开始,COALESCE函数在很多情况下就成为替代CASE语句的一条捷径
【示例】
select COALESCE(null,3*5,44) hz from dual; 返回15
select COALESCE(0,3*5,44) hz from dual; 返回0
select COALESCE(null,'','AAA') hz from dual; 返回AAA
select COALESCE('','AAA') hz from dual; 返回AAA


rownum
【功能】返回当前行号
【参数】无

【返回】数值型


BFILENAME(dir,file)
【功能】函数返回一个空的BFILE位置值指示符,函数用于初始化BFILE变量或者是BFILE列。
【参数】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 asdied

  select length('adfad合理') "bytesLengthIs" from dual --7

  select lengthb('adfad') "bytesLengthIs" from dual --5

  select lengthb('adfad合理') "bytesLengthIs" from dual --9

  select vsize('adfad合理') "bytesLengthIs" from dual --9

  select lengthc('adfad合理')"bytesLengthIs" from dual --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 为除此之外的默认情况,类似于高级语言程序中switch case的default,可以不加
4、END 后跟别名
5、只返回第一个符合条件的值,剩下的when部分将会被自动忽略,得注意条件先后顺序

【示例】
建立环境:
create table 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 = 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 xqb

decode正确表达:
SELECT xqn,
decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期
FROM xqb

decode错误表达:
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 <= 3  THEN '星期三'   --条件同:not(xqn<=1 and xqn<=2) and xqn<=3
   else '星期三以后'
       END 星期
FROM xqb


【语法】sys_guid()
【功能】生产32位的随机数,不过中间包括一些大写的英文字母。

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

【示例】
select sys_guid() from  dual


【语法】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
from dual


Oracle dbms_random包的用法

from:http://space.myfarmer.cn/?action-viewthread-tid-17039

1.dbms_random.value方法

dbms_random是一个可以生成随机数值或者字符串的程序包。这个包有initialize()、seed()、terminate()、value()、normal()、random()、string()等几个函数,但value()是最常用的,value()的用法一般有两个种,第一
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

PL/SQL 过程已成功完成。 

SQL>  

第二种value带有两个参数,第一个指下限,第二个指上限,将会生成下限到上限之间的数字,但不包含上限,“学无止境”兄说的就是第二种,如下:
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 可以实现这样的功能。

例如:
SQL> 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' - returning string in mixed case alpha characters
■ 'x', 'X' - returning string in uppercase alpha-numeric
characters
■ 'p', 'P' - returning string in any printable characters.
Otherwise the returning string is in uppercase alpha
characters.
P 表示 printable,即字符串由任意可打印字符构成

而第二个参数表示返回的字符串长度。
3. dbms_random.random 方法
    random返回的是BINARY_INTEGER类型值,产生一个任意大小的随机数
  与dbms_random.value 的区别举例:
   Order By dbms_random.value;
这条语句功能是实现记录的随机排序
另外:
dbms_random.value 和
dbms_random.random 两者之间有什么区别?
1。Order By dbms_random.value ,为结果集的每一行计算一个随机数,dbms_random.value 是结果集的一个列(虽然这个列并不在select list 中),然后根据该列排序,得到的顺序自然就是随机的啦。
2。看看desc信息便知道vlue和random这两个函数的区别了,value返回的是number类型,并且返回的值介于1和0之间,而random返回的是BINARY_INTEGER类型(以二进制形式存储的数字,据说运算的效率高于number但我没测试过,但取值范围肯定小于number,具体限制得查资料了)
如果你要实现随机排序,还是用value函数吧

4. dbms_random.normal方法 

NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1,期望值为0。这个函数返回的数值中有68%是介于-1与+1之间,95%介于-2与+2之间,99%介于-3与+3之间。

5. dbms_random.send方法  

用于生成一个随机数种子,设置种子的目的是可以重复生成随机数,用于调试。否则每次不同,难以调度。


Oracle包utl_inaddr
作用:用于取得局域网或Internet环境中的主机名和IP地址. 

1、utl_inaddr.get_host_address 环境中IP地址
如果查询失败,则提示系统错误
查询www.qq.com的IP地址
select UTL_INADDR.get_host_address('www.qq.com') from dual;
查询本机IP地址
select UTL_INADDR.get_host_address() from dual;
查询局域网内yuechu的IP地址
select UTL_INADDR.get_host_address('yuechu') from dual;

2、UTL_INADDR.get_host_name返回环境中主机名
返回本机主机名
select UTL_INADDR.get_host_name() from dual;
返回局域网内指定IP地址的主机名
select UTL_INADDR.get_host_name('192.168.0.156') from dual;
返回intrenet中指定IP地址的网址
select UTL_INADDR.get_host_name('219.153.50.84') from dual;


 

发布了21 篇原创文章 · 获赞 36 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览