Maison > base de données > tutoriel mysql > le corps du texte

MySQL 存储过程的异常处理

WBOY
Libérer: 2016-06-07 14:54:32
original
1318 Les gens l'ont consulté

无详细内容 异常处理 MySQL mysqlmysql delimiter $$mysqlmysql CREATE PROCEDURE myProc - (p_first_name VARCHAR(30), - p_last_name VARCHAR(30), - p_city VARCHAR(30), - p_description VARCHAR(30), - OUT p_sqlcode INT, - OUT p_status_message VARCH

异常处理 MySQL
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
    ->     (p_first_name          VARCHAR(30),
    ->       p_last_name           VARCHAR(30),
    ->       p_city                VARCHAR(30),
    ->       p_description         VARCHAR(30),
    ->       OUT p_sqlcode         INT,
    ->       OUT p_status_message  VARCHAR(100))
    -> BEGIN
    ->
    -> /* START Declare Conditions */
    ->
    ->   DECLARE duplicate_key CONDITION FOR 1062;
    ->   DECLARE foreign_key_violated CONDITION FOR 1216;
    ->
    -> /* END Declare Conditions */
    ->
    -> /* START Declare variables and cursors */
    ->
    ->      DECLARE l_manager_id       INT;
    ->
    ->      DECLARE csr_mgr_id CURSOR FOR
    ->       SELECT id
    ->         FROM employee
    ->        WHERE first_name=p_first_name
    ->              AND last_name=p_last_name;
    ->
    -> /* END Declare variables and cursors */
    ->
    -> /* START Declare Exception Handlers */
    ->
    ->   DECLARE CONTINUE HANDLER FOR duplicate_key
    ->     BEGIN
    ->       SET p_sqlcode=1052;
    ->       SET p_status_message='Duplicate key error';
    ->     END;
    ->
    ->   DECLARE CONTINUE HANDLER FOR foreign_key_violated
    ->     BEGIN
    ->       SET p_sqlcode=1216;
    ->       SET p_status_message='Foreign key violated';
    ->     END;
    ->
    ->   DECLARE CONTINUE HANDLER FOR not FOUND
    ->     BEGIN
    ->       SET p_sqlcode=1329;
    ->       SET p_status_message='No record found';
    ->     END;
    ->
    -> /* END Declare Exception Handlers */
    ->
    -> /* START Execution */
    ->
    ->   SET p_sqlcode=0;
    ->   OPEN csr_mgr_id;
    ->   FETCH csr_mgr_id INTO l_manager_id;
    ->
    ->   IF p_sqlcode<>0 THEN           /* Failed to get manager id*/
    ->     SET p_status_message=CONCAT(p_status_message,' when fetching manager id');
    ->   ELSE
    ->     INSERT INTO employee (first_name,id,city)
    ->     VALUES(p_first_name,l_manager_id,p_city);
    ->
    ->     IF p_sqlcode<>0 THEN     /* Failed to insert new department */
    ->       SET p_status_message=CONCAT(p_status_message,
    ->                            ' when inserting new department');
    ->     END IF;
    ->   END IF;
    ->
    ->   CLOSE csr_mgr_id;
    ->
    -> /* END Execution */
    ->
    -> END$$
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> delimiter ;
mysql> set @myCode = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @myMessage = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> call myProc('Jason','Martin','New City','New Description',@myCode,@myMessage);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select @myCode, @myMessage;
+---------+------------+
| @myCode | @myMessage |
+---------+------------+
| 0       | NULL       |
+---------+------------+
1 row in set (0.00 sec)

mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
Copier après la connexion
Étiquettes associées:
source:php.cn
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Tutoriels populaires
Plus>
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal
À propos de nous Clause de non-responsabilité Sitemap
Site Web PHP chinois:Formation PHP en ligne sur le bien-être public,Aidez les apprenants PHP à grandir rapidement!