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 blogs@bmc.com. 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 blogs@bmc.com.

BMC Brings the A-Game

BMC works with 86% of the Forbes Global 50 and customers and partners around the world to create their future. With our history of innovation, industry-leading automation, operations, and service management solutions, combined with unmatched flexibility, we help organizations free up time and space to become an Autonomous Digital Enterprise that conquers the opportunities ahead.
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.