大家好,问题是这样的我想在其它存储过程中,访问包中的数组,但不知在包体中如何给数组赋值。如下例:一、包头的定义:
CREATE OR REPLACE PACKAGE PKG_TEAM_TYPES AS
TYPE REFCURSOR IS REF CURSOR;
TYPE TYPEUnit IS RECORD(
unitid userdb.pub_t_unitinfo.unitid%type,
unitname userdb.pub_t_unitinfo.unitname%type
);
TYPE TYPEUnitSet IS TABLE OF TYPEUnit INDEX BY BINARY_INTEGER;
END PKG_TEAM_TYPES;
二、包体的定义:
create or replace package body Pkg_Team_Types as procedure p_TYPEUnit(ATYPEUnitSet out PKG_TEAM_TYPES.TYPEUnitSet) is
rs SYS_REFCURSOR;
sunitid userdb.pub_t_unitinfo.unitid%type;
sunitname userdb.pub_t_unitinfo.unitname%type;
recunit PKG_TEAM_TYPES.TYPEUnit;
i number;
begin
open rs for select unitid,unitname from userdb.pub_t_unitinfo;
i := 1;
loop
fetch rs into sunitid,sunitname;
exit when rs%NOTFOUND;
recunit.unitid := sunitid;
recunit.unitname := sunitname; ATYPEUnitSet(i) := recunit;
i:=i + 1;
end loop;
end p_TYPEUnit;
end Pkg_Team_Types;三、其它存储过程调用包中TYPEUnitSet,但unitSet.COUNT中始终小于0,不执行循环,不知为什么数组中无???请指教,谢谢CREATE OR REPLACE PROCEDURE proc_aa
IS
iCounter INTEGER :=0;
fdUnitID VARCHAR2(2);
unitSet PKG_TEAM_TYPES.TYPEUNITSET;
BEGIN
iCounter := 0;
DBMS_OUTPUT.PUT_LINE(unitSet(1).unitid);
WHILE iCounter<unitSet.COUNT LOOP
fdUnitID := unitSet(iCounter).unitid;
LOOP;EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END proc_aa;
CREATE OR REPLACE PACKAGE PKG_TEAM_TYPES AS
TYPE REFCURSOR IS REF CURSOR;
TYPE TYPEUnit IS RECORD(
unitid userdb.pub_t_unitinfo.unitid%type,
unitname userdb.pub_t_unitinfo.unitname%type
);
TYPE TYPEUnitSet IS TABLE OF TYPEUnit INDEX BY BINARY_INTEGER;
END PKG_TEAM_TYPES;
二、包体的定义:
create or replace package body Pkg_Team_Types as procedure p_TYPEUnit(ATYPEUnitSet out PKG_TEAM_TYPES.TYPEUnitSet) is
rs SYS_REFCURSOR;
sunitid userdb.pub_t_unitinfo.unitid%type;
sunitname userdb.pub_t_unitinfo.unitname%type;
recunit PKG_TEAM_TYPES.TYPEUnit;
i number;
begin
open rs for select unitid,unitname from userdb.pub_t_unitinfo;
i := 1;
loop
fetch rs into sunitid,sunitname;
exit when rs%NOTFOUND;
recunit.unitid := sunitid;
recunit.unitname := sunitname; ATYPEUnitSet(i) := recunit;
i:=i + 1;
end loop;
end p_TYPEUnit;
end Pkg_Team_Types;三、其它存储过程调用包中TYPEUnitSet,但unitSet.COUNT中始终小于0,不执行循环,不知为什么数组中无???请指教,谢谢CREATE OR REPLACE PROCEDURE proc_aa
IS
iCounter INTEGER :=0;
fdUnitID VARCHAR2(2);
unitSet PKG_TEAM_TYPES.TYPEUNITSET;
BEGIN
iCounter := 0;
DBMS_OUTPUT.PUT_LINE(unitSet(1).unitid);
WHILE iCounter<unitSet.COUNT LOOP
fdUnitID := unitSet(iCounter).unitid;
LOOP;EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END proc_aa;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货