What is a cursor?

What is a cursor?

Declare
Object_name varchar2 (24 );
Type ref_cursor_type is ref cursor;
Cursor_object ref_cursor_type;
Begin

Open cursor_object
'
Select regexp_substr (''3h, 4c, 5e, 6b, 7a, 8g'', ''[^,] +'', 1, level)
From dual
Connect by level <= regexp_count (''3h, 4c, 5e, 6b, 7a, 8g'', '','') + 1
';

Loop
Begin
Fetch cursor_object into object_name;
Exit when cursor_object % notFound;

If object_name = '4c 'then
Dbms_output.put_line ('good: '| object_name );
End if;
End;
End loop;
End;

How to use SQL cursors

A. Use FETCH in A simple cursor
In the following example, a simple cursor is declared for the row whose last name starts with B in the authors table, and these rows are extracted one by one using FETCH NEXT. The FETCH statement returns the value of the column specified by declare cursor in the form of a single row result set.

USE pubs
GO
DECLARE authors_cursor CURSOR
SELECT au_lname FROM authors
WHERE au_lname LIKE "B %"
Order by au_lname

OPEN authors_cursor

-- Perform the first fetch.
Fetch next from authors_cursor

-- Check @ FETCH_STATUS to see if there are any more rows to fetch.
WHILE @ FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
Fetch next from authors_cursor
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

Au_lname
----------------------------------------
Bennet
Au_lname
----------------------------------------
Blotchet-Hils
Au_lname
----------------------------------------

B. Use FETCH to store values into Variables
The following example is similar to the preceding example, but the output of the FETCH statement is stored in local variables instead of directly returned to the client. The PRINT statement combines variables into a single string and returns them to the client.

USE pubs
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @ au_lname varchar (40), @ au_fname varchar (20)

DECLARE authors_cursor CURSOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE "B %"
Order by au_lname, au_fname

OPEN authors_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 authors_cursor
INTO @ au_lname, @ a ...... the remaining full text>

How to use SQL cursors

A. Use FETCH in A simple cursor
In the following example, a simple cursor is declared for the row whose last name starts with B in the authors table, and these rows are extracted one by one using FETCH NEXT. The FETCH statement returns the value of the column specified by declare cursor in the form of a single row result set.

USE pubs
GO
DECLARE authors_cursor CURSOR
SELECT au_lname FROM authors
WHERE au_lname LIKE "B %"
Order by au_lname

OPEN authors_cursor

-- Perform the first fetch.
Fetch next from authors_cursor

-- Check @ FETCH_STATUS to see if there are any more rows to fetch.
WHILE @ FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
Fetch next from authors_cursor
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

Au_lname
----------------------------------------
Bennet
Au_lname
----------------------------------------
Blotchet-Hils
Au_lname
----------------------------------------

B. Use FETCH to store values into Variables
The following example is similar to the preceding example, but the output of the FETCH statement is stored in local variables instead of directly returned to the client. The PRINT statement combines variables into a single string and returns them to the client.

USE pubs
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @ au_lname varchar (40), @ au_fname varchar (20)

DECLARE authors_cursor CURSOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE "B %"
Order by au_lname, au_fname

OPEN authors_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 authors_cursor
INTO @ au_lname, @ a ...... the remaining full text>