六、自定函数和分组函数

1、Oracle中的自定义函数：

包括：字符串函数、日期函数、系统函数、数字函数、通用函数

--字符串函数：

1）lower(char):将字符串转化为小写的格式；

select lower(ename),sal from emp;

select lower(substr (ename,2,length(ename)-1)) from emp;

select lower(substr(ename,1,1)) from emp;

 

2）upper(char):将字符串转化为大写的格式；

select upper(ename), sal from emp;

select upper(substr(ename,1,1)) from emp;

select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

 

3）length(char):返回字符串的长度

select length(birthday) from student where xuehao='12';

 

4)substr(char,m,n):取字符串的长度；

select substr(birthday,3,5) from student where xuehao='12';

 

5)replace(char1,search_string,replace_string)

select replace(birthday,0,2) from student where xuehao='12';会把生日中为0的数字全部换为2

char1:表示对那个字符进行替换；  search_string：表示要被替换(淘汰的)；  replace_string：表示要拿新的什么来替换；

6)instr(sourceString,destString,start,appearPosition)函数语法

instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

 

从一个字符串中查找指定子串的位置

select instr('yuechaotianyuechao','ao') position from dual;

 

从第7个字符开始搜索

select instr('yuechaotianyuechao','ao', 7) position from dual;

 

从第1个字符开始，搜索第2次出现子串的位置

select instr('yuechaotianyuechao','ao', 1, 2) position from dual;

 

--日期函数：

1）sysdate   该函数返回系统时间

2）add_months(d,n) 表示从d对代表的日期开始再加上n个月的时间

select add_months(sysdate,3) from dual;

select add_months(birthday,12) from student where xuehao='2';

 

3)last_day(d)  返回指定日期所在月份的最后一天

select last_day(birthday) from student where xuehao='2';

4）to_char()日期转换函数

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

yy:两位数字的年份  2004-->4

yyyy:四位数字的年份 2004年

mm:2为数字的月份 8月-->08

dd:2位数字的天   30号-->30;

hh24:8点 --> 20

hh12:8点 --> 08

mi、ss   --> 显示分钟\秒

 

在薪水的前面加上RMB三个字母：

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'l99999.99' from emp )

l99999.99表示的是：要显示的是五位整数，后面带两位小数；

 

9：显示数字，并忽略前面0；

0：显示数字，如果位数不足，则用0补齐

.:在指定位置显示小数点

，：在指定位置显示逗号

￥：在数字前加美元

L：在数字前加人民币

C:在数字前加国际货币符号

G：在指定的位置显示分隔符

D:在指定位置显示小数点符号（）；

--在sal前加美元符号

select to_char(sal,'$999999.99') from student where xuehao='2';

 

显示出生日期在2001年之后的学生信息：

 select * from student where  to_char(birthday,'yyyy')>2001;

 

显示出生日期在10月之后的学生信息：

elect * from student where  to_char(birthday,'mm')>10;

 

--系统函数

sys_context系统函数   主要有七个参数

1）terminal 当前回话客户所对应的终端的标识符

2）lanuage  语言

3）db_name  当前数据库名称

4）nls_date_format  当前会话客户所对应的日期格式

5）session_user     当前会话客户所对应的数据库用户名

6）current_schema   当前回话客户所对应的默认方案名？

7）host         返回数据库所在的主机的名称；

当前适用的数据库：

select  sys_context ('USERENV','db_name')from dual ;

当前正在使用的语言：

select sys_context('USERENV','language') from dual ;

查询当前操作该数据库的用户名：

select sys_context('USERENV','session_user') from dual;

 

--数字函数：

数字函数的输入参数和返回值的数据类型都是数字类型的，

数字函数包括cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round

 

其中n表示：列明；

1)round(n,[m])

该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，如果m是整数，则四舍五入到小数点的m位后，如果m是负数，则四舍五入到小数点的m为位前

select (round(sal) ) from emp where emame='shunping';

select (round(sal)+round(comm))*13 from emp;

select (round(sal)+round(comm))*13 from emp where ename='shunping';

select round(comm,1),comm from emp where ename ='shunping';

 

2)trunc(n,[m])

该函数用于截取数字，如果省掉m，就截去小数部分，如果m是整数就截取到小数点的m位置，如果m是负数，则截取到小数点的前m位；

select trunc(comm,1),comm from emp where ename='shunping'

select trunc(comm),comm from emp where ename='shunping';

 

3)mod(m,n)

取模

select mod(10,2) from dual  --最终的结果为0,因为10对2 取模为0；

select mod(10,3) from dual  --最终的结果为0，因为10对3取模为1；

 

4)floor(n)

返回小于或是等于n的最大整数

select floor(comm),comm from emp where ename='shunping';

 

5)ceil(n)

返回大于或是等于n的最小整数；

select ceil(comm),comm from where ename='shunping';

 

对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果；

 

显示在一个月为30天的情况所有员工的日薪金，忽略余数；

方法一：select trunc(sal/30), ename from emp;  --进行截取

方法二：select floor(sal/30),ename from emp;   --向下取整

 

--其他的数学函数：

1）abs(n)   返回数字n的绝对值

select abs(-13) from dual;

2) acos(n)  返回数字的反余旋值

3）asin(n)  返回数字的反正旋值

4）atan(n)  返回数字的反正切

5）cos(n)  

6)exp(n)    返回e的n次幂

7）log(m,n) 返回对数值

 

8）power(m,n)   返回m的n次幂

--通用函数

适用与任何数据类型，同时也适用于空值

1）nvl()函数：将空值转化成一个已知的值,可以使用的数据类型有日期、字符、数字

select sal*13+nvl(comm,0)*13 "年工资"，ename,comm from emp;

nvl(comm,0)*13  表达式的含义是，如果comm值查出来是空值，那么就用0来替代，如果comm值查出来不是0，那么查出来是多少就是多少；

2）

NVL1（EXP1，EXP2）      当EXP1不为空 就选择EXP1的值 否则EXP2

NVL2(EXP1，EXP2，EXP3) 当EXP1不为空 选择EXP2  为空选择EXP3

NULLIF(EXP1，EXP2)      相等返回NULL,不等返回EXP1

 

2、分组函数

1）概念:

分组函数作用于一组数据，并对一组数据返回一个值

Group by子句:对数据分组

Having  子句:过滤分组结果集合

1） 组函数类型：

Min()、max()、

stddev()标准差、avg()、

Count(*)、sum()

 

select min(sal),max(sal),sum(sal),avg(sal),count(*) from student;

select min(sal),max(sal),sum(sal),avg(sal),sum(sal)/count(sal),count(*) from student;

 

--avg()

select avg(sal) from student;--分组函数在查询的时候会忽略空值;

--nvl()

select avg(nvl(sal,0)) from student;--nvl()函数可以使分组函数无法忽略空值

--分组函数中 有distinct关键字

select count(distinct sal) from student;