单组函数

Single-Row函数目的–描述可在SQL中使用的各种函数–在SELECT指令中使用字符,数字,日期函数–描述转换函数的使用SQL函数函数输入arg1arg2argn函数执行任务输出结果两种SQL函数函数单行函数多行函数单行函数–操作数据项–接受参数并返回一个值–对每一返回行起作用–每一行返回一个结果–可修改数据类型–可使用嵌套function_name(column|expression,[arg1,arg2,...])单行函数ConversionCharacterNumberDateGeneral单行函数字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小写转换函数字符处理函数函数结果大小写转换函数•字符串大小写转换LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourse使用大小写转换函数•显示员工号,名字,部门号SQLSELECTempno,ename,deptno2FROMemp3WHEREename='blake';norowsselectedEMPNOENAMEDEPTNO----------------------------7698BLAKE30SQLSELECTempno,ename,deptno2FROMemp3WHERELOWER(ename)='blake';CONCAT('Good','String')SUBSTR('String',1,3)LENGTH('String')INSTR('String','r')LPAD(sal,10,'*')GoodStringStr63******5000函数结果字符处理函数•处理字符串使用字符处理函数SQLSELECTename,CONCAT(ename,job),LENGTH(ename),2INSTR(ename,'A')3FROMemp4WHERESUBSTR(job,1,5)='SALES';ENAMECONCAT(ENAME,JOB)LENGTH(ENAME)INSTR(ENAME,'A')----------------------------------------------------------MARTINMARTINSALESMAN62ALLENALLENSALESMAN51TURNERTURNERSALESMAN60WARDWARDSALESMAN42范例selecta路径,sum(b)/1024/1024大小(Msize)from(selectsubstr(name,instr(name,chr(92),1),instr(name,chr(92),1,2)+1-instr(name,chr(92),1))a,bytesbfromv$datafile)groupbya/范例Replace函数SQLselectreplace('syswareoracle','oracle','training')syswarefromdual/SYSWARE-----------------syswaretraining数字函数–ROUND:4舍5入到指定的小数位ROUND(45.926,2)45.93–TRUNC:将值截断到指定的小数位TRUNC(45.926,2)45.92–MOD:返回相除后的余数MOD(1600,300)100使用ROUND函数•显示45.923到小数点后两位,个位,十位SQLSELECTROUND(45.923,2),ROUND(45.923,0),2ROUND(45.923,-1)3FROMSYS.DUAL;ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)----------------------------------------------45.924650SQLSELECTTRUNC(45.923,2),TRUNC(45.923),2TRUNC(45.923,-1)3FROMSYS.DUAL;TRUNC(45.923,2)TRUNC(45.923)TRUNC(45.923,-1)-------------------------------------------45.924540使用TRUNC函数•显示45.923到小数点后两位,个位,十位使用Trunc计算日期•取上周4的日期值selecttrunc(sysdate,'D')-3fromdual;trunc(sysdate,'D'),截取到本周的第一天(0:00)trunc(sysdate,'M'),截取到本月的第一天(0:00)trunc(sysdate,'DD'),截取到本日的0:00trunc(sysdate,'yyyy'),得到今年的第一天的0:00.使用MOD函数•计算工资除以奖金后的余数SQLSELECTename,sal,comm,MOD(sal,comm)2FROMemp3WHEREjob='SALESMAN';ENAMESALCOMMMOD(SAL,COMM)-----------------------------------------MARTIN125014001250ALLEN1600300100TURNER150001500WARD1250500250使用日期–Oracle以内部数字格式存储日期:世纪,年,月,日,小时,分钟,秒–缺省的日期格式是DD-MON-YY–SYSDATE是返回日期和时间的函数–DUAL是用来查看SYSDATE的虚表日期的运算–从日期中加或减一个数值,以得当一个日期结果值–两个日期相减以便得到他们相差多少天–往一天中加小时时,需除以24使用日期运算SQLSELECTename,(SYSDATE-hiredate)/7WEEKS2FROMemp3WHEREdeptno=10;ENAMEWEEKS-------------------KING830.93709CLARK853.93709MILLER821.36566日期函数两日期相差多少月MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC加月份到日期指定日期的下一天一个月中的最后一天Round日期Truncate日期函数描述•MONTHS_BETWEEN('01-SEP-95','11-JAN-94')使用日期函数•ADD_MONTHS('11-JAN-94',6)•NEXT_DAY('01-SEP-95','FRIDAY')•LAST_DAY('01-SEP-95')19.6774194'11-JUL-94''08-SEP-95''30-SEP-95'使用日期函数•ROUND('25-JUL-95','MONTH')•ROUND('25-JUL-95','YEAR')•TRUNC('25-JUL-95','MONTH')•TRUNC('25-JUL-95','YEAR')01-AUG-9501-JAN-9601-JUL-9501-JAN-95转换函数ImplicitConversionsExplicitConversionsDatatypeConvesionsIMPLICITCONVERSION•作为任务,Oracle可自动转换VARCHAR2orCHARFromToVARCHAR2orCHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR2IMPLICITCONVERSION•表达式可自动转换数据类型VARCHAR2orCHARFromToVARCHAR2orCHARNUMBERDATEEXPLICITCONVERSIONNUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR函数操作日期•格式模型–必须用单引号括起来,并且是大小写敏感–可包含任何有效的日期格式–有一个fm元素用于填补空格或禁止前面的零–使用逗号分离日期值TO_CHAR(date,'fmt')YYYY日期格式基础YEARMMMONTHDYDAY以数字表示全年拼写出的年两位数字月三个字母缩写的一个星期中的天全天名全月名日期格式基础时间格式作为日期的一部分用双引号括起来以加字符串数值后缀拼出数字HH24:MI:SSAM15:45:32PMDDofMONTH12ofOCTOBERddspthfourteenth使用TO_CHAR函数处理日期SQLSELECTename,2TOCHAR(hiredate,'fmDDMonthYYYY')HIREDATE3FROMemp;ENAMEHIREDATE---------------------------KING17November1981BLAKE1May1981CLARK9June1981JONES2April1981MARTIN28September1981ALLEN20February1981...14rowsselected.使用TO_CHAR函数处理数•使用TO_CHAR函数将数字作为字符显示TO_CHAR(number,'fmt')90$L.,RepresentsanumberForcesazerotobedisplayedPlacesafloatingdollarsignUsesthefloatinglocalcurrencysymbolPrintsadecimalpointPrintsathousandindicator使用TO_CHAR函数处理数SQLSELECTTO_CHAR(sal,'$99,999')SALARY2FROMemp3WHEREename='SCOTT';SALARY--------$3,000TO_NUMBER和TO_DATE函数TO_NUMBER(char)TO_DATE(char[,'fmt'])•使用TO_NUMBER函数将字符转换为数字•使用TO_DATE函数将字符转换为日期进制转换•十进制与十六进制SQLselectto_char(2463,'xxxx')fromdual;TO_CH-----99f已选择1行。•十六进制与十进制SQLselectto_number('99f','xxxx')fromdual;TO_NUMBER('99F','XXXX')-----------------------2463已选择1行。NVL函数•将空值转换为实际的值–数据格式可以是日期,字符,数字–数据类型必须匹配•NVL(comm,0)•NVL(hiredate,'01-JAN-97')•NVL(job,'NoJobYet')SQLSELECTename,sal,comm,(sal*12)+NVL(comm,0)2FROMemp;使用NVL函数ENAMESALCOMM(SAL*12)+NVL(COMM,0)------------------------------------------------KING500060000BLAKE285034200CLARK245029400JONES297535700MARTIN1250140016400ALLEN160030019500...14rowsselected.NVL2函数NVL2(expr1,expr2,expr3)如果expr1不为Null，返回expr2，如果expr1为Null，返回expr3。expr1可以为任何数据类型NULLIF(expr1,expr2)比较两个表达式，如果相等返回空值，如果不等返回第一个值。COALESCE(expr1,expr2,...,exprn)返回表达式列表里的第一个非空表达式CASE表达式实现逻辑IF-THEN-ELSECASEexprWHENcomparison_expr1THENreturn_expr1[WHENcompa