《oracle教学》尚硅谷_宋红康_03_单行函数

1、第第3 3节节单单 行行 函函 数数讲师：宋红康讲师：宋红康 新浪微博：尚硅谷新浪微博：尚硅谷- -宋红康宋红康目目 标标通过本章学习，您将可以: SQL中不同类型的函数中不同类型的函数 在在 SELECT 语句中使用字符，数字，日期和语句中使用字符，数字，日期和转换函转换函数数 使用条件表达式使用条件表达式SQL 函数函数输入输入 函数执行函数执行输出输出y = f(x1,x2)两种两种 SQL 函数函数单行函数单行函数单行函数:操作数据对象接受参数返回一个结果只对一行进行变换只对一行进行变换每行返回一个结果每行返回一个结果可以转换数据类型可以嵌套参数可以是一列或一个值function_na

2、me (arg1, arg2,.)单行函数单行函数字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE函数函数结果结果大小写控制函数大小写控制函数这类函数改变字符的大小写。LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course大小写控制函数大小写控制函数显示员工 Higgins的信息:SELECT employee_id, last_name, department_idFROM employee

3、sWHERE last_name = higgins;SELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = higgins;CONCAT(Hello, World)SUBSTR(HelloWorld,1,5)LENGTH(HelloWorld)INSTR(HelloWorld, W)LPAD(salary,10,*)RPAD(salary, 10, *)TRIM(H FROM HelloWorld)REPLACE(abcd,b,m)HelloWorldHello106*2400024

4、000*elloWorldamcd函数函数结果结果字符控制函数字符控制函数这类函数控制字符:SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, a) Contains a?FROM employeesWHERE SUBSTR(job_id, 4) = REP;字符控制函数字符控制函数123123数字函数数字函数 ROUND: 四舍五入四舍五入 TRUNC: 截断截断 MOD: 求余求余MOD(1600, 300) 100SELECT ROUND(

5、45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;ROUND ROUND 函数函数DUALDUAL 是一个是一个伪表伪表，可以用来测试函数和表达式，可以用来测试函数和表达式123312SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;TRUNC TRUNC 函数函数312123SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;MOD MOD 函数函数日

6、日 期期 Oracle 中的日期型数据实际含有两个值: 日期和时间。SELECT last_name, hire_dateFROM employeesWHERE last_name like G%日日 期期函数SYSDATE 返回: 日期 时间日期的数学运算日期的数学运算 在日期上加上或减去一个数字结果仍为日期。 两个日期相减返回日期之间相差的两个日期相减返回日期之间相差的天数天数。 日期不允许做加法运算，无意义 可以用数字除24来向日期中加上或减去天数。日期的数学运算日期的数学运算SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM emp

7、loyeesWHERE department_id = 90;日期函数日期函数两个日期相差的月数两个日期相差的月数MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向指定日期中加上若干月数向指定日期中加上若干月数指定日期的下一个星期指定日期的下一个星期 * 对应的日对应的日期期本月的最后一天本月的最后一天日期四舍五入日期四舍五入 日期截断日期截断函数函数描述描述 MONTHS_BETWEEN (01-SEP-95,11-JAN-94)日期函数日期函数 ADD_MONTHS (11-JAN-94,6) NEXT_DAY (01-SEP-95,F

8、RIDAY) LAST_DAY(01-FEB-95)11-JUL-9408-SEP-9528-FEB-95 ROUND(SYSDATE,MONTH) 01-AUG-95 ROUND(SYSDATE ,YEAR) 01-JAN-96 TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(SYSDATE ,YEAR) 01-JAN-95日期函数日期函数Assume SYSDATE = 25-JUL-95:yyyy 年年mm 月月dd 日日day 星期星期 hh 小时小时 mi 分钟分钟 ss 秒秒转换函数转换函数隐式数据类型转换隐式数据类型转换Oracle 自动完成下列转换：

9、VARCHAR2VARCHAR2 or CHAR or CHAR源数据类型源数据类型目标数据类型目标数据类型VARCHAR2 or CHARVARCHAR2 or CHARNUMBERNUMBERDATEDATENUMBERNUMBERDATEDATEVARCHAR2VARCHAR2VARCHAR2VARCHAR2varchar2numberdate显式数据类型转换显式数据类型转换NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR函数对函数对日期日期的转换的转换格式:必须包含在单引号中而且大小写敏感必须包含在单引号中而且大小写敏感。

10、可以包含任意的有效的日期格式有效的日期格式。日期之间用逗号隔开日期之间用逗号隔开。TO_CHAR(date, format_model)SELECT TO_CHAR(sysdate,yyyy-mm-dd hh:mi:ss) FROM dual;YYYY日期格式的元素日期格式的元素YEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02日期格式的元素日期格式的元素 时间格式 使用双引号向日期中添加字符使用双引号向日期中添加字符HH24:MI:SS AMHH24:MI:SS AM15:45:32 15:45:32 PMPM

11、DD of MONTHDD of MONTH12 12 of OCTOBERof OCTOBERTO_CHAR 函数对函数对日期日期的转换的转换SELECT last_name, TO_CHAR(hire_date, DD Month YYYY) AS HIREDATEFROM employees;select employee_id,last_name,hire_datefrom employeeswhere to_char(hire_date,yyyy-mm-dd) = 1987-09-17TO_DATE 函数对函数对字符字符的转换的转换 使用 TO_DATE 函数将字符转换成数字:TO_

12、DATE(char, format_model) 使用 TO_DATE :TO_DATE(2012年年10月月29日日 08:10:21,yyyy“年年”mm”月月”dd“日日”hh:mi:ss)From dual练习：返回hire_date 为 */*/*的员工信息，使用显示日期表达TO_CHAR函数对函数对数字数字的转换的转换下面是在TO_CHAR 函数中经常使用的几种格式:TO_CHAR(number, format_model)9 90 0$ $L L. ., ,数字数字零零美元符美元符本地货币符号本地货币符号小数点小数点千位符千位符SELECT TO_CHAR(salary, $99

13、,999.00) SALARYFROM employeesWHERE last_name = Ernst;TO_CHAR函数对函数对数字数字的转换的转换TO_NUMBER 函数对函数对字符字符的转换的转换 使用 TO_NUMBER 函数将字符转换成日期:TO_NUMBER(char, format_model) 使用 TO_NUMBER :TO_NUMBER(￥1,234,567,890.00,L999,999,999,999.99)from dual通用函数通用函数这些函数适用于任何数据类型，同时也适用于适用于任何数据类型，同时也适用于空值空值： NVL (expr1, expr2) NVL

14、2 (expr1, expr2, expr3) NULLIF (expr1, expr2) COALESCE (expr1, expr2, ., exprn)NVL 函数函数将空值转换成一个已知的值将空值转换成一个已知的值： 可以使用的数据类型有日期、字符、数字。 函数的一般形式: NVL(commission_pct,0) NVL(hire_date,01-JAN-97) NVL(job_id,No Job Yet)练习1：求公司员工的年薪（含commission_pct)练习2：输出last_name,department_id，当department_id为null时，显示没有部门。SE

15、LECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0) AN_SALFROM employees;使用使用NVL函数函数1212SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) incomeFROM employees WHERE department_id IN (50, 80);使用使用 NVL2 函数函数1212NVL2 (expr1, expr2,

16、 expr3) : expr1不为不为NULL，返回，返回expr2；为；为NULL，返回，返回expr3。exp1 != null ? exp2 : exp3练习：查询员工的奖金率，若为空，返回0.01,若不为空，返回实际奖金率SELECT first_name, LENGTH(first_name) expr1, last_name, LENGTH(last_name) expr2, NULLIF(LENGTH(first_name), LENGTH(last_name) resultFROM employees;使用使用 NULLIF 函数函数123123NULLIF (expr1, e

17、xpr2) : 相等返回NULL，不等返回expr1 使用使用 COALESCE 函数函数 COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;使用使用 COALESCE 函数函数条件表达式条件表达式 在 SQL 语句中使用IF-THEN-ELSE 逻辑 使用两种方法: CASE 表达式

18、 DECODE 函数CASE 表达式表达式在需要使用 IF-THEN-ELSE 逻辑时:CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND练习：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数SELECT last_na

19、me, job_id, salary, CASE job_id WHEN 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 表达式表达式下面是使用case表达式的一个例子：DECODE 函数函数在需要使用 IF-THEN-ELSE 逻辑时:DECODE(col|expression, search1, result1 , , search2, result2,., , default)DECODE 函数函数SELECT last_name, job_id, salary, DECODE(job_id, IT_PROG, 1.10