ACS3 Data Model

     1: function hp_relative_neighborhood_name (neighborhood_node IN integer)
     2: return varchar2
     3: IS
     4: 	CURSOR name_cursor IS
     5: 		select neighborhood_name from users_neighborhoods
     6: 		where neighborhood_id=neighborhood_node;
     7: 	CURSOR parent_cursor IS
     8: 		select parent_id from users_neighborhoods
     9: 		where neighborhood_id=neighborhood_node;
    10: 	fullname	varchar(500);
    11: 	parentid	integer;
    12: 	root_node	integer;
    13: BEGIN
    14: 	OPEN parent_cursor;
    15: 	OPEN name_cursor;
    16: 	FETCH parent_cursor INTO parentid;
    17: 	FETCH name_cursor INTO fullname;
    18: 	CLOSE parent_cursor;
    19: 	CLOSE name_cursor;
    20: 
    21: 	select hp_get_neighborhood_root_node
    22: 	into root_node
    23: 	from dual;
    24: 
    25: 	IF neighborhood_node = root_node
    26: 	THEN
    27: 		return '';
    28: 	END IF;
    29: 
    30: 	IF parentid is null
    31: 	THEN
    32: 		return '';
    33: 	END IF;
    34: 
    35: 	IF parentid = root_node
    36: 	THEN
    37: 		return fullname;
    38: 	ELSE
    39: 		return CONCAT(hp_relative_neighborhood_name(parentid), CONCAT(' : ',fullname));
    40: 	END IF;
    41: END;