PL/SQL Cursor双嵌套的应用

核心提示： create or replace procedure P_ADD_PROMANAGE_SET AS/**物业管理员配置表数据插入，并添加物理员角色2012年10月23日14:51:40liyscreate table TMP_PROMANAGE_SET(SLEVEL_NAME VARCHAR2(50),SLEVEL

 create or replace procedure P_ADD_PROMANAGE_SET AS
/**
物业管理员配置表数据插入，并添加物理员角色
2012年10月23日14:51:40
liys
create table TMP_PROMANAGE_SET
(
SLEVEL_NAME VARCHAR2(50),
SLEVEL_CODE VARCHAR2(50),
LOGONID VARCHAR2(20)
)--创建表录入数据
注意：存储过程中调用其它角色的表，要dba显式授权
**/

type curtyp is ref cursor;
c1 curtyp; --取临时表值
c2 curtyp; --取用户表值
m_name varchar2(50);--管理员名称
m_code varchar2(32);--管理员编码
d_code varchar2(50);--部门编码
s_name varchar2(50);--层级名称
s_code varchar2(50);--层级编码
l_id varchar2(20):='没有管理员OA账号';--登陆用户名
ro_id varchar2(32);--物业管理员roleid
ro_ids varchar2(500);--roleids
amount number:=0;

sqlstr varchar2(500);
begin

sqlstr := 'select slevel_name,slevel_code,nvl(logonid,' || '''' || l_id || '''' || ') from lmis.tmp_promanage_set';--引号转义，要注意
open c1 for sqlstr;
loop
fetch c1 into s_name, s_code, l_id;
exit when c1%NOTFOUND;
if l_id = '没有管理员OA账号' then
insert into lmis.promanage_set(resourceid,slevel_name,slevel_code) values(SYS_GUID(),s_name,s_code);
else
select ur.roleid into ro_id from lmis_urms.umrole ur where ur.rolecode = 'wygly';--查出物业管理员roleid
sqlstr := 'select userid,username,orgid from lmis_urms.umuser where logonid =' || '''' || l_id ||'''' ;--引号转义，要注意
open c2 for sqlstr;
loop
fetch c2 into m_code,m_name,d_code;
exit when c2%NOTFOUND;

insert into lmis.promanage_set(resourceid,manger_name,manger_code,slevel_name,slevel_code,dept_code)
values(SYS_GUID(),m_name,m_code,s_name,s_code,d_code);--表数据插入

select roleids into ro_ids from
(select * from lmis_urms.umuser u left join lmis_urms.umuserrole ur on u.userid=ur.orgid)
where userid = m_code;--根据userid查询角色集合（如加逗号分开，就有两个角色：38855abf7ea7413cbde4dd6037b5d54c，38855abf7ea7413cbde4dd6037b5d54c）
--sqlstr := ro_ids || ',' || ro_id;
SELECT instr(ro_ids,ro_id) into amount FROM dual;--是否存在物业管理员角色
if amount>0 then
dbms_output.put_line('角色已存在');
else
update lmis_urms.umuserrole ur set ur.roleids = ro_ids || ',' || ro_id where ur.orgid = m_code;--更新角色
end if;
end loop;

close c2;
end if;
commit;
l_id := '没有管理员OA账号';
end loop;

close c1;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
ROLLBACK;
END;