数据库-MySQL的存储过程（课堂笔记）_mysql 存储过程处理复杂的逻辑-CSDN博客

本文链接：https://blog.csdn.net/qq_34715071/article/details/130319210

MySQL的存储过程

定义

存储过程就是一组SQL语句集，能够运行比较复杂的逻辑功能，对代码的封装与重用。（类似java的方法）

特性

有输入输出函数
能够声明变量
有if/else，case，while等控制语句
实现复杂的逻辑功能
速度快

代码

格式

DELIMITER 自定义结束符号
CREATE PROCEDURE 存储名()
BEGIN
    存储过程体
END 自定义结束符号
DELIMITER ;
CALL 存储名();

举例

DELIMITER $$
CREATE PROCEDURE proc1()
BEGIN
    SELECT * FROM employees;
END $$
DELIMITER ;
CALL proc1();

变量定义

局部变量

语法：DECLARE var_name type [default var_values]; -- 声明变量

举例：DECLARE nickname VARCHAR(32);

DELIMITER $$
CREATE PROCEDURE proc02()
BEGIN
    DECLARE var_name01 VARCHAR(20) DEFAULT 'aaa';
    SET var_name01 = 'zhangsan';
    SELECT var_name01;
END $$
DELIMITER ;
CALL proc02();

使用 SELECT ... INTO ... 语句为变量赋值

DELIMTER $$
CREATE PROCEDURE proc03()
BEGIN
    DECLARE my_ename VARCHER(20);
    SELECT ename INTO my_ename FROM employees WHERE empno = 1001;
    SELECT ename;
END $$
DELIMITER ;
CALL proc03();

用户变量

语法：@var_name

不需要提前声明，使用即声明

DELIMITER $$
CREATE PROCEDURE proc04()
BEGIN
    SET @var_name01 = 'ZS';
END $$
DELIMITER ;
CALL proc04();
SELECT @var_name01;

系统变量

系统变量可分为全局变量和会话变量。

全局变量在服务器启动时会将变量初始化为默认值，默认值可由my.ini更改。

会话变量在服务器启动时会复制全局变量作为当前连接的变量。

区别：会话变量的更改只会影响当前连接的值，全局变量的更改会影响服务器的值。

对于可以更改的系统变量，可以利用SET语句进行更改。

系统变量-全局变量

语法：@@GLOBAL.var_name

SHOW GLOBAL VARIABLES; -- 查看全局变量
SELECT @@GLOBAL.AUTO_INCREMENT_INCREMENT; -- 查看某全局变量
SET @@GLOBAL.SORT_BUFFER_SIZE = 40000; -- 修改全局变量的值 或
SET GLOBAL SORT_BUFFER_SIZE = 40000;

系统变量-会话变量

语法：@@SESSION.var_name

SHOW SESSION VARIABLES; -- 查看会话变量
SELECT @@SESSION.AUTO_INCREMENT_INCREMENT; -- 查看某会话变量
SET @@SESSION.SORT_BUFFER_SIZE = 50000; -- 修改会话变量的值 或
SET SESSION SORT_BUFFER_SIZE = 50000;

传参类型（in、out、inout）

IN表示传入参数（数值或者变量），传入后不会更改变量的值，作用在函数范围内。
OUT表示从存储过程内部传给调用对象。
INOUT表示外部传入参数内部修改后可返回的变量，即可传入变量的值也可修改。（即使执行完函数）

IN类型

DELIMITER $$
CREATE PROCEDURE dec_param0x(IN dname VARCHAR(50), IN sal DECIMAL(7, 2))
BEGIN
    SELECT * FROM dept a, emp b WHERE b.sal > sal AND a.dname = dname;
END $$
DELIMITER $$
CALL dec_param0x('学工部',20000);

OUT类型

DELIMITER $$
CREATE PROCEDURE proc08(IN empno INT, OUT out_ename VARCHAR(50))
BEGIN
    SELECT ename INTO out_ename FROM emp WHERE emp.empno = empno;
END $$
DELIMITER ;
CALL proc08(1001, @o_ename);
SELECT @o_ename;

INOUT类型

DELIMITER $$
CREATE PROCEDURE proc10(INOUT inout_ename VARCHAR(50), INOUT inout_sal INT)
BEGIN
    SELECT CONCAT(deptno, "_", inout_ename) INTO inout_ename FROM emp WHERE ename = inout_ename;
    SET inout_sal = inout_sal * 12;
END $$
DELIMITER ;
SET @inout_ename = '关羽';
SET @inout_sal = 3000;
CALL proc10(@inout_ename, @inout_sal);
SELECT @inout_ename;
SELECT @inout_sal;

流程控制-判断

判断-IF语句

IF语句根据TRUE与FALSE执行多个条件判断语句。与编程语言中的if、else if、else语法类似。

语法：IF search_condition_1 THEN statement_list_1

[ELSEIF search_condition_2 THEN statement_list_2] ...

[ELSE statement_list_n]

ENDIF

示例

DELIMITER $$
CREATE PROCEDURE proc_12_if(IN score INT)
BEGIN
    IF score < 90 THEN SELECT "不及格";
        ELSEIF score < 80 THEN SELECT "及格";
        ELSEIF score < 90 THEN SELECT "良好";
        ELSEIF score <= 100 THEN SELECT "优秀";
        ELSE SELECT "成绩错误";
    END IF;
END $$
DELIMITER ;
CALL proc_12_if(120)

判断-CASE语句

CASE语句类似编程语言switch语句

语法一：

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE

示例一

DELIMITER $$
CREATE PROCEDURE proc14_case(IN pay_type INT)
BEGIN
    CASE pay_type 
        WHEN 1 THEN SELECT "微信支付";
        WHEN 2 THEN SELECT "支付宝支付";
        WHEN 3 THEN SELECT "银行卡支付";
        ELSE SELECT "其他方式支付";
    END CASE;
END $$
DELIMITER ;
CALL proc14_case(2);
CALL proc14_case(4);

语法二：

CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list] ...

[ELSE statement_list]

END CASE

示例二

DELIMITER $$
CREATE PROCEDURE proc_15_case(IN score INT)
BEGIN
    CASE
        WHEN score < 60 THEN SELECT "不及格";
        WHEN score < 80 THEN SELECT "及格";
        WHEN score < 90 THEN SELECT "良好";
        WHEN score <= 100 THEN SELECT "优秀";
        ELSE SELECT "成绩错误";
    END CASE;
END $$
DELIMITER ;
CALL proc_15_case(88);

流程控制-循环

循环是在程序中运行多次直到特定的次数或者特定的条件成立时结束循环的代码。

循环分类

WHLIE
REPEAT
LOOP

循环控制

LEAVE（类似于 break，跳出，结束当前所在的循环）
ITERATE（类似于 continue，继续，结束本次循环，继续下一次循环）

循环-WHLIE语句

语法：

[标签：]WHILE 循环条件 do

循环体;

END WHLIE [标签];

示例一

DELIMIETER $$
CREATE PROCEDURE proc16_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    LABEL:WHILE i <= insertCount DO
        INSERT INTO user(uid, username, password)
        VALUES(i, CONCAT('user-', i), 123456);
        SET i = i + 1;
    END WHILE LABEL;
END $$
DELIMITER ;
CALL proc16_while(10);

示例二

DELIMITER $$
CREATE PROCEDURE proc16_while2(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    LABEL:WHILE i <= insertCount DO
        INSERT INTO user(uid, username, password)
        VALUES(i, CONCAT('user-', i), 123456);
        IF i = 5 THEN LEAVE LABEL;
        END IF;
        SET i = i + 1;
    END WHILE LABEL;
END $$
DELIMITER ;
CALL proc16_while2(10);

示例三

DELIMITER $$
CREATE PROCEDURE proc16_while3(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    LABEL:WHILE i <= insertCount DO
        SET i = i + 1;
        IF i = 5 THEN ITERATE LABEL;
        END IF;
        INSERT INTO user(uid, username, password)
        VALUES(i, CONCAT('user-', i), 123456);
    END WHILE LABEL;
END $$
DELIMITER ;
CALL proc16_while3(10);

等待补充...