Data Manipulation with SQL › Data Manipulation Operations › Retrieving Data

Retrieving Data

Using SELECT

In a program, you use the SELECT statement in one of these ways to retrieve data from the database:

• With an INTO clause that specifies host variable, local variables, or routine parameters names, to retrieve a single row into working storage
• With a BULK clause that specifies the name of a host variable array, to retrieve multiple rows into working storage
• In a DECLARE CURSOR statement to define a cursor that you can use to retrieve multiple rows and then fetch each row one at a time into working storage
• In an INSERT statement to select from one or more other tables the data to be inserted

When embedding a SELECT statement, specify each column even if you mean to select all columns. Using SELECT * to select all columns can cause a program error if, for example, a column is added to the table.

Single-row SELECT Statement

If the result of a SELECT statement will be one and only one row, you can issue a SELECT statement with an INTO clause.

A result table will contain only one row when:

• The WHERE clause specifies a primary key value as the search condition:

  EXEC SQL
    SELECT EMP_ID,
           EMP_LNAME,
           DEPT_ID
      INTO :EMP-ID,
           :EMP-LNAME,
           :DEPT-ID
      FROM EMPLOYEE
      WHERE EMP_ID = :EMP-ID
  END-EXEC.
  

• All column values result from aggregate functions and no GROUP BY clause has been specified:

  EXEC SQL
     SELECT COUNT(P.EMP_ID) INTO :TOT-EMPLOYEES,
            SUM(B.SALARY_AMOUNT) INTO :TOT-SALARIES,
            (SUM(B.VAC_ACCRUED) - SUM(B.VAC_TAKEN))
               INTO :UNUSED-VAC
        FROM POSITION P, BENEFITS B
        WHERE P.EMP_ID = B.EMP_ID
              AND P.SALARY_AMOUNT IS NOT NULL
              AND P.FINISH_DATE IS NULL
  END-EXEC.
  

Checking Single-row Select Status

If the number of rows returned by a SELECT statement with an INTO clause is greater than 1, the DBMS returns a cardinality violation error. No data is moved to the host variables named in the INTO clause.

If no row is found that matches the selection criteria, the DBMS returns a no rows found warning and moves 100 to SQLCODE.

Updating the Single Row

Under cursor stability if the program performs single-row select that specifies the primary key in the search condition, the DBMS locks the base row from which the resulting row is derived. This prevents any update by a concurrent database transaction. The lock is maintained until one of these events occurs:

• The database transaction ends
• The database session is suspended
• The database transaction accesses a different row from the same table

Until one of these events occurs, the SQL transaction can update the row without a need to check whether a concurrent transaction has modified the row.

Note: For more information about updating rows, see Modifying Data.

Multiple-row SELECT

If the result table of a SELECT statement potentially has multiple rows, the program must declare a cursor or perform bulk processing to process retrieved data.

Note: For more information about retrieving multiple rows, see Data Manipulation with SQL.