先建立一个包,在包中建立一个记录集类型,再在该包中建立一个存储过程,用该记录集类型定义存储过程参数. 创建包 CREATE OR REPLACE PACKAGE pack_test AS TYPE mycur IS REF CURSOR; END pack_test;创建存储 CREATE OR REPLACE PROCEDURE p_test ( p_rc OUT pack_test.mycur ) ISBEGIN OPEN p_rc FOR SELECT * FROM c_well;END p_test;
SQL>create or replace type mytabletype as table of number; / 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 /
declare type a_type is table of number; -- type a_type is array(10) of number; -- 下面一种定义方式则指定了该数组的最大元素个数 a a_type := a_type(); -- 定义并初始化一个数组变量 begin a.extend(3); -- 数组扩展到3个元素 a(1) := 1; --赋值 a(2) := 10; a(3) := 100; end;
创建包
CREATE OR REPLACE PACKAGE pack_test
AS
TYPE mycur IS REF CURSOR;
END pack_test;创建存储
CREATE OR REPLACE PROCEDURE p_test
(
p_rc OUT pack_test.mycur
) ISBEGIN
OPEN p_rc FOR
SELECT *
FROM c_well;END p_test;
/
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 /
7 l_data.extend;
8 l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name);
这2句啊。
type a_type is table of number;
-- type a_type is array(10) of number;
-- 下面一种定义方式则指定了该数组的最大元素个数
a a_type := a_type(); -- 定义并初始化一个数组变量
begin
a.extend(3); -- 数组扩展到3个元素
a(1) := 1; --赋值
a(2) := 10;
a(3) := 100;
end;
8 l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name); --赋值