Oracle函数迭代父机构

最新推荐文章于 2022-11-15 15:50:24 发布

原创最新推荐文章于 2022-11-15 15:50:24 发布 · 378 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #函数

oracle 同时被 2 个专栏收录

4 篇文章

订阅专栏

SQL

3 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中使用自定义函数自动填充机构层级序列的方法。该方法通过递归查询机构及其子机构的关系，实现了从顶层到底层的序列编号自动填充，解决了系统升级迁移过程中新引入字段为空的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景说明：
系统升级在机构表里面添加了一个序列号字段用于标识机构的层级关系
例如：
A机构: orgSeq = ‘00’
A的子机构B1: orgSeq = ‘00-01’
B的子机构C1: orgSeq = ‘00-01-01’
A的子机构B2: orgSeq = ‘00-02’
在将数据迁移到新库的时候由于旧版数据库不存在orgSeq所以在新库中orgSeq=null

在数据库中机构有一个parentOrgId标识自己的父机构
通过Oracle函数来执行orgSeq自动填充，顺便复习一下函数的使用

CREATE OR REPLACE
FUNCTION CREATE_ORG_SEQ RETURN VARCHAR2
IS                                                                                        
	CURSOR orgRowCursor IS SELECT * FROM ORG;                      -- 游标变量
	myOrg ORG%ROWTYPE;                                             --行变量
	orgParent ORG%ROWTYPE;         
	org ORG%ROWTYPE;
	orgSeq VARCHAR2(256);
	parentOrgId VARCHAR2(256);
	nextParentOrgId VARCHAR2(256);
	parentOrgCode VARCHAR2(256);
BEGIN
	DBMS_OUTPUT.ENABLE(buffer_size => null);
	OPEN orgRowCursor;
	LOOP
		FETCH orgRowCursor INTO org;                                        -- 获取每一行
		EXIT WHEN orgRowCursor%NOTFOUND;                                    -- 退出条件
		myOrg := org;                                                       -- 赋值
		orgSeq := myOrg.org_code;                                           -- 获取当前行的机构编码
		parentOrgId := myOrg.parent_org_id;                                 -- 获取当前行的机构的父机构Id
		LOOP                                                                -- 开始循环，向上迭代找父机构，直到父机构的id=‘0’ 或者等自己id
			IF parentOrgId = '0' THEN
				EXIT;
			END IF;
			
			IF parentOrgId = myOrg.org_id THEN
				orgSeq := myOrg.org_code || '-' || orgSeq;
				EXIT;
			END IF;
			
	
			IF parentOrgId <> '0' THEN                                        -- 父机构id不等于0
				SELECT * INTO myOrg FROM ORG WHERE ORG_ID = parentOrgId;      -- 根据父机构的id查找父机构
				orgSeq := myOrg.org_code || '-' || orgSeq;                    -- 拼接一下orgSeq
				parentOrgId := myOrg.parent_org_id;                           -- 把当前行赋值为父机构继续迭代
			
			END IF;
		END LOOP;
		DBMS_OUTPUT.PUT_LINE(orgSeq);
		UPDATE ORG SET ORG_SEQ = orgSeq WHERE org_id = org.org_id;
	END LOOP;
	CLOSE orgRowCursor;                                                       --关闭游标
	RETURN NULL;
END;