oracle VARRAY与for .. loop

-------只要有往来单位维护(base_customer_update),就必须有这个角色(base_customer_redit);
DECLARE
   on_org_id          VARCHAR2 (100);
   on_role_id         VARCHAR2 (100);
   on_role_id_group   VARCHAR2 (100);
   on_role_count      INT;
   on_app_id          VARCHAR2 (100);
   on_count           INT;
   on_count_group     INT;
   --定义一个数组
   TYPE ROLE_VARRAY IS VARRAY(2) OF VARCHAR2(50);
   --给数组赋值
   ROLE_VAR ROLE_VARRAY := ROLE_VARRAY('base_customer_redit');
BEGIN
    FOR I IN 1 .. ROLE_VAR.COUNT LOOP   
    BEGIN
           --获取modelhome2应用的app_id
           SELECT app_id INTO on_app_id FROM membership.mem_application WHERE app_code = 'asp_modelhome2';

           DECLARE
              CURSOR user_list_cur IS
                 --得到当前拥有base_customer_update的角色的用户信息
                 SELECT u.user_name, u.user_id, u.org_id FROM membership.mem_user u
                  WHERE EXISTS (
                      SELECT 1 FROM membership.mem_user_role_rela rela, membership.mem_role r
                      WHERE r.role_id = rela.role_id AND rela.user_id = u.user_id AND u.org_id = r.org_id
                      AND r.org_id = rela.org_id AND r.role_code IN ('base_customer_update')
                  );
           BEGIN
              FOR one_user IN user_list_cur
              LOOP
                 --判断该公司是否拥有此角色base_customer_redit
                 SELECT COUNT (*) INTO on_role_count FROM membership.mem_role
                  WHERE role_code = ROLE_VAR(I) AND org_id = one_user.org_id and app_id=on_app_id;
         
                  --若公司不拥有此角色
                 IF(on_role_count<=0)
                 THEN
                     --得到当前role_id
                     select approle_id INTO on_role_id from membership.mem_approle where app_id=on_app_id and approle_code='base_customer_redit';
                    
                    INSERT INTO membership.mem_role(role_id,org_id,app_id,role_code,role_name,role_type,parent_role_code,role_description)
                        VALUES (on_role_id,one_user.org_id,on_app_id,ROLE_VAR(I),'往来单位信用等级','application','','');
                     commit;

                    --判断用户角色中是否存在
                    SELECT COUNT (*) INTO on_count FROM membership.mem_user_role_rela
                     WHERE user_id = one_user.user_id AND role_id = on_role_id and app_id=on_app_id;

                    --若关系中不存在，则插入
                    IF (on_count<=0)
                    THEN
                       INSERT INTO membership.mem_user_role_rela (role_id, user_id, org_id,app_id) VALUES (on_role_id, one_user.user_id, one_user.org_id,on_app_id);
                    END IF;
                 END IF;
              END LOOP;
           END;

       DECLARE
          CURSOR group_list_cur IS
             --得到当前拥有base_customer_update的角色的用户组信息
             SELECT DISTINCT rela.GROUP_ID, rela.org_id, rela.app_id FROM membership.mem_usergroup_role_rela rela, membership.mem_role r
                       WHERE rela.role_id = r.role_id AND rela.org_id = r.org_id AND r.role_code IN ('base_customer_update');
       BEGIN
          FOR one_group IN group_list_cur
          LOOP
             --获取当前用户公司的是否已拥有base_customer_redit角色
             SELECT COUNT (*) INTO on_role_count FROM membership.mem_role
              WHERE role_code = ROLE_VAR(I) AND org_id = one_group.org_id and app_id=on_app_id;
             --如果有
             IF (on_role_count > 0)
             THEN
                --得到当前role_id
                SELECT role_id INTO on_role_id_group FROM membership.mem_role
                 WHERE role_code = ROLE_VAR(I) AND org_id = one_group.org_id and app_id=on_app_id;

                --判断用户组中是否存在
                SELECT COUNT (*) INTO on_count_group FROM membership.mem_usergroup_role_rela
                 WHERE org_id = one_group.org_id AND GROUP_ID = one_group.GROUP_ID AND role_id = on_role_id_group and app_id=on_app_id;

                --若关系中存在，则不插入
                IF (on_count_group<=0)
                THEN
                   --DBMS_OUTPUT.put_line(one_group.group_id);
                   INSERT INTO membership.mem_usergroup_role_rela (GROUP_ID, role_id,org_id, app_id)
                        VALUES (one_group.GROUP_ID, on_role_id_group,one_group.org_id,on_app_id);
                END IF;
             END IF;
          END LOOP;
       END;
  
   END;
   END LOOP;
END;
/
commit;