1

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.

Improve this question
2
  • you are right. you have to use some appropriate data structure and use some bulk operation docs.oracle.com/database/122/LNPLS/… . or even better: you can use an sql statement to do the insert if the data to insert can be retrieved by a select. Commented Jun 3, 2017 at 17:44
  • your current insert_users procedure has a flaw, it contains a commit statement. so you should not call it from another procedure because it commits the current transaction. Transaction should be committed by top level procedures only otherwise things become rather confusing. Commented Jun 3, 2017 at 17:48

1 Answer 1

Reset to default
0

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 ) ;
Improve this answer
3
  • to loop through a select and call insert for each row is really bad. This can be done with one insert statement Commented Jun 3, 2017 at 17:54
  • @miracle173: I know. However, part of the question was about that - (quote): how can I implement a loop in my procedure? I think that the person who asked the question is in the process of learning various aspects of PL/SQL - otherwise they wouldn't have asked how to use parameters etc. Commented Jun 3, 2017 at 17:56
  • Thank you for your explanation. Very clear. I'm still learning, so both the loop and the single insert come very useful. Commented Jun 3, 2017 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.