Home / Questions / 173102
Accepted
Sergio AG
Asked: 2020-06-15 02:47:42 +0800 CST 2020-06-15 02:47:42 +0800 CST

Differences between FETCH ABSOLUTE and FETCH RELATIVE

  • 772

I'm working with cursors whose behavior parameter is SCROLLbut I don't understand what the purpose of two of its functions is when doing the FETCH.

I don't understand what they do or what they are used for FETCH ABSOLUTE and FETCH RELATIVE.

sql

1 Answers

  1. Best Answer

    As you know, the operator FETCHis used to extract rows from the resulting cursor according to the query that you have invoked, when you use the option as you say, SCROLLit is used to declare that the cursor of the resulting information will be multidirectional.

    What does this mean? If you, when creating a cursor, do not declare it with the option SCROLL, it is considered a unidirectional type cursor, which indicates that the result or the table of results that you receive from a certain cursor can only be traversed in one direction and by Therefore, each result of said cursor can only be read once at most.

    On the other hand, when declaring the option SCROLL, the resulting cursor or your results table will be able to read it in any direction thanks to the operators

    FETCH
    [ [ NEXT | PRIOR | FIRST | LAST
    | ABSOLUTE { n | @nvar }
    | RELATIVE { n | @nvar }
    ]

    NEXT: Read the next row, if you just started reading the cursor the result will be the first record of your cursor (only option for cursors NO SCROLL).

    PRIOR: Read the previous row.

    FIRST: Read the first row of the cursor.

    LAST: Read the last row of the cursor.

    ABSOLUTE n: Read the cursor row indicating position "n" For example, if the cursor has 7 results:

      Col1        Col2
 Resultado     1
 Resultado     2
 Resultado     3
 Resultado     4
 Resultado     5
 Resultado     6
 Resultado     7

    and you define the following:

     FETCH ABSOLUTE 2 FROM cursor;

    You will get the record (Result 2)

    RELATIVE n: Read the record that indicates the value of "n" based on the row you are in. For example, given the previous situation, you are in row number 2 of the cursor and you define the following:

     FETCH RELATIVE 3 FROM cursor;

    You will get the record (Result 5)

    I hope the answer is helpful, in the same way take a read of this book, it is good and it clearly explains the operation of the cursors.

    https://books.google.com.mx/books?id=vsYTINIEwd4C&pg=PT210&lpg=PT210&dq=para+que+sirve+fetch+absolute+sql+server&source=bl&ots=k1WdPvrqPP&sig=jkMSKjjm7MzTaMchZCnDWAEJULM&hl=es-419&sa=X&ved=0ahUKEwjxlP6GrNPbAhVROKwKHUYID20Q6AEIpwEwDg# v=onepage&q=for%20that%20serves%20fetch%20absolute%20sql%20server&f=false

    • 1