Explain Cursor and its properties in embedded SQL with an example

Cursor in Embedded SQL

A cursor in embedded SQL is a pointer that allows you to retrieve and process one row at a time from the result of a query.

It is mainly used in procedural languages like C, Java, or PL/SQL when a query returns multiple rows, and you need to process them row by row.

Why Use Cursors?

• SQL is set-oriented (returns multiple rows).
• Programming languages are record-oriented (handle one row at a time).
• Cursor helps bridge the gap between SQL and host languages.

Cursor Properties

Steps to Use a Cursor in Embedded SQL

1. Declare the Cursor
2. Open the Cursor
3. Fetch from the Cursor
4. Close the Cursor

Example (in Embedded SQL style pseudocode)

Suppose you want to print the names of all employees older than 30.

-- 1. Declare the Cursor
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT name FROM Employee WHERE age > 30;

-- 2. Open the Cursor
EXEC SQL OPEN emp_cursor;

-- 3. Fetch rows in a loop
LOOP
    EXEC SQL FETCH emp_cursor INTO :emp_name;
    IF SQLCODE = 100 THEN  -- No more rows
        EXIT LOOP;
    END IF;
    PRINT emp_name;
END LOOP;

-- 4. Close the Cursor
EXEC SQL CLOSE emp_cursor;

Here:

• emp_cursor is the cursor name.
• :emp_name is a host variable (from the programming language).
• SQLCODE = 100 indicates end of result set.

Summary