CURSOR in HANA SQL
A cursor is bound to a query which optionally can be parameterized and is used to fetch single row at a time from the query result set. But it is recommended to avoid use of cursor if same thing can be achieved by SQL as the cursor can not be optimized like SQL.
A cursor has 4 parts- Declaration, OPEN, FETCH and CLOSE. There are few cursor attributes that tells whether it found the result or not(NOTFOUND) or the cursor is open or close( ISCLOSED). Let’s start to see the details-
Below we have a AMDP Procedure with a CURSOR (parameterized) and bound to the query. Later point of time we can use OPEN & FETCH to pass the input and get the result and finally CLOSE the cursor.
The test program that calls the amdp method.
Debug details-
The result set gives the value-
The result set gives the value-
Another result-
Now let’s use some of the cursor attributes –
ISCLOSED – returns YES or NO depending on the cursor is close or open.
NOTFOUND- returns YES or NO depending on the row found or not from the query result set.
Debug details-
It is quite possible to LOOP over the CURSOR to get all the rows one by one from the query result set.
Test Program-
In the underlying DB we have 5 records-
If we loop then we can see all rows(few rows details shown)-
SAPCODES 