Page 165
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
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.
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. |
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.