The Magic of PL/SQL

Do you know when to anchor, when to loop, and when not to make?

By Steven Feuerstein

September/October 2016

As Bryn Llewellyn writes in his “Why Use PL/SQL” white paper, “Large software systems must be built from modules. A module hides its implementation behind an interface that exposes its functionality. This is computer science’s most famous principle. For applications that use Oracle Database, the database is the persistence module. The tables and the SQL statements that manipulate them are the implementation details. The interface is expressed with PL/SQL.” (I encourage you to read Bryn’s entire white paper to fully appreciate—and then leverage—the PL/SQL language.)

To help users of PL/SQL build the cleanest, fastest interfaces, Oracle has built several wonderful, and often deceptively simple, features into the language. This article explores a few of these PL/SQL wonders.

I execute the following statements:

CREATE TABLE plch_trees
(
   id              INTEGER,
   tree_name       VARCHAR2 (6),
   tree_location   VARCHAR2 (20)
)
/
BEGIN
   INSERT INTO plch_trees (id, tree_name, tree_location)
        VALUES (300, 'Ginkgo', 'China');
   COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE plch_show_tree1 (id_in IN INTEGER)
   AUTHID DEFINER
IS
   l_tree_name   VARCHAR2 (6);
BEGIN
   SELECT tree_name
     INTO l_tree_name
     FROM plch_trees
    WHERE id = id_in;
   DBMS_OUTPUT.put_line (l_tree_name);
END;
/
CREATE OR REPLACE PROCEDURE plch_show_tree2 (id_in IN INTEGER)
   AUTHID DEFINER
IS
   l_tree_name   plch_trees.tree_name%TYPE;
BEGIN
   SELECT tree_name
     INTO l_tree_name
     FROM plch_trees
    WHERE id = id_in;
   DBMS_OUTPUT.put_line (l_tree_name);
END;
/
CREATE OR REPLACE PROCEDURE plch_show_tree3 (id_in IN INTEGER)
   AUTHID DEFINER
IS
   l_tree   plch_trees%ROWTYPE;
BEGIN
   SELECT tree_name
     INTO l_tree.tree_name
     FROM plch_trees
    WHERE id = id_in;
   DBMS_OUTPUT.put_line (l_tree.tree_name);
END;
/
CREATE OR REPLACE PROCEDURE plch_change_table
   AUTHID DEFINER
IS
BEGIN
   EXECUTE IMMEDIATE 'alter table plch_trees modify tree_name varchar2(10)';
   EXECUTE IMMEDIATE
      q'[
   BEGIN
      INSERT INTO plch_trees (id, tree_name, tree_location)
           VALUES (100, 'Black Oak', 'Eastern US');
      INSERT INTO plch_trees (id, tree_name, tree_location)
           VALUES (200, 'Tamarack', 'Europe');
      COMMIT;
   END;]';
END;
/

Which of the following choices display “Ginkgo” followed by “Black Oak” after execution?

a.

BEGIN
   plch_show_tree1 (300);
   plch_change_table;
END;
/
BEGIN
   plch_show_tree1 (100);
END;
/

b.

BEGIN
   plch_show_tree2 (300);
   plch_change_table;
END;
/
BEGIN
   plch_show_tree2 (100);
END;
/

c.

BEGIN
   plch_show_tree3 (300);
   plch_change_table;
END;
/
BEGIN
   plch_show_tree3 (100);
END;
/

PL/SQL supports anchored declarations, using %TYPE and %ROWTYPE. This scheme lets PL/SQL directly express the intention that a variable or formal parameter has the same datatype as a table column. Using anchored declarations ensures code clarity, reliability, and maintainability.

The magic of anchored attributes is simple: when the table or column to which the variable or constant is anchored changes, the variable’s program unit is invalidated. It will then be automatically recompiled when next used. And at the time of recompilation, the changed definition of the table or column will be used.

In addition, when you use anchored declarations, your code tells a clearer story to anyone who comes along later to maintain your code. You are stating explicitly what kind of value that variable or constant is intended to hold.

Read more about % TYPE.

Read more about %ROWTYPE.

I create and populate the following table:

CREATE TABLE plch_parts
(
   partnum    INTEGER
 , partname   VARCHAR2 (100)
)
/
BEGIN
   INSERT INTO plch_parts VALUES (1, 'Mouse');
   INSERT INTO plch_parts VALUES (100, 'Keyboard');
   INSERT INTO plch_parts VALUES (500, 'Monitor');
   COMMIT;
END;
/

Which of the following choices display the names of all three parts in the table?

a.

DECLARE
   CURSOR plch_parts_cur
   IS
      SELECT * FROM plch_parts;
   rec   plch_parts_cur%ROWTYPE;
BEGIN
   OPEN plch_parts_cur;
   LOOP
      FETCH plch_parts_cur INTO rec;
      EXIT WHEN plch_parts_cur%NOTFOUND;
      DBMS_OUTPUT.put_line (rec.partname);
   END LOOP;
   CLOSE plch_parts_cur;
END;
/

b.

DECLARE
   CURSOR plch_parts_cur
   IS
      SELECT * FROM plch_parts;
BEGIN
   FOR rec IN plch_parts_cur
   LOOP
      DBMS_OUTPUT.put_line (rec.partname);
   END LOOP;
END;
/

c.

DECLARE
   CURSOR plch_parts_cur
   IS
      SELECT * FROM plch_parts;
   TYPE plch_parts_t IS TABLE OF plch_parts_cur%ROWTYPE;
   l_parts   plch_parts_t;
BEGIN
   SELECT *
     BULK COLLECT INTO l_parts
     FROM plch_parts;
   FOR indx IN 1 .. l_parts.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_parts (indx).partname);
   END LOOP;
END;
/

d.

BEGIN
   FOR rec IN (SELECT * FROM plch_parts)
   LOOP
      DBMS_OUTPUT.put_line (rec.partname);
   END LOOP;
END;
/

The cursor FOR loop is just about my favorite feature of PL/SQL. It’s a great demonstration of how thoroughly Oracle adapted Ada (the language on which PL/SQL was based) to the specialized world of database programming.

There’s no need to open, fetch, and close. Just tell the PL/SQL engine you want to work with each row returned by the query. Plus, with your optimization level set to 2 (the default) or higher, this code is automatically optimized to return 100 rows with each fetch (resulting in performance similar to an explicit BULK COLLECT fetch).

You can embed the SELECT statement within the loop header, or you can declare an explicit cursor and reference it by name in the loop header. That means you can, again, parameterize the cursor and reuse it in multiple loops.

Keep the following in mind with cursor FOR loops:

• Never use a cursor FOR loop to fetch a single row.
• It’s a lazy way to avoid declaring the INTO variable or record, and that’s bad laziness.
• If you need to iterate through rows of data but then conditionally exit the loop under certain data conditions, use a WHILE or a simple loop with an explicit cursor. Why? Because . . .
• Any kind of FOR loop is saying, implicitly, “I am going to execute the loop body for all iterations defined by the loop header” (N through M or SELECT). Conditional exits mean the loop could terminate in multiple ways, resulting in code that is hard to read and maintain.

Read more about the cursor FOR loop.

I create and populate the following table:

CREATE TABLE plch_employees
(
   employee_id   INTEGER
 , last_name     VARCHAR2 (100)
 , salary        NUMBER
)
/
BEGIN
   INSERT INTO plch_employees
        VALUES (100, 'Jobs', 1000000);
   INSERT INTO plch_employees
        VALUES (200, 'Ellison', 1000000);
   INSERT INTO plch_employees
        VALUES (300, 'Gates', 1000000);
   COMMIT;
END;
/

I then create the following two procedures. The first, plch_show_status, shows the status of a stored program unit. The second retrieves the last_name value of an employee. Both compile successfully.

CREATE OR REPLACE PROCEDURE plch_show_status (NAME_IN IN VARCHAR2)
IS
   l_validity   user_objects.status%TYPE;
BEGIN
   SELECT status
     INTO l_validity
     FROM user_objects
    WHERE object_name = NAME_IN;
   DBMS_OUTPUT.put_line ('Status of ' || NAME_IN || ': ' ||
l_validity);
END plch_show_status;
/
CREATE OR REPLACE PROCEDURE use_employees
IS
   l_name   plch_employees.last_name%TYPE;
BEGIN
   SELECT e.last_name
     INTO l_name
     FROM plch_employees e
    WHERE e.employee_id = 100;
END use_employees;
/

I then run the following statements to show the status of USE_EMPLOYEES three times.

BEGIN
   plch_show_status ('USE_EMPLOYEES');
END;
/
ALTER TABLE plch_employees ADD first_name VARCHAR2(2000)
/
BEGIN
   plch_show_status ('USE_EMPLOYEES');
END;
/
ALTER TABLE plch_employees MODIFY last_name VARCHAR2(2000)
/
BEGIN
   plch_show_status ('USE_EMPLOYEES');
END;
/

Which of the following choices shows the status that will be displayed on the screen?

a.

Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: INVALID

b.

Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: INVALID
Status of USE_EMPLOYEES: INVALID

c.

Status of USE_EMPLOYEES: INVALID
Status of USE_EMPLOYEES: INVALID
Status of USE_EMPLOYEES: INVALID

d.

Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: VALID

In software development, make is a tool or process that automates the construction of executable programs from source code. It is widely used in UNIX and Linux systems around the world. But in the world of Oracle Database programming with PL/SQL, it is entirely unnecessary.

That’s because Oracle Database automatically keeps track of dependencies between database objects (tables, views, types, packages, procedures, and so on) based on static references to objects within PL/SQL source code. The ALL_DEPENDENCIES view provides users access to this dependency information.

This automatic dependency tracking leads to three magical aspects of PL/SQL:

• Oracle Database will automatically invalidate program units if any of the objects on which they depend are changed.
• Oracle Database will usually be able to silently recompile invalid program units, resolving all references to database objects according to the current state.
• Developers do not have to explicitly link program units or run any kind of make utility. Oracle Database takes care of all the details; program units are loaded for execution on demand.

Those of us working with PL/SQL tend to take all this wondrous behind-the-scenes activity for granted, and there’s no reason why we shouldn’t. But it’s also good to remind ourselves of all the ways that Oracle Database makes database programming so productive.

Read more about schema object dependency.