PL/SQL Program to Print Patterns

Printing patterns like triangles and pyramids is a classic programming exercise for learning how to use loops. In Oracle, you can do this using PL/SQL (Procedural Language/SQL).

This simple guide will show you the basic building blocks and two common examples to get you started.

What You Need to Know

To print patterns in PL/SQL, you only need three basic concepts:

1. Enabling Output: Before you can see any output from your program, you must run this command once in your SQL tool (like SQL*Plus or SQL Developer):SET SERVEROUTPUT ON;
2. Printing a Line: The command to print a line of text is DBMS_OUTPUT.PUT_LINE().
   • DBMS_OUTPUT.PUT_LINE('Hello'); prints "Hello" and moves to a new line.
   • DBMS_OUTPUT.PUT('*'); prints just a * and moves to a new line.
3. Loops: To repeat an action, we use loops. The FOR loop is perfect for patterns because it runs a specific number of times.
4. Nested Loops: This is the key trick for all 2D patterns. You use one loop inside another:
   • The outer loop controls the rows (how many lines to print).
   • The inner loop controls the columns (what to print on each line).

PL/SQL Anonymous Blocks

You will write your code inside a DECLARE...BEGIN...END; block. This is called an "anonymous block" and is the standard way to run a simple PL/SQL script.

SET SERVEROUTPUT ON;

DECLARE
  -- (Declare variables here)
BEGIN
  -- (Your loop logic goes here)
END;
/

Example 1: Printing a Right-Angle Triangle (Half Pyramid) Pattern using PL/SQL

Let's print a simple triangle of stars (*) that is 5 rows high.

Pattern to Print:

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

PL/SQL Program

SET SERVEROUTPUT ON;

DECLARE
  v_rows NUMBER := 5;
  v_line VARCHAR2(100); -- A variable to build the string
BEGIN
  -- 1. Outer loop for the ROWS (from 1 to 5)
  FOR i IN 1..v_rows LOOP
    
    v_line := ''; -- Clear the line string for each new row
    
    -- 2. Inner loop for the COLUMNS (prints '*' i times)
    FOR j IN 1..i LOOP
      v_line := v_line || '*'; -- Add one '*' to the line
    END LOOP;
    
    -- 3. Print the completed line
    DBMS_OUTPUT.PUT_LINE(v_line);
    
  END LOOP;
END;
/

Explanation

1. v_rows := 5;: We set a variable to control the height.
2. FOR i IN 1..v_rows LOOP: The outer loop runs 5 times (for i = 1, 2, 3, 4, 5). Each i represents one row.
3. v_line := '';: We reset our v_line variable to be empty at the start of each new row.
4. FOR j IN 1..i LOOP: The inner loop's limit is i.
   • On row 1 (i=1), it runs 1 time.
   • On row 2 (i=2), it runs 2 times.
   • On row 5 (i=5), it runs 5 times.
5. v_line := v_line || '*';: This adds one * to our line string for each time the inner loop runs.
6. DBMS_OUTPUT.PUT_LINE(v_line);: After the inner loop is finished building the line, the outer loop prints it.

Example 2: Printing a Full Pyramid Pattern in PL/SQL

This is a bit more complex because it involves leading spaces.

Pattern to Print:

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

PL/SQL Program

For this, we need three loops: one for the rows, one for the leading spaces, and one for the stars.

SET SERVEROUTPUT ON;

DECLARE
  v_rows NUMBER := 5;
  v_line VARCHAR2(100);
BEGIN
  -- 1. Outer loop for the ROWS (from 1 to 5)
  FOR i IN 1..v_rows LOOP
  
    v_line := ''; -- Clear the line string
    
    -- 2. Inner loop for the leading SPACES
    -- (rows - i) = (5-1)=4, (5-2)=3, (5-3)=2, ...
    FOR s IN 1..(v_rows - i) LOOP
      v_line := v_line || ' '; -- Add a space
    END LOOP;
    
    -- 3. Inner loop for the STARS
    -- (2 * i) - 1 = (2*1)-1=1, (2*2)-1=3, (2*3)-1=5, ...
    FOR j IN 1..((2 * i) - 1) LOOP
      v_line := v_line || '*'; -- Add a star
    END LOOP;
    
    -- 4. Print the completed line
    DBMS_OUTPUT.PUT_LINE(v_line);
    
  END LOOP;
END;
/

Explanation

1. FOR s IN 1..(v_rows - i) LOOP: This first inner loop prints the spaces. On row 1, it prints 4 spaces. On row 2, it prints 3 spaces, and so on.
2. FOR j IN 1..((2 * i) - 1) LOOP: This second inner loop prints the stars. It uses the formula (2 * i) - 1 to get the odd-numbered sequence 1, 3, 5, 7, 9.
3. DBMS_OUTPUT.PUT_LINE(v_line);: After both inner loops are finished (building the spaces and then the stars), the final line is printed.