（2.11）Mysql之SQL基础——存储过程与变量及动态SQL

（2.11）Mysql之SQL基础——存储过程

关键字：mysql存储过程

注意！！！： 局部变量会覆盖相同列名，如下图，可以使用 v_id 作为变量id的命名，这样就会改成  id = v_id。

　　　　　　或者列名使用 表名.列名，如下图，可以把where后面的列名id改成  test1.id=id.

　　

【0】查看存储过程

【1】SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 'proc_or_func'
【2】SHOW CREATE PROCEDURE proc_countByName;
【3】SHOW PROCEDURE STATUS LIKE 'proc_%';

【1】存储过程的基本语法（不带参数）

--（1）通用定义

create DEFINER = user@ip procedure 过程名(参数) 
begin 
　　过程体 
end 

--（2）创建简单的存储过程例子

#因为mysql中分号是结束符，为了避免在过程体重把分号前面的所有语句当成一个sql去执行，所以要使用 delimiter 来替换结束符;

delimiter //   #声明//为结束符
create DEFINER = 'root'@'%' procedure sp_test()
begin 
　　select 1 from dual; 
end // 
delimiter ; #把结束符声明回来为分号';'

--（3）调用存储过程
call sp_test();

--如果存储过程定义了2个参数.. 如： create procedure sp_test(in i_num1 int,in i_num2 int);
那么调用的时候一定也要给与相等数量的参数值，可以为null，''等，但一定要给
call sp_test(0,0)

--（4）查询存储过程
[1]状态：show procedure status like '%sp_test%';
[2]语句：show create procedure sp_test;

--（5）删除存储过程
drop procedure sp_test;

--（6）修改存储过程
mysql8.0以下暂时没有提供直接修改存储过程代码的功能；只能删除重建。

--（7）在存储过程中退出（如mssql中的return）
leave back;

【2】带参数的存储过程

　　

--(1)参数类型
【1】in：入参　　【2】out：出参　　【3】inout：即是输入又是输出

--举例应用

delimiter //

create procedure sp_test(in num int,out num1 int,inout num2 int) 
begin 
if num is null then 
select 10 into num;  #存储过程中对变量的赋值操作
end if; 
select 10+num into num1;
select num1+num2+100 into num2; 
end //
delimiter ;


--（2）调用
select 100 into @num2; -- 定义使用局部变量
call sp_test(null,@num1,@num2);
select @num1,@num2;
得出最后结果：
　　

【3】存储过程的流程控制语句

-- (1) 关键词
【1】 IF THEN ELSE END FI;　　【2】case when  then else end;
【3】 repeat sql_statement until [end_condition] end repeat;   -- 类似于do while,无论如何都会执行一次循环体；只是，这个需要end_condition为真才结束
【4】 while [start_condition] do sql_statement end while 

-- (2)演示
【1】repeat
#插入n~999的值，如果n>=1000则会执行一次循环体内的语句（即插入值n,'a'到表bm）
delimiter //
create procedure sp_test1(in n int)  
begin 
　　repeat 
　　　　insert into bm values(n,'a'); set n=n+1; 
　　until n>=1000 end repeat; 
end //
delimiter ;

【2】while

#用while改写【1】

delimiter //
create procedure sp_test1(in n int)  
begin 
　　while(n<1000) do
　　　　insert into bm values(n,'a'); set n=n+1; 
　　end while;
end //
delimiter ;

【4】变量

-- (1)基本定义
#在存储过程中，带默认值的变量
declare n int default 10;
DECLARE var1,var2,var3 INT;

-- (2)变量赋值
#在存储过程中或mysql登录状态下
select 1 into n;
set n=1;

-- (3)局部变量：以@开头的为会话级别生效的局部变量(只会跟当前客户端绑定)

#在存储过程中或mysql登录状态下
set @n=1;  --以@开头的为会话级别生效的局部变量(只会跟当前客户端绑定)

-- (4)全局变量：定义时以如下两种形式出现
【1】set global 变量名　　【2】set @@global.name  --对所有客户端生效（需要super权限才可以设置全局变量）
演示：
mysql> set global sort_buffer_size = value;
mysql> set @@global.sort_buffer_size = value;

--(5)会话变量：定义时如下2种形式

【1】set session 变量名　　【2】set @@session.name 

mysql> set session sort_buffer_size = value;

mysql> set @@session.sort_buffer_size = value;

--

 【5】存储过程--动态SQL

这里介绍两种在存储过程中的动态sql：

【5.1】动态SQL的基本形式

　　set sql = （预处理的sql语句，可以是用concat拼接的语句）

　　set @sql = sql

　　PREPARE stmt_name FROM @sql;

　　EXECUTE stmt_name;

　　{DEALLOCATE | DROP} PREPARE stmt_name;

实例演示：

use db_name;
drop procedure if exist NewProc;

delimiter ||
CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))
BEGIN
    
      declare SQL_FOR_SELECT varchar(500);  -- 定义预处理sql语句
      set SQL_FOR_SELECT = CONCAT("select * from  user  where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'");   -- 拼接查询sql语句
      set @sql = SQL_FOR_SELECT;
      PREPARE stmt FROM @sql;         -- 预处理动态sql语句
      EXECUTE stmt ;                  -- 执行sql语句
      deallocate prepare stmt;        -- 释放prepare

END ||
delimiter ;

【5.2】sql变量存了值，变量再变化，就不会改变SQL变量

　　上述是一个简单的查询用户表的存储过程，当我们调用此存储过程，可以根据传入不同的参数获得不同的值

 　　但是：上述存储过程中，我们必须在拼接sql语句之前把USER_ID,USER_NAME定义好，而且在拼接sql语句之后，我们无法改变USER_ID,USER_NAME的值，如下

use db_name;
drop procedure if exist NewProc;

delimiter ||
CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))
BEGIN
    
        declare SQL_FOR_SELECT varchar(500);  -- 定义预处理sql语句

        set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'");   -- 拼接查询sql语句
        set @sql = SQL_FOR_SELECT;
      　PREPARE stmt FROM @sql;        -- 预处理动态sql语句
      　EXECUTE stmt ;                       -- 执行sql语句
        deallocate prepare stmt;       -- 释放prepare


        set USER_ID = '2';
        set USER_NAME = 'lisi';
        set @sql = SQL_FOR_SELECT;
      　PREPARE stmt FROM @sql;        -- 预处理动态sql语句
      　EXECUTE stmt ;                       -- 执行sql语句
        deallocate prepare stmt;      -- 释放prepare
END ||
delimiter ;

【5.3】定义带参数的动态SQL

　　

我们用call aa('1','zhangsan');来调用该存储过程，第一次动态执行，我们得到了‘张三’的信息。

然后我们在第14,15行将USER_ID，USER_NAME改为lisi我们希望得到李四的相关信息。

可查出来的结果依旧是张三的信息，说明我们在拼接sql语句后，不能再改变参数了。为了解决这种问题，下面介绍第二中方式

　set sql = （预处理的sql语句，可以是用concat拼接的语句，参数用 ？代替）

　set @sql = sql

　PREPARE stmt_name FROM @sql;

　set @var_name = xxx;

　EXECUTE stmt_name USING [USING @var_name [, @var_name] ...];

　{DEALLOCATE | DROP} PREPARE stmt_name;

修改案例代码为：

use db_name;
drop procedure if exist NewProc;

delimiter ||
CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))
BEGIN
    
        declare SQL_FOR_SELECT varchar(500);-- 定义预处理sql语句

        set SQL_FOR_SELECT = "select * from user where user_id = ? and user_name = ? ";   -- 拼接查询sql语句

        set @sql = SQL_FOR_SELECT;
        PREPARE stmt FROM @sql; -- 预处理动态sql语句

        set @parm1 = USER_ID;  -- 传递sql动态参数
        set @parm2 = USER_NAME;

        EXECUTE stmt USING @parm1 , @parm2;　-- 执行sql语句
        deallocate prepare stmt;            -- 释放prepare


        set @sql = SQL_FOR_SELECT;
        PREPARE stmt FROM @sql;   -- 预处理动态sql语句

        set @parm1 = '2';    -- 传递sql动态参数
        set @parm2 = 'lisi';

        EXECUTE stmt USING @parm1 , @parm2; -- 执行sql语句
        deallocate prepare stmt;            -- 释放prepare
END ||
delimiter ;