MySQL的存储过程的参数不能与操作的数据库表的字段相同,而且还是不区分大小写的。否则可能会造成很严重的后果,比如执行删除操作,它能把整个表的记录全删了。
例如表STUDENT结构如下:
mysql> DESC STUDENT;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| ID | int(11) unsigned | NO | PRI | NULL | auto_increment |
| NAME | varchar(50) | YES | | 0 | |
| AGE | int(10) unsigned | YES | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)
当前表内容:
mysql> SELECT * FROM STUDENT;
+----+------+------+
| ID | NAME | AGE |
+----+------+------+
| 1 | Jhon | 16 |
| 2 | Mike | 15 |
| 3 | Tom | 17 |
| 4 | Jack | 18 |
| 5 | Jim | 13 |
+----+------+------+
5 rows in set (0.01 sec)
存储过程procShow的功能是显示参数指定的学生记录:
DELIMITER $
CREATE PROCEDURE procShow(IN id INT(11) UNSIGNED)
BEGIN
SELECT * FROM STUDENT WHERE ID=id;
END$
DELIMITER ;
调用存储过程:
mysql> CALL procShow(1);
+----+------+------+
| ID | NAME | AGE |
+----+------+------+
| 1 | Jhon | 16 |
| 2 | Mike | 15 |
| 3 | Tom | 17 |
| 4 | Jack | 18 |
| 5 | Jim | 13 |
+----+------+------+
5 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
结果显示全部学生记录了,而不是显示指定ID的学生记录。
下面改下存储过程的参数名:
DROP PROCEDURE procShow;
DELIMITER $
CREATE PROCEDURE procShow(IN stuid INT(11) UNSIGNED)
BEGIN
SELECT * FROM STUDENT WHERE ID=stuid;
END$
DELIMITER ;
调用:
mysql> call procShow(1);
+----+------+------+
| ID | NAME | AGE |
+----+------+------+
| 1 | Jhon | 16 |
+----+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
结果正确。