PL/SQL Basic Syntax

All PL/SQL has the same basic format: a DECLARE section where cursors and variables are defined, a BEGIN, and an END where the SQL syntax and PL/SQL code is put. This is known as a PL/SQL block. Exception or exception handling is errors or flags in PL/SQL. Exceptions are a way of gracefully handling certain conditions within the PL/SQL block, such as “no records returned from the SQL query” or “last record returned from the SQL query”. Other exceptions can be syntax errors in the PL/SQL code itself.

There are two types of PL/SQL blocks: the anonymous block is the basic unnamed PL/SQL block and is used in SQL*Plus, 3GL programs such as C or Cobol, and so on; and named PL/SQL blocks can be declared as procedures or functions. These named PL/SQL blocks can reside on the server or at the PC (client side), and can be called (or executed) from other PL/SQL blocks (either anonymous or named). Both procedures and functions can accept input variables, but only functions can return values.

Start the SQL Navigator tool from the Start menu. From the SQL Navigator menu bar, select New Editor, the Stored Program Editor. This will display a box illustrated by Figure 3.3. Give the procedure a name and click OK. This will create a basic PL/SQL block for a procedure, as illustrated in Figure 3.4. Notice the BEGIN, EXCEPTIONS, and END clauses (the END is hidden from view, use the vertical scroll bar on the right side of the edit window to see the remainder of the code) are automatically created.

There are also two types of triggers that utilize PL/SQL blocks. Database triggers are defined on a table to perform certain functions at certain times. These triggers execute when certain events occur such as before or after a record is inserted.

Figure 3.5 shows the various database trigger levels supported by Oracle9i. This trigger edit window in SQL Navigator is accessed by clicking on File, then NEW Editor, and then Trigger. Notice the timing box determines whether this database trigger will fire before or after the action determined in box triggering event. The Fire for box determines if the trigger will be executed for each row affected by the DML SQL statement or just once per DML SQL statement. Application triggers are also executed automatically and are used by Developer v6i. Chapter 4, “Building an Oracle9i Database,” will discuss their use and illustrate how to create them.

PL/SQL Variables

PL/SQL variables are used throughout PL/SQL. They are used to store data, store the results of a calculation, store the results of a SQL query, and manipulate the data in other variables. The syntax in PL/SQL variables is rather simple: var_name [CONSTANT] datatype [NOT NULL] [:= var/calc/expression]. The var name must be unique to the PL/SQL block. The CONSTANT option will not allow the variable to change throughout the PL/SQL code execution. This feature would be handy where the PL/SQL block might be subject to somewhat regular code changes.

The datatype follows the SQL standards. The := is the method of assigning a value to the variable. The value can be set equal to a constant, the result of a calculation, or the result of a SQL query. Oracle developers follow some simple guidelines to insure a unique name for variables and give other Oracle developers an idea of the nature of the variable. Table 3.1 shows some common naming conventions used by Oracle developers.

The same variable name can be used inside nested PL/SQL blocks (PL/SQL within PL/SQL) and the name of the variable used by the PL/SQL block will be the locally defined variable, or that variable declared in that PL/SQL block.

For example, Figure 3.6 shows variable v_avar in the outer PL/SQL block and v_bvar in the inner or nested PL/SQL block. Variable v_bvar can reference v_avar but v_avar CANNOT reference v_bvar. The code in the grey box in Figure 3.6 is invalid and will generate an error because the outer PL/SQL block is making reference to a variable defined in the inner PL/SQL block. To save and execute the code block, click on the green triangular box on the bottom tool bar.

Figure 3.8 illustrates naming some variables and populating them with values. The %TYPE and %ROWTYPE are special datatypes that will retrieve and use the datatype for a particular column from the database (object-oriented inheritance, discussed in Chapter 1, “Introducing Oracle9i”). %TYPE is useful to guarantee that the datatype and length of the variable matches that of a column in the database. Notice the INTO clause in the SQL statement. This is how data is passed from the tables to the PL/SQL variables.

Figure 3.9 shows how to execute the newly developed procedure in DEBUG mode. This will allow you to stop the execution of the code, see the contents of variables, and change the contents of the variables. There are a couple of ways to set a break point: you can left-click the line where you want execution to stop, then press the right mouse button and select Toggle Breakpoint. This will highlight the whole line in yellow; or you can press the Toggle Breakpoint button on the tool bar next to the X=? button. The button with a down arrow (complete left on same line of tool bar) will run the procedure to the first break point. Because this is a procedure, SQL Navigator will start a Calling Code Generation Wizard, just click next and finish, taking the defaults. When SQL Navigator hits the break point, it will stop and display the line of code at the break point.

Now you can move the mouse cursor over a variable and its contents will display monetarily and also display in the status bar at the bottom of the screen, as shown in Figure 3.9. Figure 3.10 shows the Evaluate/Modify variable (by pressing the X=? button with the cursor over the desired variable) that not only shows the contents of the variable but also allows you the opportunity to change the contents.

This breakpoint/debug mode is very useful in visualizing returned rows later when we learn about loops and cursors.

Like %TYPE, %ROWTYPE is convenient to use to guarantee the same datatypes are used but also all the columns of a particular table are used. This too is a form of object-oriented inheritance. This feature will guarantee the number of columns and the associated data types always match that of a particular table. Figure 3.11 illustrates a practical use for %ROWTYPE. Notice how v_ename and v_sal are set by qualifying the columns returned from emp with the emp_record prefix.

Boolean variables are useful for storing the results of a calculation, a comparison, or simply holding a TRUE/FALSE/NULL value.

Variables can also be passed both to and from the SQL*Plus environment. Notice the :g_dept_sal is populated by the simple query in the PL/SQL block but that %DEPTNO is passed to the PL/SQL block. This is a classic use of bind variables and is an efficient way of coding SQL statements within PL/SQL blocks. Figure 3.12 shows this functionality from within SQL*Plus. Notice the SQL*Plus PRINT command is useful for displaying the contents of a variable. PL/SQL has no such feature as PRINT. Notice that Figure 3.13 shows how to reference SQL*Plus variables and display results with the use of SQL*Plus environment setting SET SERVEROUTPUT ON is set and the PL/SQL feature DBMS_OUTPUT.PRINT_LINE is used.

PL/SQL Cursors

Cursors are Oracle's way of processing SQL statements, checking their syntax (known as parsing), substituting any bind variables, finding the path to the data (known as the explain plan), and eventually executing and assigning a buffer to hold the row or rows returned.

There are two kinds of cursors in Oracle9i: implicit cursors and explicit cursors. An implicit cursor is one (illustrated by Figure 3.14) where a SQL statement is defined without any cursor control statements such as use of the CURSOR statement (see Figure 3.15). SQL queries in implicit cursors can only return one row. DML statements can process any number of rows. Explicit cursors are for those SQL queries that return more than one row. These cursors have a command structure so that the PL/SQL block can control the return of data or rows from the database.

SQL Navigator has a code assistant that is useful in defining the basic syntax. This code assistant can be accessed either via the menu bar (Tools Code Assistant) or by the Code Assistant button on the top tool palette. The code assistant has code syntax layouts for about any programming task desired. Notice Figure 3.15 uses the Syntax & Web Catalog tab. Simply left-click and hold (drag and drop) on the desired code feature and move the mouse pointer (it will pull a shadow object) to the desired part of the PL/SQL block in the Editor window. This will put the PL/SQL code fragments into the Editor window where you can then change the black type items to the values desired. Figure 3.16 shows the results after modifications.

Notice that the implicit cursor has the INTO clause in the SELECT statement (refer to Figure 3.14) where the explicit cursor does not. Also notice that the explicit cursor has a name, must be opened, and closed, and puts the INTO clause in the FETCH statement to populate the PL/SQL variables with data. The columns listed in the SELECT clause are in the same order as listed in the FETCH clause.

Implicit and explicit cursors have some attributes that contain information about the cursor function. The following list shows the cursor attributes, and Figure 3.17 shows the logic flow of defining and using explicit cursors. These attributes play a major role of cursor control as we will learn in the next section of this chapter.

• %ISOPEN       Returns a TRUE if the cursor is open.
• %NOTFOUND     Returns a TRUE if row is not returned.
• %FOUND        Returns a TRUE if a roe is returned.
• %ROWCOUNT     Contains number or rows returned so far or number of rows processed by the SQL statement.

PL/SQL Logic Control

There are two ways to control the flow of logic within a PL/SQL block: the IF-THEN-ELSE and LOOPING. These commands can be intermixed, depending on the requirements of the logic needed.

IF-THEN-ELSE

The IF statement allows for the checking of contents of a variable and performing additional instructions based on whether the condition tested TRUE or FALSE. If the condition tested true, the THEN clause is then followed. If the condition tested false, then the ELSIF or ELSE is followed.

The syntax is simple: IF <condition> THEN statement[s]; [ELSIF condition THEN statement[s];] [ELSE statement[s];] END IF; A simple IF statement might not have any ELSE conditions at all. Figure 3.18 illustrates the program logic flow of the IF-THEN-ELSE and Figure 3.19 illustrates a more complex IF-THEN-ELSIF-ELSE program logic.

Listing 3.1 shows a couple of different IF statement usages. Notice the first IF statement simply checks one value. Each IF statement needs an END IF statement. The second IF statement in Listing 3.1 shows the use of the ELSIF statement. Notice that this code is easier to read and easier to code because of the lack of additional END IF statements.

Listing 3.1. IF Statement Examples

...
IF job = 'SALESMAN' THEN
     v_raise := sal * .10;
ELSE
     IF job = 'CLERK' THEN
          v_raise := sal * .15
     END IF;
END IF;

..
IF job = 'SALESMAN' THEN
     v_raise := sal * .10;
ELSIF job = 'CLERK' THEN
     v_raise := sal * .15
ELSE
     v_raise = sal *.20
END IF;

LOOPING

PL/SQL provides three types of mechanisms used for repeating processes or looping: the basic loop, the WHILE loop, and the FOR loop. Each loop has some kind of an exit statement or a way to stop looping.

The basic loops syntax is LOOP statement[s]; EXIT [WHEN <condition>]; END LOOP; The EXIT checks for a condition to test TRUE and will then leave the loop and execute the next PL/SQL statement or SQL statement that follows the END LOOP statement. Figure 3.20 contains a basic loop example.

The FOR and WHILE loops handle when to exit in the FOR or WHILE clause. The syntax for the FOR loop is FOR counter IN low-range.high-range LOOP statement[s]; END LOOP;

The WHILE loop loops while some condition tests TRUE. The syntax is WHILE <condition> LOOP statement[s]; END LOOP;

Figure 3.21 shows the FOR loop in use with explicit cursors. In this example, using the FOR, an implicit open and fetch are performed. An implicit close is performed on the END LOOP. PL/SQL will automatically perform this loop while there are rows in the cursor.