SQL Clearly Explained- P7

SQL Clearly Explained- P7

SQL Clearly Explained- P7: You don’t need to be a database designer to use SQL successfully.
However, you do need to know a bit about how relational
databases are structured and how to manipulate those
structures.

Nội dung Text: SQL Clearly Explained- P7

308 Chapter 15: Embedded SQL
else
{
EXEC SQL COMMIT;
// display error message
}
// continue processing
There are three things to note about the COMMIT statement
in this code:
◊ The COMMIT must be issued after checking the SQL-
STATE. Otherwise, the COMMIT will change the val-
ue in SQLSTATE.
◊ There is no need to roll back a retrieval transaction, so
the code commits the transaction even if the retrieval
fails.
◊ The COMMIT could be placed after the IF construct.
However, depending on the length of the code that fol-
lows error checking, the transaction may stay open lon-
ger than necessary. Therefore, the repeated COMMIT
statement is an efficient choice in this situation.
Indicator Variables The SQLSTATE variable is not the only way in which a
DBMS can communicate the results of a retrieval to an appli-
cation program. Each host variable into which you place data
can be associated with an indicator variable. When indicator
variables are present, the DBMS stores a 0 to indicate that a
data variable has valid data of a –1 to indicate that the row
contained a null in the specified column and that the contents
of the data variable are unchanged.
To use indicator variables, first declare host language variables
of an integer data type to hold the indicators. Then, follow each
data variable in the INTO clause with the keyword INDICA-
TOR and the name of the indicator variable. For example, to
use indicator variables with the customer data retrieval query:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Retrieving Multiple Rows: Cursors 309
EXEC SQL SELECT first_name, last_name,
contact_phone
INTO :da_first INDICATOR :in_first,
:da_last INDICATOR :in_last,
:da_phone INDICATOR :in_phone
FROM customer
WHERE customer_numb = 12;
You can then use host language syntax to check the contents
of each indicator variable to determine whether you have valid
data to process in each data variable.
Note: The INDICATOR keyword is optional. Therefore, the syn-
tax INTO :first :ifirst, :last :ilast, and so on is acceptable.
Indicator variables can also be useful for telling you when char-
acter values have been truncated. For example, assume that
the host language variable first has been declared to accept a
10-character string but that the database column first_name is
15 characters long. If the database column contains a full 15
characters, only the first 10 will be placed in the host language
variable. The indicator variable will contain 15, indicating the
size of the column (and the size to which the host language
variable should have been set).
SELECT statements that may return more than one row pres-
ent a bit of a problem when you embed them in a program.
Retrieving
Host language variables can hold only one value at a time and Multiple Rows:
the SQL command processor cannot work with host language
arrays. The solution provides you with a pointer (a cursor) to Cursors
a SQL result table that allows you to extract one row at a time
for processing.
The procedure for creating and working with a cursor is as
follows:
1. Declare the cursor by specifying the SQL SELECT to
be executed. This does not perform the retrieval.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

310 Chapter 15: Embedded SQL
2. Open the cursor. This step actually executes the SE-
LECT and creates the result table in main memory.
It positions the cursor just above the first row in the
result table.
3. Fetch the next row in the result table and process the
data in some way.
4. Repeat step 3 until all rows in the result table have
been accessed and processed.
5. Close the cursor. This deletes the result table from main
memory but does not destroy the declaration. You can
therefore reopen an existing cursor, recreating the re-
sult table, and work with the data without redeclaring
the SELECT.
If you do not explicitly close a cursor, it will be closed au-
tomatically when the transaction terminates. (This is the de-
fault.) If, however, you want the cursor to remain open after
a COMMIT, then you add a WITH HOLD option to the
declaration.
Even if a cursor is held from one transaction to another, its re-
sult table will still be deleted at the end of the database session
in which it was created. To return that result table to the call-
ing routine, add a WITH RETURN option to the declaration.
Note: There is no way to “undeclare” a cursor. A cursor’s declara-
tion disappears when the program module in which it was created
terminates.
By default, a cursor fetches the “next” row in the result table.
However, you may also use a scrollable cursor to fetch the
“next,” “prior,” “first,” or “last” row. In addition, you can fetch
by specifying a row number in the result table or by giving an
offset from the current row. This in large measure eliminates
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Retrieving Multiple Rows: Cursors 311
the need to close and reopen the cursor to reposition the cursor
above its current location.
Declaring a cursor is similar to creating a view in that you Declaring a Cursor
include a SQL statement that defines a virtual table. The DE-
CLARE statement has the following general format in its sim-
plest form:
DECLARE cursor_name CURSOR FOR
SELECT remainder_of_query
For example, assume that someone at the rare book store
wanted to prepare labels for a mailing to all its customers. The
program that prints mailing labels needs each customer’s name
and address from the database, which it can then format for
labels. A cursor to hold the data might be declared as
EXEC SQL DECLARE address_data CURSOR FOR
SELECT first_name, last_name, street, city,
state_province, zip_postcode
FROM customer;
The name of a cursor must be unique within the program
module in which it is created. A program can therefore ma-
nipulate an unlimited number of cursors at the same time.
One of the options available with a cursor is the ability to re- Scrolling Cursors
trieve rows in other than the default “next” order. To enable
a scrolling cursor, you must indicate that you want scrolling
when you declare the cursor by adding the keyword SCROLL
after the cursor name:
EXEC SQL DECLARE address_data SCROLL CURSOR FOR
SELECT first_name, last_name, street,
city, state_province, zip_postcode
FROM customer;
You will find more about using scrolling cursors a bit later in
this chapter when we talk about fetching rows.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

312 Chapter 15: Embedded SQL
Enabling Updates The data in a cursor are by default read only. However, if the
result table meets all updatability criteria, you can use the cur-
sor for data modification. (You will find more about the updat-
ability criteria in the Modification Using Cursors section later
in this chapter.)
To enable modification for a customer, add the keywords FOR
UPDATE at the end of the cursor’s declaration:
EXEC SQL DECLARE address_data SCROLL CURSOR FOR
SELECT first_name, last_name, street, city,
state_province, zip_postcode
FROM customer
FOR UPDATE;
To restrict updates to specific columns, add the names of col-
umns following UPDATE:
EXEC SQL DECLARE address_data SCROLL CURSOR FOR
SELECT first_name, last_name, street, city,
state_province, zip_postcode
FROM customer
FOR UPDATE street, city, state_province,
zip_postcode;
Sensitivity Assume, for example, that a program for the rare book store
contains a module that computes the average price of books
and changes prices based on that average: If a book’s price is
more than 20 percent higher than the average, the price is dis-
counted 10 percent; if the price is only 10 percent higher, it is
discounted 5 percent.
A programmer codes the logic of the program in the following
way:
1. Declare and open a cursor that contains the inventory
IDs and asking prices for all volumes whose price is
greater than the average. The SELECT that generates
the result table is
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Retrieving Multiple Rows: Cursors 313
SELECT inventory_id, asking_price
FROM volume
WHERE asking_price >
(SELECT AVG (asking_price)
FROM volume);
2. Fetch each row and modify its price.
The question at this point is: What happens in the result table
as data are modified? As prices are lowered, some rows will
no longer meet the criteria for inclusion in the table. More
important, the average retail price will drop. If this program is
to execute correctly, however, the contents of the result table
must remain fixed once the cursor has been opened.
The SQL standard therefore defines three types of cursors:
◊ Insensitive: The contents of the result table are fixed.
◊ Sensitive: The contents of the result table are updated
each time the table is modified.
◊ Indeterminate (asensitive): The effects of updates made
by the same transaction on the result table are left up to
each individual DBMS.
The default is indeterminate, which means that you cannot be
certain that the DBMS will not alter your result table before
you are through with it.
The solution is to request specifically that the cursor be
insensitive:
EXEC SQL DECLARE address_data SCROLL
INSENSITIVE CURSOR FOR
SELECT first_name, last_name, street, city,
state_province, zip_postcode
FROM customer
FOR UPDATE street, city, state_province,
zip_postcode;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

314 Chapter 15: Embedded SQL
Opening a Cursor To open a cursor, place the cursor’s name following the key-
word OPEN:
EXEC SQL OPEN address_data;
Fetching Rows To retrieve the data from the next row in a result table, plac-
ing data into host language variables, you use the FETCH
statement:
FETCH FROM cursor_name
INTO host_language_variables
For example, to obtain a row of data from the list of customer
names and addresses, the rare book store’s program could use
EXEC SQL FETCH FROM address_data
INTO :da_first, :da_last, :da_street, :da_city,
:da_state_province, :da_zip_postcode;
Notice that as always the host language variables are preceded
by colons to distinguish them from table, view, or column
names. In addition, the host language variables must match
the database columns as to data type. The FETCH will fail if,
for example, you attempt to place a string value into a numeric
variable.
If you want to fetch something other than the next row, you
can declare a scrolling cursor and specify the row by adding
the direction in which you want the cursor to move after the
keyword FETCH:
◊ To fetch the first row
EXEC SQL FETCH FIRST FROM
address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Retrieving Multiple Rows: Cursors 315
◊ To fetch the last row
EXEC SQL FETCH LAST FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
◊ To fetch the prior row
EXEC SQL FETCH PRIOR FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
◊ To fetch a row specified by its position (row number) in
the result table
EXEC SQL FETCH ABSOLUTE 12
FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
The preceding fetches the twelfth row in
the result table.
◊ To fetch a row relative to and below the current position
of the cursor
EXEC SQL FETCH RELATIVE 5
FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
The preceding fetches the row five rows be-
low the current position of the cursor (cur-
rent position + 5).
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

316 Chapter 15: Embedded SQL
◊ To fetch a row relative to and above the current position
of the cursor
EXEC SQL FETCH RELATIVE -5
FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
The preceding fetches the row five rows
above the current position of the cursor
(current row – 5).
Note: If you use FETCH without an INTO clause, you will move
the cursor without retrieving any data.
If there is no row containing data at the position of the cursor,
the DBMS returns a “no data” error (SQLSTATE = ‘02000’).
The general strategy for processing a table of data is therefore to
create a loop that continues to fetch rows until a SQLSTATE
of something other than ‘00000’ occurs. Then you can test
to see whether you’ve simply finished processing or whether
some other problem has arisen. In C/C++, the code would
look something like Figure 15-1.
EXEC SQL FETCH FROM address data
INTO :da_first, :da_last, :da_street, :da_city, :da_state_province,
:da_zip_postscode;
while (strcmp (SQLSTATE, “00000”) == 0)
{
// Process one row’s data in appropriate way
EXEC SQL FETCH FROM address data
INTO :da_first, :da_last, :da_street, :da_city, :da_state_province,
:da_zip_postscode;
)
if (strcmp (SQLSTATE, “0200000”) != 0
{
// Display error message and/or do additional error checking
}
EXEC SQL COMMIT;
Figure 15-1: Using a host language loop to process all rows in an embedded SQL result table
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Embedded SQL Data Modification 317
Note: One common error that beginning programmers make is
to write loops that use a specific error code as a terminating val-
ue. This can result in an infinite loop if some other error condi-
tion arises. We therefore typically write loops to stop on any error
condition and then check to determine exactly which condition
occurred.
Note: You can use indicator variables in the INTO clause of a
FETCH statement, just as you do when executing a SELECT that
retrieves a single row.
To close a cursor, removing its result table from main memory, Closing a Cursor
use
CLOSE cursor_name
as in
EXEC SQL CLOSE address_data;
Although many of today’s database development environments
make it easy to create forms for data entry and modification,
Embedded
all those forms do is collect data. There must be a program of SQL Data
some type underlying the form to actually interact with the
database. For example, whenever a salesperson at the rare book Modification
store makes a sale, a program must create the row in sale and
modify appropriate rows in volume.
Data modification can be performed using the SQL UPDATE Direct Modification
command to change one or more rows. In some cases, you can
use a cursor to identify which rows should be updated in the
underlying base tables.
To perform direct data modification using the SQL UPDATE
command, you simply include the command in your program.
For example, if the selling price of a purchased volume is stored
in the host language variable da_selling_price, the sale ID in
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

318 Chapter 15: Embedded SQL
da_sale_id, and the volume’s inventory ID in da_inventory_id,
you could update volume with
EXEC SQL UPDATE volume
SET selling_price = :da_selling_price,
sale_id = :da_sale_id
WHERE inventory_id = :da_inventory_id;
The preceding statement will update one row in the table be-
cause its WHERE predicate contains a primary key expres-
sion. To modify multiple rows, you use an UPDATE with a
WHERE predicate that identifies multiple rows, such as the
following, which increases the prices by two percent for vol-
umes with leather bindings:
EXEC SQL UPDATE volume
SET asking_price = asking_price * 1.02
WHERE isbn IN (SELECT isbn
FROM book
WHERE binding = “Leather’);
Indicator variables, which hold information about the result of
Indicator Variables embedded SQL retrievals, can also be used when performing
and Data embedded SQL modification. Their purpose is to indicate that
you want to store a null in a column. For example, assume that
Modification the rare book store has a program that stores new rows in the
volume table. At the time a new row is inserted, there are no
values for the selling price or the sale ID; these columns should
be left null.
To do this, the program declares an indicator variable for each
column in the table. If the data variable hold a value to be
stored, the program sets the indicator variable to 0; if the col-
umn is to be left null, the program sets the indicator variable
to –1.
Sample pseudocode for performing this embedded INSERT
can be found in Figure 15-2.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

320 Chapter 15: Embedded SQL
Integrity Validation The MATCH predicate is designed to be used with embedded
SQL modification to let you test referential integrity before
with the MATCH actually inserting data into tables. When included in an ap-
Predicate plication program, it can help identify potential data modifica-
tion errors.
For example, assume that a program written for the rare book
store has a function that inserts new books into the database.
The program wants to ensure that a work for the book exists in
the database before attempting to store the book. The applica-
tion program might therefore include the following query:
EXEC SQL SELECT work_numb
FROM work JOIN author
WHERE (:entered_author, :entered_title)
MATCH (SELECT author_first_last, title
FROM work JOIN author);
The subquery selects all the rows in the join of the work and
author tables and then matches the author and title columns
against the values entered by the user, both of which are stored
in host language variables. If the preceding query returns one
or more rows, then the author and title pair entered by the
customer exist in the author and work relations. However, if
the result table has no rows, then inserting the book into book
would produce a referential integrity violation and the insert
should not be performed.
If a program written for the rare book store wanted to verify a
primary key constraint, it could use a variation of the MATCH
predicate that requires unique values in the result table. For ex-
ample, to determine whether a work is already in the database,
the program could use
EXEC SQL SELECT work_numb
FROM work JOIN author
WHERE UNIQUE (:entered_author, :entered_title)
MATCH (SELECT author_first_last, title
FROM work JOIN author);
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Direct Modification 321
By default, MATCH returns true if any value being tested is
null or, when there are no nulls in the value being tested, a row
exists in the result table that matches the values being tested.
You can, however, change the behavior of MATCH when nulls
are present:
◊ MATCH FULL is true if every value being tested is null
or, when there are no nulls in the values being tested,
a row exists in the result table that matches the values
being tested.
◊ MATCH PARTIAL is true if every value being tested is
null or a row exists in the result table that matches the
values being tested.
Note that you can combine UNIQUE with MATCH FULL
and MATCH PARTIAL.
Updates using cursors are a bit different from updating a view. Modification Using
When you update a view, the UPDATE command acts di-
rectly on the view by using the view’s name. The update is then Cursors
passed back to the underlying base table(s) by the DBMS. In
contrast, using a cursor for updating means you update a base
table directly, but identify the row that you want to modify by
referring to the row to which the cursor currently is pointing.
To do the modification, you use FETCH without an INTO
clause to move the cursor to the row you want to update. Then
you can use an UPDATE command with a WHERE predicate
that specifies the row pointed to by the cursor. For example,
to change the address of the customer in row 15 of the ad-
dress_data cursor’s result table, a program for the rare book
store could include
EXEC SQL FETCH ABSOLUTE 15 FROM address_data;
EXEC SQL UPDATE cutomer
SET street = ‘123 Main Street’,
city = ‘New Home’
state_province = ‘MA’,
zip_postcode = ‘02111’
WHERE CURRENT OF address data;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

322 Chapter 15: Embedded SQL
The clause CURRENT OF cursor_name instructs SQL to
work with the row in customer currently being pointed to by
the name cursor. If there is no valid corresponding row in the
customer table, the update will fail.
You can apply the technique of modifying the row pointed to
by a cursor to deletions as well as updates. To delete the current
row, you use
Deletion Using
DELETE FROM table_name
Cursors WHERE CURRENT OF cursor_name
The deletion will fail if the current row indicated by the cursor
isn’t a row in the table named in the DELETE. For example,
EXEC SQL DELETE FROM customers WHERE CURRENT OF
address_data;
will probably succeed, but
EXEC SQL DELETE FROM volume
WHERE CURRENT OF address_data;
will certainly fail because the volume table isn’t part of the ad-
dress_data cursor (as declared in the preceding section of this
chapter).
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

324 Chapter 16: Dynamic SQL
Figure 16-1: A typical window for gathering information for a dynamic SQL query
For example, assume that a user fills in a data entry form with
a customer number and the customer’s new address. A program
could process the update with code written something like the
pseudocode in Figure 16-2. Notice the painstaking way in which
the logic of the code examines the values the user entered and
builds a syntactically correct SQL UPDATE statement. By using
the dynamic SQL, the program can update just those columns for
which the user has supplied new data. (Columns whose fields on
the data entry are left empty aren’t added to the SQL statement.)
There are two major limitations to EXECUTE IMMEDIATE:
◊ The SQL command cannot contain input parameters or
output parameters. This means that you can’t use SELECT
or FETCH statements.
◊ To repeat the SQL statement, the DBMS has to perform
the entire immediate execution process again. You can’t save
the SQL statement, except as a string in a host language
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

326 Chapter 16: Dynamic SQL
Each time you EXECUTE IMMEDIATE the same statement,
it must be scanned for syntax errors again. Therefore, if you
need to execute a dynamic SQL statement repeatedly, you will
get better performance if you can have the syntax checked
once and save the statement in some way.1
If you want to repeat a dynamic SQL statement or if you need
Dynamic SQL to use dynamic parameters (as you would to process the form
with Dynamic in Figure 16-1), you need to use a more involved technique for
preparing and executing your commands.
Parameters
The processing for creating and using a repeatable dynamic
SQL statement is as follows:
1. Store the SQL statement in a host language string
variable using host language variables for the dynamic
parameters.
2. Allocate SQL descriptor areas.
3. Prepare the SQL statement. This process checks the
statement for syntax and assigns it a name by which it
can be referenced.
4. Describe one of the descriptor areas as input.
5. Set input parameters, associating each input parameter
with the input parameter descriptor.
6. (Required only when using a cursor) Declare the cursor.
7. (Required only when using a cursor) Open the cursor.
8. Describe another descriptor area as output.
1 A few DBMSs (for example, DB2 for Z/OS) get around this problem
by performing dynamic statement caching (DSC), where the DBMS saves
the syntax-scanned/prepared statement and retrieves it from the cache if
used again.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Dynamic SQL with Dynamic Parameters 327
9. Set output parameters, associating each output param-
eter with the output parameter descriptor.
10. (Required when not using a cursor) Execute the query.
11. (Required only when using a cursor) Fetch values into
the output descriptor area.
12. (Required only when using a cursor) Get the output
values from the descriptor area and process them in
some way.
13. Repeat steps 11 and 12 until the entire result table has
been processed.
14. Close the cursor.
15. If through with the statement, deallocate the descrip-
tor areas.
There are a few limitations to the use of dynamic parameters in
a statement of which you should be aware:
◊ You cannot use a dynamic parameter in a SELECT
clause.
◊ You cannot place a dynamic parameter on both sides of
a relationship operator such as , or =.
◊ You cannot use a dynamic parameter as an argument in
a summary function.
◊ In general, you cannot compare a dynamic parameter
with itself. For example, you cannot use two dynamic
parameters with the BETWEEN operator.
Dynamic
Many dynamic queries generate result tables containing mul-
tiple rows. As an example, consider a query that retrieves a list
Parameters with
of the customers of the rare book store who live in a given area. Cursors
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.