oracle创建临时表

一.概念

临时表是数据库中的一种特殊表，用于存储临时数据。它的主要特点是：

  1. 临时性：临时表中存储的数据在会话结束时自动删除，不会长期保留。临时表的作用范围限于创建它的会话。

  2. 私有性：临时表对于创建它的会话是可见的，其他会话无法访问该临时表的数据。这使得每个会话可以在临时表中独立地存储和处理数据，而不会相互干扰。

  3. 临时表结构：临时表的结构（列、数据类型、约束等）与普通表类似，可以定义临时表的列和相应的约束。

临时表的使用场景包括：

  1. 临时存储数据：临时表可以用于存储临时的计算结果、中间数据或临时数据集，以供后续查询和处理使用。

  2. 临时计算：临时表可以用于执行复杂的查询操作或数据转换，以提高查询性能或简化复杂的查询逻辑。

  3. 会话级数据管理：临时表可以用于会话级别的数据管理，例如临时存储用户特定的参数、上下文信息或临时状态。

在 Oracle 数据库中，可以使用 CREATE GLOBAL TEMPORARY TABLE 语句来创建临时表。临时表的数据在事务提交后会自动删除，或者在会话结束后删除，取决于使用的删除策略（ON COMMIT DELETE ROWS 或 ON COMMIT PRESERVE ROWS）。临时表提供了一种方便的方式来处理临时数据，同时确保数据的隔离性和私有性。

二.为什么会有？

临时表的设计是为了满足一些特定的需求和场景，它提供了以下几个方面的优势和用途：

  1. 临时存储和处理数据：临时表允许在数据库中暂时存储和处理临时数据，这些数据可能是计算中间结果、查询结果、临时数据集等。通过临时表，可以有效地将数据存储在数据库中，并在需要时进行查询、处理或分析。

  2. 提高性能：临时表可以用于存储和处理复杂的查询操作或计算任务，从而提高查询性能。将中间结果存储在临时表中可以减少计算量，并且可以通过适当的索引和优化来加快查询速度。

  3. 临时数据管理：临时表可以用于会话级别的数据管理，例如存储用户特定的参数、上下文信息或临时状态。它可以为每个会话提供一个私有的存储空间，确保数据的隔离性和独立性。

  4. 简化复杂查询逻辑：在某些情况下，复杂的查询逻辑可能会变得难以理解和维护。通过使用临时表，可以将复杂的查询逻辑分解为多个简单的步骤，并在每个步骤中使用临时表来存储中间结果，使查询逻辑更加清晰和可读。

  5. 临时数据共享：尽管临时表对于创建它的会话是可见的，但在某些情况下，可以通过临时表来共享数据。多个会话可以将数据插入到同一个临时表中，并在需要时进行查询和处理，从而实现临时数据的共享和协作。

总的来说，临时表的设计为数据库提供了一种灵活、高效和安全地处理临时数据的机制。它可以满足临时存储、处理和管理数据的需求，提供性能优化、简化复杂查询和临时数据共享等功能。

三.代码举例

使用 CREATE GLOBAL TEMPORARY TABLE 语句来创建临时表。临时表是一种特殊的表，用于存储临时数据，并且在会话结束时自动删除其中的数据。

以下是创建临时表的示例：

CREATE GLOBAL TEMPORARY TABLE table_name (column1 datatype [constraint],column2 datatype [constraint],...columnN datatype [constraint])[ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ];

在上面的语句中，table_name 是你要创建的临时表的名称，column1、column2 等是表的列名，datatype 是列的数据类型，constraint 是可选的约束条件。

ON COMMIT 子句用于指定在事务提交时临时表中的数据的行为。你可以选择 PRESERVE ROWS 保留数据（默认选项），这意味着在事务提交后临时表中的数据将保留，或者选择 DELETE ROWS 删除数据，这意味着在事务提交后临时表中的数据将被删除。

以下是一个示例，创建一个名为 “temp_employees” 的临时表：

CREATE GLOBAL TEMPORARY TABLE temp_employees (employee_id NUMBER(6),first_name VARCHAR2(50),last_name VARCHAR2(50),hire_date DATE,salary NUMBER(8,2),department_id NUMBER(4))ON COMMIT DELETE ROWS;

上述示例创建了一个名为 “temp_employees” 的临时表，与创建普通表类似，指定了表的列名和数据类型。此外，使用了 ON COMMIT DELETE ROWS，表示在事务提交后临时表中的数据将被删除。

请注意，临时表的数据仅在创建它的会话中可见，其他会话无法访问临时表的数据。临时表适用于临时存储和处理数据的场景，例如临时计算、查询等。

  • 当切换另一个用户去查询的时候， 这个时候是会查不到这张表。
-- 创建临时表CREATE GLOBAL TEMPORARY TABLE temp_employees (employee_id NUMBER,first_name VARCHAR2(50),last_name VARCHAR2(50),hire_date DATE) ON COMMIT PRESERVE ROWS;-- 插入数据INSERT INTO temp_employees (employee_id, first_name, last_name, hire_date)VALUES (1, 'John', 'Doe', SYSDATE);INSERT INTO temp_employees (employee_id, first_name, last_name, hire_date)VALUES (2, 'Jane', 'Smith', SYSDATE);-- 查询临时表数据SELECT * FROM temp_employees;

可以把上述sql执行一下，然后切换用户查询试一试。

四.特点

这些表只在事物进行或会话期间存在在

这些数据上没有DML锁

可以在临时建表上创建索引, 视图，和触发器

五.验证

当然可以用sql语言进行查询验证，是否真的是临时表。

假设表 EMPLOYEES 被确定为临时表：

SELECT table_name, temporaryFROM dba_tablesWHERE table_name = 'EMPLOYEES';

结果所示：

TABLE_NAMETEMPORARY----------- ---------EMPLOYEES Y

在这个示例中，EMPLOYEES 表被标记为临时表，因为 TEMPORARY 列的值为 'Y'。请注意，实际结果可能会根据数据库的配置和表的属性而有所不同。