1 Overview of PL/SQL

Tight Integration with SQL

SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like statements such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL is tightly integrated with SQL. With PL/SQL, you can use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns.

PL/SQL fully supports SQL datatypes. You do not have to convert between PL/SQL and SQL datatypes. 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. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.

Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages. Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation. Many database features, such as triggers and object types, use PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

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 information about using static SQL with PL/SQL, see Chapter 6, "Using Static SQL". For information about using dynamic SQL, see Chapter 7, "Using Dynamic SQL".

High Performance

With PL/SQL, an entire block of statements can be sent to Oracle Database at one time. This can drastically reduce network traffic between the database and an application. As Figure 1-1 shows, you can use PL/SQL blocks and subprograms (procedures and functions) to group SQL statements before sending them to the database for execution. PL/SQL also has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored subprograms are compiled once and stored in executable form, so subprogram calls are efficient. Because stored subprograms execute 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.

Figure 1-1 PL/SQL Boosts Performance

Description of "Figure 1-1 PL/SQL Boosts Performance"

High Productivity

PL/SQL lets you write very compact code for manipulating data. In the same way that scripting languages such as PERL can read, transform, and write data from files, PL/SQL can query, transform, and update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-engineering features, such as exception handling, encapsulation, data hiding, and object-oriented datatypes.

PL/SQL extends tools such as Oracle Forms. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits. PL/SQL is the same in all environments. After you learn PL/SQL with one Oracle Database tool, you can transfer your knowledge to other tools.

Full Portability

Applications written in PL/SQL can run on any operating system and platform where Oracle Database runs. With PL/SQL, you can write portable program libraries and re-use them in different environments.

Tight Security

PL/SQL stored subprograms move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a subprogram that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement. Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

For information about wrapping, or hiding, the source of a PL/SQL unit, see Appendix A, "Wrapping PL/SQL Source Code".

Access to Predefined Packages

Oracle provides product-specific packages that define APIs you can invoke from PL/SQL to perform many useful tasks. These packages include DBMS_ALERT for using triggers, DBMS_FILE for reading and writing operating system text files, UTL_HTTP for making hypertext transfer protocol (HTTP) callouts, DBMS_OUTPUT for display output from PL/SQL blocks and subprograms, and DBMS_PIPE for communicating over named pipes. For more information about these packages, see "Overview of Product-Specific PL/SQL Packages".

For complete information about the packages supplied by Oracle, see Oracle Database PL/SQL Packages and Types Reference.

Support for Object-Oriented Programming

Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides enabling you to create software components that are modular, maintainable, and re-usable, object types allow different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate. For information about object types, see Oracle Database Object-Relational Developer's Guide.

Support for Developing Web Applications and Pages

You can use PL/SQL to develop Web applications and Server Pages (PSPs). For an overview of the use of PL/SQL with the Web, see "Using PL/SQL to Create Web Applications and Server Pages".

PL/SQL 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.

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.

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

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;

An anonymous PL/SQL block can be submitted to an interactive tool (such as SQL*Plus or Enterprise Manager), or embedded in an Oracle Precompiler or OCI program. The interactive tool or program executes the anonymous block only once.

A named PL/SQL block—a subprogram—can be invoked repeatedly (see "PL/SQL Subprograms").

PL/SQL Error Handling

PL/SQL makes it easy to detect and process error conditions known as 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's exception handling is different from the manual checking you might be used to from C programming, where you insert a check to make sure that every operation succeeded. Instead, the checks and calls to error routines 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 declare exceptions of your own, for conditions that you decide are errors, or to correspond to database errors that normally result in ORA- error messages. When you detect a user-defined error condition, you execute a RAISE statement. See the exception comm_missing in Example 1-17. 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 executing 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 executing the loop even if some loop iterations raise exceptions. See Example 5-38.

For information about PL/SQL errors, see "Overview of PL/SQL Run-Time Error Handling". For information about PL/SQL warnings, see "Overview of PL/SQL Compile-Time Warnings".

PL/SQL 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.

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 about SQL*Plus, see SQL*Plus User's Guide and Reference.

Other PL/SQL APIs for processing I/O are:

• HTF and HTP for displaying output on a web page

• DBMS_PIPE for passing information back and forth between PL/SQL and operating-system commands

• UTL_FILE for reading and writing operating-system files

• UTL_HTTP for communicating with web servers

• UTL_SMTP for communicating with mail servers

Although some of the preceding APIs can accept input as well as display output, they have cannot accept data directly from the keyboard. For that, use the PROMPT and ACCEPT statements in SQL*Plus.

For more information about the preceding APIs, see "Overview of Product-Specific PL/SQL Packages".

PL/SQL Variables and Constants

PL/SQL lets you declare variables and constants, and then use them in SQL and procedural statements anywhere an expression can be used. You must declare a variable or constant before referencing it in any other statements. For more information, see "Declarations".

Topics:

• Declaring PL/SQL Variables

• Assigning Values to Variables

• Declaring PL/SQL Constants

• Bind Variables

DECLARE
  part_number       NUMBER(6);     -- SQL datatype
  part_name         VARCHAR2(20);  -- SQL datatype
  in_stock          BOOLEAN;       -- PL/SQL-only datatype
  part_price        NUMBER(6,2);   -- SQL datatype
  part_description  VARCHAR2(50);  -- SQL datatype

DECLARE                               -- You can assign values here
   wages          NUMBER;
   hours_worked   NUMBER := 40;     -- Assign 40 to hours_worked
   hourly_salary  NUMBER := 22.50;  -- Assign 22.50 to hourly_salary
   bonus          NUMBER := 150;    -- Assign 150 to bonus
   country        VARCHAR2(128);
   counter        NUMBER := 0;      -- Assign 0 to counter
   done           BOOLEAN;
   valid_id       BOOLEAN;
   emp_rec1       employees%ROWTYPE;
   emp_rec2       employees%ROWTYPE;
   TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   comm_tab       commissions;
BEGIN                                 -- You can assign values here too
   wages := (hours_worked * hourly_salary) + bonus;
   country := 'France';
   country := UPPER('Canada');
   done := (counter > 100);
   valid_id := TRUE;
   emp_rec1.first_name := 'Antonio';
   emp_rec1.last_name := 'Ortiz';
   emp_rec1 := emp_rec2;
   comm_tab(5) := 20000 * 0.15;
END;
/

DECLARE
  bonus  NUMBER(8,2);
  emp_id NUMBER(6) := 100;
BEGIN
  SELECT salary * 0.10 INTO bonus FROM employees
    WHERE employee_id = emp_id;
END;
/

REM SERVEROUTPUT must be set to ON
REM to display output with DBMS_OUTPUT
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
  new_sal NUMBER(8,2);
  emp_id  NUMBER(6) := 126;

  PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS
    emp_job VARCHAR2(10);
    avg_sal NUMBER(8,2);
  BEGIN
    SELECT job_id INTO emp_job FROM employees
      WHERE employee_id = emp_id;
    SELECT AVG(salary) INTO avg_sal FROM employees
      WHERE job_id = emp_job;
    DBMS_OUTPUT.PUT_LINE ('The average salary for ' || emp_job
      || ' employees: ' || TO_CHAR(avg_sal));
    sal := (sal + avg_sal)/2;
  END;

BEGIN
  SELECT AVG(salary) INTO new_sal FROM employees;
  DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: '
    || TO_CHAR(new_sal));
  adjust_salary(emp_id, new_sal);
END;
/

credit_limit CONSTANT NUMBER := 5000.00;

PL/SQL Data Abstraction

Data abstraction lets you work with the essential properties of data without being too involved with details. After you design a data structure, you can focus on designing algorithms that manipulate the data structure.

Topics:

• Cursors

• %TYPE Attribute

• %ROWTYPE Attribute

• Collections

• Records

• Object Types

v_last_name employees.last_name%TYPE;

DECLARE
  dept_rec departments%ROWTYPE; -- declare record variable

v_deptid := dept_rec.department_id;

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary, hire_date, job_id FROM employees 
       WHERE employee_id = 120;
-- declare record variable that represents a row
-- fetched from the employees table
   employee_rec c1%ROWTYPE;
BEGIN
-- open the explicit cursor
-- and use it to fetch data into employee_rec
  OPEN c1;
  FETCH c1 INTO employee_rec;
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
END;
/

DECLARE
  TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
  staff staff_list;
  lname employees.last_name%TYPE;
  fname employees.first_name%TYPE;
BEGIN
   staff := staff_list(100, 114, 115, 120, 122);
   FOR i IN staff.FIRST..staff.LAST LOOP
     SELECT last_name, first_name INTO lname, fname FROM employees
       WHERE employees.employee_id = staff(i);
     DBMS_OUTPUT.PUT_LINE (TO_CHAR(staff(i)) ||
                           ': ' || lname || ', ' || fname );
   END LOOP;
END;
/

DECLARE
   TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT);
   TYPE meetin_typ IS RECORD (
      date_held DATE,
      duration  timerec,  -- nested record
      location  VARCHAR2(20),
      purpose   VARCHAR2(50));
BEGIN
-- NULL does nothing but allows unit to be compiled and tested
  NULL;
END;
/

CREATE TYPE bank_account AS OBJECT ( 
  acct_number NUMBER(5),
  balance     NUMBER,
  status      VARCHAR2(10),
  MEMBER PROCEDURE open
   (SELF IN OUT NOCOPY bank_account, amount IN NUMBER),
  MEMBER PROCEDURE close (SELF IN OUT NOCOPY bank_account,
                           num IN NUMBER, amount OUT NUMBER),
  MEMBER PROCEDURE deposit (SELF IN OUT NOCOPY bank_account,
                            num IN NUMBER, amount IN NUMBER),
  MEMBER PROCEDURE withdraw (SELF IN OUT NOCOPY bank_account,
                             num IN NUMBER, amount IN NUMBER),
  MEMBER FUNCTION curr_bal  (num IN NUMBER) RETURN NUMBER);
/

PL/SQL Control Structures

Control structures are the most important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate Oracle Database data, it lets you process the data using flow-of-control statements.

Topics:

• Conditional Control

• Iterative Control

• Sequential Control

For more information, see Chapter 4, "Using PL/SQL Control Structures".

DECLARE
   jobid      employees.job_id%TYPE;
   empid      employees.employee_id%TYPE := 115;
   sal        employees.salary%TYPE;
   sal_raise  NUMBER(3,2);
BEGIN
  SELECT job_id, salary INTO jobid, sal from employees
    WHERE employee_id = empid;
  CASE
    WHEN jobid = 'PU_CLERK' THEN
        IF sal < 3000 THEN sal_raise := .12;
          ELSE sal_raise := .09;
        END IF;
    WHEN jobid = 'SH_CLERK' THEN
        IF sal < 4000 THEN sal_raise := .11;
          ELSE sal_raise := .08;
        END IF;
    WHEN jobid = 'ST_CLERK' THEN
        IF sal < 3500 THEN sal_raise := .10;
          ELSE sal_raise := .07;
        END IF;
    ELSE
     BEGIN
       DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
     END;
  END CASE;
  UPDATE employees SET salary = salary + salary * sal_raise 
    WHERE employee_id = empid;
  COMMIT;
END;
/

LOOP
  -- sequence of statements
END LOOP;

CREATE TABLE sqr_root_sum (num NUMBER, sq_root NUMBER(6,2),
                           sqr NUMBER, sum_sqrs NUMBER);
DECLARE
   s PLS_INTEGER;
BEGIN
  FOR i in 1..100 LOOP
    s := (i * (i + 1) * (2*i +1)) / 6; -- sum of squares
    INSERT INTO sqr_root_sum VALUES (i, SQRT(i), i*i, s );
  END LOOP;
END;
/

CREATE TABLE temp (tempid NUMBER(6),
                   tempsal NUMBER(8,2),
                   tempname VARCHAR2(25));
DECLARE
   sal            employees.salary%TYPE := 0;
   mgr_id         employees.manager_id%TYPE;
   lname          employees.last_name%TYPE;
   starting_empid employees.employee_id%TYPE := 120;
BEGIN
   SELECT manager_id INTO mgr_id
     FROM employees 
     WHERE employee_id = starting_empid;
   WHILE sal <= 15000 LOOP -- loop until sal > 15000
      SELECT salary, manager_id, last_name INTO sal, mgr_id, lname
         FROM employees WHERE employee_id = mgr_id;
   END LOOP;
   INSERT INTO temp VALUES (NULL, sal, lname);
     -- insert NULL for tempid
   COMMIT;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      INSERT INTO temp VALUES (NULL, NULL, 'Not found');
        -- insert NULLs
      COMMIT;
END;
/

DECLARE
  total   NUMBER(9) := 0;
  counter NUMBER(6) := 0;
BEGIN
  LOOP
    counter := counter + 1;
    total := total + counter * counter;
    -- exit loop when condition is true
    EXIT WHEN total > 25000;  
  END LOOP;
  DBMS_OUTPUT.PUT_LINE
    ('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total));
END;
/

DECLARE
  total   NUMBER(9) := 0;
  counter NUMBER(6) := 0;
BEGIN
  <<calc_total>>
    counter := counter + 1;
    total := total + counter * counter;
    -- branch to print_total label when condition is true
    IF total > 25000 THEN GOTO print_total;
      ELSE GOTO calc_total;
    END IF;
  <<print_total>>
  DBMS_OUTPUT.PUT_LINE
    ('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total));
END;
/

PL/SQL Subprograms

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

The PL/SQL code fragment in Example 1-15 declares a PL/SQL procedure.

DECLARE
  in_string  VARCHAR2(100) := 'This is my test string.';
  out_string VARCHAR2(200);

  PROCEDURE double
    (original IN VARCHAR2, new_string OUT VARCHAR2 ) AS
  BEGIN
    new_string := original || original;
  END;

A PL/SQL subprogram can be invoked from an interactive tool (such as SQL*Plus or Enterprise Manager), from an Oracle Precompiler or OCI program, from another PL/SQL subprogram, or from a trigger.

Example 1-16 shows invokes the stored subprogram in Example 1-17 from SQL*Plus twice, first with the CALL statement, and then from inside a BEGIN-END block.

CALL award_bonus(179, 1000);

BEGIN
  award_bonus(179, 10000);
END;
/
-- using named notation
BEGIN award_bonus(emp_id=>179, bonus=>10000); END;
/

Using the BEGIN-END block is recommended in several situations. For example, using the CALL statement can suppress an ORA-01403: no data found error that was not handled in the PL/SQL subprogram.

For additional examples of invoking PL/SQL subprograms, see Example 8-5. For information about the CALL statement, see Oracle Database SQL Language Reference

Topics:

• Standalone PL/SQL Subprograms

• Triggers

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

CREATE OR REPLACE PROCEDURE award_bonus
  (emp_id NUMBER, bonus NUMBER) AS
   commission        REAL;
   comm_missing EXCEPTION;
BEGIN  -- executable part starts here
   SELECT commission_pct / 100 INTO commission FROM employees
    WHERE employee_id = emp_id;
   IF commission IS NULL THEN
      RAISE comm_missing;
   ELSE
      UPDATE employees SET salary = salary + bonus*commission 
      WHERE employee_id = emp_id;
   END IF;
EXCEPTION  -- exception-handling part starts here
   WHEN comm_missing THEN
      DBMS_OUTPUT.PUT_LINE
        ('This employee does not receive a commission.');
      commission := 0;
   WHEN OTHERS THEN
      NULL; -- for other exceptions do nothing
END award_bonus;
/
CALL award_bonus(150, 400);

CREATE TABLE emp_audit (emp_audit_id NUMBER(6), up_date DATE,
  new_sal NUMBER(8,2), old_sal NUMBER(8,2));

CREATE OR REPLACE TRIGGER audit_sal
   AFTER UPDATE OF salary ON employees FOR EACH ROW
BEGIN
-- bind variables are used here for values
   INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                 :new.salary, :old.salary );
END;
/

PL/SQL Packages (APIs Written in PL/SQL)

A PL/SQL package bundles logically related types, variables, cursors, and subprograms into a database object called a package. The package defines a simple, clear, interface to a set of related subprograms and types that can be accessed by SQL statements.

PL/SQL allows you to access many predefined packages (see "Access to Predefined Packages") and to create your own packages.

A package usually has two parts: a specification and a body.

The specification defines the application programming interface (API); it declares the types, constants, variables, exceptions, cursors, and subprograms. To create a package specification, use the SQL statement CREATE PACKAGE. For more information, see Oracle Database SQL Language Reference.

The body contains the SQL queries for cursors and the code for subprograms.To create a package body, use the SQL statement CREATE PACKAGE BODY. For more information, see Oracle Database SQL Language Reference.

In Example 1-19, the emp_actions package contains two procedures that update the employees table and one function that provides information.

-- Package specification:
CREATE OR REPLACE PACKAGE emp_actions AS
   PROCEDURE hire_employee (employee_id     NUMBER,
                            last_name       VARCHAR2,
                            first_name      VARCHAR2,
                            email           VARCHAR2,
                            phone_number    VARCHAR2,
                            hire_date       DATE,
                            job_id          VARCHAR2,
                            salary          NUMBER,
                            commission_pct  NUMBER,
                            manager_id      NUMBER,
                            department_id   NUMBER);
   PROCEDURE fire_employee (emp_id NUMBER);
   FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;
END emp_actions;
/
-- Package body:
CREATE OR REPLACE PACKAGE BODY emp_actions AS
  -- Code for procedure hire_employee:
   PROCEDURE hire_employee (employee_id     NUMBER,
                            last_name       VARCHAR2,
                            first_name      VARCHAR2,
                            email           VARCHAR2,
                            phone_number    VARCHAR2,
                            hire_date       DATE,
                            job_id          VARCHAR2,
                            salary          NUMBER,
                            commission_pct  NUMBER,
                            manager_id      NUMBER,
                            department_id   NUMBER) IS
  BEGIN
    INSERT INTO employees VALUES (employee_id,
                                  last_name,
                                  first_name,
                                  email,
                                  phone_number,
                                  hire_date,
                                  job_id,
                                  salary,
                                  commission_pct,
                                  manager_id,
                                  department_id);
   END hire_employee;

  -- Code for procedure fire_employee:
  PROCEDURE fire_employee (emp_id NUMBER) IS
  BEGIN
    DELETE FROM employees WHERE employee_id = emp_id;
  END fire_employee;

  -- Code for function num_above_salary:
  FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
    emp_sal NUMBER(8,2);
    num_count NUMBER;
  BEGIN
    SELECT salary INTO emp_sal FROM employees
      WHERE employee_id = emp_id;
    SELECT COUNT(*) INTO num_count FROM employees
      WHERE salary > emp_sal;
    RETURN num_count;
  END num_above_salary;
END emp_actions;
/

To invoke a packaged subprogram, you need to know only name of the package and the name and parameters of the subprogram (therefore, you can change the implementation details inside the package body without affecting the invoking applications).

Example 1-20 invokes the emp_actions package procedures hire_employee and fire_employee.

CALL emp_actions.hire_employee(300,
                                 'Belden',
                                 'Enrique',
                                 'EBELDEN',
                                 '555.111.2222',
                                 '31-AUG-04',
                                 'AC_MGR',
                                 9000,
                                 .1,
                                 101,
                                  110);
BEGIN
  DBMS_OUTPUT.PUT_LINE
    ('Number of employees with higher salary: ' || 
      TO_CHAR(emp_actions.num_above_salary(120)));
  emp_actions.fire_employee(300);
END;
/

Packages are stored in Oracle Database, where they can be shared by many applications. Invoking a packaged subprogram for the first time loads the whole package and caches it in memory, saving on disk I/O for subsequent invocations. Thus, packages enhance re-use and improve performance in a multiuser, multi-application environment.

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

Conditional Compilation

PL/SQL provides conditional compilation, which allows you to customize the functionality in a PL/SQL application without having to remove any source code. For example, you can:

• Use the latest functionality with the latest database release and disable the new features to run the application against an older release of the database.

• Activate debugging or tracing functionality in the development environment and hide that functionality in the application while it runs at a production site.

For more information, see "Conditional Compilation".

Embedded SQL Statements

Processing a SQL query with PL/SQL is like processing files with other languages. For example, a PERL program opens a file, reads the file contents, processes each line, then closes the file. In the same way, a PL/SQL program issues a query and processes the rows from the result set as shown in Example 1-21.

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;
/

You can use a simple loop like the one shown here, or you can control the process precisely by using individual statements to perform the query, retrieve data, and finish processing.

PL/SQL Engine

The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL compilation units. The engine can be installed in Oracle 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 compilation unit. The engine executes procedural statements, but sends SQL statements to the SQL engine in the Oracle Database, as shown in Figure 1-2.

Figure 1-2 PL/SQL Engine

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

Typically, Oracle Database processes PL/SQL compilation units.

When an application development tool processes PL/SQL compilation units, it passes them to its local PL/SQL engine. If a compilation unit contains no SQL statements, the local engine processes the entire compilation 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 Oracle Database.

PL/SQL Compilation Units and Compilation Parameters

A PL/SQL compilation unit is any one of the following:

• FUNCTION

• PACKAGE

• PACKAGE BODY

• PROCEDURE

• TRIGGER

• TYPE

• TYPE BODY

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

Table 1-1 lists and briefly describes the PL/SQL compilation parameters. For more information about these parameters, see Oracle Database Reference.

To display the values of these parameters, use the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS. For more information about this view, see Oracle Database Reference.

^Footnote 1 The compile-time value of this parameter is stored with the metadata of the PL/SQL program unit.

The compile-time values of most of the parameters in Table 1-1 are stored with the metadata of the PL/SQL program unit, which means you can re-use those values when you explicitly recompile the program unit by doing the following:

1. Use one of the following statements to recompile the program unit:

   • ALTER FUNCTION COMPILE

   • ALTER PACKAGE COMPILE

   • ALTER PROCEDURE COMPILE

2. Include the REUSE SETTINGS clause in the statement.

   This clause preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in the statement.

If you use the SQL statement CREATE OR REPLACE to explicitly compile a PL/SQL subprogram, or if you do not include the REUSE SETTINGS clause in the ALTER COMPILE statement, then the value of the compilation parameter is its value for the session.