Temporary Table:
The concept of temporary table helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited.
CREATE GLOBAL TEMPORARY TABLE my_global_temp_table (
Column1 INTEGER,
Column2 VARCHAR(10),
Column3 VARCHAR(20)
);
Local Temporary Table:
CREATE LOCAL TEMPORARY TABLE #my_local_temp_table (
Column1 INTEGER,
Column2 VARCHAR(10),
Column3 VARCHAR(20)
);
* For local temporary table you will get an error like:
The concept of temporary table helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited.
- Temporary Tables lets you store and process intermediate results.
- Temporary tables only last as long as the session is alive.
- Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
- Global Temporary Table
- Local Temporary Table
- Table definition is globally available to any connection once created. Means metadata of the table is shared across sessions.
- The data in a global temporary table is session-specific meaning that data inserted by a session can only be accessed by that session.
- The table is dropped when the last connection using it is closed.
CREATE GLOBAL TEMPORARY TABLE my_global_temp_table (
Column1 INTEGER,
Column2 VARCHAR(10),
Column3 VARCHAR(20)
);
Local Temporary Table:
- Temporary table is visible only in the current session.
- Data in a local temporary table is session-specific meaning only the owner session of the local temporary table is allowed to insert/read/truncate the data.
- It exists for the duration of the session and data from the local temporary table is automatically dropped when the session is terminated.
- Local temporary table name is stared with hash ("#") sign.
CREATE LOCAL TEMPORARY TABLE #my_local_temp_table (
Column1 INTEGER,
Column2 VARCHAR(10),
Column3 VARCHAR(20)
);
Example 1: Temporary tables in SQL Editor.
This example just shows the use and scope details of temporary tables.
In real time scenario we rarely use temporary tables directly in SQL editor. They are mainly used inside procedures.
* Open SQL editor and execute following queries.
--Create local temporary table
CREATE LOCAL TEMPORARY TABLE #MY_LOCAL_TEMP_TABLE( ID INTEGER, NAME VARCHAR(10));
--Create global temporary table
CREATE GLOBAL TEMPORARY TABLE MY_GLOBAL_TEMP_TABLE( ID INTEGER, NAME VARCHAR(10));
-- Insert few records in local temporary table
INSERT INTO #MY_LOCAL_TEMP_TABLE VALUES (1,'A');
INSERT INTO #MY_LOCAL_TEMP_TABLE VALUES (2,'B');
-- Insert few records in global temporary table
INSERT INTO MY_GLOBAL_TEMP_TABLE VALUES (3,'C');
INSERT INTO MY_GLOBAL_TEMP_TABLE VALUES (4,'D');
* This will create one local temporary table and one global temporary table.
Now run the below queries in the same SQL Editor.
SELECT ID, NAME FROM #MY_LOCAL_TEMP_TABLE;
SELECT ID, NAME FROM MY_GLOBAL_TEMP_TABLE;
* You will see the output of both tables as:
* Now open another SQL Editor. Note that new SQL Editor means "a new session".
Run below queries again here.
SELECT ID, NAME FROM #MY_LOCAL_TEMP_TABLE;
SELECT ID, NAME FROM MY_GLOBAL_TEMP_TABLE;
"Could not find table/view #MY_LOCAL_TEMP_TABLE"
* For global temporary table query will work but output will not contain any data.
Conclusion:
- For global temporary table table definition is globally available to any connection once created. But local temporary table is available only to current session.
- The data in a global temporary table is session-specific meaning that data inserted by a session can only be accessed by that session.
Example 2: Use of temporary tables in stored procedure.
Replace <SCHEMA_NAME> with you schema.
-- create a table type to be used in procedure
CREATE TYPE <SCHEMA_NAME>."TT_OUTPUT" AS TABLE (
ID INTEGER,
NAME VARCHAR (10)
);
CREATE PROCEDURE <SCHEMA_NAME >."DEMO_PROC"(
IN ID INTEGER,
OUT OUTPUT_TABLE <SCHEMA_NAME >."TT_OUTPUT" )
LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
DECLARE
NEW_ID INTEGER;
CREATE LOCAL TEMPORARY TABLE "#TEST_TABLE" (ID INTEGER, NAME VARCHAR(10));
INSERT INTO "#TEST_TABLE" VALUES(:ID,'A');
NEW_ID := :ID + 1;
INSERT INTO "#TEST_TABLE" VALUES(:NEW_ID,'B');
OUTPUT_TABLE = SELECT ID, NAME FROM "#TEST_TABLE";
DROP TABLE "#TEST_TABLE";
END;
CALL <SCHEMA_NAME >."DEMO_PROC" (1, ?);
To know more about Procedure, check the articles:
No comments:
Post a Comment