mySQL教程 第7章 預存程序和函數

標籤：mysql

第7章 預存程序和函數預存程序和儲存函數

MySQL的預存程序（stored procedure）和函數（stored function）統稱為stored routines。

1. MySQL預存程序和函數的區別

函數只能通過return語句返回單個值或者表對象。而預存程序不允許執行return，但是通過out參數返回多個值。 函數是可以嵌入在sql中使用的，可以在select中調用，而預存程序不行。

函數限制比較多，比如不能用暫存資料表，只能用表變數．還有一些函數都不可用等等．而預存程序的限制相對就比較少

一般來說，預存程序實現的功能要複雜一點，而函數的實現的功能針對性比較強。

當預存程序和函數被執行的時候，SQL Manager會到procedure cache中去取相應的查詢語句，如果在procedure cache裡沒有相應的查詢語句，SQL Manager就會對預存程序和函數進行編譯。

Procedure cache中儲存的是執行計畫 (execution plan) ，當編譯好之後就執行procedure cache中的execution plan，之後SQL SERVER會根據每個execution plan的實際情況來考慮是否要在cache中儲存這個plan，評判的標準一個是這個execution plan可能被使用的頻率；其次是產生這個plan的代價，也就是編譯的耗時。儲存在cache中的plan在下次執行時就不用再編譯了。

預存程序應用執行個體

1. 建立沒有參數的預存程序

建立預存程序取得最高考分

create PROCEDURE getMaxMark()

begin

select max(mark) 最高分 from `TScore`;

end

調用預存程序

call getMaxMark();

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;height:58px;padding-top:0px;" title="clip_image001" border="0" alt="clip_image001" src="http://img1.51cto.com/attachment/201302/22/400469_1361516322ybOf.png" width="219" height="58" />

2. 建立帶輸入參數的預存程序

以下預存程序能夠輸入的學生號查出該學生的資訊 參數Sid代表學號，IN代表輸入參數。

CREATE PROCEDURE getStudentByID(IN sid varchar(15))

BEGIN

select * from `TStudent` where studentID=sid;

END

調用該參數

call getStudentByID(‘00009‘)

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;height:49px;padding-top:0px;" title="clip_image003" border="0" alt="clip_image003" src="http://img1.51cto.com/attachment/201302/22/400469_1361516322cq4T.jpg" width="650" height="49" />

3. 建立帶輸入和輸出參數的預存程序

建立預存程序，能夠輸出指定課程的最高分

create PROCEDURE getMaxMarkBySubject(IN subName varchar(30),OUT maxMark int)

begin

select MAX(mark) into maxMark from `TScore` a join `TSubject` b on a.`subJectID`=b.`subJectID`

where b.`subJectName`=subName;

end

調用預存程序，將取出的最大值放到變數@maxScore

CALL getMaxMarkBySubject(‘電腦網路‘,@maxScore);

select @maxScore 電腦網路最高分;

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;height:85px;padding-top:0px;" title="clip_image004" border="0" alt="clip_image004" src="http://img1.51cto.com/attachment/201302/22/400469_1361516323i1HF.png" width="400" height="85" />

CALL getMaxMarkBySubject(‘資料結構‘,@maxScore);

select @maxScore 資料結構最高分;

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;height:91px;padding-top:0px;" title="clip_image005" border="0" alt="clip_image005" src="http://img1.51cto.com/attachment/201302/22/400469_1361516323Y5VX.png" width="400" height="91" />

4. 思考：建立預存程序，能夠根據指定學號刪除學生記錄

5. 刪除預存程序

drop PROCEDURE `getMaxMarkBySubject`

6. 查看建立的預存程序

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image006" border="0" alt="clip_image006" src="http://img1.51cto.com/attachment/201302/22/400469_1361516330Y7RA.png" height="181" />

建立儲存函數

7. 根據學產生績判斷是否優秀

以下函數能夠根據輸入範圍輸出成績是否優良差。

create FUNCTION getGrad1(score int)

RETURNS varchar(50)

BEGIN

return IF(score>90,‘成績優秀‘,IF(score<90 and score>80,‘成績良好‘,IF(score<80 and score>70,‘成績一般‘,IF(score<70 and score>60,‘成績及格‘,‘不及格‘))));

END

在查詢中使用定義的函數

select b.sname 姓名,mark 分數,getGrad1(mark) 成績層級 from `TScore` a join `TStudent` b on a.`StudentID`=b.`StudentID`

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image007" border="0" alt="clip_image007" src="http://img1.51cto.com/attachment/201302/22/400469_1361516330sX1T.png" height="154" />

8. 取漢字拼音首字母的函數

先建立擷取漢字拼音函數需要用到的表

DROP TABLE IF EXISTS `pinyin`;

CREATE TABLE `pinyin` (

`letter` char(1) NOT NULL,

`chinese` char(1) NOT NULL,

PRIMARY KEY (`letter`)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

INSERT INTO `pinyin` VALUES (‘A‘,‘驁‘),(‘B‘,‘簿‘),(‘C‘,‘錯‘),(‘D‘,‘鵽‘),(‘E‘,‘樲‘),(‘F‘,‘鰒‘),(‘G‘,‘腂‘),

(‘H‘,‘夻‘),(‘J‘,‘攈‘),(‘K‘,‘穒‘),(‘L‘,‘鱳‘),(‘M‘,‘旀‘),(‘N‘,‘桛‘),(‘O‘,‘漚‘),(‘P‘,‘曝‘),(‘Q‘,‘囕‘),(‘R‘,‘鶸‘),

(‘S‘,‘蜶‘),(‘T‘,‘籜‘),(‘W‘,‘鶩‘),(‘X‘,‘鑂‘),(‘Y‘,‘韻‘),(‘Z‘,‘咗‘);

建立擷取漢字拼音的函數

DROP FUNCTION IF EXISTS `PINYIN`

CREATE FUNCTION PINYIN(str CHAR(255))

RETURNS char(255)

BEGIN

DECLARE hexCode char(4);

DECLARE pinyin varchar(255);

DECLARE firstChar char(1);

DECLARE aChar char(1);

DECLARE pos int;

DECLARE strLength int;

SET pinyin = ‘‘;

SET strLength = CHAR_LENGTH(LTRIM(RTRIM(str)));

SET pos = 1;

SET @str = (CONVERT(str USING gbk));

WHILE pos <= strLength DO

SET @aChar = SUBSTRING(@str,pos,1);

SET hexCode = HEX(@aChar);

IF hexCode >= "8140" AND hexCode <= "FEA0" THEN

SELECT letter into firstChar

FROM pinyin

WHERE chinese >= @aChar

LIMIT 1;

ELSE

SET firstChar = @aChar;

END IF;

SET pinyin = CONCAT(pinyin,firstChar);

SET pos = pos + 1;

END WHILE;

RETURN UPPER(pinyin);

END

使用函數擷取使用者的姓名拼音首寫字母

select sname 姓名,pinyin(sname) 拼音首字母 from `TStudent`

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;height:300px;padding-top:0px;" title="clip_image008" border="0" alt="clip_image008" src="http://img1.51cto.com/attachment/201302/22/400469_1361516330FI39.png" width="650" height="300" />

9. 思考：更改使用者郵箱，將使用者的郵箱地址設定姓名的拼音縮寫

10. 數字轉漢字

以下函數能夠把阿拉伯數字轉化成財務中用到的漢字

create FUNCTION tohanzi (n_LowerMoney DECIMAL)

RETURNS VARCHAR(120)

BEGIN

Declare v_LowerStr VARCHAR(200) ;

Declare v_UpperPart VARCHAR(200) ;

Declare v_UpperStr VARCHAR(200) ;

Declare i_I int ;

set v_LowerStr = LTRIM(RTRIM(ROUND(n_LowerMoney,2 ) ) ) ;

set i_I = 1 ;

set v_UpperStr = ‘‘ ;

while ( i_I <=char_length(v_LowerStr ) ) do

set v_UpperPart = CONCAT( case substring(v_LowerStr,char_length(v_LowerStr) - i_I + 1,1 )

WHEN ‘.‘ THEN ‘元‘

WHEN ‘0‘ THEN ‘零‘

WHEN ‘1‘ THEN ‘壹‘

WHEN ‘2‘ THEN ‘貳‘

WHEN ‘3‘ THEN ‘三‘

WHEN ‘4‘ THEN ‘肆‘

WHEN ‘5‘ THEN ‘伍‘

WHEN ‘6‘ THEN ‘陸‘

WHEN ‘7‘ THEN ‘柒‘

WHEN ‘8‘ THEN ‘捌‘

WHEN ‘9‘ THEN ‘玖‘

END,

case i_I

WHEN 1 THEN ‘分‘

WHEN 2 THEN ‘角‘

WHEN 3 THEN ‘‘

WHEN 4 THEN ‘‘

WHEN 5 THEN ‘拾‘

WHEN 6 THEN ‘佰‘

WHEN 7 THEN ‘仟‘

WHEN 8 THEN ‘萬‘

WHEN 9 THEN ‘拾‘

WHEN 10 THEN ‘佰‘

WHEN 11 THEN ‘仟‘

WHEN 12 THEN ‘億‘

WHEN 13 THEN ‘拾‘

WHEN 14 THEN ‘佰‘

WHEN 15 THEN ‘仟‘

WHEN 16 THEN ‘萬‘

ELSE ‘‘

END );

set v_UpperStr =CONCAT( v_UpperPart , v_UpperStr) ;

set i_I = i_I + 1 ;

end while;

set v_UpperStr = REPLACE(v_UpperStr,‘零拾‘,‘零‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零佰‘,‘零‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零仟‘,‘零‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零零零‘,‘零‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零零‘,‘零‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零角零分‘,‘整‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零分‘,‘整‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零角‘,‘零‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零億零萬零元‘,‘億元‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘億零萬零元‘,‘億元‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零億零萬‘,‘億‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零萬零元‘,‘萬元‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘萬零元‘,‘萬元‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零億‘,‘億‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零萬‘,‘萬‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零元‘,‘元‘) ;

set v_UpperStr = REPLACE(v_UpperStr,‘零零‘,‘零‘) ;

if ( ‘元‘ = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1));

end if;

if ( ‘零‘ = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if ( ‘角‘ = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if ( ‘分‘ = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if (‘整‘ = substring(v_UpperStr,1,1)) then

set v_UpperStr = ‘零元整‘ ;

end if;

return v_UpperStr;

END

select tohanzi(20321)

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;height:87px;padding-top:0px;" title="clip_image009" border="0" alt="clip_image009" src="http://img1.51cto.com/attachment/201302/22/400469_1361516331F12d.png" width="400" height="87" />

11. 隨機產生姓名的函數

該函數，使用三個字串，存放使用者的姓名，使用隨機函數從姓名中隨機排列組合成人名。

create function create_name()

RETURNS varchar(3)

begin

DECLARE LN VARCHAR(300);

DECLARE MN VARCHAR(200);

DECLARE FN VARCHAR(200);

DECLARE LN_N INT;

DECLARE MN_N INT;

DECLARE FN_N INT;

SET LN=‘李王張劉陳楊黃趙周吳徐孫朱馬胡郭林何高梁鄭羅宋謝唐韓曹許鄧蕭馮曾程蔡彭潘袁於董餘蘇葉呂魏蔣田杜丁沈薑範江傅鐘盧汪戴崔任陸廖姚方金邱夏譚韋賈鄒石熊孟秦閻薛侯雷白龍段郝孔邵史毛常萬顧賴武康賀嚴尹錢施牛洪龔‘;

SET MN=‘偉剛勇春菊毅俊峰強軍平保東文輝力明永健世廣志瑗琰韻融園藝詠卿聰瀾純毓悅昭冰爽琬茗羽希寧欣飄育瀅馥新利筠柔竹靄凝曉歡霄楓芸菲寒伊亞宜可姬舒義興良海山仁波寧貴福生龍元全國勝學祥亮政謙亨奇固之嵐苑富順信子傑濤昌成康星光天達安岩中茂進林有堅和彪博誠先敬震振壯會思群豪清飛彬娜靜淑惠珠翠雅芝妍茜秋珊莎錦黛青倩婷姣婉嫻瑾穎露瑤怡嬋雁蓓紈儀荷丹蓉眉君琴蕊薇菁夢素偉剛勇毅俊峰強軍平保東文輝力明永健世廣志義興良海山仁波寧貴福生龍元全國勝學祥才發武新利清飛彬富順信子傑濤昌成康星光天達安岩中茂進林有堅和彪博誠先敬震振壯會思群豪心邦承樂紹功松善厚慶磊民友裕河哲江超浩亮政謙亨奇固之輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德行時泰盛雄琛鈞冠策騰楠榕風航弘‘;

SET FN=‘偉剛勇毅俊雲蓮真環雪榮愛妹霞香月鶯媛豔瑞凡佳嘉瓊勤珍貞莉桂娣葉璧才發武麗琳輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德河哲江超浩璐婭琦晶裕華慧巧美婕馨影荔枝思心邦承樂紹功松善厚慶磊民友玉萍紅娥玲芬芳燕彩蘭鳳潔梅秀娟英行時泰盛雄琛鈞冠策騰楠榕風航弘峰強軍平保東文輝力明永健世廣志義興良海山仁波寧貴福生龍元全國勝學祥才發武新利清飛彬富順信子傑濤昌成康星光天達安岩中茂進林有堅和彪博誠先敬震振壯會思群豪心邦承樂紹功松善厚慶磊民友裕河哲江超浩亮政謙亨奇固之輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德行時泰盛雄琛鈞冠策騰楠榕風航弘‘;

SET LN_N=CHAR_LENGTH(LN);

SET MN_N=CHAR_LENGTH(MN);

SET FN_N=CHAR_LENGTH(FN);

return Concat(substring(LN,ceil(rand()*LN_N),1),substring(MN,ceil(rand()*MN_N),1),substring(FN,ceil(rand()*FN_N),1));

end

調用函數產生姓名

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名 union

select create_name() 隨機姓名

查看結果

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;height:300px;padding-top:0px;" title="clip_image010" border="0" alt="clip_image010" src="http://img1.51cto.com/attachment/201302/22/400469_1361516331HJbf.png" width="177" height="300" />

12. 查看建立的函數

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image011" border="0" alt="clip_image011" src="http://img1.51cto.com/attachment/201302/22/400469_1361516465HWH2.png" height="212" />

13. 刪除儲存的函數

drop FUNCTION `tohanzi`

在預存程序和函數中使用變數、判斷和迴圈

MySQL中變數、判斷和迴圈只能在預存程序和儲存函數中使用。

14. 在預存程序中使用迴圈、變數

寫一個預存程序，能夠給TStudent表插入指定數量的學生記錄。***號隨機產生，姓名隨機產生，性別隨機，班級隨機產生。這其中用到了隨機函數，以及上面建立的產生姓名的函數。生日有隨機函數產生，範圍在1980-1989年，使用者的郵箱由使用者的姓名首寫字母組合而成。

如果已有預存程序，必須先刪除

drop procedure addStudent

建立的預存程序

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‘),

create_name(),

if(ceil(rand()*10)%2=0,‘男‘,‘女‘),

RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,‘0‘),

Concat(‘198‘,convert(ceil(rand()*10),char(1)),‘-‘,LPAD(convert(ceil(rand()*12),char(2)),2,‘0‘),‘-‘,LPAD(convert(ceil(rand()*30),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

調用預存程序

call addStudent(100)

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;height:353px;padding-top:0px;" title="clip_image013" border="0" alt="clip_image013" src="http://img1.51cto.com/attachment/201302/22/400469_13615164669pKU.jpg" width="650" height="353" />

15. 建立使用while的預存程序插入學產生績

插入了100名學生後，執行以下命令。

建立預存程序，能夠插入為學生插入分數。預存程序使用兩個迴圈，分數在50-100分之間，使用隨機數實現。

drop procedure fillSore

建立預存程序

create procedure fillSore()

begin

DECLARE St_Num INT;

DECLARE Sb_Num INT;

DECLARE i1 INT default 1;

DECLARE i2 INT default 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

調用預存程序

call fillSore()

查詢

select * from TScore

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image014" border="0" alt="clip_image014" src="http://img1.51cto.com/attachment/201302/22/400469_1361516466W2SV.png" height="348" />

16. 建立使用if的函數

If函數支援多層嵌套

create FUNCTION getGrad2(score int)

RETURNS varchar(50)

BEGIN

declare grad varchar(50);

if score>90 then

set grad=‘成績優秀‘;

else if score>80 then

set grad=‘成績良好‘;

else if score>70 then

set grad=‘成績一般‘;

else set grad=‘剛剛及格‘;

end if;

end if;

end if;

return grad;

END

使用函數查詢資料庫

select b.sname 姓名,mark 分數,getGrad2(mark) 成績層級

from `TScore` a join `TStudent` b on a.`StudentID`=b.`StudentID` limit 5

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image015" border="0" alt="clip_image015" src="http://img1.51cto.com/attachment/201302/22/400469_1361516466T08k.png" height="134" />

17. 建立使用case的函數

該函數根據學生的分數，給出評價。

create FUNCTION getGrad3(score int)

RETURNS varchar(50)

BEGIN

declare grad varchar(50);

declare mark int;

set mark=ceil(score/10);

case mark

when 9 then set grad=‘成績優秀‘;

when 8 then set grad=‘成績良好‘;

when 7 then set grad=‘成績一般‘;

else set grad=‘剛剛及格‘;

end case;

return grad;

END

測試函數

select b.sname 姓名,mark 分數,getGrad3(mark) 成績層級

from `TScore` a join `TStudent` b on a.`StudentID`=b.`StudentID` limit 5

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image016" border="0" alt="clip_image016" src="http://img1.51cto.com/attachment/201302/22/400469_1361516467RKtc.png" height="131" />

查看預存程序和儲存函數的語句

運行一下命令可以查看建立fillSore預存程序語句

18. 使用show create查看預存程序內容

show create procedure fillSore

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image017" border="0" alt="clip_image017" src="http://img1.51cto.com/attachment/201302/22/400469_1361516467a818.png" height="335" />

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image018" border="0" alt="clip_image018" src="http://img1.51cto.com/attachment/201302/22/400469_1361516468PNEj.png" height="369" />

19. 使用管理工具產生查看建立預存程序的語句

650) this.width=650;" style="background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image019" border="0" alt="clip_image019" src="http://img1.51cto.com/attachment/201302/22/400469_13615164680owV.png" height="516" />

650) this.width=650;" style="background-image:none;padding-left:0px;padding-right:0px;padding-top:0px;" title="clip_image021" border="0" alt="clip_image021" src="http://img1.51cto.com/attachment/201302/22/400469_1361516469gzhh.jpg" height="408" />

作業：

20. 統計男生和女生人數

21. 統計各班人數

22. 把重姓的學生找出來

23. 找出***號末尾是偶數的學生。

24. 查詢出生年月在1985-01-00到1988-01-00之間的學生。

25. 統計各班“電腦網路”平均分

26. 找出“電腦網路”不及格的男同學

 

本文出自 “ghost” 部落格，請務必保留此出處http://caizi.blog.51cto.com/5234706/1543139