SQL CONTROL STATEMENT
>>____assignment-statement________________________________________________>< |_CALL statement_______| |_CASE statement_______| |_compound-statement___| |_FOR statement________| |_GET DIAGNOSTICS stmt_| |_GOTO statement_______| |_IF statement_________| |_ITERATE statement____| |_LEAVE statement______| |_LOOP statement_______| |_REPEAT statement_____| |_RESIGNAL statement___| |_RETURN statement_____| |_SIGNAL statement_____| |_WHILE statement______|
(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)
Assignment
>>______________SET____assignment-clause_________________________________>< |_label:_| assignment-clause: >_____SQL-parameter-name__=_CURRENT SERVER_______________________________>< | |_SQL-variable-name__| |_CURRENT PACKAGESET____| | | |_CURRENT PACKAGE PATH__| | | <_,________________________________ | |_____SQL-parameter-name__=_expression__|_____________________________| | |_SQL-variable-name_| ||_NULL_____| | | <_,__________________ <_,__________ | |_(____SQL-parameter-name_|_)_=_(__expression__|__________________| |_SQL-variable-name_| ||_NULL_______| | |_VALUES____expression________| ||_NULL_________| | | <_,__________ | |_(_expression__|_)_| |_NULL_____|
CALL
>>__CALL__procedure-name__________________________________________________> >________________________________________________________________________>< |_(____________________________________________)_| | <_,______________________________ | |_____SQL-variable-name____________|_| |_SQL-parameter-name_________| |_expression_________________| |_NULL_______________________|
CASE
>__CASE_____________searched-case-statement-when-clause___________________> |_simple-case-statement-when-clause___| >____________________________________________END CASE_____________________> | <____________________________ | | |_ELSE____SQL-procedure-statement__;_|_| searched-case-statement-when-clause: <_________________________________________________________________ <______________________________| >____WHEN__search-condition__THEN____SQL-procedure-statement__;_|_|_______> simple-case-statement-when-clause: >__expression_____________________________________________________________> <___________________________________________________________ <____________________________ | >____WHEN__expression__THEN____SQL-procedure-statement__;_|_|_____________> </___________________________________________________________></____________________________>
Compound
_NOT ATOMIC_ >>______________BEGIN__|____________|_____________________________________> |_label:_| >_________________________________________________________________________> | <__________________________________ | |_____SQL-variable-declaration_____;_|_| |_condition-declaration____| |_return-codes-declaration_| >_________________________________________________________________________> | <______________________________ | |___DECLARE-CURSOR-statement__;_|_| <____________________________ >____________________________________SQL-procedure-statement__;_|_________> | <________________________ | |___handler-declaration__;_|_| >__END___________________________________________________________________>< |_label_| SQL-variable-declaration: >>__DECLARE_______________________________________________________________> <_ ,________________ _DEFAULT NULL__________ >__SQL-variable-name_|_data-type__|_______________________|______________>< | |_DEFAULT__constant_| | |_RESULT_SET_LOCATOR VARYING___________| condition-declaration: >>__DECLARE__condition-name__CONDITION__FOR___string-constant____________>< |_SQLSTATE___________| |_VALUE_| return-codes-declaration: _DEFAULT ‘00000’_________ >>__DECLARE__ _SQLSTATE__CHAR(5)__|_________________________|___________>< | |__DEFAULT__constant______| | | _DEFAULT 0_______________ | |_SQLCODE__INTEGER__|_ _____________________ _|__| |_DEFAULT__constant statement-declaration: >>__DECLARE__statement-name__STATEMENT__________________________________>< handler-declaration: >>__DECLARE____CONTINUE____HANDLER__FOR__________________________________> |_EXIT_____| >____specific-condition-value_______SQL-proceudre-statement_____________>< |_general-condition-value_| specific-condition-value: <_,_____________________________ _VALUE_ | >>_____SQLSTATE__|_______|__string__|___________________________________>< |_condition-name______________| general-condition-value: >>____SQLEXCEPTION______________________________________________________>< |_SQLWARNING___| |_NOT FOUND____|
FOR
>>__________FOR_____________________________________________________________> |_label:_| |_for-loop-name_AS_|| _WITHOUT HOLD_ | |_csr-name_CURSOR_|______________|_FOR_| |_WITH HOLD__| <______________________>_select-statement__DO__SQL-procedure-statement_|_;__END FOR_______________>< |_label:_|
GET DIAGNOSTICS
>>__GET DIAGNOSTICS__SQL-variable-name__=__ROW_COUNT_______________________><
GOTO
>>_____________GOTO__target-label__________________________________________>< |_label:_|
IF
<____________________________>>__IF__search-condition__THEN____SQL-procedure-statement__;_|_____________> <____________________________________________________>_______________________________________________________|__________________> | <__________________________ | |_ELSEIF__search-condition__THEN____SQL-procedure-statement__;_|_| >______________END IF_____________________________________________________>< | <____________________________ | |_ELSE____SQL-procedure-statement__;_|_|
ITERATE
>>_____________ITERATE__target-label______________________________________>< |_label:_|
LEAVE
>>_____________LEAVE____target-label______________________________________>< |_label:_|
LOOP
<____________________________>>______________LOOP____SQL-procedure-statement__;_|_END LOOP_____________>< |_label:_| |_label_|
REPEAT
<___________________________>>______________REPEAT___SQL-procedure-statement__;_|_____________________> |_label:_| >__UNTIL__search-condition__END REPEAT____________________________________>< |_label_|
RESIGNAL
>>______________RESIGNAL____________________________________________________> |_label:_| >___________________________________________________________________________> | _VALUE_ | |__SQLSTATE__|_______|__sqlstate-string-constant_________________________| | |_SQL-variable-name_______|||_signal-information_| | |_SQL-parameter-name____| | |_SQL-condition-name____________________________| signal-information: >>__SET MESSAGE TEXT _=__diagnostic-string-expression_______________________> RETURN >>______________RETURN_____________________________________________________> |_label:_| |_expression__________________________________| |_NULL________________________________________| |__________________________________fullselect_| | <___________________________ | |_WITH_common-table-expression_|_|
SIGNAL
>>______________SIGNAL______________________________________________________> |_label:_| >___________________________________________________________________________> | _VALUE_ | |__SQLSTATE__|_______|__sqlstate-string-constant_________________________| | |_SQL-variable-name_______|||_signal-information_| | |_SQL-parameter-name_____|| |_SQL-condition-name____________________________| signal-information: >>__SET MESSAGE TEXT _=__diagnostic-string-expression_______________________>
WHILE
>>__________WHILE_search-condition_DO_SQL-procedure-statement_;_END WHILE___> |_label:_| |_label_|
SQL Procedure statement
>>__SQL-control-statement__________________________________________________>< ALLOCATE CURSOR statement ALTER DATABASE statement ALTER FUNCTION statement (external scalar, external table, sourced, SQL scalar, or SQL table) ALTER INDEX statement ALTER PROEDURE statement (external, SQL-external,or SQL-native) ALTER SEQUENCE statement ALTER STOGROUP statement ALTER TABLE statement ALTER TABLESPACE statement ALTER TRUSTED CONTEXT statement ALTER VIEW statement ASSOCIATE LOCATORS statement CALL statement CLOSE statement COMMENT statement COMMIT statement CONNECT statement CREATE ALIAS statement CREATE DATABASE statement CREATE FUCNTION statement (external scalar, external table, sourced) CREATE GLOBAL TEMPORARY TABLE statement CREATE INDEX statement CREATE PROCEDURE statement (external) CREATE ROLE statement CREATE SEQUENCE statement CREATE STOGROUP statement CREATE TABLE statement CREATE TABLESPACE statement CREATE TRUSTED CONTEXT statement CREATE TYPE statement CREATE VIEW statement DECLARE CURSOR statement DECLARE GLOBAL TEMPROARY TABLE statement DELETE statement DROP statement EXCHANGE statement EXECUTE statement EXECUTE IMMEDIATE statement FETCH statement GET DIGANOSTICS statement GRANT statement INSERT statement LABEL statement LOCK TABLE statement MERGE statement OPEN statement PREPARE statement REFRESH TABLE statement RELEASE statement RELEASE SAVEPOINT statement RENAME statement REVOKE statement ROLLBACK statement SAVEPOINT statement SELECT INTO statement SET CONNECTION statement SET special-register statement TRUNCATE statement UPDATE statement VALUES INTO statement
</___________________________>
</___________________________>
</____________________________>
</__________________________></____________________________________________________></____________________________>
</______________________>
</_></________________________></______________________________></__________________________________>
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at [email protected]. Db2 is a registered trademark of the IBM Corporation.
