游标在存储过程中的简单使用

游标的格式：cursor 游标名称 (名称 类型, 名称 类型, ...) is
select ...;
下面通过一个简单例子来说明它的使用：
[size=large]1、首先,先建个表：[/size]
create table PM_L_LEVEL
(
LEVEL_ID INTEGER not null,
LEVEL_NAME VARCHAR2(500)
)
[size=large]2、插入数据：[/size]
insert into PM_L_LEVEL (LEVEL_ID, LEVEL_NAME)
values (0, '0流程');
insert into PM_L_LEVEL (LEVEL_ID, LEVEL_NAME)
values (1, '1流程');
insert into PM_L_LEVEL (LEVEL_ID, LEVEL_NAME)
values (2, '2流程');
insert into PM_L_LEVEL (LEVEL_ID, LEVEL_NAME)
values (3, '3流程');
insert into PM_L_LEVEL (LEVEL_ID, LEVEL_NAME)
values (4, '4流程');
commit;
[size=large]3、创建存储过程：[/size]
create or replace procedure Test_P(levleId in number,l_name out varchar2,tt out varchar2)
as
lnamed varchar2(30);
--num varchar2(30);
cursor test_cur is select p.level_name from pm_l_level p where p.level_id >levleId;
BEGIN
---使用for in 输出游标
for lnamed in test_cur loop
if test_cur%found then
l_name:=lnamed.level_name;
tt:=lnamed.level_name||','||tt;
end if;
end loop;
--使用loop fetch into 输出游标
/*open test_cur;
loop
fetch test_cur into lnamed;
exit when test_cur%notfound;
if test_cur%found then
l_name:=lnamed;
tt:=l_name;
dbms_output.put_line('读取的数据为'||l_name);
end if;
end loop;
dbms_output.put_line(tt);
close test_cur;*/

-- if(lnamed!=null) then
-- l_name:=lnamed;
--else
-- l_name:=lnamed;
--end if;
-- dbms_output.put_line(l_name);
-- dbms_output.put_line(msg);
END ;
这里对游标的输出使用了两种简单的循环输出。