oracle简单学习总结（三）——函数

一、数值型函数

函 数	功 能	实 例	结 果
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) round(45.923, 0) round(45.923, −1)	45.9 46 50
trunc	按指定精度截断十进制数	trunc(45.923, 1) trunc(45.923) trunc(45.923, −1)	45.9 45 40

二、oracle中的dual表

2.1 理解

1、DUAL是 SYS用户的一个TABLE.

2、Dual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。

3、DUAL就是个一行一列的表， 该表和系统中的其他表一样，一样可以执行插入、更新、删除操作，还可以执行drop操作。但是不要去执行drop表的操作，否则会使系统不能用，数据库起不了，会报Database startup crashes with ORA-1092错误。

2.2 DUAL 能做什么

1、查找当天日期
SQL> select sysdate from dual;

2、查找当前日期的当月第一天
SQL> select trunc(sysdate,’MONTH’) from dual;

3、查找当前日期的当月最后一天
SQL> select trunc(last_day(sysdate)) from dual;

4、查看当前用户，可以在SQL Plus中执行下面语句
SQL> select user from dual;

5、获得当前系统时间
SQL> select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;

6、获得主机名
SQL> select SYS_CONTEXT(‘USERENV’,'TERMINAL’) from dual;

7、获得当前locale
SQL> select SYS_CONTEXT(‘USERENV’,'language’) from dual;

8、获得一个随机数
SQL> select dbms_random.random from dual;

9、查看当前日期、时间
SQL> select sysdate from dual;

10、获得序列your_sequence的下一个值
SQL> select your_sequence.nextval from dual;

11、获得序列your_sequence的当前值
SQL> select your_sequence.currval from dual;

12、可以用做计算器

参考网址：http://yesican.blog.51cto.com/700694/269814

http://www.itfarmer.com.cn/plsql/1093.html

三、字符型函数

函数名称	功 能	实 例	结 果
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

四、日期型函数

1、Oracle使用内部数字格式来保存时间和日期，包括世纪、年、月、日、小时、分、秒。缺省日期格式为DD-MON-YY，如“08-05月-03”代表2003年5月8日。
2、SYSDATE是返回系统日期和时间的虚列函数。

函 数	功 能	实 例	结 果
months_between	返回两个日期间的月份	months_between ('04-11 月-05','11-1 月 -01')	57.7741935
add_months	返回把月份数加到日期上的新日期	add_months('06-2 月-03',1) add_months('06-2 月-03',-1)	06-3 月-03 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') round(to_date('13-2 月-03'),'MONTH') round(to_date('13-2 月-03'),'DAY')	01-1 月-03 01-2 月-03 16-2 月-03 (按周四舍五入)
trunc	对日期按指定方式进行截断	trunc(to_date('06-2 月-03'),'YEAR') trunc(to_date('06-2 月-03'),'MONTH') trunc(to_date('06-2 月-03'),'DAY')	01-1 月-03 01-2 月-03 02-2 月-03 (按周截断

五、日期转换格式字符

代 码	代表的格式	例 子
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'	引号中的文本原样显示	TE

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

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

六、转换函数

 

函 数	功 能	实 例	结 果
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可以自动根据具体情况进行如下的转换：
（1） 字符串到数值。（2） 字符串到日期。（3） 数值到字符串。（4）日期到字符串。
sql: SELECT SYSDATE,to_char(SYSDATE,'YYyysp') FROM dual;

七、数值转换

 

代 码	代表的格式	例 子
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(123.45,'0000.00'), TO_CHAR(12345,'L9.9EEEE'), TO_CHAR(12345,'L9,9999') ,TO_CHAR(12345,'L9,9') FROM dual;

说明： 如果实际位数超过指定位数，则会填充为#号

八、其他常用函数

函 数	功 能	实 例	结 果
nvl	空值转换函数	nvl(null, '空')	空
decode	实现分支功能	decode(1,1, '男', 2, '女')	男
userenv	返回环境信息	userenv('LANGUAGE')	SIMPLIFIEDCHINESE_CHINA.ZHS16GBK
greatest	返回参数的最大值	greatest(20,35,18,9)	35
least	返回参数的最小值	least(20,35,18,9)	9

 

说明： 字符串的比较原则是，依次转换为ASCII码，先比较第一位，如果相同，则继续比较第二位，依此类推，直到出现大小关系

nvl 例：

nvl(comm, 0)：用0代替空的Comm值。
nvl(hiredate, '01-1月-97')：用1997年1月1日代替空的雇佣日期。

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

decode例：

sql: SELECT name,age,decode(age, '26', '年龄26','12','年龄12','年龄不大于10') FROM userinfor;

说明： 如果age字段的内容为26”则返回“ 年龄26”，如果是“12”则返回“ 年龄12”。如果不是“ 26” 、“ 12” 之一， 则返回“ 年龄不大于10”

userenv 例：

函数userenv返回用户环境信息字符串，该函数只有一个字符串类型的参数，参数的内容为如下之一的字符串，可以不区分大小写：
* ISDBA：判断会话用户的角色是否为SYSDBA，是则返回TRUE。
* INSTANCE：返回会话连接的INSTANCE标识符。
* LANGUAGE：返回语言、地区、数据库字符集信息。
* LANG：返回会话语言的ISO简称。
* TERMINAL：返回正在会话的终端或计算机的标识符。