Structure of PL/SQL Program

 PL/SQL (Procedural Language/Structured Query Language) is a programming language used to develop applications that interact with Oracle databases. Here is the basic structure of a PL/SQL block, along with an example:

DECLARE
   -- Variable declarations
BEGIN
   -- PL/SQL statements
   -- ...
   -- ...
   -- ...
EXCEPTION
   -- Exception handling statements
END;
 

Let's break down each part of this structure and provide an example:

DECLARE: This section is used to declare variables that will be used in the PL/SQL block. Variables can be declared using the VAR keyword, followed by the name of the variable and its data type. Here is an example of variable declarations:

DECLARE
   my_name VARCHAR2(50) := 'John';
   my_age NUMBER := 30;
BEGIN
   -- PL/SQL statements
END;
 

BEGIN: This section contains the PL/SQL statements that will be executed. PL/SQL statements can include SQL queries, control structures like loops and conditional statements, and other procedural statements. Here is an example of a PL/SQL block that calculates the sum of two numbers and stores the result in a variable:

DECLARE
   num1 NUMBER := 10;
   num2 NUMBER := 20;
   result NUMBER;
BEGIN
   result := num1 + num2;
   DBMS_OUTPUT.PUT_LINE('The sum of ' || num1 || ' and ' || num2 || ' is ' || result);
END;
 

In this example, the DBMS_OUTPUT.PUT_LINE statement is used to print the result to the console.

EXCEPTION: This section is used to handle exceptions that may occur during the execution of the PL/SQL block. Exceptions are errors that occur during the execution of the block and can be handled using exception handling statements. Here is an example of a PL/SQL block that handles an exception:

DECLARE
   balance NUMBER := 100;
   withdrawal_amount NUMBER := 200;
BEGIN
   IF withdrawal_amount > balance THEN
      RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
   ELSE
      balance := balance - withdrawal_amount;
      DBMS_OUTPUT.PUT_LINE('Withdrawal successful. New balance is ' || balance);
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
 

In this example, the RAISE_APPLICATION_ERROR statement is used to raise an exception when the withdrawal amount is greater than the balance. The WHEN OTHERS clause in the exception section is used to handle any other exceptions that may occur during the execution of the block.

Example Program: Here is an example PL/SQL program to calculate the factorial of a given number using a recursive function:

DECLARE
  FUNCTION factorial(n IN NUMBER) RETURN NUMBER IS
  BEGIN
    IF n = 0 THEN
      RETURN 1;
    ELSE
      RETURN n * factorial(n - 1);
    END IF;
  END factorial;
 
  num NUMBER := 5; -- The number for which the factorial will be calculated
  result NUMBER;
BEGIN
  result := factorial(num);
  DBMS_OUTPUT.PUT_LINE('The factorial of ' || num || ' is ' || result);
END;
 

In this example, the factorial function is defined recursively to calculate the factorial of a given number. The IF statement checks if the input number is 0 and returns 1, as the factorial of 0 is 1. If the input number is not 0, the function calls itself with n - 1 as the input parameter, and multiplies the result with n.

The program declares a variable num and assigns it a value of 5. The factorial function is called with num as the input parameter, and the result is stored in the result variable. Finally, the result is printed to the console using the DBMS_OUTPUT.PUT_LINE statement.

When executed, this program will output:

The factorial of 5 is 120