ORACLE函数大全

             ORACLE函数大全
SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

        A         A      ZERO     SPACE
--------- --------- --------- ---------
       65        97        48        32

2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C
-- -
赵 A

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

高乾竞电话
----------------
010-88888888转23

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

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

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

AABBCCDD
--------
aabbccdd

8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;

UPPER
--------
AABBCCDD

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

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

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

LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;

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

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

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

13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');

SQL> select xm from table1 where soundex(xm)=soundex('weather');

XM
--------
weather
wether

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

15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;

 ABS(100) ABS(-100)
--------- ---------
      100       100
16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;

 ACOS(-1)
---------
3.1415927

17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;

ASIN(0.5)
---------
.52359878

18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;

  ATAN(1)
---------
.78539816

19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;

CEIL(3.1415927)
---------------
              4

20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;

COS(-3.1415927)
---------------
             -1

21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;

 COSH(20)
---------
242582598

22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;

   EXP(2)    EXP(1)
--------- ---------
7.3890561 2.7182818

23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;

FLOOR(2345.67)
--------------
          2345

24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;

    LN(1)     LN(2) LN(2.7182818)
--------- --------- -------------
        0 .69314718     .99999999

25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;

 LOG(2,1)  LOG(2,4)
--------- ---------
        0         2

26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

MOD(10,3)  MOD(3,3)  MOD(2,3)
--------- --------- ---------
        1         0         2

27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;

POWER(2,10) POWER(3,3)
----------- ----------
       1024         27

28.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

29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;

SIGN(123) SIGN(-100)   SIGN(0)
--------- ---------- ---------
        1         -1         0

30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;

SIN(1.57079)
------------
           1

31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;

  SIN(20)  SINH(20)
--------- ---------
.91294525 242582598

32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;

 SQRT(64)  SQRT(10)
--------- ---------
        8 3.1622777

33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;

  TAN(20)   TAN(10)
--------- ---------
2.2371609 .64836083

34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;

 TANH(20)   TAN(20)
--------- ---------
        1 2.2371609

35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

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

36.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

37.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

38.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

39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
  2  (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

BJ_TIME             LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32

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

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

41.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

42.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

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

44.HEXTORAW
将一个十六进制构成的字符串转换为二进制

45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制

46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型

47.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
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期

49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL>  select to_multi_byte('高') from dual;
TO
--
高

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

51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));

52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
  2  0,'none',
  3  2,'insert',
  4  3,
  5  'select',
  6  6,'update',
  7  7,'delete',
  8  8,'drop',
  9  'other') cmd  from v$session where type!='background';
      SID   SERIAL# USERNAME                       CMD
--------- --------- ------------------------------ ------
        1         1                                none
        2         1                                none
        3         1                                none
        4         1                                none
        5         1                                none
        6         1                                none
        7      1275                                none
        8      1275                                none
        9        20 GAO                            select
       10        40 GAO                            none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;

GLOBAL_NAME                    DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD                   Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D

54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数

55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天

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

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

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

59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA  查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
                 152
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
                 0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
                  1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
          6 SYSTEM

60.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

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

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

63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
  1182.5032

SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
           1229.951

64.VARIANCE(DISTINCT|ALL)
求协方差

SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
    1398313.9

65.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

66.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

67.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
       20 JONES           2975
       20 ADAMS           1100
       20 SMITH            800
       30 BLAKE           2850
       30 ALLEN           1600
       30 TURNER          1500
       30 WARD            1250
       30 MARTIN          1250
30 JAMES            950

 

 

 

oracle的字符函数
--------------------------------------------------------------------
字符函数主要用于修改字符列。这些函数接受字符输入，返回字符或数字值。
Oracle 提供的一些字符函数如下。 
1. CONCAT (char1, char2)  

返回连接“char2”的“char1”。 
示例
2. INITCAP(string) 
将“string”的字符转成大写。 
示例
3. LOWER (string) 
将“string”转成小写。 
示例
4. LPAD(char1,n [,char2])  
返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果
“char1”比“n”长，则函数返回“char1”的前“n”个字符。 
示例
5. LTRIM(string,trim_set)  
从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而
“trim_set” 是我们要去掉的字符的集合。 
示例
6. REPLACE(string, if, then) 
用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出
现在“string”中的“if”，都用“then”的内容代替。 
示例
7. RPAD(char1, n [,char2])  
返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果
“char1”比“n” 长，则函数返回“char1”的前“n”个字符。 
示例
8. RTRIM(string,trim_set) 
从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而
“trim_set” 是我们要去掉的字符的集合。 
示例
9. SOUNDEX(char) 
返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类
似的字。 
示例
10. SUBSTR(string, start [,count]) 
返回“string”中截取的一部分。该命令截取“string”的一个子集，从
“start”位置开始，持续“count”个字符。如果我们不指定“count”，则
从“start”开始截取到“string”的尾部。
示例
11. TRANSLATE(string, if, then)  
“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替
换 “string”中的字符。
示例
12. UPPER(string)  
返回大写的“string”。 
示例
13. ASCII(string) 
该命令是“American Standard Code for Information Interchange”的缩
写。它是使用数字表示可打印字符的基本规则。该函数返回 “string”中第
一个（最左边）字符的 ASCII 值。 
示例
14. INSTR (string, set[, start[, occurrence] ] ) 
该命令“string”中从“start”位置开始查找字符集合的位置，再查找
“set”出现的第一次、第二次等等的“occurrence”（次数）。“start”的
值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和
日期数据类型。 
示例
15. LENGTH(string)  
返回“string”的长度值。

       Oracle一些日期计算的函数
在oracle中有很多关于日期的函数，如：
1、add_months()用于从一个日期值增加或减少一些月份
date_value:=add_months(date_value,number_of_months)
例：
SQL> select add_months(sysdate,12) "Next Year" from dual;
 
Next Year
----------
13-11月-04
 
SQL> select add_months(sysdate,112) "Last Year" from dual;
 
Last Year
----------
13-3月 -13
 
SQL> 
 
2、current_date()返回当前会放时区中的当前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;
 
SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00          13-11月-03
 
SQL> alter session set time_zone='-11:00'
   2  /
 
会话已更改。
 
SQL> select sessiontimezone,current_timestamp from dual;
 
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00          12-11月-03 04.59.13.668000 下午 -11:
                 00
 
SQL> 
 
3、current_timestamp()以timestamp with time zone数据类型返回当前会放时区中的当前日期
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone,current_timestamp from dual;
 
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
+08:00          13-11月-03 11.56.28.160000 上午 +08:
                 00
 
SQL> alter session set time_zone='-11:00'
   2  /
 
会话已更改。
 
SQL> select sessiontimezone,current_timestamp from dual;
 
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00          12-11月-03 04.58.00.243000 下午 -11:
                 00
 
SQL> 
 
4、dbtimezone()返回时区
varchar_value:=dbtimezone
SQL> select dbtimezone from dual;
 
DBTIME
------
-07:00
 
SQL> 
 
5、extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;
 
This Month
----------
         11
 
SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;
 
3 Years Out
-----------
        2006
 
SQL> 
 
6、last_day()返回包含了日期参数的月份的最后一天的日期
date_value:=last_day(date_value)
SQL> select last_day(date'2000-02-01') "Leap Yr?" from dual;
 
Leap Yr?
----------
29-2月 -00
 
SQL> select last_day(sysdate) "Last day of this month" from dual;
 
Last day o
----------
30-11月-03
 
SQL> 
7、localtimestamp()返回会话中的日期和时间
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
----------------------------
13-11月-03 12.09.15.433000
下午
 
SQL> select localtimestamp,current_timestamp from dual;
LOCALTIMESTAMP               CURRENT_TIMESTAMP
---------------------------- ------------------------------------
13-11月-03 12.09.31.006000   13-11月-03 12.09.31.006000 下午 +08:
下午                         00
 
SQL> alter session set time_zone='-11:00';
会话已更改。

SQL> select localtimestamp,to_char(sysdate,'DD-MM-YYYY HH:MI:SS AM') "SYSDATE" from dual;
 
LOCALTIMESTAMP               SYSDATE
---------------------------- ------------------------
12-11月-03 05.11.31.259000   13-11-2003 12:11:31 下午
下午
 
SQL> 
8、months_between()判断两个日期之间的月份数量
number_value:=months_between(date_value,date_value)
SQL> select months_between(sysdate,date'1971-05-18') from dual;
 
MONTHS_BETWEEN(SYSDATE,DATE'1971-05-18')
----------------------------------------
                               389.855143
 
SQL> select months_between(sysdate,date'2001-01-01') from dual;
 
MONTHS_BETWEEN(SYSDATE,DATE'2001-01-01')
----------------------------------------
                               34.4035409
SQL> 
 
9、next_day()给定一个日期值，返回由第二个参数指出的日子第一次出现在的日期值（应返回相应日子的名称字符串）
oracle的nvl函数的用法
通过查询获得某个字段的合计值，如果这个值位null将给出一个预设的默认值
select nvl(sum(t.dwxhl),1) from tb_jhde t where zydm=-1这里关心的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

Oracle常用函数
数值函数：
abs(m)  m的绝对值
mod(m,n) m被n除后的余数
power(m,n) m的n次方
round(m[,n]) m四舍五入至小数点后n位的值（n缺省为0）
trunc(m[,n]) m截断n位小数位的值（n缺省为0）
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
字符函数：
initcap(st) 返回st将每个单词的首字母大写，所有其他字母小写
lower(st) 返回st将每个单词的字母全部小写
upper(st) 返回st将每个单词的字母全部大写
concat(st1,st2)  返回st为st2接st1的末尾（可用操作符"||"）
lpad(st1,n[,st2]) 返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格
rpad(st1,n[,st2]) 返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的缺省为空格
ltrim(st[,set])  返回st,st为从左边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格
rtrim(st[,set])  返回st,st为从右边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格
replace(st,search_st[,replace_st]) 将每次在st中出现的search_st用replace_st替换，返回一个st。缺省时，删除search_st
substr(st,m[,n]) n=返回st串的子串，从m位置开始，取n个字符长。缺省时，一直返回到st末端
length(st) 数值，返回st中的字符数
instr(st1,st2[,m[,n]])  数值，返回st1从第m字符开始，st2第n次出现的位置，m及n的缺省值为1
例：
1.
select initcap('THOMAS'),initcap('thomas') from test;
initca initca
------ ------
Thomas Thomas
2.
select concat('abc','def') "first" from test;
first
-----
abcdef
3.
select 'abc'||' '||'def' "first" from test;
first
-----
abc def
4.
select lpad(name,10),rpad(name,5,'*') from test;
lpad(name,10) rpad(name,5,'*')
------------ ----------------
         mmx mmx**
      abcdef abcde
5.
去掉地址字段末端的点及单词st和rd
select rtrim(address,'. st rd') from test
6.
select name,replace(name,'a','*') from test;
name  replace(name,'a','*')
----  ---------------------
great gre*t
7.
select substr('archibald bearisol',6,9) a,substr('archibald bearisol',11) b from test;
a         b
-------   -------
bald bear bearisol
8.
select name,instr(name,' ') a,instr(name,' ',1,2) b from test;
name    a        b
------- -------- ---------
li lei  3        0
l i l   2        4
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
转换函数：
nvl(m,n) 如果m值为null,返回n,否则返回m
to_char(m[,fmt]) m从一个数值转换为指定格式的字符串fmt缺省时，fmt值的宽度正好能容纳所有的有效数字
to_number(st[,fmt]) st从字符型数据转换成按指定格式的数值，缺省时数值格式串的大小正好为整个数
附：
to_char()函数的格式：
---------------------------------
符号    说明
---------------------------------
9       每个9代表结果中的一位数字
0       代表要显示的先导0
$       美元符号打印在数的左边
L       任意的当地货币符号
.       打印十进制的小数点
,       打印代表千分位的逗号
---------------------------------
例：
1.
select to_number('123.45')+to_number('234.56') form test;
to_number('123.45')+to_number('234.56')
----------------------------------------
                               358.01
2.
select to_char(987654321) from test;
to_char(987654321)
------------------
987654321
3.
select to_char(123,'$9,999,999') a,to_char(54321,'$9,999,999') b,to_char(9874321,'$9,999,999') c from test;
a       b          c
------- ---------- -----------
$123    $54,321    $9,874,321
4.
select to_char(1234.1234,'999,999.999') a,to_char(0.4567,'999,999.999') b,to_char(1.1,'999,999.999') from test;
a         b          c
--------- ---------- ------------
1,234.123 .457       1.100
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
分组函数：
avg([distinct/all] n) 列n的平均值
count([all] *)  返回查询范围内的行数包括重复值和空值
count([distinct/all] n) 非空值的行数
max([distinct/all] n) 该列或表达式的最大值
min([distinct/all] n) 该列或表达式的最小值
stdev([distinct/all] n) 该列或表达式的标准偏差，忽略空值
sum([distinct/all] n) 该列或表达式的总和
variance([distinct/all] n) 该列或表达式的方差，忽略空值
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
日期函数：
add_months(d,n)  日期d加n个月
last_day(d)  包含d的月份的最后一天的日期
month_between(d,e) 日期d与e之间的月份数，e先于d
new_time(d,a,b)  a时区的日期和时间d在b时区的日期和时间
next_day(d,day)  比日期d晚，由day指定的周几的日期
sysdate  当前的系统日期和时间
greatest(d1,d2,...dn) 给出的日期列表中最后的日期
least(d1,k2,...dn) 给出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式转变成字符串
to_date(st [,fmt]) 字符串st按fmt指定的格式转成日期值，若fmt忽略，st要用缺省格式
round(d [,fmt])  日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt])  日期d按fmt指定格式截断到最近的日期
附：
日期格式：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
格式代码　    说明   举例或可取值的范围
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD            该月某一天  1－3
DY 　　　三个大写字母表示的周几 SUN，...SAT
DAY 　　　完整的周几，大写英文 SUNDAY，...SATURDAY
MM       月份   1－12
MON　　　　　 三个大写字母表示的月份 JAN，...DEC
MONTH         完整   JANUARY,...DECEMBER
RM       月份的罗马数字  I,...XII
YY或YYYY      两位，四位数字年
HH:MI:SS　　　时：分：秒
HH12或HH24　　以12小时或24小时显示
MI　　　　　　分
SS　　　　　　秒
AM或PM　　　　上下午指示符
SP　　　　　　后缀SP要求拼写出任何数值字段
TH　　　　　　后缀TH表示添加的数字是序数　4th,1st
FM            前缀对月或日或年值，禁止填充
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
例：
1.
下一个周五的日期
select next_day(sysdate,6) from test;
2.
两个月前的今天的日期
select add_months(sysdate,-2) from test;
      
  oracle的最基础的SQL-函数
1、字符函数

lower:把字符转成小写

upper:把字符转成大写

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

concat:连接字符  concat(‘good’,’morning’)=goodmoring

SUBSTR (column\expression, m[,n]) 用于对字符串进行截取操作，从第m个位置开始，把其后的连续n个字符的部分截取下来，如果m位负值，则从末尾开始计算。
eg:substr(‘string’,1,3) =str
 substr(‘string’-3,3)=ing

INSTR('String', 'r')=3

LPAD(sal,10,'*') =*******sal

length: 用于返回表达式中的字符数，注意返回的是NUMBER。
NVL 函数用以把一个空值转换为一个实值,NVL(expression1, expression2)如：
NVL(100/quantity, 0) ,要是quantity为空值，该函数返回一个0。
如果两个字段类型不同必须进行转换。
MIN() : 返回最小值。。如果是字符。。A<a
2、数学函数
round:四舍五入

   round(2.566,2)=2.27
   round(45,-1)=50

trunc: 截断

  trunc(2.566,2)=2.56
  trunc(45,-1)=40

mod:

  mod(m,n):m-n*flood(m/n)     //flood是取整数

3、日期函数

a)       months_between(date1,date2)：算date1和date2之间的月的数量,可以是小数可以是负数

months_between(’01-sep-95’,’11-jan-94’)=1.9774194

b)       add_months(date,n)：为date加上N个月，N只可以是整数

c)       next_date(date,’char’)：查找date的下一个星期N

next_date(’01-sep-95’,’FRIDAY’)=08-SEP-95

d)       last_day(date):查找date月的最后一天。

e)       rount(date)：把日期四舍五入

    　　 rount(25-MAY-95’,’MONTH’)=01-JUN-95

    　　 rount(25-MAY-95’,’YEAR’)=01-JAN-95

f)       trunc(date)：把日期截断

    　　 trunc (25-MAY-95’,’MONTH’)=01-MAY-95

      　 trunc (25-MAY-95’,’YEAR’)=01-JAN-95

g)       日期中RR与YY的区别，RR格式对日期作类似于舍入的操作，YY格式对日期作类似于截取的操作
                RR   YY
1995 27-oct-95 1995 1995
1995 27-oct-17 2017 1917
2001 27-oct-17 2017 2017
2001 27-oct-95 1995 2095

用法：select to_char(sysdate, 'YY') from dual;

select to_char(to_date('95-11-27', 'RR-MM-DD'), 'YYYY-MM-DD') from dual;

select to_char(to_date('95-11-27', 'YY-MM-DD'), 'YYYY-MM-DD') from dual;

4、转换函数
TO_CHAR：
  TO_CHAR(date,’fmt’)：fm前缀用来去除首尾的空字符或0
  TO_CHAR(total,’fm$999999’)
      如果想转成$0.25,那就要写成fm$9999990.99
 可以把日期转换成字符
     TO_CHAR(log_time,’MM/YY’)
     TO_CHAR(lot_time,’fmdd’’of;’’mm yyyy’)
     具体格式如下

   HH24:MI:SS AM-----------15:24:32 pm
   DD’’of’’MONTH-----------12 of MAY
   Ddspth------------------------fourteenth
   Ddsp--------------------------fourteen
   ddth---------------------------4th
   YYYY-----------------------1978
   MM-----------------------------12
   MONTH-------------------------MAY

5、group 函数
avg,count,max,min,stddev,sum,variance

 

 

 

数据类型格式化函数
作者: 由 Karel Zak (<[e-mail]>) 写于 2000-01-24
PostgreSQL 格式化函数提供一套有效的工具用于把各种数据类型 （日期/时间，integer，floating point，numeric） 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。 这些函数都遵循一个公共的调用习惯： 第一个参数是待格式化的值，而第二个是一个定义输出格式的模板．
Table 4-11. 格式化函数
函数 返回 描述 例子
to_char(timestamp, text) text 把 timestamp 转换成 string to_char(timestamp 'now','HH12:MI:SS')
to_char(int, text) text 把 int4/int8 转换成 string to_char(125, '999')
to_char(double precision, text) text 把 real/double precision 转换成 string to_char(125.8, '999D9')
to_char(numeric, text) text 把 numeric 转换成 string to_char(numeric '-125.8', '999D99S')
to_date(text, text) date 把 string 转换成 date to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text) date 把 string 转换成 timestamp to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text) numeric 把 string 转换成 numeric to_number('12,454.8-', '99G999D9S')
在输出模板字串里，该函数族可以识别一些特定的模式，并且 把待格式化的数值正确地格式化成相应的数据． 任何不属于模板模式的文本都简单地照字拷贝．同样，在一个输入 模板字串里，模板模式标识要查看的输入数据字串，并且将在该位置 寻找数值．
Table 4-12. 用于 date/time 转换的模板
模板 描述
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；SUN=1）
W 一个月里的周数（1-5），这里第一周从该月第一天开始
WW 一年里的周数（1-53），这里的第一周从该年的第一天开始
IW ISO 一年里的周数（第一个星期四在第一周里）
CC 世纪（2 位）
J Julian 日期（自公元前4712年1月1日来的日期）
Q 季度
RM 罗马数字的月份（I-XII；I=JAN）－大写
rm 罗马数字的月份（I-XII；I=JAN）－小写
TZ 时区字串 - 大写
tz 时区字串 - 小写
有一些修饰词可以应用于模板来修改它们的行为．比如， "FMMonth" 就是 带着 "FM" 前缀的 "Month" 模式．
Table 4-13. 日期/时间转换的模板模式修饰词
修饰词 描述 例子
FM 前缀 填充模式(抑制填充空白和零) FMMonth
TH 后缀 大写顺序数后缀 DDTH
th 后缀 增加小写顺序数后缀 DDth
FX 前缀 固定格式全局选项（见下面） FX Month DD Day
SP suffix 拼写模式（还未实现） DDSP
用法须知：
• FM 抑制前导的零或尾随的空白， 如果没有使用它的话，会在输出中增加这些填充最终把 输出编程固定宽度的模式．
• 如果没有使用 FX 选项， to_timestamp 和 to_date 在转换字串的时候忽略多个空白． FX 必须做为模板里的第一个项声明。 比如 to_timestamp('2000 JUN'，'YYYY MON') 是正确的， to_timestamp('2000 JUN'，'FXYYYY MON') 会返回一个错误，因为 to_timestamp() 只预料会有一个空白。
• 如果在字串常量里需要反斜杠（"\"）， 那么你必须输入双反斜杠 （"\\"） 比如 '\\HH\\MI\\SS'。对于 PostgreSQL 里的任何字串常量都是这样的．
• 在 to_char 模板 里可以有普通文本，但是任何在双引号之间的字串 都不会被解释为模板关键字并且它们处理得也更快些． （例子: '"Hello Year: "YYYY'）。
在 to_char 模板 里可以有普通文本，并且它们会被照字输出． 你可以把一个字串放到双引号里强迫它解释成一个文本， 即使它里面包含模式关键字也如此．比如，在 '"Hello Year: "YYYY'， YYYY 将被年份数据代替，但是单独 的 Y 不会．
• 如果你想在输出里有双引号，那么你必须在它们 前面放双反斜扛，比如 '\\"YYYY Month\\"'.
• 如果你使用的年份长于 4 位字符，那么用 YYYY 从字串向timestamp或者date 做转换时要受到限制． 你必须在 YYYY 后面使用一些非数字字符或者模板， 否则年份总是解释为 4 位数字．比如（对于 20000 年）： to_date('200001131', 'YYYYMMDD') 将会被解释成一个 4 位数字的年份， 最好在年后面使用一个非数字的分隔符，象 to_date('20000-1131', 'YYYY-MMDD') 或 to_date('20000Nov31', 'YYYYMonDD')．
• 将字串转化为时间戳时， 毫秒 MS 和微秒 US 都是用字串 的小数点后面的部分转换的．比如 to_timestamp('12:3', 'SS:MS') 不是 3 毫秒, 而是 300，因为转换把它看做 12 + 0.3． 这意味着对于格式 'SS:MS'而言，输入值为 12:3 或 12:30或12:300 声明了相同数目的 毫秒．对于三毫秒，你必须使用 12:003，那么转换会把它看做 12 + 0.003 = 12.003 秒．
下面是一个更复杂的 例子∶ to_timestamp('15:12:02.020.001230','HH:MI:SS.MS.US') 是 15 小时，12 分钟，和 2 秒 + 20 毫秒 + 1230微秒 = 2.021230 秒．
Table 4-14. 用于向 numeric 转换的模板模式
模板 描述
9 带有指定数值位数的值
0 带前导零的值
. (句点) 小数点
, (逗号) 分组（千）分隔符
PR 尖括号内负值
S 带负号的负值（使用区域设置）
L 货币符号（使用区域设置）
D 小数点（使用区域设置）
G 分组分隔符（使用区域设置）
MI 在指明的位置的负号（如果数字 < 0）
PL 在指明的位置的正号（如果数字 > 0）
SG 在指明的位置的正/负号
RN 罗马数字（输入在 1 和 3999 之间）
TH 或 th 转换成序数
V 移动 n 位（小数）（参阅注解）
EEEE 科学记数。（现在还未实现）
用法须知：
• 使用SG，PL 或 MI 生成的符号并不挂在数字上面； 比如， to_char(-12, 'S9999')生成 ' -12', 而to_char(-12， 'MI9999')生成 '- 12'。 Oracle 里的实现不允许在 9 前面使用 MI，而是要求9 在 MI 前面。
• 9 表示和 9 的个数 相同的数字位数的数值．如果某个数值位没有数字，则用空白代替．
• TH 不会转换小于零的数值，也不会转换小数．
• PL，SG 和 TH 是 PostgreSQL 扩展。
• V 方便地把输入值乘以 10^n，这里 n 是跟在 V后面的数字。 to_char 不支持把 V 与一个小数点组合在一起使用 （也就是说，99.9V99 是不允许的）。
Table 4-15. to_char 例子
输入 输出
to_char(now(),'Day, DD HH12:MI:SS') 'Tuesday , 06 05:39:18'
to_char(now(),'FMDay, FMDD HH12:MI:SS') 'Tuesday, 6 05:39:18'
to_char(-0.1,'99.99') ' -.10'
to_char(-0.1,'FM9.99') '-.1'
to_char(0.1,'0.9') ' 0.1'
to_char(12,'9990999.9') ' 0012.0'
to_char(12,'FM9990999.9') '0012'
to_char(485,'999') ' 485'
to_char(-485,'999') '-485'
to_char(485,'9 9 9') ' 4 8 5'
to_char(1485,'9,999') ' 1,485'
to_char(1485,'9G999') ' 1 485'
to_char(148.5,'999.999') ' 148.500'
to_char(148.5,'999D999') ' 148,500'
to_char(3148.5,'9G999D999') ' 3 148,500'
to_char(-485,'999S') '485-'
to_char(-485,'999MI') '485-'
to_char(485,'999MI') '485'
to_char(485,'PL999') '+485'
to_char(485,'SG999') '+485'
to_char(-485,'SG999') '-485'
to_char(-485,'9SG99') '4-85'
to_char(-485,'999PR') '<485>'
to_char(485,'L999') 'DM 485
to_char(485,'RN') ' CDLXXXV'
to_char(485,'FMRN') 'CDLXXXV'
to_char(5.2,'FMRN') V
to_char(482,'999th') ' 482nd'
to_char(485, '"Good number:"999') 'Good number: 485'
to_char(485.8,'"Pre:"999" Post:" .999') 'Pre: 485 Post: .800'
to_char(12,'99V999') ' 12000'
to_char(12.4,'99V999') ' 12400'
to_char(12.45, '99V9') ' 125'