Puntos clave: Las sentencias de control SQL en Db2 12 son vitales para ejecutar comandos y gestionar procedimientos, mejorando las operaciones de la base de datos con diversas sentencias como CALL, CASE y LOOP.
- Facilita la gestión y programación de bases de datos dentro de Db2 para z/OS.
- Incluye sentencias de control como asignación, caso y diagnóstica.
INSTRUCCIÓN SQL CONTROL
>>____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______ǀ
Asignación
>>______________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_______________________ǀ
CASO
>__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__;_ǀ_ǀ_____________>
Compuesto
_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____ǀ
PARA
>>__________FOR_____________________________________________________________>
ǀ_label:_ǀ ǀ_for-loop-name_AS_ǀǀ _WITHOUT HOLD_ ǀ
ǀ_csr-name_CURSOR_ǀ______________ǀ_FOR_ǀ
ǀ_WITH HOLD__ǀ
<______________________>_select-statement__DO__SQL-procedure-statement_ǀ_;__END FOR_______________>< ǀ_label:_ǀ
Obtén DIAGNÓSTICO
>>__GET DIAGNOSTICS__SQL-variable-name__=__ROW_COUNT_______________________><
GOTO
>>_____________GOTO__target-label__________________________________________>< ǀ_label:_ǀ
SI
<____________________________>>__IF__search-condition__THEN____SQL-procedure-statement__;_ǀ_____________>
<____________________________________________________>_______________________________________________________ǀ__________________>
ǀ <__________________________ ǀ ǀ_ELSEIF__search-condition__THEN____SQL-procedure-statement__;_ǀ_ǀ >______________END IF_____________________________________________________>< ǀ <____________________________ ǀ
ǀ_ELSE____SQL-procedure-statement__;_ǀ_ǀ
ITERA
>>_____________ITERATE__target-label______________________________________>< ǀ_label:_ǀ
SALIR
>>_____________LEAVE____target-label______________________________________>< ǀ_label:_ǀ
LOOP
<____________________________>>______________LOOP____SQL-procedure-statement__;_ǀ_END LOOP_____________>< ǀ_label:_ǀ ǀ_label_ǀ
REPITE
<___________________________>>______________REPEAT___SQL-procedure-statement__;_ǀ_____________________>
ǀ_label:_ǀ
>__UNTIL__search-condition__END REPEAT____________________________________>< ǀ_label_ǀ
RESIGN
>>______________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_ǀ_ǀ
SEÑAL
>>______________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_______________________>
MIENTRAS
>>__________WHILE_search-condition_DO_SQL-procedure-statement_;_END WHILE___>
ǀ_label:_ǀ ǀ_label_ǀ
Instrucción SQL Procedure
>>__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
Aviso legal: Esta Guía de Referencia de Db2® 12 para z/OS fue desarrollada para ayudar a los usuarios en sus actividades diarias en la administración y programación en Db2 para z/OS. No son garantías expresadas ni implícitas con el contenido de esta guía. Queremos proporcionar una referencia de calidad y útil para los usuarios. Por favor, notifícanos cualquier error o error en esta guía de referencia en blogs@bmc.com. Db2 están una marca registrada de IBM Corporation.
Guía Db2
- Db2 12 para z/OS: Una introducción
- Funciones escalares en Db2 12
- Cómo leer diagramas sintácticos de IBM<sup>®</sup> para Db2 12
- Elementos del lenguaje en Db2 12
- Funciones agregadas en Db2 12
- Funciones de tabla y fila en Db2 12
- Consultas en Db2 12
- Comandos en Db2 12
- Sentencias de control SQL en Db2 12
- Explica las tablas en Db2 12
- DSNZPARM en Db2 12
- Parámetros de enlace en Db2 12
- Límites de Db2 en Db2 12
- Área de Comunicación SQL (SQLCA) en Db2 12
- REXX SQLCA en Db2 12
- Obtén diagnósticos en Db2 12
- IFCIDS en Db2 12
- Tablas de catálogo en Db2 12
- Excepciones en Db2 12
- Tablas del Repositorio de Esquemas XML en Db2 12
- Estadísticas actualizables del catálogo en Db2 12
- IBM Utilities en Db2 12
- Códigos de retorno positivo SQL en Db2 12
- Códigos de retorno de errores SQL en Db2 12
Estas publicaciones son mías y no representan necesariamente la posición, estrategias u opinión de BMC.