存储过程和函数

变量

• 分类：

  • 系统变量：
    全局变量
    会话变量

  • 自定义变量：
    用户变量
    局部变量

• 系统变量

  • 说明：变量由系统定义，不是用户定义，属于服务器层面
    注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
  • 使用语法：
    1、查看所有系统变量
    show global|【session】variables;
    2、查看满足条件的部分系统变量
    show global|【session】 variables like ‘%char%’;
    3、查看指定的系统变量的值
    select @@global|【session】系统变量名;
    4、为某个系统变量赋值
    方式一：
    set global|【session】系统变量名=值;
    方式二：
    set @@global|【session】系统变量名=值;

  #1》全局变量
  /*
  作用域：针对于所有会话（连接）有效，但不能跨重启
  */
  #①查看所有全局变量
  SHOW GLOBAL VARIABLES;
  #②查看满足条件的部分系统变量
  SHOW GLOBAL VARIABLES LIKE '%char%';
  #③查看指定的系统变量的值
  SELECT @@global.autocommit;
  #④为某个系统变量赋值
  SET @@global.autocommit=0;
  SET GLOBAL autocommit=0;#2》会话变量
  /*
  作用域：针对于当前会话（连接）有效
  */
  #①查看所有会话变量
  SHOW SESSION VARIABLES;
  #②查看满足条件的部分会话变量
  SHOW SESSION VARIABLES LIKE '%char%';
  #③查看指定的会话变量的值
  SELECT @@autocommit;
  SELECT @@session.tx_isolation;
  #④为某个会话变量赋值
  SET @@session.tx_isolation='read-uncommitted';
  SET SESSION tx_isolation='read-committed';
  

• 自定义变量

  • 说明：变量由用户自定义，而不是系统提供的
  • 使用步骤：
    1、声明
    2、赋值
    3、使用（查看、比较、运算等）

  #1》用户变量
  /*
  作用域：针对于当前会话（连接）有效，作用域同于会话变量
  应用在任意地方
  */
  #赋值操作符：=或:=
  #①声明并初始化
  SET @变量名=值;
  SET @变量名:=值;
  SELECT @变量名:=值;
  #②赋值（更新变量的值）
  #方式一：SET @变量名=值;SET @变量名:=值;SELECT @变量名:=值;
  #方式二：SELECT 字段 INTO @变量名FROM 表;
  #③使用（查看变量的值）
  SELECT @变量名;#案例：
  #声明并初始化
  SET @name='john';
  SET @name=100;
  SET count=1;
  #赋值
  SELECT count(*) INTO @count
  FROM employees;
  #查看
  SELECT @count;#2》局部变量
  /*
  作用域：仅仅在定义它的begin end块中有效
  应用在 begin end中的第一句话
  */
  #①声明
  DECLARE 变量名 类型;
  DECLARE 变量名 类型 【DEFAULT 值】;
  #②赋值（更新变量的值）
  #方式一：SET 局部变量名=值;SET 局部变量名:=值;SELECT @局部变量名:=值;
  #方式二：SELECT 字段 INTO 具备变量名FROM 表;
  #③使用（查看变量的值）
  SELECT 局部变量名;#案例：声明两个变量，求和并打印
  #用户变量
  SET @m=1;
  SET @n=1;
  SET @sum=@m+@n;
  SELECT @sum;
  #局部变量（没放在begin end中，无法执行）
  DECLARE m INT DEFAULT 1;
  DECLARE n INT DEFAULT 1;
  DECLARE SUM INT;
  SET SUM=m+n;
  SELECT SUM;
  

  • 用户变量和局部变量的对比

    ​ 作用域 定义位置 语法

    用户变量 当前会话 会话的任何地方 加@符号，不用指定类型
    局部变量 BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

• 存储过程和函数：类似于java中的方法
  好处：
  1、提高代码的重用性
  2、简化操作

存储过程

• 含义：一组预先编译好的SQL语句的集合，理解成批处理语句
  1、提高代码的重用性
  2、简化操作
  3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

• 一、创建语法

  • CREATE PROCEDURE 存储过程名(参数列表)
    BEGIN

  ​ 存储过程体（一组合法的SQL语句）

  ​ END

  • 注意：
    参数列表包含三部分
    参数模式 参数名 参数类型
    举例：
    in stuname varchar(20)

  • 参数模式：
    in：该参数可以作为输入，也就是该参数需要调用方传入值
    out：该参数可以作为输出，也就是该参数可以作为返回值
    inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

  • 如果存储过程体仅仅只有一句话，begin end可以省略

  • 存储过程体中的每条sql语句的结尾要求必须加分号。
    存储过程的结尾可以使用 delimiter 重新设置
    语法：
    delimiter 结束标记
    案例：
    delimiter $

• 二、调用语法

​ CALL 存储过程名(实参列表);

#1.空参列表
#案例：插入到admin表中五条记录
SELECT * FROM admin;DELIMITER $
CREATE PROCEDURE myp1()
BEGININSERT INTO admin(username,`password`) VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGINSELECT bo.*FROM boys boRIGHT JOIN beauty b ON bo.id = b.boyfriend_idWHERE b.name=beautyName;
END $
#调用
CALL myp2('柳岩')$#案例2 ：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGINDECLARE result INT DEFAULT 0;#声明变量并初始化SELECT COUNT(*) INTO result#赋值FROM adminWHERE admin.username = usernameAND admin.password = PASSWORD;SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp4('张飞','8888')$#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGINSELECT bo.boyname INTO boynameFROM boys boRIGHT JOINbeauty b ON b.boyfriend_id = bo.idWHERE b.name=beautyName ;
END $
#调用
SET @bName$#可省略
CALL myp6('小昭',@bName)$
SELECT @bName#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGINSELECT boys.boyname,boys.usercp INTO boyname,usercpFROM boys RIGHT JOINbeauty b ON b.boyfriend_id = boys.idWHERE b.name=beautyName ;
END $
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGINSET a=a*2;SET b=b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$#三、删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#不支持#四、查看存储过程的信息
DESC myp2;#×
SHOW CREATE PROCEDURE myp2;

函数

• 含义：一组预先编译好的SQL语句的集合，理解成批处理语句
  1、提高代码的重用性
  2、简化操作
  3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

• 区别：

  存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
  函数：有且仅有1 个返回，适合做处理数据后返回一个结果

• 一、创建语法

  • 注意：
    1.参数列表 包含两部分：
    参数名 参数类型

    2.函数体：肯定会有return语句，如果没有会报错
    如果return语句没有放在函数体的最后也不报错，但不建议

    return 值;
    3.函数体中仅有一句话，则可以省略begin end
    4.使用 delimiter语句设置结束标记

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN函数体
END

• 二、调用语法

SELECT 函数名(参数列表)

#------------------------------案例演示----------------------------
#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGINDECLARE c INT DEFAULT 0;#定义局部变量SELECT COUNT(*) INTO c#赋值FROM employees;RETURN c;END $SELECT myf1()$#2.有参有返回
#案例1：根据员工名，返回它的工资CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGINSET @sal=0;#定义用户变量 SELECT salary INTO @sal #赋值FROM employeesWHERE last_name = empName;RETURN @sal;
END $SELECT myf2('kochhar') $#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGINDECLARE sal DOUBLE ;SELECT AVG(salary) INTO salFROM employees eJOIN departments d ON e.department_id = d.department_idWHERE d.department_name=deptName;RETURN sal;
END $SELECT myf3('IT')$

• 查看函数

SHOW CREATE FUNCTION myf3;

• 删除函数

DROP FUNCTION myf3;

#案例
#一、创建函数，实现传入两个float，返回二者之和CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGINDECLARE SUM FLOAT DEFAULT 0;SET SUM=num1+num2;RETURN SUM;
END $SELECT test_fun1(1,2)$