SQL - CURSOR_STATUS() Function

---

---

The SQL CURSOR_STATUS() function is used to determine the current cursor's status. Before calling the cursor, an application can use this to check its state. Particularly while using one or more cursors when using the application. The non-deterministic nature of this function must be kept in mind. Hence, outcomes may change depending on the circumstances.

The SQL CURSOR_STATUS() function return the smallint values as shown below −

Syntax

Following is the syntax of the SQL CURSOR_STATUS() function −

CURSOR_STATUS   
   (  
      { 'local' , 'cursor_name' }   
      | { 'global' , 'cursor_name' }   
      | { 'variable' , 'cursor_variable' }   
   )

Parameters

• local − Indicating that the cursor source is a local cursor name.

• cursor_name − Indicating the name of a cursor that must conform to the database identifier rules.

• global − Indicating that the cursor source is a global cursor name.

• variable − Indicating that the cursor source is a local variable.

• cursor_variable − Indicating the nme of a cursor and is defined by using th

Example

Let's create a table named Workers that we are going to use further in our examples by using the following query −

CREATE TABLE Workers(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, let us insert some records in the Workers table using INSERT statements as shown in the query below −

INSERT INTO Workers VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO Workers VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO Workers VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO Workers VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO Workers VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO Workers VALUES (6, 'Komal', 22, 'MP', 4500.00 );

Verification

Let's check whether the table has been created successfully or not by using the following query −

SELECT * FROM Workers;

Output

On executing the above query, it will generate the output as shown below −

+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitali |  25 | Mumbai    | 6500.00 |
|  5 | Hardik   |  27 | Bhopal    | 8500.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
+----+----------+-----+-----------+---------+

Example

In the following example, we are going to create the cursor for the above created Workers table and getting the status by using following query −

DECLARE work CURSOR  FOR 
SELECT AGE,ADDRESS FROM Workers
SELECT CURSOR_STATUS ('global','work') AS 'After declare'

Output

When we execute the above query, the output is obtained as follows −

+---------------+
| After declare |
+---------------+
|           -1  |
+---------------+

Example

Let's look into the following example, where we are going to open the cursor and getting the status by using the following query −

OPEN work
SELECT CURSOR_STATUS('global','work') AS 'After Open'

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                         After Open |
+------------------------------------+
|                                 1  |
+------------------------------------+

Example

Let's look at the another query, where we are going to close query and checking the status by using the following query −

CLOSE work
SELECT CURSOR_STATUS('global','work') AS 'After Close'

Output

On executing the above query, the output is displayed as follows −

+------------------------------------+
|                        After Close |
+------------------------------------+
|                                 -1 |
+------------------------------------+

Example

Let's look at the following query, where we are going to perform the fetch_status and read all the rows in the cursor variable by using the following query −

SELECT CURSOR_STATUS('global','work') AS 'isValid?'

Output

On executing the above query, the output is displayed as follows −

+-------------------+      
|          isValid? |
+-------------------+
|              -1   |
+-------------------+

Example

Let's look at the following query, where we are going to deallocate the cursor by using the following query −

DEALLOCATE work

Output

On executing the above query, the output is displayed as follows −

Commands completed successfully.