Introduction to Oracle PL/SQL: A Step-by-Step Guide for Beginners #2

8 min readApr 13, 2023

Welcome to the second article in our series on Oracle PL/SQL for beginners. In the previous article, we introduced you to the basics of Oracle PL/SQL, setting up the Oracle environment, and working with procedures and functions. In this article, we will cover more advanced topics, such as cursors, error handling, packages, and triggers.

Cursors in PL/SQL

Introduction to cursors

Cursors are essential when working with multiple rows in a result set. They allow you to retrieve and manipulate rows from a result set one at a time. Cursors can be either implicit or explicit, depending on the level of control required.

Implicit cursors

Implicit cursors are automatically created and managed by Oracle when you execute a SQL statement within PL/SQL. They are used for simple DML operations (INSERT, UPDATE, DELETE) and SELECT statements that return a single row. For example:

DECLARE
  emp_name VARCHAR2(50);
BEGIN
  SELECT first_name || ' ' || last_name
  INTO emp_name
  FROM employees
  WHERE employee_id = 100;
  
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_name);
END;

Explicit cursors (OPEN, FETCH, CLOSE)

Explicit cursors provide more control over data retrieval and manipulation. You need to declare, open, fetch data, and close them manually. For example:

DECLARE
  CURSOR emp_cursor IS
    SELECT first_name || ' ' || last_name AS full_name
    FROM employees;
    
  emp_name VARCHAR2(50);
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO emp_name;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_name);
  END LOOP;
  CLOSE emp_cursor;
END;

Cursor FOR loops

Cursor FOR loops simplifies the process of working with explicit cursors by automatically handling the opening, fetching, and closing operations. For example:

DECLARE
  CURSOR emp_cursor IS
    SELECT first_name || ' ' || last_name AS full_name
    FROM employees;
BEGIN
  FOR emp_record IN emp_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_record.full_name);
  END LOOP;
END;

In this example, the cursor FOR loop iterates through each row in the result set and automatically fetches the data, eliminating the need for manual OPEN, FETCH, and CLOSE statements.

Error handling in PL/SQL

Introduction to exceptions

Exceptions are events that occur during the execution of a PL/SQL block when an error or abnormal condition is encountered. Exception handling is crucial for managing errors and ensuring the proper functioning of your PL/SQL code.

Predefined exceptions

Oracle provides several predefined exceptions to handle common error situations. Some examples include:

NO_DATA_FOUND: Raised when a SELECT statement returns no rows.
TOO_MANY_ROWS: Raised when a SELECT statement returns more than one row.
ZERO_DIVIDE: Raised when an attempt is made to divide a number by zero.

Example:

DECLARE
  emp_name VARCHAR2(50);
BEGIN
  SELECT first_name || ' ' || last_name
  INTO emp_name
  FROM employees
  WHERE employee_id = -1;
  
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found with the given ID');
END;

User-defined exceptions

You can create custom exceptions to handle specific situations not covered by predefined exceptions. Example:

DECLARE
  invalid_age EXCEPTION;
  age NUMBER := 17;
BEGIN
  IF age < 18 THEN
    RAISE invalid_age;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Valid age');
  END IF;
EXCEPTION
  WHEN invalid_age THEN
    DBMS_OUTPUT.PUT_LINE('Age must be 18 or older');
END;

Handling exceptions with the EXCEPTION block

To handle exceptions, you need to add an EXCEPTION block in your PL/SQL code. This block contains one or more exception handlers, which define the actions to be taken when a specific exception is raised. Example:

DECLARE
  emp_name VARCHAR2(50);
BEGIN
  SELECT first_name || ' ' || last_name
  INTO emp_name
  FROM employees
  WHERE employee_id = 100;
  
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found with the given ID');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple employees found with the given ID');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

In this example, different exception handlers are defined for NO_DATA_FOUND, TOO_MANY_ROWS, and an additional catch-all handler for any other exception using the OTHERS keyword.

Introduction to PL/SQL packages

Overview of packages

A package is a schema object that groups related PL/SQL types, variables, constants, subprograms (procedures and functions), and cursors under a single name. Packages provide modularity, encapsulation, and maintainability to your PL/SQL code.

Package specification and body

A package consists of two parts: the package specification and the package body.

Package specification: It is the interface to your package, containing the declarations of public types, variables, constants, exceptions, cursors, and subprograms. Users can access these objects through the package.

CREATE OR REPLACE PACKAGE employee_pkg IS
  g_company_name CONSTANT VARCHAR2(30) := 'ABC Corporation';
  PROCEDURE display_employee_name(emp_id NUMBER);
  FUNCTION get_employee_salary(emp_id NUMBER) RETURN NUMBER;
END employee_pkg;

Package body: It is the implementation of the package, containing the definitions of private types, variables, constants, and the code for the subprograms declared in the package specification.

CREATE OR REPLACE PACKAGE BODY employee_pkg IS
  PROCEDURE display_employee_name(emp_id NUMBER) IS
    emp_name VARCHAR2(50);
  BEGIN
    SELECT first_name || ' ' || last_name
    INTO emp_name
    FROM employees
    WHERE employee_id = emp_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_name);
  END display_employee_name;

  FUNCTION get_employee_salary(emp_id NUMBER) RETURN NUMBER IS
    emp_salary NUMBER;
  BEGIN
    SELECT salary
    INTO emp_salary
    FROM employees
    WHERE employee_id = emp_id;
    
    RETURN emp_salary;
  END get_employee_salary;
END employee_pkg;

Benefits of using packages

Packages offer several advantages:

Modularity: They allow you to organize related subprograms, types, and variables in a single unit, making it easier to manage and understand your code.
Encapsulation: Packages help you hide implementation details by allowing you to declare objects as private, so they can only be accessed within the package.
Reusability: You can reuse package objects in multiple PL/SQL blocks or other packages, reducing code duplication.
Maintainability: Changes in the package body don’t require recompilation of the dependent PL/SQL blocks, as long as the package specification remains unchanged.
Improved performance: Oracle Database loads the entire package into memory when you reference any of its objects, reducing the number of calls to the database and improving performance.

PL/SQL triggers

Overview of triggers

Triggers are special types of stored procedures that are automatically executed (or fired) when a specific event occurs in the database, such as DML (INSERT, UPDATE, DELETE) or DDL (CREATE, ALTER, DROP) statements or system events. They are useful for maintaining data consistency, performing audits, enforcing business rules, or replicating data.

Types of triggers

There are several types of triggers, including:

DML triggers: These triggers are fired in response to DML statements (INSERT, UPDATE, DELETE) on specified tables or views. They can be row-level triggers (executed once for each affected row) or statement-level triggers (executed once for each triggering statement). DML triggers can be further categorized as: BEFORE triggers: Executed before the triggering event. AFTER triggers: Executed after the triggering event.
DDL triggers: These triggers are fired in response to DDL statements (CREATE, ALTER, DROP, etc.) on specified schema objects or the entire schema. They are always statement-level triggers.
INSTEAD OF triggers: These triggers are defined on views and are executed instead of the triggering DML statement. They are useful for modifying data in the underlying tables of the view.
System event triggers: These triggers are fired in response to system events such as logon, logoff, startup, or shutdown. They can be used for tasks like security, auditing, or resource management.

Creating and managing triggers

Creating a trigger

CREATE OR REPLACE TRIGGER trg_before_employee_update
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
  salary_difference NUMBER;
BEGIN
  salary_difference := :NEW.salary - :OLD.salary;
  IF salary_difference < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased.');
  END IF;
END;
/

This example creates a row-level BEFORE UPDATE trigger on the employees table that prevents salary decreases.

Managing triggers:

You can disable or enable a trigger using the ALTER TRIGGER statement:

ALTER TRIGGER trg_before_employee_update DISABLE;
ALTER TRIGGER trg_before_employee_update ENABLE;

To view information about triggers in your schema, you can query the USER_TRIGGERS data dictionary view:

SELECT trigger_name, table_name, status
FROM user_triggers;

To drop a trigger, use the DROP TRIGGER statement:

DROP TRIGGER trg_before_employee_update;

Useful built-in PL/SQL functions and packages

Date and time functions

SYSDATE: Returns the current date and time.

SELECT SYSDATE FROM dual;

TO_DATE: Converts a string to a date.

SELECT TO_DATE('2023-04-09', 'YYYY-MM-DD') FROM dual;

TO_CHAR: Converts a date to a string with a specified format.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

ADD_MONTHS: Adds a specified number of months to a date.

SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;

String manipulation functions:

LENGTH: Returns the length of a string.

SELECT LENGTH('Oracle PL/SQL') FROM dual;

SUBSTR: Returns a substring from a string.

SELECT SUBSTR('Oracle PL/SQL', 8, 5) FROM dual;

REPLACE: Replaces occurrences of a substring within a string.

SELECT REPLACE('Oracle PL/SQL', 'PL/SQL', 'SQL') FROM dual;

CONCAT: Concatenates two strings.

SELECT CONCAT('Oracle', ' PL/SQL') FROM dual;

Numeric functions

ROUND: Rounds a number to a specified decimal place.

SELECT ROUND(3.14159, 2) FROM dual;

TRUNC: Truncates a number to a specified decimal place.

SELECT TRUNC(3.14159, 2) FROM dual;

MOD: Returns the remainder of a division operation.

SELECT MOD(11, 3) FROM dual;

Conversion functions

TO_NUMBER: Converts a string to a number

SELECT TO_NUMBER('12345') FROM dual;

TO_CHAR: Converts a number to a string.

SELECT TO_CHAR(12345) FROM dual;

DBMS_OUTPUT and other built-in packages

DBMS_OUTPUT: Allows you to display output from PL/SQL blocks using the PUT_LINE procedure.

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;
/

UTL_FILE: Provides file I/O capabilities for PL/SQL.
DBMS_SCHEDULER: Allows you to create, manage, and schedule jobs in the Oracle database.
DBMS_STATS: Provides tools for gathering and managing optimizer statistics.
DBMS_CRYPTO: Offers cryptographic functions for data encryption and decryption.

Note that there are many built-in packages in Oracle, and they cover a wide range of functionalities. Explore Oracle documentation for more details on specific packages and their usage.

Tips for writing efficient and maintainable PL/SQL code

Modular programming

Modular programming involves breaking your code into smaller, reusable pieces (modules) such as procedures, functions, and packages. This improves code readability, maintainability, and reusability.

-- Procedure to calculate the area of a rectangle
CREATE PROCEDURE calculate_rectangle_area (p_length NUMBER, p_width NUMBER, p_area OUT NUMBER) AS
BEGIN
  p_area := p_length * p_width;
END;
/

Code commenting and documentation

Adding comments and documentation to your code helps others understand its purpose, functionality, and any assumptions or constraints. Use single-line comments ( — ), multi-line comments (/* … */), and header comments to document your code.

/*
  Procedure: calculate_rectangle_area
  Purpose:   Calculate the area of a rectangle
  Parameters:
    - p_length: Length of the rectangle
    - p_width:  Width of the rectangle
    - p_area:   Output parameter to hold the calculated area
*/

CREATE PROCEDURE calculate_rectangle_area (p_length NUMBER, p_width NUMBER, p_area OUT NUMBER) AS
BEGIN
  -- Calculate the area by multiplying length and width
  p_area := p_length * p_width;
END;
/

Proper use of exception handling

Exception handling allows you to catch and handle errors gracefully. Use predefined and user-defined exceptions to handle specific error scenarios.

DECLARE
  v_dividend NUMBER := 100;
  v_divisor  NUMBER := 0;
  v_result   NUMBER;
BEGIN
  BEGIN
    v_result := v_dividend / v_divisor;
  EXCEPTION
    WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
  END;
  DBMS_OUTPUT.PUT_LINE('Result: ' || NVL(TO_CHAR(v_result), 'Not available'));
END;
/

Performance considerations

Optimizing your PL/SQL code can significantly improve its performance. Some tips for performance optimization include:

Use BULK COLLECT and FORALL statements to fetch or manipulate data in bulk, reducing context switches between SQL and PL/SQL engines.
Utilize PL/SQL collections and records to manipulate data in memory.
Minimize the use of loops by leveraging SQL set-based operations where possible.
Use the appropriate data types to reduce memory consumption and improve efficiency.
Use native compilation for performance-critical PL/SQL code.
Regularly analyze and gather statistics on your database objects using DBMS_STATS package.

Conclusion

In this article, we explored Oracle PL/SQL intermediate topics, including cursors, error handling, packages, triggers, built-in functions, and writing efficient code. We hope these discussions have expanded your knowledge of PL/SQL and prepared you for more advanced tasks.

As you continue to delve deeper into these topics, your expertise in Oracle PL/SQL will grow, enabling you to tackle complex database projects with confidence. Look forward to upcoming articles in this series, where we’ll dive further into each subject with more examples and insights to enhance your PL/SQL development skills.

Master Coding: Read, Analyze, Implement, Repeat