- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
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 −
Return Value | Cursor Name | Cursor Variable |
---|---|---|
1 | The cursor results atleast one row. | The allocated cursor to this variable is open. |
0 | The cursor result set is empty. | The allocated cursor to this variable is open, but te result is definitely empty. |
-1 | The cursor is closed. | The allocated cursor to this variable is closed. |
-2 | Not Applicable. | The previously called procedure did not assign a cursor to this OUTPUT variable. |
-3 | The cursor with specified name does not exist. | The allocated cursor to this variable id does not exist,or if exists no cursor is yet allocated to it. |
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.
To Continue Learning Please Login
Login with Google