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

For this blog, I create an employee table using the Oracle’s GUI tool called “SQL Developer”.  I then exported that syntax to a file.With a few changes I added to Microsoft SQL Server (after adding the schema first), and modified it to include a primary key. My only reason to port this over was to do some more blogs and comparison of how certain features work in Oracle’s PL/SQL, and how they work in Microsoft’s T-SQL; so stay tuned for more comparisons in future blogs.

I then reformatted it to make it “skinny” so I could so a side-by-side compare and squeeze it in the frame below.  Identity columns were not introduced into Oracle until release 12c, and I was using 11g for this demo.  Unfortunately, all the data types had to be changed.  Microsoft uses square brackets around table and column names (just in case they conflict with reserved words), while Oracle uses double quotes.  When I defined the table in Oracle, I should have probably made the EMPID of type PLS_INTEGER instead of NUMBER.

Create_Table_Oracle_SQLServer_Side_by_Side_Compare

Oracle also generated the insert statements for me. I had to correct the EMPID on “Barnie Rubble” from a 3 to 4, because with the primary key constraint, I couldn’t load the data into SQL. Had I put an index on Oracle with a unique constraint, that should have also solved the problem. Other than that issue, the “INSERT” statements were 100% compatible as they are using standard ANSI SQL.

Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (1,'Neal','Walters','TX');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (2,'John','Doe','OK');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Fred','Flinstone',null);
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Barnie','Rubble',null);

However, since I added an identity field, I had to turn identity_insert off/on while running the query.

set identity_insert NEAL.EMPLOYEE on
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (1,'Neal','Walters','TX');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (2,'John','Doe','OK');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Fred','Flinstone',null);
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (4,'Barnie','Rubble',null);
set identity_insert NEAL.EMPLOYEE off

Oracle Create Table

  CREATE TABLE "NEAL"."EMPLOYEE" 
   (	"EMPID" NUMBER, 
	"FIRSTNAME" VARCHAR2(20 BYTE), 
	"LASTNAME" VARCHAR2(20 BYTE), 
	"STATE" VARCHAR2(2 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 
  MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 
  FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT 
  FLASH_CACHE DEFAULT 
  CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


Microsoft SQLServer Create Table

CREATE TABLE [Neal].[EMPLOYEE](
	[EMPID] [int] IDENTITY(1,1) NOT NULL, 
	[FIRSTNAME] [varchar](20) NULL,
	[LASTNAME] [varchar](20) NULL,
	[STATE] [varchar](2) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[EMPID] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
) ON [PRIMARY]

GO