PL/SQL PRACTICAL

How to Calculate the Area of a Rectangle Using PL/SQL

Introduction

In this lesson, we’ll learn how to write a simple PL/SQL program to calculate the area of a rectangle using its length and width. We will also explore a pattern of numbers: 1, 5, 9, 13, and so on.

PL/SQL Code for Calculating Rectangle Area

Let’s start by writing a PL/SQL program to find the area of a rectangle. We’ll use the formula Area = Length x Width.

DECLARE
    Length NUMBER := 5;   -- You can change the values as needed
    Width NUMBER := 9;
    Area NUMBER;
BEGIN
    Area := Length * Width;
    DBMS_OUTPUT.PUT_LINE('The area of the rectangle is: ' || Area);
END;
/

  • We declare two variables, Length and Width, and set their values to 5 and 9, respectively. You can change these values as per your requirements.
  • The Area variable will store the result of the calculation.
  • Inside the BEGIN block, we calculate the area by multiplying Length and Width, and then display the result using DBMS_OUTPUT.PUT_LINE.

Pattern: 1, 5, 9, 13, …

This pattern represents an arithmetic sequence with a common difference of 4. To find the nth term in this sequence, you can use the formula nth term = first term + (n - 1) x common difference.

PL/SQL Code for Finding the nth Term

Now, let’s write a PL/SQL program to find the nth term in the pattern 1, 5, 9, 13, ....

DECLARE
    n NUMBER := 7;   -- Change the value of 'n' as needed
    FirstTerm NUMBER := 1;
    CommonDifference NUMBER := 4;
    NthTerm NUMBER;
BEGIN
    NthTerm := FirstTerm + (n - 1) * CommonDifference;
    DBMS_OUTPUT.PUT_LINE('The ' || n || 'th term in the pattern is: ' || NthTerm);
END;
/
  • In this code, we declare the n variable to represent the term we want to find. You can change the value of n to find different terms.
  • FirstTerm represents the first term in the sequence (which is 1), and CommonDifference is the difference between consecutive terms (which is 4).
  • We calculate the NthTerm using the formula mentioned above and display the result using DBMS_OUTPUT.PUT_LINE.

Summary in Points

AspectRectangle Area CalculationPattern: 1, 5, 9, 13, …
PL/SQL CodeSee above exampleSee above example
VariablesLength, Widthn, FirstTerm, CommonDiff
FormulaArea = Length x Widthnth term = FirstTerm + (n – 1) x CommonDiff
Output DisplayedThe area of the rectangle is: [Area]The [n]th term in the pattern is: [NthTerm]
Customize ValuesAdjust Length and Width values as neededChange ‘n’ to find different terms
Calculation ExplanationMultiply Length and Width to find the areaUse the arithmetic sequence formula to find the nth term
ExecutionRun the codeRun the code

Now you have PL/SQL code for calculating the area of a rectangle and finding terms in the given pattern, along with a summary in a table format for easy reference. Feel free to modify the values and experiment with different inputs.

Writing a PL/SQL Program to Find the Largest Number among Three

Introduction

In this lesson, we will learn how to write a simple PL/SQL program that accepts three numbers and then prints the largest number among them. PL/SQL is a programming language used for managing data in Oracle databases.

Program Steps

Here are the steps we’ll follow to create our PL/SQL program:

  1. Declare Variables: We’ll declare three variables to store the three numbers and one more variable to store the largest number.
  2. Accept Input: We’ll use the ACCEPT statement to take input for the three numbers from the user.
  3. Compare Numbers: We’ll use conditional statements (IF-THEN-ELSE) to compare the numbers and determine which one is the largest.
  4. Display Result: Finally, we’ll use the DBMS_OUTPUT.PUT_LINE statement to display the largest number.

PL/SQL Code

DECLARE
   num1 NUMBER;
   num2 NUMBER;
   num3 NUMBER;
   largest NUMBER;

BEGIN
   -- Accept input for three numbers
   DBMS_OUTPUT.PUT_LINE('Enter the first number:');
   ACCEPT num1 NUMBER FORMAT '99999' DEFAULT 0;
   
   DBMS_OUTPUT.PUT_LINE('Enter the second number:');
   ACCEPT num2 NUMBER FORMAT '99999' DEFAULT 0;
   
   DBMS_OUTPUT.PUT_LINE('Enter the third number:');
   ACCEPT num3 NUMBER FORMAT '99999' DEFAULT 0;
   
   -- Compare and find the largest number
   IF num1 >= num2 AND num1 >= num3 THEN
      largest := num1;
   ELSIF num2 >= num1 AND num2 >= num3 THEN
      largest := num2;
   ELSE
      largest := num3;
   END IF;

   -- Display the result
   DBMS_OUTPUT.PUT_LINE('The largest number is: ' || largest);
END;
/

Example

Let’s run the program with some sample inputs:

  • First number: 25
  • Second number: 12
  • Third number: 38

The program will display:

csharpCopy code

The largest number is: 38

Summary

Here’s a summary of the PL/SQL program we created:

StepDescription
Declare VariablesWe declared four variables: num1, num2, num3, and largest.
Accept InputWe used the ACCEPT statement to take input for the three numbers.
Compare NumbersConditional statements were used to find the largest number.
Display ResultWe displayed the largest number using DBMS_OUTPUT.PUT_LINE.

By following these steps, we successfully created a PL/SQL program to find the largest number among three input numbers.

Writing a PL/SQL Program to Generate Even Numbers up to 100

In this lesson, we’ll learn how to create a simple PL/SQL program to generate even numbers from 2,4,6,8,10,12,……. to 100. PL/SQL is a programming language used with Oracle databases.

Step 1: Understanding the Problem

We want to create a list of even numbers from 2 to 100.

Step 2: Writing the PL/SQL Program

sqlCopy code

DECLARE
  num NUMBER := 2; -- Start with 2
BEGIN
  -- Using a loop to generate even numbers
  WHILE num <= 100 LOOP
    -- Output the current even number
    DBMS_OUTPUT.PUT_LINE(num);
    -- Move to the next even number (increment by 2)
    num := num + 2;
  END LOOP;
END;
/

Explanation

  • We declare a variable num and initialize it to 2 (the first even number).
  • We use a WHILE loop to iterate until num is less than or equal to 100.
  • Inside the loop, we print the current value of num.
  • We increment num by 2 in each iteration to move to the next even number.

Step 3: Running the Program

You can run this program in Oracle SQL Developer or any other Oracle database environment.

Summary

Here’s a summary of the PL/SQL program to generate even numbers up to 100:

StepDescription
Understanding ProblemWe want to generate even numbers from 2 to 100.
Writing the ProgramWe use a DECLARE block with a WHILE loop to generate and print even numbers, incrementing by 2 each time.
Running the ProgramExecute the program in an Oracle database environment.

This program generates the even numbers from 2 to 100 using PL/SQL.

How to Write a PL/SQL Program to Calculate the Area of a Triangle

In this lesson, we will learn how to write a PL/SQL program to calculate the area of a triangle. We will break down the process into simple steps and provide you with an example at the end.

Step-by-Step Instructions:

1. Define Variables

  • Create variables to store the values of the base and height of the triangle.
  • You can name these variables something like base_length and height.

2. Input Data

  • Prompt the user to enter the values of the base and height using the DBMS_OUTPUT.PUT_LINE statement.

3. Calculate the Area

  • Use the formula: Area = (base_length * height) / 2 to calculate the area of the triangle.
  • Store the result in a variable, for example, area.

4. Display the Result

  • Use the DBMS_OUTPUT.PUT_LINE statement to display the calculated area.

5. End the Program

  • Use the DBMS_OUTPUT.PUT_LINE statement to indicate the end of the program.

Example:

Here is a PL/SQL program that calculates the area of a triangle:

sqlCopy code

DECLARE base_length NUMBER; height NUMBER; area NUMBER; BEGIN -- Input data DBMS_OUTPUT.PUT_LINE('Enter the base length of the triangle:'); base_length := &base_length; -- User input DBMS_OUTPUT.PUT_LINE('Enter the height of the triangle:'); height := &height; -- User input -- Calculate the area area := (base_length * height) / 2; -- Display the result DBMS_OUTPUT.PUT_LINE('The area of the triangle is: ' || area); -- End the program DBMS_OUTPUT.PUT_LINE('Program complete.'); END; /

Summary:

StepDescription
1. Define VariablesCreate variables for base_length, height, and area.
2. Input DataPrompt the user to enter the base and height values.
3. Calculate the AreaUse the formula to calculate the area and store it in area.
4. Display the ResultShow the calculated area to the user.
5. End the ProgramIndicate the end of the program.

This program will calculate and display the area of a triangle based on user input for the base length and height.

DECLARE

   base_length NUMBER;
   height NUMBER;
   area NUMBER;
BEGIN

   -- Input data
   DBMS_OUTPUT.PUT_LINE('Enter the base length of the triangle:');
   base_length := &base_length; -- User input
   DBMS_OUTPUT.PUT_LINE('Enter the height of the triangle:');
   height := &height; -- User input

   -- Calculate the area
   area := (base_length * height) / 2;

   -- Display the result
   DBMS_OUTPUT.PUT_LINE('The area of the triangle is: ' || area);

   -- End the program
   DBMS_OUTPUT.PUT_LINE('Program complete.');
END;
/

Writing a PL/SQL Program to Print Odd Numbers from 1 to 25

Introduction

In this lesson, we will learn how to write a simple PL/SQL program to print a series of odd numbers starting from 1 and ending at 25.

Program Code

Here’s the PL/SQL code for printing the odd numbers from 1 to 25:

DECLARE

   num NUMBER := 1; -- Initialize the starting number
   
BEGIN

   -- Start a loop to print odd numbers
   WHILE num <= 25 LOOP
      DBMS_OUTPUT.PUT_LINE(num); -- Print the current number
      num := num + 2; -- Move to the next odd number
   END LOOP;
END;
/

Explanation

  • We declare a variable num and set it to 1. This variable will keep track of the current number we are printing.
  • Inside the BEGIN block, we use a WHILE loop to repeatedly execute the code within the loop as long as num is less than or equal to 25.
  • Inside the loop, we use the DBMS_OUTPUT.PUT_LINE statement to print the current value of num.
  • After printing the current number, we increment num by 2 to move to the next odd number.

Output

When you run this program, it will print the odd numbers from 1 to 25 in the following format:Copy code

1 3 5 7 9 11 13 15 17 19 21 23 25

Summary

Here’s a summary of the PL/SQL program to print odd numbers from 1 to 25:

StepDescription
1. Declare a variableDeclare a variable num and set it to 1.
2. Start a loopUse a WHILE loop to repeat the following steps while num is less than or equal to 25.
3. Print the numberInside the loop, use DBMS_OUTPUT.PUT_LINE to print the current value of num.
4. Move to next oddIncrement num by 2 to move to the next odd number.
5. RepeatContinue the loop until num exceeds 25.
6. End programEnd the program.

This program efficiently prints the odd numbers from 1 to 25 using PL/SQL.

Writing a PL/SQL Program to Find the Average of Five Numbers

Introduction:

In this lesson, we will learn how to write a simple PL/SQL program to read five numbers from the user and then calculate their average.

Steps to Follow:

  1. Declare Variables: We need to declare variables to store the five numbers and the average.
  2. Input Numbers: We will use & to prompt the user for input five times and store each number in its respective variable.
  3. Calculate Average: To find the average, we will add all five numbers and then divide the sum by 5.
  4. Display the Result: We will use DBMS_OUTPUT.PUT_LINE to display the calculated average.

PL/SQL Code:

DECLARE

   num1 NUMBER;
   num2 NUMBER;
   num3 NUMBER;
   num4 NUMBER;
   num5 NUMBER;
   avg  NUMBER;
BEGIN

   -- Input Numbers
   num1 := &Enter_Number1;
   num2 := &Enter_Number2;
   num3 := &Enter_Number3;
   num4 := &Enter_Number4;
   num5 := &Enter_Number5;

   -- Calculate Average
   avg := (num1 + num2 + num3 + num4 + num5) / 5;

   -- Display the Result
   DBMS_OUTPUT.PUT_LINE('The average of the five numbers is: ' || avg);
END;
/

Example:

Suppose we input the following numbers:

  • Enter_Number1 = 10
  • Enter_Number2 = 20
  • Enter_Number3 = 30
  • Enter_Number4 = 40
  • Enter_Number5 = 50

The program will calculate the average as (10 + 20 + 30 + 40 + 50) / 5 = 30 and display:

The average of the five numbers is: 30

Summary:

  • Declare variables to store numbers and the average.
  • Input five numbers from the user.
  • Calculate the average by adding them and dividing by 5.
  • Display the result using DBMS_OUTPUT.PUT_LINE.
StepDescription
1Declare variables
2Input five numbers
3Calculate the average
4Display the average result

PL/SQL Program to Display a Series 100, 98, 96, …, 4, 2

Introduction

In this PL/SQL program, we will create a loop to display a series of numbers starting from 100 and decreasing by 2 until we reach 2. We will use a simple loop construct to achieve this.

PL/SQL Code

DECLARE

   number_value NUMBER := 100; -- Initialize the starting number

BEGIN

   -- Start a loop to display the series
   WHILE number_value >= 2 LOOP
      -- Display the current number
      DBMS_OUTPUT.PUT_LINE(number_value);
      
      -- Decrease the number by 2 for the next iteration
      number_value := number_value - 2;
   END LOOP;
END;
/

Explanation

  • We declare a variable number_value and initialize it with the value 100. This variable will hold the current number in the series.
  • We use a BEGIN block to enclose our PL/SQL code.
  • Inside the block, we have a WHILE loop. This loop will continue as long as the number_value is greater than or equal to 2.
  • Inside the loop, we use DBMS_OUTPUT.PUT_LINE to display the current value of number_value.
  • We then decrement number_value by 2 to prepare for the next iteration.
  • The loop continues until number_value is less than 2, at which point the program ends.

Example Output

Here’s what the program will display:

Output
100
98
96
…
4
2

Summary

  • We created a PL/SQL program to display a series of numbers starting from 100 and decreasing by 2 until 2.
  • We used a WHILE loop to iterate through the numbers and DBMS_OUTPUT.PUT_LINE to display them.
  • The program ends when the number becomes less than 2.

How to Write a PL/SQL Program to Find the Smallest Number among Three

Introduction

In this lesson, we will learn how to create a simple PL/SQL program that takes three numbers as input and then prints the smallest number among them.

Steps to Write the Program

Follow these steps to create your PL/SQL program:

DECLARE
  -- Declare variables to store the three numbers
  num1 NUMBER;
  num2 NUMBER;
  num3 NUMBER;
  
  -- Declare a variable to store the smallest number
  smallest NUMBER;
BEGIN
  -- Prompt the user to enter three numbers
  DBMS_OUTPUT.PUT_LINE('Enter the first number:');
  num1 := &1; -- Input for the first number

  DBMS_OUTPUT.PUT_LINE('Enter the second number:');
  num2 := &2; -- Input for the second number

  DBMS_OUTPUT.PUT_LINE('Enter the third number:');
  num3 := &3; -- Input for the third number

  -- Find the smallest number
  IF num1 <= num2 AND num1 <= num3 THEN
    smallest := num1;
  ELSIF num2 <= num1 AND num2 <= num3 THEN
    smallest := num2;
  ELSE
    smallest := num3;
  END IF;

  -- Display the smallest number
  DBMS_OUTPUT.PUT_LINE('The smallest number is: ' || smallest);
END;
/

1. Open a PL/SQL Environment

  • Open your PL/SQL environment, such as SQL*Plus or SQL Developer.

2. Create a New PL/SQL Block

  • Begin by creating a new PL/SQL block using the DECLARE, BEGIN, and END; keywords.

DECLARE -- Declare variables to store the three numbers num1 NUMBER; num2 NUMBER; num3 NUMBER; -- Declare a variable to store the smallest number smallest NUMBER; BEGIN -- Your code will go here END;

3. Accept Three Numbers as Input

  • Use the DBMS_OUTPUT.PUT_LINE function to prompt the user for input and store the numbers in the declared variables.

-- Prompt the user to enter three numbers DBMS_OUTPUT.PUT_LINE('Enter the first number:'); num1 := &1; -- Input for the first number DBMS_OUTPUT.PUT_LINE('Enter the second number:'); num2 := &2; -- Input for the second number DBMS_OUTPUT.PUT_LINE('Enter the third number:'); num3 := &3; -- Input for the third number

4. Find the Smallest Number

  • Use IF statements to compare the numbers and find the smallest one.

-- Find the smallest number IF num1 <= num2 AND num1 <= num3 THEN smallest := num1; ELSIF num2 <= num1 AND num2 <= num3 THEN smallest := num2; ELSE smallest := num3; END IF;

5. Display the Smallest Number

  • Use DBMS_OUTPUT.PUT_LINE to display the smallest number to the user.

-- Display the smallest number DBMS_OUTPUT.PUT_LINE('The smallest number is: ' || smallest);

6. Run the Program

  • Execute the PL/SQL block to run the program. Provide three numbers when prompted.

Example

Suppose you enter the following numbers:

  • First number: 12
  • Second number: 5
  • Third number: 9

The program will output:

The smallest number is: 5

Summary

Here’s a summary of the steps to create a PL/SQL program to find the smallest number among three:

StepDescription
1Open a PL/SQL environment.
2Create a new PL/SQL block and declare variables.
3Accept three numbers as input from the user.
4Use IF statements to find the smallest number.
5Display the smallest number to the user.
6Run the program and enter three numbers for testing.

Now you know how to write a simple PL/SQL program to find the smallest number among three input numbers

Checking if a Number is Odd or Even Using PL/SQL

Introduction

In this lesson, we will learn how to create a simple PL/SQL program to determine whether a given number is odd or even. We will break down the steps into easy-to-follow points.

Prerequisites

Before we begin, make sure you have access to an Oracle Database where you can run PL/SQL code.

-- Declare a variable to store the user's input
DECLARE
  v_number NUMBER;
  v_result VARCHAR2(20);
BEGIN
  -- Prompt the user for input
  DBMS_OUTPUT.PUT_LINE('Enter a number: ');
  -- Read the user's input
  v_number := &1; -- Use &1 to accept input from the user
  
  -- Check if the number is odd or even
  IF MOD(v_number, 2) = 0 THEN
    v_result := 'Even';
  ELSE
    v_result := 'Odd';
  END IF;
  
  -- Display the result
  DBMS_OUTPUT.PUT_LINE('The number ' || v_number || ' is ' || v_result);
END;
/

Here’s how the program works:

  1. We declare a variable v_number to store the user’s input and v_result to store the result (odd or even).
  2. We use the DBMS_OUTPUT.PUT_LINE procedure to prompt the user to enter a number.
  3. We read the user’s input using the &1 syntax. This allows the user to enter a number when running the program.
  4. We use the MOD function to check if the input number is divisible by 2. If the remainder is 0, the number is even; otherwise, it’s odd.
  5. Depending on the result of the IF condition, we set the v_result variable to either ‘Even’ or ‘Odd’.
  6. Finally, we use DBMS_OUTPUT.PUT_LINE again to display the result, indicating whether the input number is odd or even.

To run this program, you can use an Oracle SQL*Plus environment or any other Oracle database tool that supports PL/SQL. Simply paste the code into the environment and provide a number when prompted, and the program will tell you if it’s odd or even.

PL/SQL program to print the series from 1 to 50.

DECLARE
    -- Declare a variable to store the current number
    current_number NUMBER := 1;

BEGIN
    -- Start a loop that will iterate from 1 to 50
    FOR current_number IN 1..50 LOOP
        -- Print the current number
        DBMS_OUTPUT.PUT_LINE(current_number);
    END LOOP;
END;
/

Here’s an explanation of the code:

  1. We declare a variable current_number to keep track of the current number in the series, initialized to 1.
  2. We use a BEGIN...END block to define the main part of our program.
  3. Inside the block, we use a FOR loop that iterates from 1 to 50 (specified by 1..50).
  4. Within the loop, we use the DBMS_OUTPUT.PUT_LINE function to print the current number on the screen.
  5. Finally, we end the PL/SQL block with a /.

You can execute this PL/SQL program in an Oracle database environment. It will print the numbers from 1 to 50, one per line, in the output.

PL/SQL program that generates and prints even numbers from 2 to 100

DECLARE
  -- Declare a variable to store the current even number
  current_even NUMBER := 2;

BEGIN
  -- Use a loop to generate and print even numbers
  WHILE current_even <= 100 LOOP
    -- Print the current even number
    DBMS_OUTPUT.PUT_LINE(current_even);
    
    -- Increment the current_even variable by 2 to get the next even number
    current_even := current_even + 2;
  END LOOP;
END;
/

This PL/SQL program starts with an initial even number of 2 and uses a WHILE loop to generate and print even numbers in increments of 2 until it reaches 100. The DBMS_OUTPUT.PUT_LINE statement is used to display each even number in the output. You can run this program in a PL/SQL environment that supports DBMS_OUTPUT, such as Oracle SQL Developer or SQL*Plus.

write a PL/SQL program to find the area of a rectangle

-- Declare variables for length and width of the rectangle
DECLARE
    v_length NUMBER := 0;
    v_width NUMBER := 0;
    v_area   NUMBER := 0;
BEGIN
    -- Input the length and width of the rectangle from the user
    DBMS_OUTPUT.PUT_LINE('Enter the length of the rectangle: ');
    ACCEPT v_length NUMBER PROMPT 'Length: ';

    DBMS_OUTPUT.PUT_LINE('Enter the width of the rectangle: ');
    ACCEPT v_width NUMBER PROMPT 'Width: ';

    -- Calculate the area of the rectangle
    v_area := v_length * v_width;

    -- Display the result
    DBMS_OUTPUT.PUT_LINE('The area of the rectangle with length ' || v_length || ' and width ' || v_width || ' is: ' || v_area);
END;
/

In this PL/SQL program:

  1. We declare three variables: v_length to store the length of the rectangle, v_width to store the width of the rectangle, and v_area to store the calculated area.
  2. We use the DBMS_OUTPUT.PUT_LINE function to prompt the user to enter the length and width of the rectangle and then accept these values using the ACCEPT statement.
  3. Next, we calculate the area of the rectangle by multiplying the length and width, and store the result in the v_area variable.
  4. Finally, we use DBMS_OUTPUT.PUT_LINE to display the calculated area along with the provided length and width.

You can run this PL/SQL program in an Oracle database environment to find the area of a rectangle.

PL/SQL program to print the series from 1 to 50

-- PL/SQL program to print the series from 1 to 50
DECLARE
   i NUMBER := 1; -- Initialize a counter variable to 1
BEGIN
   -- Loop from 1 to 50
   FOR i IN 1..50 LOOP
      -- Print the current value of 'i'
      DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
   END LOOP;
END;
/

Here’s an explanation of the code:

  1. We declare a variable i of type NUMBER and initialize it to 1. This variable will serve as our counter to track the current value in the series.
  2. We use a BEGIN...END block to enclose our PL/SQL code.
  3. Inside the block, we use a FOR loop to iterate from 1 to 50. The loop variable i will automatically increment from 1 to 50.
  4. Within the loop, we use the DBMS_OUTPUT.PUT_LINE procedure to print the value of i along with a label for clarity.
  5. Finally, we terminate the PL/SQL block with a forward slash '/ to execute the code.

To run this PL/SQL code, you’ll need access to an Oracle Database environment that supports PL/SQL, and you should have the necessary privileges to execute PL/SQL programs. Additionally, make sure that the DBMS_OUTPUT package is enabled to see the output in your Oracle environment.

PL/SQL program that prints the series of numbers 1, 4, 7, 10, …, 40:

DECLARE
    v_number NUMBER := 1;  -- Initialize the starting number
BEGIN
    -- Use a loop to print the series
    WHILE v_number <= 40 LOOP
        DBMS_OUTPUT.PUT_LINE(v_number);  -- Print the current number
        v_number := v_number + 3;  -- Increment the number by 3 for the next iteration
    END LOOP;
END;
/

In this program:

  • We declare a variable v_number and initialize it with 1. This variable will keep track of the current number in the series.
  • We use a WHILE loop to iterate as long as v_number is less than or equal to 40.
  • Inside the loop, we use DBMS_OUTPUT.PUT_LINE to print the current value of v_number.
  • We increment v_number by 3 in each iteration to generate the next number in the series.

You can execute this PL/SQL program in an Oracle Database environment with DBMS_OUTPUT enabled to see the series printed from 1 to 40.

this video provides a practical example of using triggers in PL/SQL. You can view it