实例详解Oracle临时表-猿码集

1. 什么是Oracle临时表

Oracle临时表是一种在用户会话中创建的表，临时表只对创建它的会话可见，当会话结束时临时表就会被自动删除，临时表可以在内存或者硬盘上创建，不同的创建方式会有不同的访问效率和空间占用。

Oracle临时表的特点包括：

临时表与正式表相似，可以存储数据、创建索引。

临时表仅与当前会话相关联，不会受到其他会话的干扰，且在会话结束后自动消失。

临时表的创建和删除是自动进行的，客户端无需手动干预。

2. Oracle临时表的创建

2.1 创建临时表

创建临时表与创建普通表类似，先创建表结构，然后再在表名前加上"GLOBAL TEMPORARY"关键字。


-- 以一个员工表为例，创建一个临时表
CREATE GLOBAL TEMPORARY TABLE temp_employee (
    id number(8) not null primary key,
    name varchar2(20),
    gender char(1),
    age number(3),
    salary number(8,2)
) ON COMMIT PRESERVE ROWS;

这里需要注意一下ON COMMIT子句，该子句表示在COMMIT时删除数据。ON COMMIT支持两种选项：

PRESERVE ROWS：表示不删除表中的数据。

DELETE ROWS：表示删除表中的数据。

2.2 创建使用内存的临时表

在创建临时表时，可以指定使用SHRINK SPACE选项分配内存。使用内存表的好处是速度较快，但是要注意表大数据量时可能会导致内存不够而导致性能问题。


CREATE GLOBAL TEMPORARY TABLE temp_employee (
    id number(8) not null primary key,
    name varchar2(20),
    gender char(1),
    age number(3),
    salary number(8,2)
) ON COMMIT PRESERVE ROWS
SHRINK SPACE;

2.3 创建使用磁盘的临时表

使用磁盘表的好处是存储空间较大，可以存储大量数据，但是速度可能会相对较慢。


CREATE GLOBAL TEMPORARY TABLE temp_employee (
    id number(8) not null primary key,
    name varchar2(20),
    gender char(1),
    age number(3),
    salary number(8,2)
) ON COMMIT PRESERVE ROWS
ON COMMIT DELETE ROWS
TABLESPACE temp;

其中，TABLESPACE子句指定了表的存储位置。

3. Oracle临时表的使用

3.1 插入数据

临时表的数据插入操作与普通表相同，也可以使用INSERT INTO语句来插入数据。


INSERT INTO temp_employee (id, name, gender, age, salary)
VALUES (1, 'John', 'M', 25, 3000);

3.2 查询数据

临时表的数据查询操作也与普通表类似，也可以使用SELECT语句来查询数据。例如，查询所有“John”的员工记录：


SELECT * FROM temp_employee WHERE name = 'John';

3.3 更新数据

临时表的数据更新也与普通表类似，也可以使用UPDATE语句来更新数据。


UPDATE temp_employee SET salary = 4000 WHERE id = 1;

3.4 删除数据

临时表的数据删除也与普通表类似，可以使用DELETE语句来删除数据。删除所有员工记录可以使用以下语句：


DELETE FROM temp_employee;

3.5 在SQL查询中使用临时表

临时表可以被用于查询语句中，以提高查询效率。例如，查询某个部门的员工记录：


WITH temp_department AS (
    SELECT * FROM departments WHERE department_name = 'Sales'
)
SELECT e.*
FROM employees e, temp_department d
WHERE e.department_id = d.department_id;

以上SQL语句使用了WITH子句先创建了一个临时的“Sales”部门表，然后将其与员工表进行连接查询。

4. Oracle临时表的删除

临时表的删除操作是自动进行的，可以不用手动删除。

5. 小结

Oracle临时表是一种可在会话中动态创建的临时性数据库表，表的数据只与创建它的会话相关联，会话结束时会自动删除表数据，创建与普通表类似，可以插入、查询、更新、删除数据，更多的使用可以扩展到各种复杂的SQL查询语句中。

实例详解Oracle临时表