8--Oracle函数

函数--必须有返回值---相当于其他语言的方法或过程---分为单行函数 和多行函数(多行返回一个结果)--单行函数--操作数据项--接受参数并返回处理结果--对每一返回行其作用--可修改数据类型--可嵌套使用--单行函数分类--字符函数--数值函数--日期函数--转换函数--通用函数--字符函数--lower()--转换为小写-->lower('John Smith')-->john smith--upper()--转换为大写-->upper('John Smith')-->JOHN SMITH--initcap()--单词首字母大写-->initcap('John Smith')-->John Smith --字符处理函数--concat()-->字符串链接-->concat('Hello','Woeld')-->Hello World--substr()-->截取字串-->substr('HelloWorld',4,3)-->loW--从第四位开始截取3个字符--length()-->返回字符串长度-->length('HelloWorld')-->11--instr()-->定为子串-->instr('Hello World','or')-->8--定位or的位置，从第8位开始--lpad()-->左侧填充-->lpad('Smith',10,'*')-->*****Smith--rpad()-->右侧填充-->rpad('Smith',10,'*')-->Smith*****--trim()-->过滤首位空格-->trim(' Mr Smith ')-->Mr Smith--replace-->替换-->replace('Mr Smith','Mr','Mrs')-->Mrs Smith--数值函数--abs()-->取绝对值-->abs(-3.14)-->3.14--round()-->四舍五入-->round(3.1415)-->3或者round(3.1415,3)-->3.142或者round(3.1415,-2)-->300四舍五入到左侧两位--trunc()-->截断-->trunc(3.1415,3)-->3.141--ceil()-->向上取值-->ceil(3.14)-->4--floor()-->向下取值-->floor(3.14)-->3--sign()-->判断数值正负-->sign(-3.14)-->-1--sin()-->三角函数--power()-->幂运算--sqrt()-->开平方根--mod()-->取模--exp()-->基数为e的幂运算--log()-->对数运算--ln()-->自然对数运算***************************************日期类型缺省为DD-MM-YY可使用sysdate函数获取当前系统日期和时间日期型数据的算术运算--日期型函数可以直接加或减一个数值，结果仍为日期--select ename,hiredate,hiredate+10 from emp;--两个日期型数据间可以相减，结果为二者相差多少天-- select empno,hiredate,sysdate-hiredate from emp;日期函数--add_months(x,y)-->计算在日期x的基础上增加y个月后的日期-->select add_months(sysdate,2)from dual;--last_day(x)-->返回日期x当月最后一天的日期-->select last_day(sysdate)from dual;--months_between(x,y)-->返回日期x和y之间相差的月数-->select months_between(sysdate,hiredate)from emp;--round(x,y)-->将日期x四舍五入到y所指定的日期单位(月或年)的第一天-->select round(to_date('14-3月-08'),'month')from emp;--trunc(x,y)-->讲日期x截断到y所指定的日期的单位(月或年)的第一天-->select trunc(sysdate,'year')from emp;--next_day(x,y)-->计算指定日期x后的第一个星期几(由参数y指定)对应的日期-->select next_day(sysdate,'星期二')from emp;**************************转换函数--实现数据类型转换--隐式转换(与系统类型一致)和显式转换-->insert into test values('Tom',to_date('2007-02-13','YYYY-MM-DD')); --to_number()转换成数值型 --to_date()转换成日期型 --to_char()转换成字符型--to_char(date)或to_char(date,'format_model')--常用日期格式符--yyyy--mm--dd--d(一个星期中的第几天)--ddd(一年中的第几天)day(星期几)--am/pm--hh/hh12/hh24--mi-ss--要想显示年月日 要用""引起来 --如 yyyy"年"mm"月"dd"日"--to_date(char)或to_char(char,'format_model')--to_char(number)或to_char(number,'format_model')-----select empno,ename,sal,to_char(sal,'L000,000.000') from emp;---L-->放置一个本地货币符，$-->放置一个美国符$,9-->代表一个数字，0-->代表以为数字，强制显示0，，-->千分位，.-->小数点--to_number(char)或to_number(char,'format_model')--select to_number('$1,600.00','$9,999.9999') from dual;****************************************----通用函数(适用于任何数据类型--(包括空值))nvl()----nvl2()----nullif()----coalesce()-----case表达式------decode()--NVL()--NVL()函数用于将空值null替换为指定的缺省值，适用于字符、数字、日期等类型数据NVL(exp1,exp2)---如果表达式的值为怒null，则返回exp2的值，否则返回exp1的值----select ename,sal,comm,sal+nvl(comm,0) as 总收入 from emp;---(比较)select ename,sal,comm,sal+comm as 总收入 from emp;根据习惯，如果comm的值为空值，则sal+comm的值为空，这是不符合常规的，这时候用nvl函数则返回一个真正的sal+comm的值--NVL2()---NVL2(exp1,exp2,exp3)---如果表达式exp1的值不为null，则返回exp2的值，否则返回exp3的值----select empno,ename,sal,comm,nvl2(comm,sal+comm,sal) total from emp;--nullif()---用于数据的等价性比较并根据比较结果返回null或其中一个被比较的数值nullif(exp1,exp2)---如果表达式exp1与exp2的值相等则返回null，否则返回exp1 的值----select name 原名,nullif(pen_name,name)化名 from author;--coalesce()---实现数据的"接口"功能 ---coalesce(exp1,exp2,....)依次考察各参数表达式，遇到非null值即停止并返回该值----select empno,ename,sal,coalesce(sal+comm,sal,0) total from emp;!!!!!!!!!!!!-----CASE表达式(类似于swith)----用于实现多路分支结构case exp when comparison_exp1 then return_exp1[when comparison_exp2 then return_exp2else else_exp ]end----select empno,ename,sal, case deptno when 10 then '财务部' when 20 then'研发部' when 30 then'销售部' else '未知部门' end 部门 from emp;!!!!!!!!!!!!--------DECODE()函数---和case表达式类似，decode()函数也用于实现多路分支结构---语法格式decode(col|expression,search1,result1[,search2,result2,...,][,default])------select empno,ename,sal,decode( deptno ,10 , '财务部', 20 ,'研发部',30 ,'销售部','未知部门')部门 from emp;----函数嵌套--当行函数可以嵌套使用，嵌套层次无限制--嵌套函数的执行顺序是由内到外----select empno,lpad(initcap(trim(ename)),10,'*')name,job,sal from emp;!!!!------当行函数小结--------!!!!--使用系统提供的当行函数可实现如下功能--对数据进行计算--控制数据的输出格式--设置/改变日期的显示格式--进行数据类型转换--使用nvl函数处理空值--实现IF-THEN-ELSE多路分支逻辑---------分组函数---对一组数据进行运算，针对一组数据(多行记录)只返回一个结果，也称多行函数--常用分组函数----avg()----count()-----max()-----min()-----sum()----select avg(sal),max(sal),min(sal),sum(sal) from emp;count()函数----count(*)返回组中总记录数目----count(*)返回表达式exp1值非空的记录数目----count(distinct(exp))返回表达式exp值不重复的、非空的记录数目---select count(distinct(depyno)) from emp;---分组函数与空值---分组函数省略列中的空值---可使用NVL()函数强制分组函数处理空值---建立数据分组--使用group by子句 讲表中数据分成若干小组---语法格式--select column_function(column) from table [where condition][group by **][order by column];---select deptno,avg(sal) from emp group by deptno;--说明--*出现在Select列表中的字段，如果不是包含在组函数中，那么该字段必须同时在groupby子句中出现*包含group by 子句中的字段则不必须出现在Select列表中*可使用where子句限定查询条件*可使用orderby子句指定排序方式--基于多字段分组--select deptno,job,avg(sal) from emp group by deptno,job;------组函数的错误用法1.当行函数与多行函数混用---select empno,initcap(ename),avg(sal) from emp;-->×2.不允许在where子句中使用分组函数---select deptno,avg(sal) from emp where avg(sal)>2000;-->×-----having 子句----过滤子句语法--select col,group(col) from table [where condition] [group by group][having g*][order by col];---select deptno,job,avg(sal) from emp where hiredate>= to_date('1982-05-01','yyyy-mm-dd') group by deptno,job order by deptno,job;---select deptno,job,avg(sal) from emp where empno < 20000 group by deptno,job having avg(sal) > 2000;---分组函数嵌套---分组函数最多可嵌套两层