oracle教学尚硅谷宋红康单行函数实用教案

1、目目 标标通过本章学习，您将可以:SQL中不同类型的函数在 SELECT 语句中使用(shyng)字符，数字，日期和转换函数使用(shyng)条件表达式第1页/共50页第一页，共51页。SQL 函数函数(hnsh)输入 函数(hnsh)执行输出y = f(x1,x2)第2页/共50页第二页，共51页。两种两种 SQL 函数函数(hnsh)第3页/共50页第三页，共51页。单行单行(dn xn)(dn xn)函数函数单行函数:操作数据对象接受参数返回一个结果只对一行进行变换每行返回一个结果可以(ky)转换数据类型可以(ky)嵌套参数可以(ky)是一列或一个值function_name (arg1

2、, arg2,.)第4页/共50页第四页，共51页。单行单行(dn xn)(dn xn)函数函数第5页/共50页第五页，共51页。字符字符(z f)(z f)函数函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE第6页/共50页第六页，共51页。函数(hnsh)结果(ji gu)大小写控制大小写控制(kngzh)(kngzh)函数函数这类函数改变字符的大小写。LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Cou

3、rse第7页/共50页第七页，共51页。大小写控制大小写控制(kngzh)(kngzh)函数函数显示员工(yungng) Higgins的信息:SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = higgins;SELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = higgins;第8页/共50页第八页，共51页。CONCAT(Hello, World)SUBSTR(HelloWorl

4、d,1,5)LENGTH(HelloWorld)INSTR(HelloWorld, W)LPAD(salary,10,*)RPAD(salary, 10, *)TRIM(H FROM HelloWorld)REPLACE(abcd,b,m)HelloWorldHello106*2400024000*elloWorldamcd函数(hnsh)结果(ji gu)字符控制字符控制(kngzh)(kngzh)函数函数这类函数控制字符:第9页/共50页第九页，共51页。SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENG

5、TH (last_name), INSTR(last_name, a) Contains a?FROM employeesWHERE SUBSTR(job_id, 4) = REP;字符控制字符控制(kngzh)(kngzh)函数函数123123第10页/共50页第十页，共51页。数字数字(shz)(shz)函数函数 ROUND: 四舍五入四舍五入(s sh w r) ROUND(45.926, 2)45.93 TRUNC: 截断截断 TRUNC(45.926, 2)45.92 MOD: 求余求余 MOD(1600, 300) 100第11页/共50页第十一页，共51页。SELECT ROUN

6、D(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;ROUND ROUND 函数函数(hnsh)(hnsh)DUAL DUAL 是一个(y )(y )伪表，可以用来测试函数和表达式123312第12页/共50页第十二页，共51页。SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;TRUNC TRUNC 函数函数(hnsh)(hnsh)312123第13页/共50页第十三页，共51页。SELECT last_name, salary, MOD(salary,

7、 5000)FROM employeesWHERE job_id = SA_REP;MOD MOD 函数函数(hnsh)(hnsh)第14页/共50页第十四页，共51页。日日 期期 Oracle 中的日期型数据实际含有两个(lin )值: 日期和时间。SELECT last_name, hire_dateFROM employeesWHERE last_name like G%第15页/共50页第十五页，共51页。日日 期期函数SYSDATE 返回(fnhu):日期时间第16页/共50页第十六页，共51页。日期日期(rq)(rq)的数学运算的数学运算 在日期上加上或减去一个数字结果(ji gu

8、)仍为日期。 两个日期相减返回日期之间相差的天数。 日期不允许做加法运算，无意义 可以用数字除24来向日期中加上或减去天数。第17页/共50页第十七页，共51页。日期的数学日期的数学(shxu)(shxu)运算运算SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;第18页/共50页第十八页，共51页。日期日期(rq)(rq)函数函数两个(lin )日期相差的月数MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向指定(z

9、hdng)日期中加上若干月数指定日期的下一个星期 * 对应的日期本月的最后一天日期四舍五入 日期截断函数描述第19页/共50页第十九页，共51页。 MONTHS_BETWEEN (01-SEP-95,11-JAN-94)日期日期(rq)(rq)函数函数 ADD_MONTHS (11-JAN-94,6) NEXT_DAY (01-SEP-95,FRIDAY) LAST_DAY(01-FEB-95) 19.677419411-JUL-9408-SEP-9528-FEB-95第20页/共50页第二十页，共51页。 ROUND(SYSDATE,MONTH) 01-AUG-95 ROUND(SYSDAT

10、E ,YEAR) 01-JAN-96 TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(SYSDATE ,YEAR) 01-JAN-95日期日期(rq)(rq)函数函数Assume SYSDATE = 25-JUL-95:yyyy 年年mm 月月dd 日日day 星期星期(xngq) hh 小时小时 mi 分钟分钟 ss 秒秒第21页/共50页第二十一页，共51页。转换转换(zhunhun)(zhunhun)函数函数第22页/共50页第二十二页，共51页。隐式数据隐式数据(shj)(shj)类型转换类型转换Oracle 自动(zdng)完成下列转换：VARCHAR2V

11、ARCHAR2 or CHAR or CHAR源数据类型目标(mbio)(mbio)数据类型VARCHAR2 or CHARVARCHAR2 or CHARNUMBERNUMBERDATEDATENUMBERNUMBERDATEDATEVARCHAR2VARCHAR2VARCHAR2VARCHAR2varchar2numberdate第23页/共50页第二十三页，共51页。显式数据显式数据(shj)(shj)类型转换类型转换NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE第24页/共50页第二十四页，共51页。TO_CHAR函数对日期(rq)的

12、转换格式:必须包含在单引号中而且大小写敏感。可以包含任意的有效(yuxio)的日期格式。日期之间用逗号隔开。TO_CHAR(date, format_model)SELECT TO_CHAR(sysdate,yyyy-mm-dd hh:mi:ss) FROM dual;第25页/共50页第二十五页，共51页。YYYY日期日期(rq)(rq)格式的元素格式的元素YEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02第26页/共50页第二十六页，共51页。日期日期(rq)(rq)格式的元素格式的元素 时间格式 使用双引号

13、向日期中添加(tin ji)字符HH24:MI:SS AMHH24:MI:SS AM15:45:32 15:45:32 PMPMDD of MONTHDD of MONTH12 12 of OCTOBERof OCTOBER第27页/共50页第二十七页，共51页。TO_CHAR 函数对日期(rq)的转换SELECT last_name, TO_CHAR(hire_date, DD Month YYYY) AS HIREDATEFROM employees;select employee_id,last_name,hire_datefrom employeeswhere to_char(hire

14、_date,yyyy-mm-dd) = 1987-09-17第28页/共50页第二十八页，共51页。TO_DATE 函数函数(hnsh)对字符的转换对字符的转换 使用 TO_DATE 函数(hnsh)将字符转换成数字:TO_DATE(char, format_model) 使用(shyng) TO_DATE :TO_DATE(2012年10月29日 08:10:21,yyyy“年”mm”月”dd“日”hh:mi:ss)From dual练习：返回hire_date 为 */*/*的员工信息，使用显示日期表达第29页/共50页第二十九页，共51页。TO_CHAR函数对数字函数对数字(shz)的转

15、换的转换下面是在TO_CHAR 函数中经常使用(shyng)的几种格式:TO_CHAR(number, format_model)9 90 0$ $L L. ., ,数字(shz)(shz)零美元符本地货币符号小数点千位符第30页/共50页第三十页，共51页。SELECT TO_CHAR(salary, $99,999.00) SALARYFROM employeesWHERE last_name = Ernst;TO_CHAR函数函数(hnsh)对数字的转换对数字的转换第31页/共50页第三十一页，共51页。TO_NUMBER 函数对字符函数对字符(z f)的转换的转换 使用 TO_NUMB

16、ER 函数(hnsh)将字符转换成日期:TO_NUMBER(char, format_model) 使用(shyng) TO_NUMBER :TO_NUMBER(￥1,234,567,890.00,L999,999,999,999.99)from dual第32页/共50页第三十二页，共51页。通用通用(tngyng)(tngyng)函数函数这些函数适用于任何(rnh)数据类型，同时也适用于空值：NVL (expr1, expr2)NVL2 (expr1, expr2, expr3)NULLIF (expr1, expr2)COALESCE (expr1, expr2, ., exprn)第3

17、3页/共50页第三十三页，共51页。NVL 函数函数(hnsh)将空值转换成一个将空值转换成一个(y )已知的值：已知的值：可以使用的数据类型有日期、字符、数字。可以使用的数据类型有日期、字符、数字。函数的一般形式函数的一般形式:NVL(commission_pct,0)NVL(hire_date,01-JAN-97)NVL(job_id,No Job Yet)练习1：求公司员工的年薪（含commission_pct)练习2：输出(shch)last_name,department_id，当department_id为null时，显示没有部门。第34页/共50页第三十四页，共51页。SELEC

18、T last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0) AN_SALFROM employees;使用使用(shyng)NVL函数函数1212第35页/共50页第三十五页，共51页。SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) incomeFROM employees WHERE department_id IN (50, 80);使用使用(shyng) N

19、VL2 函数函数1212NVL2 (expr1, expr2, expr3) : expr1不为不为(b wi)NULL，返回，返回expr2；为；为NULL，返回，返回expr3。exp1 != null ? exp2 : exp3练习：查询员工的奖金(jingjn)率，若为空，返回0.01,若不为空，返回实际奖金(jingjn)率+0.015第36页/共50页第三十六页，共51页。SELECT first_name, LENGTH(first_name) expr1, last_name, LENGTH(last_name) expr2, NULLIF(LENGTH(first_name)

20、, LENGTH(last_name) resultFROM employees;使用使用(shyng) NULLIF 函数函数123123NULLIF (expr1, expr2) : 相等(xingdng)返回NULL，不等返回expr1 第37页/共50页第三十七页，共51页。使用使用(shyng) COALESCE 函数函数 COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。 如果第一个(y )表达式为空,则返回下一个(y )表达式，对其他的参数进行COALESCE 。第38页/共50页第三十八页，共51页。SELECT last_name, C

21、OALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;使用使用(shyng) COALESCE 函数函数第39页/共50页第三十九页，共51页。条件条件(tiojin)(tiojin)表达式表达式 在 SQL 语句中使用IF-THEN-ELSE 逻辑 使用两种方法(fngf): CASE 表达式 DECODE 函数第40页/共50页第四十页，共51页。CASE 表达式表达式在需要(xyo)使用 IF-THEN-ELSE 逻辑时:CASE expr WHEN comparison_expr1 T

22、HEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND练习：查询部门(bmn)号为 10, 20, 30 的员工信息, 若部门(bmn)号为 10, 则打印其工资的 1.1 倍, 20 号部门(bmn), 则打印其工资的 1.2 倍, 30 号部门(bmn)打印其工资的 1.3 倍数第41页/共50页第四十一页，共51页。SELECT last_name, job_id, salary, CASE job_id WHEN

23、IT_PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARYFROM employees;CASE 表达式表达式下面是使用(shyng)case表达式的一个例子：第42页/共50页第四十二页，共51页。DECODE 函数函数(hnsh)在需要(xyo)使用 IF-THEN-ELSE 逻辑时:DECODE(col|expression, search1, result1 , , search2, result2,., , def

24、ault)第43页/共50页第四十三页，共51页。DECODE 函数函数(hnsh)SELECT last_name, job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARYFROM employees;第44页/共50页第四十四页，共51页。DECODE 函数函数(hnsh)SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATEFRO