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.count) 报错 报必须说明count 组件 。请问不通过游标能遍历这些记录集吗?
还有 只能用游标遍历集合吗? 还有什么其他的方法?
re red_array;
...for i in 1..red_array.count loop
red_array(i).
正解,可以用type来做的。
into 字句仅仅在单行查询中使用,指定值是变量或记录。
type emp_type is table of emp%rowtype index by binary_integer;
emp_rec emp_type;
begin
select * bulk collect into emp_rec from emp;
for i in 1..emp_rec.count loop
dbms_output.put_line('empno='||emp_rec(i).empno||'; ename='||emp_rec(i).ename||'; job='||emp_rec(i).job||
'; mgr='||emp_rec(i).mgr);
end loop;
end;
v_str varchar2(500);
type red is table of a1%rowtype index by binary_integer;
re red;
cur sys_refcursor;
i number:=1;
begin
v_str := 'select id,name from a1 ';
open cur for v_str;
loop
fetch cur into re(i);
exit when cur%notfound;
i:=i+1;
end loop;
close cur;for j in 1..re.count loop
dbms_output.put_line(re(j).id||''||re(j).name);
end loop;
end;
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;
execute immediate v_sql bulk collect into 集合变量
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;用7楼和9楼的方法做起来了。我将原来的record 变成了表。
type 名字 is record 只能放1条记录?