10 PL/SQL

How do I…

10.1 Create anonymous PL/SQL blocks?

10.2 Achieve conditional and looping control?

10.3 Create a stored procedure?

10.4 Create a stored function?

10.5 Create a stored package?

10.6 List information about stored objects in the database?

10.7 Overload procedures and functions in packages?

10.8 Create variables to represent database records and columns?

10.9 Achieve array functionality with PL/SQL Index-By tables?

10.10 Handle predefined exceptions and system errors?

10.11 Handle user-defined exceptions and user-defined errors?

10.12 Rebuild statements to create stored modules?

Oracle PL/SQL is a procedural language extension to SQL. It is a sophisticated block-structured programming language that provides the flexibility of third-generation languages in addition to nonprocedural SQL constructs. PL/SQL includes a full range of datatypes, conditional structures, loop structures, and exception handling structures.

Two types of PL/SQL program units exist: anonymous blocks and stored subprograms. PL/SQL subprograms (functions and procedures) promote development of modular code. PL/SQL is tightly integrated with SQL, enabling SQL statements to be executed within PL/SQL programs and PL/SQL functions to be used within SQL statements. The PL/SQL runtime engine executes procedural statements and sends SQL statements to the SQL Statement Executor in the Oracle Server. This runtime engine can reside in the Oracle Server or in an application development tool like Oracle Forms..

This chapter presents techniques that provide a foundation to help you use PL/SQL effectively in developing better applications. All your PL/SQL applications written for Oracle7 will work in Oracle8..

10.1 Create Anonymous PL/SQL Blocks.

A block is the basic unit of PL/SQL code, which can contain a mix of procedural constructs and SQL statements. All PL/SQL programs are made up of one or more blocks. Each block performs a unit of work within the application, and blocks can be nested to create modular code. The DBMS_OUTPUT package can be used in a PL/SQL block to print text messages in SQL*Plus and is commonly used to debug PL/SQL blocks. This How-To presents examples for creating PL/SQL blocks..

10.2 Achieve Conditional and Looping Control.

PL/SQL has three types of IF constructs to control the execution of statements based on Boolean conditions. PL/SQL also contains three basic types of looping constructs. The simple LOOP construct performs a sequence of statements repeatedly until an EXIT statement terminates it. A numeric FOR loop performs a series of statements repeatedly for a specified number of iterations. The WHILE loop tests a Boolean expression and executes the statements as long as the expression evaluates to TRUE. This How-To introduces conditional and looping constructs in PL/SQL..

10.3 Create a Stored Procedure.

A stored procedure is a named PL/SQL block stored in the database, making itself accessible to applications. Parameters can be passed to and from stored procedures, creating modular units of reusable code. A standalone statement is used to call a stored procedure in PL/SQL modules. This How-To presents the technique used to create a stored procedure and call it from a PL/SQL block..

10.4 Create a Stored Function.

Parameters can be passed to and from a stored function, making it similar to a stored procedure, except that it must be invoked as part of an expression. The return value can be a standard datatype, record variable, and, since PL/SQL version 2.3, a cursor variable. This How-To presents the technique used to create a stored function and calls it from a PL/SQL block..

10.5 Create a Stored Package.

A stored package groups related procedures and functions into a single package. Grouping related subprograms into packages extends an object-oriented approach to the development of stored procedures and functions. A package can contain its own local procedures, variables, and datatypes and expose only specified objects to the user. This How-To offers a framework for creating stored packages..

10.6 List Information About Stored Objects in the Database.

The USER_OBJECTS data dictionary view can be queried to reveal useful information about stored objects. The USER_OBJECT_SIZE view lets you analyze the size of stored modules. The source code of stored modules can be queried from the USER_SOURCE view. Object dependencies can be listed from the USER_DEPENDENCIES view and the USER_ERRORS view can be used to display compilation errors. This How-To presents queries to examine stored objects..

10.7 Overload Procedures and Functions in Packages.

Procedure and function overloading is a useful technique, enabling the same procedure or function to accept multiple parameter lists. Overloading is used extensively in Oracle to make procedures and functions work with parameters of varying datatypes. This How-To presents the method used to overload procedures and functions within packages..

10.8 Create Variables to Represent Database Records and Columns.

The %TYPE and %ROWTYPE attributes define the datatype of a variable in the declarative section of a block. The %TYPE attribute declares a variable to represent a column within a table, ensuring that the variable datatype can handle the values in the column. The %ROWTYPE attribute creates a record variable representing all the columns in a record. This How-To uses these attributes to declare variables representing database records, columns, and rows..

10.9 Achieve Array Functionality with PL/SQL Index-By Tables.

PL/SQL Index-By tables are similar to one-dimensional arrays in other procedural languages. The implementation of a PL/SQL Index-By table requires the creation of a datatype and a variable declaration of the datatype created. This How-To presents the method for creating a PL/SQL table and using it like an array..

10.10 Handle Predefined Exceptions and System Errors.

Exception handlers in the exception section of a block handle system errors and user-defined errors. Unhandled exceptions cause abnormal termination of a PL/SQL block, and system errors cause predefined exceptions to occur. This How-To covers the process of handling predefined exceptions and system errors without predefined exceptions..

10.11 Handle User-Defined Exceptions and User-Defined Errors.

An exception does not have to be a system error returned by Oracle. It is possible to create a user-defined exception, which can be raised and handled with a RAISE statement. User-defined exceptions enable you to treat a violation of business rules as an exception. The built-in procedure RAISE_APPLICATION_ERROR enables you to raise an exception with a user-defined error number and user-defined error message. When an exception occurs in a stored subprogram, the exception must be handled either in the subprogram or in the calling PL/SQL code. This How-To presents the method used to create, raise, and handle user-defined exceptions..

10.12 Rebuild Statements to Create Stored Modules.

As you have done throughout the book, you can use the data dictionary and SQL*Plus to rebuild DDL statements. Because the source code for stored modules is contained in the data dictionary, it is possible to rebuild the statements used to create existing stored modules. This How-To presents a query to generate DDL statements to create stored modules in addition to a query that enables you to recompile stored modules in your schema..