这可能是你们大多数人面临的一个棘手问题。 以下是我在MySQL创建的储存功能:
DROP PROCEDURE IF EXISTS teamSize;
DELIMITER //
CREATE PROCEDURE teamSize(IN teamName VARCHAR(50), IN teamYear INT(4), OUT noOfPlayers INT)
BEGIN
IF teamName IN (SELECT teamName FROM team) THEN
SELECT COUNT(playerID) INTO noOfPlayers
FROM team
JOIN teamAllocation
ON teamAllocation.teamID = team.teamID
WHERE team.teamName = teamName
AND team.teamYear = teamYear;
ELSE
SELECT "That team does not exist in the database" AS "Error Message";
END IF;
END //
DELIMITER ;
我担任这一职务,因此,我可以计算某一年某个团队的规模。 团队名称和团队年按参数价值分类。 对于这一职能,如果在数据库中没有用户分类,我希望能显示用户友好的错误信息。
例如: 如我所期望的那样,在确实存在的团队中,我取得了以下成果:
mysql> CALL teamSize("U11 Orange", 2012, @noOfPlayers);
--------------
CALL teamSize("U11 Orange", 2012, @noOfPlayers)
--------------
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @noOfPlayers;
--------------
SELECT @noOfPlayers
--------------
+--------------+
| @noOfPlayers |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
mysql>
...... 但是,如果我知道某个团队中的某类人员在数据库中不存在,我会得到以下信息:
mysql> CALL teamSize("jkgefkrkvbey", 2012, @teamSize);
--------------
CALL teamSize("jkgefkrkvbey", 2012, @teamSize)
--------------
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @teamSize;
--------------
SELECT @teamSize
--------------
+-----------+
| @teamSize |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql>
What I am wanting for the case where a team name parsed into the function doesn t exist is something like the following:
+------------------------------------------+
| Error Message |
+------------------------------------------+
| That team does not exist in the database |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
如果有人对我如何能够实现上述目标有一些反馈或建议,将会受到高度赞赏。
提前感谢!