利用oracle存储过程生成树代码
时间：2022-03-14 03:22
利用oracle存储过程生成树编码
需求
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;相关推荐
电脑软件热门排行今日推荐热门手游
|
