PL/SQL (Procedural Language/Structured Query Language)

---

Procedural Language/Structured Query Language (PL/SQL) is Oracle Corporation's procedural language extension for SQL.

PL/SQL enables you to combine SQL statements with procedural logic, control structures, and error handling capabilities.

Key features and concepts of PL/SQL:

1. Blocks:

   • PL/SQL programs are organized into blocks, which can be anonymous or named.
   • A block consists of declarative, executable, and exception handling sections.
   • Example of an anonymous block:

     
     DECLARE
         -- Declarative section
         variable_declaration;
     BEGIN
         -- Executable section
         PL/SQL code;
     EXCEPTION
         -- Exception handling section
         WHEN exception_name THEN
             -- Handle the exception
     END;
     
     

2. Variables:

   • PL/SQL supports variables to store data temporarily.
   • Variables can be of different types such as VARCHAR2, NUMBER, DATE, etc.
   • Example:

     
     DECLARE

         emp_name VARCHAR2(50);
         emp_salary NUMBER(8,2);
     BEGIN
         emp_name := 'John Doe';
         emp_salary := 50000.00;
     END;

3. Control Structures:

   • PL/SQL provides control structures such as IF-THEN-ELSE, CASE, LOOP, WHILE, and FOR loops for implementing conditional and iterative logic.
   • Example:

     DECLARE
         x NUMBER := 10;
     BEGIN
         IF x > 0 THEN
             DBMS_OUTPUT.PUT_LINE('Positive number');
         ELSE
             DBMS_OUTPUT.PUT_LINE('Non-positive number');
         END IF;
     END;
     
     

4. Cursors:

   • Cursors in PL/SQL are used to retrieve data row by row from the result set of a SELECT statement.
   • Cursors can be explicit or implicit.
   • Example:

     
     DECLARE

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

5. Exception Handling:

   • PL/SQL provides exception handling to handle errors that may occur during program execution.
   • Exceptions can be predefined or user-defined.
   • Example:

     
     DECLARE

         x NUMBER := 0;
     BEGIN
         IF x = 0 THEN
             RAISE_APPLICATION_ERROR(-20001, 'Division by zero');
         END IF;
     EXCEPTION
         WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
     END;

PL/SQL is widely used for developing stored procedures, functions, triggers, and packages in Oracle databases, providing a powerful and flexible way to manipulate and manage data.

Enroll Now

• Database Management System
• SQL