HP 3000 Manuals HP 3000 Manuals
Chapter 6 Overview Of Data Manipulation
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 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 | SELECT | FETCH | INSERT | DELETE | UPDATE | DELETE | UPDATE |
| OPERATION | | | | | | WHERE | WHERE |
| | | | | | | CURRENT | 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.