Oracle 10中的本地临时表（对于存储过程的范围） [英] Local Temporary table in Oracle 10 (for the scope of Stored Procedure)
问题描述
我是oracle的新手。我需要在存储过程中处理大量的数据。我正在考虑使用临时表。我使用连接池和应用程序是多线程的。
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.
是否有一种创建临时表的方法，即每次调用存储过程时创建不同的表实例，以便多个存储过程调用的数据不会
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?
推荐答案
你说你是Oracle的新手。我猜你是习惯了SQL Server，它是相当常见的使用临时表。 Oracle的工作方式不同，所以它不太常见，因为它不太必要。
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.
请注意，使用临时表会产生以下开销：
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
大多数活动在帮助您完成工作方面毫无用处。一个更好的想法是看看你是否可以在一个单一的动作，最好是纯SQL中做一切。
Most of that activity is useless in terms of helping you get stuff done. A better idea is to see if you can do everything in a single action, preferably pure SQL.
的连接池提出了另一个问题。处理大量数据的进程不是在OLTP模式下运行的好候选。你真的应该考虑启动一个后台（即asysnchronous）进程，可能是一个数据库作业，来运行你的存储过程。如果你想定期运行这个工作，这是特别真实的，因为我们可以使用DBMS_SCHEDULER来自动管理这些事情。
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.
这篇关于Oracle 10中的本地临时表（对于存储过程的范围）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
