Oracle数据类型及内置函数

1、Oracle的数据类型：

1.1字符数据类型

1.1.1定长字符串char(n)，n的单位为字节，缺省为1个字节(一个汉字为2字节)，n取值

范围为1到2000。

1.1.2变长字符串varchar2(n)，n的单位为字节，n取值范围为1到4000。

1.1.3固定长度Unicode字符集nchar(n)，n的单位为Unicode字符(一个Unicode占2字节

即16位)，n的最大值为2000。

1.1.4变长Unicode字符集nvarchar2(n)，n的单位为Unicode字符，n的最大值为4000。

附加说明：若char或nchar类型的字符串未填满，则Oracle会用空格填满；varchar2或

nvarchar2类型中存放的字符数为实际赋予的字符串的字符数，即使未填满，后面也不会

加空格。 char和nchar空间占用大但时间效率高，varchar2和nvarchar2空间占用小但时

间效率低。

1.2数值类型：(数字类型是可以不进行数据转换直接参与运算的数据)

1.2.1可变长的数值类型number[(m[,n])]，m为有效数字(数字中从非零开始的数字)的位数，

范围为1到38；n为小数点后的位数，范围为-84到127，负数表示精确到小数点前几位。

1.3日期类型：

1.3.1存储日期和时间的Date，范围从公元前4712年1月1日到公元9999年12月31日。

1.3.2用毫秒的粒度来存储日期的Timestamp[(precision)]，范围从公元前4712年1月1日

到公元9999年12月31日；precision是毫秒，默认是6，范围为0到9。

1.4大数据类型：(在单列中存储大量数据，如：图像、声音等)

1.4.1变长二进制类型BLOB，最大为4GB。

1.4.2变长字符数据类型CLOB，最大为4GB。

1.4.3变长Unicode字符数据类型NCLOB，最大为4GB。

1.4.4数据库外存储变长二进制数据BFILE。

1.5行数据类型：

1.5.1当往表中插入数据时，Oracle会自动生成ROWID伪列，用来存储一行的物理地址，

有64位。

2、Oracle内置函数：

2.1数学函数

2.1.1ceil(n)

解释：返回大于等于数字n的最小整数

例子：select ceil(132.21), ceil(-36.5) from dual;

2.1.2floor(n)

解释：返回小于等于数字n的最大整数

例子：select floor(11.6), floor(-6.2) from dual;

2.1.3mod(m,n)

解释：取得m/n的余数，如果n为0，则返回m

2.1.4power(m,n)

解释：返回数字m的n次冥，若m为负数，则b必须是正数

例子：select power(-2,3), power(2,-3) from dual;

2.1.5round(n[,m])

解释：执行四舍五入，若省略m，则四舍五入致整数位；若m是负数，则四舍五入到小

数点前m位；若m是正数，则四舍五入到小数点后m位。

2.1.6sqrt(n) 解释：返回n的平方根，n必须大于等于0。

2.1.7trunc(n[,m])

解释：若省略m，则截去n的小数部分；若m为正数，则截取n至小数点后的第m位；若数字m是负数，则截取n至小数点前m位。

2.1.8abs(n)

解释：返回n的绝对值

2.1.9acos(n)

解释：返回n的反余弦值，n的范围是-1到1，输出单位是弧度。

2.1.10exp(n)

解释：返回e的n次冥(e=2.71828183)。

2.1.11：log(m,n)

解释：返回m为底数的n的对数，m可以是除0和1外的任何正整数，n可以是任何正整数。

2.1.12sign(n)

解释：检测n的正负，n小于0，则返回-1；若n等于0，则返回0；若n大于0，则返回1。

2.2日期函数：(日期函数处理Date和Timestamp类型的数据，除months_between返回数值外，其他日期函数返回Date类型的数据，日期数据可以直接加或减一个数值(不一定是整数，代表多少天)，结果任为日期(约定单位为天，也可以是月或年)，两个日期可以直接相减(结果为两者相差多少天)，不可相加(没意义))。

2.2.1：add_months(d,n)

解释：n为正整数时，返回特定日期时间d之后的n个月所对应的日期时间；n为负整数时，返回特定日期d之前的n个月多对应的日期时间。

例子：select sysdate today, add_months(sysdate,2) from dual;

2.2.2：months_between(d1,d2)

解释：返回日期d1和d2间相差的月数

2.2.3：extract

解释：从日期时间中取得特定数据

例子：select extract(year from sysdate) year from dual;

2.3字符处理函数：(该类函数接收的是字符或字符串类型的参数(CHR除外)，除了特别说明外，大部分函数返回VARCHAR2类型的值)

2.3.1：instr(char1,char2[,n[,m]])

解释：在char1中从n为起始位置搜索char2第m次出现的位置，被搜索的起始位置为1，n为负数表示从尾部搜索，m必须为正整数，n和m的默认值为1。

2.3.2：length(char)

解释：返回字符串char的长度，若char为null，则返回null

2.3.3：replace(char,search_string[,replacement_string])

解释：在char字符串中用replacement_string替换search_string，若replacement_string为null，则会在char字符串中去掉search_string；若search_string为空，则返回原字符串

2.3.4：chr(n)

解释：将ASCII码转换成字符

2.3.5：concat(char1,char2)或||

解释：连接连个字符串

例子：select concat('good','morning') from dual;

select 'good' || 'morning' from dual; 2.3.6substr(char,m[,n])

解释：取得字符串char中从m开始长度为n的子串，m为负数表示从尾部开始，起始位置为1。

2.3.7trim(char from string)

解释：截去string字符串中的首部或尾部的char

2.4转换函数：(将数据从一种类型转换成另一种类型)：

2.4.1：to_char(character)

解释：将nchar、nvarchar2、clob、nclob数据转换为数据库字符集数据，当用于nchar、nvarchar2和nclob时，字符串用但引号引起来，前面加上n

例子：select to_char(n'中国') from dual;

2.4.2：to_number(char,[fmt[,nls_param]])

解释：将符合特定数字格式的字符串转换为数字值

例子：select to_number('￥451.25') from dual;

2.5空处理函数：

2.5.1：nvl(expr1,expr2)

解释：将空值转换为指定值，若expr1是null，则返回expr2；否则返回expr1，但是expr1

和expr2类型要匹配。

2.5.2：nullif(expr1,expr2)

解释：若expr1和expr2相等，则返回null；不等则返回expr1，但是expr1和expr2类型

要匹配

2.6分析统计函数：

2.6.1：avg([all | distinct] expression)

解释：计算平均值，distinct使函数只使用每个值的唯一实例，all对所有值进行计算(默认设置，包括重复)

例子：select avg(all sal) from scott.emp;

2.7count([all | distinct | expr])

解释：返回记录的总行数。

例子：select count(*) from ;--返回表的行数