两种 SQL 函数 ：
单行函数 基于单行的处理，一行产生一个结果
多行函数 基于多行的处理，多行产生一个结果
单行函数：
操作数句对象
接受函数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
单行函数包括：
字符函数,数值函数,日期函数,转换函数,通用函数
字符函数：
大小写控制函数---->这类函数改变字符的大小写
LOWER('SQL Course') sql course 转小写
UPPER('SQL Course') SQL COURSE 转大写
INITCAP('SQL Course') Sql Course 单词首字母转大写
eg:
SELECT empno,ename,deptno FROM scott.emp
WHERE LOWER(ename) = 'smith';
注：加了函数，原有
字符控制函数--->这类函数控制字符
CONCAT 字符连接同 “||”
SUBSTR 取子串
LENGTH 测试长度
INSTR 测试子字符串位置
LPAD | RPAD 定长字符串，左|右填充指定字符
TRIM 从一个字串中删除一个字符
REPLACE 字符替换
例：通过上面函数组合生一个简单的备份和还原语句
SQL> select 'host cp '||name||' /u03/backup' from v$datafile;
SQL> select 'host cp /u03/backup'||substr(name,instr(name,'/',-1))||' '||name
2 from v$datafile;
CONCAT('Hello', 'World') HelloWorld 字符连接
SUBSTR('HelloWorld',1,5) Hello 取子串，从第 1 个开始，取 5 个
LENGTH('HelloWorld') 10 返加表达式的长度，单位为字符（lengthb单位为字节）
INSTR('HelloWorld', 'W') 6 测试子字符串的位置
LPAD(salary,10,'*') *****24000 固定字符串长度，左边加＊
RPAD(salary, 10, '*') 24000***** 固定字符串长度，右边加＊
TRIM('H' FROM 'HelloWorld') elloWorld
从字符串中删除字符（默认字符为空格），默认会删除所有，
可以加控制方式，以删除开头，结尾，或者两头都删，默认both
SQL> select trim(leading 's' from 'sdfasdfs') from dual;
------
dfasdfs
SQL> select trim(both 's' from 'sdfasdfs') from dual;
------
dfasdf
SQL> select trim(trailing 's' from 'sdfasdfs') from dual;
-------
sdfasdf
REPLACE('123456','5','xxx') 1234xxx6 字符替换
select empno,concat(ENAME,JOB) name,length(ENAME),
instr(ENAME,'I') "CONTAINS 'I'?" FROM emp;
EMPNO NAME LENGTH(ENAME) CONTAINS 'I'?
------ ------------------- ------------- -------------
7369 SMITHCLERK 5 3
7499 ALLENSALESMAN 5 0
7521 WARDSALESMAN 4 0
7566 JONESMANAGER 5 0
7654 MARTINSALESMAN 6 5
SELECT lpad(sal,10,'*') from emp;
LPAD(SAL,10,'*')
--------------------
*******800
******1600
******1250
******2975
SELECT rpad(sal,10,'*') from emp;
RPAD(SAL,10,'*')
--------------------
800*******
1600******
1250******
2975******
1250******
2850******
数字函数：
ROUND（列名｜表达式，n ): 四舍五入
TRUNC (列名｜表达式，n ) : 截断
MOD ( m , n ) : 求余，余数符号同第一个参数一样
CEIL：返回大于等 于指定参数的最小整数
FLOOR：返回小于等于指定参数的最大整数
select ceil(50.3) from dual 51
select floor(50.3) from dual 50
ROUND 函数：
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
SELECT ROUND(44.215,-1) from dual;
ROUND(44.215,-1)
----------------
40
SELECT ROUND(48.215,-1) from dual;
ROUND(48.215,-1)
----------------
50
SELECT ROUND(68.215,-1) from dual;
ROUND(68.215,-1)
----------------
70
TRUNC 函数：
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-2)
--------------- ------------- ----------------
45.92 45 0
SELECT TRUNC(445.923,-2) from dual;
TRUNC(445.923,-2)
-----------------
400
SELECT TRUNC(555.923,-2) from dual;
TRUNC(555.923,-2)
-----------------
500
MOD 函数：
SELECT empno,ename,sal,mod(sal,1000) from emp;
EMPNO ENAME SAL MOD(SAL,1000)
---------- ---------- ---------- -------------
7369 SMITH 800 800
7499 ALLEN 1600 600
7521 WARD 1250 250
7566 JONES 2975 975
7654 MARTIN 1250 250
7698 BLAKE 2850 850
7782 CLARK 2450 450
7788 SCOTT 3000 0
7839 KING 5000 0
7844 TURNER 1500 500
7876 ADAMS 1100 100
EMPNO ENAME SAL MOD(SAL,1000)
---------- ---------- ---------- -------------
7900 JAMES 950 950
7902 FORD 3000 0
7934 MILLER 1300 300
日期
Oracle 内部使用数字存储日期: 世纪,年,月,日,小时,分钟,秒。不论你输入的格式如何。
9i 开始 默认的日期格式是 DD-MON-RR，之前是 DD-MON-YY
可以只指定年的后两位在20世纪存放21世纪的日期。
同样可以在21世纪存放20世纪的日期。
可输入的有效日期是从公元前4712年1月1日到9999年12月31日。
函数SYSDATE 返回:
日期
时间
日期的数学运算
在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
可以用数字除24来向日期中加上或减去小时。
select ename,(sysdate-HIREDATE)/7 as weeks from scott.emp;
ENAME WEEKS
---------- ----------
SMITH 1494.36653
ALLEN 1485.08081
WARD 1484.7951
JONES 1479.22367
MARTIN 1453.65224
BLAKE 1475.08081
CLARK 1469.50938
select ename,(sysdate-HIREDATE)/365 as weeks from scott.emp;
ENAME WEEKS
---------- ----------
SMITH 28.659085
ALLEN 28.4810029
WARD 28.4755234
JONES 28.3686741
MARTIN 27.8782631
BLAKE 28.289222
CLARK 28.1823727
SCOTT 22.319359
日期函数:
MONTHS_BETWEEN（日期1，日期2） 两个日期相差的月数
ADD_MONTHS （日期，n) 向指定日期中加上若干月数后的日期
NEXT_DAY (日期，字符串） 指定日期的下一个星期几是什么日期
LAST_DAY（日期） 本月的最后一天
ROUND 日期四舍五入
TRUNC 日期截断
EXTRACT 从指定日期中取得年、月、日
months_between('1998-11-20','1998-03-21') 7.96774194
months_between('1998-11-20','1999-03-21') -4.0322581
add_months('1998-11-23',5) 1999-04-23
next_day('2009-12-10',3) 2009-12-15 下个星期二是什么时候
last_day('2009-12-10') 2009-12-31
round(sysdate,'month') 2009-12-01
round(sysdate,'year') 2010-01-01
extract(day from sysdate) 10
extract(month from sysdate) 12
extract(year from sysdate) 2009
转换函数：
数据类型转换，分为隐式转换和显式转换，隐式转换通常在赋值语句和表达式中由oracle自动完成，
显示转换能常需要调专专门的转换函数来完成。总体来讲是三类数据的转换：字符、数字、日期。
赋值语句中，oracle能自动完成以下隐式转换：
varchar2 or char －＞number
varchar2 or char －＞date
number －＞ varchar2
date －＞ varchar2
在表达式中，oracle服务器自动完成以下转换：
varchar2 or char －＞ number
varchar2 or char －＞ date
注：字符型向数据值转换时，要保证该字符型数据为有效的数字，字符型向日期转换时，要保证该字符为有效的日期，否则转换不能成功。
尽管隐式转换是可用的，但建议大家使用显示转换以确保SQL语句的可靠性。因为用隐式转换写出的SQL语句可读性较差，并难以理解。
并随着ORACLE版的更新很可能会改变原有的一些隐式转换规则，这样在程序的移植上会遇到不必要的麻烦。
显式数据类型转换：
为了完成不同数据类型之间的转换，ORACLE提供了3 种常用的转换函数： TO_CHAR、TO_NUMBER、TO_DATE。
TO_CHAR (date|number [,'fmt']);
作用：把日期或数值数据按照模式 fmt 转换成变长字符串。
TO_CHAR 函数对日期的转换
TO_CHAR(date, 'format_model')
格式:
必须包含在单引号中而且大小写敏感。
可以包含任意的有效的日期格式。
可以使用 fm 去掉多余的空格或者前导零。
与日期值用逗号隔开。
日期格式元素：
YYYY 数字年份
YEAR 英文年份
MM 数字月
MONTH 英文月
MON 英文月缩写
D 一周中的星期几。用 1－7 表示，星期一为 1
DD 数字日（月中的第几天，1－31 表示）
DDD 年中的第几天，1－365 表示
DY 星期几的英文缩写，3 个字符表示，大小写敏感。
DAY 星期几的英文全写，9 位字符表示，不足 9 位用空格填充，大小写敏感。
HH、HH12 小时（12 小时制）
HH24 小时（24 小时制）
MI 分钟
SS 秒
select ename,to_char(hiredate,'fmDD Month yyyy') from emp;----->fm可以去除0
ENAME TO_CHAR(HIREDA
---------- --------------
SMITH 17 12月 1980
ALLEN 20 2月 1981
WARD 22 2月 1981
JONES 2 4月 1981
SQL> select to_char(sysdate,'YYYY-MM-DD DAY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDDAY
------------------------------
2010-04-01 THURSDAY 11:10:45
RR日期格式与YY日期格式（以下年份均指两位缩写时的情况）：
1、如果当前年份的最后两位为 0 －49，并且指定年份的最后两位也为 0－49，
则返回的日期在本世纪（这一点RR与YY相同）。
例：如果当前年为 2002，01－11－08，则表示 2008 年。
2、如果当前年份的最后两位为 0－49，指定年份的最后两位为 50－99 ，
YY则返回上一世纪年份。
例：如当前年为 2002，01－11－98，RR则表示为 1998 年
而YY则表示为了 2098 年
3、如果当前年份的最后两位为 50－99，指定年份的最后两位为 0－49 ，
则返回下一世纪年份。
例：当前年为 1999 ，01－10－08 ，
RR表示为 2008 年
而YY 表示为 1908 年）
4、如果当前年份的最后两位为 50－99，
指定年份的最后两位为 50－99 ，则返回本世纪年份。
例：当前年为 1999 ，01－10－98，
RR表示为 1998 年，（YY相同也为 1998 年）
总结：YY不作处理，表示本世纪的年份缩写
TO_CHAR 函数对数字的转换
TO_CHAR(number, 'format_model')
下面是在TO_CHAR 函数中经常使用的几种格式:
9 一位 数字
0 显示前导字符零
$ 显示美元符
L 显示本地货币符号
. 小数点
, 千位符
select to_char(sal,'$99,999.00') salary from emp;
SALARY
-----------
$800.00
$1,600.00
$1,250.00
$2,975.00
$1,250.00
$2,850.00
$2,450.00
$3,000.00
$5,000.00
$1,500.00
$1,100.00
TO_NUMBER ( CHAR | VARCHAR2 string [,format [,nlsparams] ] )
作用：把一个 CHAR或VARCHAR2 类型的字符串，转换成数值。
SQL> select to_number('$123456','$99999999.00') from dual;
TO_NUMBER('$123456','$99999999.00')
-----------------------------------
123456
TO_DATE(string [, ' fmt ' [ , nlsparams]]);
作用：把一个表示日期的CHAR、VARCHAR2字符串按照日期模式 fmt 转换成日期，如果省略 fmt ，
那么使用oracle缺省的日期格式，nlsparams 指定返回日期所使用的语言。
SQL> select to_date('1999-08-21','YYYY-MM-DD') from dual;
TO_DATE('
---------
21-AUG-99
嵌套函数
单行函数可以嵌套。
嵌套函数的执行顺序是由内到外。
通用函数:
这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2)
expr1不为空返回expr1,为空返回expr2
NVL2 (expr1, expr2, expr3)
expr1不为空返回expr2,为空返回expr3
NULLIF (expr1, expr2)
expr1与expr2不相等返回expr1，相等返回为空
COALESCE (expr1, expr2, ..., exprn)
expr1为空，返回expr2，expr2为空返回expr3......
返回第一个不为空的表达式，如果都为空，则返回为空
NVL 函数
将空值转换成一个已知的值：
可以使用的数据类型有日期、字符、数字。
函数的一般形式:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
SQL> SELECT ename,sal,nvl(COMM,0),nvl(HIREDATE,'17-DEC-80') from emp;
ENAME SAL NVL(COMM,0) NVL(HIREDATE
---------- ---------- ----------- ------------
SMITH 800 0 17-DEC-80
ALLEN 1600 300 20-FEB-81
WARD 1250 500 22-FEB-81
JONES 2975 0 02-APR-81
MARTIN 1250 1400 28-SEP-81
BLAKE 2850 0 01-MAY-81
CLARK 2450 0 09-JUN-81
SCOTT 3000 0 19-APR-87
使用 NVL2 函数:
SQL> select ename,comm,nvl2(comm,'sal+comm','sal') income from emp;
ENAME COMM INCOME
---------- ---------- --------
SMITH sal
ALLEN 300 sal+comm
WARD 500 sal+comm
JONES sal
MARTIN 1400 sal+comm
BLAKE sal
CLARK sal
SCOTT sal
KING sal
TURNER 0 sal+comm
ADAMS sal
使用 NULLIF 函数:
返回类型
返回类型与第一个 expression 相同。
如果两个表达式不相等，NULLIF 返回第一个 expression 的值。如果相等，NULLIF 返回第一个 expression 类型的空值。
SQL> select ename,length(ename) ,job,length(job),nullif(length(ename),length(job)) result from emp;
ENAME LENGTH(ENAME) JOB LENGTH(JOB) RESULT
---------- ------------- --------- ----------- ----------
SMITH 5 CLERK 5
ALLEN 5 SALESMAN 8 5
WARD 4 SALESMAN 8 4
JONES 5 MANAGER 7 5
MARTIN 6 SALESMAN 8 6
BLAKE 5 MANAGER 7 5
CLARK 5 MANAGER 7 5
SCOTT 5 ANALYST 7 5
KING 4 PRESIDENT 9 4
TURNER 6 SALESMAN 8 6
ADAMS 5 CLERK 5
ENAME LENGTH(ENAME) JOB LENGTH(JOB) RESULT
---------- ------------- --------- ----------- ----------
JAMES 5 CLERK 5
FORD 4 ANALYST 7 4
MILLER 6 CLERK 5 6
使用 COALESCE 函数:
COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
如果第一个表达式费空,则返回这个表达式，对其他的参数进行COALESCE 。
SQL> select sal ,comm,deptno,empno,coalesce(sal,comm,deptno,empno) from emp;
SAL COMM DEPTNO EMPNO COALESCE(SAL,COMM,DEPTNO,EMPNO)
---------- ---------- ---------- ---------- -------------------------------
20 7369 20
300 30 7499 300
条件表达式
在 SQL 语句中使用IF-THEN-ELSE 逻辑。
使用两种方法:
– CASE 表达式
– DECODE 函数
CASE 表达式:
CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
select ename,job,sal ,
case job
when 'CLERK'
then 1.1*sal
when 'SALESMAN'
then 1.2*sal
else sal
end "re" from emp;
ENAME JOB SAL re
---------- --------- ---------- ----------
SMITH CLERK 800 880
ALLEN SALESMAN 1600 1920
WARD SALESMAN 1250 1500
JONES MANAGER 2975 2975
MARTIN SALESMAN 1250 1500
例：加工资 CLERK 加20% SALESMAN加15% 其它加 10%
case job
when 'CLERK' then
DECODE 函数:
DECODE(col|expression, search1, result1
[, search2, result2,...,] [, default])
当条件1满足时，返回结果1，当条件2满足时，返回结果2，当条件3满足时，返回结果3，
所有条件都不满足，返回default
SQL> select ename,job,sal ,
decode(job,'CLERK',1.1*sal,'SALESMAN',1.2*sal,sal) re from emp;
ENAME JOB SAL RE
---------- --------- ---------- ----------
SMITH CLERK 800 880
ALLEN SALESMAN 1600 1920
WARD SALESMAN 1250 1500
JONES MANAGER 2975 2975
MARTIN SALESMAN 1250 1500
BLAKE MANAGER 2850 2850
CLARK MANAGER 2450 2450
SCOTT ANALYST 3000 3000
KING PRESIDENT 5000 5000
TURNER SALESMAN 1500 1800
ADAMS CLERK 1100 1210
例：统计scott.emp中每个季度的入取人数
SQL> select sum(decode(to_char(hiredate,'fmmm'),1,1,2,1,3,1,0)) as Q1,
sum (decode(to_char(hiredate,'fmmm'),4,1,5,1,6,1,0)) as Q2,
sum(decode(to_char(hiredate,'fmmm'),7,1,8,1,9,1,0)) as Q3,
sum(decode(to_char(hiredate,'fmmm'),10,1,11,1,12,1,0)) as Q4
from scott.emp
例：decode的行列转换，用两种格式显示1980 ～1983年之间的每年的入职人数。
SQL> select to_char(hiredate,'yyyy') as year,count(empno) as empcount
from emp where to_char(hiredate,'yyyy') between 1980 and 1982
group by to_char(hiredate,'yyyy') order by to_char(hiredate,'yyyy')
YEAR EMPCOUNT
---- ----------
1980 1
1981 10
1982 1
SQL> select sum(decode(to_char(hiredate,'yyyy'),1980,1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),1981,1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),1982,1,0)) "1982" from emp
1980 1981 1982
---------- ---------- ----------
1 10 1
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21175589/viewspace-755634/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21175589/viewspace-755634/
扫一扫
697
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
