oracle存储过程编译错误请问是哪里错了
create or replace package mypackage as
type Leader_cursor is ref cursor;
end mypackage;
create or replace procedure Leader_proc_list(personId in Varchar2,p_cursor out mypackage.Leader_cursor) IS
HeadShipName VARCHAR2(40);
PostId VARCHAR2(40);
tabCstr VARCHAR(100);
tempcursor CURSOR;
chiledCursor CURSOR;
deptId VARCHAR2(40);
PId VARCHAR2(40);
parentDepId VARCHAR2(40);
tabData varchar2(500);
BEGIN
tabCstr:= 'create global temporary table BASEDB_USERS_TEMP_TB(
personid varchar2(40),
)ON COMMIT PRESERVE ROWS '; --ON COMMIT PRESERVE ROWS 会话级临时表 ，ON COMMIT delete ROWS 事务级临时表
execute immediate tabCstr;
OPEN tempcursor FOR SELECT Headship_Name,post_Id,dept_Id FROM v_com_person_dept_post WHERE person_id=personId;
LOOP
fetch tempcursor into HeadShipName,PostId,deptId;
exit when tempcursor%notfound;
IF HeadShipName='领导职务' THEN
SELECT Person_id,PARENT_ID into PId,parentDepId FROM V_COM_PERSON_DEPT WHERE Dept_id=dept_Id AND rownum =1 AND Headship_name='领导职务'
ORDER BY Sort_Number;
IF PId<>personId THEN --不是最小序号领导
tabData := 'insert into BASEDB_USERS_TEMP_TB(personid) values('+parentDepId+')';
execute immediate tabData using timePoint;
commit;
ELSE
OPEN chiledCursor FOR
SELECT PERSON_ID FROM t_Com_Person_Info WHERE PERSON_ID IN(SELECT personID FROM v_com_person_dept WHERE dept_id=:parentDepId AND headship_name='领导职务');
LOOP
FETCH PERSON_ID INTO Pid;
exit when chiledCursor%notfound;
tabData := 'insert into BASEDB_USERS_TEMP_TB(personid) values('+Pid+')';
execute immediate tabData using timePoint;
commit;
END LOOP;
CLOSE chiledCursor;
END IF;
ELSE
OPEN chiledCursor FOR
SELECT PERSON_ID FROM t_Com_Person_Info WHERE PERSON_ID IN(SELECT personID FROM v_com_person_dept WHERE dept_id=dept_Id AND headship_name='领导职务');
LOOP
FETCH PERSON_ID INTO Pid;
exit when chiledCursor%notfound;
tabData := 'insert into BASEDB_USERS_TEMP_TB(personid) values('+Pid+')';
execute immediate tabData using timePoint;
commit;
END LOOP;
CLOSE chiledCursor;
END IF;
END LOOP；
CLOSE tempcursor；
open p_cursor for
SELECT * FROM t_Com_Person_Info WHERE person_id IN(SELECT personid FROM BASEDB_USERS_TEMP_TB);
tabCstr:= 'truncate table BASEDB_USERS_TEMP_TB ';
execute immediate tabDstr;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end Leader_proc_list;
[解决办法]
