Overview of PL/SQL Units

Naming Subprograms

Because a subprogram is stored in the database, it must be named. This distinguishes it from other stored subprograms and makes it possible for applications to invoke it. Each publicly-visible subprogram in a schema must have a unique name, and the name must be a legal PL/SQL identifier.

Subprogram Parameters

Stored subprograms can take parameters. In the procedure in Example: Stored Procedure with Parameters, the department number is an input parameter that is used when the parameterized cursor c1 is opened.

CREATE OR REPLACE PROCEDURE get_emp_names (
  dept_num IN NUMBER
)
IS
  emp_name  VARCHAR2(10);
  CURSOR    c1 (dept_num NUMBER) IS
                SELECT LAST_NAME FROM EMPLOYEES
                WHERE DEPARTMENT_ID = dept_num;
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;
/

The formal parameters of a subprogram have three major attributes, described in Table: Attributes of Subprogram Parameters.

Topics:

• Parameter Modes

• Parameter Data Types

• %TYPE and %ROWTYPE Attributes

• Tables and Records

• Initial Parameter Values

PROCEDURE get_emp_names(dept_num IN EMPLOYEES.DEPARTMENT_ID%TYPE)

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

CREATE OR REPLACE PROCEDURE get_emp_rec (
  emp_number  IN  EMPLOYEES.EMPLOYEE_ID%TYPE,
  emp_info    OUT EMPLOYEES%ROWTYPE
)
IS
BEGIN
  SELECT * INTO emp_info
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_number;
END;
/
 

DECLARE
  emp_row  EMPLOYEES%ROWTYPE;
BEGIN
  get_emp_rec(206, emp_row);
  DBMS_OUTPUT.PUT('EMPLOYEE_ID: ' || emp_row.EMPLOYEE_ID);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('FIRST_NAME: ' || emp_row.FIRST_NAME);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('LAST_NAME: ' || emp_row.LAST_NAME);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('EMAIL: ' || emp_row.EMAIL);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('PHONE_NUMBER: ' || emp_row.PHONE_NUMBER);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('HIRE_DATE: ' || emp_row.HIRE_DATE);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('JOB_ID: ' || emp_row.JOB_ID);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('SALARY: ' || emp_row.SALARY);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('COMMISSION_PCT: ' || emp_row.COMMISSION_PCT);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('MANAGER_ID: ' || emp_row.MANAGER_ID);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('DEPARTMENT_ID: ' || emp_row.DEPARTMENT_ID);
  DBMS_OUTPUT.NEW_LINE;
END;
/

EMPLOYEE_ID: 206
FIRST_NAME: William
LAST_NAME: Gietz
EMAIL: WGIETZ
PHONE_NUMBER: 415.555.0100
HIRE_DATE: 07-JUN-94
JOB_ID: AC_ACCOUNT
SALARY: 8300
COMMISSION_PCT:
MANAGER_ID: 205
DEPARTMENT_ID: 110

FUNCTION get_emp_rec (dept_num IN EMPLOYEES.DEPARTMENT_ID%TYPE)
   RETURN EMPLOYEES%ROWTYPE IS ...

PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...
PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT) IS ...

Creating Subprograms

Use a text editor to write the subprogram. Then, using an interactive tool such as SQL*Plus, load the text file containing the procedure by entering:

@get_emp

This loads the procedure into the current schema from the get_emp.sql file (.sql is the default file extension). The slash (/) after the code is not part of the code, it only activates the loading of the procedure.

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

Privileges Needed

To create a subprogram, a package specification, or a package body, you must meet these prerequisites:

• You must have the CREATE PROCEDURE system privilege to create a subprogram or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a subprogram or package in another user's schema. In either case, the package body must be created in the same schema as the package.

  
  

  Note: To create without errors (to compile the subprogram or package successfully) requires these additional privileges: The owner of the subprogram or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code. The owner cannot obtain required privileges through roles.

  
  

If the privileges of the owner of a subprogram or package change, then the subprogram must be reauthenticated before it is run. If a necessary privilege to a referenced object is revoked from the owner of the subprogram or package, then the subprogram cannot be run.

The EXECUTE privilege on a subprogram gives a user the right to run a subprogram owned by another user. Privileged users run the subprogram under the security domain of the owner of the subprogram. Therefore, users need not be granted the privileges to the objects referenced by a subprogram. This allows for more disciplined and efficient security strategies with database applications and their users. Furthermore, all subprograms 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 subprograms and packages.

Altering Subprograms

To alter a subprogram, you must first drop it using the DROP PROCEDURE or DROP FUNCTION statement, then re-create it using the CREATE PROCEDURE or CREATE FUNCTION statement. Alternatively, use the CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION statement, which first drops the subprogram if it exists, then re-creates it as specified.

Dropping Subprograms and Packages

A standalone subprogram, a standalone function, a package body, or an entire package can be dropped using the SQL statements DROP PROCEDURE, DROP FUNCTION, DROP PACKAGE BODY, and DROP PACKAGE, respectively. A DROP PACKAGE statement drops both the specification and body of a package.

This statement drops the Old_sal_raise procedure in your schema:

DROP PROCEDURE Old_sal_raise;

Privileges Needed

To drop a subprogram or package, the subprogram or package must be in your schema, or you must have the DROP ANY PROCEDURE privilege. An individual subprogram within a package cannot be dropped; the containing package specification and body must be re-created without the subprograms to be dropped.

External Subprograms

A PL/SQL subprogram executing on an Oracle Database instance can invoke an external subprogram written in a third-generation language (3GL). The 3GL subprogram runs in a separate address space from that of the database.

PL/SQL Function Result Cache

Using the PL/SQL function result cache can save significant space and time. Each time a result-cached PL/SQL function is invoked with different parameter values, those parameters and their result are stored in the cache. Subsequently, when the same function is invoked with the same parameter values, the result is retrieved from the cache, instead of being recomputed. Because the cache is stored in a shared global area (SGA), it is available to any session that runs your application.

If a database object that was used to compute a cached result is updated, the cached result becomes invalid and must be recomputed.

The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never.

For more information about the PL/SQL function result cache, see Oracle Database PL/SQL Language Reference.

PL/SQL Packages

A package is a collection of related program objects (for example, subprogram, variables, constants, cursors, and exceptions) stored as a unit in the database.

Using packages is an alternative to creating subprograms as standalone schema objects. Packages have many advantages over standalone subprograms. For example, they:

• Let you organize your application development more efficiently.

• Let you grant privileges more efficiently.

• Let you modify package objects without recompiling dependent schema objects.

• Enable Oracle Database to read multiple package objects into memory at once.

• Can contain global variables and cursors that are available to all subprograms in the package.

• Let you overload subprograms. Overloading a subprogram means creating multiple subprograms with the same name in the same package, each taking arguments of different number or data type.

  
  

  See Also: Oracle Database PL/SQL Language Reference for more information about subprogram name overloading

  
  

The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines both the objects declared in the specification and private objects that are not visible to applications outside the package.

Example: Creating PL/SQL Package and Invoking Packaged Subprogram creates a package that contains one stored function and two stored procedures, and then invokes a procedure.

-- Sequence that packaged function needs:
 
CREATE SEQUENCE emp_sequence
START WITH 8000
INCREMENT BY 10;
 
-- Package specification:
 
CREATE or REPLACE PACKAGE employee_management IS
  FUNCTION hire_emp (
  firstname  VARCHAR2,
  lastname   VARCHAR2,
  email      VARCHAR2,
  phone      VARCHAR2,
  hiredate   DATE,
  job        VARCHAR2,
  sal        NUMBER,
  comm       NUMBER,
  mgr        NUMBER,
  deptno     NUMBER
) RETURN NUMBER;
 
 PROCEDURE fire_emp(
    emp_id IN NUMBER
 );
 
 PROCEDURE sal_raise (
    emp_id IN NUMBER,
    sal_incr IN NUMBER
 );
END employee_management;
/
 
-- Package body:
 
CREATE or REPLACE PACKAGE BODY employee_management IS
  FUNCTION hire_emp (
    firstname  VARCHAR2,
    lastname   VARCHAR2,
    email      VARCHAR2,
    phone      VARCHAR2,
    hiredate   DATE,
    job        VARCHAR2,
    sal        NUMBER,
    comm       NUMBER,
    mgr        NUMBER,
    deptno     NUMBER
  ) RETURN NUMBER
 IS
   new_empno  NUMBER(10);
 BEGIN

   new_empno := emp_sequence.NEXTVAL;
 
    INSERT INTO EMPLOYEES (
      employee_id,
      first_name,
      last_name,
      email,
      phone_number,
      hire_date,
      job_id,
      salary,
      commission_pct,
      manager_id,
      department_id
    )
    VALUES (
      new_empno,
      firstname,
      lastname,
      email,
      phone,
      hiredate,  
      job,
      sal, 
      comm,
      mgr,
      deptno
    );

   RETURN (new_empno);
 END hire_emp;
 
 PROCEDURE fire_emp (
   emp_id IN NUMBER
 ) IS
 BEGIN
   DELETE FROM EMPLOYEES
   WHERE EMPLOYEE_ID = emp_id;
 
   IF SQL%NOTFOUND THEN
     raise_application_error(
       -20011,
       'Invalid Employee Number: ' || TO_CHAR(Emp_id)
     );
   END IF;
 END fire_emp;
 
 PROCEDURE sal_raise (
    emp_id IN NUMBER,
    sal_incr IN NUMBER
  ) IS
  BEGIN
    UPDATE EMPLOYEES
    SET SALARY = SALARY + sal_incr
    WHERE EMPLOYEE_ID = emp_id;
 
    IF SQL%NOTFOUND THEN
      raise_application_error(
        -20011,
        'Invalid Employee Number: ' || TO_CHAR(Emp_id)
      );
    END IF;
  END sal_raise;
END employee_management;
/

DECLARE
  empno  NUMBER(6);
  sal    NUMBER(6);
  temp   NUMBER(6);
BEGIN
  empno := employee_management.hire_emp(
            'John',
            'Doe',
            'john.doe@company.com',
            '555-0100',
            '20-SEP-07',
            'ST_CLERK',
            2500,
            0,
            100,
            20);
 
  DBMS_OUTPUT.PUT_LINE('New employee ID is ' || TO_CHAR(empno));
END;
/

PL/SQL Object Size Limits

The size limit for PL/SQL stored database objects such as subprograms, triggers, and packages is the size of the Descriptive Intermediate Attributed Notation for Ada (DIANA) code in the shared pool in bytes. The Linux and UNIX limit on the size of the flattened DIANA/code size is 64K but the limit might be 32K on desktop platforms.

The most closely related number that a user can access is the PARSED_SIZE in the static data dictionary view *_OBJECT_SIZE. That gives the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables. This is not the size in the shared pool. The size of the DIANA part of PL/SQL code (used during compilation) is significantly larger in the shared pool than it is in the system table.

Creating Packages

Each part of a package is created with a different statement. Create the package specification using the CREATE PACKAGE statement. The CREATE PACKAGE statement declares public package objects.

To create a package body, use the CREATE PACKAGE BODY statement. The CREATE PACKAGE BODY statement defines the procedural code of the public subprograms declared in the package specification.

You can also define private, or local, package subprograms, and variables in a package body. These objects can only be accessed by other subprograms in the body of the same package. They are not visible to external users, regardless of the privileges they hold.

It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE or CREATE PACKAGE BODY statements when you are first developing your application. The effect of this option is to drop the package or the package body without warning. The CREATE statements are:

CREATE OR REPLACE PACKAGE Package_name AS ...

and

CREATE OR REPLACE PACKAGE BODY Package_name AS ...

Naming Packages and Package Objects

The names of a package and all public objects in the package must be unique within a given schema. The package specification and its body must have the same name. All package constructs must have unique names within the scope of the package, unless overloading of subprogram names is desired.

Package Invalidations and Session State

Each session that references a package object has its own instance of the corresponding package, including persistent state for any public and private variables, cursors, and constants. If any of the session's instantiated packages (specification or body) are invalidated, then all package instances in the session are invalidated and recompiled. Therefore, the session state is lost for all package instances in the session.

When a package in a given session is invalidated, the session receives ORA-04068 the first time it attempts to use any object of the invalid package instance. The second time a session makes such a package call, the package is reinstantiated for the session without error.

In most production environments, DDL operations that can cause invalidations are usually performed during inactive working hours; therefore, this situation might not be a problem for end-user applications. However, if package invalidations are common in your system during working hours, then you might want to code your applications to handle this error when package calls are made.

Packages Supplied with Oracle Database

There are many packages provided with Oracle Database, either to extend the functionality of the database or to give PL/SQL access to SQL features. You can invoke these packages from your application.

Overview of Bulk Binding

Oracle Database uses two engines to run PL/SQL blocks and subprograms. The PL/SQL engine runs procedural statements, while the SQL engine runs SQL statements. During execution, every SQL statement causes a context switch between the two engines, resulting in performance overhead.

Performance can be improved substantially by minimizing the number of context switches required to run a particular block or subprogram. When a SQL statement runs inside a loop that uses collection elements as bind variables, the large number of context switches required by the block can cause poor performance. Collections include:

• Varrays

• Nested tables

• Index-by tables

• Host arrays

Binding is the assignment of values to PL/SQL variables in SQL statements. Bulk binding is binding an entire collection at once. Bulk binds pass the entire collection back and forth between the two engines in a single operation.

Typically, using bulk binds improves performance for SQL statements that affect four or more database rows. The more rows affected by a SQL statement, the greater the performance gain from bulk binds.

When to Use Bulk Binds

Consider using bulk binds to improve the performance of:

• DML Statements that Reference Collections

• SELECT Statements that Reference Collections

• FOR Loops that Reference Collections and Return DML

DECLARE
  2    TYPE numlist IS VARRAY (100) OF NUMBER;
  3    id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
  -- Efficient method, using bulk bind:
  
  FORALL i IN id.FIRST..id.LAST
  UPDATE EMPLOYEES
  SET SALARY = 1.1 * SALARY
  WHERE MANAGER_ID = id(i);
 
 -- Slower method:
 
 FOR i IN id.FIRST..id.LAST LOOP
    UPDATE EMPLOYEES
    SET SALARY = 1.1 * SALARY
    WHERE MANAGER_ID = id(i);
 END LOOP;
END;
/

DECLARE
  TYPE var_tab IS TABLE OF VARCHAR2(20)
  INDEX BY PLS_INTEGER;
  
  empno    VAR_TAB;
  ename    VAR_TAB;
  counter  NUMBER;
  
  CURSOR c IS
    SELECT EMPLOYEE_ID, LAST_NAME
    FROM EMPLOYEES
    WHERE MANAGER_ID = 7698;
BEGIN
 -- Efficient method, using bulk bind:
 
 SELECT EMPLOYEE_ID, LAST_NAME BULK COLLECT
 INTO empno, ename
 FROM EMPLOYEES
 WHERE MANAGER_ID = 7698;
 
 -- Slower method:
 
 counter := 1;
 
 FOR rec IN c LOOP
    empno(counter) := rec.EMPLOYEE_ID;
    ename(counter) := rec.LAST_NAME;
    counter := counter + 1;
 END LOOP;
END;
/

DECLARE
  TYPE emp_list IS VARRAY(100) OF EMPLOYEES.EMPLOYEE_ID%TYPE;
  empids emp_list := emp_list(182, 187, 193, 200, 204, 206);
  
  TYPE bonus_list IS TABLE OF EMPLOYEES.SALARY%TYPE;
  bonus_list_inst  bonus_list;
  
BEGIN
  -- Efficient method, using bulk bind:
 
 FORALL i IN empids.FIRST..empids.LAST
 UPDATE EMPLOYEES
 SET SALARY = 0.1 * SALARY
 WHERE EMPLOYEE_ID = empids(i)
 RETURNING SALARY BULK COLLECT INTO bonus_list_inst;
 
 -- Slower method:
 
 FOR i IN empids.FIRST..empids.LAST LOOP
   UPDATE EMPLOYEES
   SET SALARY = 0.1 * SALARY
   WHERE EMPLOYEE_ID = empids(i)
   RETURNING SALARY INTO bonus_list_inst(i);
 END LOOP;
END;
/

Triggers

A trigger is a special kind of PL/SQL anonymous block. You can define triggers to fire before or after SQL statements, either on a statement level or for each row that is affected. You can also define INSTEAD OF triggers or system triggers (triggers on DATABASE and SCHEMA).