oracle常用函数

 

1.系统变量函数

（1）SYSDATE

该函数返回当前的日期和时间。返回的是Oracle服务器的当前日期和时间。

select sysdate from dual;

 

insert into purchase values

 

(‘Small Widget’,’SH’,sysdate, 10);

 

insert into purchase values

 

(‘Meduem Wodget’,’SH’,sysdate-15, 15);

查看最近30天的所有销售记录，使用如下命令：

select * from purchase

 

where purchase_date between (sysdate-30) and sysdate;

（2）USER

查看用户名。

select user from dual;

（3）USERENV

查看用户环境的各种资料。

select userenv(‘TERMINAL’) FROM dual;

2.数值函数

（1）ROUND 四舍五入函数

ROUND(数值，保留位数）

select round(3.1415,3) from deul;

 

select product_name,round(product_price,0) price

 

from product;

（2）TRUNC 从数中截去小数部分

TRUNC(数值，截断小数点n位后的数）

select trunc(3.145159,3) from dual;

 

select trunc(123456.45,-1) from dual;

 

select trunc(123456.45) from dual;

 

select product_name,trunc(product_price) price

 

from product;

3.文本函数

（1）UPPER、LOWER和INITCAP

这三个函数更改提供给它们的文体的大小写。

select upper(product_name) from product;

 

select lower(product_name) from product;

 

select initcap(product_name) from product;

函数INITCAP能够整理杂乱的文本，如下：

select initcap(‘this TEXT hAd UNpredictABLE caSE’) from dual;

（2）LENGTH

求数据库列中的数据所占的长度。

select product_name,length(product_name) name_length

 

from product

 

order by product_name;

（3）SUBSTR

取子串，格式为：

SUBSTR（源字符串，起始位置，子串长度）；

create table item_test(item_id char(20),item_desc char(25));

 

insert into item_test values(‘LA-101’,’Can, Small’);

 

insert into item_test values(‘LA-102’,’Bottle, Small’);

 

insert into item_test values(‘LA-103’,’Bottle, Large’);

取编号：

select substr(item_id,4,3) item_num,item_desc

 

from item_test;

（4）INSTR

确定子串在字符串中的位置，格式如下：

INSTR(源字符串，要查找的字符串，查找起始位置）

select instr(‘this is line one’,’line’,1) from dual;

其返回值为子串在源字符串中从起始位置开始第一次出现的位置。上面例子的返回值为9。

select item_desc , instr(item_desc,’,’,1)

 

from item_test;

（5）LTRIM、RTRIM和TRIM

去除字符串左边的空格、去除字符串右边的空格、去除字符串左右两边的空格。

select ltrim(‘ abc def ‘) from dual;

4.日期函数

（1）SYSDATE和TRUNC

这两个函数前面已经出现过，即取Oracle服务器系统的日期和截掉小数部分的功能。观察以下操作：

create table test_date (name varchar2(20), p_date date);

 

insert into test_date values(‘name1’,sysdate);

 

select * from test_date;

 

select * from test_date where p_date=’25-10月-05‘;

 

select * from test_date where trunc(p_date)= ’25-10月-05‘;

Oracle系统中用SYSDATE取得的不仅包含日期而且还包含的有时间信息，时间信息实际上就是表示儒略日数据中的小数部分。

（2）ADD_MONTHS

该函数返回一个具有与所提供日期相差月份的日期，函数中给出了未来或以前的月份数。语法如下：

ADD_MONTHS（起始日期，增减月数）

 

select add_months(’26-10月-05‘,2) from dual;

 

select add_months(’26-10月-05‘,-2) from dual;

（3）LAST_DAY

返回包含给定日期的那个月的最后一天。语法为：

LAST_DAY(日期）

select last_day(’21-2月-80‘) from dual;

（4）MONTHS_BETWEEN

返回两个日期间的月份。语法为：

MONTHS_BETWEEN（较晚日期，较早日期）

select months_between(’12-10月-05‘,’12-9月-03‘) from dual;

 

5.数据转换函数

（1）TO_CHAR

该函数将日期、时间和数值转换为文本。它的主要价值是提供对日期、时间和数的显示控制；该函数的用法非常灵活，使用较复杂，在此我们仅对经常使用的转换举例说明。

1）格式化日期和时间值

TO_CHAR（日期数据，格式编码）

select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’)

 

from dual;

 

select to_char(sysdate,’HH24:MI:SS’)

 

from dual;

 

select to_char(sysdate,‘YYYY’)||‘年’||

 

to_char(sysdate,'MM')||'月'||

 

to_char(sysdate,'DD')||'日' 日期

 

from dual

2）格式化数值

select to_char(5764.12345,'99,999.9999') from dual;

 

select to_char(5764.12345,‘09,999.9999') from dual;

（2）TO_DATE

将文本转换为实际的Oracle日期/时间值。格式：

TO_DATE(文本，日期格式）

select to_date(‘2005-10-10 11’,’YYYY-MM-DD HH24’)

 

from dual;

 

insert into item_test values(

 

‘name-x’,

 

to_date(‘2005-10-25’,’YYYY-MM-DD’) ) ;

 

6.其它函数

NVL函数完成一个简单但有用的功能。任何时候给它一个空值，它都返回一个你所选择的值。格式如下：

NAL(输入值，如果输入值为空要返回的值)

select nvl(null,’new_value’) from dual;

 

select name,nvl(p_date,sysdate)

 

from test_date ;

注意：函数中‘输入值’和‘如果输入值为空要返回的值’的类型必须一致。

 

 

 

获取昨天：

select trunc(SYSDATE-1)  from dual

获取上个月第一天00:00:00：

 select add_months(trunc(sysdate,'MON'),-1) from dual

 select add_months(trunc(sysdate,'MON'),-13) from dual也可以

获取上个月今天00:00:00：

SELECT trunc(add_months(sysdate,-1))FROM dual

获得本季度第一天

SELECT TRUNC(add_months(SYSDATE,0),'Q') FROM dual

获得上季度第一天

SELECT TRUNC(add_months(SYSDATE,-3),'Q') FROM dual

获得去年1月1日

to_char(add_months(trunc(sysdate, 'Year'), -12), 'YYYY-MM-DD')

between and 前面的时间小后面的时间大

to_date('20110105','YYYYMMDD')-7 此处 -7代表天

转换的格式：

表示 year 的：y 表示年的最后一位 、
                     yy 表示年的最后2位 、 
                     yyy 表示年的最后3位 、
                     yyyy 用4位数表示年

表示month的： mm 用2位数字表示月 、
                       mon 用简写形式， 比如11月或者nov 、
                       month 用全称， 比如11月或者november

表示day的：dd  表示当月第几天 、
                  ddd 表示当年第几天 、
                  dy  当周第几天，简写， 比如星期五或者fri 、
                  day 当周第几天，全称， 比如星期五或者friday

表示hour的：hh   2位数表示小时 12进制、 
                   hh24 2位数表示小时 24小时

表示minute的：mi 2位数表示分钟

表示second的：ss 2位数表示秒 60进制

表示季度的：q 一位数 表示季度 （1-4）

另外还有ww 用来表示当年第几周 w用来表示当月第几周。

24小时制下的时间范围：00：00：00-23：59：59
12小时制下的时间范围：1：00：00-12：59：59

数字格式:  9  代表一个数字 
               0  强制显示0 
               $  放置一个$符 
               L  放置一个浮动本地货币符 
               .  显示小数点 
               ,  显示千位指示符

补充：
当前时间减去7分钟的时间 
select sysdate,sysdate - interval '7' MINUTE from dual; 
当前时间减去7小时的时间 
select sysdate - interval '7' hour from dual; 
当前时间减去7天的时间 
select sysdate - interval '7' day from dual; 
当前时间减去7月的时间 
select sysdate,sysdate - interval '7' month from dual; 
当前时间减去7年的时间 
select sysdate,sysdate - interval '7' year from dual; 
时间间隔乘以一个数字 
select sysdate,sysdate - 8*interval '7' hour from dual;

含义解释： 
  Dual伪列
      Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的select语句块中。
      不同系统可能返回日期的格式不一样。
      返回当前连接的用户：select user from dual;

Oracle字符串截取

select regexp_substr('aaa,bb,ccccc,ddd,vvv','[^,]+',1,1) as newcol

from dual这句返回结果是：aaa，使用正则表达式很容易以某个字符（例如“,”）截取字符串。

 

要求：A.数据库表中的一个字符串  可能含有"+" 例：ORC+001

 

　　也可能不含“+”

 

　　B.要求如果该字符串含有“+”，则取“+”之前的字符 例：ORC+001 取ORC

 

　　C.如果该字符串不含"+"，则直接取该字符串。

 

　　解答：利用ORACLE中 INSTR，SUBSTR以及CASE WHEN语法即可实现。

 

　　Oracle中函数

 

　　INSTR：INSTR方法的格式为

 

　　INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)

 

　　默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。

 

　　例：Select INSTR(‘ORC+001‘,‘+‘,1,1) from dual

 

　　返回的是＂４＂　如果该字符串没有匹配字符　返回的是“０”。

 

SUBSTR：取子字符串

 

　　SUBSTR(源字符串,起始位置,要取多少位)

 

　　从“起始位置”开始,取“多少＂个

 

　　例　Select SUBSTR(‘ORC+001‘,1,3) from dual;

 

　　返回的是“ORC”

 

　　全例如下

 

　　表T_RCV_ISSUE

 

　　WH_CD字段

 

　　取 WH_CD 如果该字段包含"+"，取“+”之前的字符

 

　　如果该字段没有"+"，取整个字段

 

　　T_RCV_ISSUE表

 

　　WH_CD 分别为 MR-PS+007，MR-WS，MR-WS+001，MR-PS

 

　　执行

 

　　SELECT CASE

 

　　WHEN INSTR(WH_CD, ‘+‘, 1, 1) > 0

 

　　THEN SUBSTR(WH_CD, 1, INSTR(WH_CD, ‘+‘, 1, 1) - 1)

 

　　ELSE WH_CD

 

　　END AS WH_CD

 

from   T_RCV_ISSUE

 

 

 

 

今天因工作需要，写了一个小函数，按分隔符截取字符串的，分享出来，希望有需要的朋友能用到。

 

功能实例： substrbysep('aaa,bb,ccccc,ddd,vvv',',',3)返回结果 'ccccc'.

 

函数：

 

create or replace function substrbysep(sourceString varchar2,

  destString varchar2,

  appearPosition number)

  return varchar2 is

  substring varchar2(255);

  

  --作者：Alex Liu

  --功能：按分隔符截取字符串

  --参数：1、原始字符串；2、分隔符；3、截取第几段

  

begin

  substring := substr(destString || sourceString || destString,

  instr(destString || sourceString || destString,

  destString,

  1,

  appearPosition) + 1,

  instr(destString || sourceString || destString,

  destString,

  1,

  appearPosition + 1) -

  instr(destString || sourceString || destString,

  destString,

  1,

  appearPosition) - 1);

  return(substring);

end substrbysep;

 

 

 

用正则表达式，一步就出来了

select regexp_substr('aaa,bb,ccccc,ddd,vvv','[^,]+',1,3)

from dual

;

REGEX

-----

Ccccc