mysql存储过程 遍历动态查询结果 mysql存储过程for

什么是存储过程：

　　是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

 

优点：

　　1、在生产环境下可以直接修改存储过程来修改业务逻辑（或者bug），不用重启服务器；

　　2、执行速度快，存储过程经过编译之后会比单条sql一条一条执行速度要快；

　　3、减少网络传输流量；

　　4、方便优化；　

缺点：

　　1、过程化编程，维护成本高；（比如复杂存储过程后续新人员进行维护会看不懂）

　　2、调试不便；

　　3、数据库移植性较差；（数据库直接语法可能不同）；

 

数据库中存储过程必须要进行创建，就像创建表一样，后续再执行；MYSQL中创建后的存储过程在 函数 列中可以看到，MYSQL后续更新存储过程需要先删除原先的存储过程，在创建新存储过程；

 

语法：

存储过程结束符：

　　正常sql语句会将分号 ；作为sql语句结束符，存储过程中也是如此，分号；会作为sql语句结束符，但存储过程结束符需要自定义，使用 delimiter关键字，后面接 自定义符号

　　关键字 delimiter

　　自定义符号 $$

delimiter $$ -- 自定义$$符号作为存储过程结束符号

存储过程基本结构：

CREATE  PROCEDURE  sp_name( [proc_parameter])
[characteristic...]  routine_body

CREATE  PROCEDURE：是用来创建存储过程的关键字，

sp_name：存储过程名称

proc_parameter：指定存储过程的参数列表，列表如下

[ IN | OUT | INOUT ]  param_name  type

其中，IN是输入参数，out是输出参数 ，INOUT是即可输入也可输出，param_name  是参数名称，type是参数类型。

 

characteristic：指定存储过程的特性，有以下取值。

1）LANGUAGE  SQL :说明routine_body部分是由SQ语句组成的，sql是LANGUAGE  特性的唯一值。

2）[ NOT ] DETERMINISTIC :指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的，当执行存储过程时，

相同的输入会得到相同的输出。NOT   DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的结果，如果没有指定任意一个值，

默认为NOT   DETERMINISTIC

3）{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: 指明子程序是SQL语句的限制。

CONTAINS SQL： 是表明子程序包含SQL语句，但是不包含读或是写数据的语句

NO SQL：表明子程序不包含SQL语句

 READS SQL DATA： 表明子程序包含读数据的语句

MODIFIES SQL DATA：表明子程序包含写数据的语句

默认情况下，系统会指定为CONTAINS SQL

4）SQL SECURITY { DEFINER | INVOKER } ; 指明谁有权限来执行，

DEFINER  表明只有定义者才有执行

INVOKER ：表明拥有权限的调用者可以执行。默认情况下指定为DEFINER  。

5）COMMENT 'string ' :注释信息，可以用来描述存储过程或是函数，

routine_body是SQL代码块的内容，可以用BEGIN....END 来表示SQL代码的开始和结束。

存储过程可能需要复杂的SQL，并且需要创建存储过程的权限。但是使用存储过程将简化操作，减少冗余的操作，

还可以减少操作过程的失误，提高效率。

 

创建第一个基本存储过程：

delimiter $$
CREATE PROCEDURE hello_world()
BEGIN
    SELECT 'Hello World';
END $$

CALL hello_world;-- 执行当前存储过程

 

变量及其赋值 

局部变量：当前存储过程中有效

DECLARE（局部变量定义）、SET（赋值）、INTO（赋值）

语法结构：

-- DECLARE、SET、INTO
DECLARE 变量名 TYPE [DEFAULT 默认值]; -- 声明一个局部变量并设置默认值
SET 变量名 = 值;                                            -- 对当前局部变量赋值
SELECT t.name into 变量名 from table t where id = '1'; -- 将表table中的name字段值赋值给变量名

示例：

delimiter $$
CREATE PROCEDURE hello_world()
BEGIN
    DECLARE testname VARCHAR(32) DEFAULT 'ZS';
    SELECT testname;
    SET testname = 'LS';
    SELECT testname;
END $$

CALL hello_world; -- 执行存储过程
DROP PROCEDURE hello_world; -- 删除存储过程

delimiter $$
CREATE PROCEDURE sp1()
BEGIN
    DECLARE testname VARCHAR(32) DEFAULT 'ZS';
    SELECT m.name into testname from module as m where id = '1'; -- 将表module中的name值使用into关键字赋值给testname
    SELECT testname;
END $$

 

用户变量：用户自定义，当前会话连接即有效

语法结构：@变量名：不需要声明，使用即声明

delimiter $$
CREATE PROCEDURE sp2()
BEGIN
    SET @test_name = 'ww';
    SELECT @test_name;
END $$

CALL sp2; -- 执行存储过程
SELECT @test_name; -- 查询用户变量@test_name

 

会话变量：由系统提供，当前会话连接有效

语法结构：@@SESSION.系统变量名

SHOW SESSION VARIABLES; -- 查看当前会话变量
SELECT @@GLOBAL.auto_increment_increment; -- 查询某会话变量
SET @@SESSION.auto_increment_increment = 1; -- 修改当前某会话变量
SHOW SESSION VARIABLES like '%char%'; -- 查看当前会话变量并进行模糊查询

 

全局变量：整个mysql服务器有效

语法结构：@@GLOBAL.系统变量名

SHOW GLOBAL VARIABLES; -- 查看当前系统变量
SELECT @@GLOBAL.auto_increment_increment; -- 查询某系统变量
SHOW GLOBAL VARIABLES like '%char%'; -- 查看当前系统变量并进行模糊查询

 

入参出参

语法结构：

-- IN 输入参数
-- OUT 输出参数
-- 输入输出参数
IN | OUT | INOUT 参数名 类型;

示例：

delimiter $$
CREATE PROCEDURE sp1(IN var1 VARCHAR(10),OUT var2 VARCHAR(10),INOUT var3 VARCHAR(20))
BEGIN
        SET var2 = var1;
        -- CONCAT 字符串拼接函数
        SELECT CONCAT(var1,var3);
END $$

SET @var3 = 'world';
CALL sp1('hello',@var2,@var3); -- 执行存储过程
SELECT @var2;
SELECT @var3;

 

流程控制-判断

IF-ELSE 

语法结构：

IF 条件判断 THEN 执行语句
    [ELSEIF 条件判断 THEN 执行语句]
    ...
    [ELSE 执行语句]
END IF

 

 CASE

 语法结构一：

CASE 变量名
    WHEN 期望值 THEN 执行语句;
    WHEN 期望值 THEN 执行语句;
    ...
    ELSE 执行语句;
END CASE

语法结构二：

CASE 变量名
    WHEN 判断语句 THEN 执行语句;
    WHEN 判断语句 THEN 执行语句;
    ...
    ELSE 执行语句;
END CASE

 

流程控制：循环

LOOP（死循环）

语法结构

自定义死循环变量:LOOP
    [死循环代码]
END LOOP 自定义死循环变量;

-- LEAVE 跳出当前自定义死循环
-- ITERATE 跳出本次循环,继续下一次循环

示例代码

delimiter $$
CREATE PROCEDURE sp1()
BEGIN
    DECLARE l_index INT DEFAULT 1; -- 声明局部变量 l_index并设置默认值 1
    num_loop:LOOP -- 自定义死循环 num_loop
        IF l_index = 5 THEN -- 如果当前值等于5
        SELECT l_index;    -- 查询输出
        SET l_index = l_index + 1; -- 当前值 + 1
        ITERATE num_loop; -- ITERATE 继续当前死循环 num_loop
        ELSEIF l_index >= 10 THEN
        SELECT l_index;    -- 查询输出
        LEAVE num_loop; -- LEAVE 跳出当前自定义死循环num_loop
        ELSE SET l_index = l_index + 1; -- 当前值 + 1
        END IF; -- IF语句结束
    END LOOP num_loop; -- LOOP语句结束
END $$

delimiter $$
CREATE PROCEDURE sp2()
BEGIN
    DECLARE l_index INT DEFAULT 1;
    num_loop:LOOP
        SET l_index = l_index + 1;
        IF l_index < 10 THEN ITERATE num_loop; -- ITERATE 继续当前死循环num_loop，不会执行后续代码块
        END IF;
        SELECT l_index;
        LEAVE num_loop; -- LEAVE 跳出循环
    END LOOP num_loop; -- LOOP语句结束
END $$

 

REPEAT（先执行循环逻辑，再判断条件，满足则退出循环）

语法结构

自定义循环变量:REPEAT
    [循环代码];
UNTIL 判断条件 -- 直到当前条件满足，才退出循环，此处没有分号;
END REPEAT 自定义循环变量;

delimiter $$
CREATE PROCEDURE sp1()
BEGIN
    DECLARE l_index INT DEFAULT 1; -- 声明局部变量 l_index并设置默认值 1
    num_repeat:REPEAT -- 自定义循环 num_repeat
        SET l_index = l_index + 1;
        UNTIL l_index > 10
    END REPEAT num_repeat; -- REPEAT语句结束
SELECT l_index;
END $$

 

while（while...do循环）

语法结构

自定义循环变量:WHILE 判断条件 DO
    [循环代码];
END WHILE 自定义循环变量;

delimiter $$
CREATE PROCEDURE sp1()
BEGIN
    DECLARE num_index INT DEFAULT 1;
    num_while:WHILE num_index < 10 DO
        SET num_index = num_index + 1;
    END WHILE num_while;
    SELECT num_index;
END $$

 

游标 （用游标得到某一个结果集，逐行处理数据）

语法结构

-- 声明语法
DECLARE 自定义游标变量 CURSOR FOR 查询结果集
-- 打开语法
OPEN 自定义游标变量
-- 取值语法
FETCH 自定义游标变量 INTO 变量1,变量2,变量3...
-- 关闭语法
CLOSE 自定义游标变量

结果集

第一次fetch，游标指向第一行，再次fetch，游标指向下一行，若游标指向最后一行时，再次fetch，会报错 no data，（fetch一次执行一次）

 

 

fetch两次，有两个查询结果

delimiter $$
CREATE PROCEDURE sp1(IN d_no INT)
    BEGIN
        DECLARE e_id INT;
        DECLARE e_name VARCHAR(32);
        DECLARE e_sal DECIMAL(7,2);
        DECLARE e_cursor CURSOR FOR select p.id,p.name,p.sal from pro p where dno = d_no; -- 声明一个游标e_cursor，结果集为FOR后接的查询语句结果集
        OPEN e_cursor; -- 打开游标

        FETCH e_cursor INTO e_id,e_name,e_sal; -- 将查询的结果集依次取出到变量中
        SELECT e_id,e_name,e_sal; -- 查询出当前局部变量值

        FETCH e_cursor INTO e_id,e_name,e_sal; -- 将查询的结果集依次取出到变量中
        SELECT e_id,e_name,e_sal; -- 查询出当前局部变量值

        CLOSE e_cursor; -- 关闭游标
    END $$

delimiter ;
CALL sp1(1); -- 传入输入值 d_no = 1

select p.id,p.name,p.sal from pro p where dno = 1;

 

 

 

使用死循环LOOP取出数据（FETCH）

delimiter $$
CREATE PROCEDURE sp1(IN d_no INT)
    BEGIN
        DECLARE e_id INT;
        DECLARE e_name VARCHAR(32);
        DECLARE e_sal DECIMAL(7,2);
        DECLARE e_cursor CURSOR FOR select p.id,p.name,p.sal from pro p where dno = d_no; -- 声明一个游标e_cursor，结果集为FOR后接的查询语句结果集
        OPEN e_cursor; -- 打开游标
            e_loop:LOOP -- 使用死循环LOOP来遍历结果集实现FETCH所有数据
                FETCH e_cursor INTO e_id,e_name,e_sal; -- 循环取出数据,当没有数据时会报错
            END LOOP e_loop;
        CLOSE e_cursor; -- 关闭游标
    END $$

delimiter ;
CALL sp1(1); -- 传入输入值 d_no = 1

 

当FETCH第四行时没有数据，报错 1329 - No data，此时需要使用 Handler 来处理异常；

HANDLER

语法结构

-- 声明一个指定类型的处理器（Handler），当满足指定条件时（异常、错误...）执行一段语句
DECLARE HANDLER_ACTION HANDLER FOR CONDITION_VALUE 执行语句

HANDLER_ACTION:{ -- HANDLER类型
    CONTINUE -- 继续
| EXIT -- 退出
| UNDO -- 
} 

CONDITION_VALUE:{ -- 条件语句
  mysql_error_code -- mysql错误代码
| sql_state -- SQL状态
| condition_name -- 条件名称
| SQLWARNING -- SQL警告
| NOT FOUND -- 找不到
| SQLEXCEPTION -- SQL异常
}

示例代码

delimiter $$
CREATE PROCEDURE sp1(IN d_no INT)
    BEGIN
        DECLARE e_id INT;
        DECLARE e_name VARCHAR(32);
        DECLARE e_sal DECIMAL(7,2);
        DECLARE e_flag BOOLEAN DEFAULT TRUE; -- 定义一个标记e_flag默认为true,表示有数据，当没有数据时会发生异常，此时更改更改标记
        DECLARE e_cursor CURSOR FOR select p.id,p.name,p.sal from pro p where dno = d_no; -- 声明一个游标e_cursor，结果集为FOR后接的查询语句结果集

        DECLARE CONTINUE HANDLER FOR 1329 SET e_flag = FALSE; -- 声明一个继续执行类型的Handler,发生错误码为1329的异常时，将标记e_flag = FALSE

        OPEN e_cursor; -- 打开游标

            e_loop:LOOP -- 使用死循环LOOP来遍历结果集实现FETCH所有数据
                FETCH e_cursor INTO e_id,e_name,e_sal; -- 循环取出数据,当没有数据时会报错
                    IF e_flag THEN SELECT e_id,e_name,e_sal; -- 如果e_flag = true，查询出来
                    ELSE LEAVE e_loop; -- 否则跳出LOOP循环
                    END IF;
            END LOOP e_loop;

        CLOSE e_cursor; -- 关闭游标
    END $$

delimiter ;
CALL sp1(1); -- 传入输入值 d_no = 1