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.
Four techniques exist for using these commands in a program:
In simple data manipulation, you
retrieve or insert a single row 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 BULK table processing, you manipulate multiple rows at a
time using a host variable declared as an array. You can retrieve
rows from a table into the host variable or insert data from the host
variable into rows of a table.
A cursor can, but need not, be used for some
BULK operations.
In dynamic operations, you preprocess
SQL commands at
run time. For example, a program might accept data manipulation commands
from a user.
A cursor is used to handle dynamic SELECT operations.
Table 6-1 “How Data Manipulation Commands May Be Used” 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
| TYPE OF OPERATION | SELECT | FETCH | INSERT | DELETE | UPDATE | DELETE WHERE CURRENT | UPDATE WHERE CURRENT |
|---|
| Simple | X | | X | X | X | | |
| Sequential | X | X | | | | X | X |
| BULK | X | X | X | | | | |
| Dynamic | X | X | X | X | X | | |
The remainder of
this chapter briefly examines each of the four data manipulation
techniques (each technique is discussed in detail in Chapters
7 through 10) and introduces the
use of a cursor for data manipulation.
First, however, this chapter addresses
the query, a 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.
