HP 3000 Manuals HP 3000 Manuals
Simple Data Manipulation Commands
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)