Oracle 递归 plsql,PLSQL 学习之路（3）递归查询返回属性结构

1.FUNCTION

FUNCTION query_all(v_org_id NUMBER) RETURN pl_json IS

v_orglist                pl_json := pl_json;

v_temp                   pl_json := pl_json;

v_total                  NUMBER := 0;

v_parentorganizationname VARCHAR2(100);

--获取org_id这个组织的基本信息并拼字符串

CURSOR v_qur_cur IS

SELECT *

FROM dfnd.dfnd_organizations do

WHERE do.organization_id = v_org_id;

--获取org_id这个组织下面的子组织数组

CURSOR v_qurall_cur IS

SELECT *

FROM dfnd.dfnd_organizations do

WHERE do.parent_organization_id = v_org_id

order by do.order_num;

BEGIN

FOR v_qur IN v_qur_cur LOOP

v_orglist.set_value(‘organizationId‘, v_qur.organization_id);

v_orglist.set_value(‘organizationCode‘, v_qur.organization_code);

v_orglist.set_value(‘organizationName‘, v_qur.organization_name);

v_orglist.set_value(‘parentOrganizationId‘,

v_qur.parent_organization_id);

v_orglist.set_value(‘organizationManager‘, ‘11‘);

IF v_qur.parent_organization_id IS NOT NULL THEN

SELECT do1.organization_name

INTO v_parentorganizationname

FROM dfnd.dfnd_organizations do1

WHERE do1.organization_id = v_qur.parent_organization_id;

v_orglist.set_value(‘parentOrganizationName‘,

v_parentorganizationname);

END IF;

END LOOP;

FOR v_qurall IN v_qurall_cur LOOP

v_total := v_total + 1;

IF (v_total >= 1) THEN

v_temp := query_all(v_qurall.organization_id);

v_orglist.add_list_item(‘childOrganizations‘, v_temp);

END IF;

END LOOP;

RETURN v_orglist;

END query_all;

2.PROCEDURE

PROCEDURE proc_query_all_organizations(p_request  CLOB,

x_response OUT CLOB) IS

v_api      VARCHAR2(100) := ‘proc_query_all_organizations‘;

v_response pl_json := pl_json;

v_temp     pl_json;

v_orglist  pl_json := pl_json;

v_org_id   NUMBER;

BEGIN

SELECT do.organization_id

INTO v_org_id

FROM dfnd.dfnd_organizations do

WHERE do.parent_organization_id IS NULL;

v_temp := query_all(v_org_id);

v_orglist.set_value(‘orgList‘, v_temp);

x_response := v_orglist.to_json;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

v_response.fail(‘接口‘ || v_api || ‘发生错误,错误原因:‘ || SQLERRM);

x_response := v_response.to_json;

END proc_query_all_organizations;

PLSQL 学习之路(3)递归查询返回属性结构

标签：char   where   proc   ota   varchar2   组织   order by   cep   for

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：http://www.cnblogs.com/Mars-Definesys/p/6744846.html