给对机构树表修改treecode时用到了递归 记录一下
mysql:
BEGIN
/* 定义变量 */
declare v_num int default 0;
declare v_c_org_id VARCHAR(32);
declare v_p_org_id VARCHAR(32);
declare done int default -1; -- 用于控制循环是否结束
/* 声明游标 */
declare v_child_org CURSOR FOR select org_id,org_parent_id from nicme_auth_org_tree where org_parent_id=IN_ORG_ID;
/* 当游标到达尾部时,mysql自动设置done=1 */
declare continue handler for not found set done=1;
SET @@max_sp_recursion_depth = 100;
/* 打开游标 */
open v_child_org;
/* 循环开始 */
myLoop: LOOP
/* 移动游标并赋值 */
fetch v_child_org into v_c_org_id,v_p_org_id;
-- 游标到达尾部,退出循环
if done = 1 then
leave myLoop;
end if;
/* do something */
-- 循环输出信息
select done;
select v_c_org_id,v_p_org_id,IN_ORG_ID ;
-- 可以加入insert,update等语句
SET v_num = v_num+1;
SELECT v_num;
CALL SP_ORG_TREE_CODE(v_c_org_id,CONCAT(IN_TREE_CODE,right(concat('0000',v_num),4)));
update nicme_auth_org_tree set tree_code=CONCAT(IN_TREE_CODE,right(concat('0000',v_num),4)) where org_id=v_c_org_id and org_parent_id=IN_ORG_ID;
/* 循环结束 */
end loop myLoop;
/* 关闭游标 */
close v_child_org;
END
oralce:
CREATE OR REPLACE PROCEDURE SP_ORG_TREE_CODE
(
IN_MAP_ID IN NUMBER,
IN_ORG_ID IN NUMBER,
IN_TREE_CODE IN VARCHAR2
) AS
cursor v_child_org is select org_id,org_parent_id,map_id from sip_auth_org_tree where map_id=IN_MAP_ID and org_parent_id=IN_ORG_ID;
v_num number := 0;
v_c_org_id number;
v_p_org_id number;
v_map_id number;
BEGIN
open v_child_org;
loop
fetch v_child_org into v_c_org_id,v_p_org_id,v_map_id;
exit when v_child_org%notfound;
v_num := v_num+1;
SP_ORG_TREE_CODE(v_map_id,v_c_org_id,IN_TREE_CODE||trim(to_char(v_num,'0000')));
update sip_auth_org_tree set tree_code=IN_TREE_CODE||trim(to_char(v_num,'0000')) where org_id=v_c_org_id and map_id=v_map_id and org_parent_id=IN_ORG_ID;
commit;
-- dbms_output.put_line(v_map_id||','||v_c_org_id||','||IN_TREE_CODE||trim(to_char(v_num,'0000')));
end loop;
update sip_auth_org_tree set tree_code=IN_TREE_CODE where org_id=IN_ORG_ID and map_id=IN_MAP_ID;
close v_child_org;
END SP_ORG_TREE_CODE;