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.
SELECT
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)
INTO :AVGSALESPRICE
FROM PURCHDB.PARTS
END-EXEC.
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
END-EXEC.
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
END-EXEC.
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:
WORKING-STORAGE SECTION.
.
.
01 MULTIPLEROWS PIC S9(9) COMP VALUE -10002.
.
.
.
PROCEDURE DIVISION.
.
.
.
The SELECT command is executed here.
IF SQLCODE = MULTIPLEROWS
DISPLAY "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 character 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
END-EXEC.
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
END-EXEC.
* 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 as to 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 at a time 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
END-EXEC.
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
END-EXEC.
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.
INSERT
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 ColumnNames when you provide values for all columns in the
target table:
EXEC SQL INSERT INTO PURCHDB.PARTS
VALUES (:PARTNUMBER,
:PARTNAME :PARTNAMEIND,
:SALESPRICE :SALESPRICEIND)
END-EXEC.
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, use
the following form 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
table 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
END-EXEC.
Then the DELETE command deletes rows from PURCHDB.ORDERS:
EXEC SQL DELETE FROM PURCHDB.ORDERS
WHERE ORDERNUMBER: = ORDERNUMBER
END-EXEC.
UPDATE
In simple data manipulation, you use 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, for example:
EXEC SQL UPDATE PURCHDB.VENDORS
SET CONTACTNAME = :CONTACTNAME :CONTACTNAMEIND,
VENDORSTREET = :VENDORSTREET,
VENDORCITY = :VENDORCITY,
VENDORSTATE = :VENDORSTATE,
VENDORZIPCODE = :VENDORZIPCODE
WHERE VENDORNUMBER = :VENDORNUMBER
END-EXEC.
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 for the four columns.
PROCEDURE DIVISION.
.
.
DISPLAY "Enter Vendor Street> ".
ACCEPT VENDORSTREET FREE.
DISPLAY "Enter Vendor City> ".
ACCEPT VENDORCITY FREE.
DISPLAY "Enter Vendor State> ".
ACCEPT VENDORSTATE FREE.
DISPLAY "Enter Vendor Zip Code> ".
ACCEPT VENDORZIPCODE FREE.
DISPLAY "Enter Contact Name (0 for null)> "
ACCEPT CONTACTNAME FREE.
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
MOVE -1 TO CONTACTNAMEIND
ELSE
MOVE ZERO TO CONTACTNAMEIND.
DELETE
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 rows must meet to be
deleted, for example:
EXEC SQL DELETE FROM PURCHDB.ORDERS
WHERE ORDERDATE < :ORDERDATE
END-EXEC.
If the WHERE clause is omitted, all rows in the table are deleted.