| Star printing in PL/SQL Program Problem [message #579895] |
Sat, 16 March 2013 21:38  |
 |
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
|
Member |
|
|
I want the output to be like
1
1 2
1 2 3
1 2 3 4
1 2 3
1 2
1
Please correct my mistake in this program:-
declare
v varchar2(50);
begin
for i in 1..10
loop
v:=v||' '||i;
display(v);
end loop;
v:=null;
for i in reverse 10..1
loop
v:=v||' '||i;
display(v);
end loop;
end;
Regards,
Ashish
|
|
|
|
|
|
|
|
|
|
| Re: Star printing in PL/SQL Program Problem [message #579907 is a reply to message #579895] |
Sun, 17 March 2013 06:02  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You misunderstand IN REVERSE. Regardless of IN or IN REVERSE lower_bound is first and upper bound is second. And in your second loop:
lower_bound > upper bound, therefore no loop iterations are executed. If you change bounds around:
SQL> declare
2 v varchar2(50);
3 begin
4 for i in 1..10
5 loop
6 v:=v||' '||i;
7 dbms_output.put_line(v);
8 end loop;
9 v:=null;
10 for i in reverse 1..10
11 loop
12 v:=v||' '||i;
13 dbms_output.put_line(v);
14 end loop;
15 end;
16 /
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9 10
10
10 9
10 9 8
10 9 8 7
10 9 8 7 6
10 9 8 7 6 5
10 9 8 7 6 5 4
10 9 8 7 6 5 4 3
10 9 8 7 6 5 4 3 2
10 9 8 7 6 5 4 3 2 1
PL/SQL procedure successfully completed.
SQL>
What you want is:
declare
v varchar2(50);
begin
for i in 1..10 loop
v := v || ' ' || i;
dbms_output.put_line(v);
end loop;
--v:=null;
for i in reverse 0..9 loop
v := substr(v,1,i * 2);
dbms_output.put_line(v);
end loop;
end;
/
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
| Re: Star printing in PL/SQL Program Problem [message #657970 is a reply to message #657968] |
Tue, 29 November 2016 10:34 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
In addition, this question which is 3 years old, was about PL/SQL not SQL, your "solution" does not produce the wanted output and is far too much complex for the question and it gives me an idea.
@all, who will find the SQL who displays the wanted result for numbers 1 to 9 with the less number of characters? 
I start with this one (166 characters):
SQL> select substr('1 2 3 4 5 6 7 8 9',1,2*level) from dual connect by level <= 9
2 union all
3 select substr('1 2 3 4 5 6 7 8 9',1,18-2*level) from dual connect by level <= 8
4 /
SUBSTR('123456789
-----------------
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1 |
|
|
|
|
|
|
|
| Re: Star printing in PL/SQL Program Problem [message #657973 is a reply to message #657970] |
Tue, 29 November 2016 11:08 |
|
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Found one with 114 characters:
SQL> select substr('1 2 3 4 5 6 7 8 9',1,decode(sign(level-10),-1,2*level,36-2*level))
2 from dual connect by level <= 17
3 /
SUBSTR('123456789
-----------------
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1[Updated on: Tue, 29 November 2016 11:09] Report message to a moderator |
|
|
|
| Re: Star printing in PL/SQL Program Problem [message #657981 is a reply to message #657973] |
Tue, 29 November 2016 14:01 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 29 November 2016 12:08
Found one with 114 characters:
SQL> select substr('1 2 3 4 5 6 7 8 9',1,least(2*level,36-2*level))
2 from dual connect by level<18
3 /
SUBSTR('123456789
-----------------
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1
17 rows selected.
SQL> select length(q'[select substr('1 2 3 4 5 6 7 8 9',1,least(2*level,36-2*level))
2 from dual connect by level<18]') len from dual
3 /
LEN
----------
92
SQL>
SY.
[Updated on: Tue, 29 November 2016 14:02] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
