MySQL存储过程和函数

变量

系统变量

变量由系统提供，不是用户定义的，属于服务器层面

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;

2. 查看满足条件的部分全局变量

SHOW GLOBAL VARIABLES LIKE '%char%'

3. 查看指定的某一个全局变量

SELECT @@global.全局变量名;

4. 更新全局变量

SET GLOBAL 全局变量名=值;
# 或者
SET @@global.全局变量名=值;

会话变量

作用域：服务器每次重启将为所有全局变量赋初始值，针对单次会话有效

1. 查看所有会话变量

SHOW SESSION VARIABLES;

2. 查看满足条件的部分会话变量

SHOW SESSION VARIABLES LIKE '%char%'
SHOW VARIABLES LIKE '%char%'

3. 查看指定的某一个会话变量

SELECT @@session.会话变量名;

4. 更新会话变量

SET SESSION 会话变量名=值;
# 或者
SET @@session.会话变量名=值;

自定义变量

用户变量

作用域与会话变量相同，只针对当前会话，可以被应用于BEGIN END里面，也可以被用于其他位置

1. 声明变量并初始化

SET @用户变量名=值
# 或者
SET @用户变量名 := 值
# 或者
SELECT @用户变量名 := 值

2. 赋值

SET @用户变量名=值
# 或者
SET @用户变量名 := 值
# 或者
SELECT @用户变量名 := 值

案例

SET @name='John'
SET @name=100

或者通过SELECT INTO

SELECT 字段 INTO 变量名 FROM TABLE WHERE...

3. 使用

SELECT @用户变量名；

案例

SET @count=1
SELECT COUNT(*) INTO @count FROM employee;
SELECT @count;

局部变量

局部变量仅仅在定义它的BEGIN END中有效

1. 声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

2. 赋值

SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;

或则

SELECT 字段 INTO 局部变量名 FROM 表名;

这个代码可以直接执行：

SET @a=1;
SET @b=2;
SET @sum=@a+@b;
SELECT @sum

这个代码无法直接执行，只有放在BEGIN END中才能执行：

DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE sum INT;
SET sum=m+n;
SELECT sum;

存储过程

一组预先编译好的SQL语句的集合，可以提高代码的重用性、简化操作、减少编译次数并且减少了和数据库服务器建立连接的次数，提高效率。

1. 创建存储过程的格式

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  存储过程体（一组SQL）;
END

其中参数列表包括三部分，分布式参数模式 参数名 参数类型，如：IN stu_name VARCHAR(255)

参数的模式分为：

• IN：输入参数

• OUT：输出参数

• INOUT：输入输出参数

2. 要求

如果存储过程只有一句话，可以省略BEGIN END，存储过程体中的每一句SQL都要求在结尾处加分号，在结尾可以使用DELIMETER重新设置

3. 调用方法

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

案例1（无参的存储过程）：

DELIMITER $
CREATE PROCEDURE my_p1()
BEGIN
  INSERT INTO student(stu_name, age)
  VALUES('John',12),('Jerry',11),('Tom', 11),('Rose', 10),('Jack',13);
END $
DELIMITER ;

CALL my_p1();

案例2（带一个或者多个IN参数的存储过程）：

DELIMITER $
CREATE PROCEDURE my_p2(IN name VARCHAR(255))
BEGIN
  SELECT bo.*
  FROM boys bo
  RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
  WHERE b.name=name;
END $

CREATE PROCEDURE my_p3(IN username VARCHAR(255), IN password VARCHAR(255))
BEGIN
  DECLARE result VARCHAR(20) DEFAULT '';
  SELECT COUNT(*) INTO result
  FROM user
  WHERE user.username=username AND user.password=password;
  SELECT result;
END $
DELIMITER ;;

CREATE PROCEDURE my_p3_other(IN username VARCHAR(255), IN password VARCHAR(255))
BEGIN
  DECLARE result VARCHAR(20) DEFAULT '';
  SELECT COUNT(*) INTO result
  FROM user
  WHERE user.username=username AND user.password=password;
  SELECT IF(result >0, '成功','失败');
END $

DELIMITER ;;

CALL my_p2('柳岩');
CALL my_p3('admin', 'passwd');
CALL my_p3_other('admin','password');

案例3（带OUT模式的存储过程）：

DELIMITER $
CREATE PROCEDURE my_p5(IN b_name VARCHAR(255), OUT boy_name VARCHAR(255))
BEGIN
  SELECT bo.boyName INTO boy_name
  FROM boys bo
  INNER JOIN beauty b ON bo.id=b.boyfriend_id
  WHERE b.name=b_name;
END $

CREATE PROCEDURE my_p6(IN b_name VARCHAR(255), OUT boy_name VARCHAR(255), OUT user_cp INT)
BEGIN
  SELECT bo.boyName,bo.userCP INTO boy_name,user_cp
  FROM boys bo
  INNER JOIN beauty b ON bo.id=b.boyfriend_id
  WHERE b.name=b_name;
END $

DELIMITER ;;

# 调用
SET @b_name;
SET @user_cp;
CALL my_p5('小昭',@b_name);
CALL my_p6('小昭',@b_name, @user_cp);

案例4（带INOUT模式的存储过程）：

DELIMITER $
CREATE PROCEDURE my_p8(INOUT a INT, INOUT b INT)
BEGIN
  SET a=a*2;
  SET b=b*2;
END $
DELIMITER ;;

# 调用
SET @m=10;
SET @n=20;
CALL my_p8(@n,@m);
SELECT @m,@n;

练习案例

DELIMITER $
CREATE PROCEDURE test1(IN username VARCHAR(255), IN login_pwd VARCHAR(255))
BEGIN
  INSERT INTO user(user.username, user.password)
  VALUES(username, login_pwd);
END $

CREATE PROCEDURE test2(IN id INT, OUT name VARCHAR(255), OUT phone CHAR(11))
BEGIN
  SELECT b.name,b.phone INTO name,phone
  FROM beauty b
  WHERE b.id=id;
END $

# 比较生日大小
CREATE PROCEDURE test3(IN birthday1 DATETIME, IN birthday2 DATETIME, OUT result INT)
BEGIN
  SELECT DATEIFF(birthday1, birthday2) INTO result;
END $

# 格式化日期
CREATE PROCEDURE test4(IN mydate DATETIME, OUT strDate VARCHAR(50))
BEGIN
  SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;
END $


CREATE PROCEDURE test5(IN beautyName VARCHAR(255), OUT str VARCHAR(255))
BEGIN
  SELECT CONCAT(beautyName, ' and ', IFNULL(boyName, 'NULL')) INTO str
  FROM boys bo
  RIGHT JOIN beauty b ON b.boyfriend_id=bo.id
  WHERE b.name=beautyName;
END $

CREATE PROCEDURE test6(IN startIndex INT, IN size INT)
BEGIN
  SELECT * FROM beauty LIMIT startIndex,size;
END $

DELIMITER ;;

删除存储过程

DROP PROCEDURE 存储过程名;

查看存储过程

SHOW CREATE PROCEDURE 存储过程名;

函数

函数类似于存储过程，但是要求返回值有且仅有一个

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

流程控制结构

分支结构

1. if函数

IF(表达式1,表达式2,表达式3)

如果表达式1成立，则返回表达式2的值，否则返回表达式3的值

2. case结构

CASE 变量|表达式|字段
  WHEN 要判断的值1 THEN 返回值或者语句1;
  WHEN 要判断的值2 THEN 返回值或者语句2;
  WHEN 要判断的值3 THEN 返回值或者语句3;
  ELSE 要返回的值或者语句N;
END CASE;

或者

CASE
  WHEN 要判断的条件1 THEN 返回值或者语句1;
  WHEN 要判断的条件2 THEN 返回值或者语句2;
  WHEN 要判断的条件3 THEN 返回值或者语句3;
  ELSE 要返回的值或者语句N
END CASE;

CASE语句可以放在任何位置，如果作为独立的语句，只能放在BEGIN END中。

如果没有提供最后的ELSE语句，并且所有的WHEN都不满足，则会返回NULL。

案例

DELIMITER $
CREATE PROCEDURE test_case(IN score INT)
BEGIN
  CASE
    WHEN score >=90 AND score <= 100 THEN SELECT 'A';
    WHEN score >=80 THEN SELECT 'B';
    WHEN score >=60 THEN SELECT 'C';
    ELSE SELECT 'D';
  END CASE;
END $
DELEMITER ;

CALL test_case(95);

3. if结构

IF 条件1 THEN 语句1；
ESLEIF 条件2 THEN 语句2;
[ELSE 语句n;]
END IF;

案例

DELIMITER $
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
  IF score >= 90 AND score<=100 THEN RETURN 'A';
  ELSEIF score >=80 THEN RETURN 'B';
  ELSEIF score >=60 THEN RETURN 'C';
  ELSE RETURN 'D';
  END IF;
END $
DELIMITER ;

循环结构

1. WHILE

[标签] WHILE 循环条件 DO
  # 循环体
END WHILE [标签];

案例

DELIMITER $
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  a:WHILE i <= insertCount DO
    INSERT INTO user(username, password) VALUES(CONCAT('Rose',i), 'passwd');
    #  break 的意思
    IF i >= 20 THEN LEAVE a;
    END IF;
    #  continue 的意思
    IF MOD(i,2)!=0 THEN ITERATE a;
    END IF;
    SET i = i + 1;
  END WHILE a;
END $
DELIMITER ;

CALL pro_while(100);

2. LOOP

[标签] loop
  #  循环体;
END LOOP 标签;

3. repeat

[标签] repeat
  #  循环体
UNTIL 结束循环的条件
END REPEAT [标签]