Explore how to use Oracle to query temporary tables

In Oracle database, a temporary table is a temporary table used to store temporary data. Unlike ordinary tables, they are automatically deleted after the session ends. Temporary tables can use SELECT, INSERT, UPDATE, DELETE and other operations like ordinary tables, but their data is only visible to the current session and cannot be accessed in other sessions. In this article, we will explore how to query temporary tables using Oracle.

Create temporary table

In Oracle database, you can use the CREATE GLOBAL TEMPORARY TABLE or CREATE TEMPORARY TABLE statement to create a temporary table. The two statements are very similar, the main difference is that CREATE GLOBAL TEMPORARY TABLE creates a global temporary table, and CREATE TEMPORARY TABLE creates a session temporary table. The following is an example of creating a session temporary table:

CREATE GLOBAL TEMPORARY TABLE temp_table (
  id NUMBER(10),
  name VARCHAR2(30)
) ON COMMIT PRESERVE ROWS;

This statement will create a temporary table named temp_table, containing two columns, id and name, and the data will be deleted at the end of the session. The ON COMMIT PRESERVE ROWS option specifies that the data will be retained after submission, so the temporary table can be used multiple times during the session.

Querying temporary tables

Querying temporary tables is very similar to querying ordinary tables. The following is an example of querying a temporary table:

SELECT * FROM temp_table;

This will return all rows and columns in temp_table.

If you want to share temporary data between multiple sessions, you can use the CREATE GLOBAL TEMPORARY TABLE statement to create a global temporary table. The following is an example of using a global temporary table:

CREATE GLOBAL TEMPORARY TABLE temp_table (
  id NUMBER(10),
  name VARCHAR2(30)
) ON COMMIT PRESERVE ROWS;

-- 在会话1中插入数据
INSERT INTO temp_table VALUES (1, 'Alice');
COMMIT;

-- 在会话2中查询数据
SELECT * FROM temp_table;

-- 在会话1中插入更多的数据
INSERT INTO temp_table VALUES (2, 'Bob');
COMMIT;

-- 再次在会话2中查询数据
SELECT * FROM temp_table;

In this example, the global temporary table temp_table is created in session 1, and then some data is inserted in session 1. Session 2 queries all rows and columns in temp_table through the SELECT statement. During this query, session 1 continues inserting data into temp_table and retains the data after submission. Finally, when I query temp_table again in session 2, I only see the previously inserted rows, but not the later ones.

In Oracle database, the syntax for querying temporary tables is the same as that for querying ordinary tables. The main advantage of temporary tables is that they are automatically deleted after the session ends, so manual cleanup work can be avoided when working with temporary data.

The above is the detailed content of Explore how to use Oracle to query temporary tables. For more information, please follow other related articles on the PHP Chinese website!