一、什么是函数
任何东西，只要它能接收输入，对输入进行加工并产生输出，它就可以被称为函数。
二、单行函数简介
单行函数只对表中的一行数据进行操作，并且对每一行数据只产生一个输出结果。单行函数可以接受一个或多个参数，其产生的输出结果的数据类型可能与参数的数据类型不同。
单行函数包含：
1.字符型
2.数字型
3.日期型
4.转换型
5.一般型函数
三、单行字符型函数
常用的字符型函数包括：
LOWER、UPPER、INITCAP、CONCAT、SUBSTR、LENGTH、INSTR、TRIM、REPLACE
1.LOWER(列名|表达式)：该函数是把字符转换成小写。
SQL> select LOWER('SQL: Structural Query Language')
2 from dual;
LOWER('SQL:STRUCTURALQUERYLANG
------------------------------
sql: structural query language
2.UPPER(列名|表达式)：该函数是把字符转换成大写。
SQL> select upper ('sql is used exclusively in rdbmses')
2 from dual;
UPPER('SQLISUSEDEXCLUSIVELYINRDBMS
----------------------------------
SQL IS USED EXCLUSIVELY IN RDBMSES
3.INITCAP(列名|表达式)：该函数是把每个字的头一个字符转换成大写，其余的转换成小写。
SQL> select INITCAP('SQL is an ENGLISH LIKE language')
2 from dual;
INITCAP('SQLISANENGLISHLIKELANG
-------------------------------
Sql Is An English Like Language
4.CONCAT(列名|表达式，列名|表达式)：该函数是把头一个字符串和第二个字符串连接成一个字符串。
SQL> select CONCAT('SQL allows you to manipulate the data in DB',
2 ' without any programming knowlege')
3 from dual;
CONCAT('SQLALLOWSYOUTOMANIPULATETHEDATAINDB','WITHOUTANYPROGRAMMINGKNOWLEGE'
----------------------------------------------------------------------------
SQL allows you to manipulate the data in DB without any programming knowlege
5.SUBSTR(列名|表达式，m,[n])：该函数是返回指定的字符串。该字符串是从第m个字符开始，其长度为n。
SQL> select SUBSTR('SQL lets you concentrate on what has to be done',14)
2 from dual;
SUBSTR('SQLLETSYOUCONCENTRATEONWHA
----------------------------------
concentrate on what has to be done
在这个例子中我们省略了n，其结果是返回从第14个字符开始一直到结尾的所有字符。
6.LENGTH(列名|表达式)：该函数是返回列中或表达式中字符串的长度。
SQL> select LENGTH('SQL does not let you concentrate on how it will be achieved')
2 from dual;
LENGTH('SQLDOESNOTLETYOUCONCENTRATEONHOWITWILLBEACHIEVED')
----------------------------------------------------------
59
7.INSTR(列名|表达式，'字符串'，[m],[n])：该函数是返回所给字符串的数字位置，m表示从第m个字符开始搜索，n表示所给字符串出现的次数，它们的默认值都为1。
SQL> select INSTR('SQL allows for dynamic DB changes','F')
2 from dual;
INSTR('SQLALLOWSFORDYNAMICDBCHANGES','F')
-----------------------------------------
0
SQL> select INSTR('SQL allows for dynamic DB changes','f')
2 from dual;
INSTR('SQLALLOWSFORDYNAMICDBCHANGES','F')
-----------------------------------------
12
8.TRIM([leading|trailing|both]要去掉的字符FROM源字符串)：该函数能够从“源字符串”中的头(leading)部，或尾(trailing)部，或从头(leading)部和尾(trailing)部(both)中去掉“要去掉的字符”。
如果没有指定头(leading)或尾(trailing)，TRIM函数按默认(both)处理(该函数是8i刚刚引入的，在8i之前的版本中是两个函数LTRIM和RTRIM)。
SQL> select TRIM('?' FROM '?SQL*PLUS is the SQL implementation
2 used in an Oracle RDBMS or ORDBMS.')
3 from dual;
TRIM('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.')
--------------------------------------------------------------------------------
SQL*PLUS is the SQL implementation
used in an Oracle RDBMS or ORDBMS.
也可以通过下面的查询语句来去掉rows之后的两个问号。
SQL> select TRIM('?' FROM ' It can process data in sets of rows??')
2 from dual;
TRIM('?'FROM'ITCANPROCESSDATAINSETSO
------------------------------------
It can process data in sets of rows
以上两个例子都没有指出是去掉头部的还是尾部的字符，因此Oracle按默认Both来处理。第一个例子只在头部有“？”而第二个例子只在尾部有“？”，所以Oracle系统可以正确的处理。
下面的例子，TRIM函数的这种用法就要遇到麻烦了。
SQL> select TRIM('s' FROM 'sql*plus is a fourth generation query languages')
2 from dual;
TRIM('S'FROM'SQL*PLUSISAFOURTHGENERATIONQUERY
---------------------------------------------
ql*plus is a fourth generation query language
在上面的SQL语句中，我们是想去掉尾部的“s”，但Oracle系统把开头的和结尾的“s”都去掉了。这显然不是我们所希望的结果。这时我们可以用trailing选项来解决这个问题。
SQL> select TRIM(trailing 's' FROM 'sql*plus is a fourth generation query languages')
2 from dual; TRIM(TRAILING'S'FROM'SQL*PLUSISAFOURTHGENERATI
----------------------------------------------
sql*plus is a fourth generation query language
在这里就不演示直接使用Leading选项的例子。
9.REPLACE(正文表达式，要搜寻的字符串，替换字符串)：该函数是在"正文表达式"中查找"要搜寻的字符串"，如果找到了就用"替换字符串"替代。
SQL> select REPLACE('SQL*PLUS supports loops or if statements','supports',
2 'does not support')
3 from dual;
REPLACE('SQL*PLUSSUPPORTSLOOPSORIFSTATEMENTS','S
------------------------------------------------
SQL*PLUS does not support loops or if statements
四、数字型函数
常用的数字型函数包括：
ROUND、TRUNC、MOD
1.ROUND(列名|表达式，n)：
该函数将列名或表达式所表示的数值四舍五入到小数点后的n位。
2.TRUNC(列名|表达式，n)：
该函数将列名或表达式所表示的数值截取到小数点后的n位。
3.MOD(m,n)：
该函数将m除以n并取余数。
SQL> select ROUND(168.888,1),TRUNC(168.888,1)
2 from dual; ROUND(168.888,1) TRUNC(168.888,1)
---------------- ----------------
168.9 168.8 SQL> select MOD(300,400)
2 from dual; MOD(300,400)
------------
300
五、日期函数
首先演示一下查询数据库的时间例子：
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY
-------------------
2013-07-07 09:44:36
常用的日期型函数包括：
MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY
1.MONTHS_BETWEEN(日期1，日期2)：
该函数是返回日期1和日期2之间的月数。如果日期1大于日期2，其返回的月数为正。如果日期1小于日期2，其返回的月数为负。
2.ADD_MONTHS(日期，n)：
该函数是把n个月加到日期上。
3.NEXT_DAY(日期，字符串)：
该函数是返回下一个由字符串(星期几)指定的日期。
4.LAST_DAY(日期)：
该函数是返回该日期所在月的最后一天。
一个综合的例子：
SQL> select ename,hiredate,LAST_DAY(hiredate),NEXT_DAY(hiredate,'SUNDAY'),
2 MONTHS_BETWEEN(SYSDATE,hiredate) "Months",
3 ADD_MONTHS(hiredate,3) "Revies"
4 from emp; ENAME HIREDATE LAST_DAY( NEXT_DAY( Months Revies
---------- --------- --------- --------- ---------- ---------
SMITH 17-DEC-80 31-DEC-80 21-DEC-80 390.690779 17-MAR-81
ALLEN 20-FEB-81 28-FEB-81 22-FEB-81 388.594005 20-MAY-81
WARD 22-FEB-81 28-FEB-81 01-MAR-81 388.529489 22-MAY-81
JONES 02-APR-81 30-APR-81 05-APR-81 387.17465 02-JUL-81
MARTIN 28-SEP-81 30-SEP-81 04-OCT-81 381.33594 28-DEC-81
BLAKE 01-MAY-81 31-MAY-81 03-MAY-81 386.206908 01-AUG-81
CLARK 09-JUN-81 30-JUN-81 14-JUN-81 384.948844 09-SEP-81
SCOTT 19-APR-87 30-APR-87 26-APR-87 314.626263 19-JUL-87
KING 17-NOV-81 30-NOV-81 22-NOV-81 379.690779 17-FEB-82
TURNER 08-SEP-81 30-SEP-81 13-SEP-81 381.981102 08-DEC-81
ADAMS 23-MAY-87 31-MAY-87 24-MAY-87 313.497231 23-AUG-87
JAMES 03-DEC-81 31-DEC-81 06-DEC-81 379.142392 03-MAR-82
FORD 03-DEC-81 31-DEC-81 06-DEC-81 379.142392 03-MAR-82
MILLER 23-JAN-82 31-JAN-82 24-JAN-82 377.497231 23-APR-82 14 rows selected.
六、ROUND和TRUNC函数用于日期型数据
除了可以把ROUND和TRUNC函数用于数字型数据外，还可以把ROUND和TRUNC函数用于日期型数据。
SQL> select ROUND(TO_DATE('25-JUL-13'),'MONTH')
2 from dual;
ROUND(TO_
---------
01-AUG-13
SQL> select TRUNC(TO_DATE('25-JUL-13'),'MONTH')
2 from dual;
TRUNC(TO_
---------
01-JUL-13
SQL> select ename,hiredate,ROUND(hiredate,'YEAR'),TRUNC(hiredate,'YEAR'),
2 ROUND(hiredate,'MONTH'),TRUNC(hiredate,'MONTH')
3 from emp
4 where hiredate like '%81';
ENAME HIREDATE ROUND(HIR TRUNC(HIR ROUND(HIR TRUNC(HIR
---------- --------- --------- --------- --------- ---------
ALLEN 20-FEB-81 01-JAN-81 01-JAN-81 01-MAR-81 01-FEB-81
WARD 22-FEB-81 01-JAN-81 01-JAN-81 01-MAR-81 01-FEB-81
JONES 02-APR-81 01-JAN-81 01-JAN-81 01-APR-81 01-APR-81
MARTIN 28-SEP-81 01-JAN-82 01-JAN-81 01-OCT-81 01-SEP-81
BLAKE 01-MAY-81 01-JAN-81 01-JAN-81 01-MAY-81 01-MAY-81
CLARK 09-JUN-81 01-JAN-81 01-JAN-81 01-JUN-81 01-JUN-81
KING 17-NOV-81 01-JAN-82 01-JAN-81 01-DEC-81 01-NOV-81
TURNER 08-SEP-81 01-JAN-82 01-JAN-81 01-SEP-81 01-SEP-81
JAMES 03-DEC-81 01-JAN-82 01-JAN-81 01-DEC-81 01-DEC-81
FORD 03-DEC-81 01-JAN-82 01-JAN-81 01-DEC-81 01-DEC-81
10 rows selected.
七、不同数据类型之间的隐含转换
赋值语句中数据类型之间的隐含转换(Oracle自动转换)：
1.将变长字符型(VARCHAR2)或定长字符型(CHAR)转换成数字型(NUMBER)；
2.将变长字符型(VARCHAR2)或定长字符型(CHAR)转换成日期型(DATE)；
3.将数字型(NUMBER)转换成变长字符型(VARCHAR2)；
4.将日期型(DATE)转换成变长字符型(VARCHAR2)。
八、不同数据类型之间的显式转换
Oracle提供了3个转换函数来完成不同数据类型之间的显式转换。
TO_CHAR、TO_NUMBER、TO_DATE
1.TO_CHAR(日期，‘fmt')：
该函数的这种格式把日期型数据转换成变长字符串，其中，fmt为日期模式。
SQL> select ename,to_char(hiredate,'DD/MM/YY')
2 from emp
3 where hiredate like '%82'; ENAME TO_CHAR(
---------- --------
MILLER 23/01/82
常用的日期模式：
a.YYYY：完整的年份数字表示(如2001)；
b.YEAR：年份的英文表示(如NINETEEN EIGHTY-SEVEN)；
c.MM：用两位数字来表示月份；
d.MONTH：月份完整的英文表示；
e.DY：用3个英文字符的缩写来表示星期几；
f.DAY：星期几的完整的英文表示；
g.DD：几号的数字表示。
查询员工的名字(Name)，工资(Salary)和雇佣日期(HIREDATE)的信息。
SQL> select ename "Name",sal "Salary",
2 to_char(hiredate,'fmDdspth "of" Month Year fmHH:MI:SS AM') HIREDATE
3 from emp; Name Salary HIREDATE
---------- ---------- ----------------------------------------------------------------------------------------------------
SMITH 800 Seventeenth of December Nineteen Eighty 12:00:00 AM
ALLEN 1600 Twentieth of February Nineteen Eighty-One 12:00:00 AM
WARD 1250 Twenty-Second of February Nineteen Eighty-One 12:00:00 AM
JONES 2975 Second of April Nineteen Eighty-One 12:00:00 AM
MARTIN 1250 Twenty-Eighth of September Nineteen Eighty-One 12:00:00 AM
BLAKE 2850 First of May Nineteen Eighty-One 12:00:00 AM
CLARK 2450 Ninth of June Nineteen Eighty-One 12:00:00 AM
SCOTT 3000 Nineteenth of April Nineteen Eighty-Seven 12:00:00 AM
KING 5000 Seventeenth of November Nineteen Eighty-One 12:00:00 AM
TURNER 1500 Eighth of September Nineteen Eighty-One 12:00:00 AM
ADAMS 1100 Twenty-Third of May Nineteen Eighty-Seven 12:00:00 AM
JAMES 950 Third of December Nineteen Eighty-One 12:00:00 AM
FORD 3000 Third of December Nineteen Eighty-One 12:00:00 AM
MILLER 1300 Twenty-Third of January Nineteen Eighty-Two 12:00:00 AM 14 rows selected.
2.TO_CHAR(数字，‘fmt')：
该函数的这种格式把数字型数据转换成变长字符串。
其中，fmt为数字模式。常用的数字模式包括：
9：位数字；
0：显示前导零；
$：显示美元号；
L：显示本地货币号；
.：显示小数点；
，：显示千位符；
MI：在数的右边显示减号；
PR：把负数用尖括号扩起来。
下面的查询语句用来显示员工的名字(Name)和年薪(Annual Salary)，其中显示年薪时要包括美元($)符、千位符(，)和小数点(.)，而且要显示小数点后两位数，即使小数点后的数为0也要显示。
SQL> select ename "Name",TO_CHAR(sal*12,'$99,999.00') "Annual Salary"
2 from emp; Name Annual Sala
---------- -----------
SMITH $9,600.00
ALLEN $19,200.00
WARD $15,000.00
JONES $35,700.00
MARTIN $15,000.00
BLAKE $34,200.00
CLARK $29,400.00
SCOTT $36,000.00
KING $60,000.00
TURNER $18,000.00
ADAMS $13,200.00
JAMES $11,400.00
FORD $36,000.00
MILLER $15,600.00 14 rows selected.
3.TO_NUMBER(字符串 [，'fmt]‘)：
该函数把字符串转换成数字。
4.TO_DATE(字符串 [，'fmt]’)：
该函数把字符串转换成日期型数据。