This shows you the differences between two versions of the page.
— |
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]] |