This chapter describes the new SQL enhancements.
This section contains the following topics:
SQL Procedural Language Support in Routines
Result Sets from SQL-invoked Procedures
Enhanced Diagnostics and Statistics
Enhanced ANSI/ISO SQL JOIN Support
Extended Use of query-expression
Pseudo Table SYSCA.SINGLETON_NULL
This new feature adds SQL as a programming language for SQL-invoked procedures and functions. Earlier releases of CA IDMS provided support for Cobol, PL/I, Assembler, and CA ADS.
The new SQL language elements, syntax, and terminology are fully compliant with the ISO standards, except where noted.
The SQL language for SQL routines includes syntax to perform the following:
The advantages of writing SQL routines in the SQL language include:
This feature introduces the following new and changed terminology for routines invoked through SQL:
Specifies a routine that is allowed to be invoked only from within SQL. An SQL-invoked routine can be defined in the SQL catalog as a procedure, function, or table procedure.
Specifies an SQL-invoked routine defined as a procedure in the SQL catalog.
Specifies an SQL-invoked routine defined as a function in the SQL catalog.
Specifies an SQL-invoked routine whose language attribute is SQL. Because table procedures cannot be written in the SQL language, an SQL routine is necessarily defined as a procedure or a function.
Specifies an SQL routine defined in the SQL catalog as a procedure with language attribute SQL.
Specifies an SQL routine defined in the SQL catalog as a function with language attribute SQL.
To define an SQL routine, use a CREATE FUNCTION or CREATE PROCEDURE statement and specify LANGUAGE SQL. In the same statement, also specify the SQL statements that make up the body of the routine. These are the statements that are executed when the routine is invoked.
After successful creation of an SQL routine, it can be invoked immediately. No additional steps, such as creating an access module are needed.
Routines written in the SQL language are implemented internally as CA ADS When an SQL routine is successfully created, it results in the creation of the following objects:
The following considerations apply when creating SQL routines:
set options command delimiter '++';
create procedure DEFJE01.TIF1
( TITLE varchar(10) with default
, P_LEFT integer
, P_RIGHT real
, RESULT varchar(30)
)
EXTERNAL NAME TIF1 LANGUAGE SQL
Label_200:
begin not atomic
/*
** Compare an integer value with a real value
*/
if (P_LEFT > P_RIGHT)
then set RESULT = 'p_left > p_right';
elseif (P_LEFT = P_RIGHT)
then set RESULT = 'p_left = p_right';
elseif (P_LEFT < P_RIGHT)
then set RESULT = 'p_left < p_right';
else set RESULT = 'p_left and/or p_right NULL !';
end if;
end
++
Note: For more information about changing the command delimiter, see SET OPTIONS COMMAND DELIMITER.
The new SQL statement components are described in this section.
The comment capabilities within SQL have been extended to support bracketed comments. This enables multiple statements to be designated as comments simply by enclosing them within comment delimiters.
Syntax
►►── /* ── comment-text ── */ ────────────────────────────────────────────────►◄
Parameters
Specifies the text to be designated as a comment.
Usage
Where bracketed comments can be used
Bracketed comments are only allowed in the routine body of an SQL routine. Outside of this context, they are not recognized.
Coding conventions
The bracket introducer '/*' and terminator '*/' strings cannot be split over two lines. They can be specified wherever a separator or space is allowed.
When defining an SQL routine using the command facility tools OCF, IDMSBCF, or the command console in CA IDMS Visual DBA, the comment introducer '/*' cannot be placed in column 1, because a '/*' is interpreted as an end of file on the input by the command processor.
The language-clause specifies the programming language of an SQL-invoked routine. This clause is required for SQL routines written in SQL. For others, it is documentational only. The language-clause can be used on a CREATE or ALTER PROCEDURE and on a CREATE or ALTER FUNCTION statement.
Syntax
►►───────── LANGUAGE ──────────────┬── ADS ─────────┬────────────────────────►◄ ├── ASSEMBLER ───┤ ├── COBOL ───────┤ ├── PLI ─────────┤ └── SQL ─────────┘
Parameters
Specifies that the SQL routine is written in the CA ADS language.
Specifies that the SQL routine is written in the assembler language.
Specifies that the SQL routine is written in the COBOL language.
Specifies that the SQL routine is written in the PL/I language.
Specifies that the SQL routine is written in the SQL language.
Note: The ability to specify ADS or ASSEMBLER as a language is a CA IDMS extension.
Usage
Specifying Language SQL
If LANGUAGE SQL is specified when creating a routine, the following routine attributes are established by default and any attempt to override them to other values will fail:
If no Language is Specified:
If the language is not specified when a routine is created, it is treated as null. There is no default.
Note: In the ISO standard, the default for LANGUAGE is SQL.
Example
The TLANG1 function defined in the schema USER01 returns the edited name, given the first and last names.
create function USER01.TLANG1
( P_FNAME char(20)
, P_LNAME char(20)
) returns varchar(41)
external name TLANG1 language SQL
return trim(P_FNAME) || ' ' || trim(P_LNAME);
select USER01.TLANG1('James ', 'Last ')
from SYSCA.SINGLETON_NULL;
*+
*+ USER_FUNC
*+ ---------
*+ James Last
Defines the SQL statements that can be included in the body of an SQL routine or in an SQL Control statement.
Syntax
Expansion of procedure-statement
►────┬── SQL-AM-mgmt-stmt ───────────┬────────────────────────────────────────►◄ ├── SQL-authorization-stmt ─────┤ ├── SQL-Control-stmt ───────────┤ ├── SQL-Diagnostics-stmt ───────┤ ├── SQL-DDL-stmt ───────────────┤ ├── SQL-DML-stmt ───────────────┤ ├── SQL-session-mgmt-stmt ──────┤ └── SQL-transaction-mgmt-stmt ──┘
Parameters
Specifies a statement from the Access Module Management Statements category.
Specifies a statement from the Authorization Statements category.
Specifies a statement from the Control Statements category.
Specifies a statement from the Diagnostics Statements category.
Specifies a statement from the Data Description Statements category.
Specifies a statement from the Data Manipulation Statements category.
Specifies a statement from the Session Management Statements category.
Note: The ability to include a RELEASE, SUSPEND, or RESUME statement in an SQL routine is a CA IDMS extension.
Specifies a statement from the Transaction Management Statements category.
Note: The ability to include a COMMIT or ROLLBACK statement in an SQL routine is a CA IDMS extension.
Usage
Grouping procedure statements into a single statement
Multiple procedure statements can be grouped together as a compound statement. A compound statement is a control statement and therefore is also a procedure statement.
More Information
Local variables are new entities introduced in support of SQL routines.
A local variable is a variable that is defined in an SQL routine. You use local variables to temporarily store and retrieve values as needed in the logic of the routine. Local variables are used for such things as:
Local variables can only be referenced within the body of the SQL routine in which they are defined.
Declaring Local Variables
A local variable is defined by a variable-declaration statement that is included in a compound statement within an SQL routine body. The declaration of a local variable consists of the specification of its name, data type, and optionally its initial value. For more information about declaring local variables, see Compound Statement.
Identifies a local variable declared in a compound statement.
Syntax
Expansion of local-variable
►─────────┬──────────────────────┬── local-variable-name ─────────────────────►◄ └── cmp-stmnt-label. ──┘
Parameters
Specifies the label of the compound statement that contains the definition of local-variable.
Identifies the local variable of an SQL routine.
Usage
Referencing Local Variables
A local variable can only be referenced from within the compound statement that contains its declaration or from within a compound statement contained in the compound statement that contains its declaration.
Avoiding Ambiguous References
The name of a local variable of an SQL routine can be the same as the name of another local variable, a routine parameter, a column, or another schema-defined entity such as a table. To avoid ambiguity when referencing these objects, qualification can be used as follows:
Resolving Ambiguous References
If a name is not qualified and more than one object has the specified name, CA IDMS uses the following precedence rules to resolve the ambiguous reference:
Note: In the ISO standard, an unqualified reference would be to the object with innermost scope.
Example
In the following SQL procedure, two local variables, FNAME and LNAME are defined. The references are qualified in the SELECT statement with the label of the compound statement that holds the definition of the local variables. The SET statement uses unqualified references.
set options command delimiter '++';
create procedure SQLROUT.LOCALVAR
( TITLE varchar(10) with default
, P_EMP_ID NUMERIC(4)
, P_NAME varchar(25)
)
external name LOCALVAR language SQL
L_MAIN: begin not atomic
/*
** Count number of employees with equal Firstname using REPEAT
*/
declare FNAME char(20);
declare LNAME varchar(20);
select EMP_FNAME, EMP_LNAME
into L_MAIN.FNAME, L_MAIN.LNAME
from DEMOEMPL.EMPLOYEE
where EMP_ID = P_EMP_ID;
set P_NAME = FNAME || LNAME;
end L_MAIN
++
*+ TITLE P_EMP_ID P_NAME
call SQLROUT.LOCALVAR('LOCALVAR',2010)++
*+
*+ ----- -------- -------------
*+ LOCALVAR 2010 Cora Parke
Routine parameters are new entities introduced in support of SQL routines.
A routine parameter is a parameter of an SQL routine. You use routine parameters to perform the following:
Routine parameters can only be referenced within the body of the SQL routine in which they are defined.
Defining Routine Parameters
A routine parameter is defined through a parameter-definition clause of the CREATE PROCEDURE or CREATE FUNCTION statements. The definition includes the specification of the name, the data type, and optional WITH DEFAULT attribute.
Note: For more information about defining routine parameters, see CREATE PROCEDURE and CREATE FUNCTION statements in the CA IDMS SQL Reference Guide.
Identifies a routine parameter of an SQL routine.
Syntax
Expansion of routine-parameter
►─┬──────────────────────────────────────────────┬── parameter-name ──────────►◄ └─┬───────────────────┬─────── routine-name. ──┘ └─ schema. ─────────┘
Parameters
Specifies the schema with which the SQL routine identified by routine-name is associated.
Specifies the name of the SQL routine in which the routine parameter identified by routine-parameter is defined.
Identifies a parameter of an SQL routine.
Usage
Referencing Routine Parameters
Routine parameters can only be referenced within the body of the SQL routine in which they are defined. A routine parameter is global to the SQL routine. It can be referenced anywhere in the body of the routine.
Avoiding Ambiguous References
The name of a routine parameter can be the same as the name of a local variable, a column, or another schema-defined entity such as a table. To avoid ambiguity when referencing these objects, qualification can be used as follows:
Resolving Ambiguous References
If a name is not qualified and more than one object has the specified name, CA IDMS uses the following precedence rules to resolve the ambiguous reference:
Note: In the ISO standard, an unqualified reference would be to the object with innermost scope.
Example
In the following SQL procedure, three routine parameters, TITLE, P_EMP_ID, and P_LAST_NAME are defined. The references are to P_EMP_ID and P_LAST_NAME in the SELECT statement are qualified. The SET statement uses an unqualified reference to TITLE.
.
set options command delimiter '++';
create procedure SQLROUT.GETLNAME
( TITLE varchar(10) with default
, P_EMP_ID NUMERIC(4)
, P_LAST_NAME varchar(25)
)
external name GETLNAME language SQL
L_MAIN: begin not atomic
select EMP_FNAME
into SQLROUT.GETLNAME.P_LAST_NAME
from DEMOEMPL.EMPLOYEE
where EMP_ID = GETLNAME.P_EMP_ID;
set TITLE = 'Success';
end L_MAIN
++
call SQLROUT.GETLNAME ('?',2010)++
*+
*+ TITL:EP_EMP_ID P_LAST_NAME
*+ ----- -------- -----------
*+ Success 2010 Cora
The expansion of value-expression has been enhanced to enable referencing parameters and local variables of SQL routines.
Syntax
►►─┬─────┬─┬─── . . . ──────────────────┬─────────────────────────────────────► ├─ + ─┤ ├── routine-parameter ───────┤ └─ - ─┘ └── local-variable ──────────┘ ►┬───────────────────────────────────────────────────────────────────────┬───►◄ │ ┌───────────────────────────────────────────────────────────────────┐ │ └─▼┬─ _ ──┬─┬───────┬─┬─── . . . ────────────────────────────────────┬┴─┘ ├─ / ──┤ ├─ + _ ─┤ ├─── routine-parameter ────────────────────────┤ ├─ + ──┤ └─ - ───┘ └─── local-variable ───────────────────────────┘ ├─ - ──┤ └─ ││ ─┘
Parameters
Specifies a parameter of an SQL routine to be used as a single operand in the value expression. For information about expanded routine-parameter syntax, see Expansion of Routine-parameter.
Specifies a local variable of an SQL routine to be used as a single operand in the value expression. For information about expanded local-variable syntax, see Expansion of Local-variable.
This section contains data description statements that have been enhanced in support of SQL routines.
The ALTER FUNCTION statement has been enhanced with the addition of the language-clause which is used to change the language of the function.
Syntax
►►─ ALTER FUNCTION ─ . . . ────────────────────────────────────────────────────► ►───┬───────────────────┬─────────────────────────────────────────────────────►◄ └─ language-clause ─┘
Parameters
Specifies the programming language of the function.
Usage
Changing the language of a function
A function with language SQL cannot be changed to any other language and a function whose language is not SQL cannot be changed to language SQL.
The ALTER PROCEDURE statement has been enhanced with the addition of the language-clause which is used to change the language of the procedure.
Syntax
►►─ ALTER PROCEDURE ─ . . . ───────────────────────────────────────────────────► ►───┬───────────────────┬─────────────────────────────────────────────────────►◄ └─ language-clause ─┘
Parameters
Specifies the programming language of the procedure.
Usage
Changing the language of a procedure
A procedure with language SQL cannot be changed to any other language and a procedure whose language is not SQL cannot be changed to language SQL.
The CREATE FUNCTION statement has been enhanced to enable the definition of functions written in the SQL language. The CREATE FUNCTION statement is a data description statement that stores the definition of a function in the SQL catalog. You can then invoke the function in any value-expression of an SQL statement except in the search condition of a table's check constraint. The function invocation results in CA IDMS calling the corresponding routine. Such routines can perform any action and return a single scalar value. Use the formal parameters of a function definition to specify the data type and format of the data to be passed to the function. Similarly, the data type of the return value is specified in the function definition.
Functions can be defined with a language of SQL, in which case, the routine actions written as SQL statements are specified and stored together with the function definition in the SQL catalog.
Syntax
►►─ CREATE FUNCTION ─ . . . ───────────────────────────────────────────────────► ►───┬───────────────────┬───┬─────────────────────────────┬────────────────────► └─ language-clause ─┘ └── PROTOCOL ───┬── IDMS ──┬──┘ └── ADS ───┘ ►───┬───────────────┬──────────────────────────────────────────────────────────► ├─ USER MODE ───┤ └─ SYSTEM MODE ─┘ ►───┬────────────────────────────────────────────────────────┬─────────────────► └─ TRANSACTION SHARING ───────────────┬─ ON ───────┬─────┘ ├─ OFF ──────┤ └─ DEFAULT ◄─┘ ►───┬───────────────────────────────────────────────────────────────────────┬─►◄ └┬──────────────────────────────────────────────┬ procedure-statement ──┘ │ ┌──────────────────┐ │ └ ADS COMPILE OPTION ─▼─ compile-option ─┴─ ; ─┘
Parameters
Specifies the programming language of the function. This clause is required for functions written in SQL. For others, it is documentational only. If the language is not specified, it is treated as null.
Specifies the protocol with which the function is invoked. This specification is required except with language SQL. If LANGUAGE SQL is specified, PROTOCOL must be ADS or the clause must not be specified.
Use IDMS for functions that are written in COBOL, PL/I, or Assembler.
Use ADS for functions that are written in CA ADS. The name of the dialog that is loaded and executed when the function is invoked is specified by the external-routine-name in the EXTERNAL NAME clause. ADS is the default if LANGUAGE SQL is specified.
Specifies that the function should execute as a user-mode application program within CA IDMS. This cannot be specified with language SQL or protocol ADS. For other languages and protocols, it is the default.
Specifies that the function should execute as a system-mode application program. SYSTEM MODE is the default if language is SQL or protocol is ADS.
To execute as SYSTEM MODE, the program must be one of the following:
Specifies whether transaction sharing should be enabled for database sessions started by the function. If transaction sharing is enabled for a function's database session, it shares the current transaction of the SQL session. If language SQL is specified, TRANSACTION SHARING must be ON or the clause must not be specified.
Specifies that transaction sharing should be enabled. ON is the default if language is SQL.
Specifies that transaction sharing should be disabled.
Specifies that the transaction sharing setting in effect when the function is invoked should be retained. DEFAULT is the default for languages other than SQL.
Specifies a CA ADS option to be used when compiling the dialog associated with an SQL function. The options that can be specified and the syntax to use are given in the CA ADS Reference Guide, Appendix D.2.6 Dialog-expression. Compile-option can be specified only if language is SQL.
Note: The ability to specify the ADS COMPILE OPTION clause is a CA IDMS extension.
Specifies the actions taken in the function. Procedure-statement is required if language is SQL. It cannot be specified otherwise.
Usage
Language SQL
If LANGUAGE SQL is specified, the following attribute settings are established by default and must not be overridden to a different value:
Functions whose language is SQL are implemented through an automatically generated CA ADS dialog whose name is external-routine-name.
An error while parsing procedure-statement or an error while compiling the associated CA ADS dialog causes termination of the CREATE FUNCTION statement with a warning instead of a statement error. This allows the erroneous procedure-statement syntax to be saved in the catalog for later correction using the DISPLAY FUNCTION command. The CA ADS dialog and associated access module are not created.
Specifying CA ADS Compile Options
If LANGUAGE SQL is specified, you can specify one or more compile options to be used when the associated dialog is compiled. Specifying compile options can be useful for debugging purposes to enable tracing and the use of online debugging facilities. Compile options can also be used to include additional work records and SQL tables which can be referenced in native CA ADS code included in the routine body.
Some useful compile options include:
Example
set options command delimiter '++';
drop function USER01.TCNTEQNAME++
commit++
create function USER01.TCNTEQNAME
( TITLE varchar(40) with default
, P_FNAME char(20)
, P_COUNT integer
, RESULT varchar(10)
) RETURNS varchar(20)
EXTERNAL NAME TCNTEQN LANGUAGE SQL
Label_700:
begin not atomic
/*
** Count number of employees with equal Firstname
*/
declare FNAME char(20);
declare LNAME varchar(20);
declare P_COUNT_SAV integer default 0;
declare EMP1 CURSOR FOR
Select EMP_FNAME, EMP_LNAME
From DEMOEMPL.EMPLOYEE
where EMP_FNAME = P_FNAME;
open EMP1;
fetch EMP1 into FNAME, LNAME;
fetching_loop:
loop
if (SQLSTATE < > '00000')
then leave fetching_loop;
end if;
set P_COUNT = P_COUNT + 1;
fetch EMP1 into FNAME, LNAME;
end loop fetching_loop;
set RESULT = SQLSTATE;
close EMP1;
if (P_COUNT < = P_COUNT_SAV)
then return null;
else return 'Res: ' || cast(P_COUNT as char(5));
end if;
end
++
The CREATE PROCEDURE statement has been enhanced to enable the definition of procedures written in the SQL language.
The CREATE PROCEDURE statement is a data description statement that stores the definition of a procedure in the SQL catalog. You can refer to the procedure in an SQL CALL statement or in an SQL SELECT statement just as you would a table procedure. These references result in CA IDMS calls to the corresponding routine. Such routines can perform any action, such as manipulating data stored in some other organization (for example, in a non SQL-defined database or in a set of VSAM files). You can also use them to implement business logic.
Procedures can be defined with a language of SQL. The routine actions, written as SQL statements, are specified and stored together with the procedure definition in the SQL catalog.
The formal parameters of a procedure definition can be used like columns of a table during a procedure invocation to pass values to and from the procedure.
Syntax
►►─ CREATE PROCEDURE ─ . . . ──────────────────────────────────────────────────► ►───┬───────────────────┬───┬─────────────────────────────┬────────────────────► └─ language-clause ─┘ └── PROTOCOL ───┬── IDMS ──┬──┘ └── ADS ───┘ ►───┬───────────────┬──────────────────────────────────────────────────────────► ├─ USER MODE ───┤ └─ SYSTEM MODE ─┘ ►───┬────────────────────────────────────────────────────────┬─────────────────► └─ TRANSACTION SHARING ───────────────┬─ ON ───────┬─────┘ ├─ OFF ──────┤ └─ DEFAULT ◄─┘ ►───┬───────────────────────────────────────────────────────────────────────┬─►◄ └┬──────────────────────────────────────────────┬ procedure-statement ──┘ │ ┌──────────────────┐ │ └ ADS COMPILE OPTION ─▼─ compile-option ─┴─ ; ─┘
Parameters
Specifies the programming language of the procedure. This clause is required for procedures written in SQL. For others, it is documentational only. If the language is not specified, it is treated as null.
Specifies the PROTOCOL with which the procedure is invoked. This specification is required except with language SQL. If LANGUAGE SQL is specified, PROTOCOL must be ADS or the clause must not be specified.
Use IDMS for procedures that are written in COBOL, PL/I, or Assembler.
Use ADS for procedures that are written in CA ADS. The name of the dialog that is loaded and executed when the procedure is invoked is specified by the external-routine-name in the EXTERNAL NAME clause. ADS is the default if LANGUAGE SQL is specified.
Specifies that the procedure should execute as a user-mode application program within CA IDMS. This cannot be specified with language SQL or protocol ADS. For other languages and protocols, it is the default.
Specifies that the procedure should execute as a system-mode application program. SYSTEM MODE is the default if language is SQL or protocol is ADS.
To execute as SYSTEM MODE, the program must be one of the following:
Specifies whether transaction sharing should be enabled for database sessions started by the procedure. If transaction sharing is enabled for a procedure's database session, it shares the current transaction of the SQL session. If language SQL is specified, TRANSACTION SHARING must be ON or the clause must not be specified.
Specifies that transaction sharing should be enabled. ON is the default if language is SQL.
Specifies that transaction sharing should be disabled.
Specifies that the transaction sharing setting in effect when the procedure is invoked should be retained. Default is the default for languages other than SQL.
Specifies a CA ADS option to be used when compiling the dialog associated with an SQL procedure. The options that can be specified and the syntax to use are given in the CA ADS Reference Guide, Appendix D.2.6 Dialog-expression. Compile-option can be specified only if language is SQL.
Note: The ability to specify the ADS COMPILE OPTION clause is a CA IDMS extension.
Specifies the actions taken in the procedure. Procedure-statement is required if language is SQL. It cannot be specified otherwise.
Usage
Language SQL
If LANGUAGE SQL is specified, the following attribute settings are established by default and must not be overridden to a different value:
Procedures whose language is SQL are implemented through an automatically generated CA ADS dialog whose name is external-routine-name.
An error while parsing procedure-statement or an error while compiling the associated CA ADS dialog causes the CREATE PROCEDURE statement to terminate with a warning instead of a statement error. This allows the erroneous procedure-statement syntax to be saved in the catalog for later correction using the DISPLAY PROCEDURE command. The CA ADS dialog and associated access module are not created.
Specifying CA ADS Compile Options
If LANGUAGE SQL is specified, you can specify one or more compile options to be used when the associated dialog is compiled. Specifying compile options can be useful for debugging purposes to enable tracing and the use of online debugging facilities. Compile options can also be used to include additional work records and SQL tables which can be referenced in native CA ADS code included in the routine body.
Some useful compile options include:
Example
The procedure USER01.TSELECT1 uses the given employee ID to retrieve the first and last name. It returns the edited name in the RESULT parameter.
create procedure USER01.TSELECT1
( TITLE varchar(10) with default
, P_EMP_ID numeric(4)
, RESULT varchar(20)
)
EXTERNAL NAME TSELECT1 LANGUAGE SQL
select trim(EMP_FNAME) || ' ' || trim(EMP_LNAME)
into RESULT
from DEMOEMPL.EMPLOYEE
where EMP_ID = P_EMP_ID
;
call user01.tselect1('TSIGNAL3', 1003);
*+
*+ TITLE P_EMP_ID RESULT
*+ ----- -------- ------
*+ TSIGNAL3 1003 Jim Baldwin
The DISPLAY/PUNCH FUNCTION statement has been enhanced to display the SQL statements that make up the body of a function written in SQL. The DISPLAY/PUNCH FUNCTION statement lets you display or punch a function. For functions with language SQL, the statement also displays the SQL routine body from the dictionary.
The DISPLAY/PUNCH PROCEDURE statement has been enhanced to display the SQL statements that make up the body of a procedure written in SQL. The DISPLAY PROCEDURE statement displays or punches the definition of a procedure. For procedures with language SQL, the statement also displays the SQL routine body from the dictionary.
The DROP FUNCTION statement has been enhanced to delete the additional components created in support of a function written in SQL. The DROP FUNCTION statement is a data description statement that deletes the definition of the referenced function from the dictionary. For functions with language SQL, the statement removes the SQL routine body from the dictionary and the associated entities: access module(AM), relational command module (RCM), ADS premap process code, and dialog load module.
If in the same SQL session the DROP of an SQL function is followed by a CREATE of an SQL routine with an external name identical to that of the dropped function, a COMMIT should follow the DROP to avoid deadlocks on the load module resources.
The DROP PROCEDURE statement has been enhanced to delete the additional components created in support of a procedure written in SQL. The DROP PROCEDURE statement is a data description statement that deletes the definition of the referenced procedure from the dictionary. For procedures with language SQL, the statement removes the SQL routine body from the dictionary and the associated entities: access module(AM), relational command module (RCM), ADS premap process code, and dialog load module.
If in the same SQL session the DROP of an SQL procedure is followed by a CREATE of an SQL routine with an external name identical to that of the dropped procedure, a COMMIT should follow the DROP to avoid deadlocks on the load module resources.
The DROP SCHEMA statement has been enhanced to delete the additional components created in support of SQL routines. The DROP SCHEMA statement is a data description statement that deletes a schema definition from the dictionary. The DROP SCHEMA statement is a CA IDMS extension of ANSI-standard SQL.
Usage
Effect of the CASCADE Parameter
When you specify CASCADE in a DROP SCHEMA statement, CA IDMS deletes the following:
This is a new category of CA IDMS SQL statements that allow you to define the flow of control in an SQL routine and assign values to routine parameters or local variables.
SQL Control Statements
|
Statement |
Purpose |
|---|---|
|
CALL |
Invokes a procedure or a table procedure. Note: The CALL statement has been available in earlier releases of CA IDMS, where it has been categorized as a DML-statement. |
|
CASE |
Determines the execution flow by the evaluation of one or more value-expressions. |
|
Compound |
Specifies a grouping of statements, with optional definitions of local variables, cursors, and handlers. |
|
EXEC ADS |
Starts a block of CA ADS code. |
|
IF |
Determines by evaluation of a search-condition, which block of statements are executed. |
|
ITERATE |
Begins a new iteration in a programmatic loop. |
|
LEAVE |
Exits a programmatic loop. |
|
LOOP |
Defines a programmatic loop. |
|
REPEAT |
Defines a programmatic loop with an end condition. |
|
RESIGNAL |
Raises an SQL exception in a handler. |
|
RETURN |
Exits an SQL-invoked routine or compound statement. |
|
SET Assignment |
Assigns a value to a routine parameter, local variable, or host variable. Note: This statement can also be embedded in any SQL client program. |
|
SIGNAL |
Raises an SQL exception. |
|
WHILE |
Defines a programmatic, conditional loop. |
The CALL statement has been enhanced to enable the invocation of SQL procedures.
Usage
Calling an SQL Procedure
An SQL procedure is an SQL-invoked procedure with language SQL. Any transaction started by this procedure is shared with the transaction of the caller. After returning from an SQL procedure, any session opened by the procedure is automatically released except for sessions that have result sets. Such sessions are released when their last result set has been processed and the associated received cursor has been closed.
The CASE statement selects different execution paths depending on the evaluation of one or more value-expressions.
Syntax
►►──── CASE ───┬── simple-case-when-clause ───┬────────────────────────────────► └── searched-case-when-clause ─┘ ►───┬───────────────────────────────────────────┬─ END CASE ───────────────────► │ ┌────────────────────────────┐ │ └─── ELSE ── ▼ ─ procedure-statement ─ ; ─┴─┘
Expansion of simple-case-when-clause
►►──── value-expression────────────────────────────────────────────────────────► ┌────────────────────────────────────────────────────────────────────┐ │ ┌────────────────────────────┐ │ ►── ▼ ─ WHEN ── value-expression ─ THEN ─ ▼ ─ procedure-statement ─ ; ─┴─┴────►◄
Expansion of searched-case-when-clause
┌────────────────────────────────────────────────────────────────────┐ │ ┌────────────────────────────┐ │ ►►─ ▼ ─ WHEN ── search-condition ─ THEN ─ ▼ ─ procedure-statement ─ ; ─┴─┴────►◄
Parameters
Simple Case:
Specifies the value expression whose outcome is compared to the outcomes of the value-expressions in the WHEN clauses.
Specifies a value expression whose outcome is compared to the outcome of the CASE value-expression. If the two values are equal, the group of statements specified in the corresponding THEN is executed.
Identifies the group of statements to be executed when the value expressions of the CASE and WHEN clauses are equal.
Searched Case:
Identifies the CASE as a searched case.
Specifies the search condition whose outcome, if true, results in the execution of the group of statements specified by the THEN clause.
Identifies the group of statements executed when the search-condition in the corresponding WHEN clause evaluates to true.
Specifies the group of statements to be executed when none of the THEN group of statements has been executed because of the evaluation and comparison of the value-expression's and search-condition's. This clause can be specified for both simple and searched case statements.
Usage
SQL Exceptions
If an ELSE clause is not specified and none of the THEN group of statements has been executed because of the outcome of evaluation of the value expressions and search conditions, an SQL exception is raised.
Examples
The first example demonstrates the use of a simple-case-when-clause.
set options command delimiter '++';
create function USER01.TCASE1
( TITLE varchar(40) with default
, P_EMP_ID unsigned numeric(4)
) RETURNS varchar(30)
external name TCASE1 language SQL
begin not atomic
/*
** Function selects an employee with the given EMP_ID and swaps
** the first_name value 'James' with 'Jim'.
** Returns a message text with the outcome of the execution
*/
declare MY_STATUS varchar(30);
declare LOC_FNAME char(20) default ' ';
select EMP_FNAME into LOC_FNAME
from DEMOEMPL.EMPLOYEE
where EMP_ID = P_EMP_ID;
case LOC_FNAME
when 'James'
then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'Jim'
where EMP_ID = P_EMP_ID;
set MY_STATUS = 'James->JIM';
when 'Jim'
then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'James'
where EMP_ID = P_EMP_ID;
set MY_STATUS = 'Jim->James';
when 'Thomas'
then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'Thomas'
where EMP_ID = P_EMP_ID;
set MY_STATUS = 'Dummy update';
else set MY_STATUS = 'No Changes';
end case;
return MY_STATUS;
end
++
select USER01.TCASE1('TCASE1', 1034)from SYSCA.SINGLETON_NULL++
*+
*+ USER_FUNC
*+ ---------
*+ Jim->James
The second example demonstrates the searched-case-when-clause. It is functionally equivalent with the example of simple-case-when-clause.
set options command delimiter '++';
create function USER01.TCASESR1
( TITLE varchar(40) with default
, P_EMP_ID unsigned numeric(4)
) RETURNS varchar(30)
external name TCASESR1 language SQL
begin not atomic
/*
** Function selects an employee with the given EMP_ID and
** does some conditional updates.
** Returns a message text with the outcome of the execution
*/
declare MY_STATUS varchar(30);
declare LOC_FNAME char(20) default ' ';
declare LOC_LNAME char(20) default ' ';
select EMP_FNAME, EMP_LNAME into LOC_FNAME, LOC_LNAME
from DEMOEMPL.EMPLOYEE where EMP_ID = P_EMP_ID;
case
when LOC_FNAME = 'James'
then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'Jim'
Where EMP_ID = P_EMP_ID;
set MY_STATUS = 'James->JIM';
when LOC_FNAME = 'Jim' and LOC_LNAME = 'Gallway'
then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'James'
Where EMP_ID = P_EMP_ID;
set MY_STATUS = 'Jim->James';
when LOC_LNAME = 'Van der Bilck'
then update DEMOEMPL.EMPLOYEE set EMP_LNAME = 'Vanderbilck'
Where EMP_ID = P_EMP_ID;
set MY_STATUS = 'Van der Bilck->Vanderbilck';
else set MY_STATUS = 'No Changes';
end case;
return MY_STATUS;
end
++
The Compound statement defines a block of related SQL statements and can include the definition of local variables, condition names, cursors, and condition handlers.
Syntax
►►──┬──────────────┬── BEGIN ──┬────────────────┬──────────────────────────────► └─ beg-label: ─┘ ├── ATOMIC ──────┤ └── NOT ATOMIC ◄─┘ ►───┬───────────────────────────────────────┬──────────────────────────────────► │ ┌──────────────────────────────────┐ │ └─ ▼ ─┬─ variable-declaration ────┬─;─┴─┘ └─ condition-declaration ───┘ ►───┬───────────────────────────────────────┬──────────────────────────────────► │ ┌──────────────────────────────────┐ │ └─ ▼ ─── cursor-declaration ────── ; ─┴─┘ ►─┬─────────────────────────────────┬──────────────────────────────────────────► │ ┌─────────────────────────────┐│ └─ ▼ ── handler-declaration ─ ; ─┴┘ ┌─────────────────────────┐ ►─── ▼ ─procedure-statement ─;─┴── END ───────┬─────────────┬─────────────────►◄ └─ end-label ─┘
Expansion of variable-declaration
┌───── , ──────┐ ►─ DECLARE ─ ▼ ── variable ─┴─ data-type ─┬─────────────────────┬─────────────►◄ └─ DEFAULT ─┬─ NULL ──┤ └─ const ─┘
Expansion of condition-declaration
►─ DECLARE ─ condition-name CONDITION FOR SQLSTATE ─┬─────────┬── const ──────►◄ └─ VALUE ─┘
Expansion of handler-declaration
┌──────────── , ────────────────────┐ ►─ DECLARE ─┬─ CONTINUE ─┬─ HANDLER FOR ─ ▼ ┬─ SQLEXCEPTION ─────────────────┬┴► ├─ EXIT ─────┤ ├─ SQLWARNING ───────────────────┤ └─ UNDO ─────┘ ├─ NOT FOUND ────────────────────┤ ├─ SQLSTATE value ─── 'sqlstate' ┤ └─ condition-name ───────────────┘ ►─── procedure-statement ─────────────────────────────────────────────────────►◄
Parameters
Specifies a 1- through 32-character SQL identifier that labels the compound statement. The value must be different from any other label used in the compound statement.
Specifies that an unhandled exception raised while executing the compound statement causes a rollback of the effects of the compound statement.
Specifies that an unhandled exception raised while executing the compound statement does not cause a rollback of the effects of the compound statement. This is the default.
Defines a local variable.
Specifies the name of the local variable. Variable must be a 1- through 32-character name that follows the conventions for SQL identifiers. The names of all local variables declared within a compound statement must be unique.
Specifies the initial value of the local variable.
NULL
Initializes the local variable to NULL.
const
Initializes the local variable to the value of const. Const must be a literal whose value is compatible for assignment to the local variable.
Note: If DEFAULT is not specified, the local variable is not initialized.
Defines a name for a condition. This name can be used in other statements to refer to the condition.
Specifies an optional keyword without semantic meaning.
Specifies the value of SQLSTATE that constitutes the condition. const is a 5-character string-literal that consists of only digits (0-9) and capital alphabetic characters (A-Z). const can not be '00000', the value of SQLSTATE for sucessful completion.
Defines a local cursor for use within the compound statement. For a description of this clause, see DECLARE CURSOR.
Defines an SQL procedure statement to be included in the compound statement. Procedure-statement may be any statement defined by Expansion of procedure-statement except a compound statement.
Defines a handler routine for SQL exception or completion conditions. A handler routine receives control when the execution of an SQL statement fails or terminates with a condition for which the handler has been defined. The three types of handlers (CONTINUE, EXIT, UNDO) and the conditions under which they are invoked are as follows:
After executing the handler action, a CONTINUE handler returns control to the statement following the one that caused the event. If this statement is contained in an IF, CASE, LOOP, WHILE, or REPEAT statement, control is returned to the statement following the IF, CASE, LOOP, WHILE, or REPEAT statement.
After executing the handler action, an EXIT handler returns control to the statement following the compound statement. If there is no statement following the compound statement, control is returned to the invoker of the routine.
Before executing the handler action, an UNDO handler will rollback the database changes caused by the execution of the compound statement that caused the handler to be activated. After the handler actions have been executed, control is returned to the statement following the compound statement. If there is no statement after the compound statement, control is returned to the invoker of the routine. An UNDO handler requires its defining compound statement to be ATOMIC.
Specifies that the handler is to be activated for all events except those of classes "Successful completion" (SQLSTATE = '00xxx'), "Completed with Warning" (SQLSTATE ='01xxx'), and "Completed with No Data" (SQLSTATE = '02xxx').
Specifies that the handler is to be activated for events of the class, "Completed with Warning" (SQLSTATE = '01xxx').
Specifies that the handler is to be activated for events of the class, "Completed with No Data" (SQLSTATE = '02xxx').
Specifies a value of SQLSTATE for which the handler is activated. 'Sqlstate' must be a 5-character string-literal that consists of only digits (0-9) and capital alphabetic characters (A-Z). 'Sqlstate' cannot be '00000', the value of SQLSTATE for successful completion.
Specifies the name of a condition for which the handler is activated. Condition-name must identify a condition declared in the compound statement.
Defines the SQL procedure statement that is to be executed when the handler routine is invoked.
Specifies an SQL identifier that labels the end of the compound statement. If specified, a beg-label must also have been specified and both labels must be equal.
Usage
Variables, Parameters, and Column Names
When ambiguity exists in referencing local variables, parameters and column names, qualification is required to resolve the ambiguity.
Note: For more information, see Expansion of Local-variable and Expansion of Routine-parameter.
Nesting of Compound Statement
A compound statement cannot contain other compound statements with the exception of handlers. A handler, which necessarily is contained in a compound statement, can have a compound statement as its procedure statement procedure-statement.
Handlers
When both a generic class handler (a handler for SQLEXCEPTION or SQLWARNING) and a specific handler cover the same event, the more specific handler is invoked when the event occurs.
Only one handler for a specific event can be defined.
Handlers cannot be defined with duplicate conditions.
If an SQL exception occurs in a compound statement for which there is no handler defined, control returns to the statement following the compound statement that caused the exception and an implicit RESIGNAL is executed. The exception is passed in the SQLSTATE. Database changes made by compound statements defined as ATOMIC will be rolled back before control returns.
Atomic Compound Statements
Compound statements defined as ATOMIC cannot contain the transaction management statements, COMMIT and ROLLBACK, or the session management statement, RELEASE.
Cursor state upon exiting from a compound statement
When execution of a compound statement ends, all cursors defined within the compound statement that are still open are automatically closed, except for returnable cursors. For more information about returnable cursors, see DECLARE CURSOR.
Example
The procedure USER01.TCOMP01 retrieves an employee for a given EMP_ID and returns a formatted name. An exit handler for NOT FOUND handles the NOT FOUND condition. An exit handler for SQLEXCEPTION handles generic database errors.
set options command delimiter '++';
create procedure USER01.TCOMP01
( P_ID numeric(4)
, P_NAME char(30)
, RESULT varchar(30)
)
external name TCOMP01 language SQL
Label_400:
/*
** Return formatted name of employee with given EMP_ID
*/
begin not atomic
declare L_FNAME char(50);
declare L_LNAME char(50);
declare exit handler for SQLEXCEPTION
label_8888:
begin not atomic
set RESULT = 'Unexpected SQLSTATE: ' || SQLSTATE;
set P_NAME = '** Error **';
end;
declare exit handler for NOT FOUND
set RESULT = 'No employee for EMP_ID: '
|| cast(P_ID as char(4));
set RESULT = ' ';
set P_NAME = ' ';
select EMP_FNAME, EMP_LNAME into L_FNAME, L_LNAME
from DEMOEMPL.EMPLOYEE
where EMP_ID = P_ID;
set P_NAME = trim(L_FNAME) || ' ' || trim(L_LNAME);
set RESULT = 'All OK';
End label_400
++
call user01.TCOMP01(1003);
*+
*+ P_ID P_NAME RESULT
*+ ---- ------ ------
*+ 1003 Jim Baldwin ALL OK
call user01.TCOMP01(9);
*+
*+ P_ID P_NAME RESULT
*+ ---- ------ ------
*+ 9 NO EMPLOYEE FOR EMP_ID: 9
call user01.TCOMP01(-2000);
*+
*+ P_ID P_NAME RESULT
*+ ---- ------ ------
*+ -2000 ** ERROR ** UNEXPECTED SQLSTATE: 22005
The EXEC ADS statement is a CA IDMS extension that enables inserting CA ADS code in SQL routines.
Syntax
┌─────────────────────┐ ►►─── EXEC ADS ─▼─ ads-process-stmnt ─┴─ ; ───────────────────────────────────►◄
Parameters
Specifies a CA ADS statement to be executed.
Usage
Allowable CA ADS statements
Only CA ADS statements that are allowed in a mapless dialog can be included in the body of an SQL routine.
Care should be taken in coding SQL transaction and session management statements because a ROLLBACK or COMMIT breaks the atomicity of a compound statement containing the EXEC ADS statement.
Referencing SQL-defined data
SQL-defined data can be referenced by respecting the mapping rules for identifiers and data types between SQL and CA ADS:
Example
The SQL function USER01.TEXECADS2 returns the LTERM ID of the LTERM on which the function is being executed.
set options command delimiter '++';
create function USER01.TEXECADS2
( P_DUMMY char(1)
) returns char(8)
external name TEXECAD2 language SQL
begin not atomic
/*
** SQL Function to return LTERM ID using EXEC ADS
*/
declare L_LTERMID char (8) default ' ';
exec ads
ACCEPT LTERM ID INTO L-LTERMID. ;
return L_LTERMID;
end
++
select USER01.TEXECADS2()
from SYSCA.SINGLETON_NULL;
*+
*+ USER_FUNC
*+ ---------
*+ VL71001
The IF statement selects different execution paths depending on the evaluation of one or more truth value expressions, given as SQL search conditions.
Syntax
┌────────────────────────────┐ ►►─── IF ── search-condition ── THEN ── ▼ ─ procedure-statement ─ ; ─┴─────────► ►─┬───────────────────────────────────────────────────────────────────────────┬► │ ┌──────────────────────────────────────────────────────────────────────┐ │ │ │ ┌────────────────────────────┐ │ │ └─ ▼ ─ ELSEIF ── search-condition ─ THEN ─ ▼ ─ procedure-statement ─ ; ─┴─┴─┘ ►─┬───────────────────────────────────────────┬─ END IF ──────────────────────►◄ │ ┌────────────────────────────┐ │ └─ ELSE ──── ▼ ─ procedure-statement ─ ; ─┴─┘
Parameters
Specifies the truth value expression to be evaluated. The outcome of the evaluation determines the execution path.
Specifies the statements to be executed if the immediately preceding search condition is true.
Specifies the truth value expression to be evaluated if the outcomes of all previously evaluated search conditions are false.
Specifies the statements to be executed if all search conditions are false.
Usage
If no alternative execution path is given, execution continues with the next statement outside the IF.
Example
set options command delimiter '++';
create procedure USER01.TIF1
( TITLE varchar(10) with default
, P_LEFT integer
, P_RIGHT real
, RESULT varchar(30)
)
EXTERNAL NAME TIF1 LANGUAGE SQL
Label_200:
begin not atomic
/*
** Compare an integer value with a real value
*/
if (P_LEFT > P_RIGHT)
then set RESULT = 'p_left > p_right';
elseif (P_LEFT = P_RIGHT)
then set RESULT = 'p_left = p_right';
elseif (P_LEFT < P_RIGHT)
then set RESULT = 'p_left < p_right';
else set RESULT = 'p_left and/or p_right NULL !';
end if;
end
++
call user01.TIF1('Test IF >', 4, 2)++
*+
*+ TITLE P_LEFT P_RIGHT RESULT
*+ ----- ------ ------- ------
*+ Test IF > 4 2.0000000E+00 P_LEFT > P_RIGHT
call user01.TIF1('Test IF <', 4, 9)++
*+
*+ TITLE P_LEFT P_RIGHT RESULT
*+ ----- ------ ------- ------
*+ Test IF < 4 9.0000000E+00 P_LEFT < P_RIGHT
call user01.TIF1('Test IF =', 2, 2)++
*+
*+ TITLE P_LEFT P_RIGHT RESULT
*+ ----- ------ ------- ------
*+ Test IF = 2 2.0000000E+00 P_LEFT = P_RIGHT
call user01.TIF1('Test IF ', 4)++
*+
*+ TITLE P_LEFT P_RIGHT RESULT
*+ ----- ------ ------- ------
*+ Test IF 4 <null> P_LEFT AND/OR P_RIGHT
NULL !
The ITERATE statement terminates execution of the current iteration of an iterated statement, such as LOOP, REPEAT or WHILE. If the iteration condition is true, a new iteration starts; otherwise, the statement following the iterated statement is executed.
Syntax
►►── ITERATE ── stmnt-label ───────────────────────────────────────────────────►
Parameters
Specifies the begin label of the iterated statement.
Usage
Statements that may be iterated
The labeled statement referred in the ITERATE must be a LOOP, REPEAT, or WHILE statement that contains the ITERATE statement.
Example
The procedure USER01.TITERATE1 retrieves all rows of the DEMOEMPL.EMPLOYEE table three times. The first loop uses a WHILE, the second uses a REPEAT, and the third uses a LOOP statement.
set options command delimiter '++';
create procedure USER01.TITERATE1
( TITLE varchar(10) with default
, P_FNAME char(20)
, P_COUNT integer
, RESULT varchar(10)
)
EXTERNAL NAME TITERATE LANGUAGE SQL
Label_600:
begin not atomic
declare FNAME char(20);
declare LNAME varchar(20);
declare EMP1 CURSOR FOR
Select EMP_FNAME, EMP_LNAME
From DEMOEMPL.EMPLOYEE;
/*
ITERATE in WHILE
*/
set RESULT = '?????';
open EMP1;
while_loop:
while (9 = 9)
do
fetch EMP1 into FNAME, LNAME;
if (SQLSTATE = '00000')
then
set P_COUNT = P_COUNT + 1;
iterate while_loop;
end if;
if (SQLSTATE = 'abcde')
then
iterate while_loop;
end if;
set RESULT = SQLSTATE;
leave while_loop;
end while while_loop;
close EMP1;
/*
ITERATE in REPEAT
*/
set RESULT = '?????';
open EMP1;
repeat_loop:
repeat
fetch EMP1 into FNAME, LNAME;
if (SQLSTATE = '00000')
then
set P_COUNT = P_COUNT + 1;
iterate repeat_loop;
end if;
set RESULT = SQLSTATE;
leave repeat_loop;
until (9 = 0)
end repeat repeat_loop;
close EMP1;
/*
ITERATE in LOOP
*/
set RESULT = '?????';
open EMP1;
loop_loop:
loop
fetch EMP1 into FNAME, LNAME;
if (SQLSTATE = '00000')
then
set P_COUNT = P_COUNT + 1;
iterate loop_loop;
end if;
set RESULT = SQLSTATE;
leave loop_loop;
end loop loop_loop;
close EMP1;
end
++
call USER01.TITERATE1('TITERATE1','James ',0,'U')++
*+
*+ TITLE P_FNAME P_COUNT RESULT
*+ ----- ------- ------- ------
*+ TITERATE1 James 165 02000
The LEAVE statement continues execution with the statement that immediately follows the specified labeled statement.
Syntax
►►── LEAVE ── stmnt-label ─────────────────────────────────────────────────────►
Parameters
Specifies the begin label of a statement that contains the LEAVE statement, and identifies the statement that needs to be left.
Usage
Statements that may be left: The labeled statement referred in the LEAVE must be a LOOP, REPEAT, WHILE or compound statement that contains the LEAVE statement.
Example
set options command delimiter '++';
create procedure USER01.TLEAVE1
( TITLE varchar(10) with default
, P_FNAME char(20)
, P_COUNT integer
, RESULT varchar(25)
)
EXTERNAL NAME TLEAVE1 LANGUAGE SQL
Label_700:
/*
** Count number of employees with equal Firstname
*/
begin not atomic
declare FNAME char(20);
declare LNAME varchar(20);
declare EMP1 CURSOR FOR
Select EMP_FNAME, EMP_LNAME
From DEMOEMPL.EMPLOYEE
where EMP_FNAME = P_FNAME;
open EMP1;
fetch EMP1 into FNAME, LNAME;
fetching_loop:
loop
if (SQLSTATE < > '00000')
then leave fetching_loop;
end if;
set P_COUNT = P_COUNT + 1;
fetch EMP1 into FNAME, LNAME;
end loop fetching_loop;
set RESULT = 'SQLSTATE: ' || SQLSTATE;
close EMP1;
end
++
call USER01.TLEAVE1('TLEAVE1','Martin',0)
*+
*+ TITLE P_FNAME P_COUNT RESULT
*+ ----- ------- ------- ------
*+ TLEAVE1 Martin 3 SQLSTATE: 02000
The LOOP statement repeats the execution of a statement or a group of statements.
Syntax
┌────────────────────────────┐ ►►─┬──────────────┬─── LOOP ─── ▼ ─ procedure-statement ─ ; ─┴── END LOOP ─────► └─ beg-label: ─┘ ►──┬─────────────┬────────────────────────────────────────────────────────────►◄ └─ end-label ─┘
Parameters
Specifies a 1- through 32-character SQL identifier that labels the LOOP statement. The value must be different from any other label used in the compound statement if the LOOP statement is contained in a compound statement.
Specifies a statement or group of statements that are repeatedly executed.
Specifies an SQL identifier that labels the end of the LOOP statement. If specified, a beg-label must also have been specified and both labels must be equal.
Usage
How execution of a LOOP statement ends
To end the repeated execution of the procedure-statements contained in a LOOP statement, a LEAVE statement can be used or an exit handler can be driven.
Example
See the example for the LEAVE statement. The procedure USER01.TLOOP1, is similar to USER01.TLEAVE1 but it uses an exit handler to terminate the LOOP.
set options command delimiter '++';
create procedure USER01.TLOOP1
( TITLE varchar(10) with default
, P_FNAME char(20)
, P_COUNT integer
, RESULT varchar(30)
)
EXTERNAL NAME TLOOP1 LANGUAGE SQL
Label_700:
/*
** Count number of employees with equal Firstname
*/
begin not atomic
declare FNAME char(20);
declare LNAME varchar(20);
declare EMP1 CURSOR FOR
Select EMP_FNAME, EMP_LNAME
From DEMOEMPL.EMPLOYEE
where EMP_FNAME = P_FNAME;
declare exit handler for SQLEXCEPTION, SQLWARNING, NOT FOUND
set RESULT = 'SQLSTATE: ' || SQLSTATE;
/*
** Count number of employees with equal Firstname
*/
open EMP1;
fetch EMP1 into FNAME, LNAME;
fetching_loop:
loop
set P_COUNT = P_COUNT + 1;
fetch EMP1 into FNAME, LNAME;
end loop fetching_loop;
end
++
call USER01.TLOOP1('TLOOP1','Martin ',0,'U')
*+
*+ TITLE P_FNAME P_COUNT
*+ ----- ------- -------
*+ TLOOP1 Martin 3
*+
*+ RESULT
*+ ------
*+ SQLSTATE: 02000
The REPEAT statement repeats the execution of a statement or a group of statements until a condition is met.
Syntax
┌────────────────────────────┐ ►►──┬──────────────┬──── REPEAT ──── ▼ ─ procedure-statement ─ ; ─┴────────────► └─ beg-label: ─┘ ►── UNTIL ── search-condition ── END REPEAT ──┬─────────────┬─────────────────►◄ └─ end-label ─┘
Parameters
Specifies a 1- through 32-character SQL identifier that labels the REPEAT statement. The value must be different from any other label used in the compound statement if the REPEAT statement is contained in a compound statement.
Specifies the statement or group of statements that are repeatedly executed.
Specifies the search condition that is evaluated after each iteration. If the outcome is true, the statement following the REPEAT statement is executed. Otherwise, a new iteration starts.
Specifies an SQL identifier that labels the end of the REPEAT statement. If specified, a beg-label must also have been specified and both labels must be equal.
Example
set options command delimiter '++';
create procedure USER01.TREPEAT1
( TITLE varchar(10) with default
, P_FNAME char(20)
, P_COUNT integer
, RESULT varchar(25)
)
EXTERNAL NAME TREPEAT1 LANGUAGE SQL
Label_700:
/*
** Count number of employees with equal First name using REPEAT
*/
begin not atomic
declare FNAME char(20);
declare LNAME varchar(20);
declare EMP1 CURSOR FOR
Select EMP_FNAME, EMP_LNAME
From DEMOEMPL.EMPLOYEE
where EMP_FNAME = P_FNAME;
open EMP1;
fetching_loop:
repeat
fetch EMP1 into FNAME, LNAME;
if (SQLSTATE = '00000')
then set P_COUNT = P_COUNT + 1;
end if;
until SQLSTATE < > '00000'
end repeat fetching_loop;
set RESULT = 'SQLSTATE: ' || SQLSTATE;
close EMP1;
end
++
call USER01.TREPEAT1('TREPEAT1','Martin',0,'U')
*+
*+ TITLE P_FNAME P_COUNT RESULT
*+ ----- ------- ------- ------
*+ TREPEAT1 Martin 3 SQLSTATE: 02000
The RESIGNAL statement resignals an SQL event or exception condition in a handler for the next higher level scope.
Syntax
►── RESIGNAL ─┬────────────────────────────────────────────────┬───────────────► ├── SQLSTATE ──┬─────────┬─── 'sqlstate' ────────┤ │ └─ VALUE ─┘ │ └─ condition-name ───────────────────────────────┘ ►──┬───────────────────────────────────────────────────────────┬──────────────►◄ └── SET MESSAGE_TEXT ── = ─┬────────────────────────────────│ └─ simple-value-specification ───┘
Parameters
Specifies the name of a condition whose SQLSTATE value is to be resignaled. Condition-name must identify a condition defined by a condition-declaration in a compound-statement containing the RESIGNAL statement. if more than one such condition-declaration has the specified condition-name, the one with the innermost scope is raised.
Specifies the value for SQLSTATE that is to be resignaled. 'Sqlstate' is a 5-character string-literal that consists of only digits (0-9) and capital alphabetic characters (A-Z). 'Sqlstate' cannot be '00000', the value of SQLSTATE for successful completion.
Specifies a character value to be added to the information item MESSAGE-TEXT. The data type of simple-value-specification must be a character.
Usage
Propagating the SQL Condition
The RESIGNAL statement can only be used in a handler to propagate an SQL condition to the scope that encloses the exception handler's scope. If the RESIGNAL is issued in a handler of a top level compound statement, control returns to the invoker of the SQL-invoked routine.
FLOW of CONTROL
If in the outer scope a handler exists for the raised exception or SQL event, the handler acquires control. After execution of the handler, control returns as with any other statement that causes a handler to activate.
SQLSTATE
There are no restrictions on the values that can be set for SQLSTATE, other than compliance with the syntactic rules for SQLSTATE values. We recommend using values in accordance with the classification of SQLSTATE values.
Note: For more information, see SQLSTATE Values in the "SQL Communication Area" appendix in the CA IDMS SQL Reference Guide.
MESSAGE_TEXT
This is an information item of CHAR type with no defined maximum length.
Example
set options command delimiter '++';
drop procedure USER01.RESIGNAL1++
commit++
create procedure USER01.RESIGNAL1
( TITLE varchar(10) with default
, RESULT varchar(120)
)
EXTERNAL NAME RESIGNA1 LANGUAGE SQL
Label_400:
/*
** Resignal show case
*/
begin not atomic
declare DEAD_LOCK condition for SQLSTATE '12000';
declare NOT_FOUND condition for SQLSTATE '02000';
declare exit handler for NOT FOUND
begin not atomic
set RESULT = RESULT || ' Not Found';
resignal SQLSTATE '38607';
end;
set RESULT = 'Signal trace:';
signal NOT_FOUND;
end label_400
++
call user01.resignal1('Signal')
*+ Status = -4 SQLSTATE = 38000 Messages follow:
*+ DB001075 C-4M321: Procedure RESIGNA1 exception 38607
The RETURN statement returns a value for an SQL function. As an extension to the ISO standard, a RETURN without parameters can also be used to exit a compound statement.
Syntax
►── RETURN ────┬────────────────────┬─────────────────────────────────────────►◄ ├─ NULL ─────────────┤ └─ value-expression ─┘
Parameters
Specifies that the function return value is NULL.
Specifies the function return value.
Usage
Compatible Data Types
The data type of the value-expression and the data type of the function return value named in the CREATE FUNCTION statement must be compatible for assignment.
Example
See CREATE FUNCTION.
The SET Assignment statement assigns values to parameters and variables used in SQL routines.
Syntax
►►── SET ──┬─ local-variable ─────┬ = ─┬─ value-expression ─┬─────────────────►◄ └─ routine-parameter ──┘ └─ NULL ─────────────┘
Parameters
Identifies the local variable that is the target of the SET assignment statement. local-variable must be the name of a local variable defined within the compound statement containing SET statement.
Identifies the SQL routine parameter that is the target of the SET assignment statement. Routine-parameter must be the name of a parameter of the routine containing the SET assignment statement.
Specifies the value to be assigned to the target of the SET assignment statement.
Specifies that the null value is to be assigned to the target of the SET assignment statement.
Usage
Valid assignments
The rules for assignment are provided in Comparison, Assignment, Arithmetic, and Concatenation Operations in the "Data Types and Null Values" chapter in the CA IDMS SQL Reference Guide.
Example
The procedure TSET3 creates a combined, edited name from a given first and last name. If the first or last name is null, or if the length of the last name is 0, the null value is returned for the edited name.
set options command delimiter '++';
create procedure SQLROUT.TSET3
( P_FNAME varchar(20)
, P_LNAME varchar(20)
, P _NAME varchar(41)
)
EXTERNAL NAME TSET3 LANGUAGE SQL
/*
** Return an edited name from the given Firstname and Lastname
*/
if (LENGTH(P_LNAME) <= 0)
then set P_NAME = null;
else set P_NAME = trim(P_FNAME) || ' ' || trim(P_NLNAME) ;
end if
++
set options command delimiter default++
call SQLROUT.TSET3('James ', 'Last ');
*+
*+ P_FNAME P_LNAME
*+ ------- -------
*+ James Last
*+
*+ P_NAME
*+ ------
*+ James Last
call SQLROUT.TSET3('James ', '');
*+
*+ P_FNAME P_LNAME
*+ ------- -------
*+ James
*+
*+ P_NAME
*+ ------
*+ <null>
The SIGNAL statement raises and signals an SQL event or exception condition.
Syntax
►── SIGNAL ──┬── SQLSTATE ──┬─────────┬───── 'sqlstate' ──────┬───────────────► │ └─ VALUE ─┘ │ └─ condition-name ───────────────────────────────┘ ►──┬───────────────────────────────────────────────────────────┬──────────────►◄ └── SET MESSAGE_TEXT ── = ─┬────────────────────────────────│ └─ simple-value-specification ───┘
Parameters
Specifies the name of a condition whose SQLSTATE value is to be signaled. Condition-name must identify a condition defined by a condition declaration in a compound statement containing the SIGNAL statement. If more than one such condition declaration has the specified condition name, the one with the innermost scope is raised.
Specifies the value for SQLSTATE that is to be signaled. 'sqlstate' is a 5-character string-literal value that consists of only digits (0-9) and capital alphabetic characters (A-Z). 'Sqlstate' cannot be '00000', the value of SQLSTATE for successful completion.
Specifies a character value to be added to the information item MESSAGE-TEXT. The data type of simple-value-specification must be a character.
Usage
FLOW of CONTROL
If a handler exists for the raised exception or SQL event, the handler acquires control. After execution of the handler, control returns as with any other statement that causes activation of a handler.
If no handler is activated, control goes to the end of the compound statement that contains the signal. If the signal is not in a compound statement of an exit handler, control returns to the invoker of the SQL routine. Otherwise, it returns to the statement after the SIGNAL statement, just as if a continue handler had been activated.
SQLSTATE
There are no restrictions on the values that can be set for SQLSTATE, other than compliance with the syntactic rules for SQLSTATE values. We recommend that values are used in accordance with the classification of SQLSTATE values. See SQLSTATE Values in the "SQL Communication Area" appendix in the CA IDMS SQL Reference Guide.
MESSAGE_TEXT
This is an information item of CHAR type with no defined maximum length.
Example
set options command delimiter '++';
create procedure USER01.TSIGNAL5
( TITLE varchar(10) with default
, RESULT varchar(120)
)
EXTERNAL NAME TSIGNAL5 LANGUAGE SQL
Label_400:
/*
** Trace execution of consecutive signal statements
*/
begin not atomic
declare DEAD_LOCK condition for SQLSTATE '12000';
declare NOT_FOUND condition for SQLSTATE '02000';
declare continue HANDLER for SQLWARNING
LABEL_9999:
begin not atomic
set RESULT = RESULT || ' Sqlwarning';
end;
declare continue handler for SQLEXCEPTION
Label_8888:
begin not atomic
set RESULT = RESULT || ' Sqlexception';
end;
declare continue handler for SQLSTATE '23800'
set RESULT = RESULT || ' 23800';
declare continue handler for DEAD_LOCK
LABEL_6666:
begin not atomic
set RESULT = RESULT || ' Deadlocked';
end;
declare continue handler for NOT FOUND
set RESULT = RESULT || ' Not Found';
set RESULT = 'Signal trace:';
signal SQLSTATE '23800';
signal NOT_FOUND;
signal SQLSTATE '01200';
signal SQLSTATE '72300';
signal DEAD_LOCK;
end label_400
++
call user01.tsignal5('Signal')
*+ *+ TITLE *+ ----- *+ Signal *+ *+ *+ RESULT *+ ------ *+ Signal trace: 23800 Not Found Sqlwarning Sqlexception Deadlocked *+
The WHILE statement repeats the execution of a statement or a group of statements while a condition is met.
Syntax
►►─┬──────────────┬───── WHILE ─── search-condition ── DO ─────────────────────► └─ beg-label: ─┘ ┌──────────────────────────────┐ ►── ▼ ─── procedure-statement ─ ; ─┴───── END WHILE ───────┬─────────────┬────►◄ └─ end-label ─┘
Parameters
Specifies a 1- through 32-character SQL identifier that labels the WHILE statement. The value must be different from any other label used in the compound statement if the WHILE statement is contained in a compound statement.
Specifies the search condition to be evaluated. If the outcome is false, the statement after the WHILE statement is executed. Otherwise, an iteration of the group of statements enclosed by DO and END WHILE is started.
Specifies the statement or group of statements that are repeatedly executed.
Specifies an SQL identifier that labels the end of the WHILE statement. If specified, a beg-label must also have been specified and both labels must be equal.
Example
set options command delimiter '++';
create procedure USER01.TWHILE2
( TITLE varchar(10) with default
, P_FNAME char(20)
, P_COUNT integer
)
EXTERNAL NAME TWHILE2 LANGUAGE SQL
Label_700:
begin not atomic
/*
** Count number of employees with equal first name
*/
declare FNAME char(20);
declare LNAME varchar(20);
declare EMP1 CURSOR FOR
Select EMP_FNAME, EMP_LNAME
From DEMOEMPL.EMPLOYEE
where EMP_FNAME = P_FNAME;
set P_COUNT = 0;
open EMP1;
fetch EMP1 into FNAME, LNAME;
fetching_loop_non_SQL:
while (SQLSTATE = '00000')
do
set P_COUNT = P_COUNT + 1;
fetch EMP1 into FNAME, LN&AME
.
end while fetching_loop_non_SQL;
close EMP1;
end
++
call USER01.TWHILE2('TWHILE2','Martin ')
;
*+
*+ TITLE P_FNAME P_COUNT
*+ ----- ------- -------
*+ TWHILE2 Martin 3
An SQL-invoked procedure can return results to the caller by assigning values to one or more parameters of the procedure. With r17, it is now possible for an SQL-invoked procedure to return result sets in the form of rows of result tables.
To exploit result sets returned by an SQL-invoked procedure, an application must consist of at least an SQL-invoked procedure and a caller of that procedure. The caller can be an SQL client program or another SQL-invoked routine. The SQL-invoked procedure that returns the result sets can be an external procedure (Cobol, PL/I, Assembler or CA ADS) or an internal SQL-invoked procedure written in SQL.
For an SQL-invoked procedure to return result sets to its caller, it must be defined with a positive integer value for the new Dynamic Result Sets attribute.
A cursor declared or dynamically allocated in the SQL-invoked procedure becomes a potential returned result set if its definition contains With Return as the value for the new returnability attribute. Such a cursor is called a returnable cursor. It becomes a returned result set if it is in the open state when the SQL-invoked procedure terminates.
An SQL-invoked procedure can return multiple result sets up to the number specified by the Dynamic Result Sets attribute of the procedure. The list of returned result sets are sequenced in the order of the open of the cursors. If the procedure starts multiple sessions, then returned result sets are grouped by session and the sessions are sequenced in the order of the connects. After a procedure CALL, the new SQLCA field SQLCNRRS contains the number of result sets returned by the procedure.
The caller of an SQL-invoked procedure accesses returned result sets by allocating a dynamic cursor and associating it with the procedure through an ALLOCATE CURSOR FOR PROCEDURE statement. Such a cursor is called a received cursor.
A successful ALLOCATE CURSOR FOR PROCEDURE statement associates the received cursor with the first result set from the sequence of returned result sets and places the cursor in the open state. The cursor position is the same as it was when the SQL-invoked procedure terminated and the associated returned result set is removed from the list of returned result sets.
The caller of the procedure can access the next in the sequence of returned result sets by either allocating another cursor for the procedure or by closing the previously allocated received cursor. If the close is successful and the list of remaining returned result sets is not empty, the received cursor is automatically placed in the open state and associated with the result set that is now first in the list. The newly associated result set is also removed from the list. This process can be repeated until the list of returned result sets is empty.
A new invocation of the SQL-invoked procedure automatically destroys all the returned result sets from the previous invocation.
The received cursors, allocated by the caller and associated with returned result sets, are necessarily dynamic. Unless the program knows the returned columns and their data type, a DESCRIBE CURSOR statement is needed to retrieve the description of the returned result set in an SQL descriptor area (SQLDA).
Only the immediate caller of an SQL-invoked procedure can process returned result sets. There is no mechanism for the caller to return returned result sets to its caller.
The ALLOCATE CURSOR statement has been enhanced with the FOR PROCEDURE and WITH/WITHOUT RETURN clauses.
The ALLOCATE CURSOR statement defines a cursor for a dynamically-prepared statement or for a result set returned from a previously invoked procedure.
Syntax
►►──── ALLOCATE extended-cursor-name ──────────────────────────────────────────► ►┬─ CURSOR ──────┬─────────────────┬─ FOR extended-statement-name ────────┬────►◄ │ ├ WITH RETURN ────┤ │ │ └ WITHOUT RETURN ◄┘ │ └┬──────────┬─ FOR PROCEDURE SPECIFIC PROCEDURE spec-routine-designator ─┘ └─ CURSOR ─┘
Expansion of spec-routine-designator
►─┬───┬────────────────┬─── procedure-identifier ──────────────────┬──────────►◄ │ └─ schema-name. ─┘ │ │ │ └┬ procedure-identifier ┬─┬────────────────────────────────────┬─┘ └ :host-variable-proc ─┘ └─ SCHEMA ─┬─ schema-name ──────────┤ └─ :host-variable-schema ┘
Parameters
Defines the cursor as a returnable cursor. If a returnable cursor is allocated in an SQL-invoked procedure and is in the open state when the procedure terminates, a result set is returned to the caller.
Specifies that the cursor is not a returnable cursor. This is the default.
Specifies that the cursor is to be allocated for a result set returned by the invocation of the identified procedure. This type of cursor is called a received cursor.
Identifies the SQL-invoked procedure.
Specifies the schema with which the procedure identified by procedure-identifier is associated.
Identifies a procedure defined in the dictionary.
Identifies a host variable, routine parameter, or local variable containing the name of the previously invoked procedure.
If :host-variable-proc is a routine parameter or local variable, the colon must not be coded.
Qualifies the procedure name with the name of the schema with which it is associated. This option is an extension to the ISO standard.
Specifies the schema with which the procedure identified by procedure-identifier or host-variable-proc is associated.
Identifies a host variable, routine parameter, or local variable containing the name of the schema with which the procedure identified by procedure-identifier or host-variable-proc is associated.
If :host-variable-schema is a routine parameter or local variable, the colon must not be coded.
Note: For more information about using a schema name to qualify a procedure, see Identifying Entities in Schemas in the "Identifiers" chapter in the CA IDMS SQL Reference Guide.
Usage
Allocating a Received Cursor for a Result Set
If the ALLOCATE statement is used for a result set, then the procedure identified by spec-routine-designator must have been previously invoked by an SQL CALL or SELECT statement in the same transaction as that in which the ALLOCATE CURSOR statement is executed.
The result sets that the SQL-invoked procedure returns, form a list ordered in the sequence in which the cursors were opened by the procedure. When a received cursor is allocated, the following actions are taken:
If an SQL-invoked procedure has started multiple sessions, the sequence of returned result sets is by session, in the order in which the sessions were connected. Within each session, the result sets are sequenced by the order in which their cursors were opened.
A received cursor cannot be used to return a result set nor can it be referenced in a positioned update or delete statement.
Note: For more information, see DESCRIBE CURSOR.
Example
exec sql
call GET_EMPLOYEE_INFO(1003)
end-exec
exec sql
allocate 'RECEIVED_CURSOR_GET_EMPG' for procedure specific
procedure GET_EMPLOYEE_INFO
end-exec
This is an existing statement that is enhanced with the DYNAMIC RESULT SETS clause. Using the ALTER PROCEDURE statement you can change the number of dynamic result sets that a procedure can return to the caller.
Syntax
►►── ALTER PROCEDURE ─ . . . ──────────────────────────────────────────────────► ►────┬─────────────────────────────────────────────────────┬──────────────────►◄ └── DYNAMIC RESULT SETS maximum-dynamic-result-sets ──┘
Parameters
Defines the maximum number of result sets that a procedure invocation can return to its caller. A result set is a sequence of rows specified by a cursor-specification, created by the opening of a cursor and ranged over that cursor.
Defines an integer in the range 0-32767 specifying the maximum number of result sets a procedure can return.
Note: For more information, see CREATE PROCEDURE.
The CALL statement has been enhanced to provide a warning if the procedure returns more result sets than specified in the DYNAMIC RESULT SETS attribute of the procedure.
Note: For a comprehensive example illustrating the basic coding techniques to use dynamic result sets in an application, see the CALL statement in the "Statements" chapter in the CA IDMS SQL Reference Guide.
Usage
Calling an SQL-invoked Procedure Returning Result Sets
After a CALL of an SQL-invoked procedure that has been defined with a positive value for the Dynamic Result Sets attribute the number of actual returned results sets is available in the field SQLCNRRS of the SQLCA. The number of returned result sets can also be determined by issuing a GET DIAGNOSTICS statement to retrieve the IDMS_RETURNED_RESULT_SETS information item.
The successful execution of a CALL statement may result in one of two warning conditions:
Indicates that the number of result sets returned by the procedure is less than or equal to the value of the procedure's DYNAMIC RESULT SETS attribute.
Indicates that the procedure attempted to return more result sets than permitted by its DYNAMIC RESULT SETS attribute. The actual number of result sets is reduced to the value of the DYNAMIC RESULT SETS attribute.
A call of a procedure destroys any result sets left over from a previous invocation of the same procedure.
Note: For more information, see ALLOCATE CURSOR.
The CLOSE statement has been enhanced to associate a received cursor with the next in the sequence of result sets returned by an SQL-invoked procedure.
The CLOSE statement places a specified cursor in the closed state or disassociates a received cursor from the current returned result set and associates it with the next result set returned by the procedure. Use this statement only in SQL that is embedded in a program.
Usage
Closing a Received Cursor
A received cursor is a dynamically allocated cursor used to process one or more result sets returned by an SQL-invoked procedure. Returned result sets are maintained in an ordered list. An ALLOCATE CURSOR statement associates the cursor with the first result set in the list and removes it from the list.
If the list of returned result sets is not empty when a received cursor is closed, the CLOSE statement causes the following actions to be taken:
Closing the cursor associated with the last result set of a session started by the called procedure, releases that session.
Note: For more information, see the FOR PROCEDURE clause in ALLOCATE CURSOR.
The CREATE PROCEDURE statement has been enhanced with the DYNAMIC RESULT SETS clause.
Syntax
►►── CREATE PROCEDURE ─ . . . ─────────────────────────────────────────────────► ►────┬─────────────────────────────────────────────────────┬──────────────────►◄ └── DYNAMIC RESULT SETS maximum-dynamic-result-sets ──┘
Parameters
Defines the maximum number of result sets that a procedure invocation can return to its caller. A result set is a sequence of rows specified by a cursor-specification, created by the opening of a cursor and ranged over that cursor.
Defines an integer in the range 0-32767 specifying the maximum number of result sets a procedure can return. The default is 0.
Usage
Dynamic Result Sets
An SQL invoked procedure can return one or more result sets to its caller, up to the maximum number specified by its dynamic result sets attribute. A result set is returned for each returnable cursor that is still open when the procedure returns control to its caller.
For information on defining a returnable cursor, see ALLOCATE CURSOR or :hdref refid=declcur.. For information on how the caller processes the returned result sets, see ALLOCATE CURSOR.
Example
The GET_EMPLOYEE_INFO procedure uses the given employee ID, to construct two result set cursors:
set options command delimiter '++';
create procedure SQLROUTE.GET_EMPLOYEE_INFO
( TITLE varchar(10) with default
, P_EMP_ID numeric(4)
, RESULT varchar(20)
)
EXTERNAL NAME GETEMPIN LANGUAGE SQL
DYNAMIC RESULT SETS 2
begin not atomic
declare STMNT_NAME char(10) default 'DYN_STMNT1';
declare STMNT_BUF char(80) default ' ';
declare RET_COVERAGE cursor with return for
select * from DEMOEMPL.COVERAGE
where EMP_ID = P_EMP_ID;
open RET_COVERAGE;
set STMNT_BUF = 'select * from DEMOEMPL.BENEFITS'
|| 'where EMP_ID = ' || P_EMP_ID;
prepare STMT_NAME from STMT_BUF;
allocate 'RET_BENEFITS' cursor with return for STMT_NAME;
open 'RET_BENEFITS';
set RESULT = '2 returned result sets';
end
set options command delimiter default ++
The DECLARE CURSOR statement has been enhanced with the cursor returnability characteristic.
The DECLARE CURSOR statement is a data manipulation statement that defines a cursor for a specified result table. Use this statement only in SQL that is embedded in a program.
Syntax
►►── cursor-declaration ──────────────────────────────────────────────────────►◄
Expansion of cursor-declaration
►►── DECLARE static-cursor-name ─┬──────────┬─ CURSOR ─────────────────────────► └─ GLOBAL ─┘ ►───┬───────────────────┬── FOR ──┬─ cursor-specification ──┬─────────────────►◄ ├─ WITH RETURN ─────┤ └─ static-statement-name ─┘ └─ WITHOUT RETURN ◄─┘
Parameters
Defines the cursor as a returnable cursor. If a returnable cursor is declared in an SQL-invoked procedure and is in the open state when the procedure returns to its caller, a result set is returned to the caller.
Specifies that the cursor is not a returnable cursor. This is the default.
Usage
Defining Returnable Cursors
While any cursor can be defined as a returnable cursor using WITH RETURN, it only makes sense to do so in programs that are invoked as SQL-invoked procedures and that are defined with a non-zero dynamic result set attribute.
The invoker must use the ALLOCATE CURSOR statement to associate returned result sets with received cursors for further processing. For more information about how the caller processes returned result sets, see ALLOCATE CURSOR.
Example
The following DECLARE CURSOR statement is specified in an SQL-invoked procedure written in SQL. The cursor RET_COVERAGE returns a result set consisting of the rows of the table DEMOEMPL.COVERAGE for which the column EMP_ID equals the value of the parameter P_EMP_ID. To effectively return the result set, the cursor must be left open on the return from the procedure.
declare RET_COVERAGE cursor with return for
select * from DEMOEMPL.COVERAGE
where EMP_ID = P_EMP_ID;
The DESCRIBE CURSOR is a new data manipulation statement that describes the result set associated with a received cursor. It directs CA IDMS to return information about the result set associated with a received cursor into an SQL descriptor area. Use this statement only in SQL that is embedded in a program.
Syntax
►►──── DESCRIBE output CURSOR extended-cursor-name STRUCTURE ──────────────────► ►───── USING sql DESCRIPTOR descriptor-area-name ─────────────────────────────►◄
Parameters
Specifies the name of the cursor whose result set is to be described. The cursor must have been previously associated with a returned result set using the ALLOCATE CURSOR statement.
Specifies the SQL descriptor area where CA IDMS is to return information about the result set with which the cursor is associated.
Directs CA IDMS to use the named area as the descriptor area. descriptor-area-name must identify an SQL descriptor area.
Example
The GET_EMPLOYEE_INFO procedure returns two result sets for a given EMP_ID:
Note: For more information about how to define this procedure, see the examples in CREATE PROCEDURE.
* Invocation of the procedure GET_EMPLOYEE_INFO.
exec sql
call GET_EMPLOYEE_INFO(1003)
end-exec
* The dynamic cursor 'RECEIVED_CURSOR' is associated with the first result set.
* The received cursor is in the open state.
exec sql
allocate 'RECEIVED_CURSOR' for procedure specific procedure
GET_EMPLOYEE_INFO
end-exec
* The 'RECEIVED_CURSOR' cursor info is being described.
exec sql
describe cursor 'RECEIVED_CURSOR' structure
using sql descriptor SQLDA-AREA
end-exec
* The COVERAGE info is being processed.
* The statement is executed in a loop until the SQLSTATE indicates NO MORE DATA.
exec sql
fetch 'RECEIVED_CURSOR' into :BUFFER-COVER
using descriptor SQLDA-AREA
end-exec
* The dynamic cursor 'RECEIVED_CURSOR' is associated with the second result set.
* The received cursor is in the open state.
exec sql
close 'RECEIVED_CURSOR'
end-exec
. .
* The 'RECEIVED_CURSOR' info is being described.
exec sql
describe cursor 'RECEIVED_CURSOR' structure
using sql descriptor SQLDA-AREA
end-exec
. . .
* The BENEFITS info is being processed.
* The statement is executed in a loop until the SQLSTATE indicates NO MORE DATA.
exec sql
fetch 'RECEIVED_CURSOR' into :BUFFER-BENEF
using descriptor SQLDA-AREA
end-exec
* Close the cursor.
exec sql
close 'RECEIVED_CURSOR'
end-exec
SQLCA
After a CALL or SELECT of a procedure, the new SQLCNRRS field holds the actual number of result sets that an SQL-invoked procedure returned.
SQLSTATE
The following new SQLSTATE values are associated with processing returned result sets.
|
SQLSTATE |
Description |
|---|---|
|
0100C |
SQL-invoked procedure returned result sets |
|
0100D |
Additional result sets returned |
|
0100E |
Attempt to return too many result sets |
The SYSTEM.TABLE and SYSTEM.SYNTAX catalog tables have been enhanced.
SYSTEM.TABLE
The new type value of 'L' identifies this as an internal table whose columns represent the local variables of an SQL routine.
The new LANGUAGE column identifies the language in which the SQL routine is written. The data type of this column is CHAR(3) and its allowable values are: ADS, ASM, COB, PLI, or SQL.
The new DYNRESULTSETS column specifies the maximum number of result sets that can be returned by a procedure. The data type of this column is SMALLINT and its allowable values range from 0-32767.
SYSTEM.SYNTAX
The new type value of 'S' identifies this as a row of text contained in the body of an SQL routine.
The new SQL Diagnostic statements category is used for diagnosing the execution of SQL statements and for returning statistical information for the current transaction.
Note: These statements can be used as embedded SQL, including embedding in an SQL-invoked routine. The GET STATISTICS statement can also be used in the command facility and the CA IDMS Visual DBA command console.
|
Statement |
Purpose |
|---|---|
|
GET DIAGNOSTICS |
Diagnoses the execution of the last executed SQL statement. |
|
GET STATISTICS |
Returns statistical information for the current transaction. |
The GET DIAGNOSTICS statement extracts information on exception or completion conditions from the diagnostics area and returns it to the issuer. Use this statement in SQL that is embedded in a program.
Syntax
►─ GET DIAGNOSTICS ─┬─ statement-info ────────────────────────────┬───────────►◄ │─ CONDITION ─┬─ condition-nr condition-info ─┘ └─ EXCEPTION ─┘
Expansion of statement-info
┌────────────────────────────────── , ───────────────────────────────────┐ ►─ ▼ ─┬─ routine-parameter ───┬──── = ────┬─ COMMAND_FUNCTION ────────────┬─┴─►◄ ├─ host-variable ───────┤ ├─ COMMAND_FUNCTION_CODE ───────┤ └─ local-variable ──────┘ ├─ DYNAMIC_FUNCTION ────────────┤ ├─ DYNAMIC_FUNCTION_CODE ───────┤ ├─ IDMS_RETURNED_RESULT_SETS ───┤ ├─ MORE ────────────────────────┤ ├─ NUMBER ──────────────────────┤ └─ ROW_COUNT ───────────────────┘
Expansion of condition-info
┌────────────────────────────────── , ───────────────────────────────────┐ ►─ ▼ ─┬─ routine-parameter ───┬──── = ────┬─ IDMS_MESSAGE_COMMENTS ───────┬─┴─►◄ ├─ host-variable ───────┤ ├─ IDMS-MESSAGE_DEFINITION ─────┤ └─ local-variable ──────┘ ├─ IDMS_MESSAGE_ID ─────────────┤ ├─ IDMS_MODULE_NUMBER ──────────┤ ├─ IDMS_REASON_CODE ────────────┤ ├─ IDMS_SQLCODE ────────────────┤ ├─ IDMS_TASK_ID ────────────────┤ ├─ MESSAGE_LENGTH ──────────────┤ ├─ MESSAGE_TEXT ────────────────┤ └─ RETURNED_SQLSTATE ───────────┘
Parameters
Identifies an SQL routine parameter that is to receive the value of the specified diagnostics item. Routine-parameter must be a parameter of the current SQL routine and must be compatible for assignment with the specified diagnostic item.
For information about expanded syntax, see Expansion of Routine-parameter.
Identifies a host variable that is to receive the value of the specified diagnostics item. Host-variable must be a host variable previously declared in the application program and must be compatible for assignment with the specified diagnostic item.
For information about expanded syntax, see Expansion of Host-variable in "Values and Value Expressions" in the CA IDMS SQL Reference Guide.
Identifies a local variable of an SQL routine that is to receive the value of the specified diagnostics item. local-variable must be a local variable declared in the current SQL routine and must be compatible for assignment with the specified diagnostic item.
For information about expanded syntax, see Expansion of Local-variable.
Identifies the type of statement information to be extracted and returned. Statement-info names that begin with 'IDMS_' are extensions to the ISO standard.
Returns a value with data type varchar(64) indicating the type of SQL command that was last executed. The values that may be returned are listed under the Statement Type column in Table Procedure Requests in the "Defining and Using Table Procedures" chapter in the CA IDMS SQL Reference Guide.
Returns a value with data type integer indicating the type of SQL command that was last executed. The values that may be returned are listed under the Command Number column in Table Procedure Requests in the "Defining and Using Table Procedures" chapter in the CA IDMS SQL Reference Guide.
Returns a value with data type varchar(64) indicating the type of SQL command that was prepared or dynamically executed by the last command. The values that may be returned are listed under the Statement Type column in Table Procedure Requests in the "Defining and Using Table Procedures" chapter in the CA IDMS SQL Reference Guide.
Returns a value with data type integer indicating the type of SQL command that was prepared or dynamically executed by the last command. The values that may be returned are listed under the Command Number column in Table Procedure Requests in the "Defining and Using Table Procedures" chapter in the CA IDMS SQL Reference Guide.
Returns a value with data type integer indicating the number of result sets returned by a procedure invoked by the last command. This value is only valid if the diagnosed statement is a call of an SQL invoked procedure.
Returns a value with data type char(1). A value of 'Y' indicates that the execution of the previous SQL statement caused more conditions than have been set in the diagnostics area. A value of 'N' means that the diagnostics area contains information on all the completion and exception conditions.
Returns a value with data type integer indicating the number of the exceptions or completion conditions set by the execution of the previous SQL statement for which information is available in the diagnostics area.
Returns a value with data type DEC(31). The value depends on the type of the previously executed statement:
Requests diagnostic information for a condition.
Specifies a synonym for CONDITION. While it is part of the current ISO standard, its use is discouraged because it will not be in future ISO standards.
Specifies the number of the completion or exception condition for which diagnostics information is being requested. An exception is raised if condition-nr does not refer to a valid condition number.
Identifies the type of condition-related information to be extracted and returned. Condition-info names that begin with 'IDMS_' are extensions to the ISO standard.
Returns a value with data type varchar(4000) containing the comments in the message dictionary for the message associated with the condition.
Returns a value with data type varchar(4000) containing the definition in the message dictionary of the message associated with the condition.
Returns a value with data type char(8) containing the message ID in the message dictionary of the message associated with the condition.
Returns a value with data type integer containing the number of the module that detected the condition.
Returns a value with data type integer containing the reason code of the condition.
Returns a value with data type integer containing the SQLCODE value associated with the condition. See the "SQL COMMUNICATION Area" appendix in the CA IDMS SQL Reference Guide.
Returns a value with data type integer containing the IDMS task ID of the task that encountered the condition.
Returns a value with data type integer indicating the length of the message associated with the specified condition.
Returns a value with data type varchar(256) containing the message text associated with the specified condition.
Returns a value with data type char(5) indicating the SQLSTATE associated with the specified condition.
Example
The procedure TGETDIAG1 executes a SELECT statement that causes a number of string truncations. The first GET DIAGNOSTICS returns the number of conditions that the SELECT statement raised. A WHILE LOOP containing the second GET DIAGNOSTICS concatenates the message texts of all the raised conditions to the RESULT parameter of the procedure.
set options command delimiter '++';
drop procedure SQLROUT.TGETDIAG1++
commit++
create procedure SQLROUT.TGETDIAG1
( TITLE varchar(10) with default
, P_NAME char(18)
, P_NUMBER integer
, RESULT varchar(512)
)
EXTERNAL NAME TGETDIAG LANGUAGE SQL
begin not atomic
declare L_NUMBER integer default 1;
declare L_MESSAGE varchar(256) default ' ';
select NAME into P_NAME from system.schema
where cast(NAME as char(12)) = P_NAME;
/* retrieve the number of conditions raised */
get diagnostics P_NUMBER = NUMBER;
while (L_NUMBER < = P_NUMBER)
do
/* retrieve the message text of the raised condition */
get diagnostics condition L_NUMBER
L_MESSAGE = MESSAGE_TEXT
set RESULT = RESULT || ' ' || L_MESSAGE;
set L_NUMBER = L_NUMBER + 1;
end while;
end
++
call SQLROUT.TGETDIAG1('TGETDIAG1', 'SYSTEM');
*+
*+ TITLE P_NAME P_NUMBER
*+ ----- ------ --------
*+ TGETDIAG1 SYSTEM 4
*+
*+ RESULT
*+ ------
*+ DB001043 T171 C1M322: String truncation DB001043 T171 C1M322:
*+ String truncation DB001043 T171 C1M322: String truncation
*+ DB001043 T171 C1M322: String truncation
The GET STATISTICS statement returns statistical information for the current transaction. It is a CA IDMS extension to the ISO SQL standard. Use this statement in SQL that is embedded in a program, in the SQL command facility, and in the command console of CA IDMS Visual DBA.
Syntax
►── GET STATISTICS ── transaction-info ───────────────────────────────────────►◄
Expansion of transaction-info
┌───────────────────────── , ──────────────────────────────────────┐ ►─ ▼ ─┬───────────────────────────────┬┬─ SQL_COMMANDS ──────────────┬┴───────►◄ ├─ routine-parameter ─────┬─ = ─┘├─ ROWS_FETCHED ──────────────┤ ├─ host-variable ─────────┤ ├─ ROWS_INSERTED ─────────────┤ └─ local-variable ────────┘ ├─ ROWS_UPDATED ──────────────┤ ├─ ROWS_DELETED ──────────────┤ ├─ SORT ──────────────────────┤ ├─ ROWS_SORTED ───────────────┤ ├─ MIN_ROWS_SORTED ───────────┤ ├─ MAX_ROWS_SORTED ───────────┤ ├─ AM_RECOMPILES ─────────────┤ ├─ PAGES_READ ────────────────┤ ├─ PAGES_WRITTEN ─────────────┤ ├─ PAGES_REQUESTED ───────────┤ ├─ CALC_TARGET ───────────────┤ ├─ CALC_OVERFLOW ─────────────┤ ├─ VIA_TARGET ────────────────┤ ├─ VIA_OVERFLOW ──────────────┤ ├─ RECORDS_REQUESTED ─────────┤ ├─ RECORDS_CURRENT ───────────┤ ├─ CALLS_DBMS ────────────────┤ ├─ FRAGMENTS_STORED ──────────┤ ├─ RECORDS_RELOCATED ─────────┤ ├─ TOTAL_LOCKS ───────────────┤ ├─ SHARE_LOCKS_HELD ──────────┤ ├─ NON_SHARE_LOCKS_HELD ──────┤ ├─ TOTAL_LOCKS_FREED ─────────┤ ├─ SR8_SPLITS ────────────────┤ ├─ SR8_SPAWNS ────────────────┤ ├─ SR8_STORED ────────────────┤ ├─ SR8_ERASED ────────────────┤ ├─ SR7_STORED ────────────────┤ ├─ SR7_ERASED ────────────────┤ ├─ B_TREE_SEARCH ─────────────┤ ├─ B_TREE_LEVELS_SEARCH ──────┤ ├─ ORPHANS_ADOPTED ───────────┤ ├─ LEVELS_SEARCH_BEST_CASE ───┤ ├─ LEVELS_SEARCH_WORST_CAS ───┤ ├─ RECORDS_UPDATED ───────────┤ ├─ SHARE_LOCKS_ACQ_CALL ──────┤ ├─ SHARE_LOCKS_FREED_CALL ────┤ ├─ NON_SHARE_LOCKS_ACQ_CALL ──┤ ├─ NON_SHARE_LOCKS_FREED_CALL ┤ └─ * ┘
Parameters
Identifies an SQL routine parameter that is to receive the value of the specified statistics item. Routine-parameter must be a parameter of the current SQL routine and must be compatible for assignment with the specified statistics item.
For information about expanded syntax, see Expansion of Routine-parameter.
Identifies a host variable that is to receive the value of the specified statistics item. Host-variable must be a host variable previously declared in the application program and must be compatible for assignment with the specified statistics item.
For information about expanded syntax, see Expansion of Host-variable in "Values and Value Expressions" in the CA IDMS SQL Reference Guide.
Identifies a local variable of an SQL routine that is to receive the value of the specified statistics item. local-variable must be a local variable declared in the SQL-invoked routine and must be compatible for assignment with the specified statistics item.
For information about expanded syntax, see Expansion of Local-variable.
Note: A routine-parameter, host-variable or local-variable must be specified for each transaction-info when the statement is embedded in a program. Otherwise, these must not be specified.
Identifies the type of transaction information that is to be returned. Each item has an integer data type and represents statistical information for the current transaction. For more information about these items, see the DCMT DISPLAY STATISTICS SYSTEM and DCMT DISPLAY TRANSACTION commands in the CA IDMS System Tasks and Operator Commands Guide.
Requests that all transaction-info items to be retrieved. This is not allowed in combination with the specification of a routine-parameter, host-variable, or local-variable and therefore cannot be used in a program.
Example
The SQL procedure TGETSTA1 counts the number of rows of one of four tables:
The actual table is selected through the value of the TITLE parameter. Besides returning the count of rows, the procedure also returns the values of a number of statistical information items for the transaction:
set options command delimiter '++';
drop procedure SQLROUT.TGETSTA1++
commit++
create procedure SQLROUT.TGETSTA1
( TITLE char(8) with default
, P_COUNT integer
, P_SQL_COMMANDS integer
, P_PAGES_REQUESTED integer
, P_PAGES_READ integer
, P_CALLS_DBMS integer
, P_TOTAL_LOCKS integer
)
EXTERNAL NAME TGETSTA1 LANGUAGE SQL
Lab1: begin not atomic
case TITLE
when 'TABLE'
then select count(*) into P_COUNT
from SYSTEM.TABLE;
when 'COLUMN'
then select count(*) into P_COUNT
from SYSTEM.COLUMN;
when 'SCHEMA'
then select count(*) into P_COUNT
from SYSTEM.SCHEMA;
when 'EMPLOYEE'
then select count(*) into P_COUNT
from DEMOEMPL.EMPLOYEE;
end case;
get statistics
P_SQL_COMMANDS = sql_commands
, P_PAGES_REQUESTED = pages_requested
, P_PAGES_READ = pages_read
, P_CALLS_DBMS = calls_dbms
, P_TOTAL_LOCKS = total_locks;
end
++
set options command delimiter default ++
call sqlrout.TGETSTA1('TABLE');
*+
*+ TITLE P_COUNT P_SQL_COMMANDS P_PAGES_REQUESTED P_PAGES_READ
*+ ----- ------- -------------- ----------------- ------------
*+ TABLE 808 2 836 9
*+
*+ P_CALLS_DBMS P_TOTAL_LOCKS
*+ ------------ -------------
*+ 813 1673
call sqlrout.TGETSTA1('COLUMN');
*+
*+ TITLE P_COUNT P_SQL_COMMANDS P_PAGES_REQUESTED P_PAGES_READ
*+ ----- ------- -------------- ----------------- ------------
*+ COLUMN 6450 3 8953 1068
*+
*+ P_CALLS_DBMS P_TOTAL_LOCKS
*+ ------------ -------------
*+ 8071 8300
call sqlrout.TGETSTA1('SCHEMA');
*+
*+ TITLE P_COUNT P_SQL_COMMANDS P_PAGES_REQUESTED P_PAGES_READ
*+ ----- ------- -------------- ----------------- ------------
*+ SCHEMA 56 4 59 2
*+
*+ P_CALLS_DBMS P_TOTAL_LOCKS
*+ ------------ -------------
*+ 61 130
call sqlrout.TGETSTA1('EMPLOYEE');
*+
*+ TITLE P_COUNT P_SQL_COMMANDS P_PAGES_REQUESTED P_PAGES_READ
*+ ----- ------- -------------- ----------------- ------------
*+ EMPLOYEE 55 5 58 2
*+
*+ P_CALLS_DBMS P_TOTAL_LOCKS
*+ ------------ -------------
*+ 60 128
ANSI/ISO SQL join support extends the CA IDMS SQL language with the following <joined table> SQL language element components:
The SQL language element table-reference is extended with the new joined-table parameter as follows:
Syntax
►►──┬────────────────┬─┬─ table-identifier ──┬┬───────────────────────────────►◄ ├─ schema-name. ─┘ └─ view-identifier ───┘│ ├─ procedure-reference ───────────────────┤ ├─ table-procedure-reference ─────────────┤ ├─ joined-table ──────────────────────────┤ └─ ( joined-table ) ──────────────────────┘
Parameter
Defines a joined table.
Joined-table represents a table that is derived from joining two specified tables. The different types of join operations are specified through the following join types:
Syntax
►──────┬─ unqualified-joined-table ─┬─────────────────────────────────────────►◄ └─ qualified-joined-table ───┘
Expansion of unqualified-joined-table
►─── table-reference ─┬────────────────────┬──┬─ CROSS ──┬───────────── JOIN ──► └┬──────┬── alias-l ─┘ └─ UNION ──┘ └─ AS ─┘ ►─── table-reference ─┬────────────────────┬──────────────────────────────────►◄ └┬──────┬── alias-r ─┘ └─ AS ─┘
Expansion of qualified-joined-table
►─── table-reference ─┬────────────────────┬─┬──────────────────────┬─ JOIN ───► └┬──────┬── alias-l ─┘ ├───── INNER ──────────┤ └─ AS ─┘ ├─ LEFT ───┬── outer ──┘ ├─ RIGHT ──┤ └─ FULL ───┘ ►─── table-reference ─┬────────────────────┬──── ON ─ join-condition ─────────►◄ └┬──────┬── alias-r ─┘ └─ AS ─┘
Expansion of join-condition
►──── extended-search-condition ──────────────────────────────────────────────►◄
Parameters
Specifies a joined-table where the join operation is a cross or union.
Specifies a joined-table where the join operation is an inner, left outer, right outer, or full outer.
Represents a table-like object. In a joined-table specification, a left and a right table-reference are required to define the left and right operands of the join operation.
Defines a new name used to identify the left table-like object within the joined-table specification. Alias-l must be a 1-through 18-character name that follows the conventions for SQL identifiers.
Defines a new name used to identify the right table-like object within the joined-table specification. Alias-r must be a 1-through 18-character name that follows the conventions for SQL identifiers.
Specifies a cross join. A cross join is the cross product of the left and right table.
Specifies a union join. A union join is equivalent to a full outer join where the join-condition always evaluates to false.
Specifies an inner join. In an inner join, the cross product of the left and right table-like objects is made, and only the rows for which join-condition evaluates to true are kept in the result. This is the default.
Specifies a left outer join. In a left outer join, the cross product of the left and right table-like objects is made, and the rows for which join-condition evaluates to true are kept. The result is extended with all the missing rows from the left table, and the values of the columns in the result row, derived from the right table, are set to NULL.
Specifies a right outer join. In a right outer join, the cross product of the left and right table-like objects is made, and the rows for which join-condition evaluates to true are kept. The result is extended with all the missing rows from the right table, and the values of the columns in the result row, derived from the left table, are set to NULL.
Specifies a full outer join. In a full outer join, the cross product of the left and right table-like objects is made, and the rows for which join-condition evaluates to true are kept. The result is extended with all the missing rows from the left table, and the values of the columns in the result row, derived from the right table, are set to NULL. The result is further extended with all the missing rows from the right table, and the values of the columns in the result row, derived from the left table, are set to NULL.
Represents the condition for joining two table-like objects in a qualified join. Expanded syntax for join-condition appears immediately after the joined-table syntax.
Specifies the condition used for joining two table-like objects in a qualified join.
Note: For more information about expanded syntax, see "Expansion of Extended-search-condition" in the CA IDMS SQL Reference Guide.
Usage
Effect on updatability
A query expression that contains a joined-table is not updatable.
Nesting of joined-table expressions
Joined-table expressions can be nested. The table reference in either the left or right operand of a joined-table expression can be another joined-table. The default order of evaluation of nested joined-table expressions is left to right. You can use parenthesis for clarity and to alter the default evaluation order.
Restrictions on the use of set-specification
Only the join-condition of the inner-most join can contain a set-specification, since both the left and right table references in a set-specification must be to base tables of a non-SQL-defined database.
Examples
Selecting all Departments and Employees in Department
The following examples list all the departments and the employees of the department. The two statements give identical results.
select d.*, e.* from DEMOEMPL.DEPARTMENT d left join DEMOEMPL.EMPLOYEE e on d.dept_id = e.dept_id select d.*, e.* from DEMOEMPL.EMPLOYEE e right join DEMOEMPL.DEPARTMENT d on d.dept_id = e.dept_id
Selecting all Departments and Employees in Department With or Without Position
The following examples show nesting of joined tables. The two statements give identical results.
select d.*, e.*, p.*
from DEMOEMPL.DEPARTMENT d left join
(DEMOEMPL.EMPLOYEE e left join DEMOEMPL.POSITION p
on p.EMP_ID = e.EMP_ID )
on e.DEPT_ID = d.DEPT_ID;
select d.*, e.*, p.*
from DEMOEMPL.DEPARTMENT d left join
(DEMOEMPL.POSITION p right join DEMOEMPL.EMPLOYEE e
on p.EMP_ID = e.EMP_ID )
on e.DEPT_ID = d.DEPT_ID;
For more information about Expansion of Table-reference, see the CA IDMS SQL Reference Guide.
A new SET statement enables directly assigning the results of an SQL value expression to a host variable. This statement can only be used in embedded SQL.
Syntax
►►── SET ──── host-variable ───────── = ────┬─ value-expression ─┬────────────►◄ └─ NULL ─────────────┘
Parameters
Identifies a host-variable that is to receive the value of the specified value expression or null. Host-variable must be a host variable previously declared in the application program.
Specifies the value to be assigned to the destination or receiving field of the assignment statement.
Specifies that host-variable is set to the NULL value.
Usage
The rules for assignment are provided in Comparison, Assignment, Arithmetic, and Concatenation Operations in the "Data Types and Null Values" chapter in the CA IDMS SQL Reference Guide.
Example
The host-variable COMB-NAME is constructed from the values in the host-variables FIRST-NAME and LAST-NAME.
EXEC SQL set:COMB-NAME=trim(:FIRST-NAME) ||' '|| trim(:LAST-NAME); END-EXEC
The UPDATE statement has been extended to allow a query-expression as a value for a column. This permits the use of UNION (ALL) in the specification of value expression.
Syntax
►►─── UPDATE table-reference ──┬───────────┬───────────────────────────────────► └─ alias ─┘ ┌─────────────────── , ───────────────────────────┐ ►─── SET ─▼── column-name ── = ─┬─ value-expression ───────┬┴─. . .──────────►◄ ├─ NULL ─────────────────┬─┘ └─ ( query-expression ) ─┘
Parameter
Represents a value to be assigned to a column in an UPDATE statement. The query-expression must return at most one row and the result table of the query-expression must consist of a single column.
Note: For more information about expanded query-expression syntax, see the chapter "Query Specifications, Subqueries, Query Expressions, and Cursor Specifications" in the CA IDMS SQL Reference Guide.
Usage
Using a query-expression as a Source Value
If a query expression, used as the value to be assigned to a column, returns no rows, then the column is set to the NULL value. If the column does not allow NULLs, an exception is raised.
The SET OPTIONS statement of the command facility (OCF and IDMSBCF) has been enhanced to support the specification of a character string different from the default command terminator, the semi-colon (;).
The use of an alternate command delimiter is required when entering multi-statement SQL routine bodies using the CREATE PROCEDURE or CREATE FUNCTION commands. According to the SQL procedural language, multiple SQL statements must be separated by the semi-colon. However, using the semi-colon also as the command terminator would truncate the CREATE command after the first semi-colon, and any statements thereafter would erroneously be interpreted as new commands for the command facility and not as statements that make up the rest of the SQL routine body.
Note: Specifying a command terminator string replaces the one that is currently in effect, which by default is the semi-colon. The specification of a command delimiter remains in effect until a new SET OPTIONS COMMAND DELIMITER is issued or until the end of the command facility session.
Syntax
┌──────────────────────────────────────────────────┐ ►►── SET ──── OPTIONS ─▼──┬─ ... ────────────────────────────────────────┬┴───►◄ └─ COMMAND DELIMITER ────┬─ DEFAULT ────────┬──┘ └─ 'delimiter' ────┘
Parameters
Specifies the character string whose value is used to delimit a command facility statement.
Specifies the character string literal to be used as a delimiter. 'Delimiter' must be a 1- to 32-character string.
Specifies that the default of a semicolon (;) be used as a delimiter.
Example
set options command delimiter '++';
drop procedure PRODUCTION.PROCESS ++
commit++
create procedure PRODUCTION.PROCESS
(PROC_TYPE integer,PROC_VALUE char(20))
external name DPROCESS language SQL
begin
set PROC_TYPE = 12;
set PROC_VALUE = 'High';
end
++
set options command delimiter DEFAULT ++
The SYSCA.SINGLETON_NULL is a pseudo table that can be used to return the results of expressions whose parameters are constants. It has one row and no columns. This table is a pseudo table because it does not exist in the catalog. It can be queried through a SELECT statement. This table is used internally by CA IDMS, and it is also useful when evaluating SQL functions and other expressions with constant parameters.
Example
select USER01.TLANG1('James ', 'Last ')
from SYSCA.SINGLETON_NULL;
*+
*+ USER_FUNC
*+ ---------
*+ James Last
|
Copyright © 2009 CA.
All rights reserved.
|
|