单组函数PPT演示课件

Single-Row函数,目的,描述可在SQL中使用的各种函数在SELECT指令中使用字符,数字,日期函数描述转换函数的使用,2,SQL函数,函数,函数执行任务,3,两种SQL函数,函数,单行函数,多行函数,4,单行函数,操作数据项接受参数并返回一个值对每一返回行起作用每一行返回一个结果可修改数据类型可使用嵌套,function_name(column|expression,arg1,arg2,.),5,单行函数,Conversion,Character,Number,Date,General,单行函数,6,字符函数,字符函数,LOWERUPPERINITCAP,CONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE,大小写转换函数,字符处理函数,7,函数,结果,大小写转换函数,字符串大小写转换,LOWER(SQLCourse)UPPER(SQLCourse)INITCAP(SQLCourse),sqlcourseSQLCOURSESqlCourse,8,使用大小写转换函数,显示员工号,名字,部门号,SQLSELECTempno,ename,deptno2FROMemp3WHEREename=blake;norowsselected,9,CONCAT(Good,String)SUBSTR(String,1,3)LENGTH(String)INSTR(String,r)LPAD(sal,10,*),GoodStringStr63*5000,函数,结果,字符处理函数,处理字符串,10,使用字符处理函数,SQLSELECTename,CONCAT(ename,job),LENGTH(ename),2INSTR(ename,A)3FROMemp4WHERE,SUBSTR(job,1,5)=SALES;,ENAMECONCAT(ENAME,JOB)LENGTH(ENAME)INSTR(ENAME,A)-MARTINMARTINSALESMAN62ALLENALLENSALESMAN51TURNERTURNERSALESMAN60WARDWARDSALESMAN42,11,范例,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/,范例,12,Replace函数,SQLselectreplace(syswareoracle,oracle,training)syswarefromdual/SYSWARE-syswaretraining,13,其他有用的函数,TranslateSQLselecttranslate(ThisissecondtrianingofsyswareinSuzhou!,abcde,12345)sysware2fromdual;SYSWARE-Thisiss53on4tri1ningofitpu2inB5ijing!FloorSQLselectfloor(4.55)fromdual;FLOOR(4.55)-4CeilSQLselectceil(4.55)fromdual;CEIL(4.55)-5,14,数字函数,ROUND:4舍5入到指定的小数位ROUND(45.926,2)45.93TRUNC:将值截断到指定的小数位TRUNC(45.926,2)45.92MOD:返回相除后的余数MOD(1600,300)100,15,使用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.924650,16,SQLSELECTTRUNC(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到小数点后两位,个位,十位,17,使用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.,18,使用MOD函数,计算工资除以奖金后的余数,SQLSELECTename,sal,comm,MOD(sal,comm)2FROMemp3WHEREjob=SALESMAN;,ENAMESALCOMMMOD(SAL,COMM)-MARTIN125014001250ALLEN1600300100TURNER150001500WARD1250500250,19,使用日期,Oracle以内部数字格式存储日期:世纪,年,月,日,小时,分钟,秒缺省的日期格式是DD-MON-YYSYSDATE是返回日期和时间的函数DUAL是用来查看SYSDATE的虚表,20,日期的运算,从日期中加或减一个数值,以得当一个日期结果值两个日期相减以便得到他们相差多少天往一天中加小时时,需除以24,21,JOB定时任务,时间计算variablejobnonumber;variableinstnonumber;beginselectinstance_numberinto:instnofromv$instance;dbms_job.submit(:jobno,statspack.snap;,trunc(sysdate+1/24,HH),trunc(SYSDATE+1/24,HH),TRUE,:instno);commit;end;/其它,22,使用日期运算,SQLSELECTename,(SYSDATE-hiredate)/7WEEKS2FROMemp3WHEREdeptno=10;,ENAMEWEEKS-KING830.93709CLARK853.93709MILLER821.36566,23,日期函数,两日期相差多少月,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,加月份到日期,指定日期的下一天,一个月中的最后一天,Round日期,Truncate日期,函数,描述,24,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,25,使用日期函数,ROUND(25-JUL-95,MONTH),ROUND(25-JUL-95,YEAR),TRUNC(25-JUL-95,MONTH),TRUNC(25-JUL-95,YEAR),01-AUG-95,01-JAN-96,01-JUL-95,01-JAN-95,26,转换函数,ImplicitConversions,ExplicitConversions,DatatypeConvesions,27,IMPLICITCONVERSION,作为任务,Oracle可自动转换,VARCHAR2orCHAR,From,To,VARCHAR2orCHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,28,IMPLICITCONVERSION,表达式可自动转换数据类型,VARCHAR2orCHAR,From,To,VARCHAR2orCHAR,NUMBER,DATE,29,EXPLICITCONVERSION,NUMBER,CHARACTER,TO_CHAR,30,TO_CHAR函数操作日期,格式模型必须用单引号括起来,并且是大小写敏感可包含任何有效的日期格式有一个fm元素用于填补空格或禁止前面的零使用逗号分离日期值,TO_CHAR(date,fmt),31,YYYY,日期格式基础,YEAR,MM,MONTH,DY,DAY,以数字表示全年,拼写出的年,两位数字月,三个字母缩写的一个星期中的天,全天名,全月名,32,日期格式基础,时间格式作为日期的一部分用双引号括起来以加字符串数值后缀拼出数字,HH24:MI:SSAM,15:45:32PM,DDofMONTH,12ofOCTOBER,ddspth,fourteenth,33,使用TO_CHAR函数处理日期,SQLSELECTename,2TOCHAR(hiredate,fmDDMonthYYYY)HIREDATE3FROMemp;,ENAMEHIREDATE-KING17November1981BLAKE1May1981CLARK9June1981JONES2April1981MARTIN28September1981ALLEN20February1981.14rowsselected.,34,使用TO_CHAR函数处理数,使用TO_CHAR函数将数字作为字符显示,TO_CHAR(number,fmt),9,0,$,L,.,Representsanumber,Forcesazerotobedisplayed,Placesafloatingdollarsign,Usesthefloatinglocalcurrencysymbol,Printsadecimalpoint,Printsathousandindicator,35,使用TO_CHAR函数处理数,SQLSELECTTO_CHAR(sal,$99,999)SALARY2FROMemp3WHEREename=SCOTT;,SALARY-$3,000,36,TO_NUMBER和TO_DATE函数,TO_NUMBER(char),TO_DATE(char,fmt),使用TO_NUMBER函数将字符转换为数字使用TO_DATE函数将字符转换为日期,37,进制转换,十进制与十六进制SQLselectto_char(2463,xxxx)fromdual;TO_CH-99f已选择1行。十六进制与十进制SQLselectto_number(99f,xxxx)fromdual;TO_NUMBER(99F,XXXX)-2463已选择1行。,38,NVL函数,将空值转换为实际的值数据格式可以是日期,字符,数字数据类型必须匹配NVL(comm,0)NVL(hiredate,01-JAN-97)NVL(job,NoJobYet),39,SQLSELECTename,sal,comm,(sal*12)+NVL(comm,0)2FROMemp;,使用NVL函数,ENAMESALCOMM(SAL*12)+NVL(COMM,0)-KING500060000BLAKE285034200CLARK245029400JONES297535700MARTIN1250140016400ALLEN160030019500.14rowsselected.,40,NVL2函数,NVL2(expr1,expr2,expr3)如果expr1不为Null，返回expr2，如果expr1为Null，返回expr3。expr1可以为任何数据类型NULLIF(expr1,expr2)比较两个表达式，如果相等返回空值，如果不等返回第一个值。COALESCE(expr1,expr2,.,exprn)返回表达式列表里的第一个非空表达式,41,CASE表达式,实现逻辑IF-THEN-ELSECASEexprWHENcomparison_expr1THENreturn_expr1WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_exprEND,42,Example,SQLselectename,sal,2casejobWHENMANAGERTHEN1.1*sal3WHENANALYSTTHEN1.2*SAL4WHENCLERKTHEN1.5*SAL5ELSEsalENDNEW_SAL6fromemp;ENAMESALNEW_SAL-SMITH8001200ALLEN16001600WARD12501250JONES29753272.5MARTIN12501250BLAKE28503135CLARK24502695SCOTT30003600KING50005000TURNER15001500ADAMS11001650JAMES9501425FORD30003600MILLER1300195014rowsselected.,43,DECODE函数,DECODE函数类似于一系列CASE或IF-THEN-ELSE语句,DECODE(col/expression,search1,result1,search2,result2,.,default),44,使用DECODE函数,SQLSELECTjob,sal,2DECODE(job,ANALYSTSAL*1.1,3CLERK,SAL*1.15,4MANAGER,SAL*1.20,5SAL)6REVISED_SALARY7FROMemp;,JOBSALREVISED_SALARY-PRESIDENT50005000MANAGER28503420MANAGER24502940.14rowsselected.,45,嵌套函数,单行函数可被嵌入到任何层嵌套函