To manipulate data in an ALLBASE/SQL DBEnvironment, you use one
of the following SQL commands:
SELECT: to retrieve one or more rows from one or more tables.
INSERT: to insert one or more rows into a single table.
DELETE: to delete one or more rows from a single table.
UPDATE: to change the value of one or more columns in one
or more rows in a single table.
Three techniques exist for using these commands in a program:
In simple data manipulation, you retrieve or
insert single rows or you delete or update one or more rows
based on a specific criterion.
In sequential table processing, you operate on a
set of rows, one row at a time, using a cursor. A cursor is a pointer that identifies one row in the set
of rows, called the active set. You move through
the active set, retrieving a row at a time and optionally
updating or deleting it.
In dynamic operations, you preprocess SQL
commands at runtime, as when the program accepts data
manipulation commands from a user.
Table 6-1 summarizes which data manipulation commands can be
used in each technique. Note that the FETCH command is included
in this table, since it must be used when you manipulate data
using a cursor.
Table 6-1 How Data Manipulation Commands May Be Used
| | USABLE SQL COMMANDS |
|---|
TYPE OF OPERATION | SELECT | FETCH | INSERT | DELETE | UPDATE | DELETE WHERE CURRENT | UPDATE WHERE CURRENT |
| Simple | X | | X | X | X | | |
| Sequential | X | X | | | | X | X |
| Dynamic | | X | X | X | X | | |
The remainder of this chapter briefly examines each of the three
data manipulation techniques (each technique is discussed in
detail in Chapters 7 through 9) and introduces the use of a
cursor for data manipulation. First, however, this chapter
addresses the query, or the description of data
you want to retrieve. Queries are fundamental to ALLBASE/SQL
data manipulation because some of the elements of a query are
also used to describe and limit data when you update or delete
it. In addition, it is common programming practice to retrieve
and display rows prior to changing or deleting them.
