DECLARE CURSOR - Sybase ASE to MariaDB Migration

You can use DECLARE CURSOR statement to declare a cursor in both Sybase ASE and MariaDB.

But Sybase ASE allows you to put DECLARE statements everywhere in the code while MariaDB requires cursor declarations to be specified after the variable declarations and before any DML statement.

In Sybase ASE you can specify DECLARE CURSOR at any position, for example, in the following code we declare 2 cursors:

Sybase ASE:

  -- Simplified procedure just to demonstrate variable references in cursors
  CREATE OR REPLACE PROCEDURE showOrdersSummary
  AS
     DECLARE @region VARCHAR(20)
     DECLARE @region_out VARCHAR(20)
      -- Orders for US
     DECLARE cur CURSOR FOR SELECT @region
     SET @region = 'US'  
     OPEN cur
     FETCH cur INTO @region_out
     SELECT @region_out
     -- ... 
     CLOSE cur
     DEALLOCATE cur
     -- Orders for all regions
     DECLARE cur2 CURSOR FOR SELECT @region
     SET @region = 'ALL'  
     OPEN cur2
     FETCH cur2 INTO @region_out
     SELECT @region_out
     -- ... 
     CLOSE cur2
     DEALLOCATE cur2
  GO

Now if you execute this procedure you will get 2 result sets:

  EXEC showOrdersSummary  
  -- Result Set 1:
  -- US
  -- Result Set 2:
  -- ALL

So you can see that @region variable value is taken at OPEN cursor execution time, not at DECLARE CURSOR execution time.

If you use SQLine SQL Converter to convert this procedure to MariaDB, you will get the following result:

MariaDB:

  DELIMITER //
 
  -- Simplified procedure just to demonstrate variable references in cursors
  CREATE PROCEDURE showOrdersSummary()
    BEGIN
       DECLARE v_region VARCHAR(20);
       DECLARE v_region_out VARCHAR(20);
       -- Orders for US
      DECLARE cur CURSOR FOR SELECT v_region;
      DECLARE cur2 CURSOR FOR SELECT v_region;
      SET v_region = 'US';  
      OPEN cur;
      FETCH cur INTO v_region_out;
      SELECT v_region_out;
      -- ... 
      CLOSE cur;
      -- Orders for all regions
      SET v_region = 'ALL';  
      OPEN cur2;
      FETCH cur2 INTO v_region_out;
      SELECT v_region_out;
      -- ... 
      CLOSE cur2;
  END;
  //
  DELIMITER ;

Note that SQLines SQL Converter moved the second cursor declaration. Does it affect the variable assigment? Let's see:

  CALL showOrdersSummary()  
  -- Result Set 1:
  -- US
  -- Result Set 2:
  -- ALL

Fortunately, MariaDB also take the variable values referenced in the cursor during OPEN cursor execution time, not at the DECLARE CURSOR execution time.

For more information, see Sybase ASE to MariaDB Migration.