1 Overview of PL/SQL

Tight Integration with SQL

PL/SQL is tightly integrated with SQL, the most widely used database manipulation language:

• PL/SQL lets you use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns.

• PL/SQL fully supports SQL data types.

  You need not convert between PL/SQL and SQL data types. For example, if your PL/SQL program retrieves a value from a database column of the SQL type VARCHAR2, it can store that value in a PL/SQL variable of the type VARCHAR2.

  You can give a PL/SQL data item the data type of a column or row of a database table without explicitly specifying that data type (see "%TYPE Attribute" and "%ROWTYPE Attribute").

• PL/SQL lets you run a SQL query and process the rows of the result set one at a time (see "Processing a Query Result Set One Row at a Time").

PL/SQL supports both static and dynamic SQL. Static SQL is SQL whose full text is known at compilation time. Dynamic SQL is SQL whose full text is not known until run time. Dynamic SQL enables you to make your applications more flexible and versatile. For more information, see Chapter 6, "PL/SQL Static SQL" and Chapter 7, "PL/SQL Dynamic SQL".

High Performance

PL/SQL lets you send a block of statements to the database at once, significantly reducing traffic between the application and the database.

Bind Arguments

When you embed a SQL INSERT, UPDATE, DELETE, or SELECT statement directly in your PL/SQL code, the PL/SQL compiler turns the variables in the WHERE and VALUES clauses into bind arguments (for details, see "Resolution of Names in Static SQL Statements"). Oracle Database can reuse these SQL statements each time the same code runs, which improves performance.

PL/SQL does not create bind arguments automatically when you use dynamic SQL, but you can use them with dynamic SQL by specifying them explicitly (for details, see "EXECUTE IMMEDIATE Statement").

Subprograms

PL/SQL lets you write subprograms. Each subprogram is compiled once and stored in executable form, which can be invoked repeatedly. Because stored subprograms run in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored subprograms are cached and shared among users, which lowers memory requirements and call overhead. For more information about subprograms, see "Subprograms".

Optimizer

The PL/SQL compiler has an optimizer that can rearrange code for better performance. For more information about the optimizer, see "PL/SQL Optimizer".

High Productivity

PL/SQL lets you write compact code for manipulating data. Just as a scripting language like PERL can read, transform, and write data in files, PL/SQL can query, transform, and update data in a database.

PL/SQL has many features that save designing and debugging time, and it is the same in all environments. If you learn to use PL/SQL with one Oracle tool, you can transfer your knowledge to other Oracle tools. For example, you can create a PL/SQL block in SQL Developer and then use it in an Oracle Forms trigger. For an overview of PL/SQL features, see "Main Features of PL/SQL".

Portability

You can run PL/SQL applications on any operating system and platform where Oracle Database runs.

Scalability

PL/SQL stored subprograms increase scalability by centralizing application processing on the database server. The shared memory facilities of the shared server enable Oracle Database to support thousands of concurrent users on a single node. For more information about subprograms, see "Subprograms".

For further scalability, you can use Oracle Connection Manager to multiplex network connections. For information about Oracle Connection Manager, see Oracle Database Net Services Reference.

Manageability

PL/SQL stored subprograms increase manageability because you can maintain only one copy of a subprogram, on the database server, rather than one copy on each client system. Any number of applications can use the subprograms, and you can change the subprograms without affecting the applications that invoke them. For more information about subprograms, see "Subprograms".

Tight Security

PL/SQL stored subprograms let you divide application logic between the database server and the clients. You can restrict client access to server data by allowing users to manipulate the data only through stored subprograms that have restricted privileges. For example, you can grant users access to a subprogram that updates a table, but not grant them access to the table itself. For information about granting access to users, see Oracle Database SQL Language Reference.

PL/SQL database triggers can prevent applications from making specified updates and can audit user queries. For more information about triggers, see "Triggers".

You can wrap your PL/SQL source code to protect it from business competitors and others who might misuse it. For more information about wrapping, see Appendix A, "PL/SQL Source Code Wrapping."

Access to Predefined Packages

A package is a PL/SQL unit that consists of related subprograms and the explicit cursors and variables that they use. Packages are stored in the database, where they can be shared by many applications.

Oracle provides product-specific packages that define application program interfaces (APIs) you can invoke from your PL/SQL programs to perform many useful tasks. For information about some widely used predefined packages, see "Overview of Product-Specific Packages".

For more information about packages in general, see "Packages (APIs Written in PL/SQL)".

Support for Object-Oriented Programming

PL/SQL supports object-oriented programming with "Abstract Data Types".

Support for Developing Web Applications

PL/SQL lets you create applications that generate web pages directly from the database, allowing you to make your database available on the Web and make back-office data accessible on the intranet.

The program flow of a PL/SQL Web application is similar to that in a CGI PERL script. Developers often use CGI scripts to produce web pages dynamically, but such scripts are often not optimal for accessing the database. Delivering Web content with PL/SQL stored subprograms provides the power and flexibility of database processing. For example, you can use DML, dynamic SQL, and cursors. You also eliminate the process overhead of forking a new CGI process to handle each HTTP request.

You can implement a Web browser-based application entirely in PL/SQL with PL/SQL Gateway and the PL/SQL Web Toolkit.

PL/SQL Gateway enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener. mod_plsql, one implementation of the PL/SQL Gateway, is a plug-in of Oracle HTTP Server and enables Web browsers to invoke PL/SQL stored subprograms.

PL/SQL Web Toolkit is a set of PL/SQL packages that provides a generic interface to use stored subprograms invoked by mod_plsql at run time.

Support for Developing Server Pages

PL/SQL Server Pages (PSPs) let you develop web pages with dynamic content. PSPs are an alternative to coding a stored subprogram that writes the HTML code for a web page one line at a time.

Special tags let you embed PL/SQL scripts into HTML source code. The scripts run when Web clients, such as browsers, request the pages. A script can accept parameters, query or update the database, and then display a customized page showing the results.

During development, PSPs can act like templates, with a static part for page layout and a dynamic part for content. You can design the layouts using your favorite HTML authoring tools, leaving placeholders for the dynamic content. Then, you can write the PL/SQL scripts that generate the content. When finished, you simply load the resulting PSP files into the database as stored subprograms.

Blocks

The basic unit of a PL/SQL source program is the block, which groups related declarations and statements.

A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. A block can have a label.

Example 1-1 shows the basic structure of a PL/SQL block. For syntax details, see "Block".

<< label >> (optional)
DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions raised in executable part]
END;

Declarations are local to the block and cease to exist when the block completes execution, helping to avoid cluttered namespaces for variables and subprograms.

Blocks can be nested: Because a block is an executable statement, it can appear in another block wherever an executable statement is allowed.

You can submit a block to an interactive tool (such as SQL*Plus or Enterprise Manager) or embed it in an Oracle Precompiler or OCI program. The interactive tool or program runs the block only once. The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label).

Error Handling

PL/SQL makes it easy to detect and process error conditions, which are called exceptions. When an error occurs, an exception is raised: normal execution stops and control transfers to special exception-handling code, which comes at the end of any PL/SQL block. Each different exception is processed by a particular exception handler.

PL/SQL exception handling differs from the manual checking that you do in C programming, where you insert a check to ensure that every operation succeeded. Instead, the checks and calls to exception handlers are performed automatically, similar to the exception mechanism in Java programming.

Predefined exceptions are raised automatically for certain common error conditions involving variables or database operations. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception ZERO_DIVIDE automatically.

You can define exceptions of your own, for conditions that you decide are errors, or to correspond to database errors that normally result in ORA-n error messages. When you detect a user-defined error condition, you raise an exception with either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. In the example, if the commission is null, the exception comm_missing is raised.

Typically, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue running from the spot where an exception happens, enclose the code that might raise an exception inside another BEGIN END block with its own exception handler. For example, you might put separate BEGIN END blocks around groups of SQL statements that might raise NO_DATA_FOUND, or around arithmetic operations that might raise DIVIDE_BY_ZERO. By putting a BEGIN END block with an exception handler inside a loop, you can continue running the loop even if some loop iterations raise exceptions. See Example 5-38.

For more information, see Chapter 11, "PL/SQL Error Handling".

Input and Output

Most PL/SQL input and output (I/O) is through SQL statements that store data in database tables or query those tables. All other PL/SQL I/O is done through APIs, such as the PL/SQL package DBMS_OUTPUT. For more information, see "DBMS_OUTPUT Package".

To display output passed to DBMS_OUTPUT, you need another program, such as SQL*Plus. To see DBMS_OUTPUT output with SQL*Plus, you must first issue the SQL*Plus command SET SERVEROUTPUT ON. For more information, see SQL*Plus User's Guide and Reference.

Other PL/SQL APIs for processing I/O are provided by these packages:

Some of the preceding APIs can both accept input and display output, but they cannot accept data directly from the keyboard. To accept data directly from the keyboard, use the SQL*Plus commands PROMPT and ACCEPT.

Variables and Constants

PL/SQL lets you declare variables and constants, and then use them wherever you can use an expression. As the program runs, the values of variables can change, but the values of constants cannot. For more information, see "Declarations" and "Assigning Values to Variables".

Data Abstraction

Data abstraction lets you work with the essential properties of data without being too involved with details. You can design a data structure first, and then design algorithms that manipulate it.

Topics:

• Cursors

• Composite Variables

• %ROWTYPE Attribute

• %TYPE Attribute

• Abstract Data Types

Control Statements

Control statements are the most important PL/SQL extension to SQL.

PL/SQL has three categories of control statements:

• Conditional selection statements, which let you run different statements for different data values.

  For more information, see "Conditional Selection Statements".

• Loop statements, which let you repeat the same statements with a series of different data values.

  For more information, see "LOOP Statements".

• Sequential control statements, which allow you to go to a specified, labeled statement, or to do nothing.

  For more information, see "Sequential Control Statements".

Subprograms

A PL/SQL subprogram is a named PL/SQL block that can be invoked (with a set of parameters, if it has them). PL/SQL has two types of subprograms, procedures and functions. A function returns a result.

You can create a PL/SQL subprogram either at schema level, inside a package, or inside a PL/SQL block (which can be another subprogram).

For more information about PL/SQL subprograms, see Chapter 8, "PL/SQL Subprograms."

PL/SQL also lets you invoke external programs written in other languages. For more information, see "External Subprograms".

Triggers

A trigger is a named PL/SQL unit that is stored in the database and run in response to an event that occurs in the database. You can specify the event, whether the trigger fires before or after the event, and whether the trigger runs once for each event or once for each row affected by the event. For example, you can create a trigger that runs every time an INSERT statement affects the EMPLOYEES table.

For more information about triggers, see Chapter 9, "PL/SQL Triggers."

Packages (APIs Written in PL/SQL)

A package is a PL/SQL unit that consists of related subprograms and the explicit cursors and variables that they use. Packages are stored in the database, where they can be shared by many applications.

The package specification defines the package, declaring the types, variables, constants, exceptions, explicit cursors, and subprograms that can be referenced from outside the package. A package specification is an API: It has all the information that client programs need to call its subprograms, but no information about their implementation.

The package body defines the queries for the explicit cursors, and the code for the subprograms, that are declared in the package specification (therefore, a package with neither explicit cursors nor subprograms does not need a body). The package body can also define local subprograms, which are not declared in the specification and can be invoked only by other subprograms in the package. Package body contents are hidden from client programs. You can change the package body without invalidating the applications that call the package.

Invoking a packaged subprogram for the first time loads the whole package and caches it in memory. Therefore, packages improve performance in a multiuser, multi-application environment.

For more information about packages, see Chapter 10, "PL/SQL Packages."

Conditional Compilation

Conditional compilation lets you customize the functionality in a PL/SQL application without removing source code. For example, you can:

• Use new features with the latest database release, and disable them when running the application in an older database release.

• Activate debugging or tracing statements in the development environment, and hide them when running the application at a production site.

For more information, see "Conditional Compilation".

Processing a Query Result Set One Row at a Time

PL/SQL lets you issue a SQL query and process the rows of the result set one at a time. You can use a simple loop, as in Example 1-2, or you can control the process precisely by using individual statements to perform the query, retrieve data, and finish processing.

BEGIN
  FOR someone IN (SELECT * FROM employees WHERE employee_id < 120)
  LOOP
    DBMS_OUTPUT.PUT_LINE (
      'First name = ' || someone.first_name ||
      ', Last name = ' || someone.last_name
    );
  END LOOP;
END;
/

First name = Steven, Last name = King
First name = Neena, Last name = Kochhar
First name = Lex, Last name = De Haan
First name = Alexander, Last name = Hunold
First name = Bruce, Last name = Ernst
First name = David, Last name = Austin
First name = Valli, Last name = Pataballa
First name = Diana, Last name = Lorentz
First name = Nancy, Last name = Greenberg
First name = Daniel, Last name = Faviet
First name = John, Last name = Chen
First name = Ismael, Last name = Sciarra
First name = Jose Manuel, Last name = Urman
First name = Luis, Last name = Popp
First name = Den, Last name = Raphaely
First name = Alexander, Last name = Khoo
First name = Shelli, Last name = Baida
First name = Sigal, Last name = Tobias
First name = Guy, Last name = Himuro
First name = Karen, Last name = Colmenares

PL/SQL Engine

The PL/SQL compilation and run-time system is an engine that compiles and runs PL/SQL units. The engine can be installed in the database or in an application development tool, such as Oracle Forms.

In either environment, the PL/SQL engine accepts as input any valid PL/SQL unit. The engine runs procedural statements, but sends SQL statements to the SQL engine in the database, as shown in Figure 1-1.

Figure 1-1 PL/SQL Engine

Description of "Figure 1-1 PL/SQL Engine"

Typically, the database processes PL/SQL units.

When an application development tool processes PL/SQL units, it passes them to its local PL/SQL engine. If a PL/SQL unit contains no SQL statements, the local engine processes the entire PL/SQL unit. This is useful if the application development tool can benefit from conditional and iterative control.

For example, Oracle Forms applications frequently use SQL statements to test the values of field entries and do simple computations. By using PL/SQL instead of SQL, these applications can avoid calls to the database.

PL/SQL Units and Compilation Parameters

A PL/SQL unit is any one of these:

• PL/SQL anonymous block

• FUNCTION

• LIBRARY

• PACKAGE

• PACKAGE BODY

• PROCEDURE

• TRIGGER

• TYPE

• TYPE BODY

PL/SQL units are affected by PL/SQL compilation parameters (a category of database initialization parameters). Different PL/SQL units—for example, a package specification and its body—can have different compilation parameter settings.

Table 1-1 summarizes the PL/SQL compilation parameters. To display the values of these parameters for specified or all PL/SQL units, query the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS. For information about this view, see Oracle Database Reference.

The compile-time values of the parameters in Table 1-1 are stored with the metadata of each stored PL/SQL unit, which means that you can reuse those values when you explicitly recompile the unit. (A stored PL/SQL unit is created with one of the "CREATE [OR REPLACE] Statements". An anonymous block is not a stored PL/SQL unit.)

To explicitly recompile a stored PL/SQL unit and reuse its parameter values, you must use an ALTER statement with both the COMPILE clause and the REUSE SETTINGS clause. For more information about REUSE SETTINGS, see "compiler_parameters_clause". (All ALTER statements have this clause. For a list of ALTER statements, see "ALTER Statements".)