declare-handler

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

declare-handler [2015/09/23 20:27]
declare-handler [2015/09/23 20:27] (current)
Line 1: Line 1:
 +====== DECLARE HANDLER Statement - PL/HQL ======
  
 +You can use DECLARE HANDLER statement to define one or more PL/HQL statements to execute when a condition occurs.
 +
 +**Syntax**:
 +
 +<code language=sql>​
 +DECLARE [CONTINUE | EXIT] HANDLER FOR 
 +  [SQLEXCEPTION | NOT FOUND | user_condition] code_block;
 +</​code>​
 +
 +**Description:​**
 +
 +| CONTINUE | When the handler completes, control is returned to PL/HQL statement following the statement that raised the condition |
 +| EXIT | After the handler completes, control is returned to the end of the block that declared the handler |
 +| //​code_block//​ | PL/HQL statement(s) to execute when the specified condition occurs |
 +
 +**Examples:​**
 +
 +<code language=sql>​
 +DECLARE name VARCHAR(100);​
 +DECLARE no_rows INT DEFAULT 0; 
 +
 +DECLARE CONTINUE HANDLER FOR NOT FOUND
 +  SET no_rows = 1;    ​
 +
 +OPEN cur FOR '​SELECT name FROM db.orders';​
 +
 +FETCH cur INTO name;
 +WHILE no_rows = 0 THEN  ​
 +  PRINT id;
 +  FETCH cur INTO name;
 +END WHILE;
 +CLOSE cur;
 +</​code>​
 +
 +**Compatibility:​** IBM DB2, Teradata and MySQL.
 +
 +**Version**: ​
 +  * PL/HQL 0.3.1 - User-defined condition supported
 +  * PL/HQL 0.01 - Introduced
 +
 +**See also:**
 +  * [[error-handling|Error Handling]]
 +  * [[declare-condition|DECLARE CONDITION]]
 +  * [[sqlcode|SQLCODE]]
 +  * [[sqlstate|SQLSTATE]]
 +  * [[get-diagnostics|GET DIAGNOSTICS]]
 +  * [[signal|SIGNAL]]
 +  * [[resignal|RESIGNAL]]