HP 3000 Manuals HP 3000 Manuals
Simple Data Manipulation
In simple data manipulation, you retrieve or insert single rows or update
one or more rows based on a specific criterion. In most cases, the
simple data manipulation technique is used to support the random
retrieval and/or change of specific rows.
In the following example, if the user wants to perform a DELETE
operation, the program performs the operation only if a single row
qualifies. If no rows qualify or if more than one row qualifies, the
program displays a message. Note that the host variables in this case
are designed to accommodate only a single row. In addition, two of the
columns may contain null values, so an indicator variable is used for
these columns:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 PARTNUMBER PIC X(16).
01 PARTNAME PIC X(30).
01 PARTNAMEIND SQLIND.
01 SALESPRICE PIC S9(8)V99 COMP-3.
01 SALESPRICEIND SQLIND.
EXEC SQL END DECLARE SECTION END-EXEC.
::
PROCEDURE DIVISION.
The program accepts a part number from the user,
then executes a query to determine whether one or
more rows containing that value actually exist.
EXEC SQL SELECT PARTNUMBER, PARTNAME, SALESPRICE
INTO :PARTNUMBER,
:PARTNAME :PARTNAMEIND,
:SALESPRICE :SALESPRICEIND
FROM PURCHDB.PARTS
WHERE PARTNUMBER = :PARTNUMBER
END-EXEC.
IF SQLCODE = OK THEN PERFORM DISPLAY-DELETE
ELSE
IF SQLCODE = 100
DISPLAY "Row not found!"
ELSE
IF SQLCODE = -10002
DISPLAY "WARNING: More than one row qualifies."
ELSE
PERFORM SQL-STATUS-CHECK.
DISPLAY-DELETE.
The qualifying row is displayed for the user to verify that
it should be deleted before the wing command is executed:
EXEC SQL DELETE FROM PURCHDB.PARTS
WHERE PARTNUMBER = :PARTNUMBER
END-EXEC.
Chapter 7 provides more details about simple data manipulation.