oracle内部讲义3-6

Copyright©OracleCorporation,2001.Allrightsreserved.单行函数3-2Copyright©OracleCorporation,2001.Allrightsreserved.目标通过本章学习，您将可以:•SQL中不同类型的函数。•在SELECT语句中使用字符，数字和日期函数。•描述转换型函数的用途。3-3Copyright©OracleCorporation,2001.Allrightsreserved.SQL函数函数输入参数1参数2参数n函数执行输出结果3-4Copyright©OracleCorporation,2001.Allrightsreserved.两种SQL函数函数单行函数多行函数3-5Copyright©OracleCorporation,2001.Allrightsreserved.单行函数单行函数:•操作数句对象•接受函数返回一个结果•只对一行进行变换•每行返回一个结果•可以转换数据类型•可以嵌套•参数可以是一列或一个值function_name[(arg1,arg2,...)]3-6Copyright©OracleCorporation,2001.Allrightsreserved.单行函数转换字符数值日期通用单行函数3-7Copyright©OracleCorporation,2001.Allrightsreserved.字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小写控制函数字符控制函数3-8Copyright©OracleCorporation,2001.Allrightsreserved.函数结果大小写控制函数这类函数改变字符的大小写。LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourse3-9Copyright©OracleCorporation,2001.Allrightsreserved.大小写控制函数显示员工Higgins的信息:SELECTemployee_id,last_name,department_idFROMemployeesWHERElast_name='higgins';norowsselectedSELECTemployee_id,last_name,department_idFROMemployeesWHERELOWER(last_name)='higgins';3-10Copyright©OracleCorporation,2001.Allrightsreserved.CONCAT('Hello','World')SUBSTR('HelloWorld',1,5)LENGTH('HelloWorld')INSTR('HelloWorld','W')LPAD(salary,10,'*')RPAD(salary,10,'*')TRIM('H'FROM'HelloWorld')HelloWorldHello106*****2400024000*****elloWorld函数结果字符控制函数这类函数控制字符:3-11Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,'a')Contains'a'?FROMemployeesWHERESUBSTR(job_id,4)='REP';字符控制函数1231233-12Copyright©OracleCorporation,2001.Allrightsreserved.数字函数•ROUND:四舍五入ROUND(45.926,2)45.93•TRUNC:截断TRUNC(45.926,2)45.92•MOD:求余MOD(1600,300)1003-13Copyright©OracleCorporation,2001.Allrightsreserved.SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;ROUND函数DUAL是一个‘伪表’，可以用来测试函数和表达式。1233123-14Copyright©OracleCorporation,2001.Allrightsreserved.SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROMDUAL;TRUNC函数3121233-15Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_name,salary,MOD(salary,5000)FROMemployeesWHEREjob_id='SA_REP';MOD函数3-16Copyright©OracleCorporation,2001.Allrightsreserved.日期•Oracle内部使用数字存储日期:世纪,年,月,日,小时,分钟,秒。•默认的日期格式是DD-MON-RR.–可以只指定年的后两位在20世纪存放21世纪的日期。–同样可以在21世纪存放20世纪的日期。SELECTlast_name,hire_dateFROMemployeesWHERElast_namelike'G%';3-17Copyright©OracleCorporation,2001.Allrightsreserved.日期函数SYSDATE返回:•日期•时间3-18Copyright©OracleCorporation,2001.Allrightsreserved.日期的数学运算•在日期上加上或减去一个数字结果仍为日期。•两个日期相减返回日期之间相差的天数。•可以用数字除24来向日期中加上或减去小时。3-19Copyright©OracleCorporation,2001.Allrightsreserved.日期的数学运算SELECTlast_name,(SYSDATE-hire_date)/7ASWEEKSFROMemployeesWHEREdepartment_id=90;3-20Copyright©OracleCorporation,2001.Allrightsreserved.日期函数两个日期相差的月数MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC向指定日期中加上若干月数指定日期的下一个日期本月的最后一天日期四舍五入日期截断函数描述3-21Copyright©OracleCorporation,2001.Allrightsreserved.•MONTHS_BETWEEN('01-SEP-95','11-JAN-94')日期函数•ADD_MONTHS('11-JAN-94',6)•NEXT_DAY('01-SEP-95','FRIDAY')•LAST_DAY('01-FEB-95')19.6774194'11-JUL-94''08-SEP-95''28-FEB-95'3-22Copyright©OracleCorporation,2001.Allrightsreserved.•ROUND(SYSDATE,'MONTH')01-AUG-95•ROUND(SYSDATE,'YEAR')01-JAN-96•TRUNC(SYSDATE,'MONTH')01-JUL-95•TRUNC(SYSDATE,'YEAR')01-JAN-95日期函数AssumeSYSDATE='25-JUL-95':3-23Copyright©OracleCorporation,2001.Allrightsreserved.转换函数隐性显性数据类型转换3-24Copyright©OracleCorporation,2001.Allrightsreserved.隐式数据类型转换Oracle自动完成下列转换：VARCHAR2orCHAR源数据类型目标数据类型VARCHAR2orCHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR23-25Copyright©OracleCorporation,2001.Allrightsreserved.隐式数据类型转换表达式计算中,Oracle自动完成下列转换:VARCHAR2orCHAR源数据类型目标数据类型VARCHAR2orCHARNUMBERDATE3-27Copyright©OracleCorporation,2001.Allrightsreserved.TO_CHAR函数对日期的转换格式:•必须包含在单引号中而且大小写敏感。•可以包含任意的有效的日期格式。•可以使用fm去掉多余的空格或者前导零。•与日期指用逗号隔开。TO_CHAR(date,'format_model')3-28Copyright©OracleCorporation,2001.Allrightsreserved.YYYY日期格式的元素YEARMMMONTHDYDAY2004TWOTHOUSANDANDFOUR02MONMONDAYJULYMONJULDD023-29Copyright©OracleCorporation,2001.Allrightsreserved.日期格式的元素•时间格式•使用双引号向日期中添加字符•日期在月份中的位置HH24:MI:SSAM15:45:32PMDDofMONTH12ofOCTOBERddspthfourteenth3-30Copyright©OracleCorporation,2001.Allrightsreserved.TO_CHAR函数对日期的转换SELECTlast_name,TO_CHAR(hire_date,'fmDDMonthYYYY')ASHIREDATEFROMemployees;…3-31Copyright©OracleCorporation,2001.Allrightsreserved.TO_CHAR函数对数字的转换下面是在TO_CHAR函数中经常使用的几种格式:TO_CHAR(number,'format_model')90$L.,数字零美元符本地货币符号小数点千位符3-32Copyright©OracleCorporation,2001.Allrightsreserved.SELECTTO_CHAR(salary,'$99,999.00')SALARYFROMemployeesWHERElast_name='Ernst';TO_CHAR函数对数字的转换3-33Copyright©OracleCorporation,2001.Allrightsreserved.TO_NUMBER和TO_DATE函数•使用TO_NUMBER函数将字符转换成数字:•使用TO_DATE函数将字符转换成日期:•这些函数可以使用fx修饰符。nTO_NUMBER(char[,'format_model'])TO_DATE(char[,'format_model'])3-34Copyright©OracleCorporation,2001.Allrightsreserved.TO_NUMBER和TO_DATE函数•使用TO_NUMBER函数将字符转换成数字:•使用TO_DATE函数将字符转换成日期:•这些函数可以使用fx修饰符。TO_NUMBER(char[,'format_model'])TO_DATE(char[,'format_model'])3-35Copyright©OracleCorporation,2001.Allrightsreserved.RR日期格式当前年1995