请问存储过程如何返回一个结果集？望指点一二

Question

这是oracle自己提供的一个例子，返回的是一个结果集，用的是cursor
CREATE PACKAGE emp_data AS
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, 
                          choice IN NUMBER);
END emp_data;CREATE PACKAGE BODY emp_data AS
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, 
                          choice IN NUMBER) IS
   BEGIN
      IF choice = 1 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
      ELSIF choice = 2 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
      ELSIF choice = 3 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
      END IF;
   END open_emp_cv;
END emp_data;

Answer 1

过程返回结果集:CREATE OR REPLACE PACKAGE pkg_test
AS
   TYPE myrctype IS REF CURSOR;   PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
   PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
   IS
      sqlstr   VARCHAR2 (500);
   BEGIN
      IF p_id = 0 THEN
         OPEN p_rc FOR
            SELECT ID, NAME, sex, address, postcode, birthday
              FROM student;
      ELSE
         sqlstr :=
            'select id,name,sex,address,postcode,birthday
           from student where id=:w_id';
         OPEN p_rc FOR sqlstr USING p_id;
      END IF;
   END get;
END pkg_test;
/函数返回结果集:CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
   不加return类型，为弱类型，允许动态sql查询，
   否则为强类型，无法使用动态sql查询;
*/
  type myrctype is ref cursor; 
 
--函数申明
  function get(intID number) return myrctype;
end pkg_test;
/
 
CREATE OR REPLACE
package body pkg_test as
--函数体
   function get(intID number) return myrctype is
     rc myrctype;  --定义ref cursor变量
     sqlstr varchar2(500);
   begin
     if intID=0 then
        --静态测试，直接用select语句直接返回结果
        open rc for select id,name,sex,address,postcode,birthday from student;
     else
        --动态sql赋值，用:w_id来申明该变量从外部获得
        sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
        --动态测试，用sqlstr字符串返回结果，用using关键词传递参数
        open rc for sqlstr using intid;
     end if;
 
     return rc;
   end get;
 
end pkg_test;
/

Answer 2

能追加一个问题吗？在asp.net如何将oracle结果集填充到dataset

Answer 3

除了使用游标外，存储过程或函数还可以直接返回记录类型的数据，或表类型的数据
比如：
TYPE StuBody_Type AS OBJECT
    (khbh      VARCHAR2(20),
     khmc      VARCHAR2(60),
     qfje      NUMBER(14));TYPE Tab_Type IS TABLE OF StuBody_Type%ROWTYPE
    INDEX BY BINARY_INTEGER;等都可以直接作为参数返回，下面是我自己写的一个例子：CREATE TABLE StuBody_Type 
    (khbh      VARCHAR2(20),
     khmc      VARCHAR2(60),
     qfje      NUMBER(14));CREATE TABLE StuHead_Type
    (yhzh      VARCHAR2(20),
     rowcount  NUMBER(5),
     sumje     NUMBER(14));CREATE OR REPLACE PACKAGE PackageWang AS TYPE Tab_Type IS TABLE OF StuBody_Type%ROWTYPE
    INDEX BY BINARY_INTEGER; FUNCTION RetuQfqk (Qfxx out Tab_Type) return StuHead_Type%ROWTYPE;END PackageWang;
/CREATE OR REPLACE PACKAGE BODY PackageWang AS FUNCTION RetuQfqk (Qfxx out Tab_Type) return StuHead_Type%ROWTYPE IS
  Retu StuHead_Type%ROWTYPE;
 BEGIN
  Qfxx(1).khbh := '0001';
  Qfxx(1).khmc := '东村';
  Qfxx(1).qfje := 12500;
  Qfxx(2).khbh := '0002';
  Qfxx(2).khmc := '南村';
  Qfxx(2).qfje := 17312;
  Qfxx(3).khbh := '0003';
  Qfxx(3).khmc := '西村';
  Qfxx(3).qfje := 50519;
  Qfxx(4).khbh := '0004';
  Qfxx(4).khmc := '北村';
  Qfxx(4).qfje := 45341;
  Retu.yhzh := '0402100003843226';
  Retu.rowcount := 4;
  Retu.sumje := 125672;
  return retu;
 END RetuQfqk;END PackageWang;
/create or replace procedure sy as
 Retu StuHead_Type%ROWTYPE;
 qfxx PackageWang.Tab_Type;
begin
 Retu := PackageWang.RetuQfqk(qfxx);
 dbms_output.put_line(chr(10));
 dbms_output.put_line('合计项--> 银行帐号：' || Retu.yhzh || '；合计行数：' || to_char(Retu.rowcount) || '；合计金额：' || to_char(Retu.sumje) || '。');
 dbms_output.put_line('客户编号：' || Qfxx(1).khbh || '；客户名称：' || Qfxx(1).khmc || '；欠费金额：' || to_char(Qfxx(1).qfje) || '。');
 dbms_output.put_line('客户编号：' || Qfxx(2).khbh || '；客户名称：' || Qfxx(2).khmc || '；欠费金额：' || to_char(Qfxx(2).qfje) || '。');
 dbms_output.put_line('客户编号：' || Qfxx(3).khbh || '；客户名称：' || Qfxx(3).khmc || '；欠费金额：' || to_char(Qfxx(3).qfje) || '。');
 dbms_output.put_line('客户编号：' || Qfxx(4).khbh || '；客户名称：' || Qfxx(4).khmc || '；欠费金额：' || to_char(Qfxx(4).qfje) || '。');
end sy;
/上面的SY是做测试用的。

Answer 4

在asp是这样调用，相对应asp.net自已相应修改：
<%@LANGUAGE="VBSCRIPT"%> 
<!--#include file="Connections/conn.asp" -->
<% 
set cm1 = Server.CreateObject("ADODB.Command")
cm1.ActiveConnection = MM_conn_STRING
cm1.CommandType = 1
cm1.CommandTimeout = 0
cm1.Prepared = true
cm1.CommandText = "{call pkg_test.get(?)}"
set para = cm1.Parameters
para.Append cm1.CreateParameter("p_id",3,1,,1)
set rs = Server.CreateObject("ADODB.Recordset")set rs=cm1.Execute  
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>中山大学附属第五医院</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<LINK 
href="images/med.css" type=text/css 
rel=stylesheet>
</head>
<body bgcolor="#B2CFE4" text="#000000">
<%
   while not rs.eof 
   response.write rs("name")
   rs.movenext()
   wend %></body>
</html>
<%
rs.close
cm1.ActiveConnection.Close
set cm1=nothing
%>