目录
仅记录本人学习，欢迎交流
MySQL存储过程入门
记录本人第一次写存储过程。
一、概念
MySQL 5.0版本开始支持存储过程
存储过程(Stored Procedure)是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数来调用执行
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
优点
- 存储过程可封装，并隐藏复杂的商业逻辑
- 存储过程可以回传值，并可以接受参数
- 存储过程无法使用SELECT指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
- 存储过程可以用在数据检验，强制实行商业逻辑等
缺点
- 存储过程，往往定制化于特定的数据库上，因为支持的变成语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
- 存储过程的性能调校与撰写，受限于各种数据库系统。
二、简单语法介绍
1、创建存储过程
create procedure 名称()
begin
.........
end
2、调用存储过程
call 名称();
3、存储过程的变量
create procedure 名称()
begin
-- 使用 declare语句声明一个变量
declare XXX varchar(32) default '';
-- 使用set语句给变量赋值
set XXX='xiaoxiao';
-- 将users表中id=1的名称赋值给username
select username into XXX from users where id=1;
-- 返回变量
select XXX;
end;
- 变量的声明使用declare，语句declare只声明一个变量，变量必须先声明后使用;
- 变量具有数据类型和长度，支持所有MySQL的SQL数据类型，还可以指定默认值、字符集和排序规则等。
- 变量可以通过set来赋值，也可以通过select into的方式进行赋值。
- 变量需要返回，可以使用select语句，例如：select 变量名。
4、变量的作用域
- 变量作用域说明
- 存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束。
- 需要多个块之间传值，可以使用全局变量，即放在所有代码块之前
- 传参变量是全局的，可以在多个块之间起作用
- begin…end可以嵌套多个begin…end
- 示例
需求：创建一个存储过程，用来统计表users、orders表中行数数量和orders表中最大金额和最小金额。
create procedure test3()
begin
begin
declare userscount int default 0; -- 用户表中的数量
declare ordercount int default 0; -- 订单表中的数量
select count(*) into userscount from users;
select count(*) into ordercount from orders;
select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
end;
begin
declare maxmoney int default 0; -- 最大金额
declare minmoney int default 0; -- 最小金额
select max(money) into maxmoney from orders;
select min(money) into minmoney from orders;
select maxmoney,minmoney; -- 返回最金额、最小金额
end;
执行结果：userscount=4，ordercount=3
maxmoney=300，minmoney=100
如果将sql改为如下
create procedure test3()
begin
begin
declare userscount int default 0; -- 用户表中的数量
declare ordercount int default 0; -- 订单表中的数量
select count(*) into userscount from users;
select count(*) into ordercount from orders;
select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
end;
begin
declare maxmoney int default 0; -- 最大金额
declare minmoney int default 0; -- 最小金额
select max(money) into maxmoney from orders;
select min(money) into minmoney from orders;
select userscount,ordercount，maxmoney,minmoney; -- 返回最金额、最小金额
end;
end;
就会报异常。Unknown column ‘usercount’ in ‘field list’
如果将userscount、orderscount改为全局变量，如下：
create procedure test3()
begin
declare userscount int default 0; -- 用户表中的数量
declare ordercount int default 0; -- 订单表中的数量
begin
select count(*) into userscount from users;
select count(*) into ordercount from orders;
select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
end;
begin
declare maxmoney int default 0; -- 最大金额
declare minmoney int default 0; -- 最小金额
select max(money) into maxmoney from orders;
select min(money) into minmoney from orders;
select userscount,ordercount，maxmoney,minmoney; -- 返回最金额、最小金额
end;
end;
再次调用，输出结果为：
userscount=4,ordercount=3
userscount=4,ordercount=3,maxmoney=300,minmoney=100
由此可见，存储过程中变量的作用域，作用范围在begin和end块之间，end结束变量的左右范围即结束
6、存储过程参数
基本语法，存储过程的参数类型有：IN,OUT,INOUT。下面分别介绍这三种类型
create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end
IN：存储过程的传出参数
说明：
- 传入参数：类型为IN，表示该参数的值必须在调用存储过程时指定，如果不显示指定为IN，那么默认就是IN类型
- IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回
- 如果调用存储过程中需要修改和返回值，可以使用OUT类型参数
下面通过一个实例来演示。需求：编写存储过程，传入id，根据id返回name
create procedure getNameByIdIN(id int)
begin
declare username varchar(32) default '';
declare ordercount int default '';
select name into username from users where userId = id;
select username;
end
call getNameByIdIN;
OUT：存储过程的传出参数
- 传出参数：在调用存储过程中，可以改变其值，并可以返回。
- out时传出参数，不能用域传入参数值
- 调用存储过程时，out参数也需要指定，但必须是变量，不能是常量
- 如果既需要传入，同时也需要传出，则可以用INOUT类型参数。
需求：调用存储过程时，传入id，返回该用户的name。
create procedure getNameByIdOUT(in id int,out username varchar(32))
begin
select name into username from users where userId = id;
end
-- 定义一个变量，set @变量名
set @uname = '';
call getNameByIdOUT(1,@uname)
select @uname as username;
INOUT：存储过程的可变参数
- 可变变量INOUT，调用时可传入值，在调用过程中，可修改其值，同时也可以返回值
- INOUT参数集合了IN和OUT类型的参数功能
- INOUT调用时传入的是变量，而不是常量
需求：调用存储过程时，传入id和userName。两者既是传入参数也是传出参数
-- 创建存储过程
create procedure getIdAndNameByIdINOUT(inout id int,inout username varchar(32)
begin
set id = 2;
set username = '';
select userId,name into id,username from users where usedId = id;
end
--调用
set @uname = '';
set @uid = '';
call getIdAndNameByIdINOUT(@uid,@uname );
select @uid,@uname as username
7、存储过程条件语句
基本结构
-- 条件语句基本结构 if() then ... else ... end if;-- 多条件判断语句 if() then ... elseif then ... else ... end if;
需求1：编写存储过程，如果用户userId是偶数则返回username，否则返回userId
create procedure getIdOrName(in userId int) begin declare username varchar(32) default ''; if(userId%2=0) then select name into username from users where if = userId; select username else select userId; end if; end
需求2：存储过程的多条件应用示例。
根据用户传入的uid参数判断
- 如果用户状态status为1，则给用户score加10分;
- 如果用户状态status为2，则给用户score加20分;
- 其他情况加30分;
create procedure updateScoreByStatus(in userId int) begin declare Astatus int default 0; select status into Astatus from users where id = userId; if(Astatus=1) then update users set score=score+10 where id= userId; elseif(Astatus=2) then update users set score=score+20 where id = userid; else update users set score=score+30 where id = userid; end if; end
8、存储过程循环语句
基本结构
-- while语句 while(表达式) do ...... end while;--repeat语句 repeat...until...end repeat;
需求1：使用while循环语句，向表demo中插入10条连续的数据
create procedure insertByWhile() begin declare i int default 0; while(i<10) do begin select i ; set i = i+1; insert into demo(id) values(i); end; end while; end;
需求2：使用repeat语句向表demo中插入10条连续数据
create procedure insertByRepeat() begin declare i int default 0; repeat begin select i; set i=i+1; insert into demo(id) values(i); end; until i>10 -- 如果i>=10，跳出循环 end repeat; end;
9、存储过程游标的使用
游标：保存查询结果的临时区域
需求：编写存储过程，使用游标，把users表中id为偶数的记录逐一更新用户名称
create procedure updateUserNameByIdForEven()
begin
declare flag int default 0;
declare username varchar(32);
-- 创建一个游标变量，语法：declare 变量名 cursor ...
declare curname cursor for select name form users where id %2=0;
-- 游标变量curname保存了查询的结果集
-- 遍历游标中的结果集，到达结尾，将变量flag设置为1，用于判断循环是否结束
declare continue handler for not found set flag=1;
-- 打开游标
open curname;
-- 游标向前一步，取出一条记录放到变量username中
fetch curname into username;
-- 如果游标还没有结尾，就继续执行
while(flag=0) do
begin
-- 更新用户名
update users set name='程大帅气' where name = username;
fetch curname into username;
end;
end while; -- 结束循环
close cuename;--关闭游标
end;
10、自定义函数
函数与存储过程的区别
函数必须有返回值，否则会报错
- 创建函数使用create function 函数名(参数) returns 返回类型;
- 函数体放在begin和end之间;
- returns指定函数的返回值;
- 函数调用使用select 函数名(参数);
需求1：根据userId，获取name,id,age作为用户的标识
create function getIdCard(userid int) returns varchar(64) --从数据库中读取数据，但不做修改 reads sql data begin declare idcard varchar(64) default ''; select concat(name.'-',id,'-',age) into idcard from users where id = userid; return idcard; end; --调用函数 select getidcard(1);返回值：程大帅气-1-18
11、触发器
！！尽量少使用触发器，不建议使用。
触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。
什么是触发器？
触发器与函数、存储过程一样，是一种对象，它能够根据对表的操作时间，触发一些动作，这些动作可以是insert、update、delete等修改操作。
- 创建触发器使用create trigger 触发器名
- 触发时机：
- after insert on 表名，在insert后触发动作
- before insert on 表名，在insert前触发动作
- 事件类型：
- after insert on 表名，对此表进行插入操作时触发。
- after update on 表名，对此表中数据更新时触发
- after delete on 表名，对此表中数据进行删除操作时触发。
- FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。
需求1：当有人网users表中插入一条记录时，将插入的信息、操作类型、操作时间记录到一张日志表中。
create trigger tr_insert_users after insert on users for each row begin insert into insertlog(id,name,type,optime) values(NEW.id,NEW.name,'insert'.now()); end;创建成功后在users表中插入一条数据，发现insertlog表同步插入了一条数据。
需求2：当删除users表中数据时，记录删除的name,id,操作事件等
create trigger tr_delete_users before delete on users for each row begin insert into deletelog(userId,username,optime) values(OLD.id,OLD.name,now()); end;接着删除users表中的一条数据，会同步记录到deletelog表中。update同理操作。
12、流程控制
TODO
实战演练
如何通过MySQL的存储过程造万条测试数据
需求来源于一个测试的朋友，需要将特定的一条数据重复制造万条，保证ID唯一。
一、数据库准备
1、DDL建表语句
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for jdp_tb_trade
-- ----------------------------
DROP TABLE IF EXISTS `jdp_tb_trade`;
CREATE TABLE `jdp_tb_trade` (
`tid` bigint(20) NOT NULL,
`status` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`type` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`seller_nick` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`buyer_nick` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`created` datetime(0) NULL DEFAULT NULL,
`modified` datetime(0) NULL DEFAULT NULL,
`jdp_hashcode` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`jdp_response` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`jdp_created` datetime(0) NULL DEFAULT NULL,
`jdp_modified` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
2、需要复制的数据sql
其中jdp_response是一个json格式的响应报文，包含了tid(唯一键)。需要将jdp_response中的tid替换成新生成的tid。
select tid ,status,type,
seller_nick,buyer_nick ,
created,modified ,jdp_hashcode ,
jdp_response,jdp_created ,now()
from jdp_tb_trade
where seller_nick='CLUB MONACO官方旗舰店'
and status='WAIT_SELLER_SEND_GOODS'
and type='fixed');
二、此存储过程演进
1、初版
首先想到的就是用存储过程做一个循环插入，然后就写了一个满足需求的初版本。
- 首先循环插入
- tid每次增加1，保证不重复
- jdp_response每次替换tid，保证和生成的id一致。
- limit 1 防止多次执行后，数据过多。保证查出数据是一条
DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
-- 循环插入
insert into jdp_tb_trade(
select tid+i tid ,status,type,
seller_nick,
buyer_nick ,
created,
modified ,
jdp_hashcode ,
replace(jdp_response,tid,tid+i) jdp_response,
jdp_created ,
now() as jdp_modified
from jdp_tb_trade
where seller_nick='CLUB MONACO官方旗舰店'
and status='WAIT_SELLER_SEND_GOODS'
and type='fixed' limit 1);
SET i = i+1;
END WHILE;
END $
CALL proc_initData()
但测试一次发现，还是会有主键重复问题。i是由程序生成的一个比较长的int串，且tid做了长度限制，如果超过会报出字段过长异常
2、解决主键重复，长度问题
为了解决主键重复、长度过长问题。我想到了取表中tid的最小值，进行每次-1，所以就有了以下SQL
DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
insert into jdp_tb_trade(
select (select min(tid)-1 from jdp_tb_trade where tid is not null) tid
,status,type,
seller_nick,
buyer_nick ,
created,
modified ,
jdp_hashcode ,
replace(jdp_response,tid,(select min(tid)-1 from jdp_tb_trade where tid is not null)) jdp_response,
jdp_created ,
now() as jdp_modified
from jdp_tb_trade
where seller_nick='CLUB MONACO官方旗舰店'
and status='WAIT_SELLER_SEND_GOODS'
and type='fixed' limit 1);
SET i = i+1;
END WHILE;
END $
CALL proc_initData()
好的，这次不会出现主键重复的问题了，由于tid支持负数再加上tid20的长度，也算是解决了长度问题。
但再次测试发现，这种写法一万条数据要跑一分钟以上，这对我来说是不能忍的。
3、解决执行时间过长
再次仔细看了以下sql，分析出了原因。这条insert语句的tid是通过sql查询出来的，每插入一次，都需要执行两次查询tid的操作，大大提升了响应的时间。故将查询tid语句抽出，当作一变量，有了下面的sql
DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE minTid BIGINT select min(tid) from jdp_tb_trade where tid is not null limit 1;
WHILE i<=10000 DO
insert into jdp_tb_trade(
select (minTid -1) tid
,status,type,
seller_nick,
buyer_nick ,
created,
modified ,
jdp_hashcode ,
replace(jdp_response,tid,((minTid -1))) jdp_response,
jdp_created ,
now() as jdp_modified
from jdp_tb_trade
where seller_nick='CLUB MONACO官方旗舰店'
and status='WAIT_SELLER_SEND_GOODS'
and type='fixed' limit 1);
SET i = i+1;
END WHILE;
END $
CALL proc_initData()
经过这么一折腾，插入一万条数据，仅需要20秒。我们都很满意。
4、终版
第二天，再次拿出这条sql，感觉还能提升一下，循环体只做插入动作，不再每次执行select，将循环体中的select也抽出，查出的数据变成变量存储。有了以下sql
-- 创建存储过程
DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData(Acount int)
BEGIN
DECLARE i int DEFAULT 1;
DECLARE minTid BIGINT DEFAULT (select min(tid) from jdp_tb_trade where tid is not null limit 1);
DECLARE Atid BIGINT;
DECLARE Astatus VARCHAR(64);
DECLARE Atype VARCHAR(64);
DECLARE Aseller_nick VARCHAR(32);
DECLARE Abuyer_nick VARCHAR(255);
DECLARE Acreated datetime;
DECLARE Amodified datetime;
DECLARE Ajdp_hashcode VARCHAR(128);
DECLARE Ajdp_response MEDIUMTEXT;
DECLARE Ajdp_created datetime;
select
tid,type,status,seller_nick,buyer_nick, created,modified,jdp_hashcode,jdp_response,jdp_created
from jdp_tb_trade where seller_nick='CLUB MONACO官方旗舰店' and status='WAIT_SELLER_SEND_GOODS' and type='fixed' limit 1 into
Atid,Astatus,Atype,Aseller_nick,Abuyer_nick,Acreated,Amodified,Ajdp_hashcode,Ajdp_response,Ajdp_created;
WHILE i<=Acount DO
insert into jdp_tb_trade (tid, status, type,seller_nick,buyer_nick,created,modified,jdp_hashcode,jdp_response,jdp_created,jdp_modified)
value((minTid-i),Astatus,Atype,Aseller_nick,Abuyer_nick,Acreated,Amodified,Ajdp_hashcode,replace(Ajdp_response,Atid,(minTid-i)),Ajdp_created,now());
SET i = i+1;
END WHILE;
END $
-- 调用存储过程，这里传入要生成的条数
CALL proc_initData(100000);
十万条插入，仅需要60秒左右。至此，结束。
149
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
