How to Program with PL/SQL in Oracle 12c

By Chris Ruel, Michael Wessler

There are several different ways to work within your Oracle 12c database to manipulate your data. PL/SQL is a structured query language (SQL) with more powerful programmatic contructs built around your code. For example, PL/SQL offers

  • Looping control

  • Variables

  • If/then constructs

  • Error handling

Normal SQL really doesn’t have any of this. Normal SQL is good to use in code that acts on specific data in the “now.” It can’t make any data-driven decisions. You have to know what that data is — and how you want it to look.

PL/SQL is also more secure than regular SQL. As it stands, when users execute SQL, they have to have permissions on the underlying objects in which the data lives. However, with PL/SQL, named programs execute with the permissions of the owner.

That way, the owner of the data could write a program to manage the data. The owner then gives access to the program to the user, not the underlying objects. For example, say you have a program that pulls a user’s salary history for them to view.

You don’t want the user to be able to select on the employee salary table. And without a PL/SQL program, that’s what you’d have to do. You can code it so that when the program runs, the program pulls in the connected user as a variable and collects the salary history for that user only.

PL/SQL is often the primary domain of application developers. As a database administrator (DBA), however, you should also be familiar with the basic premises of the code and be able to read how the code functions. Even though DBAs may not be application developers, you will be called upon to help troubleshoot code or tune code that may be in the form of PL/SQL programs.

Basic types of PL/SQL programs in Oracle 12c

PL/SQL programs come in many forms. PL/SQL programs are also sometimes referred to as “program units.” Look below for a listing of the common types of PL/SQL constructs you’ll come across.

Name Description
ANONYMOUS BLOCK Un-named program that runs from the command line
PROCEDURE Stored, named program that performs a tasks
FUNCTION Program that takes input, acts upon it, and produces
output
PACKAGE Group of named procedures and/or functions which are related by
task
TRIGGER Program acts upon outcome of some other action; fires
automatically
Basics of PL/SQL block structure in Oracle 12c

PL/SQL programs are built on the block structure. That is, they can be broken down into specific parts of the program based on function. The parts of the PL/SQL block differ slightly based on the type of program unit, but they all have similar characteristics. Here is a breakdown of the parts of a PL/SQL program unit:

  • Declarative: This section contains the name of the unit (if it’s named) and any variables. The variables are named, typed, and optionally initialized in the section. The program unit would not be named if it is coded as an anonymous block. An anonymous block is used when you are often writing a program for a one-time use.

  • Body: This is the section that holds the meat of the program. It contains the functionality and the business logic needed to process the variables and data. You will see things like loops and if/then statements in this section.

  • Exception: This section defines and handles any errors that come up during the processing of the body. If an error is properly handled, often the program can continue running. Or, at the very least, output a meaningful message to the end user. If an error is encountered and is not handled by the exception section, often the program aborts with a default error message.

  • End: The end section doesn’t contain anything. It just signifies that the program is at the end of its processing. And in a package of many procedures, the end section separates it from the next procedure in the list.

How to call PL/SQL programs with Oracle 12c

PL/SQL procedures, functions and packages are called in a couple of different ways. You can use the EXECUTE command, or you can call the program as part of another block. Triggers, on the other hand, are not called from the command line.

They automatically execute only after some other process completes. For example, you might want a trigger to fire every time someone updates the salary column of the employees table. Then, perhaps that trigger shoots an e-mail to the HR manager to report the change.

The DESCRIBE command can also work against PL/SQL programs. This can prove helpful if you don’t know the arguments or variables that the procedure may take for input. For example, say you have a procedure that gets the salary for an employee based on first and last name input. The procedure is called get_sal.

SQL> DESCRIBE get_sal
PROCEDURE get_sal
 Argument Name         Type          In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_LAST_NAME          VARCHAR2        IN
 P_FIRST_NAME          VARCHAR2        IN

The procedure takes to IN arguments of VARCHAR2 type.

Here’s how you would execute the procedure with the EXECUTE command, using the employee Mike Whalen:

SQL> EXECUTE get_sal('Whalen','Mike')
Mike, Whalen - Makes: $8300
PL/SQL procedure successfully completed.

As mentioned earlier, Oracle has a plethora of pre-supplied packages, procedures, and functions for managing the database. To get a complete list, go to the following section of the documentation that outlines all the Oracle-supplied program units.

Here are some example Oracle-supplied programs.

Name Description
DBMS_SCHEDULER Manages the internal database scheduler
DBMS_STATS Gathers statistics on users, objects, system, and whole
database
SYSDATE Outputs current time and date of system
UTL_MAIL Utility for e-mail with features, such as attachments, Cc, and
Bcc
DBMS_METADATA Function for pulling object DDL out of database among other
tasks
DBMS_DATAPUMP API Manages Data Pump within a PL/SQL program