…… cursor c is select id ,name from adm_organize where status =1; …… for v_org in c loop select level ,name,id into ???? from adm_organize start with id=v_org.id connect by prior parent_id = id; -- 这个查询会返回若干行记录,我应该怎么存放呢?end loop
怎么定义这个游标呢? 难道: …… cursor c is select id ,name from adm_organize where status =1;cursor c2 ;…… for v_org in c loop select level ,name,id into c2 from adm_organize -- into c2,这样不对吧 start with id=v_org.id connect by prior parent_id = id;end loop
create table table1 (idd number, vss varchar2(100)); insert into table1 values(1111,'abcd'); insert into table1 values(1112,'fffff'); insert into table1 values(1113,'uvw'); --commit; --select * From table1;
DECLARE TYPE t_tes IS TABLE OF table1%ROWTYPE INDEX BY BINARY_INTEGER; --定义变量 l_tes t_tes; BEGIN
--取数据 SELECT * BULK COLLECT INTO l_tes FROM table1;
FOR i IN 1 .. l_tes.COUNT LOOP dbms_output.put_line(l_tes(i).idd || ' ' || l_tes(i).vss); END LOOP; END;Output: 1111 abcd 1112 fffff 1113 uvw
view_adm adm_organize%rowtype; //定义一个adm_organize类型的变量open c loop //循环 fetch c into view_adm; //赋值 ....end loop; close c;
type rec_type is record(num number,name adm_organize.name%type,id organize.id%type); rec rec_type; cursor c is select id ,name from adm_organize where status =1; …… for v_org in c loop select level ,name,id into rec.num,rec.name,rec.id from adm_organize start with id=v_org.id connect by prior parent_id = id; -- 这个查询会返回若干行记录,我应该怎么存放呢? dbms_output.put_line(to_char(rec.num)||','||rec.name||','||rec.id);end loop ;
难道:
……
cursor c is
select id ,name from adm_organize where status =1;cursor c2 ;……
for v_org in c loop select level ,name,id into c2 from adm_organize -- into c2,这样不对吧
start with id=v_org.id
connect by prior parent_id = id;end loop
insert into table1 values(1111,'abcd');
insert into table1 values(1112,'fffff');
insert into table1 values(1113,'uvw');
--commit;
--select * From table1;
DECLARE
TYPE t_tes IS TABLE OF table1%ROWTYPE INDEX BY BINARY_INTEGER;
--定义变量
l_tes t_tes;
BEGIN
--取数据
SELECT * BULK COLLECT INTO l_tes FROM table1;
FOR i IN 1 .. l_tes.COUNT
LOOP
dbms_output.put_line(l_tes(i).idd || ' ' || l_tes(i).vss);
END LOOP;
END;Output:
1111 abcd
1112 fffff
1113 uvw
loop //循环
fetch c into view_adm; //赋值
....end loop;
close c;
type rec_type is record(num number,name adm_organize.name%type,id organize.id%type);
rec rec_type;
cursor c is
select id ,name from adm_organize where status =1;
……
for v_org in c loop select level ,name,id into rec.num,rec.name,rec.id from adm_organize
start with id=v_org.id
connect by prior parent_id = id;
-- 这个查询会返回若干行记录,我应该怎么存放呢?
dbms_output.put_line(to_char(rec.num)||','||rec.name||','||rec.id);end loop ;