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'
。请注意,实际结果可能会根据数据库的配置和表的属性而有所不同。