github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-cursor.md (about)

     1  ---
     2  layout: default
     3  title: Cursor - Reference Manual - csvq
     4  category: reference
     5  ---
     6  
     7  # Cursor
     8  
     9  A cursor is a pointer to a record in a view created by a select query.
    10  
    11  * [Usage Flow](#usage_flow)
    12  * [Cursor Operation](#operation)
    13  * [Cursor Status](#status)
    14  
    15  
    16  ## Usage Flow
    17  {: #usage_flow}
    18  
    19  1. [Declare](#declare) a cursor. 
    20  2. [Open](#open) the cursor to create the view. The pointer is set to before the first record.
    21  3. [Fetch](#fetch) the data of the referring record into variables.
    22     
    23     You can use the [While In Statement]({{ '/reference/control-flow.html#while_in_loop' | relative_url }}) to fetch all records in loop.
    24     
    25  4. [Close](#close) the cursor to discard the view.
    26  5. [Dispose](#dispose) the cursor to discard the cursor definition as necessary.
    27  
    28  Cursors are not affected by transactions and does not detect any update operations.
    29  The view refered by a cursor is retrieved when the cursor is opened, and it will be held until the cursor is closed.
    30  If you update any records in the tables that refered in any cursors, you may need to close and reopen the cursors.
    31  
    32  
    33  ## Cursor Operation
    34  {: #operation}
    35  
    36  ### Declare Cursor
    37  {: #declare}
    38  
    39  ```sql
    40  DECLARE cursor_name CURSOR FOR select_query;
    41  DECLARE cursor_name CURSOR FOR statement_name;
    42  ```
    43  
    44  _cursor_name_
    45  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    46  
    47  _select_query_
    48  : [Select Query]({{ '/reference/select-query.html' | relative_url }})
    49  
    50  _statement_name_
    51  : [Prepared Statement]({{ '/reference/prepared-statement.html' | relative_url }})
    52  
    53  ### Open Cursor
    54  {: #open}
    55  
    56  ```sql
    57  OPEN cursor_name;
    58  OPEN cursor_name USING replace_value [, replace_value ...];
    59  ```
    60  
    61  _cursor_name_
    62  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    63  
    64  _replace_value_
    65  : [replace value]({{ '/reference/prepared-statement.html#execute' | relative_url }}) for [Prepared Statement]({{ '/reference/prepared-statement.html' | relative_url }})
    66  
    67  ### Close Cursor
    68  {: #close}
    69  
    70  ```sql
    71  CLOSE cursor_name;
    72  ```
    73  
    74  _cursor_name_
    75  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    76  
    77  ### Dispose Cursor
    78  {: #dispose}
    79  
    80  ```sql
    81  DISPOSE CURSOR cursor_name;
    82  ```
    83  
    84  _cursor_name_
    85  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    86  
    87  ### Fetch Cursor
    88  {: #fetch}
    89  
    90  ```sql
    91  FETCH [position] cursor_name INTO variable [, variable ...];
    92  
    93  position
    94    : {NEXT|PRIOR|FIRST|LAST|ABSOLUTE number|RELATIVE number}
    95  ```
    96  
    97  _cursor_name_
    98  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    99  
   100  _variable_
   101  : [Variable]({{ '/reference/variable.html' | relative_url }})
   102  
   103  _number_
   104  : [integer]({{ '/reference/value.html#integer' | relative_url }})
   105  
   106  #### Position
   107  
   108  A Position keyword in a _fetch cursor statement_ specifies a record to set the pointer.
   109  If specified record does not exist, the _fetch cursor statement_ is set nulls to the variables.
   110  
   111  If any position keyword is not specified, then the NEXT keyword is used to fetch.
   112  
   113  NEXT
   114  : The pointer is set to the next record and return the record.
   115  
   116  PRIOR
   117  : The pointer is set to the previous record and return the record.
   118  
   119  FIRST
   120  : The pointer is set to the first record and return the record.
   121  
   122  LAST
   123  : The pointer is set to the last record and return the record.
   124  
   125  ABSOLUTE number
   126  : The pointer is set to the _number_-th record from the first record and return the record.
   127    _"ABSOLUTE 0"_ represents the first record.
   128  
   129  RELATIVE number
   130  : The pointer is set to the _number_-th record from the current record and return the record.
   131    _"RELATIVE 0"_ represents the current record.
   132  
   133  ## Cursor Status
   134  {: #status}
   135  
   136  You can get some cursor status by using following expressions.
   137  
   138  ### Cursor Is Open
   139  {: #cursor_is_open}
   140  
   141  ```sql
   142  CURSOR cursor_name IS [NOT] OPEN
   143  ```
   144  
   145  _cursor_name_
   146  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
   147  
   148  _return_
   149  : [ternary]({{ '/reference/statement.html#parsing#ternary' | relative_url }})
   150  
   151  Return a ternary value indicating whether the cursor is open.
   152  
   153  ### Cursor Is In Range
   154  {: #cursor_is_in_range}
   155  
   156  ```sql
   157  CURSOR cursor_name IS [NOT] IN RANGE
   158  ```
   159  
   160  _cursor_name_
   161  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
   162  
   163  _return_
   164  : [ternary]({{ '/reference/statement.html#parsing#ternary' | relative_url }})
   165  
   166  Return a ternary value indicating whether the cursor pointer is set to any existing record.
   167  If the cursor is closed, then an error is occurred.
   168  Before the first fetch, return UNKNOWN. 
   169  
   170  ### Cursor Count
   171  {: #cursor_count}
   172  
   173  ```sql
   174  CURSOR cursor_name COUNT
   175  ```
   176  
   177  _cursor_name_
   178  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
   179  
   180  _return_
   181  : [integer]({{ '/reference/statement.html#parsing#integer' | relative_url }})
   182  
   183  Return the number of rows in the view that the cursor is referring.