在Oracle中使用立即执行将数据插入表 [英] Inserting Data Into Table Using Execute Immediate in Oracle

问题描述

例如，我有一些表"Test"，其中有一列"my_date".我想做的就是使用一些变量将记录添加到表中:

For example, I have some table "Test" which has one column "my_date" . What I am trying to do is adding record to a table using some variable:

query_date := "SELECT sysdate FROM dual";
EXECUTE IMMEDIATE ('insert into test values query_date');

我需要通过构造字符串并执行查询以这种确切方式将记录插入到表中，但是我遇到了错误.有可能这样做吗?

I need to insert the record to the table in this exact way by constructing string and executing query, however I get error. Is it possible to do so?

推荐答案

您可以将第一个查询的结果放入(日期)变量中，然后使用该变量:

You can either get the result of the first query into a (date) variable and then use that:

SELECT sysdate into query_date FROM dual;
insert into test (my_date) values (query_date)
-- or if you really want dynamic SQL, with a bind variable
EXECUTE IMMEDIATE 'insert into test (my_date) values (:query_date)' using query_date;

或者按字面意义阅读您的问题，通过串联将第一个字符串用作第二个字符串的一部分:

Or reading your question literally, use the first string as part of the second string by concatenating it:

query_date := "SELECT sysdate FROM dual";
EXECUTE IMMEDIATE 'insert into test (my_date) ' || query_date;

如果您打印出第二条语句而不是执行它，则会看到:

If you printed out the second statement instead of executing it you'd see:

insert into test (my_date) SELECT sysdate FROM dual

...这是有效的SQL.如果query_string更复杂或它本身是动态构造的，则此方法将起作用.但是，如果query_string选择列表中的列表达式的数量也有所不同，则您也将必须动态地构造列列表，否则插入的列将过多或过少.

... which is valid SQL. This will work if the query_string is more complicated or is itself being constructed dynamically. But if the number of column expressions in the query_string select list also varies, you will have to construct the column list dynamically too, otherwise you'll have too many or too few columns for the insert.

确切的操作方式取决于您构造查询字符串的方式-本质上，是在向查询字符串添加表达式时，还将列名添加到单独的列表中，最后得到:

Exactly how you do that depends on how you're constructing the query string - essentially as you add an expression to the query string, you'd also add a column name to a separate list, and end up with:

EXECUTE IMMEDIATE 'insert into test (' || column_list ' ||) ' || query_string);

其中column_list的构建方式为col1, col2，而query_string的构建方式为select x.col1, y.col2 from ....

where column_list is built up as say col1, col2 and query_string as select x.col1, y.col2 from ....

没有明显的理由在所显示的内容中使用动态SQL.或者，如果您确实在使用sysdate，则只需执行以下操作即可:

There isn't an obvious reason to use dynamic SQL in what you've shown. Or, if you are really using sysdate, any need for separate query to get that, as you can just do:

insert into test (my_date) values (sysdate)

...所以我认为您的实际情况确实更复杂.但是请注意，不要将values关键字与insert ... select ...模式一起使用.您可以使用一个列和一个子查询，但是即使那样也不是一个好主意，并且如果子查询中有多个列，则不起作用.

... so I assume your real scenario is really more complicated. But note that you don't use the values keyword with an insert ... select ... pattern. You can with a single column and a subquery but it's not a good idea even then, and doesn't work if you have multiple columns in the subquery.

这篇关于在Oracle中使用立即执行将数据插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！