存储过程（二）

补充几个ERROR

mysql> use mmm;
ERROR 1049 (42000): Unknown database 'mmm'

mysql> delete from class where cid = 171;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
 (`cxc`.`student`, CONSTRAINT `cs1` FOREIGN KEY (`cid`) REFERENCES `class` (`cid`))

mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cid   | char(4)     | NO   | PRI | NULL    |       |
| cname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

mysql> select id from class;
ERROR 1054 (42S22): Unknown column 'id' in 'field list'

mysql> CREATE PROCEDURE proc1 ()
    -> BEGIN
    -> DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
    -> update student set sname="我也不知道我写的字符串长度都不够长，
反正我都写了，那就不如写的长一点，
这样我就能够保证最终出现我想要 的01004的状态编码了，希望如此吧" where sid=102;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> call proc1();
    -> //
ERROR 1406 (22001): Data too long for column 'sname' at row 1
mysql> desc student;
    -> //
+--------+-----------------+------+-----+---------+-------+
| Field  | Type            | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| sid    | char(11)        | NO   | PRI | NULL    |       |
| sname  | varchar(20)     | NO   |     | NULL    |       |
| cid    | char(4)         | NO   | MUL | NULL    |       |
| gender | enum('男','女') | YES  |     | NULL    |       |
| age    | int(11)         | YES  |     | NULL    |       |
+--------+-----------------+------+-----+---------+-------+
5 rows in set (0.05 sec)

mysql> show tables;
+---------------+
| Tables_in_cxc |
+---------------+
| account       |
| class         |
| student       |
+---------------+
3 rows in set (0.05 sec)

mysql> select * from student1;
ERROR 1146 (42S02): Table 'cxc.student1' doesn't exist

演示一个定义及处理的存储过程的使用

mysql> CREATE PROCEDURE proc1 ()
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
    -> select * from student1;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> call proc1();
Query OK, 0 rows affected (0.05 sec)

mysql> select @info;
+---------------+
| @info         |
+---------------+
| NO_SUCH_TABLE |
+---------------+
1 row in set (0.05 sec)