Using the oracle Stored Procedure generation tree encoding, the oracle Stored Procedure
Requirements for generating tree encoding using oracle stored procedures
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
N does not know how many levels the system has and needs to be called recursively
Call itself recursively;
N 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;
N Prefix: '0'
Lpad (to_char (cnt), tree_level, '0 ')
Stored Procedure Code
CREATEOR REPLACE PROCEDURE INI_TREE_CODE( V_PARENT_ID IN VARCHAR2 )AS 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;