14.数据库对象之----函数（function）

1. 什么是sql函数：sql函数是一个命令集

2. sql函数的分类：

 

   按作用范围分类 ：单行函数和多行函数

                               单行函数：作用对象是单条数据

                               多行函数：作用对象是多条数据，又称为组函数

    单行函数又分为：字符函数，日期函数，转换函数，通用函数

3.字符函数

              lower（）：  转换为大小写                                            例子：  select lower(author)from books

              upper（）                                                                      例子： select upper(author)from books

      

               lengthc（）   查询的是字符                                          例子： select lengthc('ihao还是') from dual                结果为6

               length（）     其实就是lengthc（）

               lengthb（）   查询的是字节，一个汉字两个字节  例子： select lengthb('ihao还是') from dual                        结果为8

               initcap（）   首字母大写                                               例子：select initcap('hello word') from dual

     concat（' ',' '）   连接                                                   例子：  select concat('A','B') from dual

             等同于 ‘a’||‘b’                          例子： select 'A'||'B' from dual

     substr（'abcdef',2） 截取字符串                                  例子： select substr('abcdefg',3)from dual;

            从1开始，截取指定位置及之后的，第二个参数指的是从哪里开始截

     substr（'abcdef',2，1）                                                例子： select substr('abcdefg',3,3)from dual;

          第三个参数指得是截取长度，单位是字符

     replace（‘字符串’，‘旧字符串’，‘新字符串’） 替换                 例子：  select replace('abcdEFG','abcd','ABCD') from dual;

     instr（‘字符串’，‘待查找字符串’）   查找字符串的位置                 例子： select instr('abcdacbdabcdabcd','ab')from dual;

     instr（‘字符串’，‘待查找字符串’，‘从哪里开始查’）                例子：select instr('abcdacbdabcdabcd','ab',3)from dual;

          计算长度还是从初始位置开始    

     instr（‘字符串’，‘待查找字符串’，‘从哪里开始查’，‘第几次出现’）       例子：select instr('abcdacbdabcdabcd','ab',1,2)from dual;

     lpad（‘字符串’，长度，‘填充字符’）                                          例子：select lpad('lq',6,'5') from dual;

             改变字符串长度，用指定符号填充，从左填充                        

      rpad（‘字符串’，长度，‘填充字符’）       从右填充                    例子：select rpad('lq',6,'5') from dual;

4. 数字函数

     mod（1,3） ： 取余，可以理解为%

     round（1.945,2） 四舍五入               例子：select round(1945.505,-2) from dual;

                第一个参数是一个数

                第二个参数指定小数保留几位

                如果要四舍五入整数，这个参数应该为负数，负几就是四舍五入第几位

      trunc（1.945，2） 截断               例子：select trunc(1945.505,-2)from dual;

                第二个参数指定小数保留几位

                如果要截取整数，这个参数应该为负数

                负几就是四舍五入第几位

                截取的整数部分用0填充

5. 日期函数

                sysdate   系统当前日期           例子：  select sysdate from dual

                add_months（date，n）    指定日期加n    n代表的是月份            例子：   select add_months(sysdate,3) from dual

                                                          

                next_day(date，c1)     返回date之后的下一个c1（‘星期几’）

 例子：  next_day(sysdate,'星期一')

 返回下一个礼拜一的日期   select next_day(sysdate,'星期一')from dual  

      last_day(date)              返回指定日期月份的最后一天                     例子：select last_day(sysdate) from dual

                months_between（date1,date2）         返回两个日期之间隔着的月份数（可能是小数）

                                                                  例子： select months_between(sysdate,to_date('2016-01-01','yyyy-mm-dd')) from dual;

                                                                   date1>date2 返回正数

                           date1<date2 返回负数

                round（date【c1】）返回离date最近的c1（day,month,year,q）

                                                                   例子：     round（sysdate）   返回最近0点日期                           select round(sysdate) from dual;

  round（sysdate，'day'）   返回最近星期日                 select round(sysdate,'day') from dual;

  round（sysdate，'month'）    返回最近月初              select round(sysdate,'month') from dual;

  round（sysdate，'q'）  返回最近季初日期                  select round(sysdate,'q') from dual;

  round（sysdate，'year'） 返回最近年初日期               select round(sysdate,'year') from dual;

           

                trunc（date【c1】）返回date期间的c1（day,month,year,q）

   例子：  trunc（sysdate）  返回今天日期                                      select trunc(sysdate) from dual;

 trunc（sysdate，'day'） 返回本周星期日                        select trunc(sysdate,'day') from dual;

 trunc（sysdate，'month'） 返回本月月初                      select trunc(sysdate,'month') from dual;

 trunc（sysdate，'q'）返回本季日期                                 select trunc(sysdate,'q') from dual;

 trunc（sysdate，'year'）返回本年年初日期                     select trunc(sysdate,'year') from dual;

                

                extract（c1 from timestamp‘2017-01-01 01:01:01’）

                select extract(month from timestamp '2016-02-06 02:06:05') from dual;

                c1指的是：hour，minute ，second ，day ，month，year

6. 转换函数

                to_char()

                to_char(date,['fmt'])

                fmt:格式

                                          cc：世纪

            yyyy： 年

            yyy yy y  ： 年的最后3,2,1个数字

            y,yyy： 年，在指定位置加逗号                    select to_char(sysdate,'y,yyy')from dual;

            year：英文拼写的年                                    select to_char(sysdate,'year')from dual;

            mm：月，两位数字值                                 select to_char(sysdate,'mm')from dual;

            ww/w： 本年/本月的第几周                        select to_char(sysdate,'ww')from dual; select to_char(sysdate,'w')from dual;

            month： 月份的全拼，会用空格补齐（右端）

            mon：月份的前三个字母

            day：周几的全拼，会用空格补齐（右端）

            dy： 周几的前三个字母缩写

             DDD/DD/D： 本年/本月/本周的第几天

             am/pm：上午下午

             hh/hh12： 12小时制

             hh24：24小时制

              mi：分钟

              ss： 秒

              ssss：从午夜十二点经过的秒数

             yyyy-mm-dd day hh:mi:ss： 年月日 星期几 时分秒

                to_char(number,['fmt']) fmt一般不用写， 将number转换为date

                9：（数字宽度）9的个数决定显示的宽度

                0： 显示前导0

                $：前面显示美元符

                                              

                                    

            *********************************************************

                fm  1.去除空格

                      2.去除前导0

                sp  数值转换为英文

                th 序数

                fx   在to_date 中使用，限制to_date第一个参数必须和后面的格式完全相同，（精确匹配）

                to_date(charDate,['fmt'])

                默认格式 dd-mon-rr

                日月年

                将字符串转换为日期格式

                to_number(char)

                将char转换为number

           ************************************************************

 

 

7.通用函数（支持所有数据类型，但需要一致）

                nvl（‘’，‘’）null value的简写

                第一个参数为空，返回第二个参数

                第一个参数不为空，返回第一个参数

                nvl2('','','')

                第一个参数不为空，返回第二个参数

                第一个参数为空，返回第三个参数

                第二个参数和第三个参数数据类型可以不一致，第三个参数类型会转变为第二个参数数据类型

                coalesce()合并

                可以有无数个参数

                参数类型需要一致

                取第一个不为null的值

                如果都为null，返回null

           

8.多行函数：作用对象是多条数据， 又称为组函数

           特点：

                不考虑是否重复

                会忽略等于null的元素

       

           常见的组函数:

                avg（） 先求和再平均                        select avg(age) from student;

                sum（）  求和                                    select sum(age)/count(age) from student;

                                                                           select sum(age) from student;

                stddev（） 求标准差

                varlance（） 求方差

                 count（）  计数                                   select count(age) from student;

                                 注意：

                去重复使用distinct，在括号内使用，此时不忽略空值

                比较特殊，参数可以为* ，这时会把存在null的数据计算进去

               

                max（）   求最大                                  select max(age) from student;

                min（）     求最小                                 select min(age) from student;

               

          group by:

                作用 : 将表中的数据分为若干组,为了使用组函数

                注意：

                如果是select语句，group by，必须在where 语句之后，order by 语句之前

                如果是select语句，使用了group by 则select之后的字段必须是组函数，或者分组字段

                如果group by 之后又order by ，order by的内容必须是组函数或者分组字段

                group by之后跟多个字段,先按第一个字段分组，再对第二个进行分组

         having

                作用： 对分组后的数据进行操作

                注意：

                有having必须有group by 语句

                having在group by 之后 ，order by 之前

                having 之后不能跟别名

                having之后必须是分组函数或者分组字段

                和where的区别

                          where不能用组函数

             

9.自定义函数

                语法:

                     create 【or replace 】function function_name

                    【（参数名 【mode】 参数类型） .... 】

                     return return_type

                     is|as 【 变量名 变量类型 【：=赋值】 ... 】；

                     begin

                         function_body ；

                     end [function_name] ;

               注意：

                1.参数数据类型和返回值类型不能指定长度

                          2.mode

                in  默认为in

                作用：参数是往里面传数据

                out： 参数往外输出

                in out：可以接收数据，也可以输出数据

                         3.  定义变量的时候可以指定长度，也可以不指定

                         4. 没有参数的时候，可以直接使用函数名也可以在函数名后加一个括号