Oracle queries whether a person's department (326) is part of a department string (329|2) or in a sub-department of the Department string

Department 326 is the parent department of Department 329, department 2 and department 326 are peers;

Account Muyunfei, belonging to 329 departments

The message is sent to department 2 and department 3,262 departments (326|2), sending the department objects between the "|" Segmentation

Query: Whether the Muyunfei account belongs to the department string (329|2) or in a sub-department of the Department string

1. First use start with ..... Connect by Get Account Muyunfei Department and parent Department

2, traversing the department and the parent department, in the cycle of weight nesting cycle (the loop split to "|" Is the division number of the delimiter), in the nested loop body to determine whether the two results are the same, the same, that is, the person belongs to the current department or its child departments, return 1 end loop, otherwise continue to loop

Create or Replace function check_user_in_dept (to_party_id varchar2,--department ID, multiple | split   cur_user_id VARCHAR2--Person ID) return VARCHAR2 AS/* function: Determine whether the current person is in the current department  * 1 The current person belongs to the department, 0 does not belong to */PARTY_STR VARCHAR2 (200);  V_length Number: = Length (to_party_id);  V_start number: = 1;  V_index number;                                Cursor C_1 is select * from Wx_party a start with a.party_id = (select B.department From wx_contacts b where b.user_id = cur_user_id) Connect by a.party_id = Prior A.PA Rent_id;begin/* Retrieves cursor data */For v_1 in C_1 Loop/** based on ' | '       Splits the string and matches the data, if present then 1, otherwise resumes the loop */while (V_start <= v_length) loop V_index: = INSTR (to_party_id, ' | ', V_start); --Dbms_output.      Put_Line (' V_index: ' | | v_index);        IF v_index = 0 Then party_str: = SUBSTR (to_party_id, V_start);      V_start: = v_length + 1; ELSE party_str: = SUBSTR (to_party_id, V_start, V_index-v_start);      V_start: = V_index + 1;      END IF; --dbms_output.      Put_Line (' party_str: ' | | party_str); --dbms_output.      Put_Line (' party_id: ' | | v_1.party_id);      IF party_str = v_1.party_id then return ' 1 ';    End If;    END LOOP;    /* Reset Loop Condition */v_length: = Length (to_party_id);  V_start: = 1;  End Loop; Return ' 0 '; end;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle queries whether a person's department (326) is part of a department string (329|2) or in a sub-department of the Department string