利用oracle存储进程生成树编码
需求
|
字段
|
描写
|
备注
|
|
ID
|
主键，32位UUID
|
|
|
TYPE_CODE
|
编码
|
如：1-01-003
|
|
PARENT_ID
|
父节点ID，32位UUID
|
|
|
SORT_NUM
|
排序编号
|
正整数
|
假定顶级节点的TYPE_CODE为字符1，写存储进程把表中所有的节点TYPE_CODE生成好；
2级节点前面补1个龄，3级补两个零，顺次类推；
实现关键点
n 不知道系统有多少层级，需要递归调用
通过递归调用本身；
n 如何动态在TYPE_CODE前面填充‘0’；通过计算‘-’的个数来肯定层级，从而肯定前缀的个数
tree_level:= (length(p_code)-length(replace(p_code,'-',''))) + 1;
n 前面填充前缀‘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);
end if;
end loop;
ENDINI_TREE_CODE;