MySQL數(shù)據(jù)庫高級（四）——存儲過程

MySQL數(shù)據(jù)庫高級（四）——存儲過程

一、存儲過程簡介

1、存儲過程簡介

存儲過程是一組具有特定功能的SQL語句集組成的可編程的函數(shù)，經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫中，用戶可通過指定存儲過程的名字并給定參數(shù)來調(diào)用執(zhí)行。
存儲過程是數(shù)據(jù)庫管理中常用的技術(shù)之一，可以很方便的做些類似數(shù)據(jù)統(tǒng)計、數(shù)據(jù)分析等工作，SQL SERVER、ORACLE、MySQL都支持存儲過程，但不同的數(shù)據(jù)庫環(huán)境語法結(jié)構(gòu)有所區(qū)別。

2、存儲過程的優(yōu)點

A、存儲過程增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運算。
B、存儲過程允許標(biāo)準(zhǔn)組件式編程。存儲過程被創(chuàng)建后，可以在程序中被多次調(diào)用，而不必重新編寫該存儲過程的SQL語句。而且數(shù)據(jù)庫專業(yè)人員可以隨時對存儲過程進行修改，對應(yīng)用程序源代碼毫無影響。
C、存儲過程能實現(xiàn)較快的執(zhí)行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執(zhí)行，那么存儲過程要比批處理的執(zhí)行速度快很多。因為存儲過程是預(yù)編譯的。在首次運行一個存儲過程時查詢，優(yōu)化器對其進行分析優(yōu)化，并且給出最終被存儲在系統(tǒng)表中的執(zhí)行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優(yōu)化，速度相對要慢一些。
D、存儲過程能過減少網(wǎng)絡(luò)流量。針對同一個數(shù)據(jù)庫對象的操作（如查詢、修改），如果操作所涉及的Transaction-SQL語句被組織程存儲過程，那么當(dāng)在客戶計算機上調(diào)用該存儲過程時，網(wǎng)絡(luò)中傳送的只是該調(diào)用語句，從而大大增加了網(wǎng)絡(luò)流量并降低了網(wǎng)絡(luò)負載。
E、存儲過程可被作為一種安全機制來充分利用。系統(tǒng)管理員通過執(zhí)行某一存儲過程的權(quán)限進行限制，能夠?qū)崿F(xiàn)對相應(yīng)的數(shù)據(jù)的訪問權(quán)限的限制，避免了非授權(quán)用戶對數(shù)據(jù)的訪問，保證了數(shù)據(jù)的安全。

二、存儲過程的使用

1、存儲過程的創(chuàng)建

創(chuàng)建存儲過程的語法：

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輸入?yún)?shù)：表示該參數(shù)的值必須在調(diào)用存儲過程時指定，在存儲過程中修改該參數(shù)的值不能被返回，為默認值。
OUT輸出參數(shù)：該值可在存儲過程內(nèi)部被改變，并可返回。
INOUT輸入輸出參數(shù)：調(diào)用時指定，并且可被改變和返回。
A、無參數(shù)的存儲過程創(chuàng)建
創(chuàng)建查找平均分最高的前三名同學(xué)的存儲過程

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、帶輸入?yún)?shù)的存儲過程創(chuàng)建
查找指定班級的平均分最高的前三名學(xué)生

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、帶輸入?yún)?shù)和輸出參數(shù)的存儲過程創(chuàng)建
根據(jù)輸入的班級，找到學(xué)號最大的學(xué)生，將學(xué)號存儲到輸出參數(shù)。

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;
不能在一個存儲過程中刪除另一個存儲過程，只能調(diào)用另一個存儲過程。

3、存儲過程的調(diào)用

call sp_name[(傳參)];
存儲過程名稱后面必須加括號，即使存儲過程沒有參數(shù)傳遞。

4、存儲過程信息的查看

show procedure status;
顯示數(shù)據(jù)庫中所有存儲的存儲過程基本信息，包括所屬數(shù)據(jù)庫，存儲過程名稱，創(chuàng)建時間等。
show create procedure sp_name;
顯示某一個存儲過程的詳細信息。

5、使用存儲過程插入數(shù)據(jù)

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、使用存儲過程刪除數(shù)據(jù)

根據(jù)提供的學(xué)號刪除先刪除學(xué)生的學(xué)生成績，再刪除學(xué)生。

create procedure deleteStudent(in sid CHAR(5))
BEGIN
delete from TScore where studentID=sid;
delete from TStudent where studentID=sid;
END;

7、使用存儲過程備份還原數(shù)據(jù)

A、使用存儲過程備份數(shù)據(jù)
創(chuàng)建存儲過程備份學(xué)生表，根據(jù)指定的表名創(chuàng)建新表，將TStudent表中的記錄導(dǎo)入到新表。

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、使用當(dāng)前時間作為表名備份數(shù)據(jù)
創(chuàng)建存儲過程，使用系統(tǒng)當(dāng)前事件構(gòu)造新的表名，備份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、使用存儲過程還原數(shù)據(jù)
創(chuàng)建存儲過程，根據(jù)輸入的學(xué)號從指定的表還原學(xué)記錄，存儲過程先刪除指定的學(xué)號的TStudent表中學(xué)生記錄，再從指定的表中插入該學(xué)生到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;

修改某個學(xué)生的記錄
update TStudent set sname='孫悟空' where studentID='00997';
從指定表中恢復(fù)數(shù)據(jù)
call restoreStudent('00997', 'Table20180404215950');
查看恢復(fù)的結(jié)果
select * from TStudent where studentID='00997';

三、存儲過程實例

1、增加學(xué)生到數(shù)據(jù)庫表

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 '網(wǎng)絡(luò)與網(wǎng)站開發(fā)' when 2 then 'JAVA' ELSE 'NET' END,
       NOW()
);
set i=i+1;
end while;
select * from TStudent;
end

2、給學(xué)生添加成績

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