oracle| temporary table
Note: Some of the instructions and sample code in the following section are excerpted from CSDN, and I would like to express our thanks to the relevant authors. If the author has objections, please write a letter stating
1 syntax in Oracle, you can create the following two types of temporary tables:
1 session-specific temporary tables
CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>)
On COMMIT PRESERVE ROWS;
2 transaction-specific temporary tables
CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>)
On COMMIT DELETE ROWS;
CREATE GLOBAL Temporary TABLE mytemptable
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.
--on COMMIT Delete rows Description The temporary table is a transaction specified, and Oracle truncates the table after each commit (delete all rows)
--on COMMIT PRESERVE ROWS indicates that the temporary table is a session-specific, and Oracle truncates the table when the session is interrupted.
2 Dynamic Creation
Create or Replace procedure pro_temp (v_col1 varchar2,v_col2 varchar2) as
V_num number;
Begin
Select COUNT (*) into the 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;
Test:
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
3 features and performance (compared to normal tables and views) temporary tables are not indexed for temporary tables only within the current connection. Therefore, if the amount of data is large or multiple queries, it is not recommended to use data processing more complex when the table is fast, whereas the view quickly in the query data only when the proposed cursor: Open cursor For ' SQL clause ';
Welcome to add!
