PL/SQL - String concatenation algorithm

I'm working on a PL/SQL algorithm, with Oracle.

I currently have a procedure which have one single numeric parameter. My procedure have to create a string which contains as much '0' as the parameter value.

I am currently using a for loop to achieve this:

MY_STRING VARCHAR2(30);
FOR I IN 1..MY_PARAMETER 
LOOP
     MY_STRING := CONCAT(MY_STRING, '0');
END LOOP;

Is it possible to do it in a linear way ? I mean without a loop, or even with one single statement.

Any help would be appreciated !

Tha开发者_开发知识库nks.

You can use LPAD() to achieve this:

SELECT LPAD('0', my_parameter, '0')
FROM DUAL

Here is the link to the manual:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions082.htm#i1371196

Demonstration of accepted answer using various input values.

set serveroutput on size 1000000 format wrapped

Declare
   my_parameter Number(3);
   my_string    Varchar2(10);

Begin
   DBMS_Output.Put_Line('Input Output');
   DBMS_Output.Put_Line('===== ======');

   For vLoopVar IN 0..5 Loop     
      my_parameter := vLoopVar;

      If (vLoopVar = 5) Then
         my_parameter := '';
      End If;

      DBMS_Output.Put(RPAD('~' || my_parameter || '~',6));

      --Method 1
      my_string := lpad('0',my_parameter,'0');
      DBMS_Output.Put_Line('~' || my_string || '~');

   End Loop;
End;
/

Output

Input Output
===== ======
~0~   ~~
~1~   ~0~
~2~   ~00~
~3~   ~000~
~4~   ~0000~
~~    ~~