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.

Cursor_Compare_Oracle_to_Microsoft_SQLServer

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

Cursor_Compare_Sample_Program_Microsoft_SSMS

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.
Cursor_Compare_Sample_Program_Oracle_SQL_Developer

Filed under: Oracle & SQLServer