DECLARE GLOBAL TEMPORARY TABLE Examples
1. Create a temporary table.
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE
emps
(name CHAR(20) , empno CHAR(5))
ON COMMIT PRESERVE ROWS
WITH NORECOVERY,
LOCATION = (personnel),
NODUPLICATES,
ALLOCATION=100,
EXTEND=100;
2. Use a subselect to create a temporary table containing the names and employee numbers of the highest-rated employees.
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE
emps_to_promote
AS SELECT name, empno FROM employees
WHERE rating >= 9
ON COMMIT PRESERVE ROWS
WITH NORECOVERY