下面的查询语句返回多条记录,into re,而re只是接收一条记录,所以报错。
declare
v_str varchar2(500);
type red is record
(
v_id integer,
v_name varchar2(30)
);
re red;
begin
v_str := 'select id,name from a1 ';
execute immediate v_str into re;
for i in 1..re.count loop
dbms_output.put_line(i.v_id||''||i.v_name);
end loop;
end;
应该将re声明为red类型的数组,加下面:
1 2 3 4 5 6 7 8 9 10 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
|
如果execute immediate执行的是结果集的话可以用
execute immediate v_sql bulk collect into 集合变量
declare
type red is table of a1%rowtype index by binary_integer;
re red;
begin
execute immediate 'select id,name from a1 ' bulk collect into re;
for j in 1..re.count loop
dbms_output.put_line(re(j).id||''||re(j).name);
end loop;
end;
将原来的record 变成了表。
type 名字 is record 只能放1条记录
type 名字 is record 是存放单行多列的数据
declare
v_str varchar2(500);
type red is table of a1%rowtype index by binary_integer;
re red;
begin
v_str := 'select id,name from a1 ';
execute immediate v_str bulk collect into re;
for i in re.first..re.count loop
dbms_output.put_line(re(i).id||''||re(i).name);
end loop;
end;
参考:https://bbs.csdn.net/topics/320130350