create or replace package PTEST is
type testCursorType is ref cursor;
procedure getTestInfo(testCursor out testCursorType);
end;
create or replace package PTEST is
type testCursorType is ref cursor;
procedure getTestInfo(testCursor out testCursorType);
end;--二、创建包体--Sql代码
create or replace package body PTEST is
procedure getTestInfo(testCursor out testCursorType) is
begin
open testCursor for
select * from question;
end;
end;
type testCursorType is ref cursor;
procedure getTestInfo(testCursor out testCursorType);
end;
create or replace package PTEST is
type testCursorType is ref cursor;
procedure getTestInfo(testCursor out testCursorType);
end;--二、创建包体--Sql代码
create or replace package body PTEST is
procedure getTestInfo(testCursor out testCursorType) is
begin
open testCursor for
select * from question;
end;
end;
v_result SYS_REFCURSOR;
v_rec question%ROWTYPE;
BEGIN
ptest.gettestinfo(v_result);
LOOP
FETCH v_result
INTO v_rec;
EXIT WHEN v_result%NOTFOUND;
dbms_output.put_line(v_rec.字段1 || ' ' || v_rec.字段2 || ' ' || v_rec.字段3);
END LOOP;
END;
create table questionType(
qid int primary key,
typename varchar2(200) not null)
--这是表格create table question(
tid int primary key,
content varchar2(200) not null,
pid int not null,
qid int references questionType(qid)
)create or replace package PTEST is
type testCursorType is ref cursor;
procedure getTestInfo(testCursor out testCursorType);
end; --二、创建包体--Sql代码
create or replace package body PTEST is
procedure getTestInfo(testCursor out testCursorType) is
begin
open testCursor for
select * from question;
end;
end; --三、调用测试 -- 注意:不能使用for等隐式游标的调用方法来调用游标变量,因为它已经打开了--Sql代码
declare
mycur PTEST.testCursorType;
tid int;
content varchar2(200);
begin
ptest.getTestInfo(mycur);
loop
fetch mycur into tid, content;
exit when mycur%notfound;
dbms_output.put_line(tid || content);
end loop;
close mycur;
end;
这样测试的话就报结果集变量或查询的返回类型不匹配,这样是怎么回事啊?是我存储过程写错了吗?
select * from question;
fetch mycur into tid, content;
改为open testCursor for
select tid, content from question; 取了4个值,fetch只有俩变量。不够分。