068-071

Previous

Table of Contents

Next

The case/switch Statement

While not an essential part of any language, the case statement is more elegant than:

 IF (x < 100) THEN    <statements> ELSIF (X <200) THEN    <statements> ELSIF (x < 300) THEN    <statements> ELSE    <statements> END IF; 

Unfortunately, PL/SQL allows only the clumsy IF-THEN-ELSE method of testing multiple conditions.

Incrementing Variables

The ability to increment or decrement a variable in C using the ++ , -- , += , and -= operators is something that I ve often wished for while coding a procedure or function. Instead, you ll have to settle for the less elegant method shown here:

 nSum := nSum + 1; 

While functionally equivalent, the previous example simply seems less graceful than a similar C statement:

 nSum++; 

You can create a stored function that accepts an integer value and returns the value incremented by one, although it hardly seems worth the effort.

How Does The Database Parse SQL And PL/SQL?

Sit down in front of a computer and get to an SQL prompt. Get comfortable. Then execute the following commands at the SQL prompt:

 set serveroutput on SELECT 23 FROM DUAL; 

Notice that as soon as you finished typing your command, SQL*Plus sent the command off to the database for a response. Now enter this block of PL/SQL code:

 DECLARE    iRowCount      integer; BEGIN    SELECT 23    INTO   iRowCount    FROM DUAL;    SELECT 18    INTO   iRowCount    FROM DUAL;    DBMS_Output.Put_Line ('The value of iRowCount is '  to_char (iRowCount)); END; / 

You might notice that none of your SQL commands were executed until you finished your PL/SQL block and told SQL to execute the block with the / character.

You may sit in front of SQL every day and not have noticed this difference before, but you should notice it now. The Oracle7 database handles SQL and PL/SQL commands differently. Each SQL statement inside your PL/SQL block was sent to the database only when the PL/SQL block went to the database. Figure 2.12 illustrates this concept.

Figure 2.12    How Oracle7 parse SQL and PL/SQL statements.

An SQL statement is immediately matched against existing statements in the SGA. If no matching SQL statement is found, the statement is reparsed and then executed; otherwise , a statement that is already cached in the SGA is executed instead. When the database has retrieved the necessary data or an error occurs, the database returns a response to the user .

A PL/SQL block is sent to the database as a whole concept. Because a PL/SQL block can contain both SQL statements and other PL/SQL blocks, the database will then handle each SQL statement and each PL/SQL block inside the main PL/SQL block. When the procedure has finished executing or an error condition occurs, a response is given to the user who executed the procedure.

This difference may seem to be insignificant, but the nature of PL/SQL makes it much better suited for client/server development than SQL. Each SQL statement travels over the network singly , while PL/SQL sends one block of code to the database, which then handles PL/SQL sub-blocks, embedded SQL statements, and calls to stored PL/SQL objects internally.

Consider 10 SQL statements sent over a network individually versus 1 PL/SQL block that contains 10 embedded SQL statements; 1 call to the network is necessary to send the PL/SQL block, while 10 calls are required for the individual SQL statements. Furthermore, the result sets for the embedded SQL statements are not sent back across the network, further reducing the number of packets required to complete the PL/SQL call.

Summary

Hopefully, this chapter has given you a basic grounding for the coming chapters, which contain some rather detailed examples of SQL and PL/SQL code. I have tried to avoid lapsing into a primer on syntax, but in some cases an adequate explanation demands the most elementary terms. By no means should you consider this chapter to be a complete tutorial or reference on SQL or PL/SQL; for that type of material the best sources are the Oracle7 Server SQL Language Reference Manual and the PL/SQL User s Guide that you can borrow from your database administrator.

In the coming chapters, you ll cover not only some detailed examples of SQL and PL/SQL code, but you ll also be given insight into the design and testing of these procedures.

Previous

Table of Contents

Next