Oracle 存储过程中的临时表使用指南

在Oracle数据库中，临时表是存储过程中常用的工具，用于临时存储中间结果集。以下是关于Oracle存储过程中使用临时表的详细说明：

1. Oracle临时表类型

Oracle主要有两种临时表：

1.1 全局临时表(GTT - Global Temporary Table)

CREATE GLOBAL TEMPORARY TABLE temp_table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
) ON COMMIT [PRESERVE ROWS | DELETE ROWS];

• ON COMMIT DELETE ROWS：事务结束时自动清空数据（默认）
• ON COMMIT PRESERVE ROWS：会话结束时自动清空数据

1.2 私有临时表(12c及以上版本)

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_table (
    column1 datatype,
    column2 datatype,
    ...
) ON COMMIT [DROP DEFINITION | PRESERVE DEFINITION];

2. 在存储过程中使用临时表

2.1 创建临时表（通常在数据库初始化脚本中）

CREATE GLOBAL TEMPORARY TABLE proc_temp_data (
    id NUMBER,
    name VARCHAR2(100),
    value NUMBER
) ON COMMIT PRESERVE ROWS;

2.2 在存储过程中使用

CREATE OR REPLACE PROCEDURE process_data AS
BEGIN
    -- 清空临时表（如果需要）
    EXECUTE IMMEDIATE 'TRUNCATE TABLE proc_temp_data';

    -- 插入数据到临时表
    INSERT INTO proc_temp_data (id, name, value)
    SELECT employee_id, last_name, salary
    FROM employees
    WHERE department_id = 10;

    -- 使用临时表数据进行处理
    FOR rec IN (SELECT * FROM proc_temp_data) LOOP
        -- 处理逻辑
        DBMS_OUTPUT.PUT_LINE('Processing: ' || rec.name);
    END LOOP;

    -- 提交或回滚时，根据定义可能自动清空
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END process_data;

3. 临时表最佳实践

1. 命名规范：使用统一前缀如TMP_或GTT_标识临时表
2. 索引：为频繁查询的列创建临时表索引
3. 统计信息：考虑收集统计信息DBMS_STATS.GATHER_TABLE_STATS
4. 内存考虑：大量数据时注意PGA内存使用
5. 会话隔离：不同会话的临时表数据互不可见

4. 临时表与普通表的比较

5. 常见问题解决

问题1：ORA-14452 - 尝试在临时表上创建永久索引
解决：只能为临时表创建临时索引

问题2：临时表数据"丢失"
解决：检查是ON COMMIT DELETE ROWS还是PRESERVE ROWS

问题3：性能问题
解决：考虑为临时表添加适当的索引或调整PGA内存

问题4：ORA-01790 - 临时表与永久表连接问题
解决：确保查询中临时表与永久表的连接条件正确

通过合理使用临时表，可以显著提高Oracle存储过程的性能和可维护性。