Understanding PL/SQL Procedures

A procedure is a subprogram that performs a specific action. You write procedures using the syntax:

[CREATE [OR REPLACE]]

PROCEDURE procedure_name[(parameter[, parameter]...)]

   [AUTHID {DEFINER | CURRENT_USER}] {IS | AS}

   [PRAGMA AUTONOMOUS_TRANSACTION;]

   [local declarations]

BEGIN

   executable statements

[EXCEPTION

   exception handlers]

END [name];

where parameter stands for the following syntax:

parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype

   [{:= | DEFAULT} expression]

The CREATE clause lets you create standalone procedures, which are stored in an Oracle database. You can execute the CREATE PROCEDURE statement interactively from SQL*Plus or from a program using native dynamic SQL (see Chapter 11).

The AUTHID clause determines whether a stored procedure executes with the privileges of its owner (the default) or current user and whether its unqualified references to schema objects are resolved in the schema of the owner or current user. You can override the default behavior by specifying CURRENT_USER. For more information, see "Invoker Rights Versus Definer Rights".

The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a procedure as autonomous (independent). Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction. For more information, see "Doing Independent Units of Work with Autonomous Transactions".

You cannot constrain the datatype of a parameter. For example, the following declaration of acct_id is illegal because the datatype CHAR is size-constrained:

PROCEDURE reconcile (acct_id CHAR(5)) IS ...  -- illegal

However, you can use the following workaround to size-constrain parameter types indirectly:

DECLARE 

   SUBTYPE Char5 IS CHAR(5);

   PROCEDURE reconcile (acct_id Char5) IS ...

A procedure has two parts: the specification (spec for short) and the body. The procedure spec begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: a declarative part, an executable part, and an optional exception-handling part.

The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The keyword DECLARE, which introduces declarations in an anonymous PL/SQL block, is not used. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement. The exception-handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.

Consider the procedure raise_salary, which increases the salary of an employee by a given amount:

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS

   current_salary REAL;

   salary_missing EXCEPTION;

BEGIN

   SELECT sal INTO current_salary FROM emp

      WHERE empno = emp_id;

   IF current_salary IS NULL THEN

      RAISE salary_missing;

   ELSE

      UPDATE emp SET sal = sal + amount

         WHERE empno = emp_id;

   END IF;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      INSERT INTO emp_audit VALUES (emp_id, 'No such number');

   WHEN salary_missing THEN

      INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');

END raise_salary;

When called, this procedure accepts an employee number and a salary increase amount. It uses the employee number to select the current salary from the emp database table. If the employee number is not found or if the current salary is null, an exception is raised. Otherwise, the salary is updated.

A procedure is called as a PL/SQL statement. For example, you might call the procedure raise_salary as follows:

raise_salary(emp_id, amount);