23. According to the company to find all the departments below, according to the department to find the corresponding company

/**

* Subsidiaries exist under the company, subsidiaries exist under the Department, there are sub-departments under the department

* Under the existing department of the company, there are sub-departments under the department

* Find all departments under the company: SELECT * from Lborganization where fun_findcomorgid (id) = Company ID;

* According to department corresponding company:Select FUN_FINDCOMORGID (department ID) from dual;

*/

Create or Replace function fun_findcomorgid (--Query the ID of the company or group that owns the
P_orgid int--successor Organization ID
)
return int is

V_flag int:=0;--The ID of the company or group in which it belongs
V_orgtype int;--organization type variable, 2 for company, 3 for department
V_orgid int;--Store input organization ID, or isolated

Begin
V_orgid:=p_orgid;

While V_flag=0 loop
Select Orgtype into V_orgtype from lborganization where Id=v_orgid;
If v_orgtype<=2 Then
V_flag:=v_orgid;--v_flag=-owned company ID
Else
v_flag:=0;
End If;
Select FID to V_orgid from Lborganization where Id=v_orgid;

End Loop;

return (V_flag);

End
/

23. According to the company to find all the departments below, according to the department to find the corresponding company