In Oracle and SQL Server you can declare a cursor, use OPEN, FETCH and CLOSE statements, but besides syntax there are some semantic differences.
In Oracle cursors are always local i.e visible only within the scope where they are declared. In SQL Server a cursor can be declared as LOCAL or GLOBAL.
By default, cursors are global in SQL Server i.e you can reference a cursor outside unless it is explicitly deallocated using DEALLOCATE statement.
Use the following query to check whether cursors are local or global by default in SQL Server (0 means cursors are global, 1 returned for local):
SQL Server:
-- Change db_name to specify your database name SELECT DATABASEPROPERTYEX('db_name', 'IsLocalCursorsDefault') # 0
Consider the following sample procedure that uses a cursor (do not pay attention to its logic, it is just to demonstate cursor declaration and operations):
Oracle:
CREATE OR REPLACE PROCEDURE sp_date IS v_date DATE; CURSOR cur IS SELECT SYSDATE FROM dual; BEGIN OPEN cur; FETCH cur INTO v_date; CLOSE cur; DBMS_OUTPUT.PUT_LINE(v_date); END; /
You can invoke this procedure multiple times, and it successfully declares, opens and closes the cursor each time:
Oracle:
CALL sp_date(); # 09-OCT-13 CALL sp_date(); #09-OCT-13
Now let's see what happens if you convert this procedure to SQL Server and use CLOSE statement without DEALLOCATE statement:
SQL Server:
CREATE PROCEDURE sp_date AS DECLARE @v_date DATE; DECLARE cur CURSOR FOR SELECT GETDATE(); OPEN cur; FETCH cur INTO @v_date; CLOSE cur; PRINT @v_date; GO
Without the DEALLOCATE statement, the second and any subsequent call to this procedure returns 'cursor already exists' error:
SQL Server:
EXEC sp_date # 2013-10-09 EXEC sp_date # Msg 16915, Level 16, State 1, Procedure sp_date, Line 4 # A cursor with the name 'cur' already exists. # 2013-10-09
Note that only the DECLARE statement fails, the procedure continues its execution and opens the previously declared cursor. A more serious problem arises if you have another procedure that uses a cursor with the same name, but different definition.
To release the cursor and to allow calling the procedure multiple times in a session, you use can use the DEALLOCATE statement in SQL Server:
SQL Server:
-- Cursor still GLOBAL but DEALLOCATE is used now CREATE PROCEDURE sp_date2 AS DECLARE @v_date DATE; DECLARE cur CURSOR FOR SELECT GETDATE(); OPEN cur; FETCH cur INTO @v_date; CLOSE cur; DEALLOCATE cur; PRINT @v_date; GO
Now you can successfully call the procedure multiple times:
SQL Server:
EXEC sp_date2 # 2013-10-09 EXEC sp_date2 # 2013-10-09
In the previous example, the cursor was declared as global (it is default), and although the DEALLOCATE statement released the reference to cursor in this simple example, it maybe not enough in some cases, for example:
To solve these issues specify specify LOCAL in the cursor declaration explicitly:
SQL Server:
-- Cursor is LOCAL now CREATE PROCEDURE sp_date3 AS DECLARE @v_date DATE; DECLARE cur CURSOR LOCAL FOR SELECT GETDATE(); OPEN cur; FETCH cur INTO @v_date; CLOSE cur; DEALLOCATE cur; PRINT @v_date; GO
Note that when a cursor is declared as LOCAL it is deallocated automatically when the procedure completes, but you can still use the DEALLOCATE statement too.
Note that SQL Server does not allow you to declare GLOBAL cursor in a user-defined function, and cursor are always LOCAL:
SQL Server:
CREATE FUNCTION fn_date() RETURNS DATE AS BEGIN DECLARE @v_date DATE; DECLARE cur CURSOR FOR SELECT GETDATE(); OPEN cur; FETCH cur INTO @v_date; CLOSE cur; RETURN @v_date; END GO
You can call a function multiple times without errors even if it does not use LOCAL and DEALLOCATE:
SQL Server:
SELECT dbo.fn_date() # 2013-10-09 SELECT dbo.fn_date() # 2013-10-09
Oracle allows you to declare a cursor with formal parameters. Then when you open the cursor you can specify the actual values to be used in the cursor.
Let's create a sample table with data:
Oracle:
CREATE TABLE colors (name VARCHAR2(70)); INSERT INTO colors VALUES ('Red'); INSERT INTO colors VALUES ('White'); INSERT INTO colors VALUES ('Blue');
Now let's create a sample procedure that uses a cursor with parameters:
Oracle:
CREATE OR REPLACE PROCEDURE sp_find_colors IS v_name VARCHAR2(70); CURSOR cur(p_name VARCHAR2) IS SELECT name FROM colors WHERE name = p_name; BEGIN OPEN cur('Red'); FETCH cur INTO v_name; CLOSE cur; DBMS_OUTPUT.PUT_LINE('Found: ' || v_name); OPEN cur('White'); FETCH cur INTO v_name; CLOSE cur; DBMS_OUTPUT.PUT_LINE('Found: ' || v_name); END; /
Now when you call this procedure it outputs 'Red' and 'White':
Oracle:
CALL sp_find_colors(); # Found: Red # Found: White
SQL Server does not support cursors with parameters, so you have to use variables or pass values explicitly.
Note that SQL Server gets values of variables during the cursor declaration, not during execution of OPEN cursor statement.
SQL Server:
CREATE PROCEDURE sp_find_colors AS DECLARE @v_name VARCHAR(70); -- @p_name is NULL, and this NULL value will be used in cursor DECLARE @p_name VARCHAR(70); DECLARE cur CURSOR LOCAL FOR SELECT name FROM colors WHERE name = @p_name; -- This assignment will not have any affect on @p_name used in the cursor SET @p_name = 'Red'; OPEN cur; FETCH cur INTO @v_name; CLOSE cur; PRINT 'Found: ' + ISNULL(@v_name, ''); -- This assignment also will not have any affect on @p_name used in the cursor SET @p_name = 'White'; OPEN cur; FETCH cur INTO @v_name; CLOSE cur; DEALLOCATE cur; PRINT 'Found: ' + ISNULL(@v_name, ''); GO
Now when you call this procedure it outputs empty values:
SQL Server:
EXEC sp_find_colors; # Found: # Found:
The reason is that te value of @p_name is NULL during the cursor declaration, and this NULL value is used in each OPEN statement, not the new value that was assigned just before openning the cursor.
To solve this problem you have to execute DECLARE CURSOR statement for each new parameter value:
SQL Server:
CREATE PROCEDURE sp_find_colors AS DECLARE @v_name VARCHAR(70); DECLARE @p_name VARCHAR(70); SET @p_name = 'Red'; DECLARE cur CURSOR LOCAL FOR SELECT name FROM colors WHERE name = @p_name; OPEN cur; FETCH cur INTO @v_name; CLOSE cur; DEALLOCATE cur; PRINT 'Found: ' + ISNULL(@v_name, ''); SET @p_name = 'White'; DECLARE cur CURSOR LOCAL FOR SELECT name FROM colors WHERE name = @p_name; OPEN cur; FETCH cur INTO @v_name; CLOSE cur; DEALLOCATE cur; PRINT 'Found: ' + ISNULL(@v_name, ''); GO
Now the procedure outputs correct values:
SQL Server:
EXEC sp_find_colors; # Found: Red # Found: White
For more information, see: