oracle内置SQL函数整理
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;
ASCIISTR
语法：asciistr(str)
功能：返回字符串的规则表现形式，英文和数字变为规则的，中文则前面有’\’符号,返回unicode编码形式。UNISTR(str)函数是相反的过程，将unicode编码变为字符。
使用位置：过程性语句和SQL语句。
找出含有中文的字段：
select a from test where asciistr(replace(a,'\',' ')) like '%\%';
SELECT ASCIISTR('恭喜你') FROM dual;
\606D\559C\4F60
CHR
语法: chr(x)
功能：返回在数据库字符集中与X拥有等价数值的字符。CHR和ASCII是一对反函数。经过CHR转换后的字符再经过ASCII转换又得到了原来的字符。
Chr(10)表示换行
使用位置：过程性语句和SQL语句。
如select ‘a’||chr(10)||’b’ from dual;返回
a
b
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）
功能：从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语句。
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语句。
NLS_SORT（）9i以上
语法：nls_sort（str,rule）
功能：对中文排序
按笔画排序：rule为'NLS_SORT=SCHINESE_STROKE_M'
按部首排序：'NLS_SORT=SCHINESE_RADICAL_M'
默认拼音排序：’NLS_SORT=SCHINESE_PINYIN_M’
如select * from player order by
nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
使用位置：过程性语句和SQL语句。
REPLACE
语法：REPLACE（string，search_str[,replace_str]）
功能：把string中的所有的子字符串search_str用可选的replace_str替换，如果没有指定replace_str，所有的string中的子字符串search_str都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。(与transalte差别是子字符串，而且translate中str1长度大于str2的时候，会将不够替换的在源中删除，并且参数必须是三个，replace参数可以是两个),与translate区别是，replace必须是源字符串的一个子串，而translate是可以不连续，按单个字符搜索。
使用位置：过程性语句和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语句。
SQL> select dump('123456') from dual;
DUMP('123456')
-------------------------------
Typ=96 Len=6: 49,50,51,52,53,54
SQL> select reverse('123456') from dual;
REVERSE('123456')
-----------------
654321
SQL> select dump('654321') from dual;
DUMP('654321')
-------------------------------
Typ=96 Len=6: 54,53,52,51,50,49
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语句。
例子：
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
SQL> select translate('dsadaefe','ae','12') from dual;
TRANSLATE('DSADAEFE','AE','12'
------------------------------
ds1d12f2
TRIM
语法: TRIM(string)
功能: 删除string字符串前后的空格,若是trim(string1 from string)则是删除
使用位置：过程性语句和SQL语句。
--trim(str),直接去除首尾空格
--trim(leading,trailing,both --->trim_character from str_source) 去除首，尾，首尾str_source字符在str中，直到在str_source中找到不在结束
--trim_character最多只有一个字符,不是字节
--如果没有leading,trailing,both关键字，且没有trim_character则不能有from 默认是both,去掉首尾
--如果没有trim_character，默认trim_character是空格,如果没有leading,trailing,both则不可用from关键字
--如果trim_character和str_source有一个为null,结果为null
select trim(leading '我' from '我xxXbc123') from dual; --去除前面'我'字符
select trim(trailing 'x' from 'xxxXbc123x') from dual;--去除尾x字符
select trim(both 'x' from 'xxxXbc123x') from dual; --去除首尾x字符
select trim(leading from ' abcd ') from dual; --去除前空格
select trim(trailing from ' abcd ') from dual; --去除尾空格
select trim(both from ' abcd ') from dual; --去除首尾空格
select trim(from 'xabcdx ') from dual; --错误
select trim('x' from 'xabcdx ') from dual;--直接去除首尾x，先从左边开始，然后从右边开始，abcdx 因为右边有个空格
select trim(' abcd ') from dual;--直接去除首尾x，不能有from关键字，因为前面没有leading,trailing,both关键字
select trim(both null from 'abcd') from dual; --返回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 --注意第1个有3个\
\恭喜你
2.字符函数——返回数字
这些函数接受字符参数回数字结果.参数可以是CHAR或者是VARCHAR2类型的.尽管实际下许多结果都是整数值,但是返回结果都是简单的NUMBER类型的,没有定义任何的精度或刻度范围.
ASCII
语法: ASCII（string）
功能: 数据库字符集返回string的第一个字节的十进制表示.请注意该函数仍然称作为ASCII.尽管许多字符集不是7位ASCII.CHR和ASCII是互为相反的函数.CHR得到给定字符编码的响应字符. ASCII得到给定字符的字符编码.
使用位置: 过程性语句和SQL语句。
INSTR
语法: INSTR（string1, string2[,a,b]） instr(str1,str2,pos,nth) return binary_integer
功能: 得到在string1中包含string2的位置. a为正数，string1是从左边开始检查的,开始的位置为a,如果a是一个负数,那么string1是从右边开始进行扫描的.第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置.如果string2在a和b的规定下没有找到,那么返回0.位置的计算是相对于string1的开始位置的,不管a和b的取值是多少.,nth要大于0,如果a为0.则结果为0
使用位置: 过程性语句和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语句。
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语句。
过程中用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语句。
4.日期函数
日期函数接受DATE类型的参数.除了MONTHS_BETWEEN函数返回的是NUMBER类型的结果,所有其他的日期函数返回的都是DATE类型的数值.
日期-日期，返回间隔的天数
日期+number，返回日期
日期-number，返回日期
日期+日期 不正确
ADD_MONTHS
语法: ADD_MONTHS(d,x)
功能: 返回日期d加上x个月后的日期。x可以是任意整数。如果结果日期中的月份所包含的天数比d日期中的“日”分量要少。（即相加后的结果日期中的日分量信息已经超过该月的最后一天，例如，8月31日加上一个月之后得到9月31日，而9月只能有30天）返回结果月份的最后一天。其中x可以为正数也可以为负数。
使用位置: 过程性语言和SQL语句。
LAST_DAY
语法:LAST_DAY(d)
功能:计算包含日期的d的月份最后一天的日期.这个函数可以用来计算当月中剩余天数.
last_day(sysdate)-sysdate
使用位置: 过程性语言和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的内容可以忽略大小写.其中string是星期表达，如next_day(sysdate,’Friday’),要和具体的nls_date_language关键
使用位置: 过程性语言和SQL语句。
ROUND
语法: ROUND(d[,format])
功能: 将日期d按照由format指定的格式进行处理.如果没有给format则使用缺省设置`DD`.
使用位置: 过程性语言和SQL语句。
SYSDATE
语法: SYSDATE
功能: 取得当前的日期和时间,类型是DATE.它没有参数.但在分布式SQL语句中使用时,SYSDATE返回本地数据库的日期和时间.
使用位置: 过程性语言和SQL语句。
TRUNC
语法: TRUNC(d,format)
功能: 计算截尾到由format指定单位的日期d.可以使用位置:格式和效果.缺省参数同ROUNG.
使用位置: 过程性语言和SQL语句。
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语句。
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子句.
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值十分有用.
注意：expr2类型如果和expr1不一致，oracle会强转为和expr1一致。如nvl(111,’aa’)那么是错误的，因为字符串’aa’无法转为数值类型。
使用位置: 过程性语言和SQL语句.
NVL2
语法: NVL 2(expr1, expr2,expr3)
功能: 如果expr1是NULL,那么返回expr2,否则返回expr3.
注意： 返回值类型总是和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 = expr 2 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
语法:
功能: 获得当前数据库用的惟一标识,标识是一个整数.
使用位置: 过程性语言和SQL语句.
SQL> select * from dba_users where user_id=(select uid from dual);
Dba_users表存放该数据库所有的用户信息
USER
语法:
功能: 取得当前oracle用户的名字,返回的结果是一个VARCHAR2型字符串.
使用位置: 过程性语言和SQL语句.
USERENV
语法: USERENV(option)
功能: 根据参数option,取得一个有关当前会话信息的VARCHAR2数值.
使用位置: 过程性语言和SQL语句.
VSIZE
语法: VSIZE(value)
功能: 获得value的内部表示的字节数.如果value是NULL,结果是NULL.
使用位置: 过程性语言和SQL语句.
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://')
insert into mymenu values('7','5','四月白','')
insert into mymenu values('8','5','冬白菜','htt://')
insert into mymenu values('9','2','西瓜','')
insert into mymenu values('10','2','桔子','htt://')
insert into mymenu values('11','3','大米','')
insert into mymenu values('12','3','大豆','htt://')
insert into mymenu values('13','4','猪肉','')
insert into mymenu values('14','4','鱼','')
insert into mymenu values('15','14','昌鱼','')
insert into mymenu values('16','14','王八','htt://')
从根往树末梢查询：
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
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-------------------------------------------------
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 )
