ch303单行函数和多表查询-第04章

第4章

单行函数己欲立而立人，己欲达而达人2励志逆境成长3如何使用查询语句选择列、过滤行？回顾与作业点评如何去掉重复行？如何对过滤后的数据进行排序？5预习检查什么是函数？和JS中的函数有什么区别?常用的oracle函数有哪些?举几个例子?6内容进度Oracle的函数字符处理类函数数字、日期函数转换函数通用函数8Oracle的函数什么是函数Oracle的函数和JavaScript中的函数(java中的方法)一样，能完成一定的功能，比如字符大小写转换、日期格式处理等。函数分类根据函数的功能可以大概分为以下四类函数字符处理类函数数字、日期类函数转换函数通用函数14内容进度字符处理类函数Oracle的函数数字、日期函数转换函数通用函数8字符处理类函数lower转换为小写需求1:把ename字段转换成小写 selectlower(ename)fromemp;需求2:查询scott用户的用户名称和账户状态

命令行下输入sqlplus/assysdba;--使用系统管理员登录

因为数据库中scott是以大写形式”SCOTT”存储的所以无法找到对应记录,如果想找到scott用户的信息需要进行大小写转换

14字符处理类函数其他字符处理类函数

14字符处理类函数字符处理类函数举例

14内容进度数字、日期函数Oracle的函数字符处理类函数转换函数通用函数8数字、日期函数数字函数Round(参数1,[参数2])

功能:四舍五入，参数2可选，参数2表示保留几位小数

Trunc(参数1,[参数2])

功能:截断数字(直接忽略),参数2表示截断到的小数位，如果参数2忽略则默认为0

14数字、日期函数日期函数需求1:获得系统当前时间需求2:想知道雇员表中，部门编号为60的雇员，截止到今天共工作了多少周，则可以使用如下的SQL语句

14数字、日期函数日期函数需求3:下周三是几号?

使用函数NEXT_DAY(date,char) date指定日期,char周几，计算指定日期的下一个周几是几号

注意:1表示周日2表示周1以此类推

ROUND和TRUNC函数用于日期时，这些函数按指定的格式化模板四舍五入或截断，默认到日，也可以四舍五入或截断到月或年,如上图所示

14数字、日期函数其他日期函数

14数字、日期函数日期函数举例ROUND和TRUNC函数用于日期时，这些函数按指定的格式化模板四舍五入或截断，默认到日，也可以四舍五入或截断到月或年

14数字、日期函数日期函数在英文系统中，月用三个字母作为缩写1到12月分别是：JAN、FEB、MAR、APR、MAY、JUN、JUL、AUG、SEP、OCT、NOV、DEC，周用具体的英文字母表示周1到周日分别是：SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY、SATURDAY在中文系统（字符集）下，月和周的表示方式更简单，直接用1月、2月…12月，星期一、星期二…星期日表示

14内容进度转换函数Oracle的函数字符处理类函数数字、日期函数通用函数8转换函数to_char(参数)功能:将指定参数转换成字符

需求1:转换货币格式 selectto_char(sal,‘$99,999.9999’)

as工资fromemp; 9表示一位数字，如果该位没有数字则不显示,但小数点后的数字

必须显示,如果如右图所示14转换函数to_char(参数)

需求2:格式化日期格式 selectto_char(hiredate,'YYYY-MM-DDHH:MI:SS')fromemp; selectto_char(sysdate,'YYYY-MM-DDHH:MI:SS')fromdual; selectto_char(sysdate,'YYYY-MM-DDHH24:MI:SS')fromdual;

注意: oracle日期大小写都一样，java大小写不一样

分和java不一样用MI表示java用mm表示 oracle24小时制用HH24表示java用HH表示14转换函数to_date(参数)

功能:将指定参数转换成date类型

需求:查询入职日期在‘1981-2-2012:34:56’

之后的员工的姓名和入职日期 selectename,hiredatefromemp wherehiredate>to_date('1981-2-2012:34:56','YYYY-MM-DDHH24:MI:SS');to_number(参数)

功能:将指定参数转换成number类型 selectsalfromemp wheresal>to_number('$1,250.00','$9,999.99');14内容进度通用函数Oracle的函数字符处理类函数数字、日期函数转换函数8通用函数NVL函数求真正的年薪(带年终奖),使用如下语句会发生错误,如下左图 selectename,sal*masannual_salfromemp 原因是null和任何数参与四则运算都返回null-nvl(comm,0)如果第一个参数为null，则返回第二个参数(0)，如果第一个参数不为null,则函数返回第一个参数本身selectename,sal*12+nvl(comm,0)asannual_salfromemp如右图14通用函数NVL2函数NVL2(参数1，参数2，参数3)

功能:参数1不是null返回参数2

参数1是null返回参数3

需求:有年终奖comm的员工过节费是月薪的30%

没有年终奖的员工过节费是月薪的50% selectename,nvl2(comm,sal*0.3,sal*0.5)as过节费fromemp14通用函数COALESCE函数COALESCE函数比NVL函数功能强大，它能够接受多个交替的值，其语法形式如下COALESCE(expr1,expr2,…exprn)

作用:如果表达式1为非空，则返回表达式1的值；如果表达式1为空，则返回表达式2的值，依次类推，如果前面的表达式都为空，则返回表达式n的值

需求:如果员工有年终奖comm,则返回年终奖数值

没有年终奖时，如果有上司则返回上司编号，没有上司则该员工是老板，显示“老板”即可 selectename,coalesce(to_char(comm),to_char(mgr),'老板')fromemp;14课堂练习01:002734