PL/SQL Cursor双嵌套的应用

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;