下面这个存储过程怎么返回结果集?
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;
解决方案 »
- oracle 包的问题
- oracle 10g sqlplus进不去?提示no listener
- 请教个SQL
- oracle每个表的每次插入、删除、更新都要求记入日志信息,有好的解决方案吗?
- 怎样远程导出oracle的表结构
- Oracle的初始参数怎么没有job_queue_interval,我要修改它要在哪里改?谢谢(无内容)
- sybase的临时表是在高速缓存中操作。Oracle的临时表是物理表,是否有什么方式在高速缓存中操作数据?
- 高分求Oracle售前方案
- 如何在ORACLE 8中创建一个BLOB字段?
- Delphi 在ODBC连接中,如何执行PL/SQL语句
- 如何实现定时对Oracle10g数据库中的数据进行处理
- 这两个语句的查询结果一样吗?
存储过程返回游标一例:
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;
/