Oracle创建临时表

1. 什么是临时表

在Oracle数据库中，临时表是一种特殊类型的关系表，用于存储临时数据。与普通表不同的是，临时表的数据只存在于当前会话中，当会话结束时，临时表中的数据会被自动删除。临时表主要用于存储会话过程中的临时数据，比如中间结果集、临时计算值等。

临时表的创建方式和语法与普通表类似，但需要在表名后加上 GLOBAL TEMPORARY 关键字来指定表为临时表。临时表一般使用在存储过程、函数、触发器等数据库对象中，用于临时存储和操作数据。

2. 创建临时表的语法

下面是创建临时表的通用语法：

CREATE GLOBAL TEMPORARY TABLE table_name
(
   column1 data_type [NULL | NOT NULL],
   column2 data_type [NULL | NOT NULL],
   ...
)
ON COMMIT { DELETE ROWS | PRESERVE ROWS };

• CREATE GLOBAL TEMPORARY TABLE：创建一个全局临时表。
• table_name：指定临时表的名称。
• column1, column2, ...：定义临时表的列名和数据类型。
• NULL 或 NOT NULL：定义列是否允许为空。
• ON COMMIT { DELETE ROWS | PRESERVE ROWS }：定义当提交事务时，临时表中的数据是删除还是保留。

在创建临时表时，可以根据需要定义表的列和约束。临时表的列定义和普通表一样，可以包含任意的数据类型、约束和默认值。

3. 临时表的使用案例

为了更好地理解和使用临时表，下面通过一个具体的案例来演示临时表的创建和使用。

假设我们有一个订单表 orders 和一个订单明细表 order_details，我们想要统计每个订单的总金额，并将结果存储在一个临时表中。

首先，我们创建订单表和订单明细表，并插入一些示例数据：

CREATE TABLE orders (
   order_id NUMBER,
   order_date DATE,
   customer_id NUMBER,
   amount NUMBER
);

CREATE TABLE order_details (
   detail_id NUMBER,
   order_id NUMBER,
   product_id NUMBER,
   quantity NUMBER,
   price NUMBER
);

-- 插入示例数据
INSERT INTO orders VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 100);
INSERT INTO orders VALUES (2, TO_DATE('2022-01-02', 'YYYY-MM-DD'), 1002, 200);
INSERT INTO orders VALUES (3, TO_DATE('2022-01-03', 'YYYY-MM-DD'), 1001, 300);

INSERT INTO order_details VALUES (1, 1, 1, 10, 10);
INSERT INTO order_details VALUES (2, 1, 2, 5, 20);
INSERT INTO order_details VALUES (3, 2, 1, 8, 15);
INSERT INTO order_details VALUES (4, 3, 3, 12, 25);

现在，我们可以使用临时表来计算每个订单的总金额。

首先，创建临时表 temp_order_summary 用于存储订单总金额：

CREATE GLOBAL TEMPORARY TABLE temp_order_summary
(
   order_id NUMBER,
   total_amount NUMBER
)
ON COMMIT PRESERVE ROWS;

然后，使用 INSERT INTO ... SELECT 语句将计算结果插入到临时表中：

INSERT INTO temp_order_summary (order_id, total_amount)
SELECT o.order_id, SUM(od.quantity * od.price) AS total_amount
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.order_id;

现在，我们可以查询临时表获取每个订单的总金额：

SELECT * FROM temp_order_summary;

运行结果如下：

ORDER_ID    TOTAL_AMOUNT
----------  ------------
1           300
2           120
3           300

可以看到，临时表 temp_order_summary 中存储了每个订单的总金额。

需要注意的是，临时表的数据只在当前会话中可见。在其他会话或事务中无法访问、修改临时表的数据。

4. 删除临时表

当不再需要使用临时表时，可以使用 DROP TABLE 语句来删除临时表。删除操作会自动清空临时表中的数据，并释放表所占用的空间。

以下是删除临时表的语法：

DROP TABLE table_name;

在上面的案例中，我们可以使用以下语句删除临时表 temp_order_summary：

DROP TABLE temp_order_summary;

5. 总结

临时表是Oracle数据库中一种特殊的表，用于存储临时数据。我们可以使用 CREATE GLOBAL TEMPORARY TABLE 语句来创建临时表，并在其中存储和操作临时数据。临时表的数据只在当前会话中可见，并在会话结束时自动删除。

临时表的创建和使用方式与普通表类似，唯一的区别是需要在表名后添加 GLOBAL TEMPORARY 关键字。在使用临时表时，注意数据的插入、查询和删除操作应在同一会话中进行，否则将无法访问到临时表的数据。