In this blog, I’ll be showing how a Microsoft cursor in T-SQL is similar to and different from the most similar cursor in Oracle. Oracle actually has 3 or 4 ways to do “explicit” cursors, so in this blog, we will look only at the one that is most similar.
I tried to format the code of each as close as possible to the other, so that the compare program would do a nice side-by-side view. I even named the variables and cursor names as similar as possible. Oracle users typically prefix variables with a lower-case “L” (which stands for local vs global).
I must admit, the Oracle code is more graceful. Unfortunately, Microsoft requires you to do the initial fetch, then continue to fetch in a loop. With Oracle, you can do the fetch in the loop, then exit out when needed. That’s the major reason that the code doesn’t line up well. I put a big red bracket around the corresponding loops in the picture below.
Output
They both produce the exact same results. But not that of course, in the real world, we don’t normally use “print” or DBMS_OUTPUT statements to report back data to any program or user. Had to do this to keep it simple.
Start FirstName=Neal LastName=Neal RowCount=1 FirstName=John LastName=John RowCount=2 FirstName=Fred LastName=Fred RowCount=3 FirstName=Barnie LastName=Barnie RowCount=4 End
Point by Point Discussion of Major Code Statements
The Declare Statement
In Microsoft, I usually repeat the DECLARE for each variable, I think it’s cleaner. In Oracle, there is an entire “DECLARE” section before the BEGIN/END block where your code goes. In the Microsoft example above, I used one DECLARE for the variables, but had to add a second one for the cursor. Variables in T-SQL begin with the @ sign.
The Cursor Definition (blue arrow)
The cursor in each example is called cur_get_employees, and each is reading the NEAL.EMPLOYEE table (schema name is Neal, table name is EMPLOYEE). The “INTO” is required in Oracle, at least for this style of cursor. In a future blog, I hope to show other types of cursors.
--T-SQL
PRINT 'FirstName=' + @l_firstname +
' LastName=' + @l_firstname +
' RowCount=' + convert(varchar(3),@l_rowcount)
-- PL/SQL
DBMS_OUTPUT.PUT_LINE('FirstName=' || l_firstname ||
' LasttName=' || l_lastname ||
' RowCount=' || cur_get_employees%ROWCOUNT);
The T-SQL “Print” command writes to the “Message” output area, and is similar the Oracle function DBMS_OUTPUT.PUT_LINE. Oracle uses double pipe symbols to concatenate, while T-SQL uses the plus sign. T-SQL requires specific conversion of numbers to strings before they can be concatenated.
In Oracle, each cursor has a built-in system variable called %ROWCOUNT. I haven’t discovered anything like that in SQL, so I just incremented my own counter.
The Open/Close/DeAllocate Cursor Statements
The open/close are actually identical. SQL further has a Deallocate statement to free memory when done with the cursor. My guess is that in Oracle, that is handled when the “END” of the block occurs.
The Fetch and Loop Statements
The fetch is actually identical as well. The only big difference, as stated at the beginning of this article, is that Oracle doesn’t require the initial fetch before beginning the loop. NOTE: I made one bug in my code above and it’s correct in the samples below. The “PRINT” statement only needs to be included once at the beginning of the WHILE loop. My code actually caused the last row to be printed twice, and I didn’t notice until after I did the elaborate picture above.
Microsoft returns a status in a system variable called @@FETCH_STATUS. As long as the value is zero, we need to keep looping. Oracle sets a similar variable, which is the cursor name follwoed by %NOTFOUND, and Oracle’s PL/SQL provides a nice way to jump out of the loop “EXIT WHEN cur_get_employees%NOTFOUND”.
If you want to copy/paste the code and try it for yourself, here is the code. See yesterday’s blog (Comparing “Create Table” between Oracle and Microsoft SQLServer) to get the “Create Table” and “Insert Employee” statements.
Microsoft T-SQL Code
use Employees DECLARE @l_empid INT, @l_firstname varchar(20), @l_lastname varchar(20), @l_state varchar(2), @l_rowcount int = 0 ; DECLARE cur_get_employees CURSOR FOR SELECT EMPID, FIRSTNAME, LASTNAME, STATE FROM NEAL.EMPLOYEE; PRINT 'Start'; OPEN cur_get_employees; SET @l_rowcount = @l_rowcount + 1 FETCH cur_get_employees INTO @l_empid, @l_firstname, @l_lastname, @l_state; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'FirstName=' + @l_firstname + ' LastName=' + @l_firstname + ' RowCount=' + convert(varchar(3),@l_rowcount) SET @l_rowcount = @l_rowcount + 1; FETCH cur_get_employees INTO @l_empid, @l_firstname, @l_lastname, @l_state; END PRINT 'End' CLOSE cur_get_employees DEALLOCATE cur_get_employees
Oracle PL/SQL Code
SET SERVEROUTPUT OFF;
DECLARE
l_empid EMPLOYEE.EMPID%TYPE;
l_firstname EMPLOYEE.FIRSTNAME%TYPE;
l_lastname EMPLOYEE.LASTNAME%TYPE;
l_state EMPLOYEE.STATE%TYPE;
CURSOR cur_get_employees IS
SELECT EMPID, FIRSTNAME, LASTNAME, STATE
INTO l_empid, l_firstname, l_lastname, l_state
FROM NEAL.EMPLOYEE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start');
OPEN cur_get_employees;
LOOP
FETCH cur_get_employees
INTO l_empid, l_firstname, l_lastname, l_state;
EXIT WHEN cur_get_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('FirstName=' || l_firstname ||
' LasttName=' || l_lastname ||
' RowCount=' || cur_get_employees%ROWCOUNT);
END LOOP;
DBMS_OUTPUT.PUT_LINE('End');
CLOSE cur_get_employees;
END;
And finally, I thought I would include a screen shot of the two graphical interfaces:
Microsoft SSMS – SQL Studio Management Server
Oracle – SQL Developer
Note: You have to click the green plus sign in the “Dbms Output (lower right)” pane, in order for the DBMS_OUTPUT.PUT_LINE statements to be captured and displayed. Then you have to click the little eraser symbol, if you don’t want each run to add to the prior run.
