Oracle queries whether the Department (326) of a person belongs to the Department string (329 | 2) or the sub-department of the Department string, 326329

Oracle queries whether the Department (326) of a person belongs to the Department string (329 | 2) or the sub-department of the Department string, 326329

 

Department 326 is the parent department of Department 329, and Department 2 and department 326 are departments at the same level;

Account muyunfei, belonging to department 329

The message is sent to Department 2 and department 326 (326 | 2). The objects of the sent departments are separated by "| ".

 

 

Query: whether the muyunfei account belongs to the Department string (329 | 2) or the sub-department of the Department string

1. First Use start ..... Connect by obtains the Department and parent department of the account muyunfei.

2. traverse the Department and parent department, and Judge in the nested loop body of the cyclic weight (this cycle is split with the department number separator "|, whether the two results are the same, the same, that is, the person belongs to the current department or its sub-departments, and 1 is returned to end the cycle. Otherwise, the cycle continues.

Create or replace function check_User_in_dept (to_party_id varchar2, -- department id, multiple use | split cur_user_id varchar2 -- personnel id) return varchar2 as/* function: determine whether the current person belongs to the current Department * 1 the current person belongs to this 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. party_id = (select B. department from wx_contacts B where B. user_id = cur_user_id) connect by. party_id = prior. parent_id; begin/* retrieve cursor data */for v_1 in c_1 loop/** split String Based on '|' and match data. If so, otherwise, 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 cycle condition */v_length: = LENGTH (to_party_id); v_start: = 1; end loop; return '0'; end;

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.