Temporary table usage in Oracle databases

This article mainly introduces the Oracle database of temporary table usage, I hope to be helpful to everyone's study and work.

One: Grammar webjx.com

In Oracle, you can create the following two types of temporary tables:

(1) Session-specific temporary table webjx.com

CREATE GLOBAL Temporary ()

On COMMIT PRESERVE ROWS;

(2) A transaction-specific temporary table

CREATE GLOBAL Temporary ()

On COMMIT DELETE ROWS;

CREATE GLOBAL temporary TABLE mytemptable webjx.com

The temporary table is created, but if you insert a record and then use a different connection to mount the Select, the record is empty. Web Teaching Network

--on COMMIT Delete rows Description The temporary table is a transaction specified, and Oracle truncates the table after each commit (delete all rows)

Webjx.com

--on COMMIT PRESERVE ROWS indicates that the temporary table is a session-specific, and Oracle truncates the table when the session is interrupted.

II: Dynamic creation of webjx.com

Create or Replace procedure pro_temp (v_col1 varchar2,v_col2 varchar2) as

V_num number;

Begin

Select COUNT (*) into V_num from
User_tables where table_name= ' t_temp ';

--create temporary table

If V_num<1 Then

Execute immediate ' CREATE GLOBAL temporary TABLE t_temp (

COL1 VARCHAR2 (10),

COL2 VARCHAR2 (10)

On COMMIT delete ROWS ';

End If;

--insert data

Execute immediate ' insert into t_temp values
(' ' v_col1 ' ', ' ' ', ' ' v_col2 ' ', ' "') ';

Execute immediate ' select col1 from T_temp ' into v_num;

Dbms_output.put_line (V_num);

Execute immediate ' delete from T_temp ';

Commit

Execute immediate ' drop table t_temp ';

End Pro_temp;

　

Testing: Web-page Teaching Network

15:23:54 sql> set Serveroutput on

15:24:01 sql> exec pro_temp (' 11 ', ' 22 ');

11

The PL/SQL process has completed successfully.

Time used: 00:00:00.79

15:24:08 sql> desc t_temp;

ERROR:

ORA-04043: Object t_temp does not exist webjx.com

Three: Features and performance (compared to normal tables and views)

Temporary tables are valid only within the current connection

Temporary tables are not indexed, so it is not recommended to use webjx.com if the amount of data is larger or multiple queries are made.

When the data processing is more complicated, the table is fast, whereas the view is quick.

A cursor is suggested when querying data only: Open cursor for ' SQL clause ';

Webjx.com

This article from: Web Teaching Network (www.webjx.com) original link: http://www.webjx.com/database/oracle/database_oracle_2007_12_22_2384.html