《SQL基础教程》第05章单行函数的使用

单行函数是和分组函数相对而言，按照大类可以分为五类：数值函数、字符串函数、日期函数、变换函数、其它函数。

数值函数

数值函数部分最容易混淆的不在函数，而在输出格式，因此函数转换时应该注意。

函数	说明	例子	例子结果
CEIL(n)	小数部分四舍五入后的整数	`CEIL`(43.75)	44
FLOOR(n)	小数部分截去后的整数	`FLOOR`(43.75)	43
MOD(m,n)	m整除n后的余数	`MOD`(20,6)	2
POWER(m,n)	m的n次幂	`POWER`(4,3)	64
SQRT(n)	n的算术平方根	`SQRT`(64)	8
ROUND(m,n)	对m的第n位小数后四舍五入	`ROUND`(873.14159,3) `ROUND`(873.14159,-2)	873.14200 900.00000
TRUNC(m,n)	对m的第n位小数后截去	`TRUNC`(873.14159,3) `TRUNC`(873.14159,-2)	873.14100 800.00000

字符串函数

字符川类型无任在哪儿都是最重要的类型。Oracle中trim函数已经相当强大。

函数	说明	例子	例子结果
LENGTH(s)	s的长度	`LENGTH`('abcdef')	6
INSTR(s1,s2,n,m)	s1的n位开始查s2 第m次出现的位置	`INSTR`('abcdefedcba','d') `INSTR`('abcdefedcba','d',5,1)	4 8
SUBSTR(s,n,m)	取s的n位开始的m个字符	`SUBSTR`('abcdef',3) `SUBSTR`('abcdef',3,2)	cdef cd
INITCAP(s)	s的单词首字母大写	`INITCAP`('into water')	Into Water
UPPER(s)	s全部大写	`UPPER`('into water')	INTO WATER
LOWER(s)	s全部小写	`LOWER`('Into Water')	into water
CONCAT(s1,s2)	s1与s2合起来	`CONCAT`('早上','好')	早上好
LPAD(s1,n,s2)	用s2加在s1的左边使s1的长度为n	`LPAD`('ab',6,'+-')	+-+-ab
RPAD(s1,n,s2)	用s2加在s1的右边使s1的长度为n	`RPAD`('ab',6,'+-')	ab+-+-
TRIM(s) TRIM(BOTH ' ' FROM s)	对s的两边空格截去	'[' \|\| `TRIM`(' ab ') \|\| ']' '[' \|\| `TRIM`(BOTH ' ' FROM ' xy ') \|\| ']'	[ab] [xy]
LTRIM(s) TRIM(LEADING s)	对s的左边空格截去	'[' \|\| `LTRIM(`' ab ') \|\| ']' '[' \|\| `TRIM`(LEADING ' ' FROM ' xy ') \|\| ']'	[ab ] [xy ]
RTRIM(s) TRIM(TRAILING s)	对s的右边空格截去	'[' \|\| `RTRIM`(' ab ') \|\| ']' '[' \|\| `TRIM`(TRAILING ' ' FROM ' xy ') \|\| ']'	[ ab] [ xy]
REPLACE(s1,s2,[s3])	在s1用s3替换s2	`REPLACE`('abcdedcbabcde','bc','BC')	aBCdedcbaBCde
TRIM(LEADING s1 FROM s2) TRIM(TRAILING s1 FROM s2) TRIM(BOTH s1 FROM s2)	保留s1在s2的头部之后保留s1在s2的尾部之前保留s1在s2的两头之间	`TRIM`(LEADING '日' FROM '本日去日本') `TRIM`(TRAILING '日' FROM '本日去日本') `TRIM`(BOTH '日' FROM '本日去日本')	去日本本日去去

日期函数

日期函数令许多人感到头痛，因为其格式千差万别，不同系统、不同语言、甚至不同版本都不尽相同。例如，要将某一指定日期存到日期字段里，一种办法是用函数将其转换为日期型，另一种办法是设置oracle的默认格式，使其与你要操作的格式一致。惟有SYSDATE 函数后面无括号。本章内容的日期型需要如下设置：

ALTER SESSION SET NLS_DATE_FORMAT='YYYY.MM.DD';

日期 ＋ 数值n    ＝ n天之后的日期
日期 － 数值n    ＝ n天之前的日期
日期 － 日期     ＝ 两个日期之间的天数
日期 ＋ 数值n/24 ＝ n/24小时之后的日期

SQL> select SYSDATE from DUAL;

SYSDATE
----------
08-8月 -03

SQL> alter SESSION set NLS_DATE_FORMAT='YYYY.MM.DD';

会话已更改。

SQL> select SYSDATE-4 from DUAL;

SYSDATE-4
----------
2003.08.04

SQL> select TO_DATE('2003.08.04')-TO_DATE('2003.03.26') 
  2  from   DUAL;

TO_DATE('2003.08.04')-TO_DATE('2003.03.26')
-------------------------------------------
                                        131

SQL> select SYSDATE-23/24 from DUAL;

SYSDATE-23
----------
2003.08.07

函数	说明	例子	例子结果
ADD_MONTHS(d,n)	日期d加上n个月后的日期	`ADD_MONTHS`('2003.04.30',2) `ADD_MONTHS`('2003.04.30',-2)	2003.06.30 2003.02.28
LAST_DAY(d)	日期d所在月份的最后一天	`LAST_DAY`('2003.08.08')	2003.08.31
NEXT_DAY(d,w)	从日期d开始下一个星期w的日期	`NEXT_DAY`('2003.08.08','星期二')	2003.08.12
MONTHS_BETWEEN(d1,d2)	日期d1和日期d2之间的月数	`MONTHS_BETWEEN`('2003.08.08','2003.03.26') `MONTHS_BETWEEN`('2003.08.08','2003.03.08')	4.41935484 5
SYSDATE	DB系统中的当前日期	`SYSDATE`	2003.08.08
TRUNC(d,[format])	按照格式强行截去	`TRUNC`(SYSDATE,'YEAR')	2004.01.01
ROUND(d,[format])	按照格式四舍五入截去	`ROUND`(SYSDATE,'YEAR')	2003.01.01

变换函数

下面四个函数都有3个参数，第3个参数都为nlsparams，该参数的信息包含小数点文字、时区、货币格式等，在控制面板上有相应的设置，这儿一般省略，因此下面也就不列出来讲解了。本节的关键在数值和日期的格式上。不同的格式得到的数据大部相同。例如：日期型数据利用TO_CHAR函数，通过设置不同的格式可以取出年、月、日、星期、时、分、秒等。

字符型可以直接和数值型转换，也可以直接和日期型转换，但数值型和日期型不能直接转换，需要先转换为字符性。

函数	说明
TO_NUMBER(s,format,nlsparams)	字符串→数值
TO_CHAR(n,format,nlsparams)	数值→字符串
TO_CHAR(d,format,nlsparams)	日期→字符串
TO_DATE(s,format,nlsparams)	字符串→日期

下表中最后两项与","和"."相同，使用这2项的原因是有的地区和国家并不是用","和"."来区分分组和小数位置的。控制面板中有修改的相应项。注意：数字转换为字符时，小数部分如超出格式范围，则四舍五入，整数部分如果超出格式范围，显示溢出"#####"。当字符转化为数字时，无论整数部分还是小数部分超出格式范围都将溢出或出错。

数值格式
元素	格式	内容	例子	结果
9	999	9的个数代表有效为数	`TO_CHAR`(1234.56,'999') `TO_CHAR`(1234.56,'9999')	#### 1235
0	099	位数不足时前面用0补足	`TO_CHAR`(1234.56,'0999') `TO_CHAR`(1234.56,'09999') `TO_CHAR`(123.56,'099999')	1235 01235 000124
$	$999	以$开头	`TO_CHAR`(1234.56,'$0999')	$1235
L	L999	以RMB开头	`TO_CHAR`(1234.56,'L0999')	RMB1235
,	999,999	用逗号分隔	`TO_CHAR`(1234.56,'9,999') `TO_CHAR`(1234.56,'09,999')	1,235 01,235
.	999.99	小数点的位置	`TO_CHAR`(1234.56,'9,999') `TO_CHAR`(1234.56,'09,999')	1,235 01,235
G	999G999	分组	`TO_CHAR`(1234.56,'9G999') `TO_CHAR`(1234.56,'09G999')	1,235 01,235
D	999D99	小数点	`TO_CHAR`(1.2346,'9D999') `TO_NUMBER`('1.2346','9D9999')	1.235 1.2346

格式元素
元素	内容	参数
D	小数点	NLS_NUMERIC_CHARACTORS
G	分组符号	NLS_NUMERIC_CHARACTORS
C	ISO货币符号	NLS_ISO_CURRENCY
L	本地区货币符号	NLS_CURRENCY

字符型转换为日期型时，只指明年份而没有月份时，默认为当前月。指定月份但没有月份中哪一天时，默认为该月第1天。时分秒如果未指定，则分别为00。

采用RR年份时，如果年份为00-49，则为本世纪的年份。如果年份为50-99，则为赏格世纪的年份。

日期格式
格式	内容	例子	结果
YYYY YYY YY Y RR	4位年份 3位年份 2位年份 1位年份 2位千禧年份	`TO_DATE`('2003','YYYY') `TO_DATE`('003','YYY') `TO_CHAR`(SYSDATE,'YY') `TO_CHAR`(SYSDATE,'Y') `TO_DATE`('03','RR')	2003.08.01 2003.08.01 03 3 2003.08.01
MM MONTH MON	月(01-12) 月(1月-12月) 月(JAN-DEC)	`TO_DATE`('03','MM') `TO_DATE`('3','MONTH') `TO_CHAR`(SYSDATE,'MON')	2003.03.01 2003.03.01 8月
DD	日(1-31)	`TO_DATE`('31','DD')	2003.08.31
DAY DY D	星期(星期日-星期六) 星期(星期日-星期六) 星期(1-7)	`TO_CHAR`(SYSDATE,'DAY') `TO_CHAR`(SYSDATE,'DY') `TO_CHAR`(SYSDATE,'D')	星期一星期一 2
HH HH12 HH24	时(12时制) 时(12时制) 时(24时制)	`TO_CHAR`(SYSDATE,'HH') `TO_CHAR`(SYSDATE,'HH12') `TO_CHAR`(SYSDATE,'HH24')	03 03 15
MI	分	`TO_CHAR`(SYSDATE,'HH')	45
SS	秒	`TO_CHAR`(SYSDATE,'SS')	36

其它函数

笔者的个人观点：oracle9增加的以下NVL2、NULLIF、COALESCE函数是多余的，只有NVL经常被使用，DECODE或CASE就已经包含了所有功能。

NVL(exp,val) ★★★
exp为null时，返回val的值。exp不为null时，返回exp的值。

SQL> select COMMISSION,NVL(COMMISSION,0)
  2  from   STUFF
  3  where  DEPID = 30;

COMMISSION NVL(COMMISSION,0)
---------- -----------------
      3000              3000
      5000              5000
     14000             14000
                           0
      3000              3000

NVL2(exp,val1,val2)
exp不为null时，返回val1的值。exp为null时，返回val2的值。

  1  select STDNAME,COMMISSION,SALARY,
  2         NVL2(COMMISSION,SALARY+COMMISSION,SALARY) 月收入
  3* from   STUFF
SQL> /

STDNAME  COMMISSION     SALARY     月收入
-------- ---------- ---------- ----------
张妃                      1800       1800
关玉           3000       2600       5600
刘蓓           5000       2250       7250
江叁讲                    2975       2975
李斯硼        14000       2250      16250
邓笑评                    3850       3850
孙荃                      3450       3450
周语                      3000       3000
鲁素                      9000       9000
曹草           3000       2500       5500
诸葛靓                    2100       2100
穆归营                    3000       3000
花牧岚                    2300       2300

已选择13行。

NULLIF(exp1,exp2)
exp1如果等于exp2，则返回null,否则返回exp1。

  1  select STDNAME,SALARY,SALARY+NVL(COMMISSION,0),
  2         NULLIF(SALARY,SALARY+NVL(COMMISSION,0)) 月收入
  3* from   STUFF
SQL> /

STDNAME      SALARY SALARY+NVL(COMMISSION,0) 月收入
-------- ---------- ------------------------ -------
张妃           1800                     1800
关玉           2600                     5600 5600
刘蓓           2250                     7250 7250
江叁讲         2975                     2975
李斯硼         2250                    16250 16250
邓笑评         3850                     3850
孙荃           3450                     3450
周语           3000                     3000
鲁素           9000                     9000
曹草           2500                     5500 5500
诸葛靓         2100                     2100
穆归营         3000                     3000
花牧岚         2300                     2300

已选择13行。

COALESCE(exp1,exp2,...,expn)
顺次对exp1,exp2...判断是否为null，返回最初不为null的值。

SQL> select COMMISSION,LEADER,SALARY,
  2         COALESCE(COMMISSION,LEADER,SALARY)
  3  from   STUFF;

COMMISSION LEAD     SALARY COALESCE(COMMISSION,LEADER,SALARY)
---------- ---- ---------- ----------------------------------
           7902       1800                               7902
      3000 7698       2600                               3000
      5000 7698       2250                               5000
           7839       2975                               7839
     14000 7698       2250                              14000
           7839       3850                               7839
           7839       3450                               7839
           7566       3000                               7566
                      9000                               9000
      3000 7698       2500                               3000
           7788       2100                               7788
           7566       3000                               7566
           7782       2300                               7782

已选择13行。

CASE

CASE exp WHEN cond1 THEN val1
         WHEN cond2 THEN val2
         ..........
         WHEN condn THEN valn
         ELSE default
END

exp的值为cond1，则返回val1;否则顺次判断cond2......,如果条件全部不成立，则返回defualt。没有ELSE时默认为null。

  1  select COMMISSION,LEADER,SALARY,
  2         CASE BUSINESS WHEN '科长' THEN 'MANAGER'
  3                       WHEN '销售' THEN 'SALESMAN'
  4                       ELSE 'OTHER'
  5  from   STUFF
  6* where  DEPID = '30'
SQL> /

COMMISSION LEAD     SALARY CASE(BUS
---------- ---- ---------- --------
      3000 7698       2600 SALESMAN
      5000 7698       2250 SALESMAN
     14000 7698       2250 SALESMAN
           7839       3850 MANAGER
      3000 7698       2500 SALESMAN

DECODE ★★★

DECODE (exp, cond1, val1,
             cond2, val2,
             ......
             condn, valn,
             default
       )

exp的值为cond1，则返回val1;否则顺次判断cond2......,如果条件全部不成立，则返回defualt。defualt默认为null。

  1  select COMMISSION,LEADER,SALARY,
  2         DECODE (BUSINESS, '科长', 'MANAGER',
  3                           '销售', 'SALESMAN',
  4                           'OTHER'
  5                )
  6  from   STUFF
  7* where  DEPID = '30'
SQL> /

COMMISSION LEAD     SALARY DECODE(B
---------- ---- ---------- --------
      3000 7698       2600 SALESMAN
      5000 7698       2250 SALESMAN
     14000 7698       2250 SALESMAN
           7839       3850 MANAGER
      3000 7698       2500 SALESMAN

GREATEST(exp1,exp2,...,expn)
在列表中取最大值

LEAST(exp1,exp2,...,expn)
在列表中取最小值