Previous | Table of Contents | Next

Page 165

CHAPTER 8

Creating Your First
PL/SQL Program

This chapter introduces the PL/SQL language. It starts with a look at the basic PL/SQL block. The complexity of the topics increases with the coverage of variables, cursors, loops, and error handling.

Page 166

Understanding PL/SQL

PL/SQL is Oracle's procedure language or programming language. It is very similar to other programming languages such as BASIC, FORTRAN, C, or COBOL. PL/SQL enables you to define local variables, acquire data from the database, process the data, and return it to the database. You can record specific instructions in PL/SQL that tell your applications how to act. PL/SQL has a wealth of tools that greatly enhance the processing of records. PL/SQL has looping statements that enable you to perform the same function a number of times. It has condition logic that enables you to process records when certain conditions are met. It has cursors that enable you to move sets of records into memory and process them one at a time. It has techniques that enable you to easily assign values to local variables. You can use SQL commands illustrated in Chapters 3_7 to acquire records and modify the database. PL/SQL is a powerful tool for performing complex processing tasks.

PL/SQL is the programming language used in most of the Oracle products. It is a very effective tool for converting data from an old database to a new database. The procedures can be written in Windows Notepad and executed from SQL*PLUS. The programs read records from the Oracle database by using a cursor. The values in the records are placed in the local variables. The variables are processed using condition statements. The data is then placed into the new tables.

You can also use PL/SQL in Oracle Forms to customize the behavior of the form. In addition to data manipulation statements, PL/SQL can contain built-in subprograms. They perform special functions in a form such as navigating to the next record. PL/SQL can be tied to events that can occur on the form. When the event, such as inserting a record occurs, the PL/SQL commands associated to the event are executed.

NOTE
Built-in subprogram is a special procedure developed by Oracle to perform a special function on a form. An example of a built-in subprogram is next_item, which moves the cursor to the next field on a form.

Most Oracle products use PL/SQL to some degree. They are used to perform calculations, format items, or to customize the application. Without a strong knowledge of the PL/SQL language, you limit your abilities to customize your form applications.

Understanding the PL/SQL Blocks

PL/SQL statements are generally contained in code blocks. The code block consists of four sections:

Page 167

NOTE
Exception handler is a statement included in the Exception section of the PL/SQL block.
It begins with the word when followed by the name of the exception it is to handle. An example of an exception name is no_data_found. If the exception occurs, the processor performs the commands in the handler.

There are three types of PL/SQL blocks (see Figure 8.1). The first is an anonymous block that cannot be called by another application. This type of block does not have a header section. The other two types of blocks, procedures and functions, must have a header section because they may be called by another application. The difference between the two is that functions return a value and procedures do not. This chapter covers anonymous blocks. The next chapter covers procedures and functions. Procedures and functions have additional rules and functionality that do not apply to anonymous blocks. PL/SQL block statements must end with a semi-colon. The keywords Declare, Begin, and Exception identify the beginning of a block section. Semi-
colons do not follow these words because they are not a statement or command. In an anonymous block, the first word is Declare. It denotes the beginning of the section where local variable and cursors are declared. When the program does not contain these items, the section does not have to be included in the block. The Executable section starts with the word Begin. All blocks must have an Executable section or they have no purpose. The Exception section begins with the keyword Exception. It is placed after the instructions in the Executable section. It is optional and does not have to be in the block. The word end completes the block.

FIG. 8.1
Format of the PL/SQL
block.


Previous | Table of Contents | Next