if(condition)then
--TODO
end if;
while(condition) loop
--TODO
end loop;
for i in 1..100 loop
--TODO
end loop;
系统定义的游标类型:sys_refcursor
测试使用的建表语句:
create table CATALOG
(
ID NUMBER not null primary key,
NAME VARCHAR2(50),
PID NUMBER,
ISPARENT NUMBER(1),
CLEVEL NUMBER(2)
)
测试存储过程:(为了使测试更直观,忽略一切异常和错误处理!)
插入分类的存储过程:
create or replace procedure insertCatalog
(
v_name in varchar2,
v_pid in number
)
as
c_level number;
begin
if(v_pid = 0) then
insert into catalog(id,name,pid,isparent,clevel) values(c_seq.nextval,v_name,v_pid,0,1);
else
select clevel into c_level from catalog where id = v_pid;
c_level := c_level+1;
update catalog set isParent = 1 where id = v_pid;
insert into catalog(id,name,pid,isparent,clevel) values(c_seq.nextval,v_name,v_pid,0,c_level);
end if;
commit;
end;
遍历分类的存储过程:
create or replace procedure showCatalog(v_pid number)
as
cursor c_c is select * from catalog where pid = v_pid;
i binary_integer := 0;
begin
for c in c_c loop
while( i < c.clevel ) loop
dbms_output.put('--');
i:=i+1;
end loop;
i:=0;
dbms_output.put_line(c.name);
if(c.isParent = 1)then
showCatalog(c.id);
end if;
end loop;
end;
获取分类的存储过程:
create or replace procedure getcatalog(v_id in number,cur_catalog out sys_refcursor)
as
begin
open cur_catalog for select * from catalog where pid = v_id;
end;