Create temporary table in PL / SQL

Question

I am working with an Oracle 10g database and want to retrieve a group of records from a single table, and then use it to retrieve records from several related tables.

If it were T-SQL, I would do it something like this:

CREATE TABLE #PatientIDs ( pId int ) INSERT INTO #PatientIDs select distinct pId from appointments SELECT * from Person WHERE Person.pId IN (select pId from #PatientIDs) SELECT * from Allergies WHERE Allergies.pId IN (select pId from #PatientIDs) DROP TABLE #PatientIDs 

However, all the useful pages I look at make it a lot more time consuming than it might be, so I think I'm missing something obvious.

(By the way, instead of running this as a single script, I will probably open a session in Oracle SQL Developer, create a temporary table, and then run each query from it, exporting them to CSV as I move. Will this work?)

Answer 1

Oracle has temporary tables, but they require explicit creation:

 create global temporary table... 

The data in the temporary table is private to the session that created it, and may be session specific or transaction specific. If the data is not deleted before the session ends, you need to use ON COMMIT PRESERVE ROWS at the end of the create statement. There is also no rollback or support for them ...

In the example you specified, I do not see the need for temp tables - it risks that updates made to the APPOINTMENTS table from the moment the temp table is entered will not be reflected. Use IN / EXISTS / JOIN:

 SELECT p.* FROM PERSON p WHERE EXISTS (SELECT NULL FROM APPOINTMENTS a WHERE a.personid = a.id) SELECT p.* FROM PERSON p WHERE p.personid IN (SELECT a.id FROM APPOINTMENTS a) SELECT DISTINCT p.* FROM PERSON p JOIN APPOINTMENTS a ON a.id = p.personid 

JOINING risk duplication if there are multiple APPOINTMENT records associated with one PERSON record, so I added DISTINCT.

Answer 2

Oracle cannot accidentally create temporary tables in the same way as SQL Server. You must explicitly create a table in the database schema ( create global tempory table ). It also means that you need permissions to create tables, and the script must be explicitly deployed as a database change. The table is also visible in the global namespace.

This is a significant idiomatic difference between Oracle programming and SQL Server. Idiomatic T-SQL can make extensive use of temporary tables, and the present requirements for writing procedural T-SQL code are quite rare, mainly because of this tool.

Idiomatic PL / SQL discards procedural code much faster, and you probably would be better off doing this than trying to fake temporary tables. Note that PL / SQL has performance-oriented constructs such as flow control for explicit parallel processing on cursors and nested result sets (cursor expressions); In recent versions there is a JIT compiler.

You have access to a range of tools for quickly executing PL / SQL procedural code, and this is possibly idiomatic PL / SQL programming. The underlying paradigm is somewhat different from T-SQL, and the approach to temporary tables is one of the highlights when the system architecture and programming idioms are different.

Answer 3

While the exact problem was resolved, if you want to create some useful skills in this area, I would take a look at PL / SQL collections and, in particular, bulk SQL operations using pl / sql collections (BULK COLLECT / Bulk Binds) , a RETURNING clause, and defining collections using% ROWTYPE.

You can significantly reduce the amount of pl / sql code written by understanding all of the above, although always remember that an all-SQL solution will almost always beat PL / SQL.