单行函数
函数名 [（参数1，参数2，参数3，…）]
其中参数可以为：用户定义的常量、变量、列名、表达式。
单行函数只对表中的一行数据进行操作，并且对每一行数据只产生一个输出结果。单行函数可以接受一个或多个参数，其产生的输出结果的数据类型可能与参数的数据类型不同。
单行函数可用在如下的子句中：SELECT、WHERE 、ORDER BY。而且单行函数可以嵌套。
单行函数包含：字符型、数字型、日期型、转换型、一般型函数。
一：单行字符型函数
常用的字符型函数包括：
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
LENGTH
INSTR
TRIM
REPLACE
下面我们通过例子来分别介绍它们。
LOWER（列名|表达式）：该函数是把字符转换成小写。
SQL> select lower('SQL:Structural Query Language') from dual;
LOWER('SQL:STRUCTURALQUERYLAN
-----------------------------
sql:structural query language
SQL>
UPPER（列名|表达式）：该函数是把字符转换成大写。
SQL> select upper('sql is used exclusively in rdbmses') from dual;
UPPER('SQLISUSEDEXCLUSIVELYINRDBMS
----------------------------------
SQL IS USED EXCLUSIVELY IN RDBMSES
SQL>
INITCAP（列名|表达式）：该函数是把每个字的头一个字符转换成大写，其余的转换成小写。
SQL> select initcap('SQL is an ENGLISH LIKE language') from dual;
INITCAP('SQLISANENGLISHLIKELANG
-------------------------------
Sql Is An English Like Language
SQL>
CONCAT（列名|表达式，列名|表达式）：该函数是把头一个字符串和第二个字符串连接成一个字符串。
SQL> select concat('SQL allows you to manipulate the data in DB',' without any programming knowledge') from dual;
CONCAT('SQLALLOWSYOUTOMANIPULATETHEDATAINDB','WITHOUTANYPROGRAMMINGKNOWLEDGE'
-----------------------------------------------------------------------------
SQL allows you to manipulate the data in DB without any programming knowledge
SQL>
SUBSTR（列名|表达式，m,[ n ]）：该函数是返回指定的子串。该子串是从第m个字符开始，其长度为n。
如果省略n，其结果是返回从第m个字符开始一直到结尾的所有字符，字符串的下标从1开始。
SQL> select substr('SQL lets you concentrate on what has to be done',14) from dual;
SUBSTR('SQLLETSYOUCONCENTRATEONWHA
----------------------------------
concentrate on what has to be done
SQL> select substr('hello java',7,4) from dual;
SUBS
----
java
SQL>
LENGTH（列名|表达式）：该函数是返回列中或表达式中字符串的长度。
SQL> select length('hello') from dual;
LENGTH('HELLO')
---------------
5
SQL>
INSTR（列名|表达式，‘字符串’，[m], [n]）：该函数是返回所给字符串的数字位置，m表示从第m个字符开始搜索，n表示所给字符串出现的次数，它们的默认值都为1。字符串是区分大小写的。
SQL> select instr('SQL allows for dynamic DB changes','F') from dual;
INSTR('SQLALLOWSFORDYNAMICDBCHANGES','F')
-----------------------------------------
0
SQL> select instr('SQL allows for dynamic DB changes','f') from dual;
INSTR('SQLALLOWSFORDYNAMICDBCHANGES','F')
-----------------------------------------
12
SQL> select instr('c DB changes','c',3,1) from dual;
INSTR('CDBCHANGES','C',3,1)
---------------------------
6
SQL> select instr('c DB changes','c',1,1) from dual;
INSTR('CDBCHANGES','C',1,1)
---------------------------
1
SQL>
TRIM（[leading|trailing| both]要去掉的字符 FROM 源字符串）：该函数能够从“源字符串”中的头（leading）部，或尾（trailing）部，
或从头（leading）部和尾（trailing）部（both）中去掉“要去掉的字符”。 如果没有指定头（leading）或尾（trailing），
TRIM函数按默认（both）处理（该函数是8i刚刚引入的，在8i之前的版本中是两个函数LTRIM和RTRIM）。
SQL> select trim('?' from '?hello java?') from dual;
TRIM('?'FR
----------
hello java
SQL> select trim(leading '?' from '?hello java?') from dual;
TRIM(LEADIN
-----------
hello java?
SQL> select trim(trailing '?' from '?hello java?') from dual;
TRIM(TRAILI
-----------
?hello java
SQL> select trim('?' from '???hello java????') from dual;
TRIM('?'FR
----------
hello java
SQL>
REPLACE（正文表达式，要搜寻的字符串，替换字符串）：该函数是在“正文表达式”中查找“要搜寻的字符串”，如果找到了就用“替换字符串”替代。
SQL> select replace('SQL*PLUS supports loops or if statements','supports','does not support') from dual;
REPLACE('SQL*PLUSSUPPORTSLOOPSORIFSTATEMENTS','S
------------------------------------------------
SQL*PLUS does not support loops or if statements
SQL>
==================================实际应用举例=========================================================================================
SQL> select empno as "Code",upper(ename) NAME,initcap(job) "Job"
2 from emp
3 where lower(job)='salesman';
Code NAME Job
---------- ---------- ---------
7499 ALLEN Salesman
7521 WARD Salesman
7654 MARTIN Salesman
7844 TURNER Salesman
SQL> select ename,job,concat(ename,job) "Employee",substr(job,1,5) "Title",length(ename) "length",instr(job,'M')
2 from emp
3 where upper(job) = 'SALESMAN';
ENAME JOB Employee Title length INSTR(JOB,'M')
---------- --------- ------------------- ---------- ---------- --------------
ALLEN SALESMAN ALLENSALESMAN SALES 5 6
WARD SALESMAN WARDSALESMAN SALES 4 6
MARTIN SALESMAN MARTINSALESMAN SALES 6 6
TURNER SALESMAN TURNERSALESMAN SALES 6 6
SQL>
二：数字型函数
常用的数字型函数包括：ROUND、TRUNC、MOD。
ROUND（列名|表达式，n）：该函数将列名或表达式所表示的数值四舍五入到小数点后的n位。n为1时，四舍五入到小数点后1位，n为-1时四舍五入到十位数，以此类推。
TRUNC（列名|表达式， n）：该函数将列名或表达式所表示的数值截取到小数点后的n位。n为1时，截取到小数点后1位，n为-1时截取到十位数，以此类推。
MOD（m，n）：该函数将m除以n并取余数。
SQL> select round(16.888,1),trunc(16.888,1),round(16.333,2),trunc(16.333,2),round(16.8888,0),trunc(16.888,0) from dual;
ROUND(16.888,1) TRUNC(16.888,1) ROUND(16.333,2) TRUNC(16.333,2) ROUND(16.8888,0) TRUNC(16.888,0)
--------------- --------------- --------------- --------------- ---------------- ---------------
16.9 16.8 16.33 16.33 17 16
SQL> select round(16.888),trunc(16.888),round(168.888,-1),trunc(168.888,-1) from dual;
ROUND(16.888) TRUNC(16.888) ROUND(168.888,-1) TRUNC(168.888,-1)
------------- ------------- ----------------- -----------------
17 16 170 160
SQL> select mod(5,2),mod(4,2),mod(2,3) from dual;
MOD(5,2) MOD(4,2) MOD(2,3)
---------- ---------- ----------
1 0 2
SQL>
三：日期型数据的处理
Oracle9i 日期型数据输入和输出的默认格式为 DD-MON-RR，而在之前的版本中为DD-MON-YY
alter session set nls_date_format="MON-DD-YYYY"; --更改系统日期默认格式
show parameter nls_date_format --查询系统日期默认格式（DBA权限）
如果您使用的是中文操作系统，而数据库的字符集为美国英语（导致日期型数据无法正常显示时），为了使日期型数据的显示正确。
SQL> select sysdate from dual;
SYSDATE
--------------
21-2月 -11
SQL> alter session set nls_date_language = 'american';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
------------
21-FEB-11
SQL>
可以把一个日期型数据和一个数字（默认单位是天）相加减，其结果仍为日期型。
也可以把一个日期型数据和一个小时数相加减，这个数要除以24，其结果仍为日期。
SQL> select sysdate, sysdate-10,sysdate+10 from dual;
SYSDATE SYSDATE-10 SYSDATE+10
-------------- -------------- --------------
21-2月 -11 11-2月 -11 03-3月 -11
SQL>
可以把两个日期型数据相减，其结果为数字型（结果的单位是天）。
SQL> select to_date('23-2月-11')-sysdate,sysdate from dual;
TO_DATE('23-2月-11')-SYSDATE SYSDATE
---------------------------- --------------
1.24943287 21-2月 -11
SQL> select to_char(sysdate,'YYYY-MM-DD hh24:mm:ss'),sysdate-48/24,sysdate-22/24,sysdate-3/24,to_char(sysdate-3/24,'YYYY-MM-DD hh24:mm:ss') from dual;
TO_CHAR(SYSDATE,'YY SYSDATE-48/24 SYSDATE-22/24 SYSDATE-3/24 TO_CHAR(SYSDATE-3/2
------------------- -------------- -------------- -------------- -------------------
2011-02-21 18:02:43 19-2月 -11 20-2月 -11 21-2月 -11 2011-02-21 15:02:43
四：常用的日期型函数。
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
MONTHS_BETWEEN（日期1，日期2）：该函数是返回日期 1 和日期 2 之间的月数。如果日期 1 大于日期 2，其返回的月数为正。如果日期1小于日期2，其返回的月数为负。
SQL> select months_between('21-2月-11','16-3月-11'),months_between('16-3月-11','12-5月-10') from dual;
MONTHS_BETWEEN('21-2月-11','16-3月-11') MONTHS_BETWEEN('16-3月-11','12-5月-10')
--------------------------------------- ---------------------------------------
-.83870968 10.1290323
ADD_MONTHS（日期，n）： 该函数是把n个月加到日期上。
SQL> alter session set nls_date_language = 'american';
会话已更改。
SQL> select add_months('12-may-10',10) from dual;
ADD_MONTHS('
------------
12-MAR-11
SQL>
NEXT_DAY（日期，字符串）： 该函数是返回下一个由字符串（星期几）指定的日期。
SQL> select next_day('21-feb-11','monday') next_monday from dual;
NEXT_MONDAY
------------
28-FEB-11
SQL>
LAST_DAY（日期）：该函数是返回该日期所在月的最后一天。
SQL> select last_day('21-feb-11') from dual;
LAST_DAY('21
------------
28-FEB-11
SQL>
SQL> l
1 select ename,hiredate,last_day(hiredate),next_day(hiredate,'sunday'),months_between(sysdate,hiredate) "Months",add_months(hiredate,3) "Review Date"
2* from emp
SQL> /
ENAME HIREDATE LAST_DAY(HIR NEXT_DAY(HIR Months Review Date
---------- ------------ ------------ ------------ ---------- ------------
SMITH 17-DEC-80 31-DEC-80 21-DEC-80 362.157889 17-MAR-81
ALLEN 20-FEB-81 28-FEB-81 22-FEB-81 360.061115 20-MAY-81
WARD 22-FEB-81 28-FEB-81 01-MAR-81 359.996599 22-MAY-81
JONES 02-APR-81 30-APR-81 05-APR-81 358.64176 02-JUL-81
MARTIN 28-SEP-81 30-SEP-81 04-OCT-81 352.803051 28-DEC-81
BLAKE 01-MAY-81 31-MAY-81 03-MAY-81 357.674018 01-AUG-81
CLARK 09-JUN-81 30-JUN-81 14-JUN-81 356.415954 09-SEP-81
SCOTT 19-APR-87 30-APR-87 26-APR-87 286.093373 19-JUL-87
KING 17-NOV-81 30-NOV-81 22-NOV-81 351.157889 17-FEB-82
TURNER 08-SEP-81 30-SEP-81 13-SEP-81 353.448212 08-DEC-81
ADAMS 23-MAY-87 31-MAY-87 24-MAY-87 284.964341 23-AUG-87
JAMES 03-DEC-81 31-DEC-81 06-DEC-81 350.609502 03-MAR-82
FORD 03-DEC-81 31-DEC-81 06-DEC-81 350.609502 03-MAR-82
MILLER 23-JAN-82 31-JAN-82 24-JAN-82 348.964341 23-APR-82
已选择14行。
SQL>
五：ROUND 和TRUNC函数用于日期型数据
除了可以把ROUND和TRUNC函数用于数字型数据外，还可以把ROUND 和TRUNC函数用于日期型数据。
而ROUND函数是不能用于字符型数据的。所以要先使用 TO_DATE 函数把字符串转换成日期之后才能用ROUND和TRUNC函数。
round用于日期时，精确到月时的规则是”1-15号 舍，16号以上 进一“，既15舍16入。精确到年时的规则是6舍7入。
SQL> select round('21-feb-11','month') from dual;
select round('21-feb-11','month') from dual
*
第 1 行出现错误:
ORA-01722: 无效数字
SQL> select round(to_date('21-feb-11'),'month') from dual;
ROUND(TO_DAT
------------
01-MAR-11
SQL> select round(to_date('15-feb-11'),'month'),round(to_date('16-feb-11'),'month') from dual;
ROUND(TO_DAT ROUND(TO_DAT
------------ ------------
01-FEB-11 01-MAR-11
SQL> select round(to_date('15-march-11'),'month'),round(to_date('16-march-11'),'month') from dual
ROUND(TO_DAT ROUND(TO_DAT
------------ ------------
01-MAR-11 01-APR-11
SQL> select round(to_date('30-june-11'),'year'),round(to_date('01-july-11'),'year') from dual;
ROUND(TO_DAT ROUND(TO_DAT
------------ ------------
01-JAN-11 01-JAN-12
SQL> select trunc(to_date('21-feb-11'),'month'),trunc(to_date('30-june-11'),'year'),trunc(to_date('31-december-11'),'year') from dua
TRUNC(TO_DAT TRUNC(TO_DAT TRUNC(TO_DAT
------------ ------------ ------------
01-FEB-11 01-JAN-11 01-JAN-11
六：不同数据类型之间的隐含转换
赋值语句中数据类型之间的隐含转换（Oracle自动转换）：
将变长字符型（VARCHAR2）或定长字符型（CHAR）转换成数字型（NUMBER）；
将变长字符型（VARCHAR2）或定长字符型（CHAR）转换成日期型（DATE）；
将数字型（NUMBER）转换成变长字符型（VARCHAR2）；
将日期型（DATE）转换成变长字符型（VARCHAR2）。
表达式中（Oracle自动转换）：
将变长字符型（VARCHAR2）或定长字符型（CHAR）转换成数字型（NUMBER）；
将变长字符型（VARCHAR2）或定长字符型（CHAR）转换成日期型（DATE）。
在将字符类型的数据转换为数字型时，要保证字符型数据为有效的数。在将字符类型的数据转换为日期型时，要保证字符型数据为有效的日期，否则转换不能成功。
数字型（NUMBER）的数据与日期型（DATE）的数据之间不能进行直接转换。必须将其中的一种数据类型先转换成字符型，之后再将字符型转换成其中的另一种数据类型。
注意：尽管 Oracle 提供了数据类型之间的隐含转换方法，但读者应该尽量避免使用这种方法。因为用隐含转换方法写出来的SQL语句其他的人很难理解，随着时间的流逝连您自
己都很难理解，特别是当SQL语句嵌在大型程序中时。另外，Oracle推出新的版本时可能会修改一些隐含转换的规则，这会使您的程序的移植遇到麻烦。
七：不同数据类型之间的显示转换
Oracle提供了3个转换函数来完成不同数据类型之间的显式转换。
TO_CHAR
TO_NUMBER
TO_DATE
TO_CHAR（日期，‘fmt’）：该函数的这种格式把日期型数据转换成变长字符串，其中，fmt为日期模式，必须用单引号括起来。
SQL> select to_char(sysdate),to_char(hiredate,'YYYY-MM-DD') from emp where hiredate like '%82';
TO_CHAR(S TO_CHAR(HI
--------- ----------
21-FEB-11 1982-01-23
YYYY：完整的年份数字表示（如2001）；
YEAR：年份的英文表示（如NINETEEN EIGHTY-SEVEN）；
MM：用两位数字来表示月份；
MONTH：月份完整的英文表示；
DY：用3个英文字符的缩写来表示星期几；
DAY：星期几的完整的英文表示；
DD：几号的数字表示。
TO_CHAR（数字，‘fmt’）： 该函数的这种格式把数字型数据转换成变长字符串。
9：一位数字；
0：显示前导零；
$：显示美元号；
L：显示本地货币号；
.：显示小数点；
，：显示千位符；
MI：在数的右边显示减号；
PR：把负数用尖括号扩起来。
SQL> SELECT ename "Name", TO_CHAR(sal*12, '$99,999.00') "dollar Annual Salary",TO_CHAR(sal*12, 'L99,999.00') "RMB Annual Salary" from emp;
Name dollar Annu RMB Annual Salary
---------- ----------- --------------------
SMITH $9,600.00 ￥9,600.00
ALLEN $19,200.00 ￥19,200.00
WARD $15,000.00 ￥15,000.00
JONES $35,700.00 ￥35,700.00
MARTIN $15,000.00 ￥15,000.00
BLAKE $34,200.00 ￥34,200.00
CLARK $29,400.00 ￥29,400.00
SCOTT $36,000.00 ￥36,000.00
KING $60,000.00 ￥60,000.00
TURNER $18,000.00 ￥18,000.00
ADAMS $13,200.00 ￥13,200.00
JAMES $11,400.00 ￥11,400.00
FORD $36,000.00 ￥36,000.00
MILLER $15,600.00 ￥15,600.00
已选择14行。
当在把数字转换成字符串显示时其位数一定要留够，否则您只能得到一些#号。如下：
SQL> col salary for a10;
SQL> select sal*1000000 salary from emp;
SALARY
----------
##########
##########
##########
##########
##########
##########
##########
##########
##########
##########
##########
##########
##########
##########
已选择14行。
八：两千年问题。
RR 日期格式的算法和实例：
（1）如果当前年份的最后两位数（即不包括世纪）为 0~49，并且指定的年份的最后
两位数也为 0~49，则返回的日期在本世纪。（例如：当前年份为 2002 年，指明的日期是
01-OCT-08，RR日期格式返回的日期为：2008年10月1日。而YY日期格式返回的日期
也为：2008年10月1日。）
（2）如果当前年份的最后两位数（即不包括世纪）为 0~49，并且指定的年份的最后
两位数为50~99，则返回的日期为上一世纪。（例如：当前年份为2002年，指明的日期是
01-OCT-98，RR日期格式返回的日期为：1998年10月1日。而YY日期格式返回的日期
则为：2098年10月1日。这也许就是我们所说的两千年问题。）
（3）如果当前年份的最后两位数（即不包括世纪）为50~99，并且指定的年份的最后
两位数为 0~49，则返回的日期为下一世纪。（例如：当前年份为 1999 年，指明的日期是
01-OCT-08，RR日期格式返回的日期为：2008年10月1日。而YY日期格式返回的日期
则为：1908年10月1日。）
（4）如果当前年份的最后两位数（即不包括世纪）为50~99，并且指定的年份的最后
两位数也为50~99，则返回的日期在本世纪。（例如：当前年份为1999年，指明的日期是
01-OCT-98，RR日期格式返回的日期为：1998年10月1日。而YY日期格式返回的日期
也为：1998年10月1日。）
下面我们来看一个比较实际的例子。下例的查询语句是要显示在 1981 年所雇用的
所有员工的名字（Name）、工资（Salary）和雇用日期（hiredate）的信息。
SQL> l
1 SELECT ename "Name", job, sal AS "Salary", hiredate
2 FROM emp
3 WHERE hiredate BETWEEN '01-Jan-81' AND '31-Dec-81'
4* ORDER BY hiredate
SQL> /
Name JOB Salary HIREDATE
---------- --------- ---------- ------------
ALLEN SALESMAN 1600 20-FEB-81
WARD SALESMAN 1250 22-FEB-81
JONES MANAGER 2975 02-APR-81
BLAKE MANAGER 2850 01-MAY-81
CLARK MANAGER 2450 09-JUN-81
TURNER SALESMAN 1500 08-SEP-81
MARTIN SALESMAN 1250 28-SEP-81
KING PRESIDENT 5000 17-NOV-81
JAMES CLERK 950 03-DEC-81
FORD ANALYST 3000 03-DEC-81
已选择10行。
但是如果手动把日期格式改为'DD-MM-YY'的话，则什么也差不到，如下：
SQL> l
1 SELECT ename "Name", job, sal AS "Salary", hiredate
2 FROM emp
3 where hiredate between to_date('01-Jan-81','DD-MM-YY') and to_date('31-Dec-81','DD-MM-YY')
4* ORDER BY hiredate
SQL> /
未选定行
SQL>
这是因为to_date('01-Jan-81','DD-MM-YY')转换后的日期为01-Jan-2081，而 emp 表中根本就没有任何 2081 年的记录。这就是两千年问题。
SQL> select to_char(to_date('01-Jan-81','DD-MM-YY'),'YYYY-MM-DD'),to_char(to_date('01-Jan-81','DD-MM-RR'),'YYYY-MM-DD') from dual;
TO_CHAR(TO TO_CHAR(TO
---------- ----------
2081-01-01 1981-01-01
SQL>
可以在您的查询语句中显式地指明日期格式为RR方式，Oracle9i 中默认日期输入输出格式也为 RR 方式。
Oracle9i 以前的版本默认日期输入输出格式为 YY 方式
SQL> l
1 SELECT ename "Name", job, sal AS "Salary", hiredate
2 FROM emp
3 where hiredate between to_date('01-Jan-81','DD-MM-RR') and to_date('31-Dec-81','DD-MM-RR')
4* ORDER BY hiredate
SQL> /
Name JOB Salary HIREDATE
---------- --------- ---------- ------------
ALLEN SALESMAN 1600 20-FEB-81
WARD SALESMAN 1250 22-FEB-81
JONES MANAGER 2975 02-APR-81
BLAKE MANAGER 2850 01-MAY-81
CLARK MANAGER 2450 09-JUN-81
TURNER SALESMAN 1500 08-SEP-81
MARTIN SALESMAN 1250 28-SEP-81
KING PRESIDENT 5000 17-NOV-81
JAMES CLERK 950 03-DEC-81
FORD ANALYST 3000 03-DEC-81
已选择10行。
SQL>
扫一扫
434
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
