数值型函数

abs:求绝对值函数,如:abs(?5) 5

sqrt:求平方根函数,如:sqrt(2) 1.41421356

power:求幂函数,如:power(2,3) 8

cos:求余弦三角函数,如:cos(3.14159) ?1

mod:求除法余数,如:mod(1600, 300) 100

ceil:求大于等于某数的最小整儿,如:ceil(2.35) 3

floor:求小于等于某数的最大整儿,如:floor(2.35) 2

round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9

round(45.923, 0),结果,46

round(45.923, ?1),结果,50

trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9

trunc(45.923),结果,45

trunc(45.923, ?1),结果, 40

使用数值型函数练习。

使用求绝对值函数abs。

Sql代码

SELECT abs(?5) FROM dual;

SELECT abs(?5) FROM dual;

执行结果:

ABS(?5)

---------------

5

说明:求?5的绝对值,结果为5。

字符型函数

ascii:获得字符的ASCII码,如:Ascii('A') 65

chr:返回与ASCII码响应的字符,如:Chr(65) A

lower:将字符串转换成小写,如:lower ('SQL Course') sql course

upper:将字符串转换成大写,如:upper('SQL Course') SQL COURSE

initcap:将字符串转换成每个单词以大写开头,如:initcap('SQL course') Sql Course

concat:毗连两个字符串 concat('SQL', ' Course') SQL Course

substr:给出起始位置和长度,返回子字符串,如:substr('String',1,3) Str

length:求字符串的长度 length('Wellcom') 7

instr:给出起始位置和出现的回数,求子字符串在字符串中出现的位置,如:instr('String', 'r',1,1) 3

lpad:用字符填充字符串左侧到指定长度,如:lpad('Hi',10,'-') --------Hi

rpad:用字符填充字符串右侧到指定长度,如:rpad('Hi',10,'-') Hi--------

trim:在一个字符串中去除另一个字符串,如:trim('S' FROM 'SSMITH') MITH

replace:用一个字符串替换另一个字符串中的子字符串,如:replace('ABC', 'B', 'D') ADC

要是不知道表的字段内容是大写还是小写,可以转换后比较。

输入并执行查询:

Sql代码

SELECTempno, ename, deptnoFROM emp

WHERElower(ename) ='blake';

SELECTempno, ename, deptno FROM empWHERElower(ename) ='blake';

结果为:

Sql代码

EMPNO ENAMEDEPTNO

--------------- ---------- ------------------------

7698 BLAKE30

EMPNO ENAMEDEPTNO--------------- ---------- ------------------------7698 BLAKE30

说明:该查询将表中的雇员名转换成小写,与小写的blake进行比较。

日期函数

months_between:返回两个日期间的

月份,如:months_between ('04-11月-05','11-1月-01'),结果,57.7741935

add_months:返回把月份数加到日期上的新日期,如:add_months('06-2月-03',1),结果,06-3月-03

add_months('06-2月-03',-1),结果,06-1月-03

next_day:返回指定日期后的礼拜对应的新日期,如:next_day('06-2月-03','礼拜一'),结果,10-2月-03

last_day:返回指定日期地点的月的最后一天,如:last_day('06-2月-03'),结果,28-2月-03

round:按指定格式对日期进行四舍五入,如:round(to_date('13-2月-03'),'YEAR'),结果,01-1月-03

round(to_date('13-2月-03'),'MONTH'),结果,01-2月-03

round(to_date('13-2月-03'),'DAY'),结果,16-2月-03

(按周四舍五入)

trunc:对日期按指定方式进行截断,如:trunc(to_date('06-2月-03'),'YEAR'),结果,01-1月-03

trunc(to_date('06-2月-03'),'MONTH'),结果,01-2月-03

trunc(to_date('06-2月-03'),'DAY'),结果,02-2月-03

(按周截断)

返回2003年2月的最后一天。

输入并执行查询:

Sql代码

SELECT last_day('08-2月-03') FROM dual;

SELECT last_day('08-2月-03') FROM dual;

返回结果为:

Sql代码

LAST_DAY('

----------------

28-2月-03

LAST_DAY('----------------28-2月-03

说明:该函数给定参数为某月份的任意一天,返回时间为该月份的最后一天。本例中,参数为03年2月8号,返回日期为03年2月28日,是该月的最后一天。

假定时下的系统日期是2003年2月6日,求再过1000天的日期。

输入并执行查询:

Sql代码

SELECT sysdate+1000 AS "NEW DATE" FROM dual;

SELECT sysdate+1000 AS "NEW DATE" FROM dual;

返回结果为:

Sql代码

NEW DATE

----------------

04-11月-05

NEW DATE----------------04-11月-05

说明:该查询使用到了日期的加法运算,求经过一定上天的安排后的新日期。

假定时下的系统日期是2003年2月6日,显示部门10雇员的雇佣上天的安排。

输入并执行查询:

Sql代码

SELECT ename, round(sysdate-hiredate) DAYS

FROMemp

WHEREdeptno = 10;

SELECT ename, round(sysdate-hiredate) DAYSFROMempWHEREdeptno = 10;

返回结果为:

ENAMEDAYS

--------------- ---------------------

CLARK7913

KING7752

MILLER7685

说明:该查询使用日期的减法运算求两个日期的相差上天的安排。用round函数对上天的安排进行四舍五入。

转换函数

Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER

To_char:转换成字符串类型,如:To_char(1234.5, '$9999.9'),结果:$1234.5

To_date:转换成日期类型,如:To_date('1980-01-01', 'yyyy-mm-dd'),结果:01-1月-80

To_number:转换成数值类型,如:To_number('1234.5'),结果:1234.5

自动类型转换

Oracle可以自动根据具体环境进行如下的转换:

* 字符串到数值。

* 字符串到日期。

* 数值到字符串。

* 日期到字符串。

以下是自动转换的训练。

自动转换字符型数据到数值型。

输入并执行查询:

Sql代码

SELECT '12.5'+11 FROM dual;

SELECT '12.5'+11 FROM dual;

执行结果为:

Sql代码

'12.5'+11

------------

23.5

'12.5'+11------------23.5

说明:在本训练中,因为出现+运算符,说明进行的是算术运算,所以字符串'12.5'被自动转换成数值12.5,然后参加运算。

自动转换数值型数据到字符型。

执行以下查询:

Sql代码

SELECT '12.5'||11 FROM dual;

SELECT '12.5'||11 FROM dual;

结果为:

Sql代码

'12.5'

------

12.511

'12.5'------12.511

说明:在本训练中,因为出现||运算符,说明进行的是字符串毗连运算,数值11被自动转换成字符串'11',然后参加运算。

日期类型转换

将日期型转换成字符串时,可以按新的格式显示。

如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。

主要的日期格式字符的含义

AM、PM:上午、下午,如:08 AM

D:数码表示的礼拜(1～7),如:1,2,3,4,5,6,7

DD:数码表示月中的日期(1～31),如:1,2,3,…,31

MM:两位数的月份,如:01,02,…,12

Y、YY、YYY、YYYY 年份的后几位,如:3,03,003,2003

RR:解决Y2K问题的年度转换

DY:简写的礼拜名,如:MON,TUE,FRI,…

DAY:全拼的礼拜名,如:MONDAY,TUESDAY,…

MON:简写的月份名,如:JAN,FEB,MAR,…

MONTH:全拼的月份名,如:JANUARY,FEBRUARY,…

HH、HH12:12小时制的小时(1～12),如:1,2,3,…,12

HH24:24小时制的小时(0～23),如:0,1,2,…,23

MI:分(0～59),如:0,1,2,…,59

SS:秒(0～59),如:0,1,2,…,59

,./-;: 原样显示的标点符号

'TEXT':引号中的文本原样显示,如:TEXT

将日期转换成带时间和礼拜的字符串并显示。

执行以下查询:

Sql代码

SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;

SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;

结果为:

Sql代码

TO_CHAR(SYSDATE,'YYYY-MM-DD HH24

----------------------------------------------------------

2004-02-07 15:44:48 下午 礼拜六

TO_CHAR(SYSDATE,'YYYY-MM-DD HH24----------------------------------------------------------2004-02-07 15:44:48 下午 礼拜六

说明:该语句中的第一个参数表示要转换的日期,第二个参数是格式字符串,表示转换后的格式,结果类型为字符串。“YYYY”为4位的年份,“MM”为两位的月份,“DD”为两位的日期,“HH24”表示显示24小时制的小时,“MI”表示显示分钟,“SS”表示显示秒,“AM”表示显示上午或下午(本例中为下午),“DY”表示显示礼拜。“-”、“:”和空格原样显示,用于分割日期和时间。转换出来的系统时间为:2004年2月7日(礼拜六)下午15点44分48秒。

还可以按其他的格式显示。以下查询中插入中文的年月日,其中原样显示部分区分于外层的单引号,需要用双引号导致。

将日期显示转换成中文的年月日。

输入并执行查询:

Sql代码

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;

执行结果为:

Sql代码

TO_CHAR(SYSDAT

-------------------------

2003年11月18日

TO_CHAR(SYSDAT-------------------------2003年11月18日

说明:双引号中的中书契“年”、“月”、“日”原样显示,单引号为字符串的界定标记,区分于双引号,不能混淆。

时间显示的大小写。

步骤1:执行以下查询:

Sql代码

SELECT SYSDATE,to_char(SYSDATE,'yyyysp') FROM dual;

SELECT SYSDATE,to_char(SYSDATE,'yyyysp') FROM dual;

结果为:

Sql代码

SYSDATETO_CHAR(SYSDATE,'YYYYSP')

------------- ----------------------------------------------

07-2月 -04two thousand four

SYSDATETO_CHAR(SYSDATE,'YYYYSP')------------- ----------------------------------------------07-2月 -04two thousand four

步骤2:执行以下查询:

Sql代码

SELECT to_char(SYSDATE,'Yyyysp') FROM dual;

SELECT to_char(SYSDATE,'Yyyysp') FROM dual;

结果为:

Sql代码

SYSDATETO_CHAR(SYSDATE,'YYYYSP')

-------------- -----------------------------------------------

Two Thousand Four

SYSDATETO_CHAR(SYSDATE,'YYYYSP')-------------- -----------------------------------------------Two Thousand Four

数值转换符:

9:代表一位数码,要是是正数,前面是空格,要是是负数,前面是?号,如:9999

0:代表一位数码,在响应的位置上要是没有数码则出现0 0000

,:逗号,用作组分隔符,如:99,999

.:小数点,分隔整儿和小数,如:999.9

$:$钱币符号,如:$999.9

L:本地钱币符号,如:L999.99

FM:去掉前后的空格,如:FM999.99

EEEE:科学计数法,如:9.9EEEE

S:负数符号?放在开头,如:S999.9

将数值转换成字符串并按新格式显示。

执行以下查询:

Sql代码

SELECT TO_CHAR(sal,'$99,999') SALARY FROM emp

WHERE ename = 'SCOTT';

SELECT TO_CHAR(sal,'$99,999') SALARY FROM empWHERE ename = 'SCOTT';

结果为:

Sql代码

SALARY

------------

$4,000

SALARY------------$4,000

说明:格式字符串中“$”表示转换结果前面添加$。“9”表示一位数码,“99,999”表示结果可以显示为5位的数码。“,”表示在响应的位置上添加逗号。要是实际数值位数不足5位,则只显示实际位数,如4000实际位数为4位,则只显示4位。要是实际位数超过5位,则会填充为#号。

其他函数

nvl:空值转换函数,如:nvl(null, '空'),结果:空

decode:实现分支功能,如:decode(1,1, '男', 2, '女'),结果:男

userenv:返回环境信息,如:userenv('LANGUAGE'),结果:SIMPLIFIED CHINESE_CHINA.ZHS16GBK

greatest:返回参数的最大值,如:greatest(20,35,18,9),结果:35

least:返回参数的最小值,如:least(20,35,18,9),结果:9

空值的转换

要是对空值灭茬不能很好的处理,就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是灭茬。最典型的例子是,在查询雇员表时,将工资sal字段和津贴字段comm进行相加,要是津贴为空,则相加结果也为空,这样容易导致误解。

使用nvl函数,可以转换灭茬为实际值。该函数判断字段的内容,要是不为空,返回原值;为空,则返回给定的值。

如下3个函数,分别用新内容代替字段的空值:

nvl(comm, 0):用0代替空的Comm值。

nvl(hiredate, '01-1月-97'):用1997年1月1日代替空的雇佣日期。

nvl(job, '无'):用“无”代替空的职务。

使用nvl函数转换空值。

执行以下查询:

Sql代码

SELECTename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROMemp;

SELECT ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROMemp;

结果为:

Sql代码

ENAMENVL(JOB,'N NVL(HIREDA NVL(COMM,0)

---------------- ------------------- ------------------ -------------------

SMITHCLERK17-12月-800

ALLENSALESMAN20-2月 -81300

ENAMENVL(JOB,'N NVL(HIREDA NVL(COMM,0)---------------- ------------------- ------------------ -------------------SMITHCLERK17-12月-800ALLENSALESMAN20-2月 -81300

说明:本例中,空日期将显示为“01-1月-97”,空职务显示为“无”,空津贴将显示为0。

最大、最小值函数

greatest返回参数据排列表中的最大值,least返回参数据排列表中的最小值。

这两个函数的参数是一个表达式列表,按表达式列表中的第一个表达式的类型对求值后的表达式求得最大或最小值。对字符的比较按ASCII码的顺序进行。要是表达式中有灭茬,则返回灭茬。

比较字符串的大小,返回最大值。

执行以下查询:

Sql代码

SELECTgreatest('ABC','ABD','abc', 'abd') FROM dual;

SELECT greatest('ABC','ABD','abc', 'abd') FROM dual;

执行结果为:

Sql代码

GRE

------

abd

GRE------abd

说明:在上述四个字符串中,大小瓜葛为abd>abc>ABD>ABC。在ASCII码表中,排在后边的字符大,小写字母排在大写字母然后。字符串的比较原则是,先比较第一位,要是相同,则接续比较第二位,依此类推,直到出现大小瓜葛。