mysql数据库的存储过程_MySQL数据库高级（四）——存储过程

MySQL数据库高级(四)——存储过程

一、存储过程简介

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 type

characteristic:

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 insertTStudent('01020','孙悟空','男');

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

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

create procedure deleteStudent(in sid CHAR(5))

BEGIN

delete from TScore where studentID=sid;

delete from TStudent where studentID=sid;

END;

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

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

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

create procedure backupStudent(in tablename CHAR(10))

BEGIN

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;

END;

call backupStudent('table');

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

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

create procedure backupStudentByDateTime()

BEGIN

DECLARE 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;

END

call backupStudentByDateTime();

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

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

create procedure restoreStudent(in sid VARCHAR(5), in tablename VARCHAR(20))

BEGIN

set @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 restoreStudent('00997', 'Table0404215950');

查看恢复的结果

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 do

insert 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),'@'),

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 do

set i2=1;

while Sb_Num>=i2 do

insert 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