MySQL语言基础 - MySQL存储过程语法 - 《Java之从零开始》

定义变量
变量的赋值1
变量的赋值2
if语句的使用
参数的传递
输入参数
输出参数
case语句的使用
while循环
repeat循环
loop循环
游标

定义变量

-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内
-- 定义一个int类型变量、并赋默认值为10
DELIMITER $
CREATE PROCEDURE pro_test1()
BEGIN
    DECLARE num INT DEFAULT 10;   -- 定义变量
    SELECT num;                   -- 查询变量
END$
DELIMITER ;
-- 调用pro_test1存储过程
CALL pro_test1();
+-----+
| num |
+-----+
|  10 |
+-----+
1 row in set

变量的赋值1

-- 标准语法
SET 变量名 = 变量值;
-- 定义字符串类型变量，并赋值
DELIMITER $
CREATE PROCEDURE pro_test2()
BEGIN
    DECLARE NAME VARCHAR(10);   -- 定义变量
    SET NAME = '存储过程';       -- 为变量赋值
    SELECT NAME;                -- 查询变量
END$
DELIMITER ;
-- 调用pro_test2存储过程
CALL pro_test2();
+----------+
| NAME     |
+----------+
| 存储过程 |
+----------+
1 row in set

变量的赋值2

-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
-- 定义两个int变量，用于存储男女同学的总分数
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE men,women INT;  -- 定义变量
    SELECT SUM(score) INTO men FROM student WHERE gender='男';    -- 计算男同学总分数赋值给men
    SELECT SUM(score) INTO women FROM student WHERE gender='女';  -- 计算女同学总分数赋值给women
    SELECT men,women;           -- 查询变量
END$
DELIMITER ;
-- 调用pro_test3存储过程
CALL pro_test3();

if语句的使用

-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
/*
    定义一个int变量，用于存储班级总成绩
    定义一个varchar变量，用于存储分数描述
    根据总成绩判断：
        380分及以上    学习优秀
        320 ~ 380     学习不错
        320以下       学习一般
*/
DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
    -- 定义总分数变量
    DECLARE total INT;
    -- 定义分数描述变量
    DECLARE description VARCHAR(10);
    -- 为总分数变量赋值
    SELECT SUM(score) INTO total FROM student;
    -- 判断总分数
    IF total >= 380 THEN 
        SET description = '学习优秀';
    ELSEIF total >= 320 AND total < 380 THEN 
        SET description = '学习不错';
    ELSE 
        SET description = '学习一般';
    END IF;
    -- 查询总成绩和描述信息
    SELECT total,description;
END$
DELIMITER ;
-- 调用pro_test4存储过程
CALL pro_test4();

参数的传递

DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
    执行的sql语句;
END$
/*
    IN:代表输入参数，需要由调用者传递实际数据。默认的
    OUT:代表输出参数，该参数可以作为返回值
    INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
DELIMITER ;

输入参数

DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称(IN 参数名 数据类型)
BEGIN
    执行的sql语句;
END$
DELIMITER ;
DELIMITER $
CREATE PROCEDURE pro_test5(IN total INT)
BEGIN
    -- 定义分数描述变量
    DECLARE description VARCHAR(10);
    -- 判断总分数
    IF total >= 380 THEN 
        SET description = '学习优秀';
    ELSEIF total >= 320 AND total < 380 THEN 
        SET description = '学习不错';
    ELSE 
        SET description = '学习一般';
    END IF;
    -- 查询总成绩和描述信息
    SELECT total,description;
END$
DELIMITER ;
-- 调用pro_test5存储过程
-- 直接输入变量，用这个total参数进行后续的判断
CALL pro_test5(390);
-- 通过查询值进行判断
CALL pro_test5((SELECT SUM(score) FROM student));
Query OK, 0 rows affected
+-------+-------------+
| total | description |
+-------+-------------+
|   390 | 学习优秀    |
+-------+-------------+
1 row in set
Query OK, 0 rows affected
+-------+-------------+
| total | description |
+-------+-------------+
|   383 | 学习优秀    |
+-------+-------------+
1 row in set

输出参数

DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型)
BEGIN
    执行的sql语句;
END$
DELIMITER ;
/*
    输入总成绩变量，代表学生总成绩
    输出分数描述变量，代表学生总成绩的描述
    根据总成绩判断：
        380分及以上  学习优秀
        320 ~ 380    学习不错
        320以下      学习一般
*/
DELIMITER $
CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10))
BEGIN
    -- 判断总分数
    IF total >= 380 THEN 
        SET description = '学习优秀';
    ELSEIF total >= 320 AND total < 380 THEN 
        SET description = '学习不错';
    ELSE 
        SET description = '学习一般';
    END IF;
END$
DELIMITER ;
-- 调用pro_test6存储过程
CALL pro_test6(310,@description);
-- 查询总成绩描述
SELECT @description;
+--------------+
| @description |
+--------------+
| 学习一般     |
+--------------+
1 row in set

case语句的使用

-- 标准语法
CASE 表达式
WHEN 值1 THEN 执行sql语句1;
[WHEN 值2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;
-- 标准语法
CASE
WHEN 判断条件1 THEN 执行sql语句1;
[WHEN 判断条件2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;
/*
    输入总成绩变量，代表学生总成绩
    定义一个varchar变量，用于存储分数描述
    根据总成绩判断：
        380分及以上  学习优秀
        320 ~ 380    学习不错
        320以下      学习一般
*/
DELIMITER $
CREATE PROCEDURE pro_test7(IN total INT)
BEGIN
    -- 定义变量
    DECLARE description VARCHAR(10);
    -- 使用case判断
    CASE
    WHEN total >= 380 THEN
        SET description = '学习优秀';
    WHEN total >= 320 AND total < 380 THEN
        SET description = '学习不错';
    ELSE 
        SET description = '学习一般';
    END CASE;
    -- 查询分数描述信息
    SELECT description;
END$
DELIMITER ;
-- 调用pro_test7存储过程
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));

while循环

-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
    循环体语句;
    条件控制语句;
END WHILE;
/*
    计算1~100之间的偶数和
*/
DELIMITER $
CREATE PROCEDURE pro_test8()
BEGIN
    -- 定义求和变量
    DECLARE result INT DEFAULT 0;
    -- 定义初始化变量
    DECLARE num INT DEFAULT 1;
    -- while循环
    WHILE num <= 100 DO
        -- 偶数判断
        IF num%2=0 THEN
            SET result = result + num; -- 累加
        END IF;
        -- 让num+1
        SET num = num + 1;         
    END WHILE;
    -- 查询求和结果
    SELECT result;
END$
DELIMITER ;
-- 调用pro_test8存储过程
CALL pro_test8();

repeat循环

-- 标准语法
初始化语句;
[循环名称:] LOOP
    条件判断语句
        [LEAVE 循环名称;]
    循环体语句;
    条件控制语句;
END LOOP 循环名称;
-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
--      如果不加退出循环的语句，那么就变成了死循环。
/*
    计算1~10之间的和
*/
DELIMITER $
CREATE PROCEDURE pro_test10()
BEGIN
    -- 定义求和变量
    DECLARE result INT DEFAULT 0;
    -- 定义初始化变量
    DECLARE num INT DEFAULT 1;
    -- loop循环
    l:LOOP
        -- 条件成立，停止循环
        IF num > 10 THEN
            LEAVE l;
        END IF;
        -- 累加
        SET result = result + num;
        -- 让num+1
        SET num = num + 1;
    END LOOP l;
    -- 查询求和结果
    SELECT result;
END$
DELIMITER ;
-- 调用pro_test10存储过程
CALL pro_test10();

loop循环

-- 标准语法
初始化语句;
[循环名称:] LOOP
    条件判断语句
        [LEAVE 循环名称;]
    循环体语句;
    条件控制语句;
END LOOP 循环名称;
-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
--      如果不加退出循环的语句，那么就变成了死循环。
/*
    计算1~10之间的和
*/
DELIMITER $
CREATE PROCEDURE pro_test10()
BEGIN
    -- 定义求和变量
    DECLARE result INT DEFAULT 0;
    -- 定义初始化变量
    DECLARE num INT DEFAULT 1;
    -- loop循环
    l:LOOP
        -- 条件成立，停止循环
        IF num > 10 THEN
            LEAVE l;
        END IF;
        -- 累加
        SET result = result + num;
        -- 让num+1
        SET num = num + 1;
    END LOOP l;
    -- 查询求和结果
    SELECT result;
END$
DELIMITER ;
-- 调用pro_test10存储过程
CALL pro_test10();

游标

游标的概念

游标可以遍历返回的多行结果，每次拿到一整行数据
在存储过程和函数中可以使用游标对结果集进行循环的处理
简单来说游标就类似于集合的迭代器遍历
MySQL中的游标只能用在存储过程和函数中
```sql — 创建游标 DECLARE 游标名称 CURSOR FOR 查询sql语句;

— 打开游标 OPEN 游标名称;

— 使用游标获取数据 FETCH 游标名称 INTO 变量名1,变量名2,…;

— 关闭游标 CLOSE 游标名称;

/ 当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作加标记思想： 1.定义一个变量，默认值为0(意味着有数据) 2.当游标结束后，将变量值改为1(意味着没有数据了) / — 1.定义一个变量，默认值为0(意味着有数据) DECLARE flag INT DEFAULT 0; — 2.当游标结束后，将变量值改为1(意味着没有数据了) DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

/ 将student表中所有的成绩保存到stu_score表中 / DELIMITER $

CREATE PROCEDURE pro_test12() BEGIN — 定义成绩变量 DECLARE s_score INT; — 定义标记变量 DECLARE flag INT DEFAULT 0; — 创建游标，查询所有学生成绩数据 DECLARE stu_result CURSOR FOR SELECT score FROM student; — 游标结束后，将标记变量改为1 DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

-- 开启游标
OPEN stu_result;
-- 循环使用游标
REPEAT
    -- 使用游标，遍历结果,拿到数据
    FETCH stu_result INTO s_score;
    -- 将数据保存到stu_score表中
    INSERT INTO stu_score VALUES (NULL,s_score);
UNTIL flag=1
END REPEAT;
-- 关闭游标
CLOSE stu_result;

END$

DELIMITER ;

— 调用pro_test12存储过程 CALL pro_test12();

— 查询stu_score表 SELECT * FROM stu_score; ```