存储过程
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
语法
声明结束符，避免与mysql语句结束符 分号 冲突，并在存储过程结束时，还原为分号
DELIMITER #
声明存储过程
CREATE PROCEDURE demo_in_parameter(IN p_in int)
存储过程开始/结束符
BEGIN .... END
变量赋值:
SET @p_in=1
变量定义:
DECLARE l_int int unsigned default 4000000;
条件语句:
if parameter=0 then
update t set s1=s1+1;
else
update t set s1=s1+2;
end if;
循环语句
declare var int;
set var=0;
while var<6 do
insert into t values(var);
set var=var+1;
end while;
存储过程实例
定义存储过程
delimiter ?
drop procedure batch_version_step ?
create procedure batch_version_step(in max int)
begin
declare start int default 0;
declare i int default 0;
set autocommit = 0;
while i < max do
set i = i + 1;
insert into t_dcms_version_step(create_by,create_time,del_flag,update_by,update_time,version_id,world_id,topic_id,type,content,status,class)
values (0,1573528839,0,0,1573528851,3,'-LssKL9rSbi5lM4P08qh','-LssNAtsEhswj9KY05iM',2,'{"source":"cms/prod/world/-LssKL9rSbi5lM4P08qh/topic/-LssNAtsEhswj9KY05iM/1.Nanobots.png","target":"cms/prod/publish/3/world/-LssKL9rSbi5lM4P08qh/topics/-LssNAtsEhswj9KY05iM/1.Nanobots.png"}',3,0);
end while;
commit;
end ?
delimiter ;
调用存储过程
call batch_version_step(1000000);
自定义存储过程函数
生成随机字符串
drop procedure if exists randstr;
delimiter ?
create procedure randstr(num int(11))
begin
declare str char(255) default 'q1we23r4t5y6u7i8o9p0asdfghjklzxcvbnm';
declare nums int(11);
declare returnstr char(255); #SQL变量名不能和列名一样
declare i int(11) default 0;
select floor(truncate(rand(),1)*36)+1 into nums; #加1时为了防止产生随机数生成0的情况
select substring(str,nums-1,1) into returnstr;
while i < num do
select floor(truncate(rand(),1)*36)+1 into nums;
select concat(substring(str,nums,1),returnstr) into returnstr;
set i=i+1;
end while;
select returnstr as randstr;
end ?
delimiter ;
结合存储过程和函数
delimiter ?
drop procedure if EXISTS `batch_cms_world` ?
create procedure batch_cms_world(in max int)
begin
declare start int default 0;
declare i int default 0;
declare wid char(255) default "defaultWid";
declare wname char(255) default "defaultName";
set autocommit = 0;
while i < max do
set i = i + 1;
select substring(md5(rand()),1,10) into wid;
select substring(md5(rand()),1,10) into wname;
insert into t_dcms_world(create_by,create_time,del_flag,update_by,update_time,world_id,name,content,`index`)
values(0,0,0,0,0,wid,wname,'{"id":"-M18dFH6ZqcZHMfczl2V","name":"world4","backgroundImage":{"uid":"rc-upload-1582855337169-415","name":"背景图2.png","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vc49e50e97aa24219ef0ecd80b5c739e2.png","size":99802,"status":"done"},"backgroundStories":[{"image":{"uid":"rc-upload-1582855337169-419","name":"DIYStep1.png","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V0f11d5bb594dfda07a50a425112fd3f1.png","size":953207,"status":"done"},"audio":{"uid":"rc-upload-1582855337169-431","name":"DIYStep1.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V0f11d5bb594dfda07a50a425112fd3f1.mp3","size":164036,"status":"done"},"eye":{"uid":"rc-upload-1582855337169-437","name":"eye_found.json","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vc9b92fd632d91188d26a29b2e9296d96.json","size":94627,"status":"done"}},{"image":{"uid":"rc-upload-1582855337169-424","name":"DIYStep2.png","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Ve7decdca49d5d99c8c1808c419aec431.png","size":905934,"status":"done"},"audio":{"uid":"rc-upload-1582855337169-433","name":"DIYStep2.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Ve7decdca49d5d99c8c1808c419aec431.mp3","size":236072,"status":"done"},"eye":{"uid":"rc-upload-1582855337169-439","name":"eye_Line 007a.json","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V50b8ede2ac86f020295dc738a90a031b.json","size":11148,"status":"done"}},{"image":{"uid":"rc-upload-1582855337169-429","name":"DIYStep3.png","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V866fb3ea927ada69d31003dd7e11d6b6.png","size":1024966,"status":"done"},"audio":{"uid":"rc-upload-1582855337169-435","name":"DIYStep3.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V866fb3ea927ada69d31003dd7e11d6b6.mp3","size":266348,"status":"done"},"eye":{"uid":"rc-upload-1582855337169-441","name":"eye_found.json","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vc9b92fd632d91188d26a29b2e9296d96.json","size":94627,"status":"done"}}],"playListMusic":{"uid":"rc-upload-1582855337169-443","name":"背景音乐4.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vc970ddeb9c2a8a06898df145f9961b49.mp3","size":3797703,"status":"done"},"slideShowMusic":{"uid":"rc-upload-1582855337169-445","name":"背景音乐4.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vc970ddeb9c2a8a06898df145f9961b49.mp3","size":3797703,"status":"done"},"unlockRestriction":1,"unlockStories":[{"image":{"uid":"rc-upload-1582855337169-453","name":"DIYStep1.png","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V0f11d5bb594dfda07a50a425112fd3f1.png","size":953207,"status":"done"},"audio":{"uid":"rc-upload-1582855337169-459","name":"DIYStep1.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V0f11d5bb594dfda07a50a425112fd3f1.mp3","size":164036,"status":"done"},"eye":{"uid":"rc-upload-1582855337169-465","name":"eye_found.json","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vc9b92fd632d91188d26a29b2e9296d96.json","size":94627,"status":"done"}},{"image":{"uid":"rc-upload-1582855337169-455","name":"DIYStep2.png","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Ve7decdca49d5d99c8c1808c419aec431.png","size":905934,"status":"done"},"audio":{"uid":"rc-upload-1582855337169-461","name":"DIYStep2.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Ve7decdca49d5d99c8c1808c419aec431.mp3","size":236072,"status":"done"},"eye":{"uid":"rc-upload-1582855337169-467","name":"eye_Line 007a.json","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V50b8ede2ac86f020295dc738a90a031b.json","size":11148,"status":"done"}},{"image":{"uid":"rc-upload-1582855337169-457","name":"DIYStep3.png","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V866fb3ea927ada69d31003dd7e11d6b6.png","size":1024966,"status":"done"},"audio":{"uid":"rc-upload-1582855337169-463","name":"DIYStep3.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V866fb3ea927ada69d31003dd7e11d6b6.mp3","size":266348,"status":"done"},"eye":{"uid":"rc-upload-1582855337169-469","name":"eye_found.json","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vc9b92fd632d91188d26a29b2e9296d96.json","size":94627,"status":"done"}}],"unlockMemoryMusic":{"uid":"rc-upload-1582855337169-471","name":"背景音乐4.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vc970ddeb9c2a8a06898df145f9961b49.mp3","size":3797703,"status":"done"},"levelUpAudios":[{"audio":{"uid":"rc-upload-1582855337169-473","name":"audio_Answer_Dog.mp3","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2Vfe3916ca71c7c627034069ebd5b3a0b5.mp3","size":205375,"status":"done"},"eye":{"uid":"rc-upload-1582855337169-475","name":"video.json","url":"https://corbit-dev-868303926763-us-east-1.s3.amazonaws.com/cms/test/world/-M18dFH6ZqcZHMfczl2V421b47ffd946ca083b65cd668c6b17e6.json","size":23506,"status":"done"}}]}',i);
end while;
commit;
end ?
delimiter ;
注意：
- 表字段与数据库关键字冲突，字段名用 `` 包裹
