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_exp2 else 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; ---分组函数嵌套---分组函数最多可嵌套两层