Oracle函数

Oracle函数

1.字符函数

（1）CONCAT函数

这个函数的参数是两个字符串，计算的结果是将两个字符串连接在一起，生成一个新的字符串。例如，concat（‘Hello，’，‘World’）的结果为Hello, World。

（2）CHR与ASCII函数

CHR函数的参数是一个正整数，它将这个正整数作为ASCII码，返回对应的字母。例如chr（65）的结果为A。ASCII函数的作用正好相反，它以一个字符为参数，返回这个字符对应的ASCII码。例如ASCII（‘A’）的结果为65。

（3）INSTR函数

这个函数在一个字符串中查找另一个字符串，如果找到，则返回出现的位置，否则返回0，位置的编号从1开始。这个函数的语法格式为：

instr（字符串，子字符串，start, occurrence）

（4）LENGTH函数

这个函数的作用是求得一个字符串的长度。例如length（‘Hello’）的结果为5。

（5）LOWER和UPPER函数。

这两个函数的作用是进行字符串的大小写转换，它们的参数都是一个字符串。其中LOWER函数将字符串中的字母转换为对应的小写字母，UPPER函数将字符串中的字母转换为对应的大写字母。例如，函数lower（‘Hello’）的结果为hello，而函数upper（‘Hello’）的结果为HELLO。

（6）LPAD与RPAD函数

这两个函数的作用是在字符串中填充指定的字符，使字符串达到指定的长度。LPAD函数从左边填充，RPAD函数从右边填充，处理的结果是得到一个新的字符串。这两个函数的语法格式为：

LPAD（字符串，长度，填充字符）
RPAD（字符串，长度，填充字符）

（7）LTRIM、RTRIM函数和TRIM函数

这三个函数的作用是去掉字符串左边或右边连续的空格，并得到一个新的字符串。例如，函数ltrim（‘Hello’）的结果为Hello, rtrim（‘Hello’）的结果为Hello。
TRIM函数的作用是同时去掉字符串左边和右边的连续空格，它相当于对字符串先执行LTRIM函数，再执行RTRIM函数，或者先执行RTRIM函数，再执行LTRIM函数。

（8）REPLACE函数

这个函数的作用是在一个字符串中查找另一个字符串，并将找到的字符串用第三个字符串代替。这个函数的语法格式为：

replace（字符串，子字符串，替换字符串）

(9) SUBSTR函数

这个函数的作用是根据指定的开始位置和长度，返回一个字符串的子字符串。它的语法格式为：
substr（字符串，开始位置，长度）
位置编号从1开始。开始位置可以是正整数或负整数。如果是负整数，则从字符串的右边开始数。长度是可选的，如果缺省，则返回从开始位置到字符串末尾的所有字符。例如：

substr（'Hello'，2，3）的结果为ell
substr（'Hello'，-3，2）的结果为ll
substr（'Hello'，-3）的结果为ello

2.数学函数

##（1）CONCAT函数

这个函数的参数是两个字符串，计算的结果是将两个字符串连接在一起，生成一个新的字符串。例如，concat（‘Hello，’，‘World’）的结果为Hello, World。

##（2）CHR与ASCII函数
CHR函数的参数是一个正整数，它将这个正整数作为ASCII码，返回对应的字母。例如chr（65）的结果为A。ASCII函数的作用正好相反，它以一个字符为参数，返回这个字符对应的ASCII码。例如ASCII（‘A’）的结果为65。

##（3）INSTR函数
这个函数在一个字符串中查找另一个字符串，如果找到，则返回出现的位置，否则返回0，位置的编号从1开始。这个函数的语法格式为：

instr（字符串，子字符串，start, occurrence）

##（4）LENGTH函数
这个函数的作用是求得一个字符串的长度。例如length（‘Hello’）的结果为5。

##（5）LOWER和UPPER函数。
这两个函数的作用是进行字符串的大小写转换，它们的参数都是一个字符串。其中LOWER函数将字符串中的字母转换为对应的小写字母，UPPER函数将字符串中的字母转换为对应的大写字母。例如，函数lower（‘Hello’）的结果为hello，而函数upper（‘Hello’）的结果为HELLO。

##（6）LPAD与RPAD函数
这两个函数的作用是在字符串中填充指定的字符，使字符串达到指定的长度。LPAD函数从左边填充，RPAD函数从右边填充，处理的结果是得到一个新的字符串。这两个函数的语法格式为：

LPAD（字符串，长度，填充字符）
RPAD（字符串，长度，填充字符）

##（7）LTRIM、RTRIM函数和TRIM函数
这三个函数的作用是去掉字符串左边或右边连续的空格，并得到一个新的字符串。例如，函数ltrim（‘Hello’）的结果为Hello, rtrim（‘Hello’）的结果为Hello。
TRIM函数的作用是同时去掉字符串左边和右边的连续空格，它相当于对字符串先执行LTRIM函数，再执行RTRIM函数，或者先执行RTRIM函数，再执行LTRIM函数。

##（8）REPLACE函数
这个函数的作用是在一个字符串中查找另一个字符串，并将找到的字符串用第三个字符串代替。这个函数的语法格式为：

replace（字符串，子字符串，替换字符串）

##（9）SUBSTR函数
这个函数的作用是根据指定的开始位置和长度，返回一个字符串的子字符串。它的语法格式为：
substr（字符串，开始位置，长度）
位置编号从1开始。开始位置可以是正整数或负整数。如果是负整数，则从字符串的右边开始数。长度是可选的，如果缺省，则返回从开始位置到字符串末尾的所有字符。例如：

substr（'Hello'，2，3）的结果为ell
substr（'Hello'，-3，2）的结果为ll
substr（'Hello'，-3）的结果为ello

日期函数

（1）ABS函数

这个函数的作用是求得一个数字的绝对值。
##（2）CEIL和FLOOR函数
这两个函数都以一个数字为参数，CEIL函数返回大于或等于这个数字的最小整数。FLOOR函数返回小于或等于这个数字的最大整数。例如：

ceil（-15.6）的结果是-15，floor（-15.6）的结果是-16。
ceil（15.6）的结果是16，floor（15.6）的结果是15。

##（3）MOD函数
这个函数有两个参数，结果是两个数相除所得的余数。
##（4）ROUND和TRUNC函数
ROUND函数的作用是对数据进行四舍五入计算。这个函数的语法结构为：
round（数据，舍入位置）
其中舍入位置可以是正整数，也可以是负整数。如果是正整数，则从小数点开始向右数，一直到舍入位置，从这一位开始四舍五入。如果是负整数，则从小数点开始向左数，然后进行四舍五入。例如：

round（49.456，2）的结果为49.46
round（89.456，-1）的结果为90
round（89.456，-2）的结果为100

TRUNC函数的用法与ROUND函数类似，只不过它的功能是对数据进行截取运算，只舍不入，也就是把一个数据的指定位之后的数字全部舍去。例如：

trunc（49.456，2）的结果为49.45
trunc（49.456，-1）的结果为40

#日期函数
##（1）ADD_MONTHS函数
这个函数在某个日期的基础上，加上一个指定的月数，返回一个新的日期。它的格式为：

ADD_month（日期，月数）

这个函数是在指定的日期上加上若干个整月数，所以日期中的日应该保持不变。但是指定的日期如果是当月的最后一天，函数会做相应的调整，以保证返回的日期也是当月的最后一天。例如：

ADD_months（to_date（'12-05-10'，'dd-mm-yy'），6）的结果为"12-11月-10"；
ADD_months（to_date（'31-01-10'，'dd-mm-yy'），1）的结果为"28-2月-10"
ADD_months（to_date（'28-02-10'，'dd-mm-yy'），1）的结果为"31-3月-10"

在ADD_MONTHS函数中，不能直接使用类似“12-05-10”这样的日期型数据，因为Oracle把这样的数据是当做字符串来处理的，所以首先要调用to_date函数将它转换为真正的日期型数据。上述第一个例子容易理解。在第二个例子中，因为31日是2010年1月的最后一天，所以加上一个月后，得到2月的最后一天，即28日。同理，在2010年2月28日上加上一个月后，得到3月的最后一天，即3月31日。函数返回的结果可能因系统的不同而显示不同的格式，因为不同系统采用的日期格式可能不同。
##（2）LAST_DAY函数
这个函数返回指定日期所在月份的最后一天。例如：

last_day（to_date（'01-02-10'，'dd-mm-yy'））的结果为"28-2月-10"。

##(3)MONTHS_BETWEEN函数
这个函数有两个参数，都是日期型数据，返回的结果是两个日期之间相差的月数。这个函数的语法格式为：months_between（日期1，日期2）。例如：

months_between（to_date（'01-05-10'，'dd-mm-yy'），to_date（'01-05-09'，'dd-mm-yy'））

结果为12

months_between（to_date（'01-05-10'，'dd-mm-yy'），TO_DATE（'12-05-09'，'dd-mm-yy'））

结果为11.6451613,如果两个日期中的日相同，或者都是当月的最后一天，则返回结果是一个整数，否则将返回一个小数。第一个参数如果比第二个参数小，则返回的结果为负数。
##(4）NEXT_DAY函数
这个函数有两个参数，一个是日期，一个是与星期几对应的整数，返回的结果是这个日期之后最近的星期几所对应的日期。函数的调用格式为：

next_day（日期，整数）

例如，假设2010年4月28日是星期三，则最近的星期五应该是2010年4月30日，最近的星期二应该是2010年5月27日。注意星期的编号方法是：星期天为1，星期一为2，依此类推。例如：

next_day（to_date（'28-04-10'，'dd-mm-yy'），6）的结果为"30-4月-10"
next_day（to_date（'28-04-10'，'dd-mm-yy'），3）的结果为"04-5月-10"

##（5）ROUND函数
这个函数对指定的日期进行四舍五入处理。它有两个参数，一个是日期，一个是表示日期某个组成部分的格式字符串。函数的语法格式为：

其中格式字符串用于指定从日期的哪一部分开始四舍五入。Oracle支持的格式字符串包括yy（或yyyy）、mm、dd、hh，分别表示从年、月、日、时位进行四舍五入。例如：

round（to_date（'28-08-10'，'dd-mm-yy'），'mm'）的结果为"01-9月-10"
round（to_date（'12-08-10 13：50'，'dd-mm-yy hh24：mi'），'dd'）结果为"13-8月-10"

在第一个例子中，指定从月开始四舍五入，由于28日已经过了该月的一半，故该位向前进位。在第二个例子中，指定从日开始四舍五入，由于13时已经超过当天中午12时，故向前进位，使12日变为13日。
##（6）SYSDATE函数
这是个很常用的函数，它用来获得系统当前时间。在有些日志操作中，常常需要记录当前时间，使用这个函数是很方便的。这个函数没有任何参数。这个函数返回的时间可以精确到秒，但在显示时可能只显示到日，根据系统的不同显示格式有所变化。如果希望得到时、分、秒，则需要通过to_char函数进行数据类型转换。例如：

SQL＞SELECT sysdate FROM dual；

##（7）TRUNC函数
这个函数的功能类似于ROUND函数，但是它不进行四舍五入，而是从指定位开始，截断其后面的部分，它的语法格式与ROUND函数相同。例如：

trunc（to_date（'28-08-10 23：50'，'dd-mm-yy hh24：mi'），'dd'）结果为"28-8月-10"
trunc（to_date（'28-08-10'，'dd-mm-yy'），'mm'）结果为"01-8月-10"

#4.类型转换函数
##（1）to_char
to_char函数的作用是将一个日期型或者数字型数据转换为字符串。如果操作对象是日期型数据，这个函数的语法格式为：

TO_CHAR（日期，格式字符串）

其中格式字符串是由日期格式元素和隔离符号组成的字符串，用来规定转换的格式。例如，下列语句将员工表中员工的受聘日期按照指定的格式转换为字符串：

SELECT ename, to_char（hiredate，'yyyy-mm-dd day'）FROM emp WHERE empno=7902；

又如，下列SELECT语句获取当前系统时间，然后按照指定的格式进行转换：

SELECT to_char（sysdate，'yyyy-mm-dd hh24：mi：ss'）FROM dual；

如果操作对象是数字型数据，to_char函数的语法格式为：

to_char（数字，格式字符串）

其中格式字符串是由数字格式元素和小数点、分隔符组成的字符串，用来控制转换的格式。这种转换主要用在财务报表中。例如：

to_char（5432，'$9，999.99'）的结果为'$5，432.00'

##(2）to_date函数
这个函数的作用是把一个字符串转换为一个日期型数据，它有两个参数，处理的结果是一个日期型数据。这个函数的语法格式为：

to_date（字符串，格式字符串）

其中格式字符串由表1.4中的格式元素组成，它的作用是把字符串中用分隔符分开的不同部分解释成一个日期的不同部分。例如：

to_date（'12-09-2010'，'dd-mm-yyyy'）结果为"12-9月-10"

通过格式字符串，把字符串中的12解释为日，09解释为月，2010解释为年，然后将这个字符串转换为一个日期型数据。格式字符串中的每部分对字符串中的每部分是一一对应地进行解释的，所以日期字符串中的每一部分对于格式字符串中的对应部分来说，必须是合法的数据。例如：

to_date（'12-02-2010'，'dd-mon-yyyy'）

将得到一个错误的结果，原因是格式字符串中的mon试图将字符串中的02解释为某个月份的缩写，而02并不是某个月份缩写。还要注意的是，转换后得到的日期格式与函数指定的格式并不一定相同，因为格式字符串是用来解释字符串中的不同部分的，而日期的显示格式依赖于当前系统的日期格式。
##（3）to_number
这个函数的作用是把一个字符串转换为数字，它有两个参数，处理的结果是一个数字型数据。这个函数的语法格式为：

to_number（字符串，格式字符串）

其中格式字符串包含表1.5中所介绍的格式元素，它把字符串中的$以及用逗号、小数点分开的不同部分分别进行解释，它的分隔方式与字符串不同部分的分隔方式应该是一致的。例如：

to_number（'$1，000.50'，'$999，999.99'）的结果为1000.5
to_number（'$1000.50'，'$999，999.99'）的结果将出错

第二个例子出错的原因是字符串中的数字字符并没有每三位逗号隔开，而格式字符串却要按这种方式解释它。另外，格式字符串中的9或0的位数不应少于字符串中数字字符的位数。
#5.分组函数
##（1）AVG函数
AVG函数用来求指定列上的平均值，它将自动忽略列上的空值。如果要去掉重复值的计算，可在列名前加上DISTINCE选项。例如，要求部门30的员工的平均工资，构造的SELECT语句如下：

SQL＞SELECT avg（sal），avg（distinct sal）FROM emp WHERE deptno=30；

##（2）MIN与MAX函数
MIN函数的作用是求指定列的最小值，MAX函数的作用是求指定列的最大值。这两个函数都自动忽略空行。例如，要求部门30的员工的最低工资和最高工资，构造的SELECT语句如下：

SQL＞SELECT min（distinct sal），max（sal）FROM emp WHERE deptno=30；

##(3)COUNT函数
COUNT函数用来计算数据的行数。在默认情况下，这个函数不计算空行。如果要计算空行，可以用“*”代替列名。如果要去掉重复值的计算，可在列名前加上DISTINCE选项，这样如果遇到重复值，只计算一次。例如，要计算公司中领取工资的人数，构造的SELECT语句如下：

SQL＞SELECT count（sal），count（distinct sal）FROM emp；

##（4）SUM函数
SUM函数的作用是对指定列求和，它将自动忽略空值。如果要去掉重复值的计算，可在列名前加上DISTINCE选项。例如，要求部门30的员工工资总和，构造的SELECT语句如下所示：

SQL＞SELECT sum（sal）FROM emp WHERE deptno=30；

现在，为了说明这些函数的用法，把它们综合起来，构造一个SELECT语句，求部门30的员工的平均工资、最高工资、最低工资、工资总和以及总人数。这条SELECT语句如下所示：

SQL＞SELECT avg（sal）AS平均工资，min（sal）AS最低工资，
max（sal）AS最高工资，sum（sal）AS工资总和
FROM emp
WHERE deptno=30；

##(5)分组统计
与GROUP子句一起使用的还有一个子句，即HAVING子句。这个子句是可选的，它不能单独使用，只能配合GROUP子句使用，作用是对GROUP子句设置条件，对统计后的结果进行限制。例如，对于上述统计，我们只希望显示最低工资在900元以上，并且工资总和在7000元以上的部门的统计信息，相应的SELECT语句为：

SQL＞SELECT deptno AS部门号，avg（sal）AS平均工资，min（sal）AS最低工资，
max（sal）AS最高工资，sum（sal）AS工资总和
FROM emp
GROUP BY deptno
HAVING min（sal）＞900 AND sum（sal）＞7000

HAVING子句中的关系表达式必须使用分组函数，可以是在SELECT语句中已经出现的分组函数，也可以是没有出现的函数。虽然HAVING子句和WHERE子句都是用来设置条件的，但是WHERE子句设置的条件是在查询时起作用的，它决定查询什么样的数据，如果要进行统计，这样的条件是在统计之前就已经起作用了。而HAVING子句设置的条件只有在进行统计后才起作用，它决定了对于统计产生的数据，哪些需要显示给用户。
##(6)排序
要对公司各部门的工资统计情况进行排序，要求是按照工资总和从大到小排序，如果工资总和相同，再按照部门号从小到大排序。相应的SELECT语句为：

SQL＞SELECT deptno AS部门号，avg（sal）AS平均工资，min（sal）AS最低工资，
max（sal）AS最高工资，sum（sal）AS工资总和
FROM emp
GROUP BY deptno
ORDER BY sum（sal）desc, deptno asc

ORDER BY 5 desc，部门号asc