mySQL教程 第7章 存储过程和函数
第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();
2. 創建帶輸入參數的存儲過程
以下存儲過程能夠輸入的學生號查出該學生的信息 參數Sid代表學號，IN代表輸入參數。
CREATE PROCEDURE getStudentByID(IN sid varchar(15))
BEGIN
select * from `TStudent` where studentID=sid;
END
調用該參數
call getStudentByID('00009')
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 計算機網絡最高分;
CALL getMaxMarkBySubject('數據結構',@maxScore);
select @maxScore 數據結構最高分;
4. 思考：創建存儲過程，能夠根據指定學號刪除學生記錄
5. 刪除存儲過程
drop PROCEDURE `getMaxMarkBySubject`
6. 查看創建的存儲過程
創建存儲函數
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`
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`
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)
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() 隨機姓名
查看結果
12. 查看創建的函數
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)
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
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
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
查看存儲過程和存儲函數的語句
運行一下命令可以查看創建fillSore存儲過程語句
18. 使用show create查看存儲過程內容
show create procedure fillSore
19. 使用管理工具產生查看創建存儲過程的語句
作業：
20. 統計男生和女生人數
21. 統計各班人數
22. 把重姓的學生找出來
23. 找出身份證號末尾是偶數的學生。
24. 查詢出生年月在1985-01-00到1988-01-00之間的學生。
25. 統計各班“計算機網絡”平均分
26. 找出“計算機網絡”不及格的男同學
?
廣告
總結
以上是生活随笔為你收集整理的mySQL教程 第7章 存储过程和函数的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： tolua++
- 下一篇： HDOJ---2546 饭卡[DP0