How to pass data and loop in procedure Oracle

oracleoracle-11gplsqlstored-procedures

I have a procedure in an Oracle database which I use to insert users into a table. But now, I need to implement a loop, so that I use that same procedure to insert many users.

I have little knowledge of pl/sql so I don't know which is the best way to do this. Should I pass the data through a varray?

And if so, how can I implement a loop in my procedure?

CREATE OR REPLACE PROCEDURE insert_users(
     p_username     IN xx_users.username%TYPE,
     p_pass         IN xx_users.pass%TYPE,
     p_role         IN VARCHAR2
     )

IS

l_id_role    NUMBER;
l_role       VARCHAR2(200);

l_user       VARCHAR2(200);
l_pass       VARCHAR2(200);

BEGIN

l_user := p_user;
l_pass := p_pass;
l_role := p_role;

SELECT id_role
INTO l_id_role
FROM xx_roles
WHERE role_name = l_role;

INSERT INTO xx_users (id_user,
                        username,
                        pass,
                        id_role
                        ) VALUES (xx_users_id.nextval,
                                 l_user,
                                 l_pass,
                                 l_id_role
                                 );

COMMIT;
END;

I was thinking that maybe I could create another procedure which takes an array and for each element, calls the procedure insert_users but I don't think that is the most effective way to do it.

Best Answer

Suppose you have the xx_roles and xx_users tables, and (instead of an array) a third table new_users, which contains the new users' names and temporary ids, something like :

-- table for roles
create table xx_roles (
  id_role number primary key
, role_name varchar2(32) unique );

begin
  for i in 65 .. 90  loop -- ASCII A-Z
    insert into xx_roles (id_role, role_name) 
      values (i, 'role_' || chr(i)); 
  end loop;
end;
/
-- temporary table containing names of NEW users
-- (everything can be DELETEd once xx_users is populated)
create table new_users(
  tempid number
, username varchar2(32) );

begin
  for i in 97 .. 122 loop -- ASCII a-z
    insert into new_users values (i, 'user_' || chr(i));
  end loop;
end;
/
-- "final" users table: this table will be populated via the procedure
create table xx_users(
  id_user number primary key
, username varchar2(64)
, pass varchar2(128)
, role_name varchar2(32) references xx_roles(role_name)
);

create sequence xxuserid_seq start with 1000 increment by 1;

After executing all the code above, the xx_users table is empty:

SQL> select * from xx_users;
no rows selected

Now, you could use a procedure that looks similar to this, utilising an "cursor for loop" (CAUTION: this will need exception handling, eventually!):

create or replace procedure populate( 
  p_startid  new_users.tempid%type
, p_endid    new_users.tempid%type
, p_roleid   xx_roles.id_role%type
)
is
  role xx_roles.role_name%type ;
begin
  select role_name into role from xx_roles where id_role = p_roleid ; 
  for rec in (
    select tempid, username from new_users 
    where tempid between p_startid and p_endid )
  loop
    insert into xx_users values( 
        xxuserid_seq.nextval
      , rec.username
      , standard_hash( rec.tempid )
      , role );
  end loop;
end populate;
/

Testing:

-- populate the xx_users table, using the procedure 
-- new users' and temporary ids taken from the new_users table
-- eg temporary ids 100 - 110, id_role 65 -> role_A

begin
  populate(100, 110, 65); 
  populate(111, 115, 82);
  populate(120, 122, 90);
end;
/

SQL> select * from xx_users;
ID_USER  USERNAME  PASS                                      ROLE_NAME  
1039     user_d    4C6B9619DED0EA1E8A802D85DED8E6A751D27A65  role_A     
1040     user_e    15848F7CECB65A6B6C6DD43A7F135B23C3C0B5FC  role_A     
1041     user_f    2DA6C40C74BA92AA966BBB3791DD1147BD273305  role_A     
1042     user_g    ADD9483D8920D56BF313B60A2F6EB0CDBE0E8785  role_A     
1043     user_h    CA7EB2084F5046E3BFA61003174E5042A5B7F3CC  role_A     
1044     user_i    48087C2C84F074B09F6611658B6FF1E9535E669F  role_A     
1045     user_j    90ACB83C29B5EF24BE0598C59BF5863DD0E96A4B  role_A     
1046     user_k    F55EF9C7E3465999FA1FFA6563BF87F4F700F453  role_A     
1047     user_l    27E35BDEBFD7EB16D4BE7FD4E2D08D406A94BADD  role_A     
1048     user_m    36DEEF8699FDAD1BE9216E91438419D1D3679EEB  role_A     
1049     user_n    12EB0BC8F5C5D878120B59481136049D42407BC3  role_A     
1050     user_o    F6576DF67EB05A22F36A2841B027E9986D009E7C  role_R     
1051     user_p    C9D87CF97D4C3CD5315D487505E28B611123D351  role_R     
1052     user_q    600A35A1CC089A880A03296F2ED51700B31CCC93  role_R     
1053     user_r    345FA3C5E123079850898D43FD794E4BD5CFC53E  role_R     
1054     user_s    65B1606205328C61B7749C18956F7224264A6F5B  role_R     
1055     user_x    FB4DF69CC20EF71B716182B0F16A0FA9A6047E00  role_Z     
1056     user_y    9DB01E75E374EDAB7AEB5DE2B51571896BB4DB47  role_Z     
1057     user_z    58F5BA70289B1C028DB82C153D9DB94B97DAA892  role_Z

Update - As @miracle173 has pointed out, it may be a better idea to use a single INSERT in this case. Example:

insert into xx_users
select 
  xxuserid_seq.nextval
, U.username
, standard_hash( U.tempid )
, R.role_name
from xx_roles R, new_users U
where ( ( U.tempid between 100 and 110 ) and R.id_role = 65 )
   or ( ( U.tempid between 111 and 115 ) and R.id_role = 82 )
   or ( ( U.tempid between 120 and 122 ) and R.id_role = 90 ) ;
Related Question