create or replace procedure Get_TreeDepth( @Node NUMBER(10) input, @Depth OUT number(10)) as begin --建立临时表 set @sql_exec = create global temporary table NodeCal (id number(10), levelno number(10), QTY number(10), ParentID number(10) )
exec (@sql_exec)
set @depth = select levelno from msot_nodetest where id = @Node
--初始化 set @sql_initial = insert into NodeCal values(select * from msot_nodetest where id = @node) exec (@sql_initial)
--开始逐层遍历 while (exists(select * from msot_nodetest where parentID in (select id from NodeCal))) loop set @sql_insert = insert into NodeCal values(select * from msot_nodetest where parentid in (select id from NodeCal)) exec (@sql_insert) @Depth = @Depth + 1 end loop DBMS_OUTPUT.PUT_LINE(@Depth-1)
CREATE OR REPLACE PROCEDURE Get_TreeDepth(Node NUMBER(10) input,
Depth OUT NUMBER(10)) AS
sql_exec VARCHAR2(2000);
BEGIN
--建立临时表
exec IMMEDIATE 'drop table NodeCal';
sql_exec = 'create global temporary table NodeCal
(id number(10),
levelno number(10),
QTY number(10),
ParentID number(10)
) on commit ';
exec IMMEDIATE sql_exec;
SELECT levelno INTO depth FROM msot_nodetest WHERE id = Node;
INSERT INTO NodeCal
SELECT * FROM msot_nodetest WHERE id = node;
--开始逐层遍历
FOR c IN (SELECT *
FROM msot_nodetest
WHERE parentID IN (SELECT id FROM NodeCal)) LOOP
INSERT INTO NodeCal
SELECT *
FROM msot_nodetest
WHERE parentid IN (SELECT id FROM NodeCal);
Depth = Depth + 1;
END LOOP DBMS_OUTPUT.PUT_LINE(Depth - 1);
END;
------解决方案--------------------
------解决方案-------------------- sql_exec := 'create global temporary table NodeCal (id number(10), levelno number(10), QTY number(10), ParentID number(10) ) on commit ';