Oracle常用内置函数介绍及用法(二)

来源：互联网发布：mysql deadlock 解决编辑：程序博客网时间：2024/05/22 11:42

-- REGEXP_LIKE 正则表达式

ORACLE中的支持正则表达式的函数主要有下面四个：

1，REGEXP_LIKE ：与LIKE的功能相似

2，REGEXP_INSTR ：与INSTR的功能相似

3，REGEXP_SUBSTR ：与SUBSTR的功能相似

4，REGEXP_REPLACE ：与REPLACE的功能相似

它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，

但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。

POSIX 正则表达式由标准的元字符（metacharacters）所构成：

'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。

'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'。

'.' 匹配除换行符之外的任何单字符。

'?' 匹配前面的子表达式零次或一次。

'+' 匹配前面的子表达式一次或多次。

'*' 匹配前面的子表达式零次或多次。

'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。

'( )' 标记一个子表达式的开始和结束位置。

'[]' 标记一个中括号表达式。

'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。

\num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。

字符簇：

[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:space:]] 任何白字符。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[:punct:]] 任何标点符号。

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

各种操作符的运算优先级

\转义符

(), (?:), (?=), [] 圆括号和方括号

*, +, ?, {n}, {n,}, {n,m} 限定符

^, $, anymetacharacter 位置和顺序

--创建表

create table fzq (

id varchar(4),

value varchar(10)

);

--数据插入

insert into fzq values ('1','1234560');

insert into fzq values ('2','1234560');

insert into fzq values ('3','1b3b560');

insert into fzq values ('4','abc');

insert into fzq values ('5','abcde');

insert into fzq values ('6','ADREasx');

insert into fzq values ('7','123 45');

insert into fzq values ('8','adc de');

insert into fzq values ('9','adc,.de');

insert into fzq values ('10','1B');

insert into fzq values ('10','abcbvbnb');

insert into fzq values ('11','11114560');

insert into fzq values ('11','11124560');

--regexp_like

--查询value中以1开头60结束的记录并且长度是7位

select * from fzq where value like '1____60';

select * from fzq where regexp_like(value,'1....60');

--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。

--使用like就不是很好实现了。

select * from fzq where regexp_like(value,'1[0-9]{4}60');

-- 也可以这样实现，使用字符集。

select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');

-- 查询value中不是纯数字的记录

select * from fzq where not regexp_like(value,'^[[:digit:]]+$');

-- 查询value中不包含任何数字的记录。

select * from fzq where regexp_like(value,'^[^[:digit:]]+$');

--查询以12或者1b开头的记录.不区分大小写。

select * from fzq where regexp_like(value,'^1[2b]','i');

--查询以12或者1b开头的记录.区分大小写。

select * from fzq where regexp_like(value,'^1[2B]');

-- 查询数据中包含空白的记录。

select * from fzq where regexp_like(value,'[[:space:]]');

--查询所有包含小写字母或者数字的记录。

select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');

--查询任何包含标点符号的记录。

select * from fzq where regexp_like(value,'[[:punct:]]');

-- uid 返回标识当前用户的唯一整数

select username,user_id from dba_users where user_id=uid;

-- user 返回当前用户的名字

select user from dual;

sql%rowcount;

--统计上一条SQL语句影响的行数

-- datepart() datename()

detepart() 用来抽取日期的特定部分

detepart() 有两个参数，第一个指定要抽取日期的哪一部分；第二个变量是实际的数据。

日期的各部分及其简写如下：

日期部分简写值

year yy 1753--9999

quarter qq 1--4

month mm 1--12

day of year dy 1--366

day dd 1--31

week wk 1--53

weekday dw 1--7(Sunday--Saturday)

hour hh 0--23

minute mi 0--59

second ss 0--59

milisecond ms 0--999

函数DATENAME()和DATEPART()接收同样的参数;其返回值是一个字符串(数字对应的字符串);

--选择某个周的时间段

Select case when to_char(sysdate, 'D') = 1 then to_char(sysdate - to_char(sysdate, 'D') - 5, 'yyyy-mm-dd')

else to_char(sysdate - to_char(sysdate, 'D') + 2, 'yyyy-mm-dd') end week_begin

from dual ;

Select case when to_char(sysdate, 'D') = 1 then to_char(sysdate - to_char(sysdate, 'D') + 2, 'yyyy-mm-dd')

else to_char(sysdate - to_char(sysdate, 'D') + 9, 'yyyy-mm-dd') end week_begin

from dual ;

Select case when to_char(sysdate, 'D') = 1 then to_char(sysdate - to_char(sysdate, 'D') - 12, 'yyyy-mm-dd')

else to_char(sysdate - to_char(sysdate, 'D') - 5, 'yyyy-mm-dd') end week_begin

from dual ;

----------------------------------------------------------------

Select to_char(sysdate, 'D'),

case when to_char(sysdate, 'D') = 1 then to_char(sysdate - to_char(sysdate, 'D') + 1, 'yyyy-mm-dd')

else to_char(sysdate - to_char(sysdate, 'D') + 8, 'yyyy-mm-dd') end week_end

from dual ;

Select to_char(sysdate, 'D'),

case when to_char(sysdate, 'D') = 1 then to_char(sysdate - to_char(sysdate, 'D') + 8, 'yyyy-mm-dd')

else to_char(sysdate - to_char(sysdate, 'D') + 15, 'yyyy-mm-dd') end week_end

from dual ;

Select case when to_char(sysdate, 'D') = 1 then to_char(sysdate - to_char(sysdate, 'D') - 6, 'yyyy-mm-dd')

else to_char(sysdate - to_char(sysdate, 'D') + 1, 'yyyy-mm-dd') end week_end

from dual;

round(no):四舍五入,精确到个位: select round(123.45) from dual; --123

round(no,n):四舍五入,若n>0,精确到小数点右边第n位;若n<0,则精确到小数点左边第(n+1)位;

select round(123.456,2) from dual; --123.46

select round(123.456,-2) from dual; --100

instr(C1,C2[,I[,J]]):

instrb(C1,C2[,I[,J]]):

在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 子串的第J次出现的位置,默认为1

一般用来判断某值是否存在于某值中

select instr('oracle traning', 'ra', 1, 2) instring from dual;

select 'a' a from dual where instr('football,basketball,pingpang', 'football') >=1;

-- trunc(date[,accuracy])截取

date:日期

accuracy:精确度

accuracy='yyyy' --> 精确度为年,月日时分秒的值舍去

accuracy='MM' --> 精确度为月,日时分秒的值舍去

accuracy='dd'(或者省略) --> 精确度为日,时分秒的值舍去

accuracy='hh' --> 精确度为时,分秒的值舍去

accuracy='mi' --> 精确度为分,秒的值舍去

accuracy='yyyy' --> 报错

--trunc(date[,accuracy]) 再加上(或者减去) 任意时间，能够标示任意的日期时间值,如下:

select to_char(trunc(sysdate),'yyyy-MM-dd hh24:mi:ss') from dual; --2012-08-03 00:00:00

select to_char(trunc(sysdate) + 10/(24*60*60),'yyyy-mm-dd hh24:mi:ss') from dual; --2012-08-03 00:00:10

select to_char(trunc(sysdate,'yyyy'),'yyyy-MM-dd hh24:mi:ss') from dual; --2012-01-01 00:00:00

select to_char(trunc(sysdate,'yyyy') + 4/(24*60*60), 'yyyy-mm-dd hh24:mi:ss') from dual; --2012-01-01 00:00:04

select to_char(trunc(sysdate,'MM'),'yyyy-MM-dd hh24:mi:ss') from dual; --2012-08-01 00:00:00

select to_char(trunc(sysdate,'MM') + (1+9/24), 'yyyy-mm-dd hh24:mi:ss') from dual; --2012-08-02 09:00:00

select to_char(trunc(sysdate,'dd'),'yyyy-MM-dd hh24:mi:ss') from dual; --2012-08-03 00:00:00

select to_char(trunc(sysdate) + 5/24, 'yyyy-mm-dd hh24:mi:ss') from dual; --2012-08-03 05:00:00

select to_char(trunc(sysdate,'hh'),'yyyy-MM-dd hh24:mi:ss') from dual; --2012-08-03 14:00:00

select to_char(trunc(sysdate,'hh') + 16/(24*60),'yyyy-mm-dd hh24:mi:ss') from dual; --2012-08-03 14:16:00

select to_char(trunc(sysdate,'mi'),'yyyy-MM-dd hh24:mi:ss') from dual; --2012-08-03 14:26:00

-- convert(data_type(length), data, style)

把日期转换为新数据类型的通用函数;

用不同的格式显示日期/时间数据

data_type(length) 规定目标数据类型(带有可选的长度)

data 需要转换的值

style 规定日期/时间的输出格式;

可使用的 style 值如下:

Style_ID Style_格式

100 or 0 mon dd yyyy hh:miAM （或者 PM）

101 mm/dd/yy

102 yy.mm.dd

103 dd/mm/yy

104 dd.mm.yy

105 dd-mm-yy

106 dd mon yy

107 Mon dd, yy

108 hh:mm:ss

109 or 9 mon dd yyyy hh:mi:ss:mmmAM（或者 PM）

110 mm-dd-yy

111 yy/mm/dd

112 yymmdd

113 or 13 dd mon yyyy hh:mm:ss:mmm(24h)

114 hh:mi:ss:mmm(24h)

120 or 20 yyyy-mm-dd hh:mi:ss(24h)

121 or 21 yyyy-mm-dd hh:mi:ss.mmm(24h)

126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）

130 dd mon yyyy hh:mi:ss:mmmAM

131 dd/mm/yy hh:mi:ss:mmmAM

select CONVERT(VARCHAR(19),GETDATE()) from dual; -- Dec 29 2008 11:45 PM

select CONVERT(VARCHAR(10),GETDATE(),110) from dual; -- 12-29-2008

select CONVERT(VARCHAR(11),GETDATE(),106) from dual; -- 29 Dec 08

select CONVERT(VARCHAR(24),GETDATE(),113) from dual; -- 29 Dec 2008 16:25:46.635

/* Oracle dbms_random 包用法整理 */

dbms_random.value

--返回一个具有38位精度的随机数，范围0.0 <= dbms_random.value < 1.0

SQL>select dbms_random.value from dual;

dbms_random.value(min_value, max_value)

--返回[min_value, max_value)范围内的随机数(精度38位)

SQL>select dbms_random.value(0,100) from dual;

trunc(dbms_random.value(min_value, max_value))

--返回指定范围内的整数

SQL>select trunc(dbms_random.value(0,100)) from dual;

dbms_random.random

--返回一个从 -power(2,31) 到 power(2,31) 的随机整数

SQL>select dbms_random.random from dual;

--dbms_random.random 与 dbms_random.value 区别

dbms_random.value 返回 number 类型,范围是 (0, 1] 的随机数

dbms_random.random 返回 BINARY_INTEGER 类型,任意大小的随机数

随机排序:Order By dbms_random.value;

--此语句功能是实现结果集的随机排序(dbms_random.value 是结果集的一个隐藏列,按照该列排序,就是随机排序).

dbms_random.normal

--返回服从正态分布的一组数(此正态分布标准偏差为1,期望值为0;

--且返回值中有68%是介于[-1,1]之间,95%介于[-2,2]之间,99%介于[-3,3]之间)

SQL>select dbms_random.normal from dual;

substr(cast(dbms_random.value as varchar2(38)), 3, length)

--返回长度为 length 的随机数字串(3的目的是截掉整数位和小数点)

SQL>select substr(cast(dbms_random.value as varchar2(38)), 3, 20) from dual;

dbms_random.string(opt, length)

--返回由opt组成,长度为length的随机字符串;

--此方法可用于产生随机验证码、随机密码等;

opt可取值如下:

'u','U':大写字母

'l','L':小写字母

'a','A':大,小写字母混合

'x','X':数字,大写字母混合

'p','P':任意可打印(显示)字符

如果type不是以上字符，则产生一个长度为Length,随机的字母序列.

SQL>select dbms_random.string('U',4) FROM DUAL; -- 'u'或'U' 返回大写字母

SQL>select dbms_random.string('l',3) from dual; -- 'l'或'L' 返回小写字母

SQL>select dbms_random.string('a',9) from dual; -- 'a'或'A' 大小写字母混合

SQL>select dbms_random.string('X',6) FROM DUAL; -- 'x'或'X' 大写字母和数字混合

SQL>select dbms_random.string('p',7) from dual; -- 'p'或'P' 任意可显示字符