Generate tree encoding using Oracle stored procedures

Generate tree encoding using Oracle stored procedures

Requirement

Field	Description	Remarks
ID	Primary Key, 32-bit UUID
TYPE_CODE	Encoding	Example: 1-01-003
PARENT_ID	Parent node ID, 32-bit UUID
SORT_NUM	Sort number	Positive Integer

Assume that the TYPE_CODE of the top-level node is character 1. The write Stored Procedure generates the TYPE_CODE of all nodes in the table;

One age is added before the second-level node, two zeros are added at the third-level node, and so on;

Key Points

Do not know how many levels the system has and need to be called recursively

Call itself recursively;

How to dynamically fill '0' in front of TYPE_CODE; Determine the level by calculating the number of '-' to determine the number of prefixes

Tree_level: = (length (p_code)-length (replace (p_code, '-', '') + 1;

Prefix '0'

Lpad (to_char (cnt), tree_level, '0 ')

Stored Procedure Code

Createor replace procedure INI_TREE_CODE

(

V_PARENT_ID IN VARCHAR2

)

P_id varchar2 (32 );

P_code varchar2 (256 );

 

Sub_num number (4, 0 );

Tree_level number (4, 0 );

Cnt number (4, 0) default 0;

 

Cursor treeCur (oid varchar2) is

Select id, TYPE_CODE from eval_index_type

Where parent_id = oid

Order by sort_num;

BEGIN

Sub_num: = 0;

 

Select id, type_code into p_id, p_code

From eval_index_type

Where id = V_PARENT_ID

Order by sort_num;

 

For curRow in treeCur (p_id) loop

Cnt: = cnt + 1;

Tree_level: = (length (p_code)-length (replace (p_code, '-', '') + 1;

Update eval_index_type set type_code = p_code | '-' | lpad (to_char (cnt), tree_level, '0 ')

Where id = curRow. id;

Select COUNT (*) into sub_num fromeval_index_type where parent_id = p_id;

 

If sub_num> 0 then

INI_TREE_CODE (curRow. id );

End if;

End loop;

ENDINI_TREE_CODE;