1.1 What Is PL/SQL?

PL/SQL stands for "Procedural Language extensions to SQL." PL/SQL is available primarily as an "enabling technology" within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side application development tools, such as Oracle Forms. PL/SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to this standard relational database language. As you can see from the following code example, PL/SQL allows you to combine SQL statements with "standard" procedural constructs. This single program can either insert a company into or delete a company from the database. It relies on the IF statement (not a SQL statement) to determine which action to take:

PROCEDURE maintain_company
   (action_in IN VARCHAR2,
    id_in IN NUMBER,
    name_in IN VARCHAR2 := NULL)
IS
BEGIN
   IF action_in = 'DELETE'
   THEN
      DELETE FROM company WHERE company_id = id_in;

   ELSIF action_in = 'INSERT'
   THEN
      INSERT INTO company (company_id, name)
      VALUES (id_in, name_in);
   END IF;
END;

PL/SQL is an unusual -- and an unusually powerful -- programming language. You can write programs that look just like traditional 3GL modules, but you can also include calls to SQL statements, manipulate data through cursors, and take advantage of some of the newest developments in programming languages. PL/SQL supports packages which allow you to perform object-oriented design. PL/SQL provides a powerful mechanism for trapping and, with exception handlers, resolving errors. The tight integration of PL/SQL with SQL provides developers with the best of both worlds -- declarative and procedural logic.

Figure 1.1 shows how PL/SQL fits within the client-server architecture of Oracle-based applications. It shows both an Oracle Forms client and a non-Oracle tool client, both executing against an Oracle Server database. Notice that the Oracle Forms client makes use of two versions of PL/SQL:

• PL/SQL Release 1.1: a client-side PL/SQL engine that allows the application to execute local PL/SQL programs

• PL/SQL Release 2.X: the server-based PL/SQL engine that executes stored programs

The third-party tool executes calls to stored programs, which are then run on the server.

Figure 1.1: PL/SQL within the Oracle client-server architecture

Because PL/SQL is used both in the database (for stored procedures and database triggers) and in the application code (to implement logic within a form, for example), you can leverage the same programming language for both client-side and server-side development. You can even move programs from one component of the configuration to another.[ 1 ] For example, you might decide that a function which was originally coded for a single screen could be shared by all of the modules in an application. To make this happen, you simply move that function from the client-side screen component to the server-side database environment. You have to change neither the PL/SQL code nor any of the programs that call that function.

[1] As long as there are no conflicts between different versions of PL/SQL.

Of course, as an underlying technology in the Oracle constellation of products, PL/SQL is just one element of the total programming experience. In fact, building an Oracle-based application requires a combination of technology and techniques, as you'll see in the next section.