极速掌握MySQL存储过程，开学秒室友

👨‍🎓 博主介绍：
IT邦德，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证

擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，
安装迁移，性能优化、故障应急处理等。

文章目录

前言

本文结合本人工作10年经验，对MySQL存储过程做了详细的说明~

🍁一、 简介

🍃 1.1 原理

我们常用的操作数据库语言 SQL 语句在执行的时候需要要先编译，然后执行，
而存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

🍃 1.2 优点

(1).存储过程增强了 SQL 语言的功能和灵活性。
(2).存储过程允许标准组件是编程。
(3).存储过程能实现较快的执行速度。
(4).存储过程能过减少网络流量。
(5).存储过程可被作为一种安全机制来充分利用。

MySQL 5.0 终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，
同时也可以提高数据库编程的灵活性。

🍁 二、使用

🍃 2.1 常用命令

##删除
drop PROCEDURE pro_count_mysql
##创建
CREATE PROCEDURE pro_count_mysql(OUT P_o_cnt int)
BEGIN
SELECT COUNT(*) INTO P_o_cnt FROM mysql.user;
END;
##调用，给一个变量
call pro_count_mysql(@s);
##查询变量
mysql> select @s;
##数据字典
mysql> SHOW CREATE PROCEDURE proc6;
mysql> SHOW PROCEDURE STATUS --列出所有的存储过程
mysql> show procedure status where db=‘mes_db’;
注：部分版本mysql初始化不允许建存储过程，需要做如下设置
SET GLOBAL log_bin_trust_function_creators = 1;

🍃 2.2 变量定义

#外部变量
mysql> set @p1=‘IT邦德’；
mysql> select @p1;

内部定义变量用declare，如下所示

🍃 2.3 参数

in 参数

DELIMITER //
CREATE PROCEDURE demo_in_parameter(IN p_in int)
BEGIN
SELECT p_in;
SET p_in=2;
SELECT p_in;
END;
//
DELIMITER ;
##调用，给一个变量，不影响外面参数的值
call demo_in_parameter(3);

MySQL [db1]> set @p_in=66;
MySQL [db1]> call demo_in_parameter(@p_in);

注意：CMD窗口执行，记得留意空格

inout 参数

##CMD创建
DELIMITER //
CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
END;
//
DELIMITER ;

##调用，影响外面传参的值
mysql> SET @p_inout=1;
mysql> CALL demo_inout_parameter(@p_inout);

🍁 三、实战案例

🍃 3.1 条件语句

mysql> create table t1(id int);

##case 语句
DELIMITER //
CREATE PROCEDURE proc3 (in parameter int)
begin
declare var int;
set var=parameter+1;
case var
when 0 then
insert into t values(17);
when 1 then
insert into t values(18);
else
insert into t values(19);
end case;
end;
//
DELIMITER ;

🍃 3.2 循环语句

mysql> create table t2(id int);

##repeat···· end repeat,它在执行操作后检查结果
DELIMITER //
CREATE PROCEDURE proc5 ()
begin
declarev int;setv=0;
repeat
insert into t values(v);setv=v+1;until v>=5
end repeat;
end;
//
DELIMITER ;

mysql> call proc5;
mysql>select * from t2;

🍃 3.3 动态生成表

##学生表
select * from tb_student

##成绩表
select * from tb_score

选课表
select * from tb_course

编写一个存储过程，
要求对每门已选课程生成成绩表，表名为课程名，成绩表的字段包括学号、姓名、班级、成绩。
如果能独立完成，证明你的数据库应用能力已经跃上了一个台阶

DROP PROCEDURE create_table_pro;

DELIMITER //
CREATE PROCEDURE create_table_pro()
BEGIN
# 定义接受sql数据的变量
DECLARE courseNo varchar(50);
DECLARE studentNo varchar(20);
DECLARE studentName varchar(20);
DECLARE classNo varchar(20);
DECLARE score int;
  

# 声明表名称
DECLARE tblname VARCHAR(32);
# 总行数
DECLARE cnt INT DEFAULT 0;
# 循环变量i
DECLARE i INT DEFAULT 0;

declare tmp_cursor cursor for(
select 
c.courseNo,
a.studentNo,
a.studentName,
a.classNo,
b.score from tb_student a,tb_score b,tb_course c
where a.studentNo = b.studentNo
and b.courseNo = c.courseNo
order by 1);

SELECT COUNT(*) INTO cnt from tb_student a,tb_score b,tb_course c
where a.studentNo = b.studentNo
and b.courseNo = c.courseNo;

open tmp_cursor;

 # 开始循环
  REPEAT
    SET i := i+1;
      FETCH tmp_cursor INTO courseNo,studentNo,studentName,classNo,score;
     
      SET tblname = CONCAT('course_',courseNo);
      
      SET @delTabl = CONCAT('DELETE FROM ',tblname,' WHERE studentNo=','''',studentNo,'''');
      
      SET @createTbsql = CONCAT('create table if not exists ',tblname,'(studentNo char(10) not null primary key,
      studentName varchar(10) not null,classNo char(6),credite int) ENGINE=InnoDB DEFAULT CHARSET=GB2312;');
      
      SET @insertTabl = CONCAT(' insert into ',tblname,' values(',
      '''',studentNo,'''','\,',
      '''',studentName,'''','\,',
      '''',classNo,'''','\,',
      '''',score,'''',
      ')'
      );
      
      select @insertTabl;
      
      # 执行动态删除表语句
      PREPARE temp FROM @delTabl;
      EXECUTE temp;
      DEALLOCATE PREPARE temp;
            
      # 执行动态生成的创建表语句
      PREPARE temp FROM @createTbsql;
      EXECUTE temp;
      DEALLOCATE PREPARE temp;
      
      # 执行动态表插入语句
      PREPARE temp FROM @insertTabl;
      EXECUTE temp;
      DEALLOCATE PREPARE temp;
     
      UNTIL i>=cnt END REPEAT;
      
# 循环结束
close tmp_cursor;
END;
//
DELIMITER ;

大家点赞、收藏、关注、评论啦 👇🏻👇🏻👇🏻微信公众号👇🏻👇🏻👇🏻