在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的同时进行。这样可以吗？）

- SarekOfVulcan

---

为什么需要使用临时表呢？你只是用另一个select替换了一个。是因为最初的select非常昂贵吗？还是为了防止并发事务更改数据？ - Martin Smith

Oracle并非SQL Server，需要使用临时表的情况实际上相当罕见。请阅读我对类似问题的回答：http://stackoverflow.com/questions/1192265/local-temporary-table-in-oracle-10-for-the-scope-of-stored-procedure/1193443#1193443 - APC

我在简化 -- 我实际上想做的是获取最后100个患者（减去一些重复）。但是在下面谈论时，我想起我可以只获取从今天凌晨开始的100个患者，而不必担心SYSDATE在我进行操作时发生变化。 :-) - SarekOfVulcan

问题文本中没有关于日期限制的内容。您能否更新问题并提供选择标准的详细信息？ - ThinkJet

你可能想看一下其他可用的非默认事务隔离级别。我认为Serializable和ReadOnly都会显示从事务开始时的结果。在事务开始之后插入的行将不会出现。 - Shannon Severance

Answer 1

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

create global temporary table...

临时表中的数据仅供创建它的会话使用，可以是特定于会话或特定于事务。如果在结束会话之前不希望删除数据，则需要在CREATE语句的结尾处使用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

如果一个人有多条预约记录，那么使用JOIN操作可能会导致出现重复的记录，所以我加了DISTINCT关键字。

- OMG Ponies

---

那是我一开始考虑的方式，但我正在过滤apptDate < SYSDATE，所以它可能会在我提取的第一个表和最后一个表之间发生变化。我支持我可以只对apptDate为昨天进行操作，这样就可以避免问题了...谢谢！ - SarekOfVulcan

昨天是 apptdDate >= trunc(sysdate)-1 and apptdDate < trunc(sysdate) - ThinkJet

Answer 2

与SQL Server不同，Oracle没有轻松创建临时表的功能。您必须在数据库模式中显式创建表(create global temporary table)。这也意味着您需要具有创建表的权限，并且脚本必须显式部署为数据库更改。该表也可在全局命名空间中看到。

这是Oracle和SQL Server编程之间重要的习惯差异。习惯性的T-SQL可以广泛使用临时表，而实际需要编写过程性T-SQL代码的要求相当少，这在很大程度上是由于这个功能。

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

您可以访问一系列工具，以使过程性PL/SQL代码快速运行，这可以说是习惯性的PL/SQL编程。根本范例与T-SQL有所不同，而对临时表的处理方式是系统架构和编程习惯之间的主要区别点。

- ConcernedOfTunbridgeWells

Answer 3

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

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

- JulesLt

---

最好能够在这个建议旁边提供一个示例。 - osullic