The DECLARE CURSOR statement consists of a cursor name and the SELECT statement that will create the results. The SELECT statement is identical in format to the SQL SELECT statements discussed earlier. In the following examples, we shall use the prefix EXEC SQL and the terminator END-EXEC.
The syntax is:

          EXEC SQL
            DECLARE cursor_name [CURSOR FOR]
              SELECT column1, column2 ...
              FROM table_name ...
              WHERE ...
             [FOR UPDATE OF column1, column2 ...]
          END-EXEC

A typical declare cursor using the VENDOR_TBL would look something like this:

          EXEC SQL
            DECLARE VENDOR_CSR
              SELECT VENDOR_NAME, STATE
                FROM VENDOR_TBL
               WHERE COUNTRY = :SEARCH-COUNTRY
          END-EXEC

The cursor is named VENDOR_CSR and points, one at a time, to each selected row in the table. SQL knows by the colon that :SEARCH-COUNTRY is the host variable that is defined in the host program (without the colon).

The DECLARE CURSOR statement may be placed anywhere in the host program as long as it occurs after any host variable definitions and before any statements that reference the cursor.

Each SQL statement in a host program starts with ‘SQL’, an SQL prefix identifier, an SQL statement, and an SQL terminator. The specific values of the prefix and terminator depend on the host language and are given later under the specifications of each host language.When the host language is compiled, a precompiler extracts the SQL statement and builds subroutine calls for the SQL execution within the compiled host program. The host program has variables defined in the “SQL DECLARE SECTION”. These host variables are identified within the SQL statement by immediately preceding the host variable name with a colon (:).

You will see examples of cursor control in the following topics.

SQL is called non-procedural. That means it operates on an entire table or set of tables returning zero, one, or many rows. With one query, you can get thousands of rows returned at one time.Most application languages are procedural, which means they are a procedure that handles one table row and then loops to process the next table row. A cursor, when it is active, is like a pointer that allows SQL to retrieve, insert, update, or delete a single row. The cursor retrieves a row from a table and hands it to the procedure for processing. This way, you can process the entire result table one row at a time.

The procedural application invokes the SQL and, for this reason, it is called the host program.
A typical procedure flows something like this:

          Procedural code
          EXEC SQL DECLARE CURSOR statement
          EXEC SQL OPEN cursor statement
          Test for end of table
          Procedural code
          Start loop
               Procedural code
               EXEC SQL FETCH
               Test for end of table
               Procedural code
          End loop
          EXEC SQL CLOSE cusror statement
          Procedural code