HP 3000 Manuals

ALLBASE/SQL COBOL Application Programming Guide

Defining and Manipulating Data 

You embed data definition and data manipulation commands in the PROCEDURE
DIVISION.

Data Definition 

You can embed the following SQL commands to create objects or change
existing objects:

        ALTER DBEFILE         CREATE INDEX           DROP GROUP
        ALTER TABLE           CREATE TABLE           DROP INDEX
        CREATE DBEFILE        CREATE VIEW            DROP MODULE
        CREATE DBEFILESET     DROP DBFILE            DROP TABLE
        CREATE GROUP          DROP DBFILESET         DROP VIEW

Data definition commands are useful for such activities as creating
temporary tables or views to simplify data manipulation or creating an
index that improves the program's performance:

          EXEC SQL CREATE INDEX PARTNAMEINDEX
                             ON PURCHDB.PARTS (PARTNAME);
          END-EXEC.

The index created with this command expedites data access operations
based on partial key values:

          EXEC SQL SELECT PARTNAME
                    INTO :PARTNAME
                     FROM PURCHDB.PARTS
                    WHERE PARTNAME LIKE :PARTIAL-KEY
          END-EXEC.

Data Manipulation 

SQL has four basic data manipulation commands:

   *   SELECT: retrieves data.

   *   INSERT: adds rows.

   *   DELETE: deletes rows.

   *   UPDATE: changes column values.

These four commands can be used for various types of data manipulation
operations:

   *   Simple data manipulation:  operations that retrieve a single row,
       insert a single row, or delete or update a limited number of rows.

   *   Sequential table processing:  operations that use a cursor to
       operate on a row at a time within a set of rows.  A cursor is a
       pointer the program advances through the set of rows.  ,4

   *   Bulk operations:  operations that manipulate multiple rows with a 
       single execution of a data manipulation command.

   *   Dynamic operations:  operations specified by the user at run time.

In all non-dynamic data manipulation operations, you use host variables
to pass data back and forth between your program and the DBEnvironment.
Host variables can be used in the data manipulation commands wherever the
syntax in the ALLBASE/SQL Reference Manual allows them.

The SELECT command shown at  8  in Figure 3-1  retrieves the row from
PURCHDB.PARTS that contains a part number matching the value in the host
variable named in the WHERE clause (PARTNUMBER). The three values in the
row retrieved are stored in three host variables named in the INTO clause
(PARTNUMBER, PARTNAME, and SALESPRICE). An indicator variable
(SALESPRICEIND) is also used in the INTO clause, to flag the existence of
a null value in column SALESPRICE:

          EXEC SQL SELECT  PARTNUMBER, PARTNAME, SALESPRICE
                     INTO :PARTNUMBER,
                          :PARTNAME
                          :SALESPRICE :SALESPRICEIND
                     FROM  PURCHDB.PARTS
                    WHERE  PARTNUMBER = :PARTNUMBER
          END-EXEC.

You can also use host variables in non-SQL statements; in this case, omit
the colon:

          MOVE RESPONSE TO SALESPRICE
          EXEC SQL SELECT  COUNT(PARTNUMBER)
                     INTO :PART-COUNT
                     FROM  PURCHDB.PARTS
                    WHERE  SALESPRICE > :SALESPRICE
          END-EXEC.

All host variables used in the PROCEDURE DIVISION must be declared in the
DATA DIVISION, as discussed earlier in this chapter under "Declaring Host
Variables".

MPE/iX 5.0 Documentation