Plsql Learning Road (3) Recursive query returns property structure

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);

--Get basic information org_id this organization and spell the string

CURSOR V_qur_cur is

SELECT *

From Dfnd.dfnd_organizations do

WHERE do.organization_id = v_org_id;

--Get org_id An array of sub-organizations under this organization

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 isn't 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 (+): = ' 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 (' interface ' | | | v_api | | ' An error occurred, cause of error: ' | | SQLERRM);

X_response: = V_response.to_json;

END proc_query_all_organizations;

Plsql Learning Road (3) Recursive query returns property structure