[Oracle] 取得user的office level

功能:取得user的office level
情境:每家公司應該都會有office階層, 從總公司, 分公司, 分公司的分公司, ...一層層下去,
現在想要知道某位同事所屬的office(包含其上層到源頭總公司)階層

CREATE OR REPLACE FUNCTION SCHEMA_NAME.GET_USER_OFC_LEVEL(p_user_id VARCHAR2)
RETURN VARCHAR2
IS
/*
CREATED BY CHRIS ON 2015.09.14
*/
vUSERPath VARCHAR2(200);
vOFFICEID OFFICE.OFFICE_ID%TYPE;
vOFFICE OFFICE.OFFICE%TYPE;
vUPOFFICEID OFFICE.UP_OFFICE_ID%TYPE;
BEGIN
    SELECT B.OFFICE_ID,B.OFFICE,B.UP_OFFICE_ID INTO vOFFICEID,vOFFICE,vUPOFFICEID FROM USERS A, OFFICE B WHERE A.OFFICE_ID=B.OFFICE_ID AND A.USER_ID=p_user_id;
    
    vUSERPath := vOFFICE;
    
    WHILE vUPOFFICEID <> 0
    LOOP
        SELECT OFFICE_ID,AGENT,OFFICE,UP_OFFICE_ID INTO vOFFICEID,vAGENT,vOFFICE,vUPOFFICEID FROM OFFICE WHERE OFFICE_ID=vUPOFFICEID;
        // UP_OFFICE_ID就是OFFICE_ID的上一層
        
         vUSERPath := vOFFICE || ' ==> ' || vUSERPath;
    END LOOP;
    
    RETURN vUSERPath;
END;

取得user的office level

SELECT GET_USER_OFC_LEVEL('CHRISLIN') FROM DUAL;

結果可能類似下面
HQ ==> ERO ==> HAM