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.
