利用Oracle存储过程生成树编码

主键,32位UUID

   

TYPE_CODE

 

编码

 

如:1-01-003

 

PARENT_ID

 

父节点ID,32位UUID

   

SORT_NUM

 

排序编号

 

正整

 

假设顶级节点的TYPE_CODE为字符1,写存储过程把表中所有的节点TYPE_CODE生成好;

二级节点前面补一个龄,三级补两个零,依次类推;

实现关键点

不知道系统有多少层级,需要递归调用

通过递归调用自身;

如何动态在TYPE_CODE前面填充‘0’;通过计算‘-’的个来确定层级,从而确定前缀的个数

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

前面填充前缀‘0’字符

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

存储过程代码

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);

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/7cc7763c33033c27515a82e412a0a719.html