Alternative for a MySQL temporary table in Oracle

I noticed that the concept of temporary tables in the开发者_StackOverflow社区se two systems is different, and I have a musing.. I have the following scenario in MySQL:

1. Drop temporary table 'a' if exists
2. Create temporary table 'a'
3. Populate it with data through a stored procedure
4. Use the data in another stored procedure

How can I implement the same scenario in Oracle? Can I (in one procedure preferable) create a temporary table, populate it, and insert data in another (non-temporary) table?

I think that I can use a (global) temporary table which truncates on commit, and avoid steps 1&2, but I need someone else's opinion too.

In Oracle, you very rarely need a temporary table in the first place. You commonly need temporary tables in other databases because those databases do not implement multi-version read consistency and there is the potential that someone reading data from the table would be blocked while your procedure runs or that your procedure would do a dirty read if it didn't save off the data to a separate structure. You don't need global temporary tables in Oracle for either of these reasons because readers don't block writers and dirty reads are not possible.

If you just need a temporary place to store data while you perform PL/SQL computations, PL/SQL collections are more commonly used than temporary tables in Oracle. This way, you're not pushing data back and forth from the PL/SQL engine to the SQL engine and back to the PL/SQL engine.

CREATE PROCEDURE do_some_processing
AS
  TYPE emp_collection_typ IS TABLE OF emp%rowtype;
  l_emps emp_collection_type;

  CURSOR emp_cur
      IS SELECT *
           FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur 
     BULK COLLECT INTO l_emps
    LIMIT 100;

    EXIT WHEN l_emps.count = 0;

    FOR i IN 1 .. l_emps.count
    LOOP
      <<do some complicated processing>>
    END LOOP;
  END LOOP;
END;

You can create a global temporary table (outside of the procedure) and use the global temporary table inside your procedure just as you would use any other table. So you can continue to use temporary tables if you so desire. But I can count on one hand the number of times I really needed a temporary table in Oracle.

You are right, temporary tables will work work you.

If you decide stick with regular tables you may want to use the advice @Johan gave, along with

ALTER TABLE <table name> NOLOGGING;

to make this perform a bit faster.

I see no problem in the scheme your are using.
Note that it doesn't have to be a temp-table, you can use a sort of kind of memory table as well.

Do this by creating a table as usual, then do

ALTER TABLE <table_name> CACHE;  

This will prioritize the table for storage in memory.

As long as you fill and empty the table in short order you don't need to do step 1 & 2.
Remember the cache modifier is just a hint. The table still ages in the cache and will be pushed out of memory eventually.

Just do:

1. Populate cache-table with data through a stored procedure

2. Use the data in another stored procedure, but don't wait to long. 2a. Clear the data in the cache table.

In your MySQL version, I didn't see a step 5 to drop the table a. So, if you want or don't mind having the data in the table persist you could also use a materialized view and simply refresh on demand. With a materialized view you do not need to manage any INSERT statements, just include the SQL:

CREATE MATERIALIZED VIEW my_mv
NOCACHE -- NOCACHE/CACHE: Optional, cache places the table in the most recently used part of the LRU blocks
BUILD IMMEDIATE  -- BUILD DEFERRED or BUILD IMMEDIATE
REFRESH ON DEMAND
WITH PRIMARY KEY -- Optional: creates PK column
AS
SELECT * 
FROM ....;

Then in your other stored procedure, call:

BEGIN   
    dbms_mview.refresh ('my_mv', 'c'); -- 'c' = Complete
END;

That said, a global temporary table will work as well, but you manage the insert and exceptions.