需求
假设顶级节点的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 eva l_index_type
? where parent_id = oid
? order by sort_num;
?
BEGIN
? sub_num := 0;
?
? select id,type_code into p_id,p_code
? from eva l_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 eva l_index_type set type_code =p_code || '-' || lpad(to_char(cnt) ,tree_level,'0')
? ? where id = curRow.id;
?
? ? select COUNT(*) into sub_num fromeva l_index_type where parent_id = p_id;
?
? ? if sub_num > 0 then
? ? ? INI_TREE_CODE (curRow.id);
? ? end if;
? end loop;
ENDINI_TREE_CODE;
?