PL/SQL 嵌入插入到可能不存在的表中
我更喜欢在 pl/sql 块中使用这种“嵌入”样式插入（与执行立即样式动态 sql 相反 - 您必须在其中分隔引号等）。
-- a contrived example
PROCEDURE CreateReport( customer IN VARCHAR2, reportdate IN DATE )
BEGIN
-- drop table, create table with explicit column list
CreateReportTableForCustomer;
INSERT INTO TEMP_TABLE
VALUES ( customer, reportdate );
END;
/
这里的问题是，oracle 检查“temp_table”是否存在以及它是否具有正确的列数，如果不存在则抛出编译错误。
所以我想知道是否有什么办法可以解决这个问题？本质上，我想使用表名的占位符来欺骗 Oracle 不检查该表是否存在。
编辑：
我应该提到用户能够执行任何“报告”（如上所述）。一种执行任意查询但始终写入 temp_table（在用户模式中）的机制。因此，每次运行报告过程时，它都会删除 temp_table 并使用（很可能）不同的列列表重新创建它。
I much prefer using this 'embedded' style inserts in a pl/sql block (opposed to the execute immediate style dynamic sql - where you have to delimit quotes etc).
-- a contrived example
PROCEDURE CreateReport( customer IN VARCHAR2, reportdate IN DATE )
BEGIN
-- drop table, create table with explicit column list
CreateReportTableForCustomer;
INSERT INTO TEMP_TABLE
VALUES ( customer, reportdate );
END;
/
The problem here is that oracle checks if 'temp_table' exists and that it has the correct number of colunms and throws a compile error if it doesn't exist.
So I was wondering if theres any way round that?! Essentially I want to use a placeholder for the table name to trick oracle into not checking if the table exists.
EDIT:
I should have mentioned that a user is able to execute any 'report' (as above). A mechanism that will execute an arbitrary query but always write to the temp_table ( in the user's schema). Thus each time the report proc is run it drops the temp_table and recreates it with, most probably, a different column list.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以使用动态 SQL 语句插入到可能存在的 temp_table 中，然后捕获并处理该表不存在时发生的异常。
示例：
请注意，动态 SQL 语句中的表名不同并不是很好，因此如果确保表名保持不变，那就最好了。
You could use a dynamic SQL statement to insert into the maybe-existent temp_table, and then catch and handle the exception that occurs when the table doesn't exist.
Example:
Note that having the table name vary in a dynamic SQL statement is not very good, so if you ensure the table names stay the same, that would be best.
也许您应该使用 全局临时表< /a>（GTT）。这些是保存 Oracle 会话临时数据的永久表结构。许多不同的会话可以将数据插入到同一个 GTT 中，并且每个会话只能看到自己的数据。根据 GTT 的定义，数据会在 COMMIT 时或会话结束时自动删除。
您可以像这样创建 GTT（仅一次）：
* 删除适用的
然后您的程序可以像任何其他表一样使用它 - 唯一的区别是它对于您的会话总是以空开始。
Maybe you should be using a global temporary table (GTT). These are permanent table structures that hold temporary data for an Oracle session. Many different sessions can insert data into the same GTT, and each will only be able to see their own data. The data is automatically deleted either on COMMIT or when the session ends, according to the GTT's definition.
You create the GTT (once only) like this:
* delete as applicable
Then your programs can just use it like any other table - the only difference being it always begins empty for your session.
使用 GTT 比动态删除/重新创建表要好得多 - 如果您的应用程序需要为每个报告使用不同的结构，我强烈建议您计算出每个报告所需的所有不同结构，并根据每个报告的需要创建单独的 GTT，而不是在运行时创建普通表。
也就是说，如果这是不可行的（我已经看到了一些很好的例子，例如在支持来自用户的各种临时请求的系统中），那么您将不得不使用
立即执行方法。Using GTTs are much preferable to dropping/recreating tables on the fly - if your application needs a different structure for each report, I strongly suggest you work out all the different structures that each report needs, and create separate GTTs as needed by each, instead of creating ordinary tables at runtime.
That said, if this is just not feasible (and I've seen good examples when it's not, e.g. in a system that supports a wide range of ad-hoc requests from users), you'll have to go with the
EXECUTE IMMEDIATEapproach.