Oracle常用函数/分页/序列

1、字符函数

   1.1、lower(列名|表达式)字符转小写

     select lower('AAA') from  dual;

   1.2、upper(列名|表达式)字符转大写

     select upper('aaa') from  dual;

   1.3、initcap(列名|表达式)将字符开头字母转大写其余转小写

     select initcap('aaAa') from  dual;

   1.4、concat(列名|表达式,列名|表达式)字符串连接函数

     select concat('aaa','bbb') from  dual;

   1.5、substr(列名|表达式,m,[n])字符串截取函数

     select substr('aa1133',0,3) from  dual;

   1.6、length(列名|表达式)获取字符串长度函数

     select length('aa1133') from  dual;

   1.7、instr(列名|表达式)查找字符的位置函数

     select instr('aa1133','1') from dual;

   1.8、trim([leading|trailing|both])从字符串中的头部和尾部去掉指定的字符

     select trim('?'from'??abc??') from dual;

   1.9、去掉空格

     select ltrim('   aaa  ') from dual;--去掉左空格

     select rtrim('   aaa  ') from dual;--去掉右空格

     select trim('   aaa  ') from dual;--去掉空格

   1.10、replace(列名|表达式,列名|表达式,列名|表达式)字符替换

     select replace('abc','b','@') from dual;

   1.11、translate字符转换函数

     select translate('abc','b','xx') from dual;

   1.12、lpad [左添充] rpad [右填充](用于控制输出格式)

     select lpad('func',6,'=') s1, rpad('func',6,'-') s2 from dual;

 

2、数学函数

   2.1、round(列名|表达式,n)四舍五入到小数点后的n位

   select round(198.89,1) from dual;  

   2.2、trunc(列名|表达式,n)截取到小数点后的n位

   select trunc(198.89,1) from dual;  

   2.3、mod(m,n)取余数

   select mod(8,3) from dual;

3、日期函数

   3.1、sysdate返回系统时间

   select sysdate from dual; 

   3.2、add_months(time,months)函数可以得到某一时间之前或之后n个月的时间

   select add_months(sysdate,-6) from dual;

   3.3、last_day(d)：返回指定日期所在月份的最后一天

   select last_day(sysdate) from dual;

   Oracle日期、字符串格式化函数

函数返回类型描述例子

to_char(timestamp, text)	text	把时间戳转换成字串	to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)	text	把时间间隔转为字串	to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)	text	把整数转换成字串	to_char(125, '999')
to_char(double precision,text)	text	把实数/双精度数转换成字串	to_char(125.8, '999D9')
to_char(numeric, text)	text	把 numeric 转换成字串	to_char(numeric '-125.8', '999D99S')
to_date(text, text)	date	把字串转换成日期	to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)	date	把字串转换成时间戳	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	把字串转换成 numeric	to_number('12,454.8-', '99G999D9S')

 

用于日期/时间格式化的模板

 

模板描述

HH	一天的小时数 （01-12）
HH12	一天的小时数 （01-12）
HH24	一天的小时数 （00-23）
MI	分钟 （00-59）
SS	秒 （00-59）
MS	毫秒 (000-999)
US	微秒 (000000-999999)
SSSS	午夜后的秒 （0-86399）
AM 或 A.M. 或 PM 或 P.M.	正午标识（大写）
am 或 a.m. 或 pm 或 p.m.	正午标识（小写）
Y,YYY	带逗号的年（4 和更多位）
YYYY	年（4和更多位）
YYY	年的后三位
YY	年的后两位
Y	年的最后一位
BC 或 B.C. 或 AD 或 A.D.	纪元标识（大写）
bc 或 b.c. 或 ad 或 a.d.	纪元标识（小写）
MONTH	全长大写月份名（空白填充为9字符）
Month	全长混合大小写月份名（空白填充为9字符）
month	全长小写月份名（空白填充为9字符）
MON	大写缩写月份名（3字符）
Mon	缩写混合大小写月份名（3字符）
mon	小写缩写月份名（3字符）
MM	月份号（01-12）
DAY	全长大写日期名（空白填充为9字符）
Day	全长混合大小写日期名（空白填充为9字符）
day	全长小写日期名（空白填充为9字符）
DY	缩写大写日期名（3字符）
Dy	缩写混合大小写日期名（3字符）
dy	缩写小写日期名（3字符）
DDD	一年里的日子（001-366）
DD	一个月里的日子（01-31）
D	一周里的日子（1-7；周日=1）
W	一个月里的周数（1-5）（第一周从该月第一天开始）
WW	一年里的周数（1-53）（第一周从该年的第一天开始）
IW	ISO 一年里的周数（第一个星期四在第一周里）
CC	世纪（2 位）
J	儒略日（自公元前4712年1月1日来的天数）
Q	季度
RM	罗马数字的月份（I-XII；I=JAN）（大写）
rm	罗马数字的月份（I-XII；I=JAN）（小写）
TZ	时区字串 （大写）
tz	时区字串 （小写）

例子：

--美式时间
select to_date('Aug 29 2014 1:54PM', 'Month DD YYYY hh:miam' ,'nls_date_language=American') tm
from users;
--中式时间
select to_date('6月 29 2014 1:54', 'Month DD YYYY hh24:mi') tm
from users; 

 

注意：nls_date_language=American用于指定语言，解决月分无效的问题

 

4、转换函数

   4.1、to_date(列名|表达式,m)字符转日期

   select to_date('2015-05-12 11:21:00','yyyy-mm-dd hh24:mi:ss') from dual;

   4.2、to_char(列名|表达式,m)日期转字符

   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

   4.3、to_number(列名|表达式) 转换为数字类型 

   select to_number(to_char(sysdate,'hh12')) from dual;

 

5、其它

   5.1、decode[实现if ..then 逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值

   select decode(u.status,'0','不可用','1','可用','其它') status from users u where rownum <=10;

   5.2、case when[实现switch ..case 逻辑]

   select (case when u.status = '0' then '不可用' 

               when u.status = '1' then '可用' 

               else '其它' 

               end) as  status from users u where rownum <=10;  

 

   5.3、nvl(列名|表达式,列名|表达式) 第一个值为空则返回第二个值,否则返回第一个值 

   select nvl(u.name,'请填写名字') from users u where rownum <=10;

 

   5.4、nvl2(列名|表达式,列名|表达式,列名|表达式) 如果第一个值不为空，显示第二个值,否则显示第三个值

   select nvl2(u.name,u.name,'请填写名字') from users u where rownum <=10;  

 

   5.5、nullif(列名|表达式,列名|表达式) 值相等返空，否则返回第一个值

   select nullif('2','1') as value1,nullif('1','1') as value2 from dual;          

 

   5.6、返回登录的用户名称 

   select user from dual;

 

   5.7、coalesce 返回列表中第一个非空表达式

   select coalesce(null,null,'1','2',null) from dual;

 

6、分页查询，需要引入一个rownum的函数

   6.1、使用rownum嵌套查询

   select * from ( select e.* , rownum as rn from emp e where rownum  <= 10 ) t where t.rn >= 1

   6.2、方法一的另一种写法

   select * from ( select e.* , rownum as rn from emp e) t where t.rn >= 1 and t.rn <=10;

   6.3、使用分析函数row_number

   select * from ( select t.* , row_number() over(order by ename) as rn from emp t ) where rn>=1 and rn<=10

   6.4、使用rowid

   select * from emp where rowid in ( select rid from (select rowid as rid, rownum as id from emp t where rownum < 15) where id >= 10 );

   6.5、使用minus

   select * from emp where rownum <= 15 minus select * from emp where rownum < 10;

7、序列

7.1、创建序列语法：(需要CREATE SEQUENCE系统权限)

        CREATE SEQUENCE 序列名

　　　[START WITH n]

　　　[INCREMENT BY n]

　　　[{MINVALUE n}]

　　　[{MAXVALUE n|NOMAXVALUE}]

　　　[{CYCLE|NOCYCLE}]

　　　[{CACHE n|NOCACHE}];

  MINVALUE：定义序列生成器能产生的最小值。NOMAXVALUE是默认选项(表示没有最小值定义)，

  这时对于递减序列，系统能够产生的最小值是，10的26次方;对于递增序列，最小值是1。

  MAXVALUE n|NOMAXVALUE：定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，

  代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，

  最大值是-1。

  START WITH：定义序列的初始值(即产生的第一个值)，默认为1。

  INCREMENT：用于定义序列的步长。如果省略，则默认为1，如果出现负值，

  则代表序列的值是按照此步长递减的。

  CYCLE|NOCYCLE：表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，

  NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达

  到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

  CACHE n|NOCACHE：CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示

  不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

  例子：

 　 create sequence seq_test_1

 　 minvalue 1

  　maxvalue 100000

  　start with 15

  　increment by 1

  　nocache;

  应用：

　select seq_test_1.nextval from dual;

7.2、修改序列语法：

        ALTER SEQUENCE 序列名

　　　[INCREMENT BY n]

　　　[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]

　　　[{CYCLE|NOCYCLE}]

　　　[{CACHE n|NOCACHE}];

注：不能修改序列的初始值

7.3、删除序列语法：

DROP SEQUENCE 序列名;