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;
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;
解决方案 »
- 菜鸟问一个SQL语句问题
- oracle改ip后登录出错
- oracle可以装载,无法打开,没有任何报错,一直等待
- 关于oracle中scheme的问题?
- 急!如何将.dbf文件导入到oracle?
- 查询效率问题
- Statspack 如何知道一个stats$sqltext中的SQL是什么时候运行的?
- 8i以下如果执行类似select (select count(*) from test) from dual这样的复合查询?
- 高手帮忙,关于inner jion。
- 我也来问一个入门级的问题,希望高手不要笑掉大牙
- ODP.NET for oracle 参数化 修改数据
- ado 操作oracle 与 mssql server的不同
CLOSE tempcursor;
tabData := 'insert into BASEDB_USERS_TEMP_TB(personid) values(:1)';其他地方类似
会提示哪里出错的?明白?