045-049

Previous

Table of Contents

Next

An Overview Of PL/SQL

The Procedural Language extension to SQL (PL/SQL) was first introduced in late 1991 and dramatically reshaped the role of the Oracle developer. PL/SQL literally made the impossible possible for SQL developers; for the first time, they could develop complex applications without using a 3GL program from inside Oracle*Forms.

PL/SQL introduced several abilities to the Oracle developer, including:

   Looping structures

   The ability to embed SQL statements inside PL/SQL code

    IF-THEN-ELSE logic

   The ability to deal with multiple rows of data

   A robust method of handling errors

In its first incarnation, PL/SQL was used only inside Oracle*Forms 3.0 (in special blocks called procedures). Triggers inside a form could execute both SQL DML statements and PL/SQL procedures (which in turn could execute SQL DML statements and other PL/SQL procedures). Today, PL/SQL has matured considerably, adding many new features and becoming more tightly integrated with the Oracle database.

PL/SQL code runs in any Oracle database on any hardware platform, making the code highly transportable. Applications developed for your Personal Oracle database can easily be moved up to larger machines and vice versa.

Blocks

PL/SQL is written in sections called blocks. Listing 2.10 shows the structure of a typical PL/SQL block.

Listing 2.10 A sample PL/SQL block.

 DECLARE    <variable declarations> BEGIN    <statements> EXCEPTION    <error condition>        <code for handling error> END; 

Blocks of PL/SQL code may be nested within each other to form sub-blocks, as shown in Listing 2.11.

Listing 2.11 A sample PL/SQL block with a sub-block.

 DECLARE    <variable declarations> BEGIN    <statements>    DECLARE       <variable declarations>    BEGIN       <statements>    EXCEPTION       <error condition>           <code for handling error condition>    END;    <statements> EXCEPTION    <error condition>        <code for handling error> END; 

In practice, there is no limit to how far PL/SQL blocks may be nested within each other (although too many levels of indentation becomes confusing).

The structure of a PL/SQL block is simple. The DECLARE statement is followed by variable, constant, and other definitions. You can then manipulate variables and data following the BEGIN statement. The EXCEPTION statement (errors in PL/SQL are called exceptions ; this term originated within Ada, PL/SQL s root language) allows you to define code to handle specific error conditions. The END statement signifies the end of the PL/SQL block.

Not every PL/SQL block will have a DECLARE statement. This is especially true of sub-blocks, because variables inside a block cease to exist once the block is closed.

Cursors

A cursor is a reference to a private SQL area inside Oracle. Figure 2.7 illustrates the functionality of a cursor.

Figure 2.7    The functionality of a cursor.

There are two types of cursors: implicit and explicit. PL/SQL also provides a CURSOR FOR loop that makes the process of looping through the cursor data simpler.

Explicit Cursors

An explicit cursor is defined in the DECLARE section of a PL/SQL block, as shown in Listing 2.12.

Listing 2.12 The declaration of an explicit cursor.

 DECLARE    CURSOR Students_cur    IS    SELECT last_name, first_name, middle_name, gpa    FROM   STUDENTS    WHERE  gpa > 3.0; 

The use of explicit cursors allows PL/SQL programs to handle sets of rows, rather than being forced to query a single row for each operation. Each row that meets the criteria defined by the cursor s WHERE clause can be processed individually.

Several PL/SQL statements are used to deal with explicit cursors:

    OPEN ”Parses a cursor and prepares the result set for retrieval. If the cursor is already open, an error will occur.

    CLOSE ”Disables the cursor. If the specified cursor hasn t been opened, an error will result.

    FETCH ”Retrieves the next row of the result set into variables for processing inside the PL/SQL block. If the specified cursor hasn t been opened, an error will occur.

A cursor is not an array; once a row has been bypassed using the FETCH statement, it is impossible to go backwards inside the cursor. The only way to get back to a row that has been bypassed is to close and reopen the cursor.

The CURSOR FOR Loop

The CURSOR FOR loop allows you to handle cursors without using the OPEN , FETCH , and CLOSE statements. Listing 2.13 shows a CURSOR FOR loop.

Listing 2.13 A CURSOR FOR loop.

 DECLARE    CURSOR Students_cur    IS    SELECT last_name, first_name, middle_name, gpa    FROM   STUDENTS    WHERE  gpa > 3.0; BEGIN    FOR Students_rec IN Students_cur LOOP        <statements>    END LOOP; END; 

This type of loop automatically opens the cursor, loops through each row in the result set, and closes the cursor when the last row has been processed. In this example, the loop index variable Students_rec is implicitly declared by the cursor for loop as a %ROWTYPE variable based on the structure of the cursor ( %ROWTYPE will be discussed later in this chapter).

Cursors can be used with any type of loop available in PL/SQL; the use of CURSOR FOR loops is not required.

Implicit Cursors

An implicit cursor is created by Oracle for all SQL statements that manipulate data (even if the query returns only a single row). If a query has a chance of returning multiple rows, it s a good idea to use an explicit cursor to retrieve the rows instead of using a single SELECT statement.

Previous

Table of Contents

Next