SQL函数大总结.docx

1、SQL函数大总结SQL函数大总结number数字类型函数 ABS(x) 函数，此函数用来返回一个数的绝对值。ACOS(x)函数，返回X的反余弦值。X范围从1到-1，输入值从0到派，以弧度为单位。ASIN(x)函数，返回X的反正弦值。X范围从1到-1，输入值从-PI/2到PI/2，以弧度为单位。ATAN(x)函数，返回X的反正切值。输入值从-PI/2到PI/2，以弧度为单位。BITAND(x,y)函数,返回X和Y的与结果。X和Y必须为非负整数。注意没有BITOR函数，但是在UTL_RAW包中有用 于RAW值的位操作符。CEIL(x)函数，用来返回大于或等于X的最小整数。COS(x)函数，返回x的

2、余弦值。x是以弧度表示的角度。COSH(x)函数，返回X的双曲余弦。EXP(x)函数，与power(x,y)函数类似，不过不用指明基数，返回E的X次幂。E=2.71828183.FLOOR(x)函数，用来返回小于或等于X的最大整数。LN(x)函数，返回x的自然对数。x必须大于0。LOG(x,y)函数，返回以X为底Y的对数。底必须是不为0和1的正数，Y是任意正数。MOD(被除数，除数)求余函数，如果除数为0，则返回被除数。POWER(x,y)函数，返回X的Y次幂。底X和指数Y都不必是正整数，但如果X是负数的话，Y必须是整数。ROUND(x,y)函数，返回舍入到小数点右边Y位的X值。Y缺省为0，这

3、将X舍入为最接近的整数。如果Y是负数， 那么舍入到小数点左边相应的位上，Y必须为整数。SIGN(x)函数，此函数用来返回一个数的正负值，若为一个正数则返回1，若为一个负数则返回-1， 若为0则仍返回0，有点像把模拟量数字化的意思。SIN(x)函数，返回X的正弦。x是以弧度表示的角度。SINH(x)函数，返回x的双曲正弦。SQRT(x)函数，返回x的平方根，x不能是负数。TAN(x)函数，返回x的正切。x是以弧度表示的角度。TANH(x)函数，返回x的双曲正切。TRUNC(x,y)截取值函数，Y缺省为0，这样X被截取成一个整数。如果Y为负数，那么截取到小数点左边相应位置WIDTH_BUCKET(

4、x,min,max,num_buckets) 只能在SQL语句中使用。使用WIDTH_BUCKET可以根据输入参数创建等长的段。范围MIN到MAX被分为num_buckets节，每节有相同的大小。返回X所在的那一节。如果X小于MIN，将返回0，如果X大于或等于MAX，将返回num_buckets+1.MIN和MAX都不能为NULL，num_buckets必须是一个正整数。如果X是NULL，则返回NULL。时间类型函数：（date）内部存储格式：世纪、年、月、日、小时、分钟、秒默认格式是：DD-MON-RR。SYSDATE 返回当前的系统时间。SELECT SYSDATE FROM DUAL；对

5、日期的数学运算SELECT （SYSDATE-HIRE_DATE）/7 FROM TABLENAME WHERE ROWNUM;数字列ADD_MONTHS(date,x)函数，返回加上X月后的日期DATE的值。X可以是任意整数。如果结果的月份中所包含的 日分量少于DATE的月份的日分量，则返回结果月份的最后一天。如果不小于，则 结果与DATE的日分量相同。时间分量也相同。CURRENT_DATE 以DATE类型返回会话时区当前的日期。这个函数同SYSDATE相似，除了SYSDATE不管当 会话时区。CURRENT_TIMESTAMP(precision) 以TIMESTAMP WITH TIM

6、EZONE 类型返回会话时区当前的日期。如果 指定precision，它指返回秒数的精度，缺省为6。DBTIMEZONE 返回数据库的时区。LAST_DAY（日期） 指定日期所在月份的最后一天的日期，这个函数可用来确定本月还有多少天。LOCALTIMESTAMP(precision) 以TIMESTAMP类型返回会话时区的当前日期。如果指定precision，它指 返回秒数的精度，缺省为6 。MONTHS_BETWEEN(离当前比较近的日期date1,以前的日期)两个日期之间相差的月数（以日作为最小单位来计算的）。返回是相差的月数。如果date1和date2的日分量相同，或者这两个日期都分别是

7、所在月的最后一天，那么返回结果是个整数。否则，返回结果包含一个分数，以一个月31天计算。NEW_TIME(d,zone1,zone2)函数，当时区zone1中的日期和时间是D的时候，返回时区zone2中的日期和时间。 返回类型为DATE。zone1和zone2是字符字符串，另外的时区可在ORACLE9I中 通过查询V$TIMEZONE_NAMES得到。NEXT_DAY （日期，星期几） 指定日期后将要遇到的后七天的某一天的日期。 ROUND（日期，MONTH/YEAR） 四舍五入得到新的日期。 保留位置是月和年SESSIONTIMEZONE 返回当前会话的时区。返回类型是一个时区偏移或时区片名

8、的字符字符串。如果指 定格式，则与 ALTER SESSION 语句中的格式相同。SYS_EXTRACT_UTC(datetime) 从提供的DATETIME中以UTC(Coordinated Universal Time)返回时间。 DATETIME必须包含一个时区。SYSTIMESTAMP 以TIMESTAMP WITH TIMEZONE 返回当前的日期和时间。当在分布式SQL语句中使用的时候，返回本地数据库的日期和时间。TRUNC（日期，MONTH/YEAR） 截取 TZ_OFFSET(timezone) 以字符字符串返回提供的timezone和UTC之间的偏移量。timezone可以被

9、指定为时 区名或+/-HH:HI格式表示的偏移量。也可使用SESSIONTIMEZONE和 DBTIMEZONE函数，返回格式为+/-HH:HI。字符字符串 时区AST 大西洋标准时ADT 大西洋夏令时BST 白令标准时BDT 白令夏令时CST 中央标准时CDT 中央夏令时EST 东部标准时EDT 东部夏令时GMT 格林威治平均时HST 阿拉斯加夏威夷标准时HDT 阿拉斯加夏威夷夏令时MST Mountain标准时MDT Mountain夏令时NST 纽芬兰标准时PST 太平洋标准时PDT 太平洋夏令时YST YuKon标准时YDT YuKon夏令时 日期和日期时间算术运算返回类型结果 d1-

10、d2NUMBER返回D1和D2之间相差的天数。该值是一个数值，其小数部分代表一天的几分之几。dt1-dt2INTERVAL返回DT1和DT2之间的时间间隔。i1-i2INTERVAL返回i1和i2之间的差距。d1+d2N/A非法仅能进行两个日期之间的相减。dt1+dt2N/A非法仅能进行两个日期之间的相减。i1+i2INTERVAL返回i1和i2的和。d1+nDATE在D1上加上N天作为DATE类型返回。N可以是实数，它包含一天的几分之几。d1-nDATE从D1上减去N天作为DATE类型返回。N可以是实数，它包含一天的几分之几。dt1+i1DATETIME返回DT1和I1的和。dt1-i1DA

11、TETIME返回DT1和I1之间的差距。i1*nINTERVAL返回I1的N次方。i1/nINTERVAL返回I1除以N的值。表中注：D1和D2指日期值；DT1和DT2指日期时间值；I1和I2指时间间隔值；N指数字值。显示转换：（conversion）TO_NUMBER(char,format_model)字符转换到数字类型 TO_DATE(char,format_model)字符转换到日期类型格式说明符：要与前边要转换的字符串的格式要相同才能转换（匹配问题：格式和位数）。TO_CHAR(date,format_model,nlsparams)第二个参数可以省略，不指定格式，按系统默认格式输出

12、。区分大小写。使用FM（在格式控制符前添加）符号可以去掉空格或是首位的零。如果指定了NLSPARAMS，则它控制返回字符串的月和日分量所使用的语言。格式为：NLS_DATA_LANGUAGE=language,language指需要的语言。例：select to_char(sysdate,FMyyyy-mm-dd) from dual;格式控制符的类型： YYYY 四位的年 YEAR 年的拼写 MM2位数字的月 MONTH 月的全名 MON 月名的前三个字符 DY 星期名的前三个字符 DAY 星期名的全称 DD 2位的天 时间格式控制符： HH24:MI:SS AM HH12:MI:SS PM

13、 通过“”来实现加入特殊字符的格式控制符。SELECT TO_CHAR(SYSDATE,FMyyyy年mm月dd日) from dual;DDSPTH DD是格式控制符。 TH是序数词，将日期转换成英文的序数词拼写。SP是基数词，将日期转换成英文的基数词拼写。TO_CHAR(NUM,format_model,nlsparams)转换数字 将NUMBER类型参数NUM转换成VARCHAR2类型。如果指定FORMAT，它会控制整个转换。如果没有指定FORMAT，那么结果字符串中将包含和NUM中有效位的个数相同的字符。NLSPARAMS用来指定小数点和千分符及货币符号。它的格式可为：NLS_NUME

14、RIC_CHARS= dg NLS_CURRENCY= string d和g分别代表小数点和千分符。STRING代表货币符号。数字格式控制符：9 代表一位数字（替换符。有，数字显示；没有。不什么都显示。） 0 代表一位数字（有数字，显示；没有，强制显示0。） $ 美圆符号L 本地货币. 小数点, 千分符B 当整数部分为0时，将整数部分填充为空格。 例：B999MI返回带有后继符号而不是前导负号的负数值，正数值将带有后继的空格。999MI S 返回一个前导符号或后继符号，正数为+，负数为-。 S9999 或 9999SPR使用尖括号返回负数。正数将有前导或后继空格。999PRD 在指定位置返回一

15、个小数点。两侧的9的个数指定了最大的位数。99D9G 在指定位置返回千分符，G可以在FORMAT_model中出现多次。9G999G9C 在指定位置返回ISO货币符号。C可以在FORMAT_model中出现多次。C99L 在指定位置上返回本地货币符号。 L99V 返回一个被乘以10的N次方的数值，这里N是V后边9的个数。99V99EEEE 使用科学记数法返回该数值。9.99EEEERM 使用大写的罗马数字表示返回该数值。 RMrm 使用小写的罗马数字表示返回该数值。 rmFM 返回不含前导和后继空格的数值。 FM99.09格式控制符位数一定要大于或等于NUMBER的位数，不能小于。用RR解决跨

16、世纪问题： 小于50的认为是1950-2050 大于50的认为是1951-1999 数字和日期是不能相互转换的。ASCIISTR(string) 返回只包含有效的SQL字符和斜线的字符串。string中的任何无效的字符将被转换为一个相当的数字，在之前加上斜线。 BIN_TO_NUM(num,num.) 将一位矢量转换位相当的数字。它的参数是一系列逗号隔开的NUMS，每一个都必须是0或1。例如BIN_TO_NUM(1,0,1,1)将返回11，因为11的二进制表示是1011。当使用分组集合和GROUP BY 子句时该函数很有用。 CHARTOROWID(x)函数，将字符串转换成一个ROWID类型的

17、值，注意格式必须采用ROWID数据类型格式，即“数据块号:行序号:数据文件号”。 COMPOSE(string) 以相同字符集中完全规格化Unicode形式返回string.string可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB或NCLOB类型。DECOMPOSE(string) 返回一个Unicode字符串。它是string的规范分解。string可以是CHAR、VARCHAR2、 NCHAR、NVARCHAR2、CLOB或NCLOB类型。 FROM_TZ(timestamp,timezone) 返回一个TIMESTAMP WITH TIMEZONE 类型值。

18、它将TIMESTAMP(没有时区信息)和提供的TIMEZONE组合在一起。HEXTORAW(string)将由STRING表示的二进制数值转换为一个RAW数值。STRING应该包含十六进制值。STRING中的每两个字符表示结果RAW中的一个字节。HEXTORAW和RAWTOHEX互为反函数。 NUMTODSINTERVAL(x,unit) 将X转换为INTERVAL DAY TO SECOND 值，X应该是一个数字。UNIT是一个字符字符串（可以是CHAR、VARCHAR2、NCHAR或NVARCHAR2），且是DAY、HOUR、MINUTE、SECOND之一。unit是不区分大小写的，返回值

19、的缺省精度为9。NUMTOYMINTERVAL(x,unit)将X转换成INTERVAL YEAR TO MONTH 值，X应该是一个数字。UNIT是一个字符字符串（可以是CHAR、VARCHAR2、NCHAR或NVARCHAR2），且是YEAR或MONTH之一。unit是不区分大小写的，返回值的缺省精度为9。 REFTOHEX(refvalue)返回一REF refvalue的十六进制表示。RAWTOHEX(rawvalue)将RAW类型值rawvalue转换为一个十六进制表示的字符串。rawvalue中的每个字节转换为一个双字符的字符串。RAWTONHEX(rawvalue)将RAW类型值

20、rawvalue转换为一个十六进制表示的字符串。rawvalue中的每个字节转换为一个双字符的字符串。RAWTONHEX返回值是NVARCHAR2类型而不是VARCHAR2类型。ROWIDTOCHAR(rowid)函数，将ROWID类型值转换成字符串。与CHARTOROWID互为反函数。ROWIDYONCHAR(rowid) 与ROWIDTOCHAR类似，返回类型是NCHAR，而不是CHAR。TO_CLOB(string)将string转换为CLOB。string可以是文字或另一个LOB列。如果参数包含NCHAR数据，它被转换为数据库字符集TO_DSINTERVAL(string,nlspar

21、ams)将string(可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2)转换为 INTERVAL DAY TO SECOND 类型。如果选定nlsparams,则nlsparams只能包含小数点和千分位字符的NLS_NUMERIC_CHARARCTERS表示。TO_LOB(long_column)将long_column转换成LOB。这个函数用于将LONG和LONG RAW分别转换为CLOB和LOB。TO_MULTI_BYTE(string)返回将所有单字节字符替换为等价的多字节字符的STRING。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用。否则，STRING不

22、会进行任何处理而被返回，与TO_SINGLE_BYTE 互为反函数。TO_NCHAR 和TO_CHAR相似，结果是属于国家字符集而不是数据库字符集。TO_NCLOB(string)将STRING转换为NCLOB。STRING可以是文字或另一LOB列。TO_SINGLE_BYTE(string)返回将所有双字节字符替换为等价的单字节字符的STRING。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用。否则，STRING不会进行任何处理而被返回，与TO_MULTI_BYTE 互为反函数。TO_TIMESTAMP(string,format,nlsparams)将其参数CHAR或VARCHA

23、R2类型string转换成TIMESTAMP类型。TO_TIMESTAMP_TZ(string,format,nlsparams)将其参数CHAR或VARCHAR2类型string转换成 TIMESTAMP WITH TIMEZONE 类型。TO_YMINTERVAL(string)将string(可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2)转换为INTERVAL YEAR TO MONTH 类型。TO_YMINTERVAL与TO_DSINTERVAL相似，除了它不能使用NLS参数作为参数并返回YEAR TO MONTH时间间隔而不DAY TO SECOND 时间间隔。t

24、o_label(x,y)函数，按照格式Y将字符串X转换成MLSLABEL类型的一个值，若默认格式为Y，则按照默认格式进行转换。dump(w,x,y,z)函数,用来返回字符串EXPR的数据类型，内部的存储位置和字符长度。 dump(expr,return_datatype,start_position,length). return_datatype是指定返回返回位置用什么方式表示，可以为8、10、16、17，分别表示 用八进制、十进制、十六进制和字符类型。例：select dump(last_name,8,3,2) ,dump(last_name,10,3,2) ,dump(last_name

25、,16,3,2) ,dump(last_name,17,3,2) from employeeswhere lower(last_name) = smith;greatest(x,y,.)函数，返回参数列表中的最大值。其参数的类型是由第一个参数决定的，可以为数值型、日期型、和字符型等，后面的参数被强制转换成此种数据类型。进行字符串的比较时，其大小由字符在字符集中的数值决定，在字符集中的数值大，则此字符就大，对于字符串，此函数返回VARCHAR2类型。least(x,y,.)函数，返回列表参数中的最小值。与上两个函数类似的有：greatest_lb(x,y,.)函数和least_lb(x,y,.)

26、函数，分别求出列表中的标签的最大下限和最小上限，其参数必须为MLSLABEL 类型，返回值为 RAW MLSLABEL类型。user 函数，返回当前用户的数据库用户名。uid函数，返回唯一标识当前用户的整数。这两个函数在完整性约束检查时会用到，可以当作引用变量一样引用它们。userenv(x)函数，返回当前会话的一些信息，由X指定返回何种信息。在写一个指定应用的审计测试表或决定为当 前会话指定哪种语言时会用到，但完整性约束时不能用。参数：entryid返回有效的审计条目标识 label返回当前会话的标签language以“语言.字符集”形式返回所用的语言和字符集sessionid返回正在使用的

27、审计会话号terminal返回当前会话终端所用的操作系统嵌套函数：单行函数可以嵌套任意层；嵌套函数从最深层开始执行。通用函数：BFILENAME(directory,file_name)返回操作系统中与物理文件file_name相关的BFILE位置指示符。directory必须是数据字典中的一个DIRECTORY类型对象。COALESCE（，可以多个参数）返回从左到右的第一个非空的表达式。如果所有表达式都为NULL，则返回NULL。EMPTY_BLOB/EMPTY_CLOB返回一个空的LOB位置指示符。EMPTY_CLOB返回一个字符位置指示符，EMPTY_BLOB返回一个二进制位置指示符。E

28、XISTSNODE(XMLType_instrance,Xpath_string)使用Xpath_string中的路径，确定由XMLType_instrance标识的XML文档的TRAVELSAL是否返回任何节点。这个函数将返回一个NUMBER值，如果没有节点则为0，如果有节点则为大于0。EXTRACT(XMLType_instrance,Xpath_string)应用Xpath_string之后，返回由XMLType_instrance标识的XML文档的一部分。GREATEST(expr1,expr2.)返回其参数中最大的表达式。在进行比较之前，每个表达式都被隐式转换为EXPR1的类型，如果

29、EXPR1是字符类型，则使用非填充空格字符比较，返回结果为VARCHAR2类型。LEAST(expr1,expr2.)返回其参数中最小的表达式，其余同上。NVL(EXPR1,EXPR2)类型必须匹配，如果EXPR1是NULL，则返回EXPR2，否则返回EXPR1。返回值与EXPR1类型相同，除非EXPR1是字符类型，在这种情况下将返回VARCHAR2类型。这个函数用于确保查询记录集中不包含NULL值。NVL2(EXPR1,EXPR2,EXPR3)如果EXPR1是NULL，则返回EXPR2，否则返回EXPR3。返回值与EXPR2类型相同，除非EXPR2是字符类型，在这种情况下将返回VARCHAR2类型。SYS_CONNECT_BY_PATH 返回列值的从根到结点的路径，它仅在层次查询中有效。SYS_CONTEXT(namespace,parameter,length)返回与namespace的内容相关联的patameter的值。使用DBMS_SESSION.SET_CONTEXT过程设置参数和namespace.返回值是VARCHAR2类型，如果没有指定length,则最大长度是255字节。SYS_DBURIGEN产生一个URL用于从数据库中提取XML文