declare
TYPE t_record IS RECORD(
id test.id%type,
mc test.mc%type);
--- var_record t_record;
type t_test is table of t_record;
var_test t_test;
cursor cur_test is
select id, mc from test;
begin
open cur_test;
fetch cur_test BULK COLLECT
INTO var_test;
for i in 1 .. var_test.count() loop
dbms_output.put_line(var_test(i).id || '---' || var_test(i).mc);
end loop;
end;
Compilation errors for TYPE PETER.T_RECORD
Error: PLS-00597: INTO 列表中的表达式 'VAR_TEST'类型错误
Line: 16
Text: fetch cur_test BULK COLLECT
Error: PL/SQL: SQL Statement ignored
Line: 15
Text: open cur_test;
========================
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE
9.0.1.1.1
Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production
改为如下即可！
declare
TYPE t_record IS RECORD
(
id test.id%type,
mc test.mc%type);
type t_test is table of t_record;
var_test t_test;
cursor cur_test is
select id, mc from test;
begin
select id, mc bulk collect into var_test from test;
for i in 1 .. var_test.count loop
dbms_output.put_line(var_test(i).id || '---' || var_test(i).mc);
end loop;
end;
Before Oracle9i 9.2 there was an restriction of BULK COLLECT INTO
colletion of records, this restriction is lifted, though FETCH..BULK COLLECT
INTO a PL/SQL collection of records incorrectly raises error PLS-00597 when the
record type is defined as a PL/SQL record or using CURSOR%ROWTYPE. No error is
raised if the record is defined using TABLE%ROWTYPE.
This is fixed in Oracle9i 9.2.0.3 and higher versions
在我这运行没错.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE
9.2.0.8.0
Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
