ASENSITIVE

Example

DECLARE cursor1 CURSOR ASENSITIVE FOR
SELECT * FROM employees;
OPEN cursor1;

Output

Query OK, 0 rows affected (0.00 sec)

Explanation

The given SQL code declares an ASENSITIVE cursor cursor1 that selects all records from the employees table. The ASENSITIVE keyword specifies that the cursor is sensitive to changes to the result table and may reflect those changes. The OPEN statement opens the cursor to allow for queries. The output represents successful execution of the cursor operation and opening, however, it doesn’t reflect the contents of the result set.

Example

DECLARE @myCursor AS CURSOR;

SET @myCursor = CURSOR ASENSITIVE FOR
SELECT * FROM Employees;

OPEN @myCursor;

Output

No output is available for this code because cursors are tasked with managing handles for individual rows within a query result rather than generating output that can be displayed.

Explanation

The above SQL Server script demonstrates the ASENSITIVE cursor declaration. The ASENSITIVE keyword stipulates that the cursor can be sensitive or insensitive to any changes made to the table, i.e., it does not guarantee that changes will be visible. The cursor is then assigned a Select statement to retrieve all records from the Employees table. A cursor allows you to retrieve individual rows from a database and perform operations on that row. Here, since ASENSITIVE cursor is used, changes made to the rows while the cursor is open might be visible or invisible. It depends on the implementation, server settings, and other factors.

Note: The sample cursor must be deallocated after use using the DEALLOCATE statement to free up server resources.

Example

DECLARE
   cursor_position NUMBER;
   CURSOR cur_test IS SELECT * FROM employee;
BEGIN
   OPEN cur_test;
   FETCH cur_test INTO cursor_position;
   DBMS_OUTPUT.PUT_LINE('Position: ' || TO_CHAR(cursor_position));
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error:' || SQLERRM);
END;

Output

Position: 1

Explanation

In the given code, a cursor named “cur_test” is declared and used to navigate through records in the “employee” table. The FETCH operation retrieves the current row pointed by the cursor and advances it to the next row. DBMS_OUTPUT.PUT_LINE is used to display the resultant value of cursor_position which indicates the current position of cursor in the table. However, ORACLE doesn’t support ASENSITIVE specification, thus the code doesn’t show the ASENSITIVE usage.