代码:
DECLARE
TYPE T_SNO IS TABLE OF ACN_Test_Studeng.SNO%TYPE INDEX BY BINARY_INTEGER;
CURSOR student_sno_cur IS
SELECT SNO
FROM ACN_Test_Studeng;
v_sno T_SNO;
BEGIN
OPEN student_sno_cur;
LOOP
FETCH student_sno_cur INTO v_sno;
exit;
END LOOP;
CLOSE student_sno_cur;
END;报错内容:ORA-06550:line 10,column 30;
PLS-00597:expression 'V_SNO' in the INTO list is of wrong type
ORA-06550:line 10,column 3;
PL/SQL:SQL Statement ignored我的ORACLE版本是:9.2.0.3.0
SQLPLUS版本是:8.0.0.0.0
DECLARE
TYPE T_SNO IS TABLE OF ACN_Test_Studeng.SNO%TYPE INDEX BY BINARY_INTEGER;
CURSOR student_sno_cur IS
SELECT SNO
FROM ACN_Test_Studeng;
v_sno T_SNO;
BEGIN
OPEN student_sno_cur;
LOOP
FETCH student_sno_cur INTO v_sno;
exit;
END LOOP;
CLOSE student_sno_cur;
END;报错内容:ORA-06550:line 10,column 30;
PLS-00597:expression 'V_SNO' in the INTO list is of wrong type
ORA-06550:line 10,column 3;
PL/SQL:SQL Statement ignored我的ORACLE版本是:9.2.0.3.0
SQLPLUS版本是:8.0.0.0.0
TYPE T_SNO IS TABLE OF ACN_Test_Studeng.SNO%TYPE INDEX BY BINARY_INTEGER;
CURSOR student_sno_cur IS
SELECT SNO
FROM ACN_Test_Studeng;
v_sno T_SNO;
BEGIN
OPEN student_sno_cur;
LOOP
FETCH student_sno_cur bulk collect INTO v_sno;
exit student_sno_cur%notfound;
END LOOP;
CLOSE student_sno_cur;
END;
procedure test_tab_proc
as
TYPE T_SNO_type IS TABLE OF dept.deptno%TYPE INDEX BY BINARY_INTEGER;
CURSOR student_sno_cur IS
SELECT deptno
FROM dept;create or replace
procedure test_tab_proc
as
TYPE T_SNO_type IS TABLE OF dept.deptno%TYPE INDEX BY BINARY_INTEGER;
CURSOR student_sno_cur IS
SELECT deptno
FROM dept;
v_sno T_SNO_type;
BEGIN
OPEN student_sno_cur;
LOOP
FETCH student_sno_cur bulk collect INTO v_sno;
exit when student_sno_cur%NOtFOUND;
END LOOP;
CLOSE student_sno_cur;
for i in 1..v_sno.count loop
dbms_output.put_line(v_sno(i));
end loop;
END; v_sno T_SNO_type;
BEGIN
OPEN student_sno_cur;
LOOP
FETCH student_sno_cur bulk collect INTO v_sno;
exit when student_sno_cur%NOtFOUND;
END LOOP;
CLOSE student_sno_cur;
for i in 1..v_sno.count loop
dbms_output.put_line(v_sno(i));
end loop;
END;
没指定limit参数,默认为unlimit
参考
http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html
"狂浪"说的对,默认的bulk collect抓取的数据是unlimit,即:把游标中的所有数据批量弄到table中去,所以可以在外面不用loop。DECLARE
TYPE T_SNO IS TABLE OF ACN_Test_Studeng.SNO%TYPE INDEX BY BINARY_INTEGER;
CURSOR student_sno_cur IS
SELECT SNO
FROM ACN_Test_Studeng;
v_sno T_SNO;
BEGIN
OPEN student_sno_cur;
FETCH student_sno_cur bulk collect INTO v_sno;
CLOSE student_sno_cur;
END;