Chapter Ten. PLSQL Program Units

Chapter Ten. PL/SQL Program Units

10.1 PROCEDURES

10.2 FUNCTIONS

10.3 SUBPROGRAM ENCAPSULATION: INTRODUCTION TO PACKAGES

10.4 PACKAGE SPECIFICATION

10.4.1 Syntax and Style

10.4.2 Developing a Specification

10.5 PACKAGE BODY

10.6 APPLICATION PARTITIONING

10.7 DATA ABSTRACTION

10.8 PARAMETERS AND MODES

10.8.1 IN Mode (Default) Is a Constant

10.8.2 IN OUT Mode

10.8.3 OUT Mode

10.8.4 Functions and Modes

10.8.5 Named versus Positional Notation

10.8.6 Default Parameters

10.8.7 Scaling Code with Defaults

10.8.8 %TYPE

10.9 OVERLOADING

This chapter presents PL/SQL from a software engineering perspective. PL/SQL has the following program units:

• Procedure

• Function

• Package specification

• Package body

Stored procedures are PL/SQL program units that exist as objects in the Oracle database. The term "stored procedure" refers to a compiled and callable program within the database. Oracle implements stored procedures as procedures, functions, and packages. You do not call packages. You call the procedures and functions within the visible part of the package. Database vendors have different implementations for stored procedures. SQL Server Transact-SQL provides procedures and functions but not packages.

Subprograms refer to procedures and functions in general, whether it be a stand-alone procedure/function or procedure/function in a package. PL/SQL procedures and functions model procedures and functions of other languages ”they receive parameters, declare local variables , and implement some degree of logic using the constructs of the PL/SQL programming language. Packages are program units that encapsulate persistent data as well as subprograms.

PL/SQL is a loosely typed language. It handles implicit conversion; for example, you can pass a NUMBER type argument to a procedure designed to accept a string. On the other hand, Oracle performs type checking at compile-time and run-time. A compile-time constraint check fails if a procedures attempts to assign a composite record structure to a scalar variable. Run-time errors occur when an implicit conversion fails. Variables of type NUMBER are implicitly converted to a VARCHAR2; however, if the run-time value is 9999 and the range is VARCHAR2(3), a run-time constraint violation occurs because 9999 is too large for the three-byte character string.

All subprogram parameters have a mode that designates the direction of the data ”between the caller and called program. Parameter modes are IN, OUT, and IN OUT. Most violations of this type are detected at compile-time; for example, a compile error occurs when an IN mode variable is written to. This is detected when the IN mode variable is on the left side of an assignment statement. In general, there are many forms of constraint and type checking performed by the compiler. Run-time checks enforce constraints not evaluated at compile-time.

PL/SQL is a language that integrates several software engineering principals: information hiding, encapsulation, data abstraction, modular design, and stepwise refinement. A key feature of the language is the package paradigm that couples the language with design. A compiled package specification can be reviewed as a detail design document, minimizing the transition from design to program development.

Additionally, PL/SQL integrates the ANSI SQL Standard into the programming language in a way that facilitates rapid development. Examples of these features are SQL SELECT statements that populate composite record structures and cursor FOR loops .

Structure type definitions can be created in the database from which database table column types and PL/SQL array structures can be derived. The integration of types between the database and PL/SQL allows a program to select a column that stores an array of structures in a PL/SQL variable.

The robust features of this language provide a strong motivation for Java and .Net developers to implement large portions of their functionality with PL/SQL.

A strength that continues to grow with PL/SQL is Oracle support for additional built-in packages. The list of packages provided by Oracle is quite extensive . Following are some examples of listings.

• DBMS_PIPS and DBMS_ALERT provide a mechanism by which separate Oracle processes can communicate asynchronously ”similar to UNIX-named pipes.

• Advanced Queuing includes several packages that push and pull data into and out of logical queues in a distributed environment.

• The DBMS_LOB package provides support for handling large 4-gigabyte objects as column values in a table. This allows a PL/SQL procedure to load a large document, such as a Microsoft Word or PDF file, in native format, into a table.

• Java in the database allows a PL/SQL package specification to act as a PL/SQL API to a body implementation using Java. An example would be a PL/SQL package specification written in PL/SQL with a package body written in Java.

• PL/SQL programs can perform IO to host files using the UTL_FILE package.

• There are packages for generating random numbers ; implementing HTML server-side programs, similar to a CGI model or an XML DOM API; and more. This list is relatively short compared to the complete sweep of APIs provided by Oracle.

You can execute a stored procedure from a SQL*Plus session. You can embed PL/SQL code in third-generation languages: C programs written with Pro*C can include PL/SQL blocks and call stored procedures. Any client-side interface, whether Web or client/server, can invoke PL/SQL procedures compiled in the database. Connectivity support for PL/SQL procedures includes ODBC, JDBC, and Oracle's Net8.

There is benefit to PL/SQL as a language choice for Oracle systems development. Compared to other languages, it takes less time to code a complex function written in PL/SQL. The following discussion addresses some considerations when planning what packages you might develop and how they might interface with each other.