oracle数据库临时表代码举例总结

一.概念

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

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

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

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

临时表的使用场景包括：

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

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

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

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

二.为什么会有？

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

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

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

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

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

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

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

三.代码举例

使用 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, temporary
FROM dba_tables
WHERE table_name = 'EMPLOYEES';

结果所示：

TABLE_NAME  TEMPORARY
----------- ---------
EMPLOYEES   Y

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

补充：Oracle临时表的实际应用

临时表在Oracle数据库中的应用场景是非常广泛的。下面介绍一些实际应用案例。

1. 查询结果集太大，无法一次返回

如果查询结果集太大，无法一次性完整返回给客户端，可以将查询结果集保存到临时表中，然后分批次向客户端返回数据。

示例：

CREATE GLOBAL TEMPORARY TABLE tmp_result(
   id    NUMBER,
   name  VARCHAR2(20)
) ON COMMIT PRESERVE ROWS;

INSERT INTO tmp_result
SELECT id, name
FROM big_table
WHERE condition;
SELECT * FROM tmp_result
WHERE rownum

在这个示例中，我们从名为big_table的数据表中查询符合条件的数据，将结果集保存到临时表tmp_result中。最后使用SELECT语句从临时表中返回前1000行数据。

2. 存储中间结果

在数据处理过程中，可能需要进行多次计算或多次查询，每次计算或查询的结果都需要保存下来，这时可以使用临时表来存储中间结果。

示例：

CREATE GLOBAL TEMPORARY TABLE tmp_result(
   id    NUMBER,
   name  VARCHAR2(20)
) ON COMMIT PRESERVE ROWS;

INSERT INTO tmp_result
SELECT id, name
FROM big_table
WHERE condition;
UPDATE tmp_result SET name = 'new_name' WHERE id = 1;

SELECT * FROM tmp_result;

在这个示例中，我们从名为big_table的数据表中查询符合条件的数据，将结果集保存到临时表tmp_result中。然后对临时表中的某条数据进行修改。最后使用SELECT语句从临时表中查询所有数据，包括修改后的数据。

总结

到此这篇关于oracle数据库临时表的文章就介绍到这了,更多相关oracle临时表内容请搜索程序员之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持程序员之家！

最新评论