1.单行函数
function_name(column|expression,[arg1,arg2,...])
-接收参数并返回一个结果
-每行返回一个结果
-可以改变数据类型
-能被嵌套
-可以用于SELECT,WHERE和ORDER BY子句
-包括:字符函数,数值函数,日期函数,转换函数,通用函数
2.字符函数
-包括:大小写转换函数,字符处理函数
1)大小写转换函数
函数 结果
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
例:
SELECT empno,ename,deptno
FROM emp
WHERE ename=UPPER('blake');
结果:
EMPNO ENAME DEPTNO
7698 BLAKE 30
2)字符串处理函数
函数 结果
CONCAT('Good','String') GoodString
SUBSTR('String',1,3) Str
LENGTH('String') 6
INSTR('String','r') 3
LPAD(sal,10,'*') ******5000
RPAD(sal,10,'*') 5000******
TRIM('S' FROM 'SSMITH') MITH
例:
SELECT ename,CONCAT(ename,job),LENGTH(ename),INSTR(ename,'A')
FROM emp
WHERE SUBSTR(job,1,5)='SALES';
结果:
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')
ALLEN ALLENSALESMAN 5 1
WARD WARDSALESMAN 4 2
MARTIN MARTINSALESMAN 6 2
TURNER TURNERSALESMAN 6 0
3.数值函数
1) ROUND:四舍五入到指定的小数位
-ROUND(45.926,2) 45.93
例:
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
2)TRUNC:截取到指定的小数位
-TURNC(45.926) 45.92
例:
SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,0)
FROM dual;
结果:
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
45.92 45 40
3)MOD:取余数
MOD(1600,300) 100
例:
SELECT ename,sal,comm,MOD(sal,comm)
FROM emp
WHERE job='SALESMAN';
结果:
ENAME SAL COMM MOD(SAL,COMM)
ALLEN 1600 300 100
WARD 1250 500 250
MARTIN 1250 1400 1250
TURNER 1500 0 1500
4.日期函数
-Orale是以一种内部的数值形式存储日期的,即:世纪、年、月、日、小时、分、秒
-默认日期形式是:DD-MON-RR
-SYSDATE是一个可以返回当前系统日期和时间的函数
-DUAL是一个虚拟表用于查看SYSDATE
-对一个日期型数据加上或减去一个数可以得到一个新的日期型数据
-两个日期型数据相减得到这两个日期的间隔天数
-如要以小时相加则必须用小时数除以24得到的数据进行相加
例:
SELECT ename,(SYSDATE-hiredate)/7 WEEKS
FROM emp
WHERE deptno=10;
结果:
ENAME WEEKS
CLARK 1434.49271
KING 1411.49271
MILLER 1401.92128
5.RR日期格式
| 如果指定两位数年份是:
| 0-49 | 50-99
如果当前年份| 0-49 | 返回日期是当前世纪的日期 | 返回日期是上个世纪的日期
的两位数是:| 50-99| 返回日期是下个世纪的日期 | 返回日期是当前世纪的日期
例:
当前年份 指定的日期 RR格式 YY格式
1995 27-OCT-95 1995 1995
1995 27-OCT-17 2017 1917
2001 27-OCT-17 2017 2017
2001 27-OCT-95 1995 2095
6.日期函数
1)MONTHS_BETWEEN:两个日期之间间隔多少个月
MONTHS_BETWEEN('01-9月-95','11-9月-94')
结果:11.6774194
2)ADD_MONTHS:向一个日期数据加一定的月份
ADD_MONTHS('11-1月-94',6)
结果:11-7月 -94
3)NEXT_DAY:某个指定日期之后的一周内某天
NEXT_DAY('01-8月-07',1)
结果:05-8月 -07
注:第二个参数表示下周的第几天,周日是第一天
4)LAST_DAY:返回某月的最后一天
LAST_DAY('01-9月-95')
结果:30-9月 -95
5)ROUND:对日期进行四舍五入
ROUND('25-7月-95','MONTH')
结果:01-8月-95
ROUND('25-7月-95','YEAR')
结果:01-1月-96
6)TRUNC:对日期进行截取
TRUNC('25-7月-95','MONTH')
结果:01-7月-95
TRUNC('25-7月-95','YEAR')
结果:01-1月-95
例:
SELECT empno,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) TENURE,
ADD_MONTHS(hiredate,6) REVIEW,
NEXT_DAY(hiredate,2),LAST_DAY(hiredate)
FROM emp
WHERE MONTHS_BETWEEN(SYSDATE,hiredate)<250;
7.转换函数
-隐式数据类型转换:
赋值语句中,oralce服务器自动完成以下转换
从 到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
-显式数据类型转换:
1)TO_CHAR用于日期型:TO_CHAR(date,'fmt')
日期格式模型:
-必须用单引号引起来
-可以包含任何有效的日期元素
-使用逗号与日期型数据分隔开
日期格式模型的元素:
YYYY----完整的年份数字表示
YEAR----年份的英文表示
MM------用两位数字来表示月份
MONTH---月份的英文表示
DY------用3个英文字符所写来表示星期几
DAY-----星期几完整的英文表示
例1:用时间元素格式化日期的时间部分
HH24:MI:SS:AM 15:45:32:PM
例2:通过使用双引号可以添加字符串
DD "of" MONTH 12 of 8月
例3:
SELECT ename,TO_CHAR(hiredate,'yyyy-mm-dd') HIREDATE
FROM emp
结果:
ENAME HIREDATE
SMITH 1980-12-17
ALLEN 1981-02-20
2)TO_CHAR用于数值型:TO_CHAR(number,'fmt')
通过在TO_CHAR中使用以下形式可以把数值型数据转换成变长的字符串
9------一位数字
0------显示前导零
$------显示美元符号
L------显示本地货币号
.------显示小数点
,------显示千位符
例:
SELECT TO_CHAR(sal,'$99,999') SALARY
FROM emp
WHERE ename='SCOTT';
结果:
SALARY
$3,000
3)TO_NUMBER和TO_DATE函数
-TO_NUMBER将一个字符串转换成数值型数据:TO_NUMBER(char)
-TO_DATE将一个字符串转换成日期型数据:TO_DATE(char[,'fmt'])
8.NVL函数
1)将NULL转换成为一个实际的值
-数据类型可以是date、character、number
2)数据类型必须匹配
NVL(comm,0)
NVL(hiredate,'01-7月-97')
NVL(job,'No Job Yet')
例:
SELECT ename,sal,comm,(sal*12)+NVL(comm,0)
FROM emp;
结果:
ENAME SAL COMM (SAL*12)+NVL(COMM,0)
SMITH 800 9600
ALLEN 1600 300 19500
WARD 1250 500 15500
9.DECODE函数
使用DECODE函数更便于完成诸如CASE或IF-THEN-ELSE语句的条件查询:
DECODE(col/expression,search1,result1
[,search2,result2,...,]
[,default])
例1:
SELECT job,sal,DECODE(job,'ANALYST',SAL*1.1,
'CLERK',SAL*1.15,
'MANAGER',SAL*1.20,
SAL) REVISED_SALARY
FROM emp;
结果:
JOB SAL REVISED_SALARY
CLERK 800 920
SALESMAN 1250 1250
MANAGER 2975 3570
例2:显示部门编号为30的每个雇员相应的税率
SELECT ename,sal,DECODE(TRUNC(sal/1000,0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) TAX_RATE
FROM emp
WHERE deptno=30;
结果:
ENAME SAL TAX_RATE
ALLEN 1600 .09
WARD 1250 .09
MARTIN 1250 .09
BLAKE 2850 .2
TURNER 1500 .09
JAMES 950 0
10.函数的嵌套:
例:
SELECT ename, NVL(TO_CHAR(mgr),'No Manager')
FROM emp
WHERE mgr IS NULL;
结果:
ENAME NVL(TO_CHAR(MGR),'NOMANAGER')
KING No Manager
练习
1.查询并显示雇员名。要求首字母大写,其他字母小写,显示名字的长度,只选择雇员名开始字母是J、A或M的雇员。用雇员名排序结果
SELECT INITCAP(ename),LENGTH(ename)
FROM emp
WHERE ename LIKE 'J%'
OR ename LIKE 'A%'
OR ename LIKE 'M%';
2.查询所有雇员名和薪水。薪水格式化为15个字符长度,用$左填充,列标签SALARY
SELECT ename,LPAD(sal,15,'$') SALARY
FROM emp
3.显示姓名以N结尾的雇员
SELECT *
FROM emp
WHERE UPPER(ename) LIKE '%N';
4.查询当前日期,列标题显示为Date
SELECT TO_CHAR(SYSDATE,'DD-MONTH-YYYY') "Date"
FROM dual;
5.对每一个雇员,显示其名字,并计算从雇员受雇日期到今天的月数,列标签MONTHS_WORKED。按受雇月数排序结果,四舍五入月数到最靠近的整数月
SELECT ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) MONTHS_WORKED
FROM emp;
6.查询雇员名和佣金。若雇员没有佣金,显示"No Commission",列标签COMM
SELECT ename,decode(comm,
NULL,'No Commission',
comm) COMM
FROM emp;
或:
SELECT ename,NVL(to_char(comm),'No Commission') COMM
FROM emp;
7.显示雇员雇佣期满6个月后下一个星期五的日期。显示格式如02-4-1982,并按雇佣日期排序
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate,6),6),'DD-Month-YYYY')
FROM emp
ORDER BY hiredate;
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/luyangever/archive/2008/12/13/3510416.aspx