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_bodyproc_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()BEGINselect a.sname as ‘姓名‘, AVG(b.mark) as ‘平均分‘ from TStudent a join TScore b on a.studentID=b.studentIDgroup by b.studentID order by ‘平均分‘ DESC limit 3;END;

B、帶輸入參數的預存程序建立
尋找指定班級的平均分最高的前三名學生

create procedure getMaxByClass(in classname VARCHAR(10))BEGINselect a.sname as ‘姓名‘, AVG(b.mark) as ‘平均分‘ from TStudent a join TScore b on a.studentID=b.studentID where a.class=classnamegroup by b.studentID order by ‘平均分‘ DESC limit 3;END

C、帶輸入參數和輸出參數的預存程序建立
根據輸入的班級，找到學號最大的學生，將學號儲存到輸出參數。

create procedure getMaxSIDByClass(IN classname VARCHAR(20), out maxid int)BEGINselect 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))BEGINinsert 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))BEGINdelete from TScore where studentID=sid;delete from TStudent where 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 restoreStudent(‘00997‘, ‘Table20180404215950‘);
查看恢複的結果
select * from TStudent where studentID=‘00997‘;

三、預存程序執行個體1、增加學生到資料庫表

create procedure addStudent(in num int)begindeclare 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()beginDECLARE 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

MySQL資料庫進階（四）——預存程序