oracle存储过程个人学习总结
常用分支循环：if(condition)then--TODOend if;while(condition) loop--TODOend loop;for i in 1..100 loop--TODOend 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)asc_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)ascursor c_c is select * from catalog where pid = v_pid;i binary_integer := 0;beginfor 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)asbeginopen cur_catalog for select * from catalog where pid = v_id;end;
页:
[1]