SQL 如何通过PL/SQL中的变量动态插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/696612/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert dynamically through a variable in PL/SQL?
提问by Tony Andrews
Lets create a table first
让我们先创建一个表
create table test
(
id number,
name varchar2(20)
);
Now during insert, I want to hold the data into variable first & then dynamically pass the variable into the VALUES clause like this:
现在在插入期间，我想先将数据保存到变量中，然后像这样将变量动态传递到 VALUES 子句中：
declare
v_data varchar2(50);
begin
v_data:='1,sunny';
execute immediate 'insert into test values(v_data)';
commit;
end;
But its showing some errors(Not enough values)...... plz help how to achieve this??
但它显示一些错误（值不够）......请帮助如何实现这一点？？
回答by cletus
Table test has two columns. You're only inserting one and not naming which column it is hence "not enough values". So you need:
表测试有两列。您只插入一个而不是命名它是哪一列，因此“没有足够的值”。所以你需要：
INSERT INTO test (name) VALUES (data)
or probably better is to put in an ID:
或者可能更好的是放入一个 ID：
INSERT INTO test (id, name) VALUES (1, data)
or simply:
或者干脆：
INSERT INTO test VALUES (1, data)
For this kind of thing though I would use a cursor rather than dynamic SQL (or even inline SQL).
对于这种事情，尽管我会使用游标而不是动态 SQL（甚至内联 SQL）。
回答by Dheer
You need to use different variables for each value
您需要为每个值使用不同的变量
declare
v_data1 number
v_data2 varchar2(50);
begin
v_data1 :=1
v_data2 = 'sunny';
insert into test values(v_data1,v_data2);
-- Alternatively insert into test (Name) values (v_data2);
commit;
end;
回答by Tony Andrews
The normal way to pass values into dynamic SQL statements is with bind variables like this:
将值传递给动态 SQL 语句的正常方法是使用如下绑定变量：
declare
v_id integer;
v_name varchar2(50);
begin
v_id := 1;
v_name := 'sunny';
execute immediate
'insert into test (id, name) values(:b1, :b2)'
using v_id, v_name;
commit;
end;
That needs one variable per value.
每个值需要一个变量。
回答by Tudor Olariu
Your approach works, but you need to adjust your query a little:
您的方法有效，但您需要稍微调整一下查询：
execute immediate 'insert into test values(' || v_data|| ')';
so that the contents of your v_data variable are actually inserted into the string, not the value "v_data" itself.
以便您的 v_data 变量的内容实际上插入到字符串中，而不是值“v_data”本身。
