Oracle 中临时表的替代方案
- 在存储过程中创建一个临时表,例如“#Temp”。
- 使用 select 语句将值插入“Temp”表,例如。插入#Temp 从员工中选择*。
- 现在从此临时表中提取数据,例如。从 #Temp 中选择 *,其中 #Temp.Id = @id &很快。
如何在 Oracle 存储过程中中执行此操作?
- 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.
How to do this in Oracle inside a stored procedure?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(2)
情深如许2024-12-14 02:48:03
创建全局临时表。
CREATE GLOBAL TEMPORARY TABLE <your_table>
ON COMMIT PRESERVE ROWS # If needed. Depends on your needs.
AS SELECT <your_select_query>;
然后,您可以在手术期间根据需要从表中进行选择。
http://www.oracle-base.com/articles/8i/TemporaryTables.php < /一>
<一href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548" rel="nofollow">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548
~没有更多了~
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
您想要解决的业务问题是什么?在 Oracle 中需要使用临时表的情况非常罕见。为什么不简单地
在其他数据库中,您经常创建临时表,因为读取器会阻止写入器,因此您希望创建数据的单独副本以避免阻塞任何其他会话。然而,在 Oracle 中,读取器永远不会阻止写入器,因此通常不需要保存数据的单独副本。
在其他数据库中,您创建临时表是因为您不想进行脏读。然而,Oracle 不允许脏读。多版本读取一致性意味着 Oracle 将始终向您显示查询启动时(或者如果您将事务隔离级别设置为可序列化,则事务启动时)时存在的数据。所以不需要创建临时表来避免脏读。
如果您确实想要在 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
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.
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.
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
As I said, though, it would be very unusual in Oracle to actually want to use a temporary table.