MySQL从删库到跑路_高级（四）——存储过程

作者：天山老妖s

链接：http://blog.51cto.com/9291927

 

一、存储过程简介

1、存储过程简介

存储过程是一组具有特定功能的sql语句集组成的可编程的函数，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数来调用执行。

存储过程是数据库管理中常用的技术之一，可以很方便的做些类似数据统计、数据分析等工作，sql server、oracle、mysql都支持存储过程，但不同的数据库环境语法结构有所区别。

2、存储过程的优点

a、存储过程增强了sql语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

b、存储过程允许标准组件式编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的sql语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

c、存储过程能实现较快的执行速度。如果某一操作包含大量的transaction-sql代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储过程在系统表中的执行计划。而批处理的transaction-sql语句在每次运行时都要进行编译和优化，速度相对要慢一些。

d、存储过程能够减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果操作所涉及的transaction-sql语句被组织存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

e、存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

二、存储过程的使用

1、存储过程的创建

创建存储过程的语法：

create
    [definer = { user | current_user }]
　procedure sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ in | out | inout ] param_name typecharacteristic:    comment 'string'
  | language sql
  | [not] deterministic
  | { contains sql | no sql | reads sql data | modifies sql data }
  | sql security { definer | invoker }

routine_body:
　　valid sql routine statement[begin_label:] begin　　[statement_list]
　　　　end [end_label]

in输入参数：表示该参数的值必须在调用存储过程时指定，在存储过程中修改改参数的值不能被返回，为默认值。

out输出参数：该值可在存储过程内部被改变，并可返回。

inout输入输出参数：调用时指定，并且可被改变和返回。

a、无参数的存储过程创建

创建查找平均分最高的前三名同学的存储过程

create procedure getmax() begin select a.sname as '姓名', avg(b.mark) as '平均分' from tstudent a join tscore b on a.studentid = b.studentid group by b.studentid order by '平均分' desc limit 3; end;

b、带输入参数的存储过程创建

查找指定班级的平均分最高的前三名学生

create procedure getmaxbyclass(in classname varchar(10)) begin select a.sname as '姓名', avg(b.mark) as '平均分' from tstudent a join tscore b on a.studentid = b.studentid where a.class = classname group by b.studentid order by '平均分' desc limit 3; end

c、带输入参数和输出参数的存储过程创建

根据输入的班级，找到学号最大的学生，将学号存储到输出参数。

create procedure getmaxsidbyclass(in classname varchar(20), out maxid int) begin select max(studentid) into maxid from tstudent where class = classname; end;

2、存储过程的删除

drop procedure sp_name;

不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。

3、存储过程的调用

call sp_name[(传参)];

存储过程名称后面必须加括号，即使存储过程没有参数传递。

4、存储过程信息的查看

show procedure status;

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等。

show create procedure sp_name;

显示某一个存储过程的详情信息。

5、使用存储过程插入数据

create procedure inserttstudent(in sid char(5), name char(10), ssex char(1) ) begin insert into tstudent (studentid, sname, sex) values(sid, name, ssex); select * from tstudent where studentid = sid; end; call inserttstudnet('01020','孙悟空','男');

6、使用存储过程删除数据

根据提供的学号删除学生的学生成绩，再删除学生

create procedure deletestudent(in sid char(5) ) begin delete from tscore where studentid = sid; delete from tstudent wheres studentid = sid; end;

7、使用存储过程备份还原数据

a、使用存储过程备份数据

创建存储过程备份学生表，根据指定的表明创建新表，将tstudent表中的记录导入到新表。

create procedure backupstudent(in tablename char(10))beginset @sql1=concat('create table ',tablename,'
(
studentid varchar(5),
sname varchar(10),
sex char(1),
cardid varchar(20),
birthday datetime,
email varchar(20),
class varchar(10),
entertime datetime
)');prepare ct1 from @sql1;execute ct1;set @sql2=concat('insert into ', tablename, 
'(studentid,sname,sex,cardid,birthday,email,class,entertime)
select studentid,sname,sex,cardid,birthday,email,class,entertime from tstudent');prepare ct2 from @sql2;execute ct2;end;call backupstudent('table2019');

b、使用当前时间作为表名备份数据

创建存储过程，使用系统当前事件构造新的表名，备份tstudent表中的记录。

create procedure backupstudentbydatetime()begindeclare tablename varchar(20);set tablename = concat('table', replace(replace(replace(now(),' ',''),':',''),'-',''));set @sql1=concat('create table ',tablename,'
(
studentid varchar(5),
sname varchar(10),
sex char(1),
cardid varchar(20),
birthday datetime,
email varchar(20),
class varchar(10),
entertime datetime
)');prepare ct1 from @sql1;execute ct1;set @sql2=concat('insert into ', tablename, 
'(studentid,sname,sex,cardid,birthday,email,class,entertime)
select studentid,sname,sex,cardid,birthday,email,class,entertime from tstudent');prepare ct2 from @sql2;execute ct2;endcall backupstudentbydatetime();

c、使用存储过程还原数据

创建存储过程，根据输入的学号从指定的表还原学生记录，存储过程先删除指定的学号的tstudent表中学生记录，再从指定的表中插入该学生到tstudent表。

create procedure restorestudent(in sid varchar(5), in tablename varchar(20))beginset @sql1=concat('delete from tstudent where studentid=',sid);prepare ct1 from @sql1;execute ct1;set @sql2=concat('insert into tstudent
 (studentid,sname,sex,cardid,birthday,email,class,entertime)
  select studentid,sname,sex,cardid,birthday,email,class,entertime
  from ',tablename,' where studentid=',sid);prepare ct2 from @sql2;execute ct2;end;

修改某个学生的记录

update tstudent set sname = '孙悟空' where studentid = '00997';

从指定表中恢复数据

call restore student('00997','table20180404215950');

查看恢复的结果

select * from tstudent where studentid = '00997';

三、存储过程实例

1、增加学生到数据库表

create procedure addstudent(in num int)
begin
declare i int;
set i=1;delete from tstudent;while num>=i doinsert tstudent values (
       lpad(convert(i,char(5)),5,'0'),
       createname(),       if(ceil(rand()*10)%2=0,'男','女'),
       rpad(convert(ceil(rand()*1000000000000000000),char(18)),18,'0'),
       concat(convert(ceil(rand()*10)+1980,char(4)),'-',lpad(convert(ceil(rand()*12),char(2)),2,'0'),'-',lpad(convert(ceil(rand()*28),char(2)),2,'0')),
       concat(pinyin(sname),'@hotmail.com'),
       case ceil(rand()*3) when 1 then '网络与网站开发' when 2 then 'java' else 'net' end,
       now()
);
set i=i+1;
end while;select * from tstudent;
end

2、给学生添加成绩

create procedure fillscore()
begin
declare st_num int;
declare sb_num int;
declare i1 int;
declare i2 int;set i1=1;set i2=1;
delete from tscore;select count(*) into st_num from tstudent;select count(*) into sb_num from tsubject;while st_num>=i1 doset i2=1;while sb_num>=i2 doinsert tscore values
 (lpad(convert(i1,char(5)),5,'0'),lpad(convert(i2,char(4)),4,'0'),ceil(50+rand()*50));set i2=i2+1;
end while;set i1=i1+1;
end while;
end

 

喜欢的小伙伴们可以搜索我们个人的微信公众号“程序员的成长之路”点击关注或扫描下方二维码