每次fetch只能得一个值,不能存取所有行记录。
set severoutput on
DECLARE
CURSOR cur1 is
SELECT test1 ,test2 from templh order by test2;
v_test1 templh.test1%type;
v_test2 templh.test2%type;
begin
OPEN CUR1;
FETCH CUR1 INTO v_test1,v_test2 ;
dbms_output.put_line(v_test1.v_test2);
WHILE (CUR1%FOUND) LOOP
FETCH CUR1 INTO test1 ,test2 ;
dbms_output.put_line(v_test1.v_test2);
END LOOP;
CLOSE CUR1;
end;
set severoutput on
DECLARE
CURSOR cur1 is
SELECT test1 ,test2 from templh order by test2;
v_test1 templh.test1%type;
v_test2 templh.test2%type;
begin
OPEN CUR1;
FETCH CUR1 INTO v_test1,v_test2 ;
dbms_output.put_line(v_test1.v_test2);
WHILE (CUR1%FOUND) LOOP
FETCH CUR1 INTO test1 ,test2 ;
dbms_output.put_line(v_test1.v_test2);
END LOOP;
CLOSE CUR1;
end;
你要用在哪儿呢?
Cursor 游标名 is select查询语句
例如CURSOR cur1 is SELECT test1 ,test2 from templh order by test2;可以定义一个变量来存放游标取出的值
如 cc cur1%rowtype;
然后在fetch子句中用fetch cur1`into cc;
在下面的引用中可以使用cc.test1来引用该值。
这样写总报错:DECLARE
CURSOR cur1 is错误: 發現了符號 "DECLARE" 當您等待下列事項之一發生時: ( ; is w
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/DECLARE
CUR1 pkg_test.myrctype;
v_test1 templh.test1%type;
v_test2 templh.test2%type;
str varchar2(50);='SELECT test1 ,test2 from templh order by test2';
begin
OPEN CUR1 for str;
FETCH CUR1 INTO v_test1,v_test2 ;
dbms_output.put_line(v_test1.v_test2);
WHILE (CUR1%FOUND) LOOP
FETCH CUR1 INTO test1 ,test2 ;
dbms_output.put_line(v_test1.v_test2);
END LOOP;
CLOSE CUR1;
end;
/
is
--定义部分
begin
--语句部分
end pro;