ORACLE的PROCEDURE 能返回数据集吗？（很菜的问题）

Question

返回记录集过程:
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;
/
--------------------------------------------------------------------------------------------------------------
返回记录集函数:
1、建立测试表
CREATE TABLE student
(
 id                         NUMBER,
 name                       VARCHAR2(30),
 sex                        VARCHAR2(10),
 address                    VARCHAR2(100),
 postcode                   VARCHAR2(10),
 birthday                   DATE,
 photo                      LONG RAW
);
/2、建立带ref cursor定义的包和包体及函数：
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;
/3、用pl/sql块进行测试：
declare
 w_rc       pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量，用于显示结果
 w_id       student.id%type;
 w_name     student.name%type;
 w_sex      student.sex%type;
 w_address  student.address%type;
 w_postcode student.postcode%type;
 w_birthday student.birthday%type;begin
 --调用函数，获得记录集
 w_rc := pkg_test.get(1); --fetch结果并显示
loop
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;4、测试结果：
通过。
--------------------------------------------------------------------------------------------------------------
返回对象类型:
SQL> create table a (id number,name varchar2(50),doctime date);Table created.--插入六条测试数据：
SQL> insert into a values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));1 row created.SQL> insert into a values (2,'bbb',to_date('2002-07-02','yyyy-mm-dd'));1 row created.SQL> insert into a values (3,'ccc',to_date('2002-07-03','yyyy-mm-dd'));1 row created.SQL> insert into a values (4,'ddd',to_date('2002-07-04','yyyy-mm-dd'));1 row created.SQL> insert into a values (5,'eee',to_date('2002-07-05','yyyy-mm-dd'));1 row created.SQL> insert into a values (6,'fff',to_date('2002-07-06','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.--创建两个type
SQL> create or replace type myobjectype as object (x int,y date,z varchar2(50));
 2  /Type created.SQL> create or replace type mytabletype as table of myobjectype
 2  /Type created.--创建可以返回纪录集的函数(不传入表名参数)
SQL> create or replace function testrerecordnotabname (tableid in number)
 2  return mytabletype
 3  as
 4    l_data mytabletype :=mytabletype();
 5  begin
 6    for i in (select * from a where id>=tableid)  loop
 7      l_data.extend;
 8      l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name);
 9      exit when i.id = 62;
10    end loop;  
11    return l_data;     
12  end;   
13  /Function created.SQL> commit;Commit complete.

Answer 1

--创建可以返回纪录集的函数(可以传入表名参数)
SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number)
 2  return mytabletype
 3  as
 4    l_data mytabletype :=mytabletype();
 5    strsql varchar2(50);
 6    type v_cursor is ref cursor;
 7    v_tempcursor v_cursor;
 8    i1 number;
 9    i2 varchar2(50);
10    i3 date;
11  begin
12    strsql := 'select * from ' || tablename || ' where id>=' || tableid;
13    open v_tempcursor for strsql;
14    loop 
15      fetch v_tempcursor into i1,i2,i3;
16      l_data.extend;
17      l_data(l_data.count) := myobjectype(i1,i3,i2);
18      exit when v_tempcursor%NOTFOUND;
19    end loop;  
20    return l_data;     
21  end;   
22  /Function created.SQL> commit;Commit complete.--测试不传表名参数的function(testrerecorenotabname)
SQL> set serveroutput on     
SQL> declare
 2    testre mytabletype :=mytabletype();
 3    i number :=0;
 4  begin
 5    testre := testrerecordnotabname(1);
 6    loop
 7      i := i+1;
 8      dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
 9      exit when i = testre.count;
10    end loop;
11  end;
12  /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;PL/SQL procedure successfully completed.--测试传表名参数的function(testrerecoretabname)
SQL> set serveroutput on     
SQL> declare
 2    testre mytabletype :=mytabletype();
 3    i number :=0;
 4  begin
 5    testre := testrerecordtabname('a',1);
 6    loop
 7      i := i+1;
 8      dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
 9      exit when i = testre.count;
10    end loop;
11  end;
12  /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;
;6;06-7?? -02;fff;PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------------------------------------
利用对象类型通过字符串分析出数据函数:
create or replace type mytabletype as table of number;
/create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln   number;
ldata   mytabletype:=mytabletype();
begin
loop
 ln:=instr(lstr,',');
 exit when (nvl(ln,0)=0);
 ldata.extend;
 ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
 lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/SQL> select * from table(cast(strtab('11,12,13') as mytabletype));COLUMN_VALUE
------------
         11
         12
         13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME
-- ----------
11 张三
12 李四
13 王五
------------------------------------------------------------------------------------------------------------------
字段类型为嵌套表的使用方法:
CREATE TYPE Course AS OBJECT (
   course_no NUMBER(4), 
   title     VARCHAR2(35),
   credits    NUMBER(1));
/CREATE TYPE CourseList AS TABLE OF Course;
/CREATE TABLE division (
   name     VARCHAR2(20),
   director VARCHAR2(20),
   office   VARCHAR2(20),
   courses  CourseList) 
   NESTED TABLE courses STORE AS courses_tab;
INSERT INTO division
     VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
        CourseList(Course(1000, 'General Psychology', 5),
                   Course(2100, 'Experimental Psychology', 4),
                   Course(2200, 'Psychological Tests', 3),
                   Course(2250, 'Behavior Modification', 4),
                   Course(3540, 'Groups and Organizations', 3),
                   Course(3552, 'Human Factors in the Workplace', 4),
                   Course(4210, 'Theories of Learning', 4),
                   Course(4320, 'Cognitive Processes', 4),
                   Course(4410, 'Abnormal Psychology', 4)));
INSERT INTO division
     VALUES('History', 'John Whalen', 'Applegate Hall 142',
        CourseList(Course(1011, 'History of Europe I', 4),
                   Course(1012, 'History of Europe II', 4),
                   Course(1202, 'American History', 5),
                   Course(2130, 'The Renaissance', 3),
                   Course(2132, 'The Reformation', 3),
                   Course(3105, 'History of Ancient Greece', 4),
                   Course(3321, 'Early Japan', 4),
                   Course(3601, 'Latin America Since 1825', 4),
                   Course(3702, 'Medieval Islamic History', 4)));
INSERT INTO division
     VALUES('English', 'Lynn Saunders', 'Breakstone Hall 205',
        CourseList(Course(1002, 'Expository Writing', 3),
                   Course(2020, 'Film and Literature', 4),
                   Course(2418, 'Modern Science Fiction', 3),
                   Course(2810, 'Discursive Writing', 4),
                   Course(3010, 'Modern English Grammar', 3),
                   Course(3720, 'Introduction to Shakespeare', 4),
                   Course(3760, 'Modern Drama', 4),
                   Course(3822, 'The Short Story', 4),
                   Course(3870, 'The American Novel', 5)));
----------------------------------------------------------------------------------------------------------------
游标只是plsql中使用的变量并不是oracle的对象，所以它不能在过程或函数间进行传递。
下面的方法是通过table类型的对象来存储要返回的记录集。
--创建测试表：
SQL> create table a (id number,name varchar2(50),doctime date);Table created.--插入六条测试数据：
SQL> insert into a values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));1 row created.SQL> insert into a values (2,'bbb',to_date('2002-07-02','yyyy-mm-dd'));1 row created.SQL> insert into a values (3,'ccc',to_date('2002-07-03','yyyy-mm-dd'));1 row created.SQL> insert into a values (4,'ddd',to_date('2002-07-04','yyyy-mm-dd'));1 row created.SQL> insert into a values (5,'eee',to_date('2002-07-05','yyyy-mm-dd'));1 row created.SQL> insert into a values (6,'fff',to_date('2002-07-06','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.--创建两个type
SQL> create or replace type myobjectype as object (x int,y date,z varchar2(50));
 2  /Type created.SQL> create or replace type mytabletype as table of myobjectype
 2  /Type created.--创建可以返回纪录集的过程
SQL> create or replace procedure testrerecordnotabname (tableid in number, l_data out mytabletype)
 2 
 3  as
 4    
 5  begin
 6    for i in (select * from a where id>=tableid)  loop
 7      l_data.extend;
 8      l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name);
 9      exit when i.id = 62;
10    end loop;  
11    
12  end;   
13  /Function created.SQL> commit;Commit complete.SQL> commit;Commit complete.--测试不传表名参数的procedure(testrerecorenotabname)
SQL> set serveroutput on     
SQL> declare
 2    testre mytabletype :=mytabletype();
 3    i number :=0;
 4  begin
 5    testrerecordnotabname(1,testre);
 6    loop
 7      i := i+1;
 8      dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
 9      exit when i = testre.count;
10    end loop;
11  end;
12  /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;PL/SQL procedure successfully completed. 

Answer 2

beckhambobo(beckham)procedure返回的结果集如何用ASP.NET,C#取得,并转换DataSet?

Answer 3

所以我觉得oracle与sqlserver比用起来真的没sql server 这么方便

Answer 4

放在package里面是可以的，返回的东西很灵活，数据集，参数，都可以

Answer 5

to ： beckhambobo(beckham)
我用的就是你在OTN的貼子，請不要見怪：）