SQL Oracle 10 中的本地临时表（适用于存储过程的范围）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1192265/
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
Local Temporary table in Oracle 10 (for the scope of Stored Procedure)
提问by Babar
I am new to oracle. I need to process large amount of data in stored proc. I am considering using Temporary tables. I am using connection pooling and the application is multi-threaded.
我是 oracle 的新手。我需要在存储过程中处理大量数据。我正在考虑使用临时表。我正在使用连接池并且应用程序是多线程的。
Is there a way to create temporary tables in a way that different table instances are created for every call to the stored procedure, so that data from multiple stored procedure calls does not mix up?
有没有办法创建临时表，每次调用存储过程都会创建不同的表实例，这样来自多个存储过程调用的数据就不会混淆？
回答by APC
You say you are new to Oracle. I'm guessing you are used to SQL Server, where it is quite common to use temporary tables. Oracle works differently so it is less common, because it is less necessary.
您说您是 Oracle 的新手。我猜你已经习惯了 SQL Server，在那里使用临时表是很常见的。Oracle 的工作方式不同，因此不太常见，因为它不太必要。
Bear in mind that using a temporary table imposes the following overheads:
请记住，使用临时表会带来以下开销：
- read data to populate temporary table
- write temporary table data to file
- read data from temporary table as your process starts
- 读取数据以填充临时表
- 将临时表数据写入文件
- 进程开始时从临时表中读取数据
Incidentally, your mention of connection pooling raises another issue. A process munging large amounts of data is not a good candidate for running in an OLTP mode. You really should consider initiating a background (i.e. asysnchronous) process, probably a database job, to run your stored procedure. This is especially true if you want to run this job on a regular basis, because we can use DBMS_SCHEDULER to automate the management of such things.
顺便说一句，您提到的连接池引发了另一个问题。处理大量数据的进程不适合在 OLTP 模式下运行。你真的应该考虑启动一个后台（即异步）进程，可能是一个数据库作业，来运行你的存储过程。如果您想定期运行此作业，则尤其如此，因为我们可以使用 DBMS_SCHEDULER 来自动管理此类事情。
回答by cagcowboy
IF you're using transaction (rather than session) level temporary tables, then this may already do what you want... so long as each call only contains a single transaction? (you don't quite provide enough detail to make it clear whether this is the case or not)
如果您使用的是事务（而不是会话）级别的临时表，那么这可能已经满足您的要求……只要每个调用只包含一个事务？（您没有提供足够的细节来说明情况是否如此）
So, to be clear, so long as each call only contains a single transaction, then it won't matter that you're using a connection pool since the data will be cleared out of the temporary table after each COMMIT or ROLLBACK anyway.
因此，要清楚的是，只要每次调用只包含一个事务，那么您使用连接池就无关紧要，因为无论如何都会在每次 COMMIT 或 ROLLBACK 后从临时表中清除数据。
(Another option would be to create a uniquely named temporary table in each call using EXECUTE IMMEDIATE. Not sure how performant this would be though.)
（另一种选择是使用 EXECUTE IMMEDIATE 在每次调用中创建一个唯一命名的临时表。但不确定这会如何表现。）
回答by Jeffrey Kemp
In Oracle, it's almost never necessary to create objects at runtime.
在 Oracle 中，几乎不需要在运行时创建对象。
Global Temporary Tables are quite possibly the best solution for your problem, however since you haven't said exactly why you need a temp table, I'd suggest you first check whether a temp table is necessary; half the time you can do with one SQL what you might have thought would require multiple queries.
Global Temporary Tables 很可能是您问题的最佳解决方案，但是由于您还没有确切说明为什么需要临时表，我建议您首先检查是否需要临时表；一半的时间您可以使用一个 SQL 完成您可能认为需要多个查询的工作。
That said, I have used global temp tables in the past quite successfully in applications that needed to maintain a separate "space" in the table for multiple contexts within the same session; this is done by adding an additional ID column (e.g. "CALL_ID") that is initially set to 1, and subsequent calls to the procedure would increment this ID. The ID would necessarily be remembered using a global variable somewhere, e.g. a package global variable declared in the package body. E.G.:
也就是说，我过去在需要在表中为同一会话中的多个上下文维护单独“空间”的应用程序中非常成功地使用了全局临时表；这是通过添加一个初始设置为 1 的附加 ID 列（例如“CALL_ID”）来完成的，随后对该过程的调用将增加此 ID。ID 必须在某处使用全局变量来记住，例如在包体中声明的包全局变量。例如：
PACKAGE BODY gtt_ex IS
last_call_id integer;
PROCEDURE myproc IS
l_call_id integer;
BEGIN
last_call_id := NVL(last_call_id, 0) + 1;
l_call_id := last_call_id;
INSERT INTO my_gtt VALUES (l_call_id, ...);
...
SELECT ... FROM my_gtt WHERE call_id = l_call_id;
END;
END;
You'll find GTTs perform very well even with high concurrency, certainly better than using ordinary tables. Best practice is to design your application so that it never needs to delete the rows from the temp table - since the GTT is automatically cleared when the session ends.
您会发现 GTT 即使在高并发的情况下也能很好地执行，当然比使用普通表要好。最佳实践是设计您的应用程序，使其永远不需要从临时表中删除行 - 因为在会话结束时 GTT 会自动清除。
回答by Amit
I used global temporary table recently and it was behaving very unwantedly manner.
我最近使用了全局临时表，它的行为非常不受欢迎。
I was using temp table to format some complex data in a procedure call and once the data is formatted, pass the data to fron end (Asp.Net). In first call to the procedure, i used to get proper data and any subsequent call used to give me data from last procedure call in addition to current call.
我使用临时表在过程调用中格式化一些复杂的数据，一旦数据被格式化，将数据传递到前端（Asp.Net）。在第一次调用该过程时，我曾经获取正确的数据，并且任何后续调用都用于从上次过程调用以及当前调用中提供数据。
I investigated on net and found out an option to delete rows on commit. I thought that will fix the problem.. guess what ? when i used on commit delete rows option, i always used to get 0 rows from database. so i had to go back to original approach of on commit preserve rows, which preserves the rows even after commiting the transaction.This option clears rows from temp table only after session is terminated. then i found out this post and came to know about the column to track call_id of a session.
我在网上调查并发现了一个在提交时删除行的选项。我认为这会解决问题..你猜怎么着？当我使用提交删除行选项时，我总是从数据库中获取 0 行。所以我不得不回到提交时保留行的原始方法，即使在提交事务后也保留行。此选项仅在会话终止后从临时表中清除行。然后我发现了这篇文章并开始了解跟踪会话的 call_id 的列。
I implemented that solution and still it dint fix the problem. then i wrote following statement in my procedure before i starting any processing.
我实施了该解决方案，但仍然无法解决问题。然后我在开始任何处理之前在我的程序中写了以下语句。
Delete From Temp_table;
从 Temp_table 中删除；
Above statemnet made the trick. my front end was using connection pooling and after each procedure call it was commitng the transaction but still keeping the connection in connection pool and subsequent request was using the same connection and hence the database session was not terminated after every call.. Deleting rows from temp table before strating any processing made it work....
上面的statemnet 成功了。我的前端正在使用连接池，在每次过程调用之后，它正在提交事务，但仍将连接保留在连接池中，随后的请求使用相同的连接，因此每次调用后数据库会话都没有终止.. 从 temp 中删除行在进行任何处理之前的表使它工作....
It drove me nuts till i found this solution....
它让我发疯，直到我找到这个解决方案......
