ORACLE函数详解

ORACLE函数详解1、SQL语句基础下面给出SQL语句的基本介绍。1.1、SQL语句所用符号操作符用途例子+-表示正数或负数，正数可省去+-1234.56+将两个数或表达式进行相加A=c+b-将两个数或表达式进行相减34-12*将两个数或表达式进行相乘12*34/除以一个数或表达式18*11NULL空值判断Wherenameisnull;||字符串连接‘101-’||tel_num=等于测试Select*fromempwherename=’赵元杰’;!=或或^=不等于测试Select*fromempwherename!=’赵元杰’;小于测试Select*fromempwheresal5000;大于测试Select*fromempwheresal5000;=小于等于测试Select*fromempwheresal=5000;=大于等于测试Select*fromempwheresal=5000;Notin测试某值是否在一个指定的结果集中Selectname,addrfromexpertwherelocalnotin(‘北京’,’上海’);ANY将一个值与一组值进行比较，返回满足条件的结果。必须跟!=,,,=,=selectename,salfromempwheresal=any(selectsalfromempwheredeptno=10)SOME同ANY，必须跟!=,,,=,=ALL将一个值与一组值比较，返回满足条件的所有列值。必须跟!=,,,=,=Selectname,salfromempwheresal=all(500,800,1200);NotbetweenAandB判断某个值是否界于两者之间。Selectname,salfromempWheresalbetween500and1200;[not]exists判断某个列是否存在于一组值中。selectdname,deptnofromdeptwhereexists(select*fromempwheredept.deptno=emp.deptno)A[not]likeb比较两个模式是否相似，当使用like语句时Oracle不去访问索引。Select*fromempWhereenamelike‘TH%’;Is[not]null测试值是否为空。Selectename,deptnofromempWherecomm.Isnullorcomm.=0;Not对结果的否定。Select*fromempWheresalnot(sal1000);等价于selectename,salfromempwheresal=1000;AND用于判断两个条件十分都满足。Select*fromempwhereEname=’SIMTH’andsal=1000;OR用于判断两个条件中是否有一个满足。Select*fromempwhereEname=’SIMTH’orename=’SCOTT’;UNION用于返回（组合）两个查询中所有唯一的行。SelectenamefromempunionSelectenamefromemp;UNIONALL用于返回（组合）两个查询中所有所有的行。INTERSECT用于返回两个查询中相同的行。Selectenamefromemp1intersectselectenamefromemp2;MINUS用于返回两个查询中的不同的行。1.2、简单select查询当我们可以用SQL*PLUS登录到SQL下后，我们可以用DESC显示某表的结构，也可以用select语句简单查询表中的一些列的内容。例：要查询EMP表中员工的姓名、工资及出生日期，则：SQLselectename,sal,hiredatefromemp;2、伪列及伪表Oracle系统为了实现完整的关系数据库功能，系统专门提供了一组称为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象（如建表）时由我们完成的，而是在我们建立对象时由自动Oracle完成的。Oracle目前有以下的伪列：CURRVALandNEXTVAL使用序列号的保留字LEVEL查询数据所对应的级ROWID记录的唯一标识ROWNUM限制查询结果集的数量Oracle还提供了一个DUAL的伪表，该目的表主要是保证在使用SELECT语句中语句的完整性而提供的，如：我们要查询当前的系统日期及时间，而系统的日期和时间并是放在一个指定的表里。所以在from语句后就没有表名给出。为了使用from后有个表名，我们就用DUAL代替。如：例1：查询Oracle系统日期及时间：SQLselectto_char(sysdate,'yyyy.mm.ddhh24:mi:ss')fromDUAL;TO_CHAR(SYSDATE,'YY-------------------2001.06.0207:28:09例2：计算一下5000+5000*0.1的结果是多少，则：SQLselect5000+5000*0.1fromDUAL;5000+5000*0.1-------------------55003、SELECT语句在关系数据库中，使用频率最高要算SELECT语句了。尽管SELECT语句的使用非常简单，但确很有学问。下面简单介绍有关SELECT语句的常用方法。1.命令语法：SELECT的简单语法：SELECT[DISTINCT|ALL]{*|column1[,column2]...}FROM{table_1|(subquery)}[alias][,{table_2|(subquery)}[alias]]...[WHEREcondition][CONNECTBYcondition[STARTWITHcondition][GROUPBYexpn][HAVINGexpn][{UNION[ALL]|INTERSECT|MINUS}SELECT...][ORDERBY[expn][ASC|DESC][FORUPDATE[OF[user.]table|view]column][NOWAIT]详细语法结构需查阅最新ORACLE原版《ORACLE8iSQLREFERENCEMANUAL》或《ORACLE9iSQLREFERENCEMANUAL》4、SQL中的单记录函数许多资料（包括Oracle的资料）把Oracle的SQL语句中用到的函数分为单值函数和多值函数，单值函数又分为字符函数和数字函数。下面分别介绍它们。4.1、单记录字符函数函数说明ASCII返回对应字符的十进制值CHR给出十进制返回字符CONCAT拼接两个字符串，与||相同INITCAT将字符串的第一个字母变为大写INSTR找出某个字符串的位置INSTRB找出某个字符串的位置和字节数LENGTH以字符给出字符串的长度LENGTHB以字节给出字符串的长度LOWER将字符串转换成小写LPAD使用指定的字符在字符的左边填充LTRIM在左边裁剪掉指定的字符RPAD使用指定的字符在字符的右边填充RTRIM在右边裁剪掉指定的字符REPLACE执行字符串搜索和替换SUBSTR取字符串的子串SUBSTRB取字符串的子串（以字节）SOUNDEX返回一个同音字符串TRANSLATE执行字符串搜索和替换TRIM裁剪掉前面或后面的字符串UPPER将字符串变为大写NVL以一个值来替换空值1、ASCII(c1)c1是字符串。返回与指定的字符对应的十进制数。SQLselectascii('A')A,ascii('a')a,ascii('0')zero,ascii('')spacefromdual;AaZEROSPACE----------------------------------------65974832SQLselectascii('赵')zhao,length('赵')lengfromdual;ZHAOLENG--------------------5474012、CHR(I[NCHAR])给出整数，返回对应字符。如：SQLselectchr(54740)zhao,chr(65)chr65fromdual;ZHC---赵A3、CONCAT(c1,c2)返回字符串c1与字符串c2合并后的值。例如：SQLselectconcat('010-','88018159')||'转23'赵元杰电话fromdual;赵元杰电话-----------------010-88018159转234、INITCAP(c1)返回字符串c1并第一个字母变为大写。例如：SQLselectinitcap('simth')uppfromdual;UPP-----Simth5、INSTR(c1,c2[,I[,j]])在一个字符串中搜索指定的字符，返回发现指定的字符的位置。C1:被搜索的字符串C2:希望搜索的字符串I:搜索的开始位置，缺省是1J:出现的位置，缺省是1。SQLSELECTINSTR('OracleTraining','ra',1,2)InstringFROMDUAL;Instring----------96、INSTRB(c1,c2[,I[,j]])除了返回的字节外，与INSTR相同，7、LENGTH(c)返回字符串c的长度。SQLl1selectname,length(name),addr,length(addr),sal,length(to_char(sal))2*fromnchar_tstSQL/NAMELENGTH(NAME)ADDRLENGTH(ADDR)SALLENGTH(TO_CHAR(SAL))------------------------------------------------------------------------赵元杰3北京市海淀区699999.9988、LENGTHB(c)以字节返回字符串的字节数。SQLselectname,lengthb(name),length(name)fromnchar_tst;NAMELENGTHB(NAME)LENGTH(NAME)-------------------------------赵元杰639、LOWER(c)返回字符串并将所有字符变为小写。SQLselectlower('AaBbCcDd')AaBbCcDdfromdual;AABBCCDD--------aabbccdd10、UPPER(c)与LOWER相反，将给出字符串变为大写。如：SQLselectupper('AaBbCcDd')AaBbCcDdfromdual;AABBCCDD--------AABBCCDD11、RPAD和LPAD(粘贴字符)RPAD(string，Length[，'set'])LPAD(string，Length[，'set'])RPAD在列的右边粘贴字符；LPAD在列的左边粘贴字符。例1：SQLselectRPAD(City，35，'.')，temperaturefromweather;RPAD(City，35，'.')temperature------------------------------------------CLEVELAND......85LOSANGELES..81.........................（即不够35个字符用'.'填满）12、LTRIM(左截断)RTRIM（右截断）函数LTRIM(string[,’set’])LeftTRIM(左截断)删去左边出现的任何set字符。RTRIM(string[,’set’])RightTRIM(右截断)删去右边出现的任何set字符。例1：SELECTRTRIM(‘MotherTheresa,The’,‘The’)“ExampleofRightTrimming”FROMDUAL;ExampleofRight----------------MotherTheresa,