下面这个存储过程怎么返回结果集?
create or replace procedure portcount is
v_id port.id%TYPE;
v_sum NUMBER(20);
v_num port.connectnum%TYPE;
CURSOR c_emp IS select P.ID,P.CONNECTNUM from port p where p.eqid in (select eq.id from equipment eq where typecode=3 and kindcode=0);
begin
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id,v_num;
EXIT WHEN c_emp%NOTFOUND;
select count(1) into v_sum from connection
where (objectid = v_id AND objecttablename = 'PORT')
OR PORTID = v_id;
DBMS_OUTPUT.PUT_LINE(v_id||' '|| v_num||' '|| v_sum);
END LOOP;
CLOSE c_emp;
end;
create or replace procedure portcount is
v_id port.id%TYPE;
v_sum NUMBER(20);
v_num port.connectnum%TYPE;
CURSOR c_emp IS select P.ID,P.CONNECTNUM from port p where p.eqid in (select eq.id from equipment eq where typecode=3 and kindcode=0);
begin
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id,v_num;
EXIT WHEN c_emp%NOTFOUND;
select count(1) into v_sum from connection
where (objectid = v_id AND objecttablename = 'PORT')
OR PORTID = v_id;
DBMS_OUTPUT.PUT_LINE(v_id||' '|| v_num||' '|| v_sum);
END LOOP;
CLOSE c_emp;
end;
存储过程返回游标一例:
SQL> create or replace procedure test(mycursor out sys_refcursor) is
2 begin
3 open mycursor for select * from emp;
4 end test;
5 /Procedure created.SQL> var c1 sys_refcursor;
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]SQL> var c1 refcursorSQL> exec test(:c1);PL/SQL procedure successfully completed.SQL> print :c1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.
where (objectid = v_id AND objecttablename = 'PORT')
OR PORTID = v_id; )
返回集就是打印的那个v_id,v_num,v_sum
type REF_CURSOR is ref cursor;
end pkg_const;
/create or replace procedure SBGL_SELECT(
in_deptno in number,
out_curemp out pkg_const.ref_cursor
) is
begin
open out_curemp for
select * from emp where deptno=in_deptno;
end SBGL_SELECT;
/
declare
emptab pkg_const.ref_cursor;
emprow emp%rowtype;
begin
sbgl_select(10,emptab);
loop
FETCH emptab INTO emprow;
EXIT WHEN emptab%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name = ' || emprow.ename);
end loop;
end;
/
select v_id, v_num, v_sum from dual;其中out_curemp就是返回结果集
create or replace package pkg_const as
type REF_CURSOR is ref cursor;
end pkg_const;
/
create or replace procedure portcount(out_curemp out pkg_const.ref_cursor ) is
begin
OPEN out_curemp FOR
select P.ID id ,
P.CONNECTNUM num
(select count(1) from connection where (object_id=p.id and objecttablename = 'PORT') OR PORTID = p.id) sum
from port p where p.eqid in (select eq.id from equipment eq where typecode=3 and kindcode=0);
END;
/这里out_curemp是返回的结果集.如果想要调用这个结果集,用下面的程序declare
emptab pkg_const.ref_cursor;
emprow emp%rowtype;
begin
portcount(emptab);
loop
FETCH emptab INTO emprow;
EXIT WHEN emptab%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name = ' || emprow.ename);
end loop;
end;
/