Oracle数据类型与函数

Oracle数据类型

数据类型						说明
char		字符型，最大长度2000B，缺省长度为1B
nchar		基于NLS国家字符集的字符型，最大长度2000B，缺省为1字符
varchar2	变长字符型，最大长度4000B
nvarchar2	基于NLS国家字符集的字符型，其余同varchar2
varchar		同于varchar2
number(m,n)	数值型,m为总位数,n为小位数,总长度最大为38位
date		日期型,有效表数范围:公元前4712y-1m-1d到公元后4712y-12m-31d
long		变长字符型，最大长度2GB,不支持对字符串内容进行搜索
raw			变长二进制数据类型,最大长度2000B
long raw	变长二进制数据类型,最大长度2GB
blob		二进制大对象类型,最大长度4GB
clob		字符大对象类型,最大长度4GB
nclob		基于NLS国家字符集的字符,字符大对象类型,最大长度4GB
bfile		在数据库外部保存的大型二进制文件,大对象类型，最大长度4GB

 函数

Oracle函数分为单行函数和多行函数两大类

单行函数

• 操作数据项
• 接受参数并返回处理结果
• 对每一返回行起作用
• 可修改数据类型
• 可嵌套使用

单行函数分类

• 字符函数
• 数值函数
• 日期函数
• 转换函数
• 通用函数

字符函数(函数,功能,用法,返回结果)

字符大小写转换函数  

lower()	转换为小写		lower("John Smith")		john smith
upper()	转换为大写		upper("John Smith")		JOHN SIMTH
initcap()单词首字母大写	initcap("JOHN smith")	John Smith

 字符处理函数  

concat()	字符串连接		concat('Hello','World')		Hello World
subst()		截取字串		substr('HelloWorld',4,3)	loW
length()	返回字符串长度	  length('Hello world')		  11
instr()		定位字串		instr('Hello World','or')	8
lpad()		左侧填充		lpad('Smith',10,'*')		*****Smith
rpad()		右侧填充		rpad('Smith',10,'*')		Smith*****
trim()		过滤首尾空格	  trim('  Mr Smith   ')		   Mr Smith
replace()	替换			replace('Mr Tom','Mr','Mrs')  Mrs Tom

 数值函数（abs 返回值有误为3.14）

 

 日期类型

 关于日期类型

• Oracle内部以数字格式存储日期和时间信息:世纪,年,月,日,小时,分钟,秒
• 缺省的日期格式是 DD-MON-YY
• 可使用sysdate函数获取当前系统日期和时间

日期型数据的算术运算

• 日期型数据可以直接加或减一个数值,结果仍为日期
• 两个日期型数据可以相减,结果为二者相差多少天

日期函数

转换函数

数据类型转换包括隐含转换盒显式转换两种方式,建议使用现实转换，确保sql语句的可靠性.

日期------------------>字符串

to_char()函数可以将日期型数值转换为字符串形式

格式:

to_char(date)  //缺省转换为'dd-mm-yy'格式
to_char(date,'format_model') //转换为模式串指定的格式

 

select id,name,age,to_char(time,'yyyy-mm-dd') from table

 常用日期格式符

说明：除上述格式符外，日期模式串中还可以出现如下字符 ' - : ; / ' ,如要显示其他文本字符串则需使用双引号括起来,也可在模式串的开头使用"fm"标记以去掉数字前面的零.

字符串------->日期

to_date()函数可以将字符串转换为日期类型

to_date(char) //按缺省格式('dd-mm-yy')进行解析
to_date(char,'format_model')//按模式串指定的格式进行解析

insert into test values('Tom',to_date('2010-01-02','yyyy-mm-dd'))

 数字-------->字符串

to_char()函数可以将数字值转换为字符串形式

to_chat(number) 
to_char(number,'format_model')

select to_char(12345.678901) from tab;
select to_char(12345.678901,'$99,999.0000')from tab  --$12,345.6789

 

 

数字模式符

 

字符串---->数字

to_number()函数可以将字符串转换为数字值

to_number(char) 
to_number(char,'format_model')

select to_number('12345.678901')from tab; --12345.6789
select to_number('$12345.6789','$99,999.0000')from tab; --12345.6789

 通用函数

通用函数适用于任何类型数据(包括空值):

• nvl()
• nvl2()
• nullif()
• coalesce()
• case表达式
• decode()

NVL()函数

NVL()函数用于将空值null替换为指定的缺省值,适用于字符、数字、日期等类型数据.

NVL(exp1,exp2)

说明：如果表达式exp1的值为null，则返回exp2的值，否则返回exp1的值。

select id,name,age,time,nvl(time,sysdate) from tab;

NVL2()函数

NVL2()函数用于实现条件表达式功能

NVL2(exp1,exp2,exp3)

说明：如果表达式exp1的值不为null，则返回exp2的值，否则返回exp3的值

select id,name,age,time,nvl2(age,sysdate-time,name) from tab;

NULLIF()函数

nullif()函数用于数据等价性比较返回null或其中一个被比较的数值

nullif(exp1,exp2)

说明：如果表达式exp1与exp2的值相等则返回null,否则返回exp1的值

select name 原名,nullif(pen_name,name)笔名 from tab;

COALESCE()函数

coalesce()函数用于实现数据"接合"功能

coalesce(exp1,exp2,....)

说明：依次考察各参数表达式，遇到非null值即停止并返回该值

select id,name,age,bonus,wage,coalesce(wage+bonus,0)总收入 from tab;

CASE表达式

case表达式用于实现多路分治结构

case exp when comparision_exp1 then return_exp1
               [when comparision_exp2 then return_exp2
                when comparision_expn then return_expn
                else else_exp]
end 

select id,name,work,
             case department when 10 then '财务部'
                                         when 20 then '研发部'
                                         when 30 then '销售部'
                                          else '未知部门'
              end 部门
form tab

DECODE()函数

和case表达式类似,decode()函数也用于实现多路分支结构

decode(col | expression,search1,result1
                                      [,search2,result2,...,]
                                      [,default])

 

select id,name,age,
             decode(department,10,'财务部',
                                             20,'研发部',
                                              30,'销售部',
                                              '未知部门')
              部门
from tab;

 函数嵌套

单行函数可以嵌套使用,嵌套层次无限制

嵌套函数的执行顺序是由内到外

单行函数小结

使用系统提供的单行函数可实现如下功能:

• 对数据进行计算
• 控制数据的输出格式
• 设置/改变日期的显示格式
• 进行数据类型转换
• 使用NVL函数处理空值
• 实现if-then-else多路分支逻辑

分组函数

分组函数对一组数据进行运算，针对一组数据(多行记录)只返回一个结果,也称多行函数.

count()函数

count(*)返回组中总记录数目;

count(exp)返回表达式exp值非空的记录数目

count(distince(exp))返回表达式exp值不重复的，非空的记录数目

select count(*) from tab;
select count(name) from tab;

 GROUP BY 子句

group by 子句将表中数据分成若干小组，出现selcet列表中的字段，如果不是包含在组函数中，那么该字段必须同时在group by子句中出现,包含group by子句中的字段则不必须出现在selcet列表中。

HAVING子句

having子句用于过滤分组

selcet    id,work,avg(age)
from       tab
where    time>=to_date('2000-01-02','yyyy-mm-dd')
group by department,work
having  avg(age)>30
order by department,work

 分组函数嵌套

分组函数最多可嵌套两层

selcet   max(avg(age))
from table
group by department