09_PROCEDURE_FUNCTION

• Intro(PROCEDURE) #

  类似于java中的方法，MySQL 5.0 版本开始支持存储过程。
  含义：一组预先编译好的sql语句的集合，理解成批处理语句。
  
  好处：
  1). 提高代码的重用性
  2). 简化操作
  3). 减少编译次数并且减少了和数据库服务器的连接次数，提高了效率。

  • 创建语法 #

    create procedure 存储过程名称(参数列表)
    begin
        存储过程题（一组合法的sql语句）
    end
    

    Note

    注意：
    1). 参数列表包含三部分（参数模式，参数名，参数类型），例如：IN stuname varchar(20)
    参数模式：
    in: 该参数可以作为输入，也就是该参数需要调用方传入值
    out: 该参数可以作为输出，也就是该参数可以作为返回值
    inout: 该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
    
    2). 如果存储过程体仅仅只有一句话，begin end可以省略,存储过程体中的每条sql语句的结尾要求必须加分号。存储过程的结尾可以使用 delimiter重新设置。语法：delimiter 结束标记
delimiter $

  • 调用语法 #

    call 存储过程(实参列表);

    • 1.空参列表 #

      case1：插入到admin表中五条记录
      使用下面代码创建存储过程，
      然后使用语句call myp1();进行调用

      delimiter $
      create procedure myp1()
      begin
          insert into admin(username, `password`) values('john1', '0000'), ('lily', '0000'), ('rose', '0000'), ('jack', '0000'), ('tom', '0000');
      end $
      

    • 2.(IN)模式列表 #

      case1：创建存储过程实现 根据女神名，查询对应的男神信息
      使用下面代码创建存储过程，
      然后使用语句call myp2('小昭');进行调用，控制台会展示返回值。

      create procedure myp2(in beautyName varchar(20))
      begin
          select bo.*
          from boys bo
          right join beauty b on bo.id = b .boyfriend_id
          where b.name = beautyName;
      end $
      

      case2：创建存储过程实现，用户是否登录成功
      使用下面代码创建存储过程，
      然后使用语句call myp3('张飞','8888');进行调用

      create procedure myp3(in username varchar(20), in password varchar(20))
      begin
          declare result varchar(20) default '';
          select count(1) into result
          from admin
          where admin.username = username and admin.password = password;
          select result;
      end $
      

      create procedure myp4(in username varchar(20), in password varchar(20))
      begin
          declare result int default 0;
          select count(1) into result
          from admin
          where admin.username = username and admin.password = password;
          select if(result > 0, '成功', '失败');
      end $
      

    • 3.(OUT)模式列表 #

      case1：根据女神名，返回对应的男神名
      使用下面代码创建存储过程，
      然后使用语句set @bName; call myp5('小昭', @bName);进行调用, set 定义可有可无。
      可以通过select @bName;进行查看返回值。

      create procedure myp5(in beautyName varchar(20), out boyName varchar(20))
      begin
          select bo.boyName into boyName
          from boys bo
          inner join beauty b on bo.id = b .boyfriend_id
          where b.name = beautyName;
      end $
      

      case2：根据女神名，返回对应的男神名和男神魅力值
      使用下面代码创建存储过程，
      然后使用语句call myp6('小昭', @bName, @userCP);进行调用。
      可以通过select @bName,@userCP;进行查看返回值。

      create procedure myp6(in beautyName varchar(20), out boyName varchar(20), out userCP int)
      begin
          select bo.boyName, bo.userCP into boyName, userCP
          from boys bo
          inner join beauty b on bo.id = b .boyfriend_id
          where b.name = beautyName;
      end $
      

    • 4.(INOUT)模式列表 #

      case1：传入a和b两个值，最终a和b都翻倍返回
      使用下面代码创建存储过程，
      然后使用语句set @m = 10; set @n = 20;call myp7(@m, @n);进行调用
      可以通过select @m,@n;进行查看返回值。

      create procedure myp7(inout a int, inout b int)
      begin
          set a = a * 2;
          set b = b * 2;
      end $
      

  • 删除语法 #

    Note

    语法：drop procedure 存储过程名; 一次只能删除一个。
    drop procedure myp3;

  • 查看语法 #

    Note

    语法
    desc myp2;
show create procedure myp2;

• Intro(FUNCTION) #

  类似于java中的方法，
  含义：一组预先编译好的sql语句的集合，理解成批处理语句。
  
  好处：
  1). 提高代码的重用性
  2). 简化操作
  3). 减少编译次数并且减少了和数据库服务器的连接次数，提高了效率。 以上与存储过程一致
  
  和存储过程的区别
  存储过程：可以有0个返回，也可以有多个返回，适合做批量插入，批量更新
  函数：有且仅有1一个返回，适合做处理数据后返回一个结果

  • 创建语法 #

    create function 函数名(参数列表) returns 返回类型
    begin
        函数体
    end
    

    Note

    注意：
    参数列表包含两部分：函数名，参数类型
    
    函数体：肯定会有return语句，如果没有会报错
    如果return语句没有放在函数体的最后也不保存，但不建议
    
    return值
    函数体中仅有一句话，则可以省略 begin end
    使用delimiter语句设置结束标记

  • 调用语法 #

    select 函数名(参数列表);

    • 1.空参有返回 #

      case1：返回公司的员工个数
      使用下面代码创建函数，
      然后使用语句select myf1();进行调用

      create function myf1() returns int
      begin
          declare c int default 0;
          select count(1) into c
          from employees;
          return c;
      end $
      

    • 2.有参有返回 #

      case1：根据员工名，返回他的工资
      使用下面代码创建函数，
      然后使用语句select myf2('Kochhar');进行调用

      create function myf2(empName varchar(20)) returns double
      begin
          set @sal = 0;
          select salary into @sal
          from employees
          where last_name = empName;
          return @sal;
      end $
      

      case2：根据部门名，返回该部门的平均工资
      使用下面代码创建函数，
      然后使用语句select myf3('IT');进行调用

      create function myf3(deptName varchar(20)) returns double
      begin
          declare sal double;
          select avg(salary) into sal
          from employees e
          join departments d on e.department_id = d.department_id
          where d.department_name = deptName;
          return sal;
      end $
      

  • 查看语法 #

    Note

    语法
    desc myf3;
show create function myf3;

  • 删除语法 #

    Note

    语法：drop function 函数名; 一次只能删除一个。
    drop function myf3;