Movatterモバイル変換

WHENEVER

WHENEVER — specify the action to be taken when an SQL statement causes a specific class condition to be raised

Synopsis

WHENEVER { NOT FOUND | SQLERROR | SQLWARNING }action

Description

Define a behavior which is called on the special cases (Rows not found, SQL warnings or errors) in the result of SQL execution.

Parameters

SeeSection 34.8.1 for a description of the parameters.

Examples

EXEC SQL WHENEVER NOT FOUND CONTINUE;EXEC SQL WHENEVER NOT FOUND DO BREAK;EXEC SQL WHENEVER NOT FOUND DO CONTINUE;EXEC SQL WHENEVER SQLWARNING SQLPRINT;EXEC SQL WHENEVER SQLWARNING DO warn();EXEC SQL WHENEVER SQLERROR sqlprint;EXEC SQL WHENEVER SQLERROR CALL print2();EXEC SQL WHENEVER SQLERROR DO handle_error("select");EXEC SQL WHENEVER SQLERROR DO sqlnotice(NULL, NONO);EXEC SQL WHENEVER SQLERROR DO sqlprint();EXEC SQL WHENEVER SQLERROR GOTO error_label;EXEC SQL WHENEVER SQLERROR STOP;

A typical application is the use ofWHENEVER NOT FOUND BREAK to handle looping through result sets:

intmain(void){    EXEC SQL CONNECT TO testdb AS con1;    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;    EXEC SQL ALLOCATE DESCRIPTOR d;    EXEC SQL DECLARE cur CURSOR FOR SELECT current_database(), 'hoge', 256;    EXEC SQL OPEN cur;    /* when end of result set reached, break out of while loop */    EXEC SQL WHENEVER NOT FOUND DO BREAK;    while (1)    {        EXEC SQL FETCH NEXT FROM cur INTO SQL DESCRIPTOR d;        ...    }    EXEC SQL CLOSE cur;    EXEC SQL COMMIT;    EXEC SQL DEALLOCATE DESCRIPTOR d;    EXEC SQL DISCONNECT ALL;    return 0;}

Compatibility

WHENEVER is specified in the SQL standard, but most of the actions are PostgreSQL extensions.