在 PL/SQL 中创建临时表

Question

我正在使用 Oracle 10g 数据库，我想从一个表中提取一组记录，然后使用它从一堆相关表中提取记录。

如果这是 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

但是，我查看的所有有用页面都使这看起来比可能的工作要多得多，所以我认为我一定错过了一些明显的东西。

（顺便说一句，我可能会在 Oracle SQL Developer 中打开一个会话，创建临时表，然后运行每个查询，然后将它们导出到 CSV，而不是将其作为一个脚本运行。这样可以吗？）

Answer 1

Oracle 有临时表，但它们需要显式创建：

create global temporary table...

临时表中的数据对于创建它的会话来说是私有的，并且可以是特定于会话或特定于事务的。如果在会话结束之前不会删除数据，则需要在创建语句末尾使用ON COMMIT PRESERVE ROWS。也没有对它们的回滚或提交支持...

我认为在您给出的示例中不需要临时表 - 由于临时表正在填充，因此可能无法对 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

如果有多个 APPOINTMENT 记录与单个 PERSON 记录关联，则 JOINing 存在重复风险，这就是我添加 DISTINCT 的原因。

Answer 2

Oracle 不具备像 SQL Server 那样随意创建临时表的功能。您必须在数据库架构中显式创建表（创建全局临时表）。这也意味着您需要允许创建表的权限，并且必须将脚本显式部署为数据库更改。该表在全局名称空间中也是可见的。

这是 Oracle 和 SQL Server 编程之间显着的惯用差异。惯用的 T-SQL 可以广泛使用临时表，并且真正需要编写过程 T-SQL 代码的情况相当罕见，这主要是因为这种便利。

惯用的 PL/SQL 会更快地退出过程代码，并且这样做可能比尝试伪造临时表更好。请注意，PL/SQL 具有面向性能的构造，例如用于对游标和嵌套结果集（游标表达式）进行显式并行处理的流控制；最近的版本有一个 JIT 编译器。

您可以使用一系列工具来使过程 PL/SQL 代码快速运行，这可以说是惯用的 PL/SQL 编程。底层范例与 T-SQL 有所不同，临时表的方法是系统架构和编程习惯不同的主要点之一。

Answer 3

虽然确切的问题已经解决，但如果您想在该领域建立一些有用的技能，我会看看 PL/SQL 集合，特别是使用 pl/sql 集合的批量 SQL 操作（BULK COLLECT / Bulk Binds）， RETURNING 子句，并使用 %ROWTYPE 定义集合。

通过理解上述所有内容，您可以显着减少编写的 pl/sql 代码量 - 尽管始终记住全 SQL 解决方案几乎总是会击败 PL/SQL 解决方案。