存储过程是一组具有特定功能的SQL语句集组成的可编程的函数,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数来调用执行。
存储过程是数据库管理中常用的技术之一,可以很方便的做些类似数据统计、数据分析等工作,SQL SERVER、ORACLE、MySQL都支持存储过程,但不同的数据库环境语法结构有所区别。
A、存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
B、存储过程允许标准组件式编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
C、存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
D、存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
E、存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
创建存储过程的语法:
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;
drop procedure sp_name;
不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程。
call sp_name[(传参)];
存储过程名称后面必须加括号,即使存储过程没有参数传递。
show procedure status;
显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等。show create procedure sp_name;
显示某一个存储过程的详细信息。
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','孙悟空','男');
根据提供的学号删除先删除学生的学生成绩,再删除学生。
create procedure deleteStudent(in sid CHAR(5))
BEGIN
delete from TScore where studentID=sid;
delete from TStudent where studentID=sid;
END;
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('table2019');
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', 'Table20180404215950');
查看恢复的结果select * from TStudent where studentID='00997';
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),'@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
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
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。