仅记录本人学习，欢迎交流
记录本人第一次写存储过程。
MySQL 5.0版本开始支持存储过程 存储过程(Stored Procedure)是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。 存储过程是为了完成特定的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数来调用执行 存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
优点
存储过程可封装，并隐藏复杂的商业逻辑存储过程可以回传值，并可以接受参数存储过程无法使用SELECT指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。存储过程可以用在数据检验，强制实行商业逻辑等缺点
存储过程，往往定制化于特定的数据库上，因为支持的变成语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。存储过程的性能调校与撰写，受限于各种数据库系统。执行结果：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结束变量的左右范围即结束
基本语法，存储过程的参数类型有：IN,OUT,INOUT。下面分别介绍这三种类型
create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 ) begin ......... endIN：存储过程的传出参数 说明：
传入参数：类型为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基本结构
-- 条件语句基本结构 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基本结构
-- 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;游标：保存查询结果的临时区域 需求：编写存储过程，使用游标，把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;函数与存储过程的区别 函数必须有返回值，否则会报错
创建函数使用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
！！尽量少使用触发器，不建议使用。 触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。
什么是触发器？ 触发器与函数、存储过程一样，是一种对象，它能够根据对表的操作时间，触发一些动作，这些动作可以是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同理操作。
TODO
如何通过MySQL的存储过程造万条测试数据 需求来源于一个测试的朋友，需要将特定的一条数据重复制造万条，保证ID唯一。
其中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');首先想到的就是用存储过程做一个循环插入，然后就写了一个满足需求的初版本。
首先循环插入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做了长度限制，如果超过会报出字段过长异常
为了解决主键重复、长度过长问题。我想到了取表中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的长度，也算是解决了长度问题。 但再次测试发现，这种写法一万条数据要跑一分钟以上，这对我来说是不能忍的。
再次仔细看了以下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秒。我们都很满意。
第二天，再次拿出这条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秒左右。至此，结束。
