目录
一、定义
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;
361
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
