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 blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
