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
FETCH l_cursor INTO l_record;
WHILE NOT l_cursor%notfound
l_count := 0;
WHERE employee_id = l_record.employee_id;
IF l_count = 1 THEN
IF l_record.terminated = 1 THEN
DELETE FROM employees WHERE employee_id =
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;
FETCH l_cursor INTO l_record;
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.
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.
Adapted from Beginning Oracle Database 12c Administration