MySQL存储过程简介
存储过程简介
https://www.cnblogs.com/mark-chan/p/5384139.html
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。
存储过程的优点：
(1).增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
MySQL的存储过程
存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
MySQL存储过程的创建
语法
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER // CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM students; END // DELIMITER ;
分隔符
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。
注意: DELIMITER 后面不是一定要加//， 完全可以用其他字符代替, 只要确保在END之后也是用同样字符就行,例如用$$代替：
而且END之后的;可以有，也可以没有
DELIMITER $$ CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM students; END // 可以写成END; 也可以写成END $$ DELIMITER ;
参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
- IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
- OUT:该值可在存储过程内部被改变，并可返回
- INOUT:调用时指定，并且可被改变和返回
过程体
过程体的开始与结束使用BEGIN与END进行标识。下面的测试实例本人都在mysql的5.5版本测试全部通过.
-
IN参数例子drop procedure if exists in_param; delimiter $$ create procedure in_param (in p_in int) begin select p_in ; # 输出1 set p_in = 2 ; # 改变值 select p_in ; # 输出2 end $$ delimiter ; #调用 set @p_in = 1; # 初始p_in的值 call in_param (@p_in); # 会输出1,然后输出2 select @p_in; # 输出1
以上可以看出，p_in虽然在存储过程中被修改，但并不影响初始@p_in的值，所以in类型相当于java中的值传递.OUT参数例子drop procedure if exists out_param; #存储过程out参数 delimiter // create procedure out_param(out p_out int) begin select p_out; # 第一次要输出输出p_out，没有设置值, 所以伪null, 这里千万不要以为要输出的是参数p_out, 因为它的类型是out，所以要改变值才行 set p_out=2; select p_out; # 第二次输出2, 因为改变了值, 就算这里之前把它设为set p_out=1, 也会输出1,因为只要改变就可以，而且影响全局的 end; // delimiter ; #调用 set @p_out=1; call out_param(@p_out); # 输出null， 然后2 select @p_out; # 输出2
以上可以看出，p_out在存储过程中被修改，同时影响了初始@p_out的值，所以out类型相当于java中的引用传递(地址传递).INOUT参数例子drop procedure if exists inout_param; #存储过程inout参数 delimiter // create procedure inout_param(inout p_inout int) begin select p_inout; #输出1 set p_inout = 2; select p_inout; #输出2 end // delimiter ; set @p_inout = 1; call inout_param(@p_inout); select @p_inout; #在存储过程中改变了值,所以这里输出是2
变量
语法：DECLARE 变量名1[,变量名2...] 数据类型 [默认值];
数据类型为MySQL的数据类型：
数值类型
日期和时间类型
字符串类型
变量赋值
语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]
用户变量
用户变量一般以@开头
注意：滥用用户变量会导致程序难以理解及管理
-
select 'hello world' into @x; select @x; # 输出hello world set @y='goodbye cruel world'; select @y; # 输出goodbye cruel world set @z=1+2+3; select @z; # 输出6
drop procedure if exists greetworld; create procedure greetworld() select concat(@greeting,' world'); set @greeting='hello'; call greetworld(); # 输出hello world
drop procedure if exists p1; drop procedure if exists p2; create procedure p1() set @last_proc='p1'; create procedure p2() select concat('last procedure was ',@last_proc); call p1(); call p2(); #输出last procedure was p1
- 注释
-
MySQL存储过程可使用两种风格的注释：
- #号：#，该风格一般用于单行注释
- C风格：/* code */ 一般用于多行注释
MySQL存储过程的调用
用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。
-
如上面 call inout_param(@p_inout); call p1(); call p2();MySQL存储过程的查询
- 假设目前存在一个数据库名字是test,里面有一个表plugin，数据模型如下图, (其中的数据是随便添加的,没有任何的实际意义, 仅作测试使用)
-
-
下面是个人在测试用的实例, 亲测都可以通过
use test; drop procedure if exists mysql_data; delimiter $$ create procedure mysql_data(inout param int) begin #select test.plugin.sender from test.plugin limit 2; # 有数据的话就输出 #select sender from test.plugin limit 2; # 比上面的写法简介点 select * from information_schema.columns where table_name = 'plugin' and column_name = 'sender' limit 2;
# 这种写法是要判断是否存在指定的列名， information_scheme是mysql的系统表，无需建立，读者可以在自己mysql上查看这个表的具体内容 end $$ delimiter; set @param = 45; call mysql_data(@param);
show create procedure test.mysql_data; #可以查看存储过程的详细信息use test; drop procedure if exists mysql_data; delimiter $$ create procedure mysql_data() begin if not exists (select * from information_schema.columns where table_name = 'plugin' and column_name = 'cx1') then alter table test.plugin add column cx1 varchar(100); # 在plugin表增加一列cx1 end if; if not exists (select * from information_schema.columns where table_name = 'plugin' and column_name = 'cx2') then alter table test.plugin add column cx2 varchar(100) after cx1; # 在plugin表的列名为cx1后面增加一列cx2 end if; if not exists (select * from plugin where id = '1') then insert into plugin(id, sender, cx2) values (1, 'sendertest', 'cx2test'); # 如果id为1的不存在,就增加一条数据,否则不增加(id是主键, duplicate key error) end if;
end $$
delimiter;
call mysql_data();use test; drop procedure if exists mysql_data; delimiter $$ create procedure mysql_data() begin if exists (select * from information_schema.columns where table_name = 'plugin' and column_name = 'link') then alter table test.plugin modify column link varchar(120); # 修改plugin表中link的字段长度 end if; if exists (select * from information_schema.columns where table_name = 'plugin' and column_name = 'hidden') then alter table test.plugin modify column hidden char(110); # 修改plugin表中hidden的字段类型或者长度 end if; if not exists (select * from information_schema.columns where table_name = 'plugin' and column_name = 'cx1') then alter table test.plugin add column cx1 varchar(100) after hidden; # 在plugin表的列名为cx1后面增加一列cx1 end if; if not exists (select * from information_schema.columns where table_name = 'plugin' and column_name = 'cx2') then alter table test.plugin change cx1 cx2 varchar(150); # 修改plugin表中cx1的名字为cx2, 同时更改长度 end if; if exists (select * from information_schema.columns where table_name = 'plugin' and column_name = 'cx2') then alter table test.plugin drop column cx2; # 删除plugin表中名字为cx2的列 end if; end $$ delimiter; call mysql_data();
drop procedure if exists mysql_data; delimiter $$ create procedure mysql_data(in p_in int(11), out p_out varchar(100)) begin declare pluginid int default 0; select p_out; # 输出null select id into pluginid from plugin where plugin.id = p_in; if pluginid <= 100 then set p_out = 'lees than 100'; elseif (pluginid <= 110 and pluginid > 100) then #这里elseif 必须写在一起,不能写成else if, 下同 set p_out = 'less than 110'; elseif (pluginid <= 120 and pluginid > 110) then set p_out = 'less than 120';
else
set p_out = 'unknow'; end if;
select p_out; # 输出less than 110 end $$ delimiter ; set @p_in = 101; set @p_out = 'init'; call mysql_data(@p_in, @p_out);drop procedure if exists mysql_data; delimiter $$ create procedure mysql_data() begin declare para varchar(10); set para = 'good'; # 假设数据库中值存在为good select id from plugin order by #case when status is not null then id end desc, #case when status <> para then id end asc, case when status = para then id end desc, case when status is null then id end asc; end $$ delimiter ; call mysql_data();
drop procedure if exists mysql_data; delimiter $$ create procedure mysql_data(out times char(50)) begin declare i int (11); declare types varchar(50); set i = 1; while i < 10 do if (i > 1 and i < 5) then set times = date_format(now(), '%Y/%m/%d-%H:%i:%s'); set types = 'aaa'; elseif ((i mod 2) = 4) then set times = date_format(now(), '%Y-%m-%d-%H:%i:%s'); set types = 'bbb'; else set times = date_format(now(), '%Y%m%d-%H:%i:%s'); set types = 'ccc'; end if; set i = i + 1; end while; end $$ delimiter ; set @times = ''; call mysql_data(@times); select @times; // 20180329-15:00:09
devices 表格的模型在这里就不给出了, 这里主要是学习存储过程的用法和mysql对string/int的处理, 如使用cast方法相互转换、拼接concat用法、循环while、判断if等的用法
drop procedure if exists insert_devices; delimiter $$ create procedure insert_devices() begin declare i int (11); declare ip int (11); declare deviceid varchar(50); declare tvserialnumber varchar(20); declare tvuniqueid varchar(255); declare tvip varchar(15); declare types varchar(50); declare firmId varchar(100); declare times varchar(30); set i = 1 ; while i < 150 do set deviceid = '6463g092105542aa2c5a21e'; set tvserialnumber = '6463g092105'; set tvuniqueid = '6463g092105542aa2c5a21e'; set deviceid = concat(cast(i as char), deviceid); set tvserialnumber = concat(cast(i as char), tvserialnumber); set tvuniqueid = concat(cast(i as char), tvuniqueid); set ip = i + 40; set tvip = concat('192.168.0.', ip); if ((i mod 2) = 2) then set types = '2016 MS'; elseif ((i mod 2) = 4) then set types = '2016 MS'; else set types = '2016 SS'; end if; if (i < 10) then set firmId = concat('TPM153', cast(i as char), 'HE.5.249.5.065'); elseif (i > 10 and i < 100) then # 注意这里一定要写成elseif, 千万不要写成else if, 要连在一起写 set firmId = concat('TPM1531HE.5.249.5.0', cast(i as char)); elseif (i > 100) then set firmId = concat('TPM1531HE.5.', cast(i as char), '.5.065'); end if; set times = date_format(now(), '%Y%m%d%H%i%s'); if not exists (select * from devices where id = deviceid) then insert into smartinstall.devices values ( deviceid, 'TV 28 Mar 2018 10:18:05', '49HFl7011T/12', tvserialnumber, concat(cast(i as char), cast(i as char)), '54:2a:a2:c5:a2:1e', tvip, '800000000000000f6921', types, 'offline', tvuniqueid, null, null, '{\"old\":\"Unknown\",\"new\":\"Unknown\"}', null,null,null,null,null, '27/03/2018:10:47', firmId, null, null, 'black', 'black', '2018/03/28-10:18:05', null, times, 'Upgrade', 'ST', null,null,null,null,null,null,null ) ; end if; set i = i + 1 ; end while ; end $$ delimiter ; call insert_devices();drop procedure if exists insert_device; delimiter $$ create procedure insert_device() begin declare i int (11); declare ip int (11); declare deviceid varchar(50); declare tvserialnumber varchar(20); declare tvmacaddress varchar(17); declare tvuniqueid varchar(255); declare tvip varchar(15); declare types varchar(50); declare firmId varchar(100); declare times varchar(30); set i = 11 ; set deviceid = '6463g092105542aa2c5a21e'; set tvserialnumber = '6463g092105'; set tvmacaddress = ':2a:a2:c5:a2:1e'; set tvuniqueid = '6463g092105542aa2c5a21e'; set deviceid = concat(cast(i as char), deviceid); set tvserialnumber = concat(cast(i as char), tvserialnumber); set tvmacaddress = concat(cast(i as char), tvmacaddress); set tvuniqueid = concat(cast(i as char), tvuniqueid); set ip = i + 40; set tvip = concat('192.168.0.', ip); if ((i mod 2) = 2) then set types = '2016 MS'; elseif ((i mod 2) = 4) then set types = '2016 MS'; else set types = '2016 SS'; end if; if (i < 10) then set firmId = concat('TPM153', cast(i as char), 'HE.5.249.5.065'); elseif (i > 10 and i < 100) then set firmId = concat('TPM1531HE.5.249.5.0', cast(i as char)); elseif (i > 100) then set firmId = concat('TPM1531HE.5.', cast(i as char), '.5.065'); end if; set times = date_format(now(), '%Y%m%d%H%i%s'); if not exists (select * from devices where id = deviceid) then insert into smartinstall.devices values ( deviceid, 'TV 28 Mar 2018 10:18:05', '49HFl7011T/12', tvserialnumber, concat(cast(i as char), cast(i as char)), '54:2a:a2:c5:a2:1e', tvip, '800000000000000f6921', types, 'offline', tvuniqueid, null, null, '{\"old\":\"Unknown\",\"new\":\"Unknown\"}', null,null,null,null,null, '27/03/2018:10:47', firmId, null, null, 'black', 'black', '2018/03/28-10:18:05', null, times, 'Upgrade', 'ST', null,null,null,null,null,null,null ) ; end if; end$$ delimiter ; create event if not exists e_insert_device on schedule every 3 second on completion preserve do call insert_device();
参考网址: https://blog.csdn.net/qq_26562641/article/details/53301407
-
-
-
MySQL存储过程的修改
ALTER PROCEDURE 更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。
-
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] characteristic: { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'
sp_name参数表示存储过程或函数的名称； characteristic参数指定存储函数的特性。 CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句； NO SQL表示子程序中不包含SQL语句； READS SQL DATA表示子程序中包含读数据的语句； MODIFIES SQL DATA表示子程序中包含写数据的语句。 SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。 COMMENT 'string'是注释信息。后续会接着写的....
努力做一个伪程序员, 不管道路有多么的艰苦......
