PL/SQL Program to Find the Greatest of Three Numbers

A common task for new programmers is to compare several values to find the largest or smallest. In Oracle PL/SQL, you can easily find the greatest of three numbers using conditional logic.

This simple guide will show you the basic building blocks and a complete program to solve this problem.

What You Need to Know

To write this program, you will use a few basic PL/SQL concepts:

1. Enabling Output: Before you can see any printed results, you must run this command once in your SQL tool (like SQL*Plus or SQL Developer):SET SERVEROUTPUT ON;
2. Anonymous Block: We will write our code in a DECLARE...BEGIN...END; block. This is the standard way to run a simple PL/SQL script.
3. Variables: We need to DECLARE variables to hold our three numbers (n1, n2, n3) and a fourth variable (max_num) to store the largest value we find.
4. Conditional Logic (IF statement): This is the core of the program. We will use an IF...ELSIF...THEN...ELSE block to compare the numbers.
5. Printing the Result: We use DBMS_OUTPUT.PUT_LINE() to print the final answer to the screen.

PL/SQL Program: Find Greatest of Three Numbers

This program finds the largest of three hardcoded numbers (n1, n2, n3) and prints the result.

PL/SQL Program

SET SERVEROUTPUT ON;

DECLARE
  -- Define our three numbers
  n1 NUMBER := 25;
  n2 NUMBER := 10;
  n3 NUMBER := 45;
  
  -- A variable to hold the largest number
  max_num NUMBER;

BEGIN
  -- Start the logic to find the greatest number
  
  -- Check if n1 is greater than or equal to both n2 and n3
  IF (n1 >= n2 AND n1 >= n3) THEN
    max_num := n1;
  
  -- If n1 isn't the greatest, check if n2 is
  ELSIF (n2 >= n1 AND n2 >= n3) THEN
    max_num := n2;
  
  -- If neither n1 nor n2 is the greatest, then n3 must be
  ELSE
    max_num := n3;
  END IF;
  
  -- Print the final result to the screen
  DBMS_OUTPUT.PUT_LINE('The greatest number is: ' || max_num);

END;
/

Result

The greatest number is: 45

Program Explanation

1. DECLARE section: We create four variables. n1, n2, and n3 are given default values of 25, 10, and 45. max_num is declared to store our final result.
2. BEGIN section: The program starts.
3. IF (n1 >= n2 AND n1 >= n3) THEN: The first check is to see if n1 (25) is the largest.
   • 25 >= 10 is TRUE.
   • 25 >= 45 is FALSE.
   • Since TRUE AND FALSE is FALSE, the code inside this IF block is skipped.
4. ELSIF (n2 >= n1 AND n2 >= n3) THEN: The first check failed, so it tries the next "else if" block. It checks if n2 (10) is the largest.
   • 10 >= 25 is FALSE.
   • The code inside this ELSIF block is skipped.
5. ELSE: Since all previous checks (IF and ELSIF) failed, the ELSE block automatically runs. This block assumes that if neither n1 nor n2 was the greatest, n3 must be.
6. max_num := n3;: The max_num variable is set to the value of n3 (45).
7. END IF;: The conditional logic block is closed.
8. DBMS_OUTPUT.PUT_LINE(...): The program prints the final string, concatenating the text with the value stored in max_num (45).