How to look or loop through Till the leaf Node in Oracle Or Sqlserver. -A to Z help Asp.Net,C#.Net,vb.Net

By using the below code you can loop through the leaf node easilty using the Oracle procedure which contains cursor.

Below is the Code for your Template : Please change the code acceording to your Business rule.

ANSWER :1

CREATE TABLE leaf_table (leaf NUMBER);

CREATE TABLE temp_table (child NUMBER);

CREATE OR REPLACE PROCEDURE get_leaves(

pid  IN NUMBER,

Rc1 OUT SYS_REFCURSOR

)

AS

var_count      NUMBER;

var_child      NUMBER;

var_childcount NUMBER;

BEGIN

                var_count := 1;

                var_child := pid;

                WHILE (var_count > 0)

                                SELECT count(1)

                                  INTO var_childcount

                                  FROM Regions

                                WHERE parent = var_child;

                                IF var_childcount = 0 THEN

                                INSERT INTO leaf_table VALUES (var_child);

                                ELSE

                                INSERT INTO temp_table (SELECT Child

                                                           FROM Regions

                                                          WHERE parent = var_child);

                                END IF;

                                DELETE FROM temp_table WHERE Child = var_child;

                                SELECT count(1)

                                  INTO var_count

                                  FROM temp_table;

                END WHILE;

               

                OPEN RC1 FOR SELECT * FROM leaf_table;

END;

Note : Provide your comments by clicking beloe options! Thanks ! :)

Tweet