Values and Value Expressions › Expansion of Local-variable

Expansion of Local-variable

The expanded parameters of local-variable identify program variables declared in a compound statement.

Syntax

Expansion of local-variable

►─────────┬──────────────────────┬── local-variable-name ─────────────────────►◄
          └── cmp-stmnt-label. ──┘

Parameters

cmp-stmnt-label

Specifies the label of the compound statement that contains the definition of local-variable.

local-variable-name

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:

• A local variable can be qualified with the label of the compound statement in which it is declared.
• A routine parameter can be qualified with its associated schema and routine name.
• A column can be qualified with its schema and table name.
• Other schema-defined objects can be qualified with the name of the schema in which they are defined.

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:

• If a local variable with a matching name has been declared within the compound statement in which the reference occurs, the reference is to the local variable. If more than one such variable is declared, the reference is to the variable declared in the innermost compound statement containing the reference.
• If a parameter of the routine in which the reference occurs has a matching name, the reference is to the routine parameter.
• Otherwise, the reference is treated as a reference to a schema-defined object.

  Note: For more information about how such a reference is resolved, see Resolving References to Entities in Schemas.

In the SQL 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