oracle 游标
CREATE OR REPLACE FUNCTION get_cop_person_group(p_person_id NUMBER)
RETURN NUMBER IS
l_group NUMBER;
l_dept_name VARCHAR2(240);
CURSOR cur_dept(t_group_name VARCHAR2) IS
SELECT wdv.dept_name
FROM web_departments_v wdv
START WITH wdv.dept_name = t_group_name
CONNECT BY PRIOR wdv.parent_dept_id = wdv.dept_id;
BEGIN
SELECT wev.dept
INTO l_dept_name
FROM web_employees_v wev
WHERE wev.person_id = p_person_id;
FOR rec_dept IN cur_dept(l_dept_name) LOOP
IF rec_dept.dept_name = '功率电子元件' THEN
l_group := 1;
EXIT;
ELSIF rec_dept.dept_name = '工业控制元件' THEN
l_group := 2;
EXIT;
ELSIF rec_dept.dept_name = '微波与通信元件' THEN
l_group := 3;
EXIT;
ELSIF rec_dept.dept_name IN ('元件事业二部') THEN
l_group := 4;
EXIT;
ELSIF rec_dept.dept_name = '汽车电子事业部' THEN
l_group := 5;
EXIT;
ELSIF rec_dept.dept_name = '在线商务部' THEN
l_group := 6;
EXIT;
END IF;
END LOOP;
RETURN l_group;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;