 |
» |
|
|
|
The SQL commands used for simple data manipulation are:
SELECT
INSERT
DELETE
UPDATE
|
Refer to the ALLBASE/SQL Reference Manual for the complete syntax and semantics of
these commands. The SELECT Command | |
In simple data manipulation, you use the SELECT command to
retrieve a single row, i.e., a one-row query result. The form
of the SELECT command that describes a one-row query result is:
SELECT SelectList
INTO HostVariables
FROM TableNames
WHERE SearchCondition
|
Note that the GROUP BY, HAVING, and ORDER BY clauses are not
necessary, since these clauses usually describe multiple-row
query results. You may omit the WHERE clause from certain queries when the
select list contains only aggregate functions:
EXEC SQL SELECT AVG(SalesPrice)
1 INTO :AvgSalesPrice
2 FROM PurchDB.Parts
|
A WHERE clause may be used, however, to qualify the rows over
which the aggregate function is applied:
EXEC SQL SELECT AVG(SalesPrice)
1 INTO :AvgSalesPrice
2 FROM PurchDB.Parts
3 WHERE SalesPrice > :SalesPrice
|
If the select list does not contain aggregate functions, a
WHERE clause is needed to restrict the query result to a single
row:
EXEC SQL SELECT PartName, SalesPrice
1 INTO :PartName, :SalesPrice
2 FROM PurchDB.Parts
3 WHERE PartNumber = :PartNumber
|
Because the host variables that hold query results for a simple
SELECT command are not arrays of records, they can hold only a
single row. A runtime error occurs when multiple rows qualify
for a simple SELECT command. You can test for an SQLCode
value of -10002 to detect this condition:
.
.
.
SUBROUTINE GetRow
MultipleRows = -10002
.
.
.
The SELECT command is executed here.
IF (SQLCode .EQ. MultipleRows) THEN
WRITE(*,*) 'WARNING: More than one row qualifies.'
ENDIF
.
.
.
RETURN
END
|
When multiple rows qualify but the receiving host variables are
not in an array of records, none of the rows are returned. When a column named in the WHERE clause has a unique index on
it, you can omit testing for multiple-row query results if the
column was defined NOT NULL. A unique index prevents the key
column(s) from having duplicate values. The following index,
for example, ensures that only one row will exist for any part
number in PurchDB.Parts:
CREATE UNIQUE INDEX PartNumIndex
ON PurchDB.Parts (PartNumber)
|
If a key column of a unique index can contain a null value,
the unique index does not prevent more than one null value for
that column, since each null value is considered unique.
Therefore if a query contains a WHERE clause using the null
predicate for such columns, multiple-row query results may
occur. It is useful to execute the SELECT command before executing
the INSERT, DELETE, or UPDATE commands in the following
situations: When an application updates or deletes rows, the SELECT command
can retrieve the target data for user verification before the
data is changed. This technique minimizes inadvertent data
changes:
This program accepts a part number from the user into
a host variable named PartNumber, then retrieves a row
for that part.
EXEC SQL SELECT PartNumber, BinNumber
1 INTO :PartNumber, :BinNumber
2 FROM PurchDB.Inventory
3 WHERE PartNumber = :PartNumber
The row is displayed, and the user is asked whether they
want to change the bin number. If so, the user is
prompted for the new bin number, which is accepted into the
host variable named BinNumber. Then the UPDATE command
is executed. If not, the user is prompted for another
part number.
EXEC SQL UPDATE PurchDB.Inventory
1 SET BinNumber = :BinNumber
2 WHERE PartNumber = :PartNumber
|
Another method of qualifying the rows you want to select is to use the LIKE
specification to search for a particular character string pattern.
For example, suppose you want to search for all VendorRemarks that contain
a reference to 6%.
Since the percent sign (%) happens to be one of the wild card characters
for the LIKE specification, you could use the following SELECT statement
specifying the exclamation point (!) as your escape character.
SELECT * FROM PurchDB.Vendors
WHERE VendorRemarks LIKE '%6!%%' ESCAPE '!'
|
In this example, the first and last percent signs are wildcard characters,
and the percent sign after the exclamation point is the percent sign
that is part of the search pattern.
The character following an escape character must be either a wild card
character or the escape character itself.
Complete syntax is presented in the ALLBASE/SQL Reference Manual . To prohibit the multiple-row changes possible if multiple rows
qualify for an UPDATE or DELETE operation, an application can
use the SELECT command. If multiple rows qualify for the SELECT
operation, the UPDATE or DELETE would not be executed.
Alternatively, the user could be advised that multiple rows
would be affected and given a choice about whether to perform
the change:
This program prompts the user for an order number and
a vendor part number in preparation for allowing the user
to change the vendor part number. The following SELECT
command determines whether more than one line item
exists on the order for the specified vendor part number:
|
EXEC SQL SELECT ItemNumber
1 INTO :ItemNumber
2 FROM PurchDB.OrderItems
3 WHERE OrderNumber = :OrderNumber
4 AND VendPartNumber = :VendPartNumber
When more than one row qualifies for this query, the
program lets the user decide whether to proceed with
the update operation.
|
When an application lets the user INSERT a row that must contain
a value higher than an existing value, the SELECT command can
identify the highest existing value:
EXEC SQL SELECT MAX(OrderNumber)
1 INTO :MaxOrderNumber
2 FROM PurchDB.Orders
This program can increment the maximum order number by
one, then provide the user with the new number and
prompt for information describing the new order.
|
The INSERT Command | |
In simple data manipulation, you use the INSERT command to
either insert a single row or copy one or more rows into a table
from another table.
You use the following form of the INSERT command to insert a
single row:
INSERT INTO TableName
(ColumnNames)
VALUES (DataValues)
|
You can omit column names when you provide values for all
columns in the target table:
EXEC SQL INSERT INTO PurchDB.Parts
1 VALUES (:PartNumber,
2 :PartName :PartNameInd,
3 :SalesPrice :SalesPriceInd)
|
Remember that when you do include column names but do not
name all the columns in the target table, ALLBASE/SQL attempts
to insert a null value into each unnamed column. If an unnamed
column was defined as NOT NULL, the INSERT command fails. To copy one or more rows from one or more tables to another
table, you use the following form of the INSERT command:
INSERT INTO TableName
(ColumnName)
SELECT SelectList
FROM TableNames
WHERE SearchCondition1
GROUP BY ColumnName
HAVING SearchCondition2
|
Note that the SELECT command embedded in the INSERT command
cannot contain an INTO or ORDER BY clause. In addition, any
host variables used must be within the WHERE or HAVING clauses:
This program makes a copy of historical data for filled
orders into the PurchDB.OldOrders table, then deletes
rows for these orders from PurchDB.Orders,
keeping that table minimal in size. The following
INSERT command copies rows from PurchDB.Orders
to PurchDB.OldOrders:
EXEC SQL INSERT INTO PurchDB.OldOrders
1 (OldOrder,OldVendor,OldDate)
2 SELECT OrderNumber, VendorNumber, OrderDate
3 FROM PurchDB.Orders
4 WHERE OrderNumber = :OrderNumber
|
Then the DELETE command deletes rows from PurchDB.Orders:
EXEC SQL DELETE FROM PurchDB.OldOrders
1 WHERE OrderNumber = :OrderNumber
|
The DELETE Command | |
In simple data manipulation, you use the DELETE command to
delete one or more rows from a table:
DELETE FROM TableName
WHERE SearchCondition
|
The WHERE clause specifies a SearchCondition that all rows
satisfying to be deleted:
EXEC SQL DELETE FROM PurchDB.Orders
1 WHERE OrderDate < :OrderDate
|
If the WHERE clause is omitted, all rows in the table are
deleted.
The UPDATE Command | |
In simple data manipulation, you use the UPDATE command to
change data in one or more columns:
UPDATE TableName
SET Columname = :ColumnValue :ColumnValueInd
[,...]
WHERE SearchCondition
|
As in the case of the DELETE command, if you omit the WHERE
clause, the value of any column specified is changed in all
rows of the table.
If the WHERE clause is specified, all rows satisfying the search
condition are changed:
EXEC SQL UPDATE PurchDB.Vendors
1 SET VendorStreet = :VendorStreet,
2 VendorCity = :VendorCity,
3 VendorState = :VendorState,
4 VendorZipCode = :VendorZipCode
5 WHERE VendorNumber = :VendorNumber
|
In this example, all target columns were defined NOT NULL. If
the UPDATE command is used to change the value of a column that
allows NULL values, you use a null indicator variable
directly following the variable holding the value of the column
to be updated. The values that get updated at runtime depend on
whether or not the program user wants to assign a null value to
a column when the UPDATE command is executed:
If this program does allow the user to put a null
value into column ContactName, the following UPDATE
command is executed:
EXEC SQL UPDATE PurchDB.Vendors
1 SET ContactName = :ContactName :ContactNameInd
2 WHERE VendorNumber = :VendorNumber
If this program does not allow the user to put a null
value into the column, the following command is executed
instead:
EXEC SQL UPDATE PurchDB.Vendors
1 SET ContactName = :ContactName
2 WHERE VendorNumber = :VendorNumber
|
In the following application, the row is selected before the
user enters the column data to be updated. You can achieve the
same effect by using the DELETE and INSERT commands:
First retrieve all columns from the row to be updated.
EXEC SQL SELECT PartNumber,
1 PartName,
2 SalesPrice
3 INTO :PartNumber,
4 :PartName :PartNameInd,
5 :SalesPrice :SalesPriceInd
6 FROM :PurchDB.Parts
7 WHERE PartNumber = :PartNumber
|
Prompt the user for new values. If the user wants to
set a column to null, set the indicator variable for
that column to -1.
WRITE (*,*) 'Enter new PartName (or 0 for NULL)> '
READ(6,100) PartName
100 FORMAT (A30)
IF (PartName .EQ. '0') THEN
PartNameInd = -1
ELSE
PartNameInd = 0
ENDIF
C
WRITE (*,*) 'Enter new SalesPrice (or 0 for NULL)> '
READ(6,101) SalesPrice
101 FORMAT (F10.2)
IF (SalesPrice .EQ. 0) THEN
SalesPriceInd = -1
ELSE
SalesPriceInd = 0
ENDIF
After accepting new data values from the user,
UPDATE the existing row.
EXEC SQL UPDATE FROM PurchDB.Parts
1 SET PartNumber = :PartNumber,
2 PartName = :PartName :PartNameInd,
3 SalesPrice = :SalesPrice :SalesPriceInd
When an indicator variable contains a value less than 0,
ALLBASE/SQL assigns a null value to that column. When the
indicator variable contains a value of 0, ALLBASE/SQL assigns
the data entered by the user to the column.
|
The following combination of DELETE and INSERT commands would
have accomplished the same result, as long as all columns in the
table were in the INSERT command:
EXEC SQL DELETE FROM PurchDB.Parts
WHERE PartNumber = :PartNumber
EXEC SQL INSERT INTO PurchDB.Parts
1 (PartNumber,
2 PartName,
3 SalesPrice)
4 VALUES (:PartNumber,
5 :PartName :PartNameInd,
6 :SalesPrice :SalesPriceInd)
|
|
