CODEWITHSUNDEEP
sqloracle-databaseplsql
selvackp
selvackp

Reputation: 1

Incrementing values in table using PL/SQL?

In my query I'm using a for loop, which displays 1000 three times. I have to increment 1000 for each iteration of the loop, i.e. 1001, 1002,.. same number three times i.e. I want to add into my table 1000,1000,1000,1001,1001,1001 and 1002,1002,1002,

declare
   CPName varchar(20) :=1000;
   a number; 
begin
   for a in 1 .. 3 loop
       insert into clients values (CPName,null,null);
   end loop;    
end;

How can I do this?

Upvotes: 0

Views: 3244

Answers (1)

Ben
Ben

Reputation: 52853

CPName is a VARCHAR; I assume you want this to be a number, in which case you just add it on.

There's no need to define the variable a either, it's implicitly declared by the LOOP. I would call this i as it's a more common name for an index variable.

declare
   CPName integer := 1000;
begin
   for i in 1 .. 3 loop
       insert into clients values (CPName + i, null, null);
   end loop;    
end;

You can do this all in a single SQL statement though; there's no need to use PL/SQL.

insert into clients
 select 1000 + i, null, null
   from dual
  cross join ( select level as i
                 from dual
              connect by level <= 3 )

Based on your comments you actually want something like this:

insert into clients
with multiply as (
 select level - 1 as i
   from dual
connect by level <= 3
        )
 select 1000 + m.i, null, null
   from dual
  cross join multiply m
  cross join multiply

This will only work if you want the same number of records as you want to increase so maybe you'd prefer to do it this way, which will give you a lot more flexibility:

insert into clients
with increments as (
 select level - 1 as i
   from dual
connect by level <= 5
        )
, iterations as (
 select level as j
   from dual
connect by level <= 3
        )
 select 1000 + m.i, null, null
   from dual
  cross join increments m
  cross join iterations

Using your LOOP methodology this would involve a second, interior loop:

declare
   CPName integer := 1000;
begin
   for i in 1 .. 3 loop
      for j in 1 .. 3 loop
         insert into clients values (CPName + i, null, null);
      end loop;
   end loop;    
end;

Upvotes: 3

Related Questions