存储过程 批量更新

取出用户表的用户ID

再往权限表中，增加用户ID对应的权限

'1341' 是角色ID，已由手工添加

 

create or replace procedure abc is
  m_busi_code NUMBER(16);

  cursor m_cur is
    SELECT user_id from tab_user;

begin
  open m_cur;
  loop
    fetch m_cur
      into m_busi_code;
    exit when m_cur%NOTFOUND;
 
    insert into tab_uvr
      (UVR_ID, USER_ID, ROLE_ID)
    values
      (SEQ_UVR.NEXTVAL, m_busi_code, '1341');
 
    commit;
  end loop;
  close m_cur;
exception
  when others then
    if m_cur%isopen then
      close m_cur;
    end if;
    rollback;
end abc;

 

 

SQL> EXEC abc;