Plsql | Advanced Topics | Oracle Tutorial

Introduction to PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL. It combines the data manipulation power of SQL with the processing capabilities of procedural languages.

Benefits of PL/SQL

PL/SQL provides several advantages:

Combines SQL with procedural constructs
Supports structured programming with blocks, functions, and procedures
Improves performance with optimized execution
Enhances security and manageability

PL/SQL Block Structure

A PL/SQL block is the basic unit of a PL/SQL program. It has a defined structure consisting of:

Declaration section
Executable section
Exception handling section

Example of a PL/SQL block:

DECLARE
    -- Declaration section
    v_message VARCHAR2(50);
BEGIN
    -- Executable section
    v_message := 'Hello, PL/SQL!';
    DBMS_OUTPUT.PUT_LINE(v_message);
EXCEPTION
    -- Exception handling section
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;

Variables and Constants

PL/SQL supports the declaration and use of variables and constants. Variables can store data that changes during program execution, while constants store data that remains the same.

Example of declaring variables and constants:

DECLARE
    v_number NUMBER := 100;
    c_constant CONSTANT NUMBER := 500;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Variable value: ' || v_number);
    DBMS_OUTPUT.PUT_LINE('Constant value: ' || c_constant);
END;

Control Structures

PL/SQL provides control structures for conditional execution, loops, and branching.

Example of IF-THEN-ELSE statement:

DECLARE
    v_number NUMBER := 10;
BEGIN
    IF v_number < 20 THEN
        DBMS_OUTPUT.PUT_LINE('v_number is less than 20');
    ELSE
        DBMS_OUTPUT.PUT_LINE('v_number is 20 or more');
    END IF;
END;

Example of a LOOP statement:

DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        EXIT WHEN v_counter > 5;
        DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;

Cursors

Cursors are used to retrieve multiple rows from a query and process each row individually.

Example of using an explicit cursor:

DECLARE
    CURSOR c_emp IS
        SELECT emp_id, emp_name FROM employees;
    v_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_emp;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp.emp_id || ', Name: ' || v_emp.emp_name);
    END LOOP;
    CLOSE c_emp;
END;

Stored Procedures and Functions

Stored procedures and functions are named PL/SQL blocks that can be reused. Procedures perform actions, while functions return values.

Example of a stored procedure:

CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;

-- Execute the procedure
BEGIN
    greet_user('Alice');
END;

Example of a function:

CREATE OR REPLACE FUNCTION get_greeting (p_name IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
    RETURN 'Hello, ' || p_name || '!';
END;

-- Execute the function
DECLARE
    v_greeting VARCHAR2(50);
BEGIN
    v_greeting := get_greeting('Bob');
    DBMS_OUTPUT.PUT_LINE(v_greeting);
END;

Packages

Packages are collections of related procedures, functions, and other program objects grouped together as a single unit.

Example of creating a package:

CREATE OR REPLACE PACKAGE my_package AS
    PROCEDURE greet (p_name IN VARCHAR2);
    FUNCTION get_greeting (p_name IN VARCHAR2) RETURN VARCHAR2;
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package AS
    PROCEDURE greet (p_name IN VARCHAR2) AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
    END greet;

    FUNCTION get_greeting (p_name IN VARCHAR2) RETURN VARCHAR2 AS
    BEGIN
        RETURN 'Hello, ' || p_name || '!';
    END get_greeting;
END my_package;

-- Use the package
BEGIN
    my_package.greet('Charlie');
    DBMS_OUTPUT.PUT_LINE(my_package.get_greeting('Dave'));
END;

Triggers

Triggers are PL/SQL blocks that are automatically executed in response to certain events on a particular table or view.

Example of creating a trigger:

CREATE OR REPLACE TRIGGER before_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.emp_id := employees_seq.NEXTVAL;
END;

-- Insert a new employee
INSERT INTO employees (emp_name) VALUES ('Eve');

Exception Handling

PL/SQL provides a robust exception handling mechanism to catch and manage runtime errors.

Example of exception handling:

BEGIN
    -- Simulate an error
    RAISE_APPLICATION_ERROR(-20001, 'Custom error');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

Conclusion

PL/SQL is a powerful and versatile language that enhances the capabilities of SQL in Oracle. By using PL/SQL, you can create complex and efficient database applications with ease. This tutorial covered the basics and advanced topics of PL/SQL, providing you with a solid foundation to start developing your own PL/SQL programs.

Keep practicing and exploring the extensive features of PL/SQL to become proficient in using Oracle's procedural programming language.

Using PL/SQL for Advanced Procedural Programming