--我想写个存储过程实现树形目录,现已知道有三层,树形菜单存储过程如下:
CREATE OR REPLACE PROCEDURE Tree_menu
(wzidIn in varchar2)
IS
num number;
BEGIN
num:=0;
delete from tree_class;
commit;
declare cursor c1 is
select typeid,typename from v_class where wzid=wzidIn and bigtypeid='0';
c1R c1%rowtype;
begin
for c1R in c1 loop
num:=num+1;
insert into tree_class(typeid,typename,orderby) values(c1R.typeid,c1R.typename,num);
declare cursor c2 is
select typeid,' '||typename from v_class where wzid=wzidIn and bigtypeid=c1R.typeid;
c2R c2%rowtype;
begin
for c2R in c2 loop
num:=num+1;
insert into tree_class(typeid,typename,orderby) values(c2R.typeid,c2R.typename,num);
declare cursor c3 is
select typeid,' '||typename from v_class where wzid=wzidIn and bigtypeid=c2R.typeid;
c3R c3%rowtype;
begin
for c3R in c3 loop
num:=num+1;
insert into tree_class(typeid,typename,orderby) values(c3R.typeid,c3R.typename,num);
end loop;
end;
end loop;
end;
end loop;
commit;
end;
END Tree_menu;
/
---菜单表
create table v_class(typeid varchar2(20),typename varchar2(50),bigtypeid varchar2(20),wzid varchar2(20));
--临时表
create table tree_class(typeid varchar2(20 byte),typename varchar2(50 byte),orderby number);
CREATE OR REPLACE PROCEDURE Tree_menu
(wzidIn in varchar2)
IS
num number;
BEGIN
num:=0;
delete from tree_class;
commit;
declare cursor c1 is
select typeid,typename from v_class where wzid=wzidIn and bigtypeid='0';
c1R c1%rowtype;
begin
for c1R in c1 loop
num:=num+1;
insert into tree_class(typeid,typename,orderby) values(c1R.typeid,c1R.typename,num);
declare cursor c2 is
select typeid,' '||typename from v_class where wzid=wzidIn and bigtypeid=c1R.typeid;
c2R c2%rowtype;
begin
for c2R in c2 loop
num:=num+1;
insert into tree_class(typeid,typename,orderby) values(c2R.typeid,c2R.typename,num);
declare cursor c3 is
select typeid,' '||typename from v_class where wzid=wzidIn and bigtypeid=c2R.typeid;
c3R c3%rowtype;
begin
for c3R in c3 loop
num:=num+1;
insert into tree_class(typeid,typename,orderby) values(c3R.typeid,c3R.typename,num);
end loop;
end;
end loop;
end;
end loop;
commit;
end;
END Tree_menu;
/
---菜单表
create table v_class(typeid varchar2(20),typename varchar2(50),bigtypeid varchar2(20),wzid varchar2(20));
--临时表
create table tree_class(typeid varchar2(20 byte),typename varchar2(50 byte),orderby number);
存储过程执行不成功,不知道哪里写的有问题,迷糊中。。
虽然不知道你要干吗(真的没耐心看你想干嘛)
但你的存储过程的确有问题,比如:
declare cursor c3 is
select typeid,' '||typename from v_class where wzid=wzidIn and bigtypeid=c2R.typeid;
c3R c3%rowtype;
你前面定义了c3和c3R
后面用到了c3R.typename
但你游标里有typename这列吗?
' '||typename应该as typename吧?