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;