1

I want use this in a varchar2. Ex.:

declare
  num number := &Number;
  serie varchar2(200) := 'S = ';
begin
  for x in 1 .. num loop
    serie += x, ' + ';
  end loop; `
end;
/

In the end I want that the serie be like "S = 1 + 2 + 3 ..." How can i make that work?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Giulio
  • 13
  • 2

2 Answers2

1

That would be something like this:

SQL> set serveroutput on
SQL> declare
  2    num number := &Number;
  3    serie varchar2(200) := 'S = ';
  4  begin
  5    for x in 1 .. num loop
  6      serie := serie || to_char(x) || ' + ';
  7    end loop;
  8
  9    -- remove the trailing "+"
 10    serie := rtrim(serie, ' +');
 11    dbms_output.put_Line(serie);
 12  end;
 13  /
Enter value for number: 5
S = 1 + 2 + 3 + 4 + 5

PL/SQL procedure successfully completed.

SQL>

A few comments:

  • line 6: you have to concatenate (concatenation operator is a double pipe sign, ||) previous value of SERIE; otherwise, you'd have only the last number in it
  • line 10: remove the trailing "+" sign
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

What could be done in pure SQL, usually should be done in pure SQL:

declare
  num number := &Number;
  serie varchar2(200);
begin
  select 'S = ' || listagg(rownum, ', ') within group (order by rownum)
    into serie
    from dual
 connect by level <= num;

  dbms_output.put_line(serie);
end;
/

The result for num = 10:

S = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Note, listagg function was introduced in Oracle version 11.2.

Dmitriy
  • 5,525
  • 12
  • 25
  • 38
  • Thansk man, but i want only in pure pl/sql. But thank you so much for the answer – Giulio Sep 14 '18 at 19:41
  • @Giulio, Of course, my solution is in PL/SQL too. I mean, that the calculation is done by single SQL query, not by PL/SQL `for` loop. Usually, SQL queries work faster. – Dmitriy Sep 15 '18 at 15:29
  • Yes, but i can't work with SQL in this exampel, just pl/sql. But thanks – Giulio Sep 16 '18 at 21:13