Create Sample SQLScript Cursor on SAP HANA Database

This SAP HANA SQLScript tutorial shows how to declare and create SQL cursor for HANA database programmers. It is important to avoid cursors in SQL developments as a principal because cursors are just like ABAP LOOP statements dealing with each record one by one.
On the other hand, for code performance considerations it is better to deal with data using set based operations instead of record based operations like executing SQL cursors in SQLScript codes on a SAP HANA database.

If there is no other way, SQL programmer can use SQLScript cursors in native HANA database developments or in SAP HANA AMDP procedures as shown in this tutorial.

SAP HANA Database SQLScript Cursor Sample for SQL Programmer

Basic syntax to create a SQL cursor on SAP HANA database SQLScript code, developers can use below cursor example.

A SQLScript cursor is defined by "DECLARE CURSOR" command following the cursor name and SELECT statement which is the source data of the SQL cursor.


DECLARE CURSOR cursorName FOR

SELECT ...

Code

To loop through all the records or rows within the SQL cursor, database developers can use following template


FOR cursorRow AS cursorName

DO

-- sqlscript codes

END FOR;

Code

Business logic related with the purpose of the SQL cursor construction is executed for each row of the cursor declaration statement one by one between FOR and END FOR statements.

SQLScript cursor

ABAP SQL programmers can see easily that by using cursorname.columnname format, it is possible to read column values of the current SQLScript cursor loop step.


DO

BEGIN



 DECLARE CURSOR cursorSample FOR

  select BUKRS from "SAPABAP1".T001 where mandt <> '000';



 declare vBUKRS nvarchar(256);

 declare vRowCount int;



 for cursorRow as cursorSample

 do

  -- logic with sqlscript code within cursor loop



  vBUKRS := cursorRow.BUKRS;

  -- select :vBUKRS from dummy;



  tblSalesOrg = select bukrs, vkorg from "SAPABAP1".TVKO

   where bukrs = :vBUKRS;

  select count(*) into vRowCount from :tblSalesOrg;

  if (:vRowCount > 0) then

   select

    cursorSample::rowcount as rownum,

    :vBUKRS as vBUKRS,

    :vRowCount as vRowCount,

    *

   from :tblSalesOrg;

  end if;



  -- sql code in cursor loop (END)

 end for;



END

Code

While processing each row of the SQL cursor between FOR, END FOR statements SQLScript developer can get the current loop number by using cursorName::rowcount

SAP HANA database SQL cursor code sample

If you execute above SAP SQLScript cursor example code by modifying the SAP schema according to your database configuration, you can fetch some data displayed similar to ones in below screenshot. The output is displaying company codes with sales organizations and some cursor loop variables like row number within the cursor loop.

SQLScript cursor execution on SAP HANA database

ABAP programmers who build SQL scripts should remember the most important rule related with SQL Cursors: Avoid using cursor if possible

For example, the same output provided by the above sample SQL cursor query can be fetched by using following single SELECT statement


select tvko.bukrs, tvko.vkorg

from "SAPABAP1".T001 as t001

inner join "SAPABAP1".TVKO as tvko on t001.bukrs = tvko.bukrs

where t001.mandt <> '000'

Code