1、没看出过程中嵌套有什么错,应该不是这个原因。编译失败的原因是什么?2、http://www.vbip.com/books/1861001789/chapter_1789_10.asp
http://www.vbip.com/books/1861001789/chapter_1789_11.asp
http://www.csdn.net/Expert/TopicView2.asp?id=338638&datebasetype=now
http://www.vbip.com/books/1861001789/chapter_1789_11.asp
http://www.csdn.net/Expert/TopicView2.asp?id=338638&datebasetype=now
输入被截为1个字符警告: 创建的过程带有编译错误
EXIT WITH c_tdylf%NOTFOUND;
-〉EXIT WHEN c_tdylf%NOTFOUND;
show errors;
就可以把错误打出来。
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结果并显示
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
dbms_output.put_line(w_name);
end;4、测试结果:
通过。请问高手:
我已经按上面的程序做了一遍,可以运行。
有2个问题:
1。测试后,未在屏幕是显示。是否在最后加
dbms_output.get_line();
2.我如何获取这个结果集,我用java 写程序,只知道有一个out输出参数。
问题:1,如果符合条件的记录有多条,为什么只显示1条,
2,返回的结果集怎么取?
谢!
只好变通实现.在包中申明一个全局游标,在过程中不断FETCH值,用PB循环取这个过程的返回值,只到遇到自己定义的结束标识.
...
String sql = "{ call get_myRS(?, ?) }"; //函数
stmt = connection.prepareCall(sql);
stmt.setInt(...); //加入参数
stmt.registerOutParameter(1,Types.REF); //注册返回参数
rs = stmt.execute(); //返回结果集大致是这样的,我没测试过,你自己试一下
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
ASPROCEDURE 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;
/