PLSQL Blocks

 

Anonymous Block :

Ø  Unnamed block that is executed directly.

 

DECLARE

    v_dividend NUMBER := 10;

    v_divisor NUMBER := 0;

    v_result NUMBER;

BEGIN

    -- Attempting division

    v_result := v_dividend / v_divisor;

    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

 

EXCEPTION

    WHEN ZERO_DIVIDE THEN

        DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');

END;

/

 

PL/SQL Procedure:

Ø  A PL/SQL Procedure is a named PL/SQL block that performs a specific task.

Ø  It is stored in the database and can be executed multiple times.

Ø  Unlike functions, a procedure does not return a value directly but can modify data, process logic, or return values via OUT parameters.

 

CREATE OR REPLACE PROCEDURE greet_user

IS

BEGIN

    -- Print a welcome message

    DBMS_OUTPUT.PUT_LINE('Hello, Welcome to PL/SQL!');

 

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

END greet_user;

/

 

Executing Procedure :

 

1.

BEGIN

    greet_user;

END;

/

2.

EXEC greet_user;

 

 

 

 

 

PL/SQL Function

Ø  A PL/SQL Function is a named block that returns a single value.

Ø  Function must have a RETURN statement to return a result.

Ø  Can be used in SQL queries

 

CREATE OR REPLACE FUNCTION calculate_bonus (

    p_salary IN NUMBER

) RETURN NUMBER

IS

    v_bonus NUMBER;

BEGIN

    -- Calculate 10% bonus

    v_bonus := p_salary * 0.10;

   

    -- Return the calculated bonus

    RETURN v_bonus;

 

EXCEPTION

    WHEN OTHERS THEN

        RETURN NULL;  -- Handle errors

END calculate_bonus;

/

Function                                        V/S Procedure:

 

Feature	Function	Procedure
Returns a Value?	Yes (using RETURN).	No (can use OUT parameters instead).
Can be Used in SQL?	Yes (in SELECT, WHERE etc.).	No (cannot be used in SQL queries).
Can Modify Data?	Should not modify database data (DML operations are discouraged).	Can modify database data using INSERT, UPDATE, DELETE

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PL/SQL Package

Ø  A PL/SQL Package is a collection of related procedures, functions, variables, cursors, and exceptions stored together as a single unit in the database.

Ø  It helps in organizing and modularizing PL/SQL code for better performance and reusability.

Ø  Improves performance, modularity, and security.

Ø  Functions in a package can be used in SQL, but procedures cannot.

Ø   Private elements (inside Package Body) are not accessible outside.

Consists of two parts:
1️.Package Specification – Declares public procedures, functions, and variables.
2️.Package Body – Implements the actual logic of the procedures and functions.

 

Step 1: Create the Package Specification

CREATE OR REPLACE PACKAGE emp_salary_pkg IS

    -- Procedure to update salary

    PROCEDURE update_salary(emp_id IN NUMBER, p_percent IN NUMBER);

 

    -- Function to calculate bonus

    FUNCTION calculate_bonus(salary IN NUMBER) RETURN NUMBER;

END emp_salary_pkg;

/

Step 2: Create the Package Body

CREATE OR REPLACE PACKAGE BODY emp_salary_pkg IS

 

    -- Procedure to update salary

    PROCEDURE update_salary(emp_id IN NUMBER, p_percent IN NUMBER) IS

    BEGIN

        UPDATE employees

        SET salary = salary + (salary * p_percent / 100)

        WHERE employee_id = emp_id;

 

        DBMS_OUTPUT.PUT_LINE('Salary updated successfully for Employee ID: ' || emp_id);

    END update_salary;

 

    -- Function to calculate bonus

    FUNCTION calculate_bonus(salary IN NUMBER) RETURN NUMBER IS

    BEGIN

        RETURN salary * 0.10;

    END calculate_bonus;

 

END emp_salary_pkg;

/

 

 

 

 

Step 3: Execute the Package

Calling the Procedure

BEGIN

    emp_salary_pkg.update_salary(101, 10);

END;

/

Calling the Function

DECLARE

    v_bonus NUMBER;

BEGIN

    v_bonus := emp_salary_pkg.calculate_bonus(50000);

    DBMS_OUTPUT.PUT_LINE('Bonus Amount: ' || v_bonus);

END;

/