Oracle 中临时表的替代方案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7598631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alternatives for temporary tables in Oracle
提问by nylon610
- Create a temporary table inside a stored procedure, say '#Temp'.
- Insert values into 'Temp' table using a select statement, eg. Insert Into #Temp Select * from Employees.
- Now extract data from this Temp table, eg. Select * from #Temp where #Temp.Id = @id & so on.
- 在存储过程中创建一个临时表，比如“#Temp”。
- 使用 select 语句将值插入到 'Temp' 表中，例如。插入#Temp 从员工中选择*。
- 现在从这个临时表中提取数据，例如。从 #Temp 中选择 * 其中 #Temp.Id = @id 等等。
How to do this in Oracle inside a stored procedure?
如何在Oracle 里面的一个存储过程中做到这一点？
回答by Justin Cave
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
您要解决的业务问题是什么？在 Oracle 中需要使用临时表的情况极为罕见。你为什么不简单地
SELECT *
FROM employees
WHERE id = p_id_passed_in;
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 中，读取器从不阻塞写入器，因此通常不需要保存单独的数据副本。
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 不允许脏读。多版本读取一致性意味着 Oracle 将始终向您显示查询开始时（或者如果您将事务隔离级别设置为可序列化，事务开始时）存在的数据。所以不需要创建临时表来避免脏读。
If you reallywanted 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
如果您真的想在 Oracle 中使用临时表，则不会动态创建该表。在创建存储过程之前，您将创建一个全局临时表。表结构对所有会话可见，但数据仅对插入它的会话可见。您将在过程中填充临时表，然后查询该表。就像是
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.
As I said, though, it would be very unusual in Oracle to actually want to use a temporary table.
不过，正如我所说，在 Oracle 中实际上想要使用临时表是非常不寻常的。
回答by Bart K
Create a global temporary table.
创建全局临时表。
CREATE GLOBAL TEMPORARY TABLE <your_table>
ON COMMIT PRESERVE ROWS # If needed. Depends on your needs.
AS SELECT <your_select_query>;
You can then select from the table as needed for the duration of your procedure.
然后，您可以在手术期间根据需要从表中进行选择。
http://www.oracle-base.com/articles/8i/TemporaryTables.php
http://www.oracle-base.com/articles/8i/TemporaryTables.php
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548
