MySQL存储过程详解与案例应用

目录

一、定义

1.1 介绍

1.2 存储过程的种类

1.3 存储过程的优点

1.4 基本语法

二、案例应用

2.1 建表和导入案例数据

2.2 普通查询

2.3 if 条件语句

2.4 case 条件语句

2.5 while 循环语句

2.6 repeat 循环语句

2.7 loop 循环语句

2.8 修改存储过程

2.9 存储函数

2.10 浮标

---

一、定义

1.1 介绍

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

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

1.2 存储过程的种类

系统存储过程
以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。

本地存储过程
用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

临时存储过程
分为两种存储过程：
一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

远程存储过程
在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

扩展存储过程
扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

1.3 存储过程的优点

1) 封装性
存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

2) 可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

3) 可减少网络流量
由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

4) 高性能
存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。

5) 提高数据库的安全性和数据的完整性
使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。

1.4 基本语法

--------------创建存储过程-----------------
 
DELIMITER //
CREATE PROCEDURE procedure_name([in ,out ,inout] 参数名 数据类型...)
BEGIN
 
END;
 
--------------调用存储过程-----------------
 
CALL procedure_name(值1, 值2, ...); 
 
--------------删除存储过程-----------------
 
DROP PROCEDURE procedure_name;
 
--------------查询存储过程状态-----------------
 
SHOW PROCEDURE STATUS WHERE db='db1' and name='procedure_name';
 
--------------显示存储过程源码-----------------
 
SHOW CREATE PROCEDURE procedure_name;

二、案例应用

 2.1 建表和导入案例数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for pro_table
-- ----------------------------
DROP TABLE IF EXISTS `pro_table`;
CREATE TABLE `pro_table`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` tinyint(3) UNSIGNED NULL DEFAULT 0 COMMENT '0男 1女',
  `age` int(10) UNSIGNED NULL DEFAULT 0,
  `add_time` int(10) UNSIGNED NULL DEFAULT 0,
  `up_time` int(10) UNSIGNED NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
 
-- ----------------------------
-- Records of pro_table
-- ----------------------------
INSERT INTO `pro_table` VALUES (1, '张强', 0, 28, 1662713804, 0);
INSERT INTO `pro_table` VALUES (2, '刘云', 1, 30, 1662713804, 0);
INSERT INTO `pro_table` VALUES (3, '王刚', 0, 31, 1662713804, 0);
INSERT INTO `pro_table` VALUES (4, '刘博', 0, 29, 1662713804, 0);
INSERT INTO `pro_table` VALUES (5, '王红', 1, 30, 1662713804, 0);
INSERT INTO `pro_table` VALUES (6, '刘丽', 1, 32, 1662713804, 0);
 
SET FOREIGN_KEY_CHECKS = 1;

 2.2 普通查询

# 传入参数查询
DELIMITER //
 
CREATE PROCEDURE pro1(in sname VARCHAR(100))
 
BEGIN
select * from pro_table where name=sname;
END;
 
call pro1('张强');
 
 
# 设置查询条件
DELIMITER //
 
CREATE PROCEDURE pro2()
 
BEGIN
declare sname varchar(100);
set sname = '张强';
 
select * from pro_table where name=sname;
END;
 
call pro2();

 2.3 if 条件语句

DELIMITER //
CREATE PROCEDURE pro1(in age_type int(10))
BEGIN
 
if age_type = 1 then
select * from pro_table where age between 28 and 30;
elseif age_type = 2 then
select * from pro_table where age between 30 and 31;
else 
select * from pro_table where age between 31 and 32;
end if;
END;
 
call pro1(2);

2.4 case 条件语句

DELIMITER //
CREATE PROCEDURE pro1(in age_type int(10))
BEGIN
 
case 
when age_type = 1 then
select * from pro_table where age between 28 and 30;
when age_type = 2 then
select * from pro_table where age between 30 and 31;
when age_type = 3 then
select * from pro_table where age between 31 and 32;
end case;
END;
 
call pro1(2);

 2.5 while 循环语句

DELIMITER //
CREATE PROCEDURE pro1(in num int(10))
BEGIN
declare snum int(10) default 0;
while num>1 do
 
set snum = snum + num;
set num = num -1;
end while;
select snum;
END;
 
call pro1(10);

2.6 repeat 循环语句

DELIMITER //
CREATE PROCEDURE pro1(in num int(10))
BEGIN
declare snum int(10) default 0;
repeat
set snum = snum + num;
set num = num+1;
 
until num>10
end repeat;
select snum;
END;
 
call pro1(1);

 2.7 loop 循环语句

DELIMITER //
CREATE PROCEDURE pro1(in num int(10))
BEGIN
declare snum int(10) default 0;
 
loop_sum:loop
set snum = snum + num;
set num = num+1;
if num>10 then
leave loop_sum;
end if;
end loop loop_sum;
 
select snum;
END;
 
call pro1(1);

 2.5、2.6、2.7三个循环语句输出结果都是 55

 2.8 修改存储过程

MySQL 中修改存储过程的语法格式如下：

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

特征指定了存储过程的特性，可能的取值有：

• CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
• NO SQL 表示子程序中不包含 SQL 语句。
• READS SQL DATA 表示子程序中包含读数据的语句。
• MODIFIES SQL DATA 表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行（DEFINER 表示只有定义者自己才能够执行，INVOKER 表示调用者可以执行）。
• COMMENT 'string' 表示注释信息。

 2.9 存储函数

存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作。

基本语法： 

DELIMITER //
CREATE FUNCTION 方法名([ 参数列表 ])
RETURNS 返回值类型 特征
 
BEGIN
return 返回值;
END;

特征：
DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL ：不包含 SQL 语句。
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

案例：

----------------------新建存储方法-------------
DELIMITER //
CREATE FUNCTION fun1(num int(10))
RETURNS int(10) deterministic
 
BEGIN
DECLARE snum int(10) default 0;
while num>0 do
set snum = snum + num;
set num = num - 1;
end while;
 
return snum;
END;
 
-------------------执行存储方法----------------
select fun1(2);
 
-------------------删除存储方法----------------
drop function fun1;

 2.10 浮标

在mysql存储过程中，游标也称为光标，是一个存储在DBMS服务器上的数据库查询，是检索操作返回一组结果集，一般用于对检索出来的数据进行前进或者后退操作。

案例： 

CREATE PROCEDURE pro1()
BEGIN
 DECLARE id_val int(10);
 DECLARE name_val varchar(255);
 DECLARE age_val int(10);
 DECLARE my_cursor CURSOR FOR SELECT id,name,age FROM pro_table where name='王刚'; 
 
 OPEN my_cursor;
 fetch my_cursor into id_val,name_val,age_val;
 SELECT CONCAT_WS(',',id_val,name_val,age_val);
 CLOSE my_cursor;
 
END;