Oracle PL/SQL basics 2 (cursor learning)

Cursor Learning

1. What is a cursor:
A cursor is a swimming cursor.
Description in the database language: the cursor is the object mapped to the position of a row of data in the result set, with the cursor
You can access any row of data in the result set. After placing the cursor on a row, you can perform operations on the row of data, such as extracting the current
Row data.
Ii. Categories of cursors:
Explicit and implicit cursors

Four steps are required to display the cursor:
1. Declare a cursor

Cursor mycur (vartype number) is
Select emp_no, emp_zc from cus_emp_basic
Where com_no = vartype;

2. Open the cursor
Open mycur (000627) Note: 000627: Parameter

3. Read data
Fetch mycur into varno, varprice;

4. Close the cursor
Close mycur;
Iii. cursor attributes
Oracle cursor has four attributes: % isopen, % found, % notfound, % rowcount

% Isopen: determines whether the cursor is opened. If % isopen is enabled, it is equal to true. Otherwise, it is equal to false.
% Found % notfound: determines whether the row where the cursor is located is valid. If the row is valid, % foundd is equal to true; otherwise, false.
% Rowcount returns the number of rows read by the cursor until the current position.

4> example:
 

Set serveroutput on;
Declare
Varno varchar2 (20 );
Varprice varchar2 (20 );

Cursor mycur (vartype number) is
Select emp_no, emp_zc from cus_emp_basic
Where com_no = vartype;
Begin

If mycur % isopen = false then

Open mycur (1, 000627 );
End if;
Fetch mycur into varno, varprice;
While mycur % found
Loop
Dbms_output.put_line (varno | ',' | varprice );
If mycur % rowcount = 2 then
Exit;
End if;
Fetch mycur into varno, varprice;

End loop;
Close mycur;

End;

 

 

The structure of PL/SQL records is similar to the structure in C. It is a logical unit consisting of a group of data items.
PL/SQL records are not stored in the database. They are stored in the memory space like variables. When using records, you must first define the record structure.
And then declare the record variable. PL/SQL records can be viewed as user-defined data types.

Set serveroutput on;
Declare

Type person is record
(
Empno cus_emp_basic.emp_no % type,
Empzc cus_emp_basic.emp_zc % type );

Person1 person;

Cursor mycur (vartype number) is
Select emp_no, emp_zc from cus_emp_basic
Where com_no = vartype;

Begin
If mycur % isopen = false then
Open mycur (1, 000627 );
End if;

Loop
Fetch mycur into person1;
Exit when mycur % notfound;
Dbms_output.put_line ('employee No.: '| person1.empno |', address: '| person1.empzc );
End loop;
Close mycur;
End;

 

Typical cursor For Loop

The cursor for loop shows a quick way to use the cursor. It uses the for loop to read rows in the result set in sequence.
Data. When the form loop starts, the cursor is automatically opened (open is not required), and the system automatically reads the data every cycle.
Cursor data of the current row (fetch is not required). When you exit the for loop, the cursor is automatically closed (close is not required)

When you use a cursor for loop, you cannot use open statements, fetch statements, or close statements. Otherwise, an error occurs.

 

Set serveroutput on;
Declare


Cursor mycur (vartype number) is
Select emp_no, emp_zc from cus_emp_basic
Where com_no = vartype;

Begin

For person in mycur (000627) loop

Dbms_output.put_line ('employee No.: '| person. emp_no |', address: '| person. emp_zc );
End loop;

End;