在存储过程中如何返回数据集?

[lsz74]

在存储过程中如何返回数据集?（好像只有用到游标才能解决，但是具体的实现方法怎么写呢？）有例子代码更好．谢谢！

[icesummit]

这个问题我已经在论坛看到不下10遍了：）

[penitent]

看FAQ
http://expert.csdn.net/Expert/FAQ/FAQ_Index.asp?id=7012

[岁月无声-我心飞翔]

DROP TABLE person;

CREATE TABLE person
(ssn NUMBER(9) PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(20));

INSERT INTO person VALUES(555662222,'Sam','Goodwin');

INSERT INTO person VALUES(555882222,'Kent','Clark');

INSERT INTO person VALUES(666223333,'Jane','Doe');

COMMIT;
/
Create the following package on your Oracle server:

CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER;

PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
Create the following package body on your Oracle server:

CREATE OR REPLACE PACKAGE BODY packperson
AS

PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person;

percount NUMBER DEFAULT 1;

BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;

PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn;

percount NUMBER DEFAULT 1;

BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;