DECLARE @Last Name varchar(50), @First Name varchar(50); DECLARE contact_cursor CURSOR FOR SELECT Last Name, First Name FROM Person.Person WHERE Last Name LIKE ' B%' ORDER BY Last Name, First Name; OPEN contact_cursor; -- Perform the first fetch and store the values in variables.-- Note: The variables are in the same order as the columns -- in the SELECT statement.FETCH NEXT FROM contact_cursor INTO @Last Name, @First Name; -- Check @@FETCH_STATUS to see if there are any more rows to fetch.FETCH RELATIVE 3 FROM contact_cursor; -- Fetch the row that is two rows prior to the current row.FETCH RELATIVE -2 FROM contact_cursor; CLOSE contact_cursor; DEALLOCATE contact_cursor; GO Note The feedback system for this content will be changing soon. If content within a comment thread is important to you, please save a copy.If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.
Before using an explicit cursor, you must declare it in the declaration section of a block or package as shown below: is the name of the cursor declared in the declaration section.
The number of variables must match the number of columns in the cursor select list.
If the SCROLL option is not specified in an ISO style DECLARE CURSOR statement, NEXT is the only FETCH option supported.
When you open a cursor, Oracle parses the query, binds variables, and executes the SQL statement.
Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.
If SCROLL is specified in an ISO style DECLARE CURSOR, all FETCH options are supported.