NoteVerse

PL/SQL

Comprehensive study notes, diagrams, and exam preparation for PL/SQL.

PL/SQL

Definition

PL/SQL is Oracle’s procedural extension to SQL that combines SQL’s data manipulation capabilities with programming constructs such as variables, control structures, exceptions, and modular program units.

In simple terms, PL/SQL allows you to write programs that interact directly with an Oracle database in a structured and efficient way.

Main Content

1. PL/SQL Block Structure

  • PL/SQL programs are written in blocks, and each block contains logically grouped statements.
  • A basic block usually has three sections:
  • Declaration section for variables, constants, cursors, and user-defined types.
  • Executable section for SQL statements and procedural code.
  • Exception section for handling runtime errors.

A general structure looks like this:

DECLARE
   v_name VARCHAR2(50);
BEGIN
   SELECT first_name INTO v_name
   FROM employees
   WHERE employee_id = 101;

   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
/

Explanation:

  • DECLARE is optional and used for declarations.
  • BEGIN marks the start of executable statements.
  • EXCEPTION handles errors.
  • END; closes the block.

Types of PL/SQL blocks:

Anonymous blocks

  • : Created and executed without a name; commonly used for one-time tasks.

Named blocks

  • : Stored in the database, such as procedures, functions, packages, and triggers.

Why block structure is important:

  • It improves readability and organization.
  • It separates data declaration, logic, and error handling.
  • It supports modular programming.

2. PL/SQL Language Elements

  • PL/SQL includes a rich set of language elements that make programming easier and more powerful.
  • These elements include variables, data types, operators, control statements, cursors, and exceptions.

Variables and data types:

  • Variables store temporary values during program execution.
  • Common data types include:
  • VARCHAR2 for text
  • NUMBER for numeric values
  • DATE for dates
  • BOOLEAN for logical values

Example:

DECLARE
   v_salary NUMBER := 50000;
   v_name   VARCHAR2(30) := 'Asha';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_salary);
END;
/

Control structures:

Conditional statements

  • : IF, IF...THEN...ELSE, CASE

Looping statements

  • : LOOP, WHILE LOOP, FOR LOOP

Example:

BEGIN
   FOR i IN 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('Count: ' || i);
   END LOOP;
END;
/

Cursors:

  • A cursor is a pointer to the result set of a query.
  • Used when PL/SQL needs to process query rows one by one.
  • Types include:
  • Implicit cursors created automatically by Oracle
  • Explicit cursors declared by the programmer

Exceptions:

  • Exceptions are runtime errors or abnormal conditions.
  • Common exceptions:
  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • ZERO_DIVIDE
  • OTHERS for all remaining errors

Example:

BEGIN
   DECLARE
      v_result NUMBER;
   BEGIN
      v_result := 10 / 0;
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
         DBMS_OUTPUT.PUT_LINE('Cannot divide by zero.');
   END;
END;
/

3. PL/SQL Program Units

  • PL/SQL supports reusable program units that make database applications modular and maintainable.
  • The main program units are procedures, functions, packages, and triggers.

Procedures:

  • A procedure performs a task but does not necessarily return a value.
  • It can accept parameters as input, output, or both.

Example:

CREATE OR REPLACE PROCEDURE greet_employee(p_name IN VARCHAR2) IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name);
END;
/

Functions:

  • A function returns a single value.
  • It is often used in SQL expressions or PL/SQL programs.

Example:

CREATE OR REPLACE FUNCTION get_bonus(p_salary NUMBER)
RETURN NUMBER IS
BEGIN
   RETURN p_salary * 0.10;
END;
/

Packages:

  • A package groups related procedures, functions, variables, and cursors.
  • It usually has two parts:
  • Package specification
  • Package body

Benefits of packages:

  • Encapsulation
  • Better organization
  • Easier maintenance
  • Improved performance due to loading efficiency

Triggers:

  • A trigger automatically runs in response to events such as INSERT, UPDATE, or DELETE.
  • Triggers are used for auditing, validation, and enforcing rules.

Example idea:

  • If a salary update occurs, a trigger can log the old and new salary values.

Why program units matter:

  • They promote reusability.
  • They reduce duplication of code.
  • They make large systems easier to manage.

Working / Process

1. Write the PL/SQL block or program unit

  • Decide whether the task should be done in an anonymous block, procedure, function, package, or trigger.
  • Declare variables, cursors, and any required data types.
  • Write the logic using SQL and procedural statements.

2. Compile and validate the code

  • Oracle checks the syntax and checks whether referenced objects exist.
  • Errors such as invalid table names, undeclared variables, or syntax mistakes must be corrected.
  • Stored program units are compiled before execution.

3. Execute and handle results

  • The executable part runs statement by statement.
  • SQL statements interact with tables, while PL/SQL controls flow and logic.
  • If an error occurs, the exception section handles it gracefully.

A simple flow can be understood as:

Input/Requirement
      ↓
Declarations
      ↓
Execution of SQL + Logic
      ↓
Exception Handling
      ↓
Output / Database Update

Example process: Suppose you want to display employee names with salaries above a threshold:

  • Declare a variable for the threshold
  • Query the employee table
  • Use a cursor or loop to process matching rows
  • Print or store the output
  • Handle errors if no rows are found

This process shows how PL/SQL combines database access with programming control.

Advantages / Applications

Improved performance

  • PL/SQL reduces network traffic because multiple SQL statements and logic can run inside the database server.

Better security

  • Business logic can be placed in stored procedures and packages, limiting direct table access.

Modularity and reuse

  • Code written once in procedures, functions, and packages can be reused many times.

Error handling

  • Built-in exception handling allows programs to manage failures more effectively.

Database automation

  • Triggers and scheduled procedures can automatically enforce rules and perform tasks.

Widely used in enterprise applications

  • PL/SQL is commonly applied in banking, payroll, inventory, reporting, and transaction processing systems.

Typical applications:

  • Validating business rules before data is inserted or updated
  • Generating reports from database tables
  • Performing batch updates and data cleanup
  • Logging user actions for auditing
  • Implementing secure database APIs for application layers

Summary

  • PL/SQL is Oracle’s procedural language for working with SQL.
  • It uses blocks, variables, conditions, loops, exceptions, and reusable program units.
  • It is used to write efficient, secure, and maintainable database programs.
  • Key terms to remember: block, variable, cursor, exception, procedure, function, package, trigger
← Prev: Indexing
Next: Domains →