Using CURSOR in Different Databases

• Download source files - 2 KB

Introduction

In this post, we are going to create a cursor in databases like SQL Server, Oracle, MySQL, PostgreSQL. Here, we will be able to find basic similarities and differences between implementation and execution process while working with the same CURSOR clause in different databases.

Background

What are we going to do?

1. Create a CURSOR with targeted rows
2. Open/Close a CURSOR
3. Iterate/loop through CURSOR rows
4. Skip a row depending on conditions
5. Break loop if needed

Table and Data

Let us create some sample data before getting started with the cursor.

/*table*/
--DROP TABLE People;
CREATE TABLE People (
  FirstName VARCHAR(100),
  LastName VARCHAR(100)
);

/*data*/
--DELETE FROM People;
INSERT INTO People VALUES ('Elon', 'Musk');       /*result 3*/
INSERT INTO People VALUES ('Den', 'Parker');      /*skip this item in loop, will not print*/
INSERT INTO People VALUES ('Gordon', 'Ramsay');   /*break loop here, will not print*/
INSERT INTO People VALUES ('Bryan', 'Adams');     /*result 2*/
INSERT INTO People VALUES ('Aaron', 'Lord');      /*result 1*/
INSERT INTO People VALUES ('Little', 'Alton');    /*as loop out, will not print*/

/*select*/
SELECT * FROM People ORDER BY FirstName;          /*this query will be used in all cursor*/

SQL Server

Here, we are creating a cursor for SQL Server. Have a look at the message window, the result should look as expected.

DECLARE 
    @firstName VARCHAR(100),
    @lastName VARCHAR(100),
    @fullName VARCHAR(200);
DECLARE
    peopleCrs CURSOR FOR
    SELECT FirstName, LastName FROM People ORDER BY FirstName, LastName

OPEN peopleCrs
    FETCH NEXT FROM peopleCrs INTO @firstName, @lastName
    SET @fullName = '';

    WHILE @@FETCH_STATUS = 0    /*cursor iteration*/
    BEGIN  
        IF @firstName = 'Gordon'
        BEGIN
            BREAK;              /*get out of cursor*/
        END
        IF @firstName = 'Den'
        BEGIN
            GOTO Refatch;       /*ignore item*/
        END 

        SET  @fullName = @firstName +' ' +@lastName;
        PRINT @fullName;   

        Refatch:
            FETCH NEXT FROM peopleCrs INTO @firstName, @lastName 
    END
CLOSE peopleCrs
DEALLOCATE peopleCrs;

Oracle

Here, we are creating a cursor for Oracle. The result will be printed at the DBMS_OUTPUT message window.

DECLARE
    FIRST_NAME VARCHAR(100);
    LAST_NAME VARCHAR(100);
    FULL_NAME VARCHAR(100);
   
    CURSOR CUR_PEOPLE IS 
        SELECT FirstName, LastName FROM People ORDER BY FirstName, LastName;
BEGIN
    OPEN CUR_PEOPLE;
        LOOP
            FETCH CUR_PEOPLE INTO FIRST_NAME, LAST_NAME;
            FULL_NAME := ''; 
            
            IF CUR_PEOPLE % NOTFOUND    /*cursor iteration*/      
            THEN
                EXIT;
            END IF;
            
            IF FIRST_NAME = 'Gordon'    /*get out of cursor*/
            THEN
                EXIT;                   
            END IF;
            IF FIRST_NAME = 'Den'       /*ignore item*/
            THEN
                CONTINUE;                   
            END IF;
            
            FULL_NAME := CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME);
            DBMS_OUTPUT.put_line(FULL_NAME);                                               
        END LOOP;   
    CLOSE CUR_PEOPLE;
END;

If you are using Toad, enable DBMS_OUTPUT in Toad for Oracle: https://www.foxinfotech.in/2018/09/how-to-enable-dbms_output-in-toad-for-oracle.html.

MySQL

MySQL doesn't have the option to print a message. So we are going to SELECT the entire result.

/*create sp*/
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_people$$
CREATE
    PROCEDURE sp_people()
    BEGIN
     DECLARE first_name VARCHAR(100);
    DECLARE last_name VARCHAR(100);
    DECLARE full_name VARCHAR(200);
    DECLARE result VARCHAR(800);
    
    DECLARE people_cursor_finished BOOLEAN;
    DECLARE people_cursor CURSOR FOR 
        SELECT firstname, lastname FROM people ORDER BY Firstname ASC;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET people_cursor_finished = TRUE;

    OPEN people_cursor;
        SET result = '';
        people_cursor_loop: LOOP    
            FETCH people_cursor INTO first_name, last_name;    
            SET full_name = '';    
    
            IF people_cursor_finished THEN         /*cursor iteration*/
                LEAVE people_cursor_loop;
            END IF;                
            IF first_name = 'Gordon' THEN          /*get out of cursor*/
                LEAVE people_cursor_loop;
            END IF;    
            IF first_name = 'Den' THEN             /*ignore item*/
                ITERATE people_cursor_loop;
            END IF;    

            SET full_name = CONCAT(first_name, " ", last_name);
            SET result = CONCAT(result, ", ", full_name);
        END LOOP people_cursor_loop;
    CLOSE people_cursor;
    
    SELECT result;
    END$$
DELIMITER;

Now, calling the created stored procedure.

/*run*/
CALL sp_people();

Unlike SQL Server or Oracle, in MySQL, we can use a cursor only inside a stored procedure or a function.

Cursor without a stored procedure, possible or not: https://stackoverflow.com/questions/14739940/can-i-run-a-loop-in-mysql-without-using-a-procedure-function

PostgreSQL

Like MySQL, here we are also going to SELECT the entire result.

But there is also an option of print, using RAISE NOTICE.

/*create function*/
CREATE OR REPLACE FUNCTION fn_people()
    RETURNS VARCHAR(800) AS $$
DECLARE 
    first_name VARCHAR(100);
    last_name VARCHAR(100);
    full_name VARCHAR(200);
    result_string VARCHAR(800);    
    people_cursor CURSOR FOR 
        SELECT firstname, lastname FROM people ORDER BY Firstname ASC;
BEGIN    
    OPEN people_cursor; 
        result_string := '';
    LOOP
        full_name := '';
        FETCH people_cursor INTO first_name, last_name;
        EXIT WHEN NOT FOUND;                 /*cursor iteration*/

        IF first_name = 'Gordon' THEN 
            EXIT;                            /*get out of cursor*/
        END IF;
        IF first_name = 'Den' THEN 
            CONTINUE;                        /*ignore item*/
        END IF;

        full_name := first_name || ' ' || last_name;
        result_string := result_string || ', ' || full_name;
    END LOOP;
    CLOSE people_cursor;

    RETURN result_string;
END; $$
LANGUAGE plpgsql;

Now, calling the created function.

/*run*/
SELECT fn_people();

Cursor without a function, possible or not: https://stackoverflow.com/questions/2569504/how-can-i-execute-pl-pgsql-code-without-creating-a-function

Limitations

Things may vary depending on database versions.

My working database versions are:

• Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
• Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
• MySQL 5.5.61
• PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit

Check Database Version

Here are few SQL queries to check our working database versions:

SELECT @@VERSION;            /*sql server*/       /*also works with mysql*/
SELECT * FROM V$VERSION;     /*oracle*/
SELECT VERSION();            /*mysql*/            /*also works with postgre*/
SELECT VERSION();            /*postgre sql*/      /*also works with mysql*/

Please find the SQL files as an attachment.

History

• 28^th May, 2019: Initial version