存储过程循环语句出现的怪事?

microchu 2003-06-24 10:15:54
Create OR REPLACE Package p_test
as
type NE_ID IS TABLE OF number(4) INDEX BY BINARY_INTEGER;
type DXC_NAME IS TABLE OF varchar(20) INDEX BY BINARY_INTEGER;
PROCEDURE pc_Stu(empID OUT NE_ID,empName OUT DXC_NAME);
END p_test;
/
CREATE OR REPLACE Package body p_test
as
PROCEDURE pc_Stu(empID OUT NE_ID,empName OUT DXC_NAME)
is
CURSOR c1 IS SELECT ID,NAME FROM NE_INFO WHERE Role =1 ORDER BY ID;
cnt NUMBER DEFAULT 1;
c c1%ROWTYPE;
Begin
OPEN c1;
LOOP
FETCH c1 INTO c;
empID(cnt) := c.ID;
empName(cnt) := c.NAME;
EXIT WHEN c1%NOTFOUND;
cnt := cnt + 1;
END LOOP;
CLOSE c1;
END pc_Stu;
END p_test;
/
上面的循环语句改用While...Loop:
WHILE c1%FOUND LOOP
FETCH c1 INTO cc1;
empID(cnt) := cc1.ID;
empName(cnt) := cc1.NAME;
cnt := cnt + 1;
END LOOP;

则没有任何结果返回?这是怎么回事?
...全文
8 点赞 收藏 2
写回复
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
beckhambobo 2003-06-24
循环前,首先fetch一个值,游标才会检查到。
如:
Begin
OPEN c1;
FETCH c1 INTO cc1;
WHILE c1%FOUND LOOP

empID(cnt) := cc1.ID;
empName(cnt) := cc1.NAME;
cnt := cnt + 1;
FETCH c1 INTO cc1;
END LOOP;
CLOSE c1;
END pc_Stu;
回复
bobfang 2003-06-24
open后没有fetch,则c1%FOUND为false。
回复
发动态
发帖子
基础和管理
创建于2007-09-28

1.7w+

社区成员

Oracle 基础和管理
申请成为版主
社区公告
暂无公告