GLOBAL TEMP TABLES
Temporary tables are much like an ordinary table, with an only vital difference of not having foreign keys related to other tables. It contains all the attributes of a table including information about rows and block, triggers, join cardinality, etc.
Syntax: To create a Global Temporary Table with a single column.
CREATE GLOBAL TEMPORARY TABLE table_name ( column_name data_type [ NULL | NOT NULL ]);
Syntax: To create a Global Temporary Table with multiple columns.
CREATE GLOBAL TEMPORARY TABLE table_name ( column_1 data_type [ NULL | NOT NULL ], column_2 data_type [ NULL | NOT NULL ], ... column_n data_type [ NULL | NOT NULL ] );
Parameters:
table_name: It is used to specify the name of the global temporary table to be created.
column definition:
It is used to specify the name of the column to be created in the global temporary table. The next definition is to specify the datatype of the column. The column constraint must also be defined as either NULL or NOT NULL. The column constraint holds a default value of NULL.
Example: Creating a GLOBAL TEMPORARY TABLE with multiple columns.
CREATE GLOBAL TEMPORARY TABLE retailers ( retailer_id numeric(10) NOT NULL, retailer_name varchar2(30) NOT NULL, retailer_city varchar2(50) NULL, retailer_state varchar2(50) ); |
Explanation:
A global temporary table called RETAILERS will be created. The next would be the creation of the four columns of the table, namely, the retailer_id, retailer_name, retailer_city and the retailer_state. The first column is of a NUMERIC data type with a maximum limit of 10 digits and it doesn’t accept NULL values. The second column is VARCHAR data type with a maximum limit of 30 characters and it also doesn’t accept NULL values. The third column is also of VARCHAR datatype with a maximum limit of 50 characters and it does accept NULL values. Similarly, the fourth column is also of VARCHAR datatype with a maximum limit of 50 characters and it also does accept NULL values.