Chapter Ten. PL/SQL Program Units
This chapter presents PL/SQL from a software engineering perspective. PL/SQL has the following program units:
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.
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. |