oracle存储过程个人学习总结

g3hk52t4ql

　　常用分支循环：

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;