存储过程如何返回记录集？

Question

好像不能返回,只能插入一个表中:
insert into table1 select * from table2

Answer 1

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 T_Person_info;
     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;
/-----------------------------------------------
以前，记得有很多人，说ORACLE的存储过程，不能返回一个ADO的记录集给客户端。这个说法显然是错误的。
下面，给出个例子加以说明
1 ORACLE端建立一个存储包
CREATE OR REPLACE PACKAGE ado_callpkg AS
TYPE eid IS TABLE of NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE ename IS TABLE of VARCHAR2(40) INDEX BY BINARY_INTEGER;
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename);
end ado_callpkg;
CREATE OR REPLACE PACKAGE BODY ado_callpkg  AS
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename) IS
CURSOR c1 IS select employee_id,first_name||','||Middle_Initial||','||last_name as name from employee;
cnt NUMBER DEFAULT 1;
c c1%ROWTYPE;
BEGIN
open c1;
LOOP
      FETCH c1 INTO c;
      empname(cnt):=c.name;
      empid(cnt):=c.employee_id;
      EXIT WHEN c1%NOTFOUND;  -- process the data
       cnt :=cnt+1;
  END LOOP;
close c1;
END;
end ado_callpkg;2 客户端用ADO调用
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim str As String
    
    str = "{call ado_callpkg.getEmpNames({resultset 100,empid,empname})}"
    cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=ORACLE;Persist Security Info=True"
    With cmd
        .CommandText = str
        .ActiveConnection = cn
        .CommandType = adCmdText
    End With
    
    rs.CursorLocation = adUseClient
    rs.Open cmd
    Do While Not rs.EOF
    
        Debug.Print rs.Fields(0).Value & vbTab & rs.Fields(1).Value
        rs.MoveNext
    Loop总结
1 oracle的后台存储过程，应该通过一个类似数组并且带有数字索引的变量返回，有多少个列，就有对应多少个变量
2 前台，调用的sql语句写法要注意，
{call <package_name>.<prodecure name>(<input1>,<input2>,....<inputn>,{resultset <number>,<output1>,<output2>,...<outputn>})}
注意的细节，
(1) <number>要自己指定一个数字，表示接受的行数大小，如果太小，而实际返回的记录大于这个数字，会出错
(2) 如果有输入参数，应该在command中创建输入参数,对应的地方用?替代，如
{call ado_callpkg.getEmpNames(?,{resultset 100,empid,empname})}
(3) output和你存储函数的定义一致，参数名要一样，次序也一样，否则也会出错。

Answer 2

to liuyi8903(西西):怎么返回？能不能给个示例？

Answer 3

轉自OTN網，beckham發表
返回记录集过程:
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.

Answer 4

--创建可以返回纪录集的函数(不传入表名参数)
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.--创建可以返回纪录集的函数(可以传入表名参数)
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 王五
------------------------------------------------------------------------------------------------------------------

Answer 5

字段类型为嵌套表的使用方法:
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.