一、什么是函数
任何东西，只要它能接收输入，对输入进行加工并产生输出，它就可以被称为函数。
二、单行函数简介
单行函数只对表中的一行数据进行操作，并且对每一行数据只产生一个输出结果。单行函数可以接受一个或多个参数，其产生的输出结果的数据类型可能与参数的数据类型不同。
单行函数包含：
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]’)：
该函数把字符串转换成日期型数据。