If not specified it means WITHOUT RETURN. The optional determines whether to return the cursor result set to the invoker such as another SQL routine or host language. If not specified it maintains WITHOUT HOLD option. The optional defines whether to hold or automatically close when the transaction due to the cursor is committed. If not specified it assumes NO SCROLL option. The optional defines the scroll ability of the cursor. If not specified it assumes ASENSITIVE option. SENSITIVE means cursor is affected by changes, INSENSITIVE means cursor is not affected and ASENSITIVE means changes may or may not be visible to the cursor. The optional part such as signifies whether the cursor is sensitive to changes and whether to reflect them in the query result. The essential part of a cursor declaration is as follows: DECLARE FOR OPEN: The open statement executes the query expression assigned and make ready query result for subsequent FETCH.įETCH: Retrieves data values into variables which then can be passed to host programming language or to other embedded SQL statements.ĬLOSE: The cursor is closed from fetching any more query result. There are four basic steps to declare a cursor:ĭECLARE CURSOR: The declaration begins by giving cursor a name and assigning the query expression to be invoked when the cursor is opened. We must use SQL statements to fully implement cursor functionality because simply declaring a cursor is not enough to extract data from a SQL database. But there is a general agreement on how the cursor should be written. Note that, different SQL implementations support the use of cursors in a different way. A name is given, there are statements to open the cursor, retrieve the query result, and finally close the cursor. Declaring a CursorĬursors are declared much like a variable. Other rows are extracted subsequently, in sequence until the cursor is closed. The cursor then is opened much like a file and extract single row from the query result. As a result, cursor is declared as an embedded SQL statement within the application programming language. The result may contain many rows, but the host application language can deal with one row at a time. The query result from the above statement returns employee details of all those employees whose birth date falls on the current day of a particular month. WHERE MONTH(birth_date) = MONTH(CURRENT_DATE) Consider the following SQL query: SELECT emp_no, first_name, last_name, birth_date A cursor can traverse through all rows of a query result focusing on one row at a time. The cursor, therefore, serves as a pointer that enables programming language to process query result one record at a time. The cursor acts like an iterator variable to loop over tuples returned by the SQL query and extract individual values within each tuple which then can be mapped to appropriate type of program variables. The mechanism of looping over tuples is necessary to iterate over tuples and their attribute values. Therefore, it is essential that SQL query result maps with the data structure supported by the programming language. Host programming languages typically works on individual data values of tuple returned by the query.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |