oracle常用函数

来源：互联网发布：有什么社交软件编辑：程序博客网时间：2024/06/14 07:04

日期函数

格式：LAST_DAY(DATE) 即 LAST_DAY(日期)

找到雇佣日期是当月最后一天的人员信息

select * from emp where hire_date=last_day(hire_date);

SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
31-10月-14

add_months函数，可以获得当前时间之前或之后n个月的时间

语法：add_months(time,months)

SQL> select add_months(sysdate,-2) from dual;---获取前2个月的时间

ADD_MONTHS(SYS
--------------
23-8月 -14

SQL> select add_months(sysdate,2) from dual;--获取后2个月的时间
ADD_MONTHS(SYS
--------------
23-12月-14

month_between函数，返回date1,与date2之间月的数量

语法： months_between(date1, date2)

如果d1和d2是月份中相同的一天，或都是这个月的最后一天，则会返回整数。

如

select months_between(lase_day(sysdate),to_Date('20120129','yyyymmdd')) from dual = 1
select months_between(lase_day(sysdate),to_Date('20120130','yyyymmdd')) from dual = 0.989
select months_between(lase_day(sysdate),to_Date('20120131','yyyymmdd')) from dual = 1

找出25年前受雇的雇员信息

select * from emp where months_between(sysdate,hire_date)/12>25;

next_day(date1,n)指定下一星期的星期n的日期，n可以是1到7，当n是6,7的时候，指定的是当前星期的周六日

SQL> select sysdate from dual;
SYSDATE
--------------
23-10月-14
SQL> select next_day(sysdate,1) from dual;
NEXT_DAY(SYSDA
--------------
26-10月-14
SQL> select next_day(sysdate,2) from dual;
NEXT_DAY(SYSDA
--------------
27-10月-14
SQL> select next_day(sysdate,3) from dual;
NEXT_DAY(SYSDA
--------------
28-10月-14
SQL> select next_day(sysdate,4) from dual;
NEXT_DAY(SYSDA
--------------
29-10月-14
SQL> select next_day(sysdate,5) from dual;
NEXT_DAY(SYSDA
--------------
30-10月-14
SQL> select next_day(sysdate,6) from dual;
NEXT_DAY(SYSDA
--------------
24-10月-14
SQL> select next_day(sysdate,7) from dual;
NEXT_DAY(SYSDA
--------------
25-10月-14
n也可以使星期一、星期二。。。星期日

SQL> select next_day(sysdate,'星期日') from dual;
NEXT_DAY(SYSDA
--------------
26-10月-14

round将日志四舍五入

SQL> select round(sysdate) from dual;
ROUND(SYSDATE)
--------------
23-10月-14
SQL> select round(sysdate,'MONTH') from dual;
ROUND(SYSDATE,
--------------
01-11月-14
SQL> select round(sysdate,'YEAR') from dual;
ROUND(SYSDATE,
--------------
01-1月 -15

trunc:将日期截断

SQL> select trunc(sysdate,'MONTH') from dual;返回当月第一天
TRUNC(SYSDATE,
--------------
01-10月-14
SQL> select trunc(sysdate,'YEAR') from dual;返回当年第一天
TRUNC(SYSDATE,
--------------
01-1月 -14

SQL> select trunc(sysdate,'D') from dual; 返回当前星期第一天
TRUNC(SYSDATE,'D')
-----------------------------
2014-10-19 星期日 00:00:00

控制日期显示格式的设置

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
-------------------
2014-10-23 10:52:47
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD day HH24:MI:SS';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
-----------------------------
2014-10-23 星期四 10:52:55

*******************日期中的操作*****************************

SQL> select to_char(sysdate,'yyyy-mm-dd hh-mi-ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH
------------------------------------------------------------------
2014-10-23 11-53-13
SQL> select to_char(sysdate,'yyyy-mm-dd hh24-mi-ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH
------------------------------------------------------------------
2014-10-23 11-53-30

得到当前时间的前一分钟

select to_char(Sysdate-1/21/60,'HH24:MI:SS')from dual;

得到当前时间的后一分钟

select to_char(Sysdate+1/21/60,'HH24:MI:SS')from dual;

得到当前时间的后一小时

select to_char(Sysdate+1/24,'HH24:MI:SS')from dual;

得到当前时间的后一天

select to_char(Sysdate+1,'YYYY-MM-DD')from dual;

求某天是星期几

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;

星期一

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;

monday

时间为空的用法

select 1, TO_DATE(null) from dual;

注意要用TO_DATE(null)

两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

***********************************************************

字符函数

initcap: 把单词的第一个字母变成大写

SQL> select initcap(name) from t;
INITCAP(NAME)
--------------------
Bai
Bai
Xiao
Yu
Ao
Er
Qi

concat: 连接字符

SQL> select concat(name,'**') from t;
CONCAT(NAME,'**')
--------------------------------------
bai**
bai**
xiao**
yu**
ao**
er**
qi**
已选择7行。

substring截取字串

substr(字符串,截取开始位置,截取长度) //返回截取的字

SQL> select substr('Hello World',0,7) value from dual;
VALUE
---------------------------------------------------------
Hello W
SQL> select substr('Hello World',1,7) value from dual;
VALUE
---------------------------------------------------------
Hello W
SQL> select substr('Hello World',3,-1) value from dual;

VALUE
---------------------------------------------------------

SQL> select substr('Hello World',-3,2) value from dual;
VALUE
---------------------------------------------------------
rl

instr返回匹配字符串在目标串中的索引

语法：INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)匹配序号就是目标字符串第几次出现的位置，找不到的话就是0

SQL> select instr('Hello World','ll',1) from dual;
INSTR('HELLOWORLD','LL',1)
--------------------------
3
SQL> select instr('Hello World','ll',2) from dual;
INSTR('HELLOWORLD','LL',2)
--------------------------
3
SQL> select instr('Hello World','ll',2,2) from dual; ll字符串第二次出现的位置，没有就是0
INSTR('HELLOWORLD','LL',2,2)
----------------------------
0

nvl

语法：NVL( string1, replace_with)

作用：如果string1是null，则返回replace_with

SQL> select * from t;
ID NAME
---------- -------------
1 bai
1 bai
2 xiao
3 yu
4 ao
0 er
qi

找出id为空或id小于3的值

SQL> select id,name from t where nvl(id,0) < 3;
ID NAME
---------- --------------------
1 bai
1 bai
2 xiao
0 er
qi

nvl2

语法：NVL2(E1, E2, E3)

功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。

SQL> select nvl2(id,88,99),name from t;
NVL2(ID,88,99) NAME
-------------- --------------------
88 bai
88 bai
88 xiao
88 yu
88 ao
88 er
99 qi

lpad(string,n,[pad_string])

string：可是字符或者参数

n：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;

pad_string：是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

SQL> select lpad('Hello World',3,'**') from dual;
LPAD('HELLOWORLD',3,'**')
--------------------------------------------------------
Hel
SQL> select lpad('Hello World',11,'**') from dual;
LPAD('HELLOWORLD',11,'**')
--------------------------------------------------------
Hello World
SQL> select lpad('Hello World',12,'**') from dual;
LPAD('HELLOWORLD',12,'**')
--------------------------------------------------------
*Hello World

当字符串的长度小于n的时候才用后面的字符填充的。

rpad也是一样的

SQL> select rpad('Hello World',3,'**') from dual;
RPAD('HELLOWORLD',3,'**')
-----------------------------------------------------
Hel
SQL> select rpad('Hello World',12,'**') from dual;
RPAD('HELLOWORLD',12,'**')
-----------------------------------------------------
Hello World*

trim取出空格，及特定字符

SQL> select length(trim(' Hello World ')) from dual;
LENGTH(TRIM('HELLOWORLD'))
--------------------------
11

SQL> select length(ltrim(' Hello World ')) from dual;
LENGTH(LTRIM('HELLOWORLD'))
---------------------------
12

trim语法

TRIM(leading|trailing|both string1 FROM string2)只能去除单个字符

SQL> select trim(leading 'H' from 'Hello World') from dual;
TRIM(LEADING'H'FROM'HELLOWORL
--------------------------------
ello World

ltrim和rtrim去除字符的写法：
--表示字符串string1去除前面与string2字符集匹配的，若无匹配则结束返回
SELECT ltrim(string1,string2) FROM dual;
--rtrim与ltrim类似，只是去除的是右边算起匹配的字符

REPLACE(c1,c2,c3)：c1,c2,c3都是字符串，函数用c3代替出现在c1中的c2后返回。

SQL> select replace('Hello Word','ll','**') from dual;
REPLACE('HELLOWORD','LL','**'
--------------------------------------------------------
He**o Word

TRANSLATE(c1,c2,c3)：将c1中与c2相同的字符以c3代替。

数值型truncat

SQL> select trunc(34.36,1) from dual;
TRUNC(34.36,1)
--------------
34.3
SQL> select trunc(34.36,-1) from dual;
TRUNC(34.36,-1)
---------------
30
SQL> select trunc(34.36,-2) from dual;
TRUNC(34.36,-2)
---------------
0

wm_concat将列中的值全部放到一行上，用逗号分隔。

SQL> select wm_concat(name) from t;
WM_CONCAT(NAME)
----------------------------------------
bai,bai,xiao,yu,ao,er,qi

decode函数，oracle中的if else

DECODE函数，它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。

区别于SQL的其它函数，DECODE函数还能识别和操作空值。

语法：DECODE(control_value,value1,result1[,value2,result2…][,default_result]);

SQL> select decode(id,1,'id is 1',2,'id is 2','id is everything') from t
DECODE(ID,1,'IDIS1',2,'IDIS
--------------------------------
id is 1
id is 1
id is 2
id is everything
id is everything
id is everything
id is everything

使用 case when的实现

SQL> select id,
2 case when id=1 then 'id is 1'
3 when id=2 then 'id is 2'
4 else 'id is everything'
5 end as iidd from t;
ID IIDD
---------- ------------------------------
1 id is 1
1 id is 1
2 id is 2
3 id is everything
4 id is everything
0 id is everything
id is everything

已选择7行。

0 0