PL/SQL

You can directly issue SQL data manipulation language (DML) statements inside PL/SQL blocks, and you can use procedures, supplied by Oracle, to perform data definition language (DDL) statements.

PL/SQL code executes on the server, so using PL/SQL allows you to centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.

Note: Some Oracle tools, such as Oracle Forms, contain a PL/SQL engine, and can execute PL/SQL locally.

You can even use PL/SQL for some database applications in place of 3GL programs that use embedded SQL or the Oracle Call Interface (OCI).

There are several kinds of PL/SQL program units:

• anonymous PL/SQL blocks

• triggers

• standalone stored procedures and functions

• packages, that can contain stored procedures and functions

Anonymous Blocks

You use the declarative part to declare PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is called when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND or ZERO_DIVIDE), or as an exception that you define.

The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the EMP table, using the DBMS_OUTPUT package (described ):

DECLARE
    emp_name    VARCHAR2(10);
    CURSOR      c1 IS SELECT ename FROM emp
                        WHERE deptno = 20;
BEGIN
    LOOP
        FETCH c1 INTO emp_name;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp_name);
    END LOOP;
END;

Note: If you try this block out using SQL*Plus make sure to issue the command SET SERVEROUTPUT ON so that output using the DBMS_OUTPUT procedures such as PUT_LINE is activated. Also, terminate the example with a slash (/) to activate it.

Exceptions allow you to handle Oracle error conditions within PL/SQL program logic. This allows your application to prevent the server from issuing an error that could cause the client application to abort. The following anonymous block handles the predefined Oracle exception NO_DATA_FOUND (which would result in an ORA-01403 error if not handled):

DECLARE
    emp_number   INTEGER := 9999;
    emp_name     VARCHAR2(10);
BEGIN
    SELECT ename INTO emp_name FROM emp
        WHERE empno = emp_number;   -- no such number
    DBMS_OUTPUT.PUT_LINE('Employee name is ' || emp_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No such employee: ' || emp_number);
END;

You can also define your own exceptions, declare them in the declaration part of a block, and define them in the exception part of the block. An example follows:

DECLARE
    emp_name           VARCHAR2(10);
    emp_number         INTEGER;
    empno_out_of_range EXCEPTION;
BEGIN
    emp_number := 10001;
    IF emp_number > 9999 OR emp_number < 1000 THEN
        RAISE empno_out_of_range;
    ELSE
        SELECT ename INTO emp_name FROM emp
            WHERE empno = emp_number;
        DBMS_OUTPUT.PUT_LINE('Employee name is ' || emp_name);
END IF;
EXCEPTION
    WHEN empno_out_of_range THEN
        DBMS_OUTPUT.PUT_LINE('Employee number ' || emp_number ||
          ' is out of range.');
END;

See the PL/SQL User's Guide and Reference for a complete treatment of exceptions.

Anonymous blocks are most often used either interactively, from a tool such as SQL*Plus, or in a precompiler, OCI, or SQL*Module application. They are normally used to call stored procedures, or to open cursor variables. (See for a description of cursor variables.)

Database Triggers

Stored Procedures and Functions

• has a name

• can take parameters, and return values

• is stored in the data dictionary

• can be invoked by many users

Procedure Names

Note: If you plan to call a stored procedure using a stub generated by SQL*Module, the stored procedure name must also be a legal identifier in the calling host 3GL language such as Ada or C.

Procedure and function names that are part of packages can be overloaded. That is, you can use the same name for different subprograms as long as their formal parameters differ in number, order, or datatype family. See the PL/SQL User's Guide and Reference for more information about subprogram name overloading.

Procedure Parameters

PROCEDURE get_emp_names (dept_num IN NUMBER) IS
    emp_name       VARCHAR2(10);
    CURSOR         c1 (depno NUMBER) IS
                      SELECT ename FROM emp
                        WHERE deptno = depno;






BEGIN
    OPEN c1(dept_num);
    LOOP
        FETCH c1 INTO emp_name;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp_name);
    END LOOP;
    CLOSE c1;
END;

In the stored procedure example, the department number is an input parameter, which is used when the parameterized cursor C1 is opened.

The formal parameters of a procedure have three major parts:

name	The name of the parameter, which must be a legal PL/SQL identifier.
mode	The parameter mode, which indicates whether the parameter is an input-only parameter (IN), an output-only parameter (OUT), or is both an input and an output parameter (IN OUT). If the mode is not specified, IN is assumed.
datatype	The parameter datatype is a standard PL/SQL datatype.

Parameter Modes

Table 7 - 1 summarizes the information about parameter modes. Parameter modes are explained in detail in the PL/SQL User's Guide and Reference.

IN	OUT	IN OUT
the default	must be specified	must be specified
passes values to a subprogram	returns values to the caller	passes initial values to a subprogram; returns updated values to the caller
formal parameter acts like a constant	formal parameter acts like an uninitialized variable	formal parameter acts like an initialized variable
formal parameter cannot be assigned a value	formal parameter cannot be used in an expression; must be assigned a value	formal parameter should be assigned a value
actual parameter can be a constant, initialized variable, literal, or expression	actual parameter must be a variable	actual parameter must be a variable

Table 7 - 1. Parameter Modes

Parameter Datatypes

• an unconstrained type name, such as NUMBER or VARCHAR2

• a type that is constrained using the %TYPE or %ROWTYPE attributes

%TYPE and %ROWTYPE Attributes However, you can use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the GET_EMP_NAMES procedure specification could be written as

PROCEDURE get_emp_names(dept_num IN emp.deptno%TYPE)

to have the DEPT_NUM parameter take the same datatype as the DEPTNO column in the EMP table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.

Using %TYPE is recommended, since if the type of the column in the table changes, it is not necessary to change the application code.

If the GET_EMP_NAMES procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:

dept_number    number(2);
...
PROCEDURE get_emp_names(dept_num IN dept_number%TYPE);

You use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the GET_EMP_REC procedure, that returns all the columns of the EMP table in a PL/SQL record, for the given EMPNO:

PROCEDURE get_emp_rec (emp_number  IN emp.empno%TYPE,
                       emp_ret    OUT emp%ROWTYPE) IS
BEGIN
    SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
        INTO emp_ret
        FROM emp
        WHERE empno = emp_number;
END;

You could call this procedure from a PL/SQL block as follows:

DECLARE
  emp_row      emp%ROWTYPE;     -- declare a record matching a
                                -- row in the EMP table
BEGIN
  get_emp_rec(7499, emp_row);   -- call for emp# 7499
  DBMS_OUTPUT.PUT(emp_row.ename || ' ' || emp_row.empno);
  DBMS_OUTPUT.PUT(' ' || emp_row.job || ' ' || emp_row.mgr);
  DBMS_OUTPUT.PUT(' ' || emp_row.hiredate || ' ' || emp_row.sal);
  DBMS_OUTPUT.PUT(' ' || emp_row.comm || ' ' || emp_row.deptno);
  DBMS_OUTPUT.NEW_LINE;
END;

Stored functions can also return values that are declared using %ROWTYPE. For example:

FUNCTION get_emp_rec (dept_num IN emp.deptno%TYPE)
    RETURN emp%ROWTYPE IS ...

Tables and Records

Default Parameter Values

PROCEDURE get_emp_names (dept_num IN NUMBER DEFAULT 20) IS ...

or as

PROCEDURE get_emp_names (dept_num IN NUMBER := 20) IS ...

When a parameter takes a default value, it can be omitted from the actual parameter list when you call the procedure. When you do specify the parameter value on the call, it overrides the default value.

DECLARE Keyword

Creating Stored Procedures and Functions

CREATE PROCEDURE procedure_name AS   ...

For example, to use the example , you can create a text (source) file called get_emp.sql containing the following code:

CREATE PROCEDURE get_emp_rec (emp_number  IN emp.empno%TYPE,
                              emp_ret    OUT emp%ROWTYPE) AS
BEGIN
    SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
        INTO emp_ret
        FROM emp
        WHERE empno = emp_number;
END;

Then, using an interactive too such as SQL*Plus, load the text file containing the procedure by entering the command

SQLPLUS> @get_emp

to load the procedure into the current schema. (.SQL is the default file extension.) Note the slash (/) at the end of the code. This is not part of the code; it just activates the loading of the procedure.

Note: When developing a new procedure, it is usually much more convenient to use the CREATE OR REPLACE . . . PROCEDURE command. This replaces any previous version of that procedure in the same schema with the newer version. This is done with no warning.

You can use either the keyword IS or AS after the procedure parameter list.

Use the CREATE [OR REPLACE] FUNCTION . . . command to store functions. See the Oracle7 Server SQL Reference for the complete syntax of the CREATE PROCEDURE and CREATE FUNCTION commands.

Privileges Required to Create Procedures and Functions

• You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user's schema.

The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.

If the privileges of a procedure's or package's owner change, the procedure must be reauthenticated before it is executed. If a necessary privilege to a referenced object is revoked from the owner of the procedure (or package), the procedure cannot be executed.

The EXECUTE privilege on a procedure gives a user the right to execute a procedure owned by another user. Privileged users execute the procedure under the security domain of the procedure's owner. Therefore, users never have to be granted the privileges to the objects referenced by a procedure. This allows for more disciplined and efficient security strategies with database applications and their users. Furthermore, all procedures and packages are stored in the data dictionary (in the SYSTEM tablespace). No quota controls the amount of space available to a user who creates procedures and packages.

Altering Stored Procedures and Functions

The procedure or function is dropped with no warning.