Oracle Learning Series II (session and Transaction level staging table)

--Create Session temp Table
Create global temporary table tmp_user_session (user_id int, user_name varchar2 (), User_email varchar2 (30))
--This sentence indicates that the data is persisted when the transaction commits
On commit Preserve rows

--inserting data into the staging table
Insert into tmp_user_session (User_id,user_name,user_email) VALUES (1, ' Sun Yebao ', ' [email protected] ')
Insert into tmp_user_session (User_id,user_name,user_email) VALUES (1, ' Lisa Wong ', ' [email protected] ')
Commit
--Query with data
SELECT * FROM Tmp_user_session
--Reopen a SQL window and query again no data. Select * from tmp_user_session indicates that this inserted data only exists for this window session




--Create a transaction temp table
Create global temporary table tmp_users_transaction (user_id int,user_name varchar2), User_email varchar2 (30))
-This sentence means that data is deleted when a transaction commits
On commit Delete rows

--inserting data into the staging table
Insert into tmp_users_transaction (User_id,user_name,user_email) VALUES (1, ' Sun Yebao ', ' [email protected] ');
Insert into tmp_users_transaction (User_id,user_name,user_email) VALUES (1, ' Lisa Wong ', ' [email protected] ')

--Query with data
SELECT * FROM Tmp_users_transaction
--Commit the next statement or rollback the transaction rollback query SELECT * FROM Tmp_users_transaction there is no data because the transaction temp table is the data that was emptied when the transaction was committed.
Commit;rollback;

Oracle Learning Series II (session and Transaction level staging table)