Oracle中临时表的替代方法 [英] Alternatives for temporary tables in Oracle

问题描述

1. 在存储过程中创建一个临时表，例如"#Temp".
2. 使用select语句将值插入"Temp"表中，例如插入到#Temp选择员工的*中.
3. 现在从此Temp表中提取数据，例如从#Temp中选择*，其中#Temp.Id = @id&等等.

在 Oracle存储过程中如何做到这一点?

推荐答案

您要解决的业务问题是什么?在Oracle中需要使用临时表的情况很少见.为什么不简单地

What is the business problem you are trying to solve? It is exceptionally rare that you need to use temporary tables in Oracle. Why wouldn't you simply

SELECT *
  FROM employees
 WHERE id = p_id_passed_in;

在其他数据库中，您通常会创建临时表，因为读取器会阻止写入器，因此您希望创建数据的单独副本，以避免阻塞任何其他会话.但是，在Oracle中，读取器永远不会阻塞写入器，因此通常无需保存单独的数据副本.

In other databases, you often create temporary tables because readers block writers so you want to create a separate copy of the data in order to avoid blocking any other sessions. In Oracle, however, readers never block writers, so there is generally no need to save off a separate copy of the data.

在其他数据库中，您创建临时表是因为您不想进行脏读.但是，Oracle不允许脏读.多版本读取一致性意味着Oracle将始终向您显示查询启动时(或者如果您设置了事务隔离级别为可序列化的事务启动时)存在的数据.因此，无需创建临时表来避免脏读.

In other databases, you create temporary tables because you don't want to do dirty reads. Oracle, however, does not allow dirty reads. Multi-version read consistency means that Oracle will always show you the data as it existed when the query was started (or when the transaction started if you've set a transaction isolation level of serializable). So there is no need to create a temporary table to avoid dirty reads.

如果您真的想在Oracle中使用临时表，则不会动态创建该表.您将在创建存储过程之前创建一个全局临时表.该表结构对所有会话都可见，但是数据仅对插入它的会话可见.您将在过程中填充临时表，然后查询该表.像

If you really wanted to use temporary tables in Oracle, you would not create the table dynamically. You would create a global temporary table before you created the stored procedure. The table structure would be visible to all sessions but the data would be visible only to the session that inserted it. You would populate the temporary table in the procedure and then query the table. Something like

CREATE GLOBAL TEMPORARY TABLE temp_emp (
  empno number,
  ename varchar2(10),
  job   varchar2(9),
  mgr   number,
  sal   number(7,2)
)
ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE PROCEDURE populate_temp_emp
AS
BEGIN
  INSERT INTO temp_emp( empno,
                        ename,
                        job,
                        mgr,
                        sal )
    SELECT empno, 
           ename,
           job,
           mgr,
           sal
      FROM emp;
END;
/

SQL> begin
  2    populate_temp_emp;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select *
  2    from temp_emp;

     EMPNO ENAME      JOB              MGR        SAL
---------- ---------- --------- ---------- ----------
      7623 PAV        Dev
      7369 smith      CLERK           7902        800
      7499 ALLEN      SALESMAN        7698       1600
      7521 WARD       SALESMAN        7698       1250
      7566 JONES      MANAGER         7839       2975
      7654 MARTIN     SALESMAN        7698       1250
      7698 BLAKE      MANAGER         7839       2850
      7782 CLARK      MANAGER         7839       2450
      7788 SCOTT      ANALYST         7566       3000
      7839 KING       PRESIDENT                  5000
      7844 TURNER     SALESMAN        7698       1500
      7876 ADAMS      CLERK           7788       1110
      7900 SM0        CLERK           7698        950
      7902 FORD       ANALYST         7566       3000
      7934 MILLER     CLERK           7782       1300
      1234 BAR

16 rows selected.

但是，正如我所说，在Oracle中实际上要使用临时表是非常不寻常的.

As I said, though, it would be very unusual in Oracle to actually want to use a temporary table.

这篇关于Oracle中临时表的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！