Concept: 

    The cursor is used to process the query result (ResultSet) of the select statement. 

    Some people also think that cursors are query results 

Use steps and grammatical formats (non-scrolling cursors)

    1. Declare cursors

            Declaration area: 
                Cursor cursor name is select statement;
    2. Open the cursor

            open cursor name;

    3. Extract a row of data (cursor moves down)

            fetch cursor name into variable; assign a row in the cursor to a variable!

    4. Close the cursor
            close cursor name;

            1. Why should resources be closed? 
                    Handle number 1024

            2. Can closed resources be closed repeatedly? 

                Opening and closing cursors cannot be repeated, otherwise errors will be reported.

Example: Define a cursor , For storage s_emp In table id, last_name,salary ,Then extract the first five data of the cursor, And print , Finally close the cursor!


set serveroutput on;
declare
    /*declare cursor*/
    cursor s_emp_c is select id,last_name,salary from s_emp;
    /*Declare a type that matches the data type of a row in the result set*/
    type myemp is record(
        id s_emp.id%type,
        last_name s_emp.last_name%type,
        salary s_emp.salary%type
    );
    /*Declare a variable that accepts the data extracted by the cursor*/
    var_me myemp;
begin
    /*open*/
    open s_emp_c;
    /*Move the cursor down one line and fetch the data*/
    for i in 1..5 loop
        fetch s_emp_c into var_me;
        dbms_output.put_line('The employee's number is:'||var_me.id||',Name is:'||var_me.last_name||',His monthly salary is:'||var_me.salary);
    end loop;
    close s_emp_c;
end;

Use the cursor name% rowtype

Define a cursor , For storage s_emp In table id, last_name,salary ,Then extract the first five data of the cursor, And print , Finally close the cursor!


set serveroutput on;
declare
    /*declare cursor*/
    cursor s_emp_c is select id,last_name,salary from s_emp;

    /*Declare a variable to get an order record variable matching the data type in the current cursor by the cursor name% rowtype
    */
    var_me  s_emp_c%rowtype;
begin
    /*open*/
    open s_emp_c;
    /*Move the cursor down one line and fetch the data*/
    for i in 1..5 loop
        fetch s_emp_c into var_me;
        dbms_output.put_line('The employee's number is:'||var_me.id||',Name is:'||var_me.last_name||',His monthly salary is:'||var_me.salary);
    end loop;
    close s_emp_c;
end;
/

Getting data from multiple tables through cursors

query s_dept Medium name Field and s_region Medium name field ,Operate with a cursor

set serveroutput on;

declare
    cursor dr_c is select d.name dn,r.name rn from s_dept d,s_region r where d.region_id=r.id;

    var_dr dr_c%rowtype;
begin
    open dr_c;

    fetch dr_c into var_dr;

    dbms_output.put_line('Department name:'||var_dr.dn||',Area name:'||var_dr.rn);

    close dr_c;
end;
/

cursor attribute

Loop-through cursor operations using some attributes

1. Cursor name% found  

    If the cursor extracts new data, it returns true or false.

2. Cursor name% notfound

    If the cursor does not extract the new data, it returns true and the extracted data returns false.

-------------------------------------------

The above two attributes, if you want to use them, must satisfy two prerequisites: 

    1. The cursor must be open! Otherwise, illegal cursor operation will occur.

    2. The cursor must execute fetch (any row of the cursor in the result set) or return null

   Acquisition by cursor s_emp In the form(id,last_name,salary), Get all rows

declare
    cursor myemp is select id,last_name,salary from s_emp;
    var_me myemp%rowtype;
begin
    open myemp;
    loop
        fetch myemp into var_me;
        exit when myemp%notfound;
        dbms_output.put_line('Employee number:'||var_me.id||',Full name:'||var_me.last_name||',Monthly salary of employees:'||var_me.salary);
    end loop;
    close myemp;
end;

Intelligent cyclic traversal cursor

for loop, when iterating over cursors, 

    Variables are automatically defined. 
    Open the cursor automatically. 
    Automatically extracting data, 
    Close the cursor automatically.


Use the for loop to get id,last_name,salary in the s_emp table

declare
    cursor myemp is select id,last_name name,salary from s_emp;
begin
    for var_me in myemp loop
            /*
                In this loop, var_me is the data for each row in each iteration of the loop
            */
        Dbms_output.put_line('id='| | var_me.id |', name ='| | var_me.name |', monthly salary ='| | var_me.salary);
    end loop;
end;
/

Parametric cursor

When a cursor is defined, 
It can be designed to pass parameters when opening, which can be used in select statements.

Format:

Cursor cursor name (parameter list) is select statement
 1. The type of parameter can not be modified by length, but can be passed by% type.

2. Time of parameter transfer:
    Pass in when you open the cursor.
    For example:
    open cursor name (parameter);


Query an employee's information (id,last_name_salary) based on the ID entered by the user
set serveroutput on;
declare
    cursor myemp(var_id number) is select id,last_name name,salary from s_emp where id=var_id;

    var_me myemp%rowtype;
    var_input number;
begin
    Var_input: = & please enter the id of the employee you want to query;
    open myemp(var_input);
    fetch myemp into var_me;
    dbms_output.put_line(var_me.name);
    close myemp;
end;
/

How to pass parameters when using intelligent loops

According to user input id , Query for information about employees whose employee number is greater than the employee number entered by the user(id,last_name_salary)
set serveroutput on;
declare
    cursor myemp(var_id number) is select id,last_name name,salary from s_emp where id>var_id;

    var_input number;
begin
    var_input :=&Please enter the employee's id;

    for  var_me in myemp(var_input) loop

        dbms_output.put_line('id='||var_me.id); 

    end loop;

end;
/

Reference cursor ref

Concept: 

    The select statement corresponding to the cursor does not need to be specified in the declaration area. You can specify the SQL statement when opening the cursor.

Grammatical Format:

    1. The declaration area defines the type of a reference cursor

        The type reference cursor type name is ref cursor;

    2. Define a variable

        Variable name refers to cursor type;

    3. Open the cursor and associate the SELECT statement

        open cursor variable name for'SELECT statement';

Example:

According to user input id , Query for information about employees whose employee number is greater than the employee number entered by the user

//Before traversing the cursor, we retrieved the type by the name% rowtype of the cursor.

//This cannot be done in reference cursors, because in the declaration area, the cursor does not know its own row type.
set serveroutput on;
declare
    type mc is ref cursor;
    var_mc mc;
    -- This string , It's for doing it. SQL Query statement
    var_select varchar2(3000);
    var_input number;

    type myemp is record(
        id  s_emp.id%type,
        salary s_emp.salary%type
    );
    var_me myemp;
begin
    var_input:=&Please enter employees id;
    var_select:='select id,salary from s_emp where id>'||var_input;
    open var_mc for var_select;

    loop
        fetch var_mc into var_me;
        exit when var_mc%notfound;
        dbms_output.put_line('Staff id:'||var_me.id||',Salaries of employees:'||var_me.salary);
    end loop;
end;
/