原创

Oracle内置函数

字符函数-返回字符
这些函数全都接收的是字符类型的参数(CHR除外)并且返回字符值.除了特别说明的之外,这些函数大部分返回VARCHAR2类型的数值。当在过程性语句中使用时,它们可以被赋值给VARCHAR2或者CHAR类型的PL/SQL变量。
ASCII
语法:ascii(char c)
功能:返回一个字符串的第一个字符的ASCII码,他的逆函数是CHR()
使用位置:过程性语句和SQL语句。
select ascii('baa') from dual;结果为:98
select chr(98) from dual;结果为:'b'
ASCIISTR
语法:asciistr(str)
功能:返回字符串的规则表现形式,英文和数字变为规则的,中文则前面有’\’符号,返回unicode编码形式。UNISTR(str)函数是相反的过程,将unicode编码变为字符。
使用位置:过程性语句和SQL语句。
找出含有中文的字段:
SELECT ASCIISTR('恭喜你') FROM dual;结果为:\606D\559C\4F60
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;
select INITCAP('luo\jia\you') from dual;
LTRIM
语法:LTRIM (string1,string2)
功能:从string1中删除从string1左边算起出现在string2中的字符,然后返回删除后string1还剩下的字符。String2被缺省设置为单个的空格。数据库将扫描string1,从最左边开始。当遇到不在string2中的第一个字符,结果就被返回了。LTRIM的行为方式与RTRIM很相似。
使用位置:过程性语句和SQL语句。
ltrim(char,set) 从左边去除在set中的字符,默认是去除空格, 遇到不匹配结束,有一个null则结果为null
rtrim(char,set)右边,有一个为null,则结果为null

select ltrim('11asf你好975','0123456789') from dual;
SELECT ltrim('xyxXxyLAST WORD','xyX') from dual;
SELECT ltrim('xyxXxyLAST WORD','') from dual;
select ltrim(' xy',null) from dual;
LOWER
语法:LOWER(string)
功能:返回字符串,并将所有的字符小写
使用位置:过程性语句和SQL语句。
NLSINITCAP
语法:NLSINITCAP(string[,nlsparams])
功能:返回字符串每个单词第一个字母大写而单词中的其他字母小写的string,nlsparams指定了不同于该会话缺省值的不同排序序列。如果不指定参数,则功能和INITCAP相同。nlsparams可以使用的形式是:
NLSSORT=sort类型
这里sort制订了一个语言排序序列。
使用位置:过程性语句和SQL语句。
select nlsinitcap('hello guys','NLSSORT=BINARY') from dual;
NLSLOWER
语法:NLSLOWER(string[,nlsparams])
功能:返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。
nlsparams参数的形式与用途和NLSINITCAP中的nlsparams参数是相同的。如果nlsparams没有被包含,那么NLSLOWER所作的处理和LOWER相同。
使用位置:过程性语句和SQL语句。
NLSUPPER
语法:nlsupper(string[,nlsparams])
功能:返回字符串中的所有字母都是大写的形式的string。不是字母的字符不变。nlsparams参数的形式与用途和NLSINITCAP中的相同。如果没有设定参数,则NLSUPPER功能和UPPER相同。
使用位置:过程性语句和SQL语句。
NLSSORT
语法:nlssort(str,rule)
功能:对中文排序
按笔画排序:'NLSSORT=SCHINESESTROKEM'
按部首排序:'NLSSORT=SCHINESERADICALM'
默认拼音排序:'NLSSORT=SCHINESEPINYINM'
使用位置:过程性语句和SQL语句。
select zzmc from xtzzjg order by nlssort(zzmc,'NLSSORT=SCHINESEPINYINM');
REPLACE
语法:REPLACE(string,searchstr[,replacestr])
功能:把string中的所有的子字符串searchstr用可选的replacestr替换,如果没有指定replacestr,所有的string中的子字符串searchstr都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。(与transalte差别是子字符串,而且translate中str1长度大于str2的时候,会将不够替换的在源中删除,并且参数必须是三个,replace参数可以是两个)
使用位置:过程性语句和SQL语句。
RPAD
语法:RPAD(string1,x[,string2])向右填充函数
功能:返回一个string,这个是string结果是在string1右边填充string2直到长度length变为x。如果string2的长度要比X字符少,就按照需要进行复制。(比如string1+string2的长度还是小于x)如果string2多于X字符,则仅string1前面的X各字符被使用。如果没有指定string2,那么使用空格进行填充。X是使用显示长度可以比字符串的实际长度要长。如果x小于string1,那么按照要求从右边截断string1,取第1到剩下的。RPAD的行为方式与LPAD很相似,除了它是在右边而不是在左边进行填充。按照length来填充x,对于中文按环境,如果截断中文则不显示,但是长度认为x。
使用位置:过程性语句和SQL语句。
LPAD
语法:LPAD(string1,x[,string2])向左填充函数
功能:返回一个string,这个是string结果是在string1左边填充string2直到长度length变为x。如果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语句。
RERVSE
语法: RERVSE(string|数字)
功能: 按内部存储的字节翻转,对中文和数字可能有问题
使用位置:过程性语句和SQL语句。
SOUNDEX
语法: SOUNDEX(string)
功能: 返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助. 返回与string发音相似的词
使用位置:过程性语句和SQL语句。
SUBSTR
语法: SUBSTR(string,a[,b])--a是开始位置,b是返回的字符串长度,而不是截取的结束值。
功能: 返回从字母位置,即从a开始的b个字符长的string的一个子字符串.如果a是0,那么它就被认为从第一个字符开始.如果是a是正数,返回字符位置a是从左边向右边进行计算的.如果a是负数,那么返回的字符位置a是从string的末尾开始从右向左进行计算的.如果b不存在,那么它将缺省的设置为从a开始的整个字符串.如果b小于1,那么将返回NULL.如果a或b使用了浮点数,那么该数值将在处理进行以前首先被却为一个整数.(截断小数位,没有四舍五入)
使用位置:过程性语句和SQL语句。
select substr('asaa',0,2) from dual;
SUBSTRB
语法: SUBSTRB(string,a[,b])
功能: 与SUBSTR大致相同,只是a,b是以字节计算,取到不完整的中文,不显示中文
使用位置:过程性语句和SQL语句。
TRANSLATE
语法: TRANSLATE(string,src,dest)
功能: 返回将所出现的src中的每个字符替换为dest中的相应字符的string。TRANSLATE是REPLACE所提供的功能的一个超集。如果src比dest长,那么string中的字符在src中而不在dest中以外的字符将从string中被删除,其他替换成dest中字符(这样理解:src全部替换成dest,因为其他长度不够没有相应的替换字符,那么删除) src不能为空。Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是NULL。如果dest比src长,那么按顺序替换长度为src长度的字符,其他不替换。如果src中的字母位置在string中不是连续的,则按照string中位置替换。 主要用于替换和查找等。
使用位置:过程性语句和SQL语句。

select TRANSLATE('fumble','umf','abc') test from dual; --cabble
select TRANSLATE('fumble','fu','abcdd') test from dual; --abmble
TRIM
语法: TRIM(string)
功能: 删除string字符串前后的空格,若是trim(string1 from string)则是删除 select trim('a' from 'asdscsaa') from dual;结果:sdscs。
使用位置:过程性语句和SQL语句。
trim(str)直接去除首尾空格
trim(leading|trailing|both trimcharacter from strsource)去除首、尾或首尾
select trim(leading|trailing|both 'a' from 'asdada') from dual;
注意:
trimcharacter最多只有一个字符,不是字节。
如果没有leading、trailing、both关键字,且没有trimcharacter则不能有from,默认是both,去掉首尾。
如果没有trimcharacter,默认trimcharacter是空格,如果没有leading、trailing、both,则不可用from关键字。
如果trimcharacter和strsource有一个为null,结果为null。
UPPER
语法: UPPER(string)
功能: 返回大写的string。不是字母的字符不变。如果string是CHAR数据类型的,那么结果也是CHAR类型的。如果string是VARCHAR2类型的,那么结果也是VARCHAR2类型的。
使用位置: 过程性语句和SQL语句。
UNISTR
语法: UNISTR(string)
功能: 返回string unicode编码的对应字符串。如果有\字符,则有两个\转义。
使用位置: 过程性语句和SQL语句。
SELECT UNISTR('\606D\559C\4F60') FROM dual;恭喜你
SELECT UNISTR('\\606D\559C\4F60') FROM dual;\恭喜你
字符函数-返回数字
这些函数接受字符参数回数字结果。参数可以是CHAR或者是VARCHAR2类型的。
ASCII
语法: ASCII(string)
功能: 数据库字符集返回string的第一个字节的十进制表示。
使用位置: 过程性语句和SQL语句。
INSTR
语法: INSTR(string1, string2[,a,b])
功能: 得到在string1中包含string2的位置。a为正数,string1是从左边开始检查的,开始的位置为a,如果a是一个负数,那么string1是从右边开始进行扫描的,第b次出现的位置将被返回。a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置。如果a为0,则结果为0。
使用位置: 过程性语句和SQL语句。
select instr('acdress','s',1,1)from dual;6
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的作用和NLSINITCAP中的相同,如果忽略参数,会话使用缺省排序。
使用位置:过程性语句和SQL语句。
数字函数
函数接受NUMBER类型的参数并返回NUMBER类型的数值。
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语句。
过程中用xy也可以实现此功能,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语句。
日期函数
日期函数接受DATE类型的参数。除了MONTHSBETWEEN函数返回的是NUMBER类型的结果,所有其他的日期函数返回的都是DATE类型的数值。
日期-日期,返回间隔的天数
日期+number,返回日期
日期-number,返回日期
日期+日期 不正确
ADDMONTHS
语法: ADDMONTHS(d,x)
功能: 返回日期d加上x个月后的日期。x可以是任意整数。如果结果日期中的月份所包含的天数比d日期中的“日”分量要少。(即相加后的结果日期中的日分量信息已经超过该月的最后一天,例如,8月31日加上一个月之后得到9月31日,而9月只能有30天)返回结果月份的最后一天。其中x可以为正数也可以为负数。
使用位置: 过程性语言和SQL语句。
LASTDAY
语法:LASTDAY(d)
功能:计算包含日期的d的月份最后一天的日期。这个函数可以用来计算当月中剩余天数。
select lastday(sysdate)-sysdate from dual;
使用位置: 过程性语言和SQL语句。
MONTHSBETWEEN
语法: MONTHSBETWEEN(date 1,date2)
功能: 计算date 1和date2之间月数。如果date 1,date2这两个日期中日分量信息是相同的,或者这两个日期都分别是所在月的最后一天,那么返回的结果是一个整数,否则包括一个小数,小数为富余天数除以31。
使用位置: 过程性语言和SQL语句。
NEWTIME
语法: NEWTIME(d,zone1,zone2)
功能:计算当时区zone1中的日期和时间是d时候,返回时区zone2中的日期和时间。zone1和zone2是字符串。
使用位置: 过程性语言和SQL语句。
NEXTDAY
语法: NEXTDAY(d,string)
功能: 计算在日期d后满足由string给出的条件的第一天。string使用当前会话的语言指定一周中的某一天。返回值的时间分量与d的时间分量是相同的。 String的内容可以忽略大小写,其中string是星期表达,如nextday(sysdate,'Friday'),要和具体的nlsdatelanguage关联。
使用位置:过程性语言和SQL语句。
select nextday(sysdate,'星期日') from dual;
ROUND
语法: ROUND(d[,format])
功能: 将日期d按照由format指定的格式进行处理。如果没有给format则使用缺省设置DD。
使用位置:过程性语言和SQL语句。
SYSDATE
语法: SYSDATE
功能:取得当前的日期和时间,类型是DATE。
使用位置:过程性语言和SQL语句。
TRUNC
语法: TRUNC(d,format)
功能: 计算截尾到由format指定单位的日期d。缺省参数同ROUNG.
使用位置: 过程性语言和SQL语句。
转换函数
转换函数用于在PL/SQL数据类型之间进行转换。
CHARTOROWID
语法: CHARTOROWID(string)
功能: 把包含外部格式的ROWID的CHAR或VARCHAR2数值转换为内部的二进制格式。参数string必须是包含外部格式的ROWID的18字符的字符串。CHARTOROWID是ROWIDTOCHAR的反函数。
使用位置:过程性语言和SQL语句。
CONVERT
语法: CONVERT(string,destset[,sourceset])
功能: 将字符串string从sourceset所表示的字符集转换为由destset所表示的字符集。如果sourceset没有被指定,它缺省的被设置为数据库的字符集。
使用位置: 过程性语言和SQL语句。
select convert('中国','utf8') from dual;
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字符的字符串表示。ROWIDTOCHAR和CHARTOROWID是两个相反的函数。
使用位置:过程性语言和SQL语句。
TOCHAR(dates)
语法: TOCHAR(d [,format[,nlsparams]])
功能: 将日期d转换为一个VARCHAR2类型的字符串。格式字符串是由格式元素构成的。如果指定了nlsparams,它就控制着返回字符串的月份和日分量信息所使用的语言。nlsparams的格式是:
“NLSDATELANGUAGE”
使用位置: 过程性语言和SQL语句。
select tochar(sysdate,'yyyy-mm-dd','NLSDATELANGUAGE=AMERICAN') from dual;
TOCHAR(labels)
语法: TOCHAR(labels[,format])
功能: 将MISLABEL的LABEL转换为一个VARCHAR2类型的变量。
使用位置:在trusted数据库的过程性语句和SQL语句。
TOCHAR(numbers)
语法:TOCHAR(num[,format[,nlsparams]])
功能:将NUMBER类型的参数num转换为一个VARCHAR2类型的变量。如果没有指定format,那么结果字符串将包含和num中有效位的个数相同的字符。nlsparams用来指定小数点和千分位分隔符和货币符号。可以使用的格式:
NLSNUMERICCHARACTERS="dg"NLSCURRENCY="string"
d和g分别表示小数点和千分位分隔符。string表示了货币的符号。例如,在美国小数点分隔符通常是一个句点(.),千分位分隔符通常是一个逗号(,),而货币符号通常是一个$。
使用位置:过程性语言和SQL语句。
select tochar(1234.233,9999.99,'NLSNUMERICCHARACTERS=".,"NLSCURRENCY="¥"') from dual;
TODATE
语法: TODATE(string[,format[,nlsparams]])
功能: 把CHAR或者VARCHAR2类型的string转换为一个DATE类型的变量。format是一个日期格式字符串。当不指定format的时候,使用该会话的缺省日期格式。
使用位置:过程性语言和SQL语句。
TOMULTIBYTE
语法: TOMULTIBYTE(string)
功能:计算所有单字节字符都替位换位等价的多字节字符的string。该函数只有当数据库字符集同时包含多字节和单字节的字符的时候有效。否则,string不会进行任何处理。TOMULTIBYTE和TOSINGLEBYTE是相反的两个函数。
使用位置:过程性语言和SQL语句。
select tomultibyte('hello') from dual;
TONUMBER
语法:TONUMBER(string[,format[,nlsparams]])
功能:将CHAR或者VARCHAR2类型的string转换为一个NUMBER类型的数值。nlsparams的行为方式和TOCHAR中的完全相同。TONUMBER和TOCHAR是两个相反的函数。
使用位置:过程性语言和SQL语句。
TOSINGLEBYTE
语法:TOSINGLEBYTE(string)
功能:计算string中所有多字节字符都替换为等价的单字节字符。该函数只有当数据库字符集同时包含多字节和单字节的字符的时候有效。否则,string不会进行任何处理。TOMULTIBYTE和TOSINGLEBYTE是相反的两个函数。
使用位置:过程性语言和SQL语句。
分组函数
分组函数返回基于多个行的单一结果。
AVG
语法: AVG([DISTINCT|ALL]col)
功能: 返回一列数据的平均值.
使用位置:查询列表和GROUP BY子句。
COUNT
语法: COUNT(|[DISTINCT|ALL] col)
功能: 得到查询中行的数目。如果使用了获得行的总数。如果在参数中传递的是选择列表,那么计算的是非空数值。
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子句。
其他函数
BFILENAME
语法:BFILENAME(directory,filename)
功能:获得操作系统中与物理文件filename相关的BFILE位置指示符。directory必须是数据字典中的DIRECTORY类型的对象。
使用位置:过程性语言和SQL语句。
DECODE
语法:DECODE(baseexpr,compare1,value2,compare2,value2,…default)
功能:把baseexpr与后面的每个compare(n)进行比较,如果匹配返回相应的value(n)。如果没有发生匹配,则返回default。
使用位置:过程性语言和SQL语句。
DUMP
语法:DUMP(expr[,numberformat[,startposition][,length]])
功能:获得有关expr的内部表示信息的VARCHAR2类型的数值。numberformat指定了按照下面返回数值的基数(base):

numberformat 结果
8 八进制表示
10 十进制表示
16 十六进制表示
17 单字符
默认的值是十进制。
如果指定了startposition和length,那么返回从startposition开始的长为length的字节,缺省返回全部。
数据类型按照下面规定的内部数据类型的编码作为一个数字进行返回。

代码 数据类型
1 VARCHAR2
2 NUMBER
8 LONG
12 DATE
23 RAW
69 ROWID
96 CHAR
106 MLSLABEL
使用位置: SQL语句。
select dump(121213123,8,1,6) from dual;
EMPTYCLOB/EMPTYBLOB
语法: EMPTYCLOB()|EMPTYBLOB()
功能:获得一个空的LOB提示符 (locator)。EMOTYCLOB返回一个字符指示符,而EMPTYBLOB返回一个二进制指示符。
使用位置:过程性语言和SQL语句。
GREATEST
语法: GREATEST(expr1[,expr2]…)
功能: 计算参数中最大的表达式。所有表达式的比较类型以expr1为准。返回一组表达式中的最大值,即比较字符的编码大小。
使用位置:过程性语言和SQL语句。
select greatest(1,2,3) from dual;
LEAST
语法: LEAST(expr1[,:expr2]…)
功能: 获得参数中最小的表达式。
使用位置:过程性语言和SQL语句。
select least(1,5,9) from dual;
NVL
语法: NVL(expr1, expr2)
功能: 如果expr1是NULL,那么返回expr2,否则返回expr1。
如果expr1不是字符串,那么返回值的数据类型和expr1是相同的,否则,返回值的数据类型是VARCHAR2。
注意:expr2类型如果和expr1不一致,oracle会强转为和expr1一致。如nvl(111,’aa’)那么是错误的,因为字符串’aa’无法转为数值类型。
使用位置:过程性语言和SQL语句。
NVL2
语法: NVL2(expr1,expr2,expr3)
功能: 如果expr1是NULL,那么返回expr3,否则返回expr2。
注意:返回值类型总是和expr2一致,如果expr3和expr2类型不一致,会将expr3转为何expr2类型一致。除非expr3为null。
使用位置:过程性语言和SQL语句.
select NVL2('11ac', 1,'11a') from dual; 错误,'11a'无法转为数值类型。
select NVL2('11ac', 1,'11') from dual; 正确,可以将'11'转为数值类型。
NULLIF
语法: NULLIF(expr1, expr2)
功能: CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
如果expr1和expr2相等,则返回null,否则返回expr1。expr1可以为'',但是不能为null。
select NULLIF('','dd') from dual;返回null
select NULLIF('aa','dd') from dual;返回aa
使用位置:过程性语言和SQL语句。
UID
语法:UID
功能:获得当前数据库用户的惟一标识,标识是一个整数。
使用位置:过程性语言和SQL语句.
select uid from dual;
USER
语法:USER
功能: 取得当前oracle用户的名字,返回的结果是一个VARCHAR2型字符串。
使用位置:过程性语言和SQL语句。
select user from dual;
USERENV
语法: USERENV(option)
功能: 根据参数option,取得一个有关当前会话信息的VARCHAR2数值。
使用位置:过程性语言和SQL语句。
select userenv('language') from dual;
VSIZE
语法:VSIZE(value)
功能:获得value的内部表示的字节数。如果value是NULL,结果是NULL。
使用位置:过程性语言和SQL语句。
select vsize('hi') from dual;

SQLPlus命令
sqlplus常用命令
SPOOL将屏幕所有的输出输出到指定文件
-- spool 文件路径名;
spool g:\mysql.sql;
--业务操作
spool off;

执行一个SQL脚本文件
我们可以将多条sql语句保存在一个文本文件中,这样当要执行这个文件中的所有的sql语句时,用下面的任一命令即可,这类似于dos中的批处理。
--start filename
--@ filename
start g:\mysql.sql;
@ g:\mysql.sql;

对当前的输入进行编辑
edit
ed

重新运行上一次运行的sql语句
/

显示一个表的结构
desc tablename ;

清屏
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;

是否显示用DBMSOUTPUT.PUTLINE包进行输出的信息。
--SET SERVEROUT[PUT] {ON|OFF}
set serveroutput on;

是否在屏幕上显示输出的内容,主要与SPOOL结合使用。
--在用spool命令将一个大表中的内容输出到一个文件中时,将内容输出在屏幕上会耗费大量的时间,
--设置set termout off后,则输出的内容只会保存在输出文件中,不会显示在屏幕上,极大的提高了spool的速度
--SET TERM[OUT] {ON|OFF}
set termout off;

在dos里连接oracle数据库
CONNECT username/passwd@ljiayou

在sqlplus中连接到指定的数据库
CONNECT username/passwd@数据库名称
connect lee/oracle@orcl as sysdba;

显示当前用户
show user;

显示当前环境变量的值:
show all;

显示当前在创建函数、存储过程、触发器、包等对象的错误信息
show error;

显示数据库的版本:
--show REL[EASE]
show release

显示SGA的大小
show SGA

显示初始化参数的值:
--show PARAMETERS [parametername]
show parameters;
show parameters undo;

查看当前用户的缺省表空间
select username,defaulttablespace from userusers;

查看当前用户的角色
select from userroleprivs;

查看当前用户的系统权限和表级权限
select from usersysprivs;
select from usertabprivs;

查看用户下所有的表
select from usertables;

查看名称包含log字符的表
select objectname,objectid from userobjects where instr(objectname,'LOG')>0;

查看某表的创建时间
select objectname,created from userobjects where objectname=upper('&tablename');

查看某表的大小
select sum(bytes)/(10241024) as "size(M)" from usersegments where segmentname=upper('&tablename');

查看放在ORACLE的内存区里的表
select tablename,cache from usertables where instr(cache,'Y')>0;

查看索引个数和类别
select indexname,indextype,tablename from userindexes order by tablename;

查看索引中被索引的字段
select from userindcolumns where indexname=upper('&indexname');

查看索引的大小
select sum(bytes)/(10241024) as "size(M)" from usersegments where segmentname=upper('&indexname');

查看序列号,lastnumber是当前值
select from usersequences;

查看视图的名称
select viewname from userviews;

查看创建视图的select语句
select viewname,textlength from userviews;
set long 2000; 说明:可以根据视图的textlength值设定set long 的大小
select text from userviews where viewname=upper('&viewname');

查看同义词的名称
select from usersynonyms;

查看某表的约束条件

select constraintname, constrainttype,searchcondition, rconstraintname from userconstraints where tablename = upper('&tablename');
查看某表的约束和被约束的列

select c.constraintname,c.constrainttype,cc.columnname
from userconstraints c,userconscolumns cc where c.owner = upper('&tableowner') and c.tablename = upper('&tablename')
and c.owner = cc.owner and c.constraintname = cc.constraintname order by cc.position;
查看函数和过程
select objectname,status from userobjects where objecttype='FUNCTION';
select objectname,status from userobjects where objecttype='PROCEDURE';

查看函数和过程的源代码
select text from allsource where owner=user and name=upper('&plsqlname');

查看表空间的名称及大小

select t.tablespacename, round(sum(bytes/(10241024)),0) tssize
from dbatablespaces t, dbadatafiles d
where t.tablespacename = d.tablespacename
group by t.tablespacename;
查看表空间物理文件的名称及大小

select tablespacename, fileid, filename,
round(bytes/(10241024),0) totalspace
from dbadatafiles
order by tablespacename;
查看回滚段名称及大小

select segmentname, tablespacename, r.status,
(initialextent/1024) InitialExtent,(nextextent/1024) nextextent,
maxextents, v.curext curextent
from dbarollbacksegs r, v$rollstat v
where r.segmentid = v.usn(+)
order by segmentname ;
查看控制文件
select name from v$controlfile;

查看日志文件
select member from v$logfile;

查看表空间的使用情况

select sum(bytes)/(10241024) as freespace,tablespacename
from dbafreespace group by tablespacename;
SELECT A.TABLESPACENAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES100)/A.BYTES "% USED",(C.BYTES100)/A.BYTES "% FREE"
FROM SYS.SM$TSAVAIL A,SYS.SM$TSUSED B,SYS.SM$TSFREE C
WHERE A.TABLESPACENAME=B.TABLESPACENAME AND A.TABLESPACENAME=C.TABLESPACENAME;
查看数据库库对象
select owner, objecttype, status, count() count# from allobjects group by owner, objecttype, status;

查看数据库的版本

select version FROM productcomponentversion
Where SUBSTR(PRODUCT,1,6)='Oracle';
查看数据库的创建日期和归档方式
select created, logmode, logmode 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 命令杀此进程号。

树形递归查询:start with...connect By
从根往树末梢查询:
select from mymenu start with treepid='0' connect by prior treeid=treepid;

从树末梢向根查询:
select from mymenu start with treepid='0' connect by prior treepid=treeid;

如果还有其他条件用and 加在语句后面
select from mymenu start with treepid='0' connect by prior treeid=treepid and treelink is null;
select from mymenu start with treepid='0' connect by prior treeid=treepid and treelink is not null;*

正文到此结束
该篇文章的评论功能已被站长关闭