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

Format:   REVOKE {SELECT | INSERT | UPDATE | DELETE | REFERENCES} [column_names]
              ON {table_name | view_name}
              TO {user_id | PUBLIC} [CASCADE | RESTRICT];

The privileges that have been granted with the GRANT statement can be removed with the REVOKE statement. The REVOKE statement syntax is very similar to the GRANT syntax. It simply says that you are removing privileges on specific database objects from one or more database users.
> Many RDBMS systems use a REVOKE similar to DB2’s format which allows you to specify a list of tables and a list of user-IDs. This makes it easier to revoke many privileges at once.

Using a view to restrict row access:

 

 

Here the user, JAMES, has been given SELECT, INSERT, UPDATE, and DELETE privileges on the MANAGER_VIEW, while PATRICK has the same privileges on the EMPLOYEE_VIEW. But this does not give either JAMES or PATRICK any access privileges to the base table EMPLOYEE_TBL.

Using a view to restrict column access:

 

Here the user, ACTGUSER, has been given SELECT privileges on the EMPLOYEE_VIEW.
But this does not give ACTGUSER any access privileges to the base table EMPLOYEE_TBL,
so ACTGUSER can not access RATE_HOUR information.

Format:   GRANT ALL PRIVILEGES
             ON table_name
             TO {user_id | PUBLIC} [WITH GRANT OPTIONS];

ALL PRIVILEGES is used to give all privileges the grantor has to grant to another user.
Generally this is not a good idea, because you may presume that a user has certain rights when they don’t.

Format:   GRANT {SELECT | INSERT | UPDATE | DELETE} [column_names]
             ON table_name
             TO {user_id | PUBLIC} [WITH GRANT OPTIONS];

Let us restate: When you create a database object, you own it and you are the only person who can grant privileges to others. When you grant privileges to them, they can use those privileges, but they can not grant those privileges to others. This allows you to give users access to objects, but they can not give access to others.

If you wish to relinquish this control, you can add WITH GRANT OPTIONS when you grant privileges to others. Now it is like they also own the database object and they can grant privileges to as many people as they wish.

Format:   GRANT {SELECT | INSERT | UPDATE | DELETE} [column_names]
             ON {table_name | view_name}
             TO {user_id | PUBLIC} [WITH GRANT OPTIONS];

When you GRANT privileges TO PUBLIC, it means that you are giving privileges on this table to all current and future users who can access the database or schema. Obviously you should only use this privilege with extreme care and some installations never use this option.

Format:   GRANT {SELECT | INSERT | UPDATE | DELETE | REFERENCES} [column_names]
             ON {table_name | view_name}
             TO {user_id | PUBLIC} [WITH GRANT OPTIONS];

When you create a database object, you own it and you are the only person who can grant privileges to others. When you grant privileges to them, they can use those privileges, but they can not grant those privileges to others. This allows you to give users access to objects, but they can not give access to other users.

If you limit the GRANT to specific column_names, this option normally applies only to the INSERT and UPDATE privileges. You limit the ability to SELECT specific columns by creating a VIEW with the specific columns and then GRANT SELECT on that VIEW.
> SQL Server and Sybase do allow specific column_names to be specified in the SELECT, allowing you to define fewer views.

> Many RDBMS systems use a GRANT similar to DB2’s format, which allows you to specify a list of tables and a list of user-IDs. This makes it easier to grant many privileges at once.