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

r囧r小猫 2021-12-09 07:55 224阅读 0赞

*-- 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' 任意可显示字符*

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Python内置函数用法介绍

相关 SqlServer 常用内置函数及用法

相关 Python常用内置函数

相关 Mysql常用内置函数

相关 MySQL 常用内置函数与所有内置函数

相关 Oracle常用内置函数

相关 python常用内置函数（二）

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

相关 Oracle常用内置函数介绍及用法(一)

相关 Python基础（二）——常用内置函数