Oracle, sqlserver--temp table

First, overview:

Features of the Oracle Database temp table:

• Temporary tables are saved in temp by default;
• The table structure persists until it is deleted; that is, it is created once and used permanently;
• Primary foreign keys are not supported.
• You can index temporary tables and establish views on a temporary table basis. Indexes that are built on temporary tables are also temporary and valid only for the current session or transaction. Temporary tables can have triggers.

Temporal tables are divided into transactional and session-type

• Session Type: A session-based temporal table in which data is valid from the beginning of a session to the end of a session, and the data in the table is automatically emptied when the conversation ends. The data between sessions is isolated and non-affected.
• Transactional: A transaction-based temporal table, which is more flexible than a session-type temporal table, can be thought of as an optimization from a session-type temporal table because the data in the table is saved at the same time as the session, and the validity period starts at the end of the session and the database automatically empties the data in the staging table. Unlike session temporary tables , the data in the staging table is emptied when the transaction commits or the transaction is rolled back . Of course, the session temp table can use the delete temporary table name during the session to empty the temporary table data.

Grammar:

Create global temporary table name
(
......
)
On commit [preserve|delete] rows;　

• Preserve is a session-type temporal table
• Delete is a transactional (TRANSACTION) type of temporary table

The temporary table data at the transaction level disappears after the tranaction ends, that is, the Commit/rollback or end session clears the tranaction temporal table data.

Second, example:

1. Create a temporary table

DECLARE tempisexist integer:=0;
Begin
Select COUNT (*) into tempisexist from all_tables where table_name= ' NK_SLTJ ';
If tempisexist=0 then--does not exist a temporary table creates a
Execute immediate ('
CREATE GLOBAL Temporary TABLE NK_SLTJ
(
LCK_FJNM varchar (36),
LCMC varchar (70),
GFX Integer,
ZFX Integer,
DFX Integer,
KZDSL integer
)
On commit preserve rows '--preserve represents the reply level.
);
End If;
End

Ii. Use of temporary tables

Declare
FXZ number;
FJNM varchar (36);
ZZNM varchar: = ' 77c48880-a2be-4d3c-97b7-26f8de0bee63 ';
CURSOR nkfxzcur is select nkfxjz_fxz,nklck_fjnm from NKFXJZ INNER JOIN nklck on NKLCK_NM=NKFXJZ_LCNM where nklck_zznm=zznm ;
Begin
Delete NK_SLTJ; --Prevents data duplication during multiple executions in a unified session, so the program should empty the staging table data at the outset
Insert INTO NK_SLTJ SELECT nklck_fjnm, Nklck_mc,0,0,0,kzd from--inserts data into the staging table.
(Select KZDSL. Nklck_fjnm,kzdsl. KZD,LCJZ. NKLCK_MC from
(select substr (nklck_fjnm,1,4) as NKLCK_FJNM, Count (Nknkjz_kzd) as KZD from Nklck left join NKNKJZ on NKNKJZ_LCNM =nklck_n M WHERE nklck_zznm=zznm GROUP by substr (nklck_fjnm,1,4)) KZDSL
INNER JOIN
(select Nklck_fjnm, nklck_mc from Nklck WHERE nklck_jc=1 and NKLCK_ZZNM=ZZNM) LCJZ on LCJZ. Nklck_fjnm=kzdsl. NKLCK_FJNM) CCC;

Open nkfxzcur; --Open cursor
Fetch nkfxzcur into fxz,fjnm; --Extracting cursor data
While Nkfxzcur%found loop--loop
If fxz>=3.5 and fxz<=5 then--high risk
Update NK_SLTJ set gfx=gfx+1 where LCK_FJNM=SUBSTR (fjnm,0,4);
elsif fxz>2 and fxz<3.5 then--Medium risk
Update NK_SLTJ set zfx=zfx+1 where LCK_FJNM=SUBSTR (fjnm,0,4);
Else
If Fxz>=0 and fxz<=2 then--Low risk
Update NK_SLTJ set dfx=dfx+1 where LCK_FJNM=SUBSTR (fjnm,0,4);
End If;
End If;
Fetch nkfxzcur into fxz,fjnm;
End loop;--Loop
Close nkfxzcur; --Close cursor
End

Iii. differences from the SQL Server temp table

Oracle

• There are two kinds of Transaction table and session table;
• The temporary table retains the table structure forever, and the data is saved on disk. The only difference from the permanent table is that the data is deleted after the end of the user session or after the transaction commits and rolls back, but the table structure is preserved;

SQL Server is a local and global temporary table.

• The table name begins with #, and the table structure is completely removed at the end of the session, and the different users are isolated from each other;
• Global tables: Table names start with # # and are visible to all users, and when a user creates a global table, other users can access the global table, but when a user is created to end a reply, the global table accepts access to the existing Connection global table, but no longer accepts access from the new user, and the table structure is completely removed when the connected user disconnects.
• SQL Server: Created manually when temporary tables are needed, data is saved in memory, and the table structure is dropped directly at the end of the session.

Oracle, sqlserver--temp table