MySQL 存儲(chǔ)函數(shù)可以引用表，但不能使用返回結(jié)果集的語(yǔ)句。因此我們可以說(shuō)不存在返回結(jié)果集的 SELECT 查詢。但我們可以使用 SELECT INTO 來(lái)擺脫這個(gè)問(wèn)題。例如，我們正在創(chuàng)建一個(gè)函數(shù)“Avg_marks”，它使用名為“Student_marks”的表中的動(dòng)態(tài)數(shù)據(jù)（具有以下記錄）來(lái)計(jì)算平均分?jǐn)?shù)。
mysql> Select * from Student_marks;
+-------+------+---------+---------+---------+
| Name | Math | English | Science | History |
+-------+------+---------+---------+---------+
| Raman | 95 | 89 | 85 | 81 |
| Rahul | 90 | 87 | 86 | 81 |
+-------+------+---------+---------+---------+
2 rows in set (0.00 sec)
mysql> DELIMITER //
mysql> Create Function Avg_marks(S_name Varchar(50))
-> RETURNS INT
-> DETERMINISTIC
-> BEGIN
-> DECLARE M1,M2,M3,M4,avg INT;
-> SELECT Math,English,Science,History INTO M1,M2,M3,M4 FROM Student_marks W
HERE Name = S_name;
-> SET avg = (M1+M2+M3+M4)/4;
-> RETURN avg;
-> END //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> Select Avg_marks('Raman') AS 'Raman_Marks';
+-------------+
| Raman_Marks |
+-------------+
| 88 |
+-------------+
1 row in set (0.07 sec)
mysql> Select Avg_marks('Rahul') AS 'Raman_Marks';
+-------------+
| Raman_Marks |
+-------------+
| 86 |
+-------------+
1 row in set (0.00 sec)
登錄后復(fù)制
以上就是我們?nèi)绾蝿?chuàng)建一個(gè)使用表中動(dòng)態(tài)數(shù)據(jù)的 MySQL 存儲(chǔ)函數(shù)？的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注www.92cms.cn其它相關(guān)文章！
