在 PL/SQL 中创建临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3462131/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a temp table in PL/SQL
提问by SarekOfVulcan
I'm working with an Oracle 10g database, and I want to extract a group of records from one table, and then use that for pulling records out of a bunch of related tables.
我正在使用 Oracle 10g 数据库，我想从一个表中提取一组记录，然后使用它从一堆相关表中提取记录。
If this were T-SQL, I'd do it something like this:
如果这是 T-SQL，我会这样做：
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 helpful pages I look at make this look like a lot more work than it could possibly be, so I think I must be missing something obvious.
然而，我看到的所有有用的页面都让这看起来比它可能做的工作要多得多，所以我想我一定遗漏了一些明显的东西。
(BTW, instead of running this as one script, I'll probably open a session in Oracle SQL Developer, create the temp table, and then run each query off it, exporting them to CSV as I go along. Will that work?)
（顺便说一句，我可能不会将它作为一个脚本运行，我可能会在 Oracle SQL Developer 中打开一个会话，创建临时表，然后运行每个查询，然后将它们导出到 CSV 中。这会起作用吗？）
回答by OMG Ponies
Oracle has temporary tables, but they require explicit creation:
Oracle 有临时表，但它们需要显式创建：
create global temporary table...
The data in a temporary table is private for the session that created it and can be session-specific or transaction-specific. If data is notto be deleted until the session ends, you need to use ON COMMIT PRESERVE ROWSat the end of the create statement. There's also no rollback or commit support for them...
临时表中的数据对于创建它的会话是私有的，可以是特定于会话或特定于事务的。如果数据不被直到会话结束删除，您需要使用ON COMMIT PRESERVE ROWS在创建语句的结束。也没有对它们的回滚或提交支持......
I see no need for temp tables in the example you gave - it risks that updates made to the APPOINTMENTStable since the temp table was populating won't be reflected. Use IN/EXISTS/JOIN:
在您提供的示例中，我认为不需要临时表 - 存在APPOINTMENTS自临时表填充以来对表所做的更新不会被反映的风险。使用 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 risks duplicates if there are more than one APPOINTMENT records associated to a single PERSON record, which is why I added the DISTINCT.
如果有多个 APPOINTMENT 记录与单个 PERSON 记录相关联，则 JOINing 有重复的风险，这就是我添加 DISTINCT 的原因。
回答by JulesLt
While the exact problem has been solved, if you want to build up some useful skills in this area, I would take a look at PL/SQL Collections, and particularly bulk SQL operations using pl/sql collections (BULK COLLECT / Bulk Binds), the RETURNING clause, and defining collections using %ROWTYPE.
虽然确切的问题已经解决，但如果你想在这方面建立一些有用的技能，我会看看 PL/SQL 集合，特别是使用 pl/sql 集合的批量 SQL 操作（BULK COLLECT / Bulk Binds）， RETURNING 子句，并使用 %ROWTYPE 定义集合。
You can dramatically reduce the amount of pl/sql code you write through understanding all the above - although always remember that an all-SQL solution will almost always beat a PL/SQL one.
通过理解以上所有内容，您可以显着减少编写的 pl/sql 代码量 - 尽管始终记住全 SQL 解决方案几乎总是胜过 PL/SQL 解决方案。
回答by ConcernedOfTunbridgeWells
Oracle doesn't have the facility to casually create temporary tables in the same way as SQL Server. You have to create the table explicitly in the database schema (create global tempory table). This also means that you need permissions that allow you to create tables, and the script must explicitly be deployed as a database change. The table is also visible in a global name space.
Oracle 没有像 SQL Server 那样随意创建临时表的功能。您必须在数据库模式 ( create global tempory table)中显式创建表。这也意味着您需要允许您创建表的权限，并且必须将脚本显式部署为数据库更改。该表在全局命名空间中也是可见的。
This is a significant idiomatic difference between Oracle and SQL Server programming. Idiomatic T-SQL can make extensive use of tempory tables and genuine requirements to write procedural T-SQL code are quite rare, substantially because of this facility.
这是 Oracle 和 SQL Server 编程之间的显着惯用差异。惯用的 T-SQL 可以广泛使用临时表，而真正需要编写过程性 T-SQL 代码的需求非常少，主要是因为这种便利。
Idiomatic PL/SQL is much quicker to drop out to procedural code, and you would probably 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 over cursors and nested result sets (cursor expressions); recent versions have a JIT compiler.
惯用的 PL/SQL 退出程序代码要快得多，与尝试伪造临时表相比，这样做可能更好。请注意，PL/SQL 具有面向性能的构造，例如用于对游标和嵌套结果集（游标表达式）进行显式并行处理的流控制；最近的版本有一个 JIT 编译器。
You have access to a range of tools to make procedural PL/SQL code run quickly, and this is arguably idiomatic PL/SQL programming. The underlying paradigm is somewhat different from T-SQL, and the approach to temporary tables is one of the major points where the system architecture and programming idioms differ.
您可以使用一系列工具来使过程 PL/SQL 代码快速运行，这可以说是惯用的 PL/SQL 编程。底层范式与T-SQL有些不同，临时表的方法是系统架构和编程习惯不同的主要点之一。
