 |
|
How Does the Database
Parse SQL and PL/SQL?
Oracle Tips by
Burleson
|
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.
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.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
