HP 3000 Manuals HP 3000 Manuals
SQL 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 syntax 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)
INTO :AvgSalesPrice
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)
INTO :AvgSalesPrice
FROM PurchDB.Parts
WHERE SalesPrice > :SalesPrice;
If the select list does not contain aggregate functions, a WHERE clause
is used to restrict the query result to a single row:
EXEC SQL SELECT PartName, SalesPrice
INTO :PartName, :SalesPrice
FROM PurchDB.Parts
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:
const
MultipleRows = -10002;
.
.
.
procedure GetRow;
.
.
.
The SELECT command is executed here.
if SQLCA. SQLCODE = MultipleRows then
writeln('WARNING: More than one row qualifies.');
When multiple rows qualify but the receiving host variables are not in an
array of records and the BULK option is not specified, 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 ensures that no more than one null value can exist for that column.
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 wildcard 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 '!'
The first and last percent sign characters are the wildcard characters.
The next to the last percent sign, preceded by an exclamation point, is
the percent sign that you want to escape, so that it is actually used in
the search pattern for the LIKE clause.
The character following an escape character must be either a wildcard
character or the escape character itself. Complete syntax is presented
in the ALLBASE/SQL Reference Manual .
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:
The 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
INTO :PartNumber, :BinNumber
FROM PurchDB.Inventory
WHERE PartNumber = :PartNumber;
The row is displayed, and the user is asked if the bin number is
to be changed. If not, the user is prompted for another part 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:
EXEC SQL UPDATE PurchDB.Inventory
SET BinNumber = :BinNumber
WHERE PartNumber = :PartNumber;
* 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:
The 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
INTO :ItemNumber
FROM PurchDB.OrderItems
WHERE OrderNumber = :OrderNumber
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)
INTO :MaxOrderNumber
FROM PurchDB.Orders;
The 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 INSERT command syntax to either
insert a single row or copy one or more rows into a table from another
table.
Use the following syntax of the INSERT command to insert a single row:
INSERT INTO TableName
(ColumnNames)
VALUES (DataValues)
You can omit ColumnNames when you provide values for all columns in the
target table:
EXEC SQL INSERT INTO PurchDB.Parts
VALUES (:PartNumber,
:PartName :PartNameInd,
:SalesPrice :SalesPriceInd);
Remember that when you do include ColumnNames 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, use the following syntax of the INSERT
command:
INSERT INTO TableName
(ColumnNames)
SELECT SelectList
FROM TableNames
WHERE SearchCondition1
GROUP BY ColumnName
HAVING SearchCondition2
Note that the SELECT command embedded in this INSERT command cannot
contain an INTO or ORDER BY clause. In addition, any host variables used
must be within the WHERE or HAVING clauses:
The following example copies historical data for filled orders
into PurchDB.OldOrders, then deletes rows for these orders from
PurchDB.Orders, keeping that table minimal in size. The INSERT command
copies rows from PurchDB.Orders to PurchDB.OldOrders.
EXEC SQL INSERT INTO PurchDB.OldOrders
(OldOrder, OldVendor, OldDate)
SELECT OrderNumber, VendorNumber, OrderDate
FROM PurchDB.Orders
WHERE OrderNumber = :OrderNumber;
Then the DELETE command deletes rows from PurchDB.Orders:
EXEC SQL DELETE FROM PurchDB.Orders
WHERE OrderNumber: = OrderNumber;
The UPDATE Command
In simple data manipulation, you use this syntax of the UPDATE command to
change data in one or more columns:
UPDATE TableName
SET Columname = ColumnValue
[,...]
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
SET ContactName = :ContactName :ContactNameInd,
VendorStreet = :VendorStreet,
VendorCity = :VendorCity,
VendorState = :VendorState,
VendorZipCode = :VendorZipCode
WHERE VendorNumber = :VendorNumber;
In this example, column ContactName can contain a null value. To insert
a null value, the program must assign a number less than 0 to the
indicator variable for this column, ContactNameInd:
The program prompts the user for new values
prompt ('Enter Vendor Street> ');
readln (VendorStreet);
prompt ('Enter Vendor City> ');
readln (VendorCity);
prompt ('Enter Vendor State> ');
readln (VendorState);
prompt ('Enter Vendor Zip Code> ');
readln (VendorZipCode);
prompt ('Enter Contact Name (0 for null)> ');
readln (ContactName);
If the user enters a 0 to assign a null value to column ContactName,
the program assigns a -1 to the indicator variable; otherwise, the program
assigns a 0 to this variable:
if ContactName = '0' then
ContactNameInd := -1
else
ContactNameInd := 0;
The DELETE Command
In simple data manipulation, you use the DELETE command to delete one or
more rows from a table, as shown in the following syntax:
DELETE FROM TableName
WHERE SearchCondition
The WHERE clause specifies a SearchCondition rows must meet to be
deleted, for example:
EXEC SQL DELETE FROM PurchDB.Orders
WHERE OrderDate < :OrderDate;
If the WHERE clause is omitted, all rows in the table are deleted.