17,382
社区成员




DECLARE
ocursor sys_refcursor;
S VARCHAR2(4000);
begin
open ocursor for
select column_name,desc_name from TB ;
select * from ocursor ;
end
另外两种方法是:
declare
ocursor sys_refcursor;
type tb_type is table of tb%rowtype;
v_type tb_type;
begin
open ocursor for
select column_name, desc_name from tb;
fetch ocursor bulk collect into v_type;
for i in v_type.first..v_type.last loop
dbms_output.put_line(v_type(i).column_name ||' '||v_type(i).desc_name);
end loop;
close ocursor;
end;
declare
v_type tb%rowtype;
begin
cursor ocursor is select column_name, desc_name from tb;
open ocursor;
loop
fetch ocursor into v_type;
exit when ocursor%notfound;
dbms_output.put_line(v_type.column_name ||' '||v_type.desc_name);
end loop;
close ocursor;
end;
SQL> SET SERVEROUTPUT ON;
SQL> --1
SQL> DECLARE
2 ocursor SYS_REFCURSOR;
3 S VARCHAR2(4000);
4 v_empno emp.empno%TYPE;
5 v_ename emp.ename%TYPE;
6 BEGIN
7 OPEN ocursor FOR
8 SELECT empno, ename FROM emp;
9 LOOP
10 FETCH ocursor
11 INTO v_empno, v_ename;
12 EXIT WHEN ocursor%NOTFOUND;
13 dbms_output.put_line(v_empno || ',' || v_ename);
14 END LOOP;
15 END;
16
17 /
7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN
7698,BLAKE
7782,CLARK
7788,SCOTT
7839,KING
7844,TURNER
7876,ADAMS
7900,JAMES
7902,FORD
7934,MILLER
PL/SQL 过程已成功完成。
SQL>
SQL> --2
SQL> variable ocursor refcursor;
SQL> begin
2 OPEN :ocursor FOR
3 SELECT empno, ename FROM emp;
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> print :ocursor;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER
已选择14行。
SQL>
declare
ocursor sys_refcursor;
tb_type a%rowtype;
begin
open ocursor for select * from a;
loop
fetch ocursor into tb_type;
exit when ocursor%notfound;
dbms_output.put_line(tb_type.id);
end loop;
close ocursor;
end;