ORACLE SQL函数

原创

emily19831018 2010-05-13 15:02:12 博主文章分类：ORACLE ©著作权

文章标签 ORACLE 函数 SQL 数据库休闲 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者emily19831018的原创作品，如需转载，请与作者联系，否则将追究法律责任

分为单行函数和多行函数。

1、单行函数：仅对单个行进行运算，可接受多个参数并返回一个值，可以修改数据类型，可以嵌套，参数可以是一个列或者是一个表达式，可以用在SELECT,WHERE和ORDER BY子句中。

语法：FUNCTION_NAME[(ARG1,ARG2...)]

单行函数的类型如下：

1）字符函数：接受字符输入，可以返回字符或者数字值；

字符函数包括大小写处理函数和字符处理函数。

a)大小写处理函数：LOWER、UPPER、INITCAP

LOWER（C)：将指定字符串内的字符变为小写，支持CHAR\VARCHAR\NCHAR\NVARCHAR2\CLOB\NCLOB类型；

UPPER（C）：将指定字符串内的字符变为大写；

INITCAP(C)：将字符串中单词的第一个字母变为大写，其他则转换为小写；

b)字符处理函数：

CONCAT(C1,C2)：连接字符，等同于||；

SUBSTR(C1,N1[,N2])：截取指定长度字符串；

N1：开始长度；N2：截取的字符串长度，如果N2为空，则截取到字符串结尾；如果N1=0,则N1=1；如果N1>0,则ORACLE从左向右确认起始位置；

如果N1<0，则ORACLE从右向左确认其实位置；

如果N1>C1.length或N2<0，则返回空；

LENGTH(C)：返回指定字符串的长度；

INSTR(C1,C2[N1,[N2]])：返回C2在C1中的位置；其中，C1为原字符串，C2为要寻找的字符串，N1为查询起始位置，N1>1则从左往右数，N1<1则从右往左数，N1=0则返回0，N2为第几个匹配项，N2>0；

LPAD(C1,N[,C2])：返回指定长度=N的字符串；如果N<C1.LENGTH，则从右往左截取制定长度返回；如果N>C1.LENGTH AND C2 IS NULL，则从左往右补充字符长度至N并返回；如果N>C1.LENGTH AND C2 IS NOT NULL，以指定字符C2从左向右补充C1长度至n并返回；

RPAD(C1,N[,C2]）：返回指定长度=N的字符串。

TRIM（[LEADING\TRAILING\BOTH] C2 FROM C1)：去掉字符串头、尾或头尾的一个字符，其中BOTH参数指定与否没有区别。

注意：C2.LENGTH=1

REPLACE(C1,C2[,C3])：将C1字符串中的C2替换为C3，如果C3为空，则直接删除C2。

2）数字函数：接受数字输入，返回数字值；

ROUND(N1[,N2])：四舍五入函数，返回四舍五入小数点右边N2位的N1的值；N2缺省为0，N2为负数则舍入小数点左边的相应位上。

TRUNC(N1[,N2])：返回截尾到N2为小数的N1的值；N2为缺省，将N1截尾为整数；N2为负数，将N1截尾在小数点左边的相应位上。

MOD(N1,N2)：返回N1除以N2的余数，如果N2为0，返回N1；

3）日期函数：对DATE数据类型的值进行运算，除了MONTHS_BETWEEN函数返回一个数字，其他日期类型的函数都返回一个DATE数据类型的值；

CURRENT_DATE：返回当前session所在时区的默认时间；

用日期计算：

运算	结果	说明
date + number	日期	加一个天数到一个日期上
date - number	日期	从一个日期上减一个天数
date - date	天数	用一个日期减另一个日期
date + number/24	日期	加一个小时数到一个日期上

MONTHS_BETWEEN(D1,D2)：返回D1和D2之间的月份差；

ADD_MONTHS(D,N)：返回指定月份加N之后的值，N是任意整数；

NEXT_DAY(D,N)：返回指定日期后的第N个日期，N为一周中的某一天；

LAST_DAY(D)：返回指定日期所在月内的最后一天；

ROUND(D[,FMT])：如果不指定fmt参数，则返回距离指定日期最近的日期；

TRUNC(D[,FMT])

4）转换函数：从一个数据类型到另一个数据类型转换了一个值；

a)隐式数据类型转换：

对于直接赋值，ORACLE SQL可以进行以下的隐式转换：

从	到
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

对于表达式赋值，ORACLE SQL可以进行以下的隐式转换：

从	到
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE

b)显式数据类型转换：TO_CHAR()，TO_NUMBER()，TO_DATE()

TO_CHAR()分为：字符->字符，时间->字符

转换数值->字符，字符->日期TO_DATE（C），C必须为0-5373484的正整数。

TO_NUMBER()

日期格式模板：

YYYY	数字全写年
YEAR	年的拼写
MM	月的两数字值
MONTH	月的全名
MON	月的三字母缩写
DY	周中天的三字母缩写
DAY	周中天的全名
DD	月的数字天

时间格式模板：

元素	说明
AM或PM	正午指示
A.M.或P.M.	带句点的正午指示
HH或HH12或HH24	天的小时，或小时(1–12)，或小时(0–23)
MI	分钟 (0–59)
SS	秒 (0–59)
SSSSS	午夜之后的秒 (0–86399)

数字格式模板：

9	表示一个数
0	强制显示为零
$	放置一个浮动美元符号
L	使用浮动本地货币符号
.	打印一个小数点
,	打印一个千位指示
MI	右边减号 (负值)
PR	将负数加上括号
EEEE	科学计数法 (格式化必须指定四个E)
V	乘10，n次 (n = V 后面 9 的个数)
B	将0显示为空格