oracle的存储过程示例

1、循环，与查询读取表值示例

create or replace procedure demo is

rid long;
res long;
begin
    --程序中先添加ROLE_TEAM_FKCJ 罚款催缴的角色
    select r.id into rid from fk_role r where r.role_code='ROLE_TEAM_FKCJ';
    --刷新用户默认数据
    for rec in (select u.id,u.fact_name,r.role_name from fk_user_role_rel ur left join fk_user_info u on(ur.user_id=u.id) left join fk_role r on(r.id=ur.role_id) where u.is_deleted=0 and r.is_deleted=0 and r.role_code='ROLE_TEAM')
    LOOP
       INSERT INTO fk_user_role_rel(user_id,role_id) VALUES(rec.id,rid);
    end LOOP;
    --删除罚款催缴与角色挂钩
    select rs.id into res from fk_resource rs where rs.resource_name='罚款催缴';
    delete fk_role_resource_rel a where a.resource_id = res;
    INSERT INTO fk_role_resource_rel(resource_id,role_id) VALUES(res,rid);
end demo;