Oracle sql to print generic pattern-CodePudding

I need to write generic query to print the below pattern. How can this be achieved?

 *****
 ****
 ***
 **
 *

CodePudding user response：

You can use:

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

to generate 5 rows:

LEVEL

1

2

3

4

5

LEVEL
1
2
3
4
5

You can use RPAD('*', 4, '*') to pad a string with * characters to a length of 4 characters ****.

You can then combine the two and reverse the ordering (either with an ORDER BY clause or using mathematics).

db<>fiddle here

Since this appears to be a homework question, I'll leave actually putting all the component parts together into a single query to you.

CodePudding user response：

Probably looking for something like this. Note that the query includes two bind variables: ch for the character used (in your example, asterisk) and n for the starting length (in your example, 5).

In SQL*Plus, which I used for this illustrations, you declare the variables with the variable command, and you assign values to them in an anonymous PL/SQL block. In other interfaces (SQL Developer, Toad, etc.), the specific way to assign values to bind variables may be different.

The output is a single row, consisting of a single string; that string is text in multiple lines.

SQL> variable ch char(1)
SQL> variable n  number
SQL> exec :ch := '*'; :n := 5

PL/SQL procedure successfully completed.


SQL> select  listagg(rpad(:ch, :n   1 - level, :ch), chr(10))
  2             within group (order by level) as pattern
  3  from    dual
  4  connect by level <= :n;



PATTERN
----------
*****
****
***
**
*