TABLE REF data type

The TABLE REF data type stores a reference to a base table, temporary table, or view. This data type is only for use with connection-scope variables.

Syntax

Declaring a variable of type TABLE REF

DECLARE table-ref-variable TABLE REF 
  [ { DEFAULT | = } TABLE REF ( [ owner.]table-name ) ]

Creating a variable of type TABLE REF

CREATE VARIABLE table-ref-variable TABLE REF 
  [ { DEFAULT | = } TABLE REF ( [ owner.]table-name ) ]

Setting a variable of type TABLE REF

SET table-ref-variable = TABLE REF ( table-name )

Referencing a variable of type TABLE REF in DML statements
```
TABLE REF ( table-ref-variable ) AS correlation-name
```
Referencing a variable of type TABLE REF as a parameter in a function or procedure
```
TABLE REF ( table-ref-variable )
```

Parameters

table-ref-variable A valid identifier for the table reference variable.
table-name The name of a base table. Optionally, include the owner as part of the specification (for example, GROUPO.Employees); this is recommended for base tables and views.

Remarks

Table reference variables (variables of type TABLE REF) allow procedures and functions to be defined even though the names of the tables they operate on change or have not yet been defined.

When referencing a variable of TABLE REF type in a DML statement, you must specify a correlation name for results.

When you specify a table reference variable in a statement, the table is looked up immediately before the statement is executed.

Table reference variables can only accessed by their creator.

Creating a table reference variable does not create a dependence between the variable and the underlying table, and DDL statements can still be performed on tables referenced by a table reference variable.

If a table is dropped, then any table reference variables that refer to it are invalidated; an attempt to use an invalid table reference variable returns an error.

When executing a statement that acts on a table specified by using a table reference variable, you need the appropriate privileges on the underlying table referenced by the variable.

Restrictions on the use of table reference variables:

Table reference variables cannot be used in a SELECT or DML statement if the variable resolves to the NULL value.
Table reference variables cannot be used to specify tables in DDL statements.
Table reference variables cannot be used as columns in base tables, temporary tables, or views.
Table reference variables cannot be used in a top-level SELECT block or query expression that is returned to a client.
Table reference variables cannot be combined with other types of variables in built-in functions that require a common super-type for the parameters.
Table reference variables cannot be ordered or used as part of calculations or comparisons except for equality and inequality.

The table reference variable functionality overlaps with indirect identifier functionality; both are ways of indirectly referring to a table. However, a table reference is resolved at creation time and remains a valid reference, whereas an indirect reference is resolved when the statement that references it is executed and therefore may not be a valid reference.

Additionally, a table reference can provide access to a table that is not accessible in the current context, whereas an indirect identifier cannot. For example, suppose your procedure creates, and then refers to, a local table that has the same name as a base table. Now let's say you need to refer to the base table from within the procedure. An indirect identifier for the table would resolve to the local table, which is not what you want. To precisely identify the base table instead, use a table reference variable. For example:

CREATE OR REPLACE PROCEDURE PROC1()
BEGIN
	DECLARE LOCAL TEMPORARY TABLE myTab (x INTEGER, y INTEGER);
	DECLARE tab_ref TABLE REF = TABLE REF (myTab);
	INSERT INTO myTab VALUES (1,100), (2,200), (3,300);
	SELECT * FROM PROC2( tab_ref );
END;

CREATE OR REPLACE PROCEDURE PROC2( IN @tab_ref TABLE REF )
RESULT (v1 LONG VARCHAR, v2 INTEGER)
BEGIN
	DECLARE LOCAL TEMPORARY TABLE myTab ( pk INTEGER, val LONG VARCHAR );
	INSERT INTO myTab VALUES( 1, 'apple'), (3, 'pear'), (10, 'banana');
	SELECT val,T2.y FROM myTab T1 JOIN TABLE REF( @tab_ref ) AS T2 ON T2.x = T1.pk;
END;
CALL PROC1;

Table 1: Result:
v1	v2
apple	100
pear	300

The myTab table in PROC2 shadows (hides) myTab that was created in PROC1, so the only table accessible by using the name myTab in PROC2 would be the locally declared myTab. Using a table reference (TABLE REF( @tab_ref )) more precisely identifies the object being joined to (in this example, the table created in PROC1.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following example declares a table reference variable, @ref, sets it to the GROUPO.Employees table reference, and then queries the table using the table reference variable:

DECLARE @ref TABLE REF = TABLE REF ( GROUPO.Employees )
SELECT * FROM TABLE REF ( @ref ) AS T;

The following example creates a table reference variable called @tableDefinition and sets it to the GROUPO.Employees table reference, and then selects from the table using the table reference variable:

CREATE VARIABLE @tableDefinition TABLE REF;
SET @tableDefinition = TABLE REF ( GROUPO.Employees );
SELECT * FROM TABLE REF ( @tableDefinition ) AS T;

The following code snippet declares a variable named @myTableRefVariable1 with the TABLE REF data type and sets it to the GROUPO.Employees table reference:

DECLARE @myTableRefVariable1 TABLE REF;
SET @myTableRefVariable1 = TABLE REF ( GROUPO.Employees );

The following example creates a TABLE REF variable, sets it to the GROUPO.Employees table, and then queries the table reference variable for employees with birthdays in the month of February:

CREATE VARIABLE @myTableRefVariable2 TABLE REF;
SET @myTableRefVariable2 = TABLE REF ( GROUPO.Employees );
SELECT T.surname, T.givenname, T.birthdate FROM TABLE REF ( @myTableRefVariable2 ) AS T
    WHERE MONTH( T.birthdate ) = 2;

Table 2: Results
surname	givenname	birthdate
Davidson	Jo Ann	1957-02-17
Samuels	Peter	1968-02-28
Barker	Joseph	1969-02-14
Sterling	Paul	1950-02-27

The following example shows a table reference variable (@myTableRefVariable3) being used in several statements to update the GROUPO.Employees table. Notice that a correlation name (T, in this example) is required when specifying a table using a table reference variable in a DML statement:

CREATE VARIABLE @myTableRefVariable3 TABLE REF;
SET @myTableRefVariable3 = TABLE REF ( GROUPO.Employees );
UPDATE TABLE REF ( @myTableRefVariable3 ) AS T SET T.GivenName = REPLACE( GivenName, 'Fran', 'Francis' );
DELETE FROM TABLE REF ( @myTableRefVariable3 ) AS T WHERE Surname = 'Holmes';
SELECT * FROM TABLE REF ( @myTableRefVariable3 ) AS T;

The following example shows how you can use table reference variables in a procedure:

CREATE OR REPLACE PROCEDURE leapday_births( IN @tab TABLE REF )
RESULT ( Surname person_name_t, GivenName person_name_t, Birthdate TIMESTAMP)
BEGIN
    SELECT Surname, GivenName, Birthdate FROM TABLE REF ( @tab ) AS T
        WHERE MONTH( Birthdate ) = 02 AND DAY( Birthdate ) = 29;
END;
CREATE VARIABLE @myTableRefVariable4 TABLE REF;
SET @myTableRefVariable4 = TABLE REF ( GROUPO.Employees );
CALL leapday_births(@myTableRefVariable4);