Wednesday, March 11, 2009

Oracle procedure to generate records as per binary tree.

We are aware about binary tree structure which looks like



So each node in this binary tree has its respective Childs (2).
We can generate these relations among nodes as database records.
For example:
Node 7 has Node 14 and Node 15 has child nodes, so
The table should have



Step1)
I have created a table saying “tree”.

CREATE TABLE TREE (
PRNT NUMBER,
CHLD NUMBER);
Where PRNT column holds the parent node number and CHLD holds child node number.
Step 2)
I have created an oracle procedure which expects “argMaxLevel” as input number.
This is to generate the nodes of a binary tree to certain level, that is





Oracle procedure
CREATE OR REPLACE PROCEDURE Createchlds(argMaxLevel IN NUMBER)
AS
BEGIN
DELETE FROM TREE;
FOR i IN 2 .. POWER(2,(argMaxLevel))-1
LOOP
INSERT INTO TREE VALUES(FLOOR(i/2),i);
END LOOP;
COMMIT;
END;
/


Step 3)
I said
Exec Createchlds(20), which should then generate nodes to level 20
And number of nodes generated are 108575 (Power(2,20))-1.

Output:-



You can observe there are 1 record missing
1) That record is node 1 which is not inserted as there is no associated parent record to point.




The last child record should be (power(2,20)-1) which is 1048575 and it is perfect from above screen shot.
The record with maximum child node is 1048575

No comments: