Defining and Manipulating Data

You embed data definition and data manipulation commands in statement parts.

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 DBEFILE            DROP TABLE

   CREATE GROUP              DROP DBEFILESET         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);

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 :PartialKey;

Data Manipulation

SQL has the following 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.
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;

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

   SalesPrice := Response;

   EXEC SQL SELECT  COUNT(PartNumber)

              INTO :PartCount

              FROM  Purchdb.Parts

             WHERE  SalesPrice > :SalesPrice;

All host variables used in procedures must be declared in a declaration part, as discussed earlier in this chapter under "Declaring Host Variables".

Defining and Manipulating Data

Technical documentation

» Table of Contents

» Index

Data Definition

Data Manipulation