Oracle cursor usage

I. What is a cursor?

Cursor letters are the moving cursor.

Description in database language: A cursor is the entity mapped to a row of data in the result set. With a cursor, you can access any row of data in the result set, after the cursor is located on a row, the row data can be operated, such as extracting data from the current row.

Ii. cursor Classification

Show cursor and hermit cursor

Four steps are required to display the cursor:

1. Declare a cursor

Cursor mycursor (vartype number) is

Select ID from Table1

Where id = vartype

2. Open the cursor

Open mycursor (0, 000627)

3. Read cursor

Fetch mycursor into varno

4. Close the cursor

Close mycursor

Iii. cursor attributes

Oracle cursor has four attributes:

% Isopen: determines whether the cursor is opened. If % isopen is enabled, it is equal to true. Otherwise, it is equal to false.

% Found and % notfound determine whether the row where the cursor is located is valid. If % found is valid, it is true; otherwise, it is false.

% Rowcount returns the number of records read by the cursor until the current position.

Iv. Example

Declare

Varno varchar (20 );

Varprice varchar (20 );

Cursor mycursor (vartype number) is

Select emp_no.emp_zc from cus_emp_basic

Where com_no = vartype

Begin

If mycursor % isopen = false then

Open mycursor (0, 000627)

End if;

Fecth mycursor into varno. varprice;

While mycursor % found

Dbms_output.putlne (varno | '.' varprice );

If mycursor % rowcount = 2 then

Exit;

End if;

Fetch mycursor into varno. varprice;

End;