Oracle中MySQL临时表的替代方法 [英] Alternative for a MySQL temporary table in Oracle

问题描述

我注意到这两个系统中的临时表的概念是不同的，我有一个沉思..在MySQL中，我有以下情形:

I noticed that the concept of temporary tables in these two systems is different, and I have a musing.. I have the following scenario in MySQL:

1. 删除临时表"a"(如果存在)
2. 创建临时表"a"
3. 通过存储过程向其中填充数据
4. 在另一个存储过程中使用数据

如何在Oracle中实现相同的方案?我可以(在一个过程中最好)创建一个临时表，填充它，然后在另一个(非临时)表中插入数据吗?

How can I implement the same scenario in Oracle? Can I (in one procedure preferable) create a temporary table, populate it, and insert data in another (non-temporary) table?

我认为我可以使用一个(全局)临时表，该表在提交时会被截断，并避免了步骤1和步骤2，但是我也需要别人的意见.

I think that I can use a (global) temporary table which truncates on commit, and avoid steps 1&2, but I need someone else's opinion too.

推荐答案

在Oracle中，很少很少需要首先使用临时表.通常，您需要其他数据库中的临时表，因为这些数据库未实现多版本读取一致性，并且可能在过程运行时阻止从表中读取数据的人，或者如果过程不执行，则过程将进行脏读不能将数据保存到单独的结构中.由于这些原因之一，您不需要在Oracle中使用全局临时表，因为读取器不会阻塞写入器，并且不可能进行脏读取.

In Oracle, you very rarely need a temporary table in the first place. You commonly need temporary tables in other databases because those databases do not implement multi-version read consistency and there is the potential that someone reading data from the table would be blocked while your procedure runs or that your procedure would do a dirty read if it didn't save off the data to a separate structure. You don't need global temporary tables in Oracle for either of these reasons because readers don't block writers and dirty reads are not possible.

如果在执行PL/SQL计算时仅需要一个临时位置来存储数据，则PL/SQL集合比Oracle中的临时表更常用.这样，您就不会在PL/SQL引擎和SQL引擎之间以及PL/SQL引擎之间来回推送数据.

If you just need a temporary place to store data while you perform PL/SQL computations, PL/SQL collections are more commonly used than temporary tables in Oracle. This way, you're not pushing data back and forth from the PL/SQL engine to the SQL engine and back to the PL/SQL engine.

CREATE PROCEDURE do_some_processing
AS
  TYPE emp_collection_typ IS TABLE OF emp%rowtype;
  l_emps emp_collection_type;

  CURSOR emp_cur
      IS SELECT *
           FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur 
     BULK COLLECT INTO l_emps
    LIMIT 100;

    EXIT WHEN l_emps.count = 0;

    FOR i IN 1 .. l_emps.count
    LOOP
      <<do some complicated processing>>
    END LOOP;
  END LOOP;
END;

您可以创建全局临时表(在过程外部)，并在过程内使用全局临时表，就像使用其他任何表一样.因此，您可以根据需要继续使用临时表.但是我一方面可以指望我真正需要Oracle中的临时表的次数.

You can create a global temporary table (outside of the procedure) and use the global temporary table inside your procedure just as you would use any other table. So you can continue to use temporary tables if you so desire. But I can count on one hand the number of times I really needed a temporary table in Oracle.

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