在Oracle数据库的实际开发中，存储过程常常需要处理多步骤、大批量或者逻辑复杂的数据操作，临时表作为临时存储中间数据的载体，能够有效简化存储过程的逻辑结构，同时避免对永久表的频繁读写带来的性能损耗。临时表的数据仅在当前会话或者事务中存在，不会长期占用存储空间，是存储过程开发中非常实用的工具。
Oracle临时表的两种类型
Oracle的临时表分为会话级临时表和事务级临时表两种，二者的数据保留范围不同，适用场景也有区别。
会话级临时表
会话级临时表的数据会在整个会话期间保留，只有当会话结束或者手动删除数据时，表中的数据才会被清除。创建会话级临时表时需要指定ON COMMIT PRESERVE ROWS参数。
事务级临时表
事务级临时表的数据仅会在当前事务中保留，一旦事务提交或者回滚，表中的数据就会被自动清空。创建事务级临时表时需要指定ON COMMIT DELETE ROWS参数，这也是Oracle临时表的默认类型。
存储过程中使用临时表的完整步骤
在存储过程中使用临时表通常需要先创建临时表结构，再进行数据的插入、查询、处理操作，最后可以根据需求清理数据。下面通过一个实际案例演示完整流程。
1. 创建临时表
首先在存储过程外部或者存储过程内部创建临时表结构，通常建议在存储过程外部提前创建好临时表，避免每次调用存储过程都重复创建表结构，浪费资源。
-- 创建事务级临时表，存储用户订单中间数据
CREATE GLOBAL TEMPORARY TABLE tmp_user_order (
user_id NUMBER(10),
order_id NUMBER(10),
order_amount NUMBER(10,2),
create_time DATE
) ON COMMIT DELETE ROWS;
2. 存储过程中操作临时表
下面编写一个存储过程，将用户订单表中符合条件的订单数据插入临时表，再对临时表中的数据进行汇总统计，最后返回统计结果。
CREATE OR REPLACE PROCEDURE proc_calc_user_order (
p_start_time IN DATE,
p_end_time IN DATE,
p_result OUT SYS_REFCURSOR
) AS
BEGIN
-- 清空临时表数据，避免残留数据影响结果
DELETE FROM tmp_user_order;
-- 将符合条件的订单数据插入临时表
INSERT INTO tmp_user_order (user_id, order_id, order_amount, create_time)
SELECT user_id, order_id, order_amount, create_time
FROM t_order
WHERE create_time >= p_start_time
AND create_time <= p_end_time;
-- 对临时表中的数据进行汇总，返回每个用户的订单总金额
OPEN p_result FOR
SELECT user_id, SUM(order_amount) AS total_amount, COUNT(order_id) AS order_count
FROM tmp_user_order
GROUP BY user_id;
-- 事务级临时表会在提交后自动清空，这里也可以手动提交
COMMIT;
END proc_calc_user_order;
3. 调用存储过程验证结果
调用上述存储过程，传入时间范围参数，即可获取对应时间段内每个用户的订单汇总数据。
DECLARE
v_result SYS_REFCURSOR;
v_user_id NUMBER(10);
v_total_amount NUMBER(10,2);
v_order_count NUMBER(10);
BEGIN
-- 调用存储过程，查询2024年1月的订单数据
proc_calc_user_order(
TO_DATE('2024-01-01', 'YYYY-MM-DD'),
TO_DATE('2024-01-31', 'YYYY-MM-DD'),
v_result
);
-- 遍历返回的结果集
LOOP
FETCH v_result INTO v_user_id, v_total_amount, v_order_count;
EXIT WHEN v_result%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('用户ID:' || v_user_id ||
',订单总金额:' || v_total_amount ||
',订单数量:' || v_order_count);
END LOOP;
CLOSE v_result;
END;
临时表与永久表的差异对比
为了更清晰地了解临时表的特性，我们将临时表和永久表从多个维度进行对比：
| 对比维度 | 临时表 | 永久表 |
|---|---|---|
| 数据存储周期 | 会话级或事务级，自动清理 | 长期存储，需手动删除 |
| 数据可见性 | 仅当前会话可见 | 所有有权限的会话可见 |
| redo日志生成 | 仅生成少量undo日志，redo日志少 | 生成完整的redo和undo日志 |
| 锁竞争 | 无跨会话锁竞争 | 可能存在跨会话锁竞争 |
| 适用场景 | 中间数据处理、复杂逻辑拆分 | 业务核心数据存储 |
存储过程中使用临时表的注意事项
- 临时表的结构是全局的，所有会话共享临时表的结构定义，但是数据是相互隔离的，不同会话操作同一临时表不会互相影响。
- 不要在存储过程中频繁创建和删除临时表，临时表的结构只需要创建一次即可，重复创建会消耗额外的数据库资源。
- 如果存储过程中需要多次使用临时表的中间数据，优先选择会话级临时表，避免事务提交后数据丢失。
- 临时表同样可以创建索引，针对临时表中需要频繁查询的字段创建索引，能够进一步提升存储过程的处理效率。
- 注意临时表的权限管理，给需要调用存储过程的用户授予临时表的对应操作权限，避免出现权限不足的错误。
临时表的性能优化建议
在使用临时表配合存储过程开发时，可以通过以下方式进一步优化性能：
- 只往临时表中插入需要的字段数据，避免插入冗余字段，减少数据写入和存储的开销。
- 如果临时表的数据量较大，可以在插入数据前暂时禁用临时表上的索引，插入完成后再重建索引，提升插入效率。
- 合理选择临时表类型，如果只是单事务内的中间数据处理，优先使用事务级临时表，减少数据清理的额外操作。
- 对于需要多次查询临时表数据的场景，可以将临时表的数据缓存到PL/SQL的嵌套表中，减少临时表的查询次数。