Sunday, January 05, 2014

Looping the hard way

The task was to construct partition names from 'P001' to (for some reason) 'P336', as part of a larger maintenance script. Here's what they came up with:

declare
   p varchar2(4);
   i number := 1;
begin
   loop
      if i < 10 then
         p := 'P00' || to_char(i);
      elsif i < 100 then
         p := 'P0' || to_char(i);
      else
         p := 'P' || to_char(i);
      end if;
      
      i := i + 1;
      
      exit when i > 336;

      dbms_output.put_line(p);
   end loop;
end;

1 comment:

stewstools said...

You expect DBAs to be conversant in using LPAD?