Oracle内置SQL函数
F.1字符函数——返回字符值
这些函数全都接收的是字符族类型的参数(CHR除外)并且返回字符值.除了特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的,比如: VARCHAR2数值被限制为2000字符(ORACLE 8中为4000字符),而CHAR数值被限制为255字符(在ORACLE8中是2000).当在过程性语句中使用时,它们可以被赋值给VARCHAR2或者CHAR类型的PL/SQL变量.
ASCII
语法：ascii(char c)
功能：返回一个字符串的第一个字符的ASCII码，他的逆函数是CHR()
使用位置：过程性语句和SQL语句。
select ascii('罗') from dual;
select chr(49886) from dual;
CHR
语法: chr(x)
功能：返回在数据库字符集中与X拥有等价数值的字符。CHR和ASCII是一对反函数。经过CHR转换后的字符再经过ASCII转换又得到了原来的字符。
使用位置：过程性语句和SQL语句。
CONCAT
语法： CONCAT（c1,c2）
功能： c1,c2均为字符串，函数将c2连接到c1的后面，如果c1为null,将返回c2.如果c2为null,则返回c1，如果c1、c2都为null，则返回null,他和操作符||返回的结果相同.
使用位置：过程性语句和SQL语句。
INITCAP
语法：INITCAP（string）
功能：返回字符串的每个单词的第一个字母大写而单词中的其他字母小写的string。单词是用.空格或给字母数字字符由空格，控制字符，标点符号进行分隔。不是字母的字符不变动。
使用位置：过程性语句和SQL语句。
select INITCAP('luo,jia,you')from dual;
select INITCAP('luo jia you')from dual;
LTRIM
语法：LTRIM（string1,string2）
功能：返回删除从左边算起出现在string2中的字符的string1。String2被缺省设置为单个的空格。数据库将扫描string1，从最左边开始。当遇到不在string2中的第一个字符，结果就被返回了。LTRIM的行为方式与RTRIM很相似。
使用位置：过程性语句和SQL语句。
LOWER
语法：LOWER(string)
功能：返回字符串,并将所有的字符小写
使用位置：过程性语句和SQL语句。
NLS_INITCAP
语法：NLS_INITCAP（string[,nlsparams]）
功能：返回字符串每个单词第一个字母大写而单词中的其他字母小写的string，nlsparams
指定了不同于该会话缺省值的不同排序序列。如果不指定参数，则功能和INITCAP相同。Nlsparams可以使用的形式是：
‘NLS_SORT=sort’
这里sort制订了一个语言排序序列。
使用位置：过程性语句和SQL语句。
NLS_LOWER
语法：NLS_LOWER（string[,nlsparams]）
功能：返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。
Nlsparams参数的形式与用途和NLS_INITCAP中的nlsparams参数是相同的。如果nlsparams没有被包含，那么NLS_LOWER所作的处理和LOWER相同。
使用位置；过程性语句和SQL语句。
NLS_UPPER
语法：nls_upper（string[,nlsparams]）
功能：返回字符串中的所有字母都是大写的形式的string。不是字母的字符不变。nlsparams参数的形式与用途和NLS_INITCAP中的相同。如果没有设定参数，则NLS_UPPER功能和UPPER相同。
使用位置：过程性语句和SQL语句。
REPLACE
语法：REPLACE（string，search_str[,replace_str]）
功能：把string中的所有的子字符串search_str用可选的replace_str替换，如果没有指定replace_str，所有的string中的子字符串search_str都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。
使用位置：过程性语句和SQL语句。
RPAD
语法：RPAD（string1,x[,string2]）
功能：返回在X字符长度的位置上插入一个string2中的字符的string1。如果string2的长度要比X字符少，就按照需要进行复制。如果string2多于X字符，则仅string1前面的X各字符被使用。如果没有指定string2，那么使用空格进行填充。X是使用显示长度可以比字符串的实际长度要长。RPAD的行为方式与LPAD很相似，除了它是在右边而不是在左边进行填充。
使用位置：过程性语句和SQL语句。
LPAD
语法：LPAD（string1,x[,string2]）
功能：返回在X字符长度的位置上插入一个string2中的字符的string1。如果string2的长度要比X字符少，就按照需要进行复制。如果string2多于X字符，则仅string1前面的X各字符被使用。如果没有指定string2，那么使用空格进行填充。X是使用显示长度可以比字符串的实际长度要长。RPAD的行为方式与LPAD很相似，除了它是在右边而不是在左边进行填充。
String1,string2均为字符串，x为整数。在string1的左侧用string2字符串补足致长度x,可多次重复，如果x小于string1的长度，那么只返回string1中左侧x个字符长的字符串，其他的将被截去。String2的缺省值为单空格
使用位置：过程性语句和SQL语句。
select LPAD('123',8,'0') from dual; -- 00000123
select LPAD('123456789',8,'0') from dual; --12345678
RTRIM
语法: RTRIM（string1,[,string2]）
功能: 返回删除从右边算起出现在string1中出现的字符string2. string2被缺省设置为单个的空格.数据库将扫描string1,从右边开始.当遇到不在string2中的第一个字符,结果就被返回了RTRIM的行为方式与LTRIM很相似.
使用位置：过程性语句和SQL语句。
SOUNDEX
语法: SOUNDEX（string）
功能: 返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助. 返回与string发音相似的词
使用位置：过程性语句和SQL语句。
SUBSTR
语法: SUBSTR（string,a[,b]）
功能: 返回从字母为值a开始b个字符长的string的一个子字符串.如果a是0,那么它就被认为从第一个字符开始.如果是正数,返回字符是从左边向右边进行计算的.如果b是负数,那么返回的字符是从string的末尾开始从右向左进行计算的.如果b不存在,那么它将缺省的设置为整个字符串.如果b小于1,那么将返回NULL.如果a或b使用了浮点数,那么该数值将在处理进行以前首先被却为一个整数.
使用位置：过程性语句和SQL语句。
SUBSTRB
语法: SUBSTRB（string,a[,b]）
功能: 与SUBSTR大致相同，只是a,b是以字节计算
使用位置：过程性语句和SQL语句。
TRANSLATE
语法: TRANSLATE(string,from_str,to_str)
功能: 返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的string. TRANSLATE是REPLACE所提供的功能的一个超集.如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string中被删除,因为它们没有相应的替换字符. to_str不能为空.Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是NULL.
使用位置：过程性语句和SQL语句。
select TRANSLATE('fumble','umf','abc') test from dual; --cabble
select TRANSLATE('fumble','fu','abcdd') test from dual; --abmble
TRIM
语法: TRIM(string)
功能: 删除string字符串前后的空格
使用位置：过程性语句和SQL语句。
UPPER
语法: UPPER（string）
功能: 返回大写的string.不是字母的字符不变.如果string是CHAR数据类型的,那么结果也是CHAR类型的.如果string是VARCHAR2类型的,那么结果也是VARCHAR2类型的.
使用位置: 过程性语句和SQL语句。
F.2字符函数——返回数字
这些函数接受字符参数回数字结果.参数可以是CHAR或者是VARCHAR2类型的.尽管实际下许多结果都是整数值,但是返回结果都是简单的NUMBER类型的,没有定义任何的精度或刻度范围.
ASCII
语法: ASCII（string）
功能: 数据库字符集返回string的第一个字节的十进制表示.请注意该函数仍然称作为ASCII.尽管许多字符集不是7位ASCII.CHR和ASCII是互为相反的函数.CHR得到给定字符编码的响应字符. ASCII得到给定字符的字符编码.
使用位置: 过程性语句和SQL语句。
INSTR
语法: INSTR（string1, string2[a,b]）
功能: 得到在string1中包含string2的位置. string1时从左边开始检查的,开始的位置为a,如果a是一个负数,那么string1是从右边开始进行扫描的.第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置.如果string2在a和b的规定下没有找到,那么返回0.位置的计算是相对于string1的开始位置的,不管a和b的取值是多少.
使用位置: 过程性语句和SQL语句。
INSTRB
语法: INSTRB（string1, string2[a,[b]]）
功能: 和INSTR相同,只是操作的对参数字符使用的位置的是字节.
使用位置: 过程性语句和SQL语句。
LENGTH
语法: LENGTH（string）
功能: 返回string的字节单位的长度.CHAR数值是填充空格类型的,如果string由数据类型CHAR,它的结尾的空格都被计算到字符串长度中间.如果string是NULL,返回结果是NULL,而不是0.
使用位置: 过程性语句和SQL语句。
LENGTHB
语法: LENGTHB（string）
功能: 返回以字节为单位的string的长度.对于单字节字符集LENGTHB和LENGTH是一样的.
使用位置: 过程性语句和SQL语句。
NLSSORT
语法: NLSSORT（string[,nlsparams]）
功能: 得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同数据库之间就保持了一致性. Nlsparams的作用和NLS_INITCAP中的相同.如果忽略参数,会话使用缺省排序.
使用位置: 过程性语句和SQL语句。
F.3数字函数
函数接受NUMBER类型的参数并返回NUMBER类型的数值.超越函数和三角函数的返回值精确到36位.ACOS、ASIN、ATAN、ATAN2的结果精确到36位.
ABS
语法: ABS(x)
功能: 得到x的绝对值.
使用位置: 过程性语言和SQL语句。
ACOS
语法: ACOS(x)
功能: 返回x的反余弦值. x应该从0到1之间的数,结果在0到pi之间,以弧度为单位.
使用位置: 过程性语言和SQL语句。
ASIN
语法: ASIN(x)
功能: 计算x的反正弦值. X的范围应该是－1到1之间,返回的结果在－pi/2到pi/2之间,以弧度为单位.
使用位置: 过程性语言和SQL语句。
ATAN
语法: ATAN(x)
功能: 计算x的反正切值.返回值在－pi/2到pi/2之间,单位是弧度.
使用位置: 过程性语言和SQL语句。
ATAN2
语法: ATAN2(x,y)
功能: 计算x和y的反正切值.结果在负的pi/2到正的pi/2之间,单位是弧度.
使用位置: 过程性语言和SQL语句。
CEIL
语法: CEIL(x)
功能: 计算大于或等于x的最小整数值.
使用位置: 过程性语言和SQL语句。
COS
语法: COS(x)
功能: 返回x的余弦值. X的单位是弧度.
使用位置: 过程性语言和SQL语句。
COSH
语法: COSH(x)
功能: 计算x的双曲余弦值.
EXP
语法: EXP(x)
功能: 计算e的x次幂. e为自然对数,约等于2.71828.
使用位置: 过程性语言和SQL语句。
FLOOR
语法: FLOOR(x)
功能: 返回小于等于x的最大整数值.
使用位置: 过程性语言和SQL语句。
LN
语法: LN(x)
功能: 返回x的自然对数. x必须是正数,并且大于0
使用位置: 过程性语言和SQL语句。
LOG
语法: LOG(x,y)
功能: 计算以x为底的y的对数 .x必须大于0而且不等于1, y为任意正数.
使用位置: 过程性语言和SQL语句。
MOD
语法: MOD(x,y)
功能: 返回x除以y的余数.如果y是0,则返回x
使用位置: 过程性语言和SQL语句。
POWER
语法: POWER(x,y)
功能: 计算x的y次幂.
使用位置: 过程性语言和SQL语句。
ROUND
语法: ROUND(x[,y])
功能: 计算保留到小数点右边y位的x值. y缺省设置为0,这会将x保留为最接近的整数.如果y小于0,保留到小数点左边相应的位. Y必须是整数.进行四舍五入。
使用位置: 过程性语言和SQL语句。
SIGN
语法: SIGN(x)
功能: 获得x的符号位标志.如果x<0返回－1.如果x=0返回0.如果x>0返回1.
使用位置: 过程性语言和SQL语句。
SIN
语法:SIN(x)
功能:计算x的正弦值. X是一个以弧度表示的角度.
使用位置: 过程性语言和SQL语句。
SINH
语法:SINH(x)
功能:返回x的双曲正弦值.
使用位置: 过程性语言和SQL语句。
SQRT
语法: SQRT(x)
功能: 返回x的平方根. x必须是正数.
使用位置: 过程性语言和SQL语句。
TAN
语法: TAN(x)
功能: 计算x的正切值, x是一个以弧度位单位的角度.
使用位置: 过程性语言和SQL语句。
TANH
语法: TANH(x)
功能: 计算x的双曲正切值.
使用位置: 过程性语言和SQL语句。
TRUNC
语法: TRUNC(x[,y])
功能: 计算截尾到y位小数的x值. y缺省为0,结果变为一个整数值.如果y是一个负数,那么就截尾到小数点左边对应的位上. 只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
使用位置: 过程性语言和SQL语句。
F.4日期函数
日期函数接受DATE类型的参数.除了MONTHS_BETWEEN函数返回的是NUMBER类型的结果,所有其他的日期函数返回的都是DATE类型的数值.
ADD_MONTHS
语法: ADD_MONTHS(d,x)
功能: 返回日期d加上x个月后的月份。x可以是任意整数。如果结果日期中的月份所包含的天数比d日期中的“日”分量要少。（即相加后的结果日期中的日分量信息已经超过该月的最后一天，例如，8月31日加上一个月之后得到9月31日，而9月只能有30天）返回结果月份的最后一天。
使用位置: 过程性语言和SQL语句。
LAST_DAY
语法:LAST_DAY(d)
功能:计算包含日期的d的月份最后一天的日期.这个函数可以用来计算当月中剩余天数.
使用位置: 过程性语言和SQL语句。
MONTHS_BETWEEN
语法: MONTHS_BETWEEN(date 1,date2)
功能: 计算date 1和date2之间月数.如果date 1,date2这两个日期中日分量信息是相同的,或者这两个日期都分别是所在月的最后一天,那么返回的结果是一个整数,否则包括一个小数,小数为富余天数除以31.
使用位置: 过程性语言和SQL语句。
NEW_TIME
语法: NEW_TIME(d,zone1,zone2)
功能: 计算当时区zone1中的日期和时间是s时候,返回时区zone2中的日期和时间. zone1和zone2是字符串.
使用位置: 过程性语言和SQL语句。
NEXT_DAY
语法: NEXT_DAY(d,string)
功能: 计算在日期d后满足由string给出的条件的第一天. String使用位置;当前会话的语言指定了一周中的某一天.返回值的时间分量与d的时间分量是相同的. String的内容可以忽略大小写.
使用位置: 过程性语言和SQL语句。
ROUND
语法: ROUND(d[,format])
功能: 将日期d按照由format指定的格式进行处理.如果没有给format则使用缺省设置`DD`.
使用位置: 过程性语言和SQL语句。
SYSDATE
语法: SYSDATE
功能: 取得当前的日期和时间,类型是DATE.它没有参数.但在分布式SQL语句中使用时,SYSDATE返回本地数据库的日期和时间.
使用位置: 过程性语言和SQL语句。
TRUNC
语法: TRUNC(d,format)
功能: 计算截尾到由format指定单位的日期d.可以使用位置:格式和效果.缺省参数同ROUNG.
使用位置: 过程性语言和SQL语句。
F.5转 换 函 数
转换函数用于在PL/SQL数据类型之间进行转换.PL/SQL尽可能地自动进行转换,也就是采用隐含方式转换.隐含转换无法转换格式信息,并且有些类型的数据之间不支持隐含转换,所以对这些可以采用显示转换.使用显示转换也是一种好的编程习惯
.
CHARTOROWID
语法: CHARTOROWID(string)
功能: 把包含外部格式的ROWID的CHAR或VARCHAR2数值转换为内部的二进制格式.参数string必须是包含外部格式的ROWID的18字符的字符串.oracle7和 oracle8中的外部格式是不同的.CHARTOROWID是ROWIDTOCHAR的反函数.
使用位置: 过程性语言和SQL语句。
CONVERT
语法: CONVERT(string,dest_set[,source_set])
功能: 将字符串string从source_set所表示的字符集转换为由dest_set所表示的字符集.如果source_set没有被指定,它缺省的被设置为数据库的字符集.
使用位置: 过程性语言和SQL语句。
HEXTORAW
语法: HEXTORAW(string)
功能: 将由string表示的二进制数值转换为一个RAW数值. String应该包含一个十六进制的数值. String中的每两个字符表示了结果RAW中的一个字节..HEXTORAW和RAWTOHEX为相反的两个函数.
使用位置: 过程性语言和SQL语句。
RAWTOHEX
语法: RAWTOHEX(rawvalue)
功能: 将RAW类数值rawvalue转换为一个相应的十六进制表示的字符串. rawvalue中的每个字节都被转换为一个双字节的字符串. RAWTOHEX和HEXTORAW是两个相反的函数.
使用位置: 过程性语言和SQL语句。
ROWIDTOCHAR
语法: ROWIDTOCHAR(rowid)
功能: 将ROWID类型的数值rowid转换为其外部的18字符的字符串表示,在oracle7和oracle8之间有些不一样的地方. ROWIDTOCHAR和CHARTOROWID是两个相反的函数.
使用位置: 过程性语言和SQL语句。
TO_CHAR(dates)
语法: TO_CHAR(d [,format[,nlsparams]])
功能: 将日期d转换为一个VARCHAR2类型的字符串.如果指定了format,那么就使用位置:它控制结果的方式.格式字符串是由格式元素构成的.第一个元素返回日期数值一个部份,例如日子.如果没有给定format,使用的就是该会话的缺省日期格式.如果指定了nlsparams,它就控制着返回字符串的月份和日分量信息所使用的语言. nlsparams的格式是:
“NLS_DATE_LANGUAGE”
使用位置: 过程性语言和SQL语句。
TO_CHAR(labels)
语法: TO_CHAR(labels[,format])
功能: 将MISLABEL的LABEL转换为一个VARCHAR2类型的变量.
使用位置: 在trusted数据库的过程性语句和SQL语句。
TO_CHAR(numbers)
语法: TO_CHAR(num[,format[,nlsparams]])
功能: 将NUMBER类型的参数num转换为一个VARCHAR2类型的变量.如果指定了format,那么它会控制这个转换处理.表5-5列除了可以使用的数字格式.如果没有指定format,它会控制这个转换过程.下面列出了可以使用的数字格式.如果没有指定format,那么结果字符串将包含和num中有效位的个数相同的字符. nlsparams用来指定小数点和千分位分隔符和货币符号.可以使用的格式:
`NLS_NUMERIC_CHARS=”dg”NLS_CURRENCY=”string”
d和g分别表示列小数点和千分位分隔符. String表示了货币的符号.例如,在美国小数点分隔符通常是一个句点(.),分组分隔符通常是一个逗号(,),而千分位符号通常是一个$.
使用位置: 过程性语言和SQL语句。
TO_DATE
语法: TO_DATE(String[,format[,nlsparams]])
功能: 把CHAR或者VARCHAR2类型的String转换为一个DATE类型的变量. format是一个日期格式字符串.当不指定format的时候,使用该会话的缺省日期格式.
使用位置: 过程性语言和SQL语句。
TO_LABEL
语法: TO_LABEL(String[,format])
功能: 将String转换为一个MLSLABEL类型的变量. String可以是VARCHAR2或者CHAR类型的参数.如果指定了format,那么它就会被用在转换中.如果没有指定format,那么使用缺省的转换格式.
使用位置: 过程性语言和SQL语句。
TO_MULTI_BYTE
语法: TO_MULTI_BYTE(String)
功能: 计算所有单字节字符都替位换位等价的多字节字符的String.该函数只有当数据库字符集同时包含多字节和单字节的字符的时候有效.否则, String不会进行任何处理. TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的两个函数.
使用位置: 过程性语言和SQL语句。
TO_NUMBER
语法: TO_NUMBER(String[,format[,nlsparams]])
功能: 将CHAR或者VARCHAR2类型的String转换为一个NUMBER类型的数值.如果指定了format,那么String应该遵循相应的数字格式. Nlsparams的行为方式和TO_CHAR中的完全相同.TO_NUMBER和TO_CHAR是两个相反的函数.
使用位置: 过程性语言和SQL语句。
TO_SINGLE_BYTE
语法: TO_SINGLE_BYTE(String )
功能: 计算String中所有多字节字符都替换为等价的单字节字符.该函数只有当数据库字符集同时包含多字节和单字节的字符的时候有效.否则, String不会进行任何处理.
TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的两个函数.
使用位置: 过程性语言和SQL语句。
F.6分 组 函 数
分组函数返回基于多个行的单一结果,这和单行函数正好形成对比,后者是对单行返回一个结果.这些函数仅仅对于查询的选择列表和GROUP BY子句有效.
这些函数大都可以接受对参数的修饰符.可以使用位置:的修饰符有DISTINCT和ALL.如果使用位置:了DISTINCT修饰符,那么在处理中仅仅会考虑由查询返回的不同的取值.ALL修饰符会使得该函数考虑由查询返回的所有数值.如果没有指定任何修饰符,那么缺省使用位置:的是ALL修饰符.
AVG
语法: AVG([DISTINCT| ALL]col)
功能: 返回一列数据的平均值.
使用位置: 查询列表和GROUP BY子句.
COUNT
语法: COUNT(*| [DISTINCT| ALL] col)
功能: 得到查询中行的数目.如果使用了*获得行的总数.如果在参数中传递的是选择列表,那么计算的是非空数值.
GLB
获得由label界定的最大下界.函数仅用于trusted oracle.GLB
语法: GLB ([DISTINCT| ALL]label)
功能: 获得由label界定的最大下界.函数仅用于trusted oracle.
使用位置:trusted数据库的选择列表和GROUP BY子句.
LUB
语法: LUB ([DISTINCT| ALL]label)
功能: 获得由label界定的最小上界.用于trusted oracle.数据库.
使用位置: trusted数据库的选择列表和GROUP BY子句.
过程性语言和SQL语句。
MAX
语法: MAX([DISTINCT| ALL]col)
功能: 获得选择列表项目的最大值.
使用位置: 仅用于查询选择和GROUP BY子句.
MIN
语法: MIN([DISTINCT| ALL]col)
功能: 获得选择列表的最小值.
使用位置: 仅用于查询选择和GROUP BY子句.
STDDEV
语法: STDDEV([DISTINCT| ALL]col)
功能: 获得选择列表的标准差.标准差为方差的平方根.
使用位置: 仅用于查询选择和GROUP BY子句.
SUM
语法:SUM([DISTINCT| ALL]col)
功能:返回选择的数值和总和
使用位置: 仅用于查询选择和GROUP BY子句.
VARIANCE
语法: VARIANCE([DISTINCT| ALL]col)
功能:返回选择列表项目的统计方差.
使用位置: 仅用于查询选择和GROUP BY子句.
F.7其 他 函 数
BFILENAME
语法: BFILENAME(directory,file_name)
功能: 获得操作系统中与物理文件file_name相关的BFILE位置指示符. directory必须是数据字典中的DIRECTORY类型的对象.
使用位置: 过程性语言和SQL语句。
DECODE
语法:
DECODE(base_expr,comparel,valuel,
Compare2,value2,
…
default)
功能: 把base_expr与后面的每个compare (n) 进行比较,如果匹配返回相应的value (n) .如果没有发生匹配,则返回default
使用位置: 过程性语言和SQL语句。
DUMP
语法:DUMP(expr[,number_format[,start_position][,length]])
功能:获得有关expr的内部表示信息的VARCHAR2类型的数值. number_format指定了按照下面返回数值的基数(base):
number_format 结果
8 八进制表示
10 十进制表示
16 十六进制表示
17 单字符
默认的值是十进制.
如果指定了start_position和length,那么返回从start_position开始的长为length的字节.缺省返回全部.
数据类型按照下面规定的内部数据类型的编码作为一个数字进行返回.
代码 数据类型
1 VARCHAR2
2 NUMBER
8 LONG
12 DATE
23 RAW
69 ROWID
96 CHAR
106 MLSLABEL
使用位置: SQL语句.
EMPTY_CLOB/EMPTY_BLOB
语法: EMPTY_CLOB
EMPTY_BLOB
功能: 获得一个空的LOB提示符 (locator) .EMOTY_CLOB返回一个字符指示符,而 EMPTY_BLOB返回一个二进制指示符.
使用位置: 过程性语言和SQL语句.
GREATEST
语法: GREATEST(expr1[,expr2]…)
功能: 计算参数中最大的表达式.所有表达式的比较类型以expr1为准.
返回一组表达式中的最大值,即比较字符的编码大小.
使用位置: 过程性语言和SQL语句.
select greatest(’AA’,’AB’,’AC’) from dual;
select greatest(1,2,5) from dual;
GREATEST_LB
语法: GREATEST_LB(label1[,label2]…)
功能: 返回标签(label)列表中最大的下界.每个标签必须拥有数据类型MLSLABEL、RAWMLSLABEL或者是一个表因字符串文字.函数只能用于truested oracle库.
使用位置: 过程性语言和SQL语句.
LEAST
语法: LEAST(expr1[,:expr2]…)
功能: 获得参数中最小的表达式.
使用位置: 过程性语言和SQL语句.
select least(’啊’,’安’,’天’) from dual;
select least(1,5,9) from dual;
LEAST_UB
语法: LEAST_UB(label1[,label2]…)
功能: 与GREATEST_UB函数相似,本函数返回标签列表的最小上界.
使用位置: 过程性语言和SQL语句.
NVL
语法: NVL (expr1, expr2)
功能: 如果expr1是NULL,那么返回expr2,否则返回expr1.
如果expr1不是字符串,那么返回值的数据类型和expr1是相同的,否则,返回值的数据类型是VARCHAR2.此函数对于检查并确定查询的活动集不包含NULL值十分有用.
使用位置: 过程性语言和SQL语句.
UID
语法:
功能: 获得当前数据库用的惟一标识,标识是一个整数.
使用位置: 过程性语言和SQL语句.
USER
语法:
功能: 取得当前oracle用户的名字,返回的结果是一个VARCHAR2型字符串.
使用位置: 过程性语言和SQL语句.
USERENV
语法: USERENV(option)
功能: 根据参数option,取得一个有关当前会话信息的VARCHAR2数值.
使用位置: 过程性语言和SQL语句.
VSIZE
语法: VSIZE(value)
功能: 获得value的内部表示的字节数.如果value是NULL,结果是NULL.
使用位置: 过程性语言和SQL语句.
F.8 sqlplus常用命令
SPOOL将屏幕所有的输出输出到指定文件
-- spool 文件路径名;
spool g:\mysql.sql;
--业务操作
--结束输出
spool off;
执行一个SQL脚本文件
我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。
--start file_name
-- @ file_name
start g:\mysql.sql;
@ g:\mysql.sql;
对当前的输入进行编辑
edit
ed
重新运行上一次运行的sql语句
/
显示一个表的结构
desc table_name ;
清屏
clear screen;
退出
exit;
置当前session是否对修改的数据进行自动提交
--SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
set autocommit on;
在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句
-- SET ECHO {ON|OFF};
set echo on;
是否显示当前sql语句查询或修改的行数
--SET FEED[BACK] {6|n|ON|OFF}
-- 默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数
set feedback 1;
是否显示列标题
--当set heading off 时，在每页的上面不显示列标题，而是以空白行代替
--SET HEA[DING] {ON|OFF}
set heading on;
设置一行可以容纳的字符数
-- 如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示
--SET LIN[ESIZE] {80|n}
set linesize 100;
设置页与页之间的分隔
-- SET NEWP[AGE] {1|n|NONE}
--当set newpage 0 时，会在每页的开头有一个小的黑方框。
--当set newpage n 时，会在页和页之间隔着n个空行。
--当set newpage none 时，会在页和页之间没有任何间隔
set newpage 1;
设置一页有多少行数
--如果设为0，则所有的输出内容为一页并且不显示列标题
--SET PAGES[IZE] {24|n}
set pagesize 20;
是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
--SET SERVEROUT[PUT] {ON|OFF}
set serveroutput on;
是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。
--在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，
--设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度
--SET TERM[OUT] {ON|OFF}
set termout off;
在dos里连接oracle数据库
CONNECT user_name/passwd@l_jiayou
在sql*plus中连接到指定的数据库
CONNECT user_name/passwd@数据库名称
显示当前用户
show user;
显示当前环境变量的值：
show all;
显示当前在创建函数、存储过程、触发器、包等对象的错误信息
Show error
显示数据库的版本：
--show REL[EASE]
show release
显示SGA的大小
show SGA
显示初始化参数的值：
--show PARAMETERS [parameter_name]
show parameters;
查看当前用户的缺省表空间
select username,default_tablespace from user_users
查看当前用户的角色
select * from user_role_privs
查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
查看用户下所有的表
select * from user_tables
查看名称包含log字符的表
select object_name,object_id from user_objects where instr(object_name,'LOG')>0;
查看某表的创建时间
select object_name,created from user_objects where object_name=upper('&table_name');
查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');
查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,'Y')>0;
查看索引个数和类别
select index_name,index_type,table_name from user_indexes order by table_name;
查看索引被索引的字段
select * from user_ind_columns where index_name=upper('&index_name');
查看索引的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');
查看序列号，last_number是当前值
select * from user_sequences;
查看视图的名称
--select view_name from user_views;
查看创建视图的select语句
select view_name,text_length from user_views;
set long 2000; 说明：可以根据视图的text_length值设定set long 的大小
select text from user_views where view_name=upper('&view_name');
查看同义词的名称
select * from user_synonyms
查看某表的约束条件
select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;
查看函数和过程
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
查看函数和过程的源代码
select text from all_source where owner=user and name=upper('&plsql_name');
查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
查看控制文件
select name from v$controlfile;
查看日志文件
select member from v$logfile;
查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
查看数据库的版本
Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';
查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
用系统管理员，查看当前数据库有几个用户连接：
select username,sid,serial# from v$session;
如果要停某个连接用
alter system kill session 'sid,serial#';
如果这命令不行,找它UNIX的进程数
select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
--说明：21是某个连接的sid数,然后用 kill 命令杀此进程号。
例子：
表test122,有两个字段t_id varchar2(20),t_name varchar2(10);
要求t_id的值为当天日期加上0001,0002的形式递加作为序列，如20070209_0001,200709_0002；
思路：查讯当天的t_id的最大值加1,然后生成序列；
insert into test122 values
(to_char(sysdate,'yyyymmdd')||'_'||(select lpad(to_number(ltrim(substr(max(t_id),length(max(t_id))-3),'0'))+1,4,0)
from test122 where substr(t_id,0,length(t_id)-5)=to_char(sysdate,'yyyymmdd')),'ok');
树形递归查询：Start with...Connect By
准备：
create table mymenu(tree_id varchar(10),tree_pid varchar(10),tree_lable varchar(50),tree_link varchar(100))
insert into mymenu values('1','0','蔬菜','')
insert into mymenu values('2','0','水果','')
insert into mymenu values('3','0','谷物','')
insert into mymenu values('4','0','肉类','')
insert into mymenu values('5','1','白菜','')
insert into mymenu values('6','1','茄子','htt://www.baidu.com')
insert into mymenu values('7','5','四月白','http://www.google.cn')
insert into mymenu values('8','5','冬白菜','htt://www.baidu.com')
insert into mymenu values('9','2','西瓜','http://www.google.cn')
insert into mymenu values('10','2','桔子','htt://www.baidu.com')
insert into mymenu values('11','3','大米','http://www.google.cn')
insert into mymenu values('12','3','大豆','htt://www.baidu.com')
insert into mymenu values('13','4','猪肉','http://www.google.cn')
insert into mymenu values('14','4','鱼','')
insert into mymenu values('15','14','昌鱼','http://www.google.cn')
insert into mymenu values('16','14','王八','htt://www.baidu.com')
从根往树末梢查询：
select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid;//查询所有
select * from mymenu start with tree_id='1' connect by prior tree_id=tree_pid; //查询指定ID
从树末梢向根查询：
select * from mymenu start with tree_pid='0' connect by prior tree_pid=tree_id
select * from mymenu start with tree_id='8' connect by prior tree_pid=tree_id
如果还有其他条件用and 加在语句后面
select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid and tree_link is null
select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid and tree_link is not null
F.9 oracle客户端连接的文件配置：
oracle的目录/network/ADMIN/tnsnames.ora
内容：
MIMI(客户端连接的名称) =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = NTDB.RUNNER)
)
)
---------------------------------------end-------------------------------------------------
F.10修改表结构
alter table m_gl_gls3_history add (aaaaa varchar2(20),bbbbb varchar2(10))
alter table m_gl_gls3_history modify (aaaaa varchar2(10))
--要改变表中的字段的类型或缩小字段长度，该字段的所有记录值必须为空。
--如果改字段存在记录值，则该字段长度只能扩大，不能缩小。
alter table m_gl_gls3_history drop (aaaaa , bbbbb )
Aggregate Functions集合函数
1、COUNT(*)
2、COUNT([ALL | DISTINCT] expr)
3、MAX(expr)
4、MEDIAN(expr)
5、MIN(expr)
6、STDDEV(expr)
7、SUM(expr)
8、VARIANCE(expr)
注：ORACLE PLSQL内置函数VARIANCE和STDDEV的算法
PLSQL中提供了两个内置的统计函数VARIANCE和STDDEV，分别对应于方差和标准差，今天验证了一下，他们的算法如下：
VARIANCE：方差，数列中各项和平均值的差平方后求和，然后除以数列个数减一，得到的即为方差。
STDDEV：标准差，就是上面算出来的方差的开平方根
Character Functions字符串函数
1、CHR(n)
2、CONCAT(char1,char2)
3、INITCAP(char)
4、LOWER(char)
5、LPAD(expr1,n,expr2)
6、LTRIM(char,set)
7、NLS_INITCAP(char,'NLS_SORT=lang')
8、NLS_LOWER(char,'NLS_SORT=lang')
9、NLS_UPPER(char,'NLS_SORT=lang')
10、NLSSORT(char,'NLS_SORT=lang')
11、RPAD(expr1,n,expr2)
12、RTRIM(char,set)
13、SOUNDEX(char)
14、SUBSTR(char,position,substring_length)
15、TRANSLATE(expr,from_string,to_string)
16、TRIM([LEADING | TRAILING | BOTH] trim_character FROM trim_source)
17、UPPER(char)
Conversion Functions转换函数
1、TO_CHAR(nchar | clob | nclob)
2、TO_CHAR(datetime,fmt,nlsparam)
3、TO_CHAR(n,fmt,nlsparam)
4、TO_CLOB(lob_column | char)
5、TO_DATE(datetime,fmt,nlsparam)
6、TO_TIMESTAMP(char,fmt,nlsparam)
7、NVL(value,default)
Date Formats时间日期格式
Date / Time Functions日期/时间函数
ADD_MONTHS(date,integer)
CURRENT_DATE
CURRENT_TIMESTAMP
SYSDATE
SYSTIMESTAMP
TO_CHAR(datetime,fmt,nlsparam)
TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS')
TO_TIMESTAMP(char,fmt,nlsparam)
TRUNC(date,fmt)
MONTHS_BETWEEN(date,date)
Numeric Format数值格式
Numeric Functions数值型函数
ABS(n)
CEIL(n)
EXP(n)
FLOOR(n)
LOG(n2,n1)
MOD(n2,n1)
POWER(n2,n1)
REMAINDER(n2,n1)
ROUND(n,integer)
TRUNC(n1,n2)
Pseudocolumns伪列
【摘要】 在select语句中也可以选择一些伪列(pseudocolumns),一些可用的伪列有： currval , nextval , level , rowid , rownum 等
1、COLUMN_VALUE
2、CONNECT_BY_ISCYCLE
3、CONNECT_BY_ISLEAF
4、CURRVAL
5、LEVEL
注：Oracle中树状结构查询中level的使用
Oracle中使用connect by 来实现树状查询，其中可以使用level这个伪列来实现分层查询。
具体使用如下：
一张表menu记录菜单的层级情况。表结构如下：
menu_id number,
parent_id number,
menu_name nvarchar2(20)
首先select * from menu connect by menu_id = parent_id start with menu_id = 1;
这样可以看到Oracle以树状结构产生结果。某些时候如果我只想要第2层的菜单项如何做呢。
select * from
(select level,menu_id,parent_id,menu_name
from menu
conncet by menu_id = parent_id
start with menu_id = 1
)
where level = 2
这样就可以得到层级为第二层的菜单项。这样的SQL写法同样适合rownum伪列，Oracle中实现TOP n查询就是如此写的。
NEXTVAL
OBJECT_ID
OBJECT_ID
OBJECT_VALUE
ORA_ROWSCN
ROWID
ROWNUM
XMLDATA
1.ASCII 返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A_1 ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR 给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZHAO CHR65
-- -
赵 A
3.CONCAT 连接两个字符串;
SQL> select concat('010-','88888888')||'转23' 张三电话 from dual;
张三电话
----------------
010-88888888转23
4.INITCAP 返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
---------
9
6.LENGTH 返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from .nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
张三 3 杭州市西湖区 6 9999.99 7
7.LOWER 返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER 返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace('he love you','he','i') from dual;
REPLACE('HELOVEYOU','HE','I')
------------------------------
i love you
13.SOUNDEX 返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s' from 'string')
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
15.ABS 返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
16.ACOS 给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
17.ASIN 给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.ATAN 返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816
19.CEIL 返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
20.COS 返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
21.COSH 返回一个数字反余弦值
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598
22.EXP 返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
23.FLOOR 对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
24.LN 返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999
25.LOG(n1,n2) 返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
#p#26.MOD(n1,n2) 返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
27.POWER 返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
29.SIGN 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0
30.SIN 返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
31.SIGH 返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
32.SQRT 返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
33.TAN 返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月 -04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.dd')) mon_betw from dual;
MON_BETW
---------
-60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE 用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
42.CHARTOROWID 将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
#p#43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
conver
------
strutz
44.HEXTORAW 将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT 将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR 将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41
48.TO_DATE(string,'format') 将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('高') from dual;
TO
--
张
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;
YEAR
---------
1999
51.BFILENAME(dir,file)指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source') 将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,'none',
3 2,'insert',
4 3,
5 'select',
6 6,'update',
7 7,'delete',
8 8,'drop',
9 'other') cmd from v$session where type!='background';
SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天
56.LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25
58.USER
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
152
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM
#p#60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL) 求协方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUP BY 主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400
66.HAVING 对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having nt(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by tno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY 用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950
oralce 常用函数
1.单行函数
function_name(column|expression,[arg1,arg2,...])
-接收参数并返回一个结果
-每行返回一个结果
-可以改变数据类型
-能被嵌套
-可以用于SELECT,WHERE和ORDER BY子句
-包括：字符函数，数值函数，日期函数，转换函数，通用函数
2.字符函数
-包括：大小写转换函数，字符处理函数
1)大小写转换函数
函数 结果
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
例：
SELECT empno,ename,deptno
FROM emp
WHERE ename=UPPER('blake');
结果：
EMPNO ENAME DEPTNO
7698 BLAKE 30
2)字符串处理函数
函数 结果
CONCAT('Good','String') GoodString
SUBSTR('String',1,3) Str
LENGTH('String') 6
INSTR('String','r') 3
LPAD(sal,10,'*') ******5000
RPAD(sal,10,'*') 5000******
TRIM('S' FROM 'SSMITH') MITH
例：
SELECT ename,CONCAT(ename,job),LENGTH(ename),INSTR(ename,'A')
FROM emp
WHERE SUBSTR(job,1,5)='SALES';
结果：
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')
ALLEN ALLENSALESMAN 5 1
WARD WARDSALESMAN 4 2
MARTIN MARTINSALESMAN 6 2
TURNER TURNERSALESMAN 6 0
3.数值函数
1) ROUND：四舍五入到指定的小数位
-ROUND(45.926,2) 45.93
例：
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)
FROM dual;
结果：
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
45.92 46 50
2)TRUNC：截取到指定的小数位
-TURNC(45.926) 45.92
例：
SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,0)
FROM dual;
结果：
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
45.92 45 40
3)MOD：取余数
MOD(1600,300) 100
例：
SELECT ename,sal,comm,MOD(sal,comm)
FROM emp
WHERE job='SALESMAN';
结果：
ENAME SAL COMM MOD(SAL,COMM)
ALLEN 1600 300 100
WARD 1250 500 250
MARTIN 1250 1400 1250
TURNER 1500 0 1500
4.日期函数
-Orale是以一种内部的数值形式存储日期的，即:世纪、年、月、日、小时、分、秒
-默认日期形式是：DD-MON-RR
-SYSDATE是一个可以返回当前系统日期和时间的函数
-DUAL是一个虚拟表用于查看SYSDATE
-对一个日期型数据加上或减去一个数可以得到一个新的日期型数据
-两个日期型数据相减得到这两个日期的间隔天数
-如要以小时相加则必须用小时数除以24得到的数据进行相加
例：
SELECT ename,(SYSDATE-hiredate)/7 WEEKS
FROM emp
WHERE deptno=10;
结果：
ENAME WEEKS
CLARK 1434.49271
KING 1411.49271
MILLER 1401.92128
5.RR日期格式
| 如果指定两位数年份是：
| 0-49 | 50-99
如果当前年份| 0-49 | 返回日期是当前世纪的日期 | 返回日期是上个世纪的日期
的两位数是：| 50-99| 返回日期是下个世纪的日期 | 返回日期是当前世纪的日期
例：
当前年份 指定的日期 RR格式 YY格式
1995 27-OCT-95 1995 1995
1995 27-OCT-17 2017 1917
2001 27-OCT-17 2017 2017
2001 27-OCT-95 1995 2095
6.日期函数
1)MONTHS_BETWEEN：两个日期之间间隔多少个月
MONTHS_BETWEEN('01-9月-95','11-9月-94')
结果：11.6774194
2)ADD_MONTHS：向一个日期数据加一定的月份
ADD_MONTHS('11-1月-94',6)
结果：11-7月 -94
3)NEXT_DAY：某个指定日期之后的一周内某天
NEXT_DAY('01-8月-07',1)
结果：05-8月 -07
注：第二个参数表示下周的第几天，周日是第一天
4)LAST_DAY：返回某月的最后一天
LAST_DAY('01-9月-95')
结果：30-9月 -95
5)ROUND：对日期进行四舍五入
ROUND('25-7月-95','MONTH')
结果：01-8月-95
ROUND('25-7月-95','YEAR')
结果：01-1月-96
6)TRUNC：对日期进行截取
TRUNC('25-7月-95','MONTH')
结果：01-7月-95
TRUNC('25-7月-95','YEAR')
结果：01-1月-95
例：
SELECT empno,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) TENURE,
ADD_MONTHS(hiredate,6) REVIEW,
NEXT_DAY(hiredate,2),LAST_DAY(hiredate)
FROM emp
WHERE MONTHS_BETWEEN(SYSDATE,hiredate)<250;
7.转换函数
-隐式数据类型转换：
赋值语句中，oralce服务器自动完成以下转换
从 到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
-显式数据类型转换：
1)TO_CHAR用于日期型：TO_CHAR(date,'fmt')
日期格式模型：
-必须用单引号引起来
-可以包含任何有效的日期元素
-使用逗号与日期型数据分隔开
日期格式模型的元素：
YYYY----完整的年份数字表示
YEAR----年份的英文表示
MM------用两位数字来表示月份
MONTH---月份的英文表示
DY------用3个英文字符所写来表示星期几
DAY-----星期几完整的英文表示
例1：用时间元素格式化日期的时间部分
HH24:MI:SS:AM 15:45:32:PM
例2：通过使用双引号可以添加字符串
DD "of" MONTH 12 of 8月
例3：
SELECT ename,TO_CHAR(hiredate,'yyyy-mm-dd') HIREDATE
FROM emp
结果：
ENAME HIREDATE
SMITH 1980-12-17
ALLEN 1981-02-20
2)TO_CHAR用于数值型：TO_CHAR(number,'fmt')
通过在TO_CHAR中使用以下形式可以把数值型数据转换成变长的字符串
9------一位数字
0------显示前导零
$------显示美元符号
L------显示本地货币号
.------显示小数点
,------显示千位符
例：
SELECT TO_CHAR(sal,'$99,999') SALARY
FROM emp
WHERE ename='SCOTT';
结果：
SALARY
$3,000
3)TO_NUMBER和TO_DATE函数
-TO_NUMBER将一个字符串转换成数值型数据：TO_NUMBER(char)
-TO_DATE将一个字符串转换成日期型数据:TO_DATE(char[,'fmt'])
8.NVL函数
1)将NULL转换成为一个实际的值
-数据类型可以是date、character、number
2)数据类型必须匹配
NVL(comm,0)
NVL(hiredate,'01-7月-97')
NVL(job,'No Job Yet')
例：
SELECT ename,sal,comm,(sal*12)+NVL(comm,0)
FROM emp;
结果：
ENAME SAL COMM (SAL*12)+NVL(COMM,0)
SMITH 800 9600
ALLEN 1600 300 19500
WARD 1250 500 15500
9.DECODE函数
使用DECODE函数更便于完成诸如CASE或IF-THEN-ELSE语句的条件查询:
DECODE(col/expression,search1,result1
[,search2,result2,...,]
[,default])
例1：
SELECT job,sal,DECODE(job,'ANALYST',SAL*1.1,
'CLERK',SAL*1.15,
'MANAGER',SAL*1.20,
SAL) REVISED_SALARY
FROM emp;
结果:
JOB SAL REVISED_SALARY
CLERK 800 920
SALESMAN 1250 1250
MANAGER 2975 3570
例2：显示部门编号为30的每个雇员相应的税率
SELECT ename,sal,DECODE(TRUNC(sal/1000,0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) TAX_RATE
FROM emp
WHERE deptno=30;
结果：
ENAME SAL TAX_RATE
ALLEN 1600 .09
WARD 1250 .09
MARTIN 1250 .09
BLAKE 2850 .2
TURNER 1500 .09
JAMES 950 0
10.函数的嵌套：
例：
SELECT ename, NVL(TO_CHAR(mgr),'No Manager')
FROM emp
WHERE mgr IS NULL;
结果：
ENAME NVL(TO_CHAR(MGR),'NOMANAGER')
KING No Manager
练习
1.查询并显示雇员名。要求首字母大写，其他字母小写，显示名字的长度，只选择雇员名开始字母是J、A或M的雇员。用雇员名排序结果
SELECT INITCAP(ename),LENGTH(ename)
FROM emp
WHERE ename LIKE 'J%'
OR ename LIKE 'A%'
OR ename LIKE 'M%';
2.查询所有雇员名和薪水。薪水格式化为15个字符长度，用$左填充，列标签SALARY
SELECT ename,LPAD(sal,15,'$') SALARY
FROM emp
3.显示姓名以N结尾的雇员
SELECT *
FROM emp
WHERE UPPER(ename) LIKE '%N';
4.查询当前日期，列标题显示为Date
SELECT TO_CHAR(SYSDATE,'DD-MONTH-YYYY') "Date"
FROM dual;
5.对每一个雇员，显示其名字，并计算从雇员受雇日期到今天的月数，列标签MONTHS_WORKED。按受雇月数排序结果，四舍五入月数到最靠近的整数月
SELECT ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) MONTHS_WORKED
FROM emp;
6.查询雇员名和佣金。若雇员没有佣金，显示"No Commission"，列标签COMM
SELECT ename,decode(comm,
NULL,'No Commission',
comm) COMM
FROM emp;
或：
SELECT ename,NVL(to_char(comm),'No Commission') COMM
FROM emp;
7.显示雇员雇佣期满6个月后下一个星期五的日期。显示格式如02-4-1982，并按雇佣日期排序
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate,6),6),'DD-Month-YYYY')
FROM emp
ORDER BY hiredate;