All database activity is conducted in SQL, and therefore database administrators need to be intimately familiar with it. Figure 2-1 illustrates that the greatest potential for performance improvement usually lies within the software application, not within the database where the application stores its data or within the physical infrastructure where the database is housed.¹

Figure 2-1. The performance improvement pyramid

A common example of performance problems caused by applications is that the applications aren’t designed to take advantage of indexing schemes. As an example of a performance problem that you might encounter in SQL, consider the query in Listing 2-1, which retrieves the names of employees with first_name “Steven” and last_name “King”. An index exists on the first_name and last_name columns of the employees table, but the query can’t use the index because it uses the upper function in the restriction. Because Oracle Database can’t use the index, it has to retrieve and examine every single record in the employees table.²

SELECT first_name, last_name
  FROM employees
 WHERE upper(first_name) = ’STEVEN’
   AND upper(last_name) = ’KING’;

Another source of performance problems is the temptation to use the Oracle Database engine as a calculator to perform arithmetic calculations and string manipulations. This is a highly inefficient use of SQL and is better done using math and string libraries instead. However, it is usually fine to issue a simple query against dual such as in Listing 2-2 that executes just one time.

select to_char(sysdate, ’DAY’) from dual;

An equally important reason why database administrators need to understand SQL is that all database administration activities such as database maintenance and user management are also conducted in SQL. It should come as no surprise, therefore, that the Oracle Database 12c SQL Language Reference is almost 2,000 pages—compare this with the 20 or so pages in this chapter. Fortunately, you can go online and search the SQL manual or download the electronic version free of charge.³ You can purchase a printed copy if you like having a mountain of paper on your desk.

A third reason why database administrators need to understand SQL is that it has deficiencies that must be guarded against. These deficiencies include redundancy, problems introduced by nullable data items, and the absence of prohibitions on duplicate data records. I return to this subject later in this chapter.

Railroad Diagrams

SQL statements have many optional clauses, so the Oracle reference manuals use railroad diagrams as a visual aid. Figure 2-2 is an example of a railroad diagram for a hypothetical ROW command.

Figure 2-2. A railroad diagram for a hypothetical ROW command

Start at the left of the diagram, and take any path the diagram allows you to take. Along the way, you encounter mandatory words and clauses as well as optional words and clauses and even subdiagrams. The diagram indicates that the ROW command can take the forms indicated in Listing 2-3.

ROW YOUR BOAT
ROW, ROW YOUR BOAT
ROW, ROW, ROW YOUR CORACLE
ROW, ROW, ROW, ROW YOUR PIROGUE
ROW, ROW, ROW, ROW, ROW YOUR LARGE CORACLE

Let’s look at some railroad diagrams for a simplified version of the SELECT statement. The SELECT statement starts with the keyword SELECT and is used to retrieve data from a relational database. What you see here is a very small subset of the complete syntax, but it’s powerful enough for many purposes.

The diagrams in Figures 2-3 through 2-8 show that the keyword SELECT is followed a number of clauses: the mandatory SELECT_list and FROM_clause and the optional WHERE_clause, GROUP_BY_clause, and ORDER_BY_clause. Separate railroad diagrams are also shown for each of the clauses.

Figure 2-3. A simplified railroad diagram for the SELECT statement

Figure 2-4. The railroad diagram for SELECT_list

Figure 2-5. The railroad diagram for FROM_clause

Figure 2-6. The railroad diagram for WHERE_clause

Figure 2-7. The railroad diagram for GROUP_BY_clause

Figure 2-8. The railroad diagram for ORDER_BY_clause

As an example, let’s generate a report of the total salary in each department, sorted by descending order of average salary. Let’s assemble the necessary clauses.

The SELECT_list clause is a comma-separated list of column names or expressions in which you’re interested. Here’s what you need. The upper function produces the uppercase version of a character string, and the avg function produces the average of values in a group:

SELECT upper(department_name),
  AVG(salary)

The WHERE_clause portion of the statement is a list of tables that must be joined. You need the departments table and the employees table. You give each table a short alias that will come in handy in other clauses:

FROM departments d, 
  employees e

The optional GROUP_BY_clause portion of the statement tells Oracle Database that you apply aggregate functions such as avg to groups of records. In this case, you need to group by upper(department_name):

GROUP BY upper(department_name)

Finally, the ORDER_BY_clause portion of the statement specifies how you want the report sorted. In this case, you need to sort by descending values of AVG(salary):

ORDER BY AVG(salary) DESC;

Let’s put this all together:

SELECT upper(department_name),
  AVG(salary)
FROM departments d,
  employees e
WHERE e.department_id = d.department_id
GROUP BY upper(department_name)
ORDER BY AVG(salary) DESC;

And here is the result:

UPPER(DEPARTMENT_NAME)         AVG(SALARY)
------------------------------ -----------
EXECUTIVE                       19333.3333
ACCOUNTING                           10150
PUBLIC RELATIONS                     10000
MARKETING                             9500
SALES                           8955.88235
FINANCE                               8600
HUMAN RESOURCES                       6500
IT                                    5760
ADMINISTRATION                        4400
PURCHASING                            4150
SHIPPING                        3475.55556

Notice the formatting of the results. Formatting instructions aren’t part of the SQL query; the formatting is left to the application program—SQL*Plus in this case—that sent the SQL query to the Oracle Database engine for processing.

Now take a minute and check out the complete syntax of the SELECT statement in the SQL reference manual. Notice the subquery diagram in particular and how it references itself; just before the ORDER BY clause, you can optionally specify a UNION, INTERSECT, or MINUS clause followed by another subquery. Also observe that the subquery diagram is referenced by other diagrams. For example:

• If you check the Subquery Factoring Clause diagram, you see that it too refers to the subquery diagram.
• If you check the Table Reference subdiagram, you see that a table reference can include a subquery. This kind of subquery is called an inline view.
• Subqueries that produce a single value can be used any place where a single value is indicated. This kind of subquery is called a scalar subquery. For example, scalar subqueries can be used in a SELECT list and in a WHERE clause.

Types of SQL

Only a small fraction of the SQL reference manual is devoted to the sort of SQL statements you’ve encountered so far. SQL statements are commonly classified into Database Manipulation Language (DML) statements to modify data and Database Definition Language (DDL) statements to create and modify the different types of objects that compose an Oracle database. The SQL reference manual also describes commands that can create and modify databases and perform database administration activities such as stopping and starting databases.

Data Definition Language

A large portion of the SQL reference manual is devoted to DDL: commands that are used to create, alter, and drop different types of database objects such as tables and indexes. Listing 2-4 shows the DDL commands that can be used to create the employees table in the HR schema. These commands specify a data type such as variable character (VARCHAR2) or numeric for each data item, the length of each data item, and, in the case of numeric data items, the precision. For example, VARCHAR2(32) indicates no more than 32 data characters, and NUMBER(8, 2) indicates a decimal number with no more than 8 digits, 2 of which are to the right of the decimal point. Some of the columns are specified to be NOT NULL, and you also have check, unique-key, and primary-key constraints.

Using SQL Developer, you can review the DDL-creation commands of other tables in the HR schema. Simply go to the SQL tab for each table.

CREATE TABLE employees
  (
    employee_id    NUMBER(6,0),
    first_name     VARCHAR2(20),
    last_name      VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL enable,
    email          VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL enable,
    phone_number   VARCHAR2(20),
    hire_date      DATE CONSTRAINT emp_hire_date_nn NOT NULL enable,
    job_id         VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL enable,
    salary         NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id     NUMBER(6,0),
    department_id  NUMBER(4,0),
    CONSTRAINT emp_salary_min CHECK (salary > 0) enable,
    CONSTRAINT emp_email_uk UNIQUE (email) enable,
    CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id) enable,
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
      REFERENCES departments (department_id) enable,
    CONSTRAINT emp_job_fk FOREIGN KEY (job_id)
      REFERENCES jobs (job_id) enable,
    CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id)
      REFERENCES employees (employee_id) enable
  )

Database Manipulation Language

You’ve already seen several examples of the SELECT statement. Next, let’s consider the INSERT, UPDATE, DELETE, and MERGE statements.

The INSERT Statement

The INSERT statement is the way in which data enters the relational database. In Listing 2-5, a new record is being added to the employees table. However, if you were to try this command using SQL Developer, it would fail because the employee_id column is the primary-key column and the value 100 is already in use. Also, values in the email column are required to be unique, and the value “SKING” is already in use. Try this statement using SQL Developer, and observe the error message. Then change the value to one that will succeed.

INSERT
INTO employees
  (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
  )
  VALUES
  (
    100,
    ’Steven’,
    ’King’,
    ’SKING’,
    ’515.123.4567’,
    TO_DATE(’17-JUN-1987’, ’dd-MON-yyyy’),
    ’AD_PRES’,
    24000,
    NULL,
    NULL,
    90
  );

The UPDATE Statement

The UPDATE statement enables you to specify a subset of data rows and instructions for modifying them. In Listing 2-6, the e-mail address of an employee is being changed. However, if you were to try this command using SQL Developer, it would fail because the values in the email column are required to be unique and the value “NKOCHAR” is already in use. Try this statement using SQL Developer, and observe the error message. Then change the value “NKOCHAR” to one that will succeed.

UPDATE employees SET email=’NKOCHHAR’ WHERE employee_id=100;

The DELETE Statement

The DELETE statement lets you specify a subset of data rows to delete. In Listing 2-7, an employee record is being deleted. However, if you were to try this statement using SQL Developer, it would fail, because the employee_id column is referenced by a foreign-key constraint specifying that the value in the manager_id column must be a valid employee_id (or be left unspecified). The employee_id column is also referenced by a foreign-key constraint on the job_history table, which specifies that the values in the employee_id column should all be valid. Try this statement using SQL Developer, and observe the error message. This statement won’t succeed as long as there any rows for which the manager_id column has the value 101.

DELETE FROM employees WHERE employee_id = 101;

The MERGE Statement

MERGE is a powerful statement that combines the capabilities of the INSERT statement, the UPDATE statement, and the DELETE statement. Consider the following example. Suppose you have employee updates in a table called employee_updates. If a record contains an employee_id that already exists in the table, then the corresponding record in the employees table has to be updated. If a record contains an employee_id that doesn’t exist in the table, then the values must be inserted into the employees table. Finally, if the terminated column has value 1, then the corresponding record has to be deleted from the database. You can merge the contents of the employee_updates table into the employees table by using the MERGE statement—inserting, updating, or deleting records as necessary—as shown in Listing 2-8.

MERGE INTO employees e USING employee_updates u ON (
  e.employee_id = u.employee_id)
WHEN matched THEN
  UPDATE
  SET last_name    = u.last_name,
    email          = u.email,
    phone_number   = u.phone_number,
    hire_date      = u.hire_date,
    job_id         = u.job_id,
    salary         = u.salary,
    commission_pct = u.commission_pct,
    manager_id     = u.manager_id,
    department_id  = u.department_id
  DELETE WHERE terminated = 1
  WHEN NOT matched THEN
  INSERT
    (
      employee_id,
      first_name,
      last_name,
      email,
      phone_number,
      hire_date,
      job_id,
      salary,
      commission_pct,
      manager_id,
      department_id
    )
    VALUES
    (
      u.employee_id,
      u.first_name,
      u.last_name,
      u.email,
      u.phone_number,
      u.hire_date,
      u.job_id,
      u.salary,
      u.commission_pct,
      u.manager_id,
      u.department_id
    );

Embedded SQL

Application programs written by application software developers can communicate with an Oracle database only by using SQL. These application programs must therefore be linked with Oracle-supplied routines that give them the capability to communicate with an Oracle database.

Listing 2-9 shows an example of SQL statements embedded in a Java program. Each embedded SQL statement is prefixed with the phrase #sql (refer to the text in boldface in the example). The example lists the names of all parts supplied by a specified supplier and the price quote in each case; the account name and password required to connect to the database must be provided by the user at runtime.

import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;

public class PrintQuote
{
  public static void main(String[] args)
  {
    Connection connection = null;

    #sql iterator quote_iterator (
      String partname,
      double quote);

    quote_iterator quote = null;

    try
    {

      // Connect to the database
      // The account name and password are provided to the program by the user

      DriverManager.registerDriver(
        new oracle.jdbc.driver.OracleDriver());
      connection = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:ORCL",
        args[0],
        args[1]);
      DefaultContext.setDefaultContext(
        new DefaultContext(connection));

      // Retrieve the data for one supplier
      // The supplier’s name is provided to the program by the user

      String suppliername = args[2];

      #sql quote = {
        SELECT
          upper(department_name) AS department_name,
          AVG(salary) AS average_salary
        FROM
          departments d,
          employees e
        WHERE e.department_id = d.department_id
        GROUP BY upper(department_name)
        ORDER BY AVG(salary) DESC
      };

      // Print one row of data on each line

      while (quote.next()) {
        System.out.printf(
          "%-40s%10.2f
",
          quote.department_name(),
          quote.average_salary());
      }

      connection.close();

    }
    catch (SQLException exception) {
      exception.printStackTrace();
    }
  }
}

SQL*Plus and SQL Developer

Oracle Database provides a command-line tool called SQL*Plus that enables you to interact with the database without having to embed SQL in an application program; this is the tool that database administrators most frequently use in their work besides SQL Developer. The advantage of a command-line tool such as SQL*Plus is that it can be used to automate tasks—that is, a series of SQL statements can be placed in a file and automatically executed at prescribed times.

The SQL*Plus utility can also be used as a simple report-writing tool to produce neatly formatted reports. The commands in Listing 2-10 cause the entire employees table to be listed. The average salary is also printed for each department. Listing 2-11 shows the output produced by the commands in Listing 2-10.

-- Print page titles and a closing line
SET pagesize 66
SET linesize 74
TTITLE center "SALARY LISTING" RIGHT "Page:" sql.pno skip 2
REPFOOTER skip 1 center "END OF REPORT"

-- Print suitable titles for each column of information
COLUMN deartment_name format a30 heading "Department Name"
COLUMN employee_name format a30 heading "Employee Name"
COLUMN salary format 99999990.00 heading "Salary"

-- Print the department name only once and skip a line when the supplier name changes
BREAK ON department_name skip 1

-- Print the number of parts supplied by each supplier
COMPUTE AVG OF salary ON department_name

-- Print all information in the quote table
SELECT upper(department_name) AS department_name,
  upper(last_name || ’, ’ ||first_name) AS employee_name,
  salary
FROM departments d,
  employees e
WHERE e.department_id = d.department_id
ORDER BY department_name,
  employee_name;

SALARY LISTING      Page: 1

DEPARTMENT_NAME                Employee Name                        Salary
------------------------------ ------------------------------ ------------
ACCOUNTING                     GIETZ, WILLIAM                      8300.00
                               HIGGINS, SHELLEY                   12000.00
******************************                                ------------
Avg                                                               10150.00

ADMINISTRATION                 WHALEN, JENNIFER                    4400.00
******************************                                ------------
Avg                                                                4400.00

EXECUTIVE                      DE HAAN, LEX                       17000.00
                               KING, STEVEN                       24000.00
                               KOCHHAR, NEENA                     17000.00
******************************                                ------------
Avg                                                               19333.33

Criticisms of SQL

The IBM team that developed SQL made certain decisions that violated relational principles—chief among them that duplicate rows were allowed. The inventor of relational database technology, Dr. Edgar Codd, was not part of IBM’s development team, and, despite his urging, these deficiencies were not corrected in subsequent revisions of the language. In the opinion of some commentators, the gap then continued to widen. Nevertheless, no one has been successful in providing an alternative to SQL, and you must learn to live with its deficiencies. Chris Date makes the point forcefully in An Introduction to Database Systems:

Michael Stonebraker makes the point that SQL is the intergalactic language. Everybody in the galaxy speaks SQL, so, like it or not, we have to be masters of it.

Duplicates

The SQL standard allows tables to contain duplicate data records and doesn’t require that duplicates be eliminated from the results of a projection or union operation, thus violating the principles laid down by Codd. Date has published an example in which 12 formulations of a certain SQL query returned 9 different results because duplicate data rows existed in the tables in question. Not only does this become a programming nightmare, but Date points out that this is one reason why the query optimizer can’t always rewrite queries into alternative forms that are more efficient—the optimizer can’t be sure the result will be unaffected by the rewrite.

Redundancy

Redundancy isn’t a violation of relational principles but creates a serious performance problem that database administrators must understand. There are usually many ways to rephrase the same SQL query. For various reasons (such as the one described in the previous section), the query optimizer might not find the optimal query execution plan in all cases even though all the plans retrieve the same data and, therefore, the query plan that is optimal in one case is equally optimal in all other cases.

Listing 2-12 shows lots of different ways to express the query Departments that have at least one employee. Only the first version conforms to the simplified railroad diagram in Figure 2-3 and is obviously an abbreviated way of specifying the application of three separate relational algebra operators: join, restriction, and projection.

SELECT DISTINCT d.department_name
FROM departments d,
  employees e
WHERE e.department_id = d.department_id
AND e.job_id          = ’SH_CLERK’;

SELECT DISTINCT d.department_name
FROM departments d NATURAL
JOIN employees e
WHERE e.job_id = ’SH_CLERK’;

SELECT DISTINCT d.department_name
FROM departments d
JOIN employees e USING (department_id)
WHERE e.job_id = ’SH_CLERK’;

SELECT DISTINCT d.department_name
FROM departments d
JOIN employees e
ON (e.department_id = d.department_id)
WHERE e.job_id      = ’SH_CLERK’;

SELECT department_name
FROM departments
WHERE department_id IN
  ( SELECT department_id FROM employees WHERE job_id = ’SH_CLERK’
  );

SELECT department_name
FROM departments d
WHERE department_id = ANY
  ( SELECT department_id FROM employees e WHERE job_id = ’SH_CLERK’
  );

SELECT department_name
FROM departments d
WHERE ’SH_CLERK’ IN
  ( SELECT job_id FROM employees e WHERE e.department_id = d.department_id
  );

SELECT department_name FROM departments
MINUS
SELECT department_name
FROM departments d
WHERE ’SH_CLERK’ NOT IN
  ( SELECT job_id FROM employees e WHERE e.department_id = d.department_id
  );

SELECT d.department_name
FROM departments d
WHERE ’SH_CLERK’ = ANY
  ( SELECT job_id FROM employees WHERE department_id = d.department_id
  );

SELECT d.department_name
FROM departments d
WHERE EXISTS
  (SELECT *
  FROM employees
  WHERE department_id = d.department_id
  AND job_id          = ’SH_CLERK’
  );

SELECT department_name FROM departments
MINUS
SELECT department_name
FROM departments d
WHERE NOT EXISTS
  (SELECT *
  FROM employees
  WHERE department_id = d.department_id
  AND job_id          = ’SH_CLERK’
  );

SELECT department_name
FROM departments d
WHERE (SELECT COUNT(*)
  FROM employees
  WHERE department_id = d.department_id
  AND job_id          = ’SH_CLERK’ ) > 0;

SELECT DISTINCT d.department_name
FROM departments d
LEFT JOIN employees e
ON (e.department_id = d.department_id)
WHERE e.job_id      = ’SH_CLERK’;

SELECT DISTINCT d.department_name
FROM departments d,
  lateral
  (SELECT 1
  FROM employees e
  WHERE e.department_id = d.department_id
  AND e.job_id          = ’SH_CLERK’
  );

In Chapter 1, you saw the SQL answer to the problem Which employees have worked in all accounting positions; that is, those for which the job_id starts with the characters AC? Listing 2-13 shows two alternative formulations: the first formulation uses correlated subqueries, and the second formulation uses aggregate functions.

SELECT employee_id
FROM employees e
WHERE NOT EXISTS
  (SELECT *
  FROM jobs j
  WHERE job_id LIKE ’AC%’
  AND NOT ( e.job_id = j.job_id
  OR EXISTS
    (SELECT *
    FROM job_history
    WHERE employee_id = e.employee_id
    AND job_id        = j.job_id
    ) )
  );

SELECT employee_id
FROM
  ( SELECT employee_id, job_id FROM job_history WHERE job_id LIKE ’AC%’
  UNION
  SELECT employee_id, job_id FROM employees WHERE job_id LIKE ’AC%’
  )
GROUP BY employee_id
HAVING COUNT(DISTINCT job_id) =
  (SELECT COUNT(*) FROM jobs WHERE job_id LIKE ’AC%’
  );

Nullable Data Items

Sometimes the value of a data item isn’t known. If you permit a record to be stored even if the values of some data items aren’t known, those data items are said to be nullable. Missing information leads to “fuzzy” logic in which there is a third alternative—unknown—to truth and falsehood of a statement such as department_id=90. Nullable data items are commonly used by database designers, but three-valued logic isn’t intuitive. In the employees table, manager_id is a nullable column. Intuitively, the following queries should return the value 107—the number of rows in the employees table—but instead they return the value 106 because one record doesn’t contain a value for department_id:

SELECT COUNT(*) FROM employees WHERE manager_id = 100 OR manager_id != 100;
SELECT COUNT(*) FROM employees WHERE manager_id = manager_id;
SELECT COUNT(*) FROM employees WHERE manager_id != -1;

To obtain the expected answer, you need to add the clause OR manager_id IS NULL to the preceding SQL queries; you can find more information about the problems created by nullable data items in any good book on SQL.

Introduction to PL/SQL

Oracle Database gives you the ability to store programs in the database using a language called Procedural Language/SQL (PL/SQL). PL/SQL offers the entire suite of structured programming mechanisms, such as condition checking, loops, and subroutines, as shown in Figure 2-9. Just like programs written in other languages, PL/SQL programs use SQL to interact with the database.

Figure 2-9. Programming patterns provided by all programming languages

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 2-10 diagrams the logic of a PL/SQL program to merge the contents of the employee_updates table with the employees table. This is not the way you should do things, because the merge command is available, but the approach in the figure nicely illustrates the capabilities of PL/SQL. The program corresponding to Figure 2-10 is shown in Listing 2-14. It uses a cursor, which is a mechanism that allows you to iterate through the rows of data returned by a SQL query.

Figure 2-10. Flow chart for the employee_updates procedure

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;

Storing programs in the database has many advantages. 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. Storing sequences of commands in the database greatly reduces the amount of communication between client and server and improves efficiency. Also, PL/SQL functions can be used in SQL statements; this increases the power and flexibility of SQL.

Summary

Database administrators need to understand SQL in all its forms. Many Oracle experts have devoted their entire careers to the study of SQL, and I have hardly been able to scratch the surface in this chapter. I highly recommend the books listed at the end of this chapter; and, of course, you can always download the official Oracle reference works from the Oracle web site. Here is a short summary of the concepts discussed in this chapter:

• All database activity, including database administration activities, is transacted in SQL.
• Oracle reference works use railroad diagrams to teach the SQL language. Railroad diagrams can include subdiagrams and can even refer to themselves in recursive fashion. For instance, a table reference can be an entire subquery—this kind of subquery is called an inline view. The SELECT list can include scalar subqueries—that is, subqueries that return exactly one data item from exactly one data row.
• SQL is divided into Data Manipulation Language (DML) and Data Definition Language (DDL). DML includes the SELECT, INSERT, UPDATE, MERGE, and DELETE statements. DDL includes the CREATE, ALTER, and DROP statements for the different classes of objects in an Oracle database. The SQL reference manual also describes commands that can be used to perform database administration activities such as stopping and starting databases.
• SQL needs to be embedded into software application programs so they can communicate with the database.
• SQL has been criticized because it doesn’t prohibit duplicates; the absence of a prohibition against duplicates causes queries that are seemingly equivalent to produce differing results and inhibits the query optimization process.
• Using nullable data items can lead to results that contradict common sense.
• SQL queries can usually be formulated in several ways. The Oracle Database optimizer may not always choose the same query execution plan in all cases, even though the query plan that is most efficient in one case is obviously the most efficient for all other cases.
• Programs written in PL/SQL can be stored in an Oracle database. Using these programs has many advantages, including efficiency, control, and flexibility. PL/SQL offers a full complement of structured programming mechanisms such as condition checking, loops, and subroutines.

Exercises

• Download Oracle Database 12c SQL Reference from http://docs.oracle.com. Review the railroad diagram for the SELECT statement and all the subdiagrams.
• Using SQL Developer, try the INSERT, UPDATE, and DELETE statements in Listing 2-5, Listing 2-6, and Listing 2-7. Construct a solution in each case.
• Use SQL Developer to perform the following tasks:
  1. Create an employee_updates table by cloning the employees table.
  2. Add a terminated column to the employee_updates table.
  3. Insert records into the employee_updates table.
  4. Merge the data in the employee_updates table into the employees table using the MERGE command.

Further Reading

• De Haan, Lex, and Tim Gorman. Beginning Oracle SQL: For Oracle Database 12c. Apress, 2009. The work of the late Lex De Haan, a well-known Oracle expert and teacher has been revised for Oracle Database 12c by Tim Gorman, an Oracle ACE Director and a member of the OakTable network.
• Feuerstein, Steven. Oracle Magazine. www.oracle.com/technetwork/issue-archive/index-087690.html. A 12-part series on PL/SQL starting in the March/April 2011 issue and ending in the March/April 2013 issue. Part 2, “Controlling the Flow of Execution,” and Part 12, “Working with Cursors,” are especially relevant to the PL/SQL example in this chapter.
• Feuerstein, Steven, and Bill Pribyl. Oracle PL/SQL Programming. O’Reilly, 2014. Steven Feuerstein is the world’s top expert in PL/SQL and has a highly readable style. If you’re planning to invest in a PL/SQL book, I recommend this one.
• Gennick, Jonathan. Oracle SQL*Plus: The Definitive Guide. O’Reilly, 2009. Oracle reference works are good at documenting every detail of a product but not as good at describing how to use the product to maximum advantage. This book will help you do that.
• McJones, Paul. “The 1995 SQL Reunion: People, Projects, and Politics.” www.hpl.hp.com/techreports/Compaq-DEC/SRC-TN-1997-018.pdf.

Footnotes

¹Based on a discussion in Oracle Rdb Guide to Database Performance and Tuning.

²It’s possible to create a function-based index for the upper(first_name) and upper(last_name) columns.

³The URL is http://docs.oracle.com.

⁴Such a query is called a Cartesian product.