WHENEVER — 指定一个要在一个 SQL 语句导致发生一个特定类别的情况时要采取的动作
WHENEVER { NOT FOUND | SQLERROR | SQLWARNING } action
定义一个行为,它会在 SQL 执行结果的特殊情况(行未找到、SQL 警告或错误)中被调用。
参数描述见第 35.8.1 节。
EXEC SQL WHENEVER NOT FOUND CONTINUE; EXEC SQL WHENEVER NOT FOUND DO BREAK; 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;
一个典型的应用是使用WHENEVER NOT FOUND BREAK
来处理通过结果集的循环:
int main(void) { EXEC SQL CONNECT TO testdb AS con1; EXEC SQL ALLOCATE DESCRIPTOR d; EXEC SQL DECLARE cur CURSOR FOR SELECT current_database(), 'hoge', 256; EXEC SQL OPEN cur; /* 当到达结果集末尾时,跳出循环 */ 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; }
SQL 标准中说明了WHENEVER
,但是大部分动作是 PostgreSQL 扩展。