Master of the universe

Getting Started With PL/SQL: Basics And Extensions

Introduction

PL/SQL, or Procedural Language/Structured Query Language, is a powerful programming language designed specifically for the Oracle Database management system. It allows developers to create and manage stored procedures, functions, and triggers, offering a robust and efficient way to manipulate and process data within the database. In this article, we will cover the basics of PL/SQL and dive into some of its essential extensions, providing you with the necessary foundation to start utilizing this powerful language in your Oracle database management tasks.

PL/SQL Basics

What is PL/SQL?

PL/SQL is a procedural extension of SQL, designed to provide advanced programming capabilities not available in standard SQL. It allows developers to write complex algorithms, create reusable code, and improve the overall performance of database applications. PL/SQL combines the flexibility of SQL with the power of procedural programming languages, providing a versatile tool for developers working with Oracle databases.

Standard SQL is a language designed for managing data in relational databases, allowing users to query, insert, update, and delete data. However, SQL lacks several features that are essential for more advanced programming tasks, such as looping, conditional statements, and error handling. PL/SQL fills this gap by adding procedural constructs to SQL, making it possible to create more sophisticated database applications.

PL/SQL Blocks

A PL/SQL block is the fundamental unit of PL/SQL code. It consists of three main sections: the declaration, execution, and exception sections.

  • The declaration section is where variables, constants, cursors, and other elements are defined. This section is optional, but it is a good practice to include it for better code organization.
  • The execution section contains the actual PL/SQL code, including SQL statements, loops, and conditional statements. This is the only mandatory section in a PL/SQL block.
  • The exception section handles errors that may occur during the execution of the PL/SQL block. This section is optional, but it is highly recommended to include proper error handling in your PL/SQL code.

There are two types of PL/SQL blocks:

  1. Anonymous blocks: These are unnamed blocks of code that are executed immediately and are not stored in the database. Anonymous blocks are useful for testing and ad hoc tasks.
  2. Named blocks: These are blocks of code that are given a name and can be stored in the database. Named blocks can be procedures, functions, or triggers.
https://www.youtube.com/watch?v=J5wjIf4gdq4

Variables and Data Types

Variables are used to store and manipulate data in PL/SQL. To use a variable, you must first declare it in the declaration section of a PL/SQL block. When declaring a variable, you must specify its data type and, optionally, provide an initial value.

Here's an example of declaring and initializing a variable in PL/SQL:

DECLARE
  v_name VARCHAR2(50) := 'John Doe';
  v_age NUMBER := 30;
BEGIN
  -- Execution section code here
END;

Common PL/SQL data types include:

  • NUMBER: Represents numeric values, including integers and decimals.
  • VARCHAR2: Represents variable-length character strings.
  • DATE: Represents dates, including day, month, and year.
  • TIMESTAMP: Represents dates and times, including fractional seconds.
  • BOOLEAN: Represents true or false values.

Control Structures

Control structures in PL/SQL allow you to control the flow of your code by using conditional statements and loops.

  • Conditional statements include IF, ELSIF, and ELSE. These statements allow you to execute different sections of code based on one or more conditions. For example: 
    IF v_age >= 18 THEN
  dbms_output.put_line('You are an adult.');
ELSIF v_age >= 13 THEN
  dbms_output.put_line('You are a teenager.');
ELSE
  dbms_output.put_line('You are a child.');
END IF;
  • Looping constructs include LOOP, WHILE, and FOR. These constructs allow you to repeat a block of code multiple times. For example: 
    FOR i IN 1..5 LOOP
  dbms_output.put_line('Iteration: ' || i);
END LOOP;

    Working with Procedures and Functions

    PL/SQL procedures and functions are named blocks of code that can be stored in the database and called as needed. Procedures and functions are similar in many ways, but they have some key differences that set them apart.

    Procedures

    A PL/SQL procedure is a named block of code that performs a specific action or series of actions. It can be called from other PL/SQL blocks, or from other database objects, such as triggers or other procedures. Procedures can also accept input parameters and return output parameters, allowing them to process and return data as needed.

    Here's an example of creating a simple procedure:

    CREATE OR REPLACE PROCEDURE greet_user(p_name VARCHAR2) IS
BEGIN
  dbms_output.put_line('Hello, ' || p_name || '!');
END;

    To call a procedure, you can use the EXECUTE statement or simply the procedure name followed by its parameters:

    EXECUTE greet_user('John Doe');
-- or
greet_user('John Doe');

    You can also modify an existing procedure using the CREATE OR REPLACE statement:

    CREATE OR REPLACE PROCEDURE greet_user(p_name VARCHAR2, p_age NUMBER) IS
BEGIN
  dbms_output.put_line('Hello, ' || p_name || '! You are ' || p_age || ' years old.');
END;

    Procedures can accept parameters using the IN, OUT, and IN OUT modes:

    • IN: The parameter is used for input, and its value cannot be changed within the procedure. This is the default mode.
    • OUT: The parameter is used for output, and its value can be changed within the procedure.
    • IN OUT: The parameter can be used for both input and output, allowing its value to be modified and returned by the procedure.

    Functions

    A PL/SQL function is a named block of code that returns a single value. Functions can be called from other PL/SQL blocks, or they can be used directly within SQL statements. Like procedures, functions can also accept input parameters, but they must also define a return type.

    Here's an example of creating a simple function:

    CREATE OR REPLACE FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER IS
  v_sum NUMBER;
BEGIN
  v_sum := p_num1 + p_num2;
  RETURN v_sum;
END;

    To call a function, you can use the function name followed by its parameters:

    DECLARE
  v_result NUMBER;
BEGIN
  v_result := add_numbers(5, 7);
  dbms_output.put_line('The sum is: ' || v_result);
END;

    You can also modify an existing function using the CREATE OR REPLACE statement:

    CREATE OR REPLACE FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER, p_num3 NUMBER) RETURN NUMBER IS
  v_sum NUMBER;
BEGIN
  v_sum := p_num1 + p_num2 + p_num3;
  RETURN v_sum;
END;

    The main differences between procedures and functions are:

    1. Functions must return a value, while procedures do not.
    2. Functions can be used directly within SQL statements, while procedures cannot.

    Triggers and Exception Handling

    Triggers and exception handling are essential components of PL/SQL programming, allowing you to respond to events and handle errors effectively.

    Triggers

    A PL/SQL trigger is a named block of code that is automatically executed in response to an event, such as an INSERT, UPDATE, DELETE, or other database operation. Triggers can be used to enforce business rules, maintain data integrity, or audit changes to data.

    Triggers can be classified into the following types:

    1. Row-level triggers: These triggers are executed once for each row affected by the triggering event.
    2. Statement-level triggers: These triggers are executed once for each triggering event, regardless of the number of rows affected.
    3. Before triggers: These triggers are executed before the triggering event.
    4. After triggers: These triggers are executed after the triggering event.

    To create a trigger, you can use the CREATE TRIGGER statement:

    CREATE OR REPLACE TRIGGER trg_before_insert_user
  BEFORE INSERT ON users
  FOR EACH ROW
DECLARE
  v_username VARCHAR2(20);
BEGIN
  v_username := :NEW.username;
  IF v_username IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'Username cannot be null.');
  END IF;
END;

    You can also modify an existing trigger using the CREATE OR REPLACE statement:

    CREATE OR REPLACE TRIGGER trg_before_insert_user
  BEFORE INSERT ON users
  FOR EACH ROW
DECLARE
  v_username VARCHAR2(20);
  v_email VARCHAR2(50);
BEGIN
  v_username := :NEW.username;
  v_email := :NEW.email;

  IF v_username IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'Username cannot be null.');
  ELSIF v_email IS NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'Email cannot be null.');
  END IF;
END;

    Exception Handling

    Exception handling in PL/SQL is a powerful feature that allows you to handle errors and unexpected situations gracefully. By including exception handling code in your PL/SQL blocks, you can ensure that your programs continue to run smoothly even when errors occur.

    PL/SQL provides a set of predefined exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE, which can be used to handle common error situations. You can also define custom exceptions to handle application-specific errors.

    To handle exceptions in PL/SQL, you can use the RAISE, RAISE_APPLICATION_ERROR, and EXCEPTION_INIT statements:

    • The RAISE statement is used to explicitly raise an exception within your PL/SQL code. For example: 
      IF v_age < 0 THEN
  RAISE VALUE_ERROR;
END IF;
    • The RAISE_APPLICATION_ERROR procedure is used to raise a user-defined exception with a custom error message and error number. For example: 
      IF v_age < 0 THEN
  RAISE_APPLICATION_ERROR(-20000, 'Age cannot be negative.');
END IF;
    • The EXCEPTION_INIT pragma is used to associate a custom exception with a specific Oracle error number. For example: 
      DECLARE
  e_invalid_age EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_age, -20000);
BEGIN
  -- Execution section code here
EXCEPTION
  WHEN e_invalid_age THEN
    dbms_output.put_line('Error: Age cannot be negative.');
END;

      Cursors in PL/SQL

      Cursors are an essential feature of PL/SQL that allows you to retrieve and manipulate rows of data from a result set. There are two types of cursors in PL/SQL: implicit cursors and explicit cursors.

      Implicit Cursors

      Implicit cursors are automatically created and managed by PL/SQL when you execute a SQL statement that returns one or more rows. You don't need to declare or manage implicit cursors explicitly. However, you can access the attributes of the most recently executed implicit cursor using the %ROWCOUNT, %FOUND, %NOTFOUND, and %ISOPEN attributes:

      • %ROWCOUNT: Returns the number of rows affected by the most recent SQL statement.
      • %FOUND: Returns TRUE if the most recent SQL statement affected at least one row, or FALSE otherwise.
      • %NOTFOUND: Returns TRUE if the most recent SQL statement did not affect any rows, or FALSE otherwise.
      • %ISOPEN: Returns TRUE if the most recent SQL statement is still open and has not been closed, or FALSE otherwise.

      Here's an example of using implicit cursor attributes in PL/SQL:

      DECLARE
  v_employee_count NUMBER;
BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

  v_employee_count := SQL%ROWCOUNT;

  IF SQL%FOUND THEN
    dbms_output.put_line('Updated ' || v_employee_count || ' employees.');
  ELSE
    dbms_output.put_line('No employees updated.');
  END IF;
END;

      Explicit Cursors

      Explicit cursors give you more control over the retrieval and processing of rows from a result set. You need to declare, open, fetch, and close explicit cursors explicitly in your PL/SQL code.

      To use an explicit cursor, follow these steps:

      1. Declare the cursor in the declaration section of your PL/SQL block. The cursor declaration includes a SELECT statement that defines the result set for the cursor. 
        DECLARE
  CURSOR c_employees IS
    SELECT employee_id, first_name, last_name FROM employees;
      2. Open the cursor in the execution section of your PL/SQL block. This step also initializes the result set for the cursor. 
        BEGIN
  OPEN c_employees;
      3. Fetch rows from the cursor's result set one at a time or in batches. You can use the FETCH statement or a FOR loop to retrieve rows. 
        FETCH c_employees INTO v_employee_id, v_first_name, v_last_name;

        Alternatively, you can use a FOR loop:

        FOR rec IN c_employees LOOP
  dbms_output.put_line('Employee ID: ' || rec.employee_id || ', Name: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
      4. Close the cursor when you are done processing the result set. This step releases the resources associated with the cursor. 
        CLOSE c_employees;
END;

      Explicit cursors can also be used with parameters and FOR loops to create more flexible and reusable code. For example:

      DECLARE
  CURSOR c_employees(p_department_id NUMBER) IS
    SELECT employee_id, first_name, last_name FROM employees WHERE department_id = p_department_id;

  v_department_id NUMBER := 10;
BEGIN
  FOR rec IN c_employees(v_department_id) LOOP
    dbms_output.put_line('Employee ID: ' || rec.employee_id || ', Name: ' || rec.first_name || ' ' || rec.last_name);
  END LOOP;
END;

      PL/SQL Extensions

      PL/SQL offers several powerful extensions that can help you optimize your code and perform advanced tasks more efficiently. In this section, we will cover two essential PL/SQL extensions: dynamic SQL and bulk operations.

      Dynamic SQL

      Dynamic SQL allows you to build and execute SQL statements at runtime, which can be particularly useful when the structure of your query or the objects being queried are not known until execution time. PL/SQL provides two main ways to execute dynamic SQL: EXECUTE IMMEDIATE and the DBMS_SQL package.

      • EXECUTE IMMEDIATE: This statement allows you to execute a single SQL statement that has been dynamically constructed as a string. For example: 
        DECLARE
  v_table_name VARCHAR2(30) := 'employees';
  v_column_name VARCHAR2(30) := 'salary';
  v_increment NUMBER := 0.1;
BEGIN
  EXECUTE IMMEDIATE 'UPDATE ' || v_table_name || ' SET ' || v_column_name || ' = ' || v_column_name || ' * (1 + ' || v_increment || ')';
END;
      • DBMS_SQL: This package provides a more advanced and flexible way to work with dynamic SQL, allowing you to execute multiple SQL statements, work with cursors, and bind variables. For example: 
        DECLARE
  v_table_name VARCHAR2(30) := 'employees';
  v_column_name VARCHAR2(30) := 'salary';
  v_increment NUMBER := 0.1;
  v_cursor INTEGER;
  v_rows_affected INTEGER;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, 'UPDATE ' || v_table_name || ' SET ' || v_column_name || ' = ' || v_column_name || ' * (1 + ' || v_increment || ')', DBMS_SQL.NATIVE);
  v_rows_affected := DBMS_SQL.EXECUTE(v_cursor);
  DBMS_SQL.CLOSE_CURSOR(v_cursor);

  dbms_output.put_line('Rows affected: ' || v_rows_affected);
END;

      Bulk Operations

      Bulk operations in PL/SQL can significantly improve the performance of your code by reducing the number of context switches between the PL/SQL and SQL engines. The main bulk operations provided by PL/SQL are BULK COLLECT and FORALL.

      • BULK COLLECT: This statement allows you to fetch multiple rows from a cursor in a single operation, reducing the overhead associated with fetching rows one at a time. For example: 
        DECLARE
  TYPE t_employee_ids IS TABLE OF employees.employee_id%TYPE;
  v_employee_ids t_employee_ids;
BEGIN
  SELECT employee_id BULK COLLECT INTO v_employee_ids FROM employees WHERE department_id = 10;

  FOR i IN v_employee_ids.FIRST .. v_employee_ids.LAST LOOP
    dbms_output.put_line('Employee ID: ' || v_employee_ids(i));
  END LOOP;
END;
      • FORALL: This statement allows you to execute a DML statement, such as INSERT, UPDATE, or DELETE, for multiple rows in a single operation. FORALL works in conjunction with collections and can greatly improve the performance of your code when processing large data sets. For example: 
        DECLARE
  TYPE t_employee_ids IS TABLE OF employees.employee_id%TYPE;
  TYPE t_salaries IS TABLE OF employees.salary%TYPE;
  v_employee_ids t_employee_ids;
  v_salaries t_salaries;
BEGIN
  SELECT employee_id, salary BULK COLLECT INTO v_employee_ids, v_salaries FROM employees WHERE department_id = 10;

  FORALL i IN v_employee_ids.FIRST .. v_employee_ids.LAST
    UPDATE employees SET salary = v_salaries(i) * 1.1 WHERE employee_id = v_employee_ids(i);
END;

      By incorporating these PL/SQL extensions into your code, you can enhance the functionality and performance of your Oracle database applications.

      Conclusion

      In this article, we have covered the basics of PL/SQL programming, including the structure of PL/SQL blocks, variables and data types, control structures, procedures, functions, triggers, exception handling, and cursors. We have also introduced some powerful PL/SQL extensions, such as dynamic SQL and bulk operations.

      By understanding and applying these concepts in your Oracle database management tasks, you can create efficient, flexible, and reusable code that will help you optimize the performance of your database applications and ensure their reliability.

      As you continue to work with PL/SQL, remember that practice is key to mastering this powerful programming language. Keep experimenting with different features and techniques, and don't be afraid to tackle more advanced topics as you gain experience.

      Frequently Asked Questions

      What is the difference between PL/SQL and SQL?

      PL/SQL is a procedural extension of SQL, designed to provide advanced programming capabilities not available in standard SQL. While SQL is focused on managing data in relational databases, PL/SQL adds procedural constructs such as looping, conditional statements, and error handling, allowing developers to create more sophisticated database applications.

      How do I create a PL/SQL procedure?

      To create a PL/SQL procedure, use the CREATE OR REPLACE PROCEDURE statement followed by the procedure name, input parameters (if any), and the PL/SQL block containing the procedure's code. For example:

      CREATE OR REPLACE PROCEDURE my_procedure(p_param1 NUMBER, p_param2 VARCHAR2) IS
BEGIN
  -- Your code here
END;

      How do I call a PL/SQL function in a SQL query?

      To call a PL/SQL function in a SQL query, simply include the function name and its input parameters within the query. For example, if you have a function called calculate_tax that accepts a salary as input, you can use it in a SQL query like this:

      SELECT employee_id, first_name, last_name, salary, calculate_tax(salary) AS tax
FROM employees;

      What is the difference between a procedure and a function in PL/SQL?

      The main differences between procedures and functions in PL/SQL are:

      1. Functions must return a value, while procedures do not.
      2. Functions can be used directly within SQL statements, while procedures cannot.

      How do I handle exceptions in PL/SQL?

      To handle exceptions in PL/SQL, include an EXCEPTION section in your PL/SQL block, followed by one or more WHEN clauses to handle specific exceptions. You can use predefined exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS, or ZERO_DIVIDE, or create custom exceptions with the DECLARE statement and RAISE_APPLICATION_ERROR procedure. For example:

      DECLARE
  e_invalid_age EXCEPTION;
BEGIN
  -- Your code here
  IF age < 0 THEN
    RAISE e_invalid_age;
  END IF;
EXCEPTION
  WHEN e_invalid_age THEN
    dbms_output.put_line('Error: Age cannot be negative.');
END;

Sign up for the Artisan Beta

Help us reimagine WordPress.

Whether you’re a smaller site seeking to optimize performance, or mid-market/enterprise buildinging out a secure WordPress architecture – we’ve got you covered. 

We care about the protection of your data. Read our Privacy Policy.