In response to Tim Ford’s #EntryLevel Challenge.

Oracle Database gives you the ability to store programs in the database using a language called Procedural Language/SQL (PL/SQL). As the name indicates—and just like database programs written in other languages—PL/SQL programs use SQL statements to interact with your Oracle database. PL/SQL offers the programmer a complete suite of structured programming mechanisms. The three basic mechanisms—sequence, selection, and iteration—are illustrated in Figure 1.

Figure 1: Programming patterns provided by all programming languages

The definitions of the mechanisms illustrated in Figure 1 are recursive; that is, each  “step” in Figure 1 can itself be a sequence, selection, or iteration. This is illustrated in Figure 2.

Figure 2: Recursive use of the three basic mechanisms

A common use of PL/SQL is to write triggers. Triggers are tied to actions such as the action of inserting a record into a table. For example, the HR schema contains a trigger that inserts a history record into the job_history table whenever the job_id in the employees table is updated. This preserves the history of changes to an employee’s position.

Figure 3 shows the logic of a PL/SQL program to merge the contents of a table called employee_updates with the employees table. Notice the resemblance to Figure 2.  We are using a cursor, which is a mechanism that allows you to iterate through the rows of data returned by a SQL query. Figure 4 shows the corresponding PL/SQL program.

Figure 3: Logic of a PL/SQL program to merge the contents of the employee_updates table with the employees table

CREATE OR REPLACE
PROCEDURE employee_updates_proc
IS
  CURSOR l_cursor
  IS
    SELECT terminated,
      employee_id,
      first_name,
      last_name,
      email,
      phone_number,
      hire_date,
      job_id,
      salary,
      commission_pct,
      manager_id,
      department_id
    FROM employee_updates;
  l_record l_cursor%rowtype;
  l_count pls_integer;
BEGIN
  OPEN l_cursor;
  FETCH l_cursor INTO l_record;
  WHILE NOT l_cursor%notfound
  LOOP
    l_count := 0;
    SELECT COUNT(*)
    INTO l_count
    FROM employees
    WHERE employee_id        = l_record.employee_id;
    IF l_count               = 1 THEN
      IF l_record.terminated = 1 THEN
        DELETE FROM employees WHERE employee_id =
          l_record.employee_id;
      ELSE
        UPDATE employees
        SET last_name    = l_record.last_name,
          email          = l_record.email,
          phone_number   = l_record.phone_number,
          hire_date      = l_record.hire_date,
          job_id         = l_record.job_id,
          salary         = l_record.salary,
          commission_pct = l_record.commission_pct,
          manager_id     = l_record.manager_id,
          department_id  = l_record.department_id;
      END IF;
    ELSE
      INSERT
      INTO employees
        (
          employee_id,
          first_name,
          last_name,
          email,
          phone_number,
          hire_date,
          job_id,
          salary,
          commission_pct,
          manager_id,
          department_id
        )
        VALUES
        (
          l_record.employee_id,
          l_record.first_name,
          l_record.last_name,
          l_record.email,
          l_record.phone_number,
          l_record.hire_date,
          l_record.job_id,
          l_record.salary,
          l_record.commission_pct,
          l_record.manager_id,
          l_record.department_id
        );
    END IF;
    FETCH l_cursor INTO l_record;
  END LOOP;
  CLOSE l_cursor;
END;

Figure 4: PL/SQL program to merge the contents of the employee_updates table with the employees table

Storing programs in the database has many advantages. For example, they can be used to control what database users may or may not do. As another example, special PL/SQL programs called triggers can be executed whenever a user performs a specified action. This gives you the ability to enforce business rules, control access to data, and keep records of who accessed the data and how it changed. Further, storing sequences of commands in the database itself greatly improves efficiency by reducing the amount of client-server communication. Also, PL/SQL functions can be used in SQL statements; this increases the power and flexibility of SQL.

Quiz

The answers to the following questions may not be found in the above article. You may need to perform internet research to find the answers.

  1. In what sense is PL/SQL a more complete language than SQL?
  2. Is PL/SQL a standard language or a proprietary language?
  3. What is the  “thick database” paradigm?
  4. Can PL/SQL be used without storing the code inside the database?
  5. Implement and run the employee_updates_proc procedure in your database.
  6. How can you merge the contents of the employee_updates table into the employees table with SQL statements only (without using PL/SQL)?

Adapted from Beginning Oracle Database 12c Administration