oracle stored procedure temporary table

In Oracle database, a stored procedure is a set of predefined SQL statements that can receive input parameters and return output values. They are used to perform specific tasks, usually to improve performance and maintainability in Oracle databases, and to achieve tight integration with business logic.

Temporary table is a temporary storage space used during the query process. In Oracle database, temporary tables can be created using the CREATE GLOBAL TEMPORARY TABLE statement. Such tables are automatically deleted at the end of a session and can be used as intermediate results for multiple queries within a single session.

Over the past few years, the combination of stored procedures and temporary tables has become one of the best practices for executing efficient and complex queries. For example, in large data warehouses, complex ETL (extraction, transformation and loading) operations can be implemented using stored procedures and temporary tables, and have good performance in terms of performance and maintainability.

In this article, we will introduce how to create a temporary table in Oracle database and use it with stored procedures.

Create a temporary table

To create a temporary table in the Oracle database, you can use the following CREATE GLOBAL TEMPORARY TABLE statement:

CREATE GLOBAL TEMPORARY TABLE temp_table_name
(
  column_name1  datatype1,
  column_name2  datatype2,
  column_name3  datatype3,
  ...
)
ON COMMIT DELETE ROWS;

In this statement:

• temp_table_name is the name of the temporary table you want to create.
• column_name is the column name in the table, multiple columns can be defined.
• datatype is the data type of each column.

Note that in this statement, ON COMMIT DELETE ROWS refers to deleting all rows in the temporary table when the transaction is committed or the session is closed. This ensures that each session uses an empty table and frees system resources at the end of the session.

Using Temporary Tables

Once you create a temporary table, you can use it in a stored procedure. For example, if you need to define an intermediate result set in a stored procedure, you can use the following steps:

1. Define a temporary table in a stored procedure:

CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
END my_procedure;

In this In the example, I defined a temporary table named temp_table in the stored procedure, including two columns col1 and col2, and used ON COMMIT DELETE ROWS option to define it.

2. Insert data into the temporary table in the stored procedure:

CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
  
  INSERT INTO temp_table (col1, col2)
  VALUES (1, 'Hello');
  
  INSERT INTO temp_table (col1, col2)
  VALUES (2, 'World');
  
  COMMIT;
END my_procedure;

In this example, I inserted two rows of data into the temporary table. Note that the COMMIT statement here is required because temporary tables in the Oracle database are only available when the transaction is committed.

3. Querying the temporary table in a stored procedure:

CREATE OR REPLACE PROCEDURE my_procedure IS
  cur1 SYS_REFCURSOR;
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
  
  INSERT INTO temp_table (col1, col2)
  VALUES (1, 'Hello');
  
  INSERT INTO temp_table (col1, col2)
  VALUES (2, 'World');
  
  COMMIT;
  
  OPEN cur1 FOR
  SELECT *
  FROM temp_table;
  
  -- Use the result set
  -- ...
END my_procedure;

In this example, I used the SYS_REFCURSOR data type to declare a result set pointer . I then opened a cursor in the stored procedure, used the SELECT statement to retrieve the data from the temporary table, and returned the result set to the program that called the stored procedure. After this, you can use the result set like any other cursor.

Conclusion

In Oracle database, stored procedures and temporary tables have become a best practice for executing complex queries. Using temporary tables in conjunction with stored procedures can greatly improve query performance and maintainability, and achieve tight integration with business logic. In practical applications, it is recommended to carefully design your temporary tables and stored procedures and ensure that they can achieve your needs.

The above is the detailed content of oracle stored procedure temporary table. For more information, please follow other related articles on the PHP Chinese website!