Oracle - 常用函数

字符串操作：

concat

instr

substr

replace

initcap

length

lower/upper

lpad/rpad

ltrim/rtrim

trim

 

日期：

add_months

last_day

next_day

sysdate

months_between

 

to_char

to_date

to_number

 

avg/floor/ceil/round/max/min

 

nvl

DECODE

 

 

CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23'  高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转23

INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith

INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1    被搜索的字符串
C2    希望搜索的字符串
I     搜索的开始位置,默认为1
J     出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;

 INSTRING
---------
        9

SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;

SUBSTR('
--------
08888888

REPLACE('string','s1','s2')
string   希望被替换的字符或变量
s1       被替换的字符串
s2       要替换的字符串
SQL> select replace('he love you','he','i') from dual;

REPLACE('H
----------
i love you

LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;

NAME   LENGTH(NAME) ADDR             LENGTH(ADDR)       SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞            3 北京市海锭区                6   9999.99                    7

LOWER 、UPPER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD
--------
aabbccdd

 

RPAD、LPAD
RPAD  在列的右边粘贴字符
LPAD  在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;

LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满

LTRIM、RTRIM
LTRIM  删除左边出现的字符串
RTRIM  删除右边出现的字符串
SQL> select ltrim(rtrim('   gao qian jing   ',' '),' ') from dual;

LTRIM(RTRIM('
-------------
gao qian jing


TRIM('s' from 'string')
LEADING   剪掉前面的字符
TRAILING  剪掉后面的字符
如果不指定,默认为空格符

ABS、ceil、round、
ABS：返回指定值的绝对值

CEIL：返回大于或等于给出数字的最小整数

FLOOR：对给定的数字取整数

MOD(n1,n2)：返回一个n1除以n2的余数

ROUND和TRUNC：按照指定的精度进行舍入

SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
         56          -55          55          -55

SQL> select abs(100),abs(-100) from dual;

 ABS(100) ABS(-100)
--------- ---------
      100       100

 

SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

   TRUNC1 TRUNC(124.16666,2)
--------- ------------------
      100             124.16


ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

TO_CHA
------
199910

LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10

SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月 -04

MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

MON_BETWEEN
-----------
          9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;

 MON_BETW
---------
      -60

NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;

NEXT_DAY
----------
25-5月 -01

SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;

TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日

trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
  2  to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

HH                  HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

ROWID              ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;

conver
------
strutz

TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41

TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期

TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;

     YEAR
---------
     1999

CONVERT('x','desc','source')
将x字段或变量的源source转换为desc

LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;

LE
--
啊

UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;

USERNAME                         USER_ID
------------------------------ ---------
GAO                                   25

USER
返回当前用户的名字
SQL> select user from  dual;

USER
------------------------------
GAO


AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS>  insert into table3 values('gao',1111.11);
SQLWKS>  insert into table3 values('gao',1111.11);
SQLWKS>  insert into table3 values('zhu',5555.55);
SQLWKS> commit;

SQL> select avg(distinct sal) from gao.table3;

AVG(DISTINCTSAL)
----------------
         3333.33

SQL> select avg(all sal) from gao.table3;

AVG(ALLSAL)
-----------
    2592.59

MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;

MAX(DISTINCTSAL)
----------------
            5000

MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;

MIN(ALLSAL)
-----------
    1111.11

GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;

   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       10         3      8750
       20         5     10875
       30         6      9400

HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;

   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       20         5     10875
       30         6      9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;

   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       20         5     10875
       30         6      9400

ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;

   DEPTNO ENAME            SAL
--------- ---------- ---------
       10 KING            5000
       10 CLARK           2450
       10 MILLER          1300
       20 SCOTT           3000
       20 FORD            3000     

NVL

nvl（arg,value）代表如果前面的arg的值为null那么返回的值为后面的value

 

另一个有关的有用方法

declare

i integer

select nvl(sum(t.dwxhl),1) into i from tb_jhde t where zydm=-1这样就可以把获得的合计值存储到变量i中，如果查询的值为null就把它的值设置为默认的1

 

IF NVL(LENGTH(XML_ERROR), 0) > 0 THEN

    ..

  END IF;

 

SELECT LENGTH(NULL) FROM DUAL  

// 结果为空

 

Case..when

Sql代码  

1. SELECT col1, col2,  
2.        CASE  
3.           WHEN col3 > 1 AND col3 <2  
4.              THEN '1'  
5.           WHEN col3 > 2 AND col3 <3  
6.              THEN '2'  
7.           WHEN col3 > 3 AND col3 <4  
8.              THEN '3'  
9.           ELSE '4'  
10.        END AS mylevel  
11. FROM table1  

 

注意点：

1、以CASE开头，以END结尾

2、分支中WHEN 后跟条件，THEN为显示结果

3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加

4、END 后跟别名 

Sql代码  

1. SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1      /*sex 1为男生，2位女生*/  
2.                 ELSE NULL  
3.                 END) 男生数,  
4.         COUNT (CASE WHEN sex = 2 THEN 1  
5.                 ELSE NULL  
6.                 END) 女生数  
7.     FROM students GROUP BY grade;  
8.   
9.     SELECT COUNT(NULL) FROM EIP_PERFORMANCE_CPU  
10.         //0  
11.           
12.     SELECT interface_type,COUNT(1) FROM eip_service group by interface_type  
13.            -- READ  489  
14.            -- WRITE 155  
15.              
16.     SELECT interface_type,COUNT(NULL) FROM eip_service group by interface_type         
17.            -- READ  0  
18.            -- WRITE 0     
19.   
20.     SELECT province_code,   
21.        COUNT(case when interface_type = 'READ' then 1 else null end) READ数,  
22.        COUNT(case when interface_type = 'WRITE' then 1 else null end) WRITE数  
23.     FROM eip_service  
24.     group by province_code  

 

 

translate

 

今天看以前写的sql，突然想到这个问题：怎么统计一个字符串中某个字符出现的次数

    想到了用把要被统计的字符用''（空格）替换掉，然后拿原来字符串长度减去现在的长度就得出了，例：

SELECT LENGTHB('ABCDEFGEFGDBE')-LENGTHB(REPLACE('ABCDEFGEFGDBE','E','')) FROM DUAL

 

后来在网上看到了translate函数，用了一下，感觉真不错，

SELECT LENGTHB(TRANSLATE('ABCDEFGEFGDBE','EABCDEFGEFGDBE','E')) as sl FROM DUAL;

EEE

 

 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。TRANSLATE   是   REPLACE   所提供的功能的一个超集。如果   from_str   比   to_str   长，那么在   from_str   中而不在   to_str   中的额外字符将从   string   中被删除，因为它们没有相应的替换字符。to_str   不能为空。Oracle   将空字符串解释为   NULL，并且如果TRANSLATE   中的任何参数为NULL，那么结果也是   NULL。  

 

TRANSLATE(string,from_str,to_str) 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。

知道了TRANSLATE函数的用法，上一句sql就可以明白是什么意思了：用E替换E，其它的不管（即其它对应的每个字符都被替换为‘’了，即E被替换成E,A被替换成空，B被替换成空……），所以'ABCDEFGEFGDBE'对应字符替换后就只剩下E了。

select translate('222tech', '2ec', '3it') from dual; 

结果：333tith

对于汉字就要用函数LENGTH了

 SELECT LENGTH(TRANSLATE('你好你不好好不好','好'||'你好你不好好不好','好')) FROM DUAL;

 

 

DECODE

 

DECODE函数,是ORACLE公司的SQL软件ORACLE PL/SQL所提供的特有函数计算方式，以其简洁的运算方式，可控的数据模型和灵活的格式转换而闻名。

　在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：

　　DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

　　Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。

　　需要注意的是，这里的if、then及else 都可以是函数或计算表达式。

如：

DECODE(S.INTERFACE_TYPE, 'READ', '>', 'WRITE', '<', '') AS FLOW

 

=================================================================

[,str] :代表可选。

1：decode

用法：  decode(value,search_value,result,defaultValue)

类似Java中的三目表达式 value==search_value?result:defaultValue ;

 

2 ： translate

用法 ： translate(x,exist_string,to_string)

例子

select translate('very good','abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ') from dual;

 

输出的结果 ： VERY GOOD；

 

就是把x 里面 有exist_string存在的字符转成to_string对应的字符，比方说存在e，e在exist_string第五位，那么取to_string的第五位字符。

 

3 : union all ： 返回查询所检索出的所有行，包括重复的行。

select id,name from student union all select id,name from more_student;

 

4: union ： 返回查询所检索出的所有非重复行。

select id,name from student union  select id,name from more_student;

 

5:intersect : 返回两个查询所检索出的共有行 .

select id,name from student intersect select id ,name from more_student;

 

6: minus  : 返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行。

select id,name from student minusselect id,name from more_student;

 

7: case :

case

    when condition then result

    else default_resut

8:树查询：

 select level,employee_id,manager_id from employees start with employee_id=0 connect by prior employee_id =manager_id order by level ;

 a : start with  ...connect by prior ... 通过改查询获取一颗树的列表数据。

 b ： level ：伪列 ，代表节点在树中的层次。

 

9:分组(group by)：

grouy by的一些注意事项：如果查询中包含一个聚合函数，而所选择的列并不在聚合函数中，那么这些列就必须在group by 字句中。

rollup :

例子：select sex,count(studentid) from Student group by rollup(sex) ;

按照sex返回学生数，并在末尾返回总的学生数。

cube :和rollup类似，但是结果放在非空的sex之前，空的sex之后。

 

10：ltrim(),rtrim()和trim() :

    ltrim(x,[,trim_string]) : 从x的左边截去一些字符，可以用trim_string来指定要截去的字符，如果没有，默认截去左边的空格

   rtrim(x,[,trim_string]) : 和ltrim()类似，默认截去右边的空格。

   trim(x,[,trim_string])  ： 默认截取两边的空格。

   是java中string.trim ()的加强版

 

11：instr():

   instr(x,find_string[,strart][,occurrence])函数用于在x中查找find_string 。instr()函数返回find_string 所在得位置，改函数可以用可选参数start来说明从 x中得哪个位置开始查找，还可以使用可选参数occurrence说明应该返回find_string第几次出现得位置。

 

 12:  Oracke 10 新增的MODEL字句用来进行行间运算。MODEL字句允许像访问数组中的元素那样访问记录中的某个列。这就提供了诸如电子表格计算之类的计算能力。

 

  例子 ： 下面这个查询获取2003年内有员工#21 完成的产品类型为#1和#2的销量，并根据2003年的销售数计算出2004年一月，二月，三月的销量预测值：

 

Sql代码 

 

Sql代码  

1. select prd_type_id, year, month, sales_amount  
2.   from all_sales  
3.  where prd_type_id between 1 and 2  
4.    and emp_id = 21 model partition by(prd_type_id) dimension  
5.  by(month, year) measures(sales_amount)  
6.  (sales_amount[1, 2004] = sales_amount[1, 2003],   
7.   sales_amount[2,2004] = sales_amount[2, 2003] + sales_amount[3, 2003],   
8.   sales_amount[3,2004] = round(sales_amount[3, 2003] * 1.25, 2)  
9.  )  
10.  order by prd_type_id, year, month;  

 

partition by(prd_type_id)指定结果是根据prd_type_id分区的。

 

dimension by(month,year)定义数组的维数是month和year。这就意味着必须提供月份和年份才能访问某个列。

 

measures(sales_amount):指定做为数组的列。

 

sales_amount[月，年]，按照dimension by(month,year)的要求。

  

13：initcap(x)  : 用于将 x中的每个单词的首字母转换为大写。

 

14 :length(x) ;用于获得x中字符的个数。

 

15 ： concat(x,y) ：用于将y附加在x之后，该函数会返回得到的字符串。

 

16： lower(x) :将x中的每个字符转换为小写。

 

17： upper(x):将x中的每个字符转换为大写。

 

18：lpad(x,width [,pad_string])用于在x的左边补齐空格，使x的总长度达到width个字符，如果在pad_string参数中指定了一个字符串，那么就用这个字符串反复填充x左边的空位

 

19：rpad(x,width [,pad_string]) 和lpad(x,width [,pad_string])类似，当时在x右边填充，right。

 

20 ：nul(x,value) 如果x为空，则返回value，否则返回null.

  if(tmp==null)

{

   return value;

}

else

{

   return x;

}

21: nul2(x,value1,value2):非空判断的增强版，如果x为空，返回value2，否则返回value1；

 

22 ： substr(x,start[,length]) 和java 的substring()方法一样;

 

23 : nanvl(x,value) : oracle10g新增的一个函数。如果x匹配NaN这个特殊值(非数字)，返回value，否则返回x

 

 

24 ：replace(x,search_string,replace_string) :在x中查找search_string,并将其替换为replace_string