动态创建临时表 [英] Created Temporary Tables Dynamically

问题描述

我有这样的情况，我需要创建一个存储过程，其中的列将动态出现.基于此列，应创建临时表. 实际上，这种情况是我在SQL Server中所做的，并且已经将SQL Server数据库迁移到Oracle.

I have scenario like, I need to create a stored procedure in which the columns will come dynamically. Based upon this columns temporary tables should be created. Actually, this scenario I did in SQL Server and I have migrated the SQL Server database to Oracle.

谢谢

推荐答案

如果要动态创建表，则需要使用动态SQL

If you want to create a table dynamically, you'd need to use dynamic SQL

BEGIN
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE table_name( col1 number, col2 number )';
END;

话虽这么说，直接将创建临时表的SQL Server代码直接移植到Oracle几乎是一个错误.它的工作方式与您的SQL Server代码不同.

That being said, it is almost certainly a mistake to directly port SQL Server code that creates temporary tables to Oracle. It will not work the same way that your SQL Server code does.

• 首先，Oracle中的临时表对于每个会话都是可见的，与SQL Server不同，这些临时表在会话中是本地的.这意味着，如果您有两个调用存储过程的会话，它们都将尝试创建临时表，而第二个将失败(假设您也在过程的末尾而不是删除临时表，情况会变得更糟).累积数千个临时表).
在给定所需的闩锁数量的情况下，
• 在Oracle中创建表是一个低效的过程. Oracle的假设是您不是在动态创建对象.如果您的应用程序违反了该假设，则会遇到一些可伸缩性问题.
• 如果要动态创建表，则必须动态引用它们.因此，对临时表的任何查询也将需要使用动态SQL.这比静态SQL效率低，并且使您的代码更难编写，更难调试和更难维护.

• First off, temporary tables in Oracle are visible to every session unlike SQL Server where they are local to the session. That means that if you have two sessions calling your stored procedure, they'd both try to create the temporary table and the second one would fail (things get worse assuming that you are also dropping the temporary table at the end of the procedure rather than letting thousands of temporary tables accumulate).
• Creating tables is an inefficient process in Oracle given the amount of latching that is required. Oracle's assumption is that you are not creating objects on the fly. If your application violates that assumption, you will encounter some scalability issues.
• If you create tables on the fly, you'll have to refer to them on the fly. So any queries against the temporary tables will also need to use dynamic SQL. That is less efficient than static SQL and it makes your code harder to write, harder to debug, and harder to maintain.

这篇关于动态创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！