《单行函数》PPT课件.ppt
46页
单行函数单行函数 目标•掌握SQL中不同类型的函数•掌握在 SELECT 语句中使用字符，数字和日期函数•描述转换型函数的用途 单行函数单行函数:•操作数据对象•接受函数返回一个结果•只对一行进行变换•每行返回一个结果•可以转换数据类型•可以嵌套•参数可以是一列或一个值function_name [(arg1, arg2,...)] 单行函数ConversionConversionCharacterCharacterNumberNumberDateDateGeneralGeneralSingle-row Single-row functionsfunctions FunctionResult大小写控制函数这类函数改变字符的大小写LOWER('SQL Course')UPPER('SQL Course')INITCAP('SQL Course')sql courseSQL COURSESql Course 大小写控制函数显示员工 Higgins的信息:SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = 'higgins';no rows selectedno rows selectedSELECT 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’)HelloWorldHello106*****2400024000*****elloWorldFunctionResult字符控制函数这类函数控制字符: 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: 四舍五入ROUND(45.926, 2)45.93•TRUNC: 截断(保留小数点)TRUNC(45.926, 2) 45.92•MOD: 求余MOD(1600, 300)100 SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;ROUND 函数DUAL 是一个是一个‘伪表伪表’，可以用来测试函数和表达式。
可以用来测试函数和表达式123312 SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;TRUNC 函数312123 SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = 'SA_REP';MOD 函数取模运算 日期•Oracle 内部使用数字存储日期: 世纪,年,月,日,小时,分钟,秒•默认的日期格式是 DD-MON-RR.SELECT last_name, hire_dateFROM employeesWHERE last_name like ' 'G%';'; 日期函数SYSDATE 返回:•日期•时间 日期的数学运算•在日期上加上或减去一个数字结果仍为日期•两个日期相减返回日期之间相差的天数•可以用数字除24来向日期中加上或减去小时 日期的数学运算SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90; 日期函数两日期相差多少月两日期相差多少月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')•SELECT NEXT_DAY(TO_DATE(‘2006-08-15’,‘YYYY-MM-DD’),‘星期二') FROM EMP •LAST_DAY('01-FEB-95') 19.6774194'08-'11-JUL-94'SEP-95''28-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': TO_CHAR 函数对日期的转换格式:•必须包含在单引号中而且大小写敏感。
•可以包含任意的有效的日期格式•可以使用 fm 去掉多余的空格或者前导零•与日期只用逗号隔开TO_CHAR(date, 'format_model') YYYY日期格式的元素YEARMMMONTHDYDAY以数字表示全年以数字表示全年拼写出的年拼写出的年两位数的月两位数的月三个字母三个字母缩写的一个星期中的天写的一个星期中的天星期的全拼星期的全拼全月名全月名MON三个字母的月三个字母的月缩写写DD数字日数字日 日期格式的元素•时间格式作为日期的一部分.•用双引号括起来以加字符串.HH24:MI:SS AM15:45:32 PMDD”日日” MONTH”月月”12 日日 OCTOBER月月 TO_CHAR 函数对日期的转换SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATEFROM employees;… TO_CHAR 函数对数字的转换下面是在TO_CHAR 函数中经常使用的几种格式: 如:select to_char(sal,'$99G999D9999') from empTO_CHAR(number, 'format_model')90$L.,代表一个数字代表一个数字强制制显示示0放置一个放置一个$符符放置一个浮放置一个浮动本地本地货币符符显示小数点示小数点D显示千位指示符示千位指示符G(通常和通常和D搭配搭配) SELECT TO_CHAR(salary, '$99,999.00') SALARYFROM employeesWHERE last_name = 'Ernst';TO_CHAR函数对数字的转换•一般将.放在,后 TO_NUMBER 和 TO_DATE 函数 •使用 TO_NUMBER 函数将字符转换成数字:TO_NUMBER(char[, 'format_model'])TO_DATE(char[, 'format_model']) 如如:select to_number('100.00','999,999.999999') from emp .前面的数字个数必须大于等于前面的数字个数必须大于等于char中中.前面的位数前面的位数如如:SELECT TO_DATE( ‘2006-08-15 星期二星期二', 'yyyy-mm-dd day') FROM DUAL RR 日期格式Oracle9i 中的缺省日期格式DD-MON-RROracle 为了解决千年问题， 而引入了RR 日期型格式1、如果当前年份的最后两位数（即不包括世纪）为0—49，并且指定的年份的最后两位数也为0—49，则返回的日期在本世纪2、如果当前年份的最后两位数（即不包括世纪）为0—49，并且指定的年份的最后两位数为50—99，则返回的日期在上一世纪3、如果当前年份的最后两位数（即不包括世纪）为50—99，并且指定的年份的最后两位数为0—49，则返回的日期在下一世纪4、如果当前年份的最后两位数（即不包括世纪）为50—99，并且指定的年份的最后两位数也为50—99，则返回的日期在本世纪 举例:select to_char(to_date('09-feb-98','DD-MON-RR'), 'yyyy-mm-dd') from dual返回结果为:1998-02-09 RR 日期格式 使用RR日期格式查找雇佣日期在1990年之前的员工，在1999或2009年，使用下面的命令会产生相同的结果:SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')FROM employeesWHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR'); 通用函数这些函数适用于任何数据类型，同时也适用于空值：•NVL (expr1, expr2)•NVL2 (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') SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (12*NVL(commission_pct, 0)) AN_SALFROM employees;使用NVL函数…1212•如果表达式1的值为null,返回表达式2的值 SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') incomeFROM employees WHERE department_id IN (50, 80);使用 NVL2 函数1212•如果表达式1的值不为null,返回表达式2,否则返回表达式3 SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) resultFROM employees;使用 NULLIF 函数…123123•当表达式1等于表达式2时,返回null,否则返回表达式1的值 使用 COALESCE 函数•如果表达式1的值不为空,返回表达式1的值,否则返回表达式2的值,如果表达式2的值为空,返回表达式3的值•COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
•即返回参数列表中第一个非空表达式•如: SELECT sal, comm, coalesce(comm, sal, 100) FROM EMP 通常最后一个参数会是一个常量 SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;使用 COALESCE 函数… 条件表达式•在 SQL 语句中使用IF-THEN-ELSE 逻辑•使用两种方法:–CASE 表达式–DECODE 函数 CASE 表达式在需要使用 IF-THEN-ELSE 逻辑时:expr不能为null和比较运算符CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr]END SELECT last_name, 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_SALARY"FROM employees;CASE 表达式下面是使用case表达式的一个例子：…… DECODE 函数在需要使用 IF-THEN-ELSE 逻辑时:expression不能为NULL和比较运算符DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE 函数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;…… DECODE 函数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_RATEFROM employeesWHERE department_id = 80;使用decode函数的一个例子： 嵌套函数•单行函数可以嵌套。
•嵌套函数的执行顺序是由内到外F3(F2(F1(col,arg1),arg2),arg3)Step 1 = Result 1Step 2 = Result 2Step 3 = Result 3 SELECT last_name, NVL(TO_CHAR(manager_id), 'No Manager')FROM employeesWHERE manager_id IS NULL;嵌套函数 总结通过本章学习，您应该学会: •使用函数对数据进行计算•使用函数修改数据•使用函数控制一组数据的输出格式•使用函数改变日期的显示格式•使用函数改变数据类型•使用 NVL 函数•使用IF-THEN-ELSE 逻辑 谢谢 谢！谢！ 。
