Mainframe Blog

SQL Control Statements in Db2 12

2 minute read
Stephen Watts

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.

Order now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.