Detailed use of PL/SQL cursors

One: through cursors, PL/SQL points to the active set after the statement is parsed

Two: For different SQL statements, cursors are used differently:
1: Non-query statement-implicit
2: The result is a single-line query statement-implicit or explicit
3: The result is a multiline query statement--an explicit

Three: Cursor properties
1:%found
2:%notfound
3:%isopen
4:%rowcount

Three: The use of an explicit cursor
Four Steps
(1) defines a cursor name and its corresponding SELECT statement. Syntax: CURSOR cursor_name is select_statement
(2) Open the cursor. Syntax: OPEN cursor_name
(3) extracting the cursor. Syntax: FETCH cursor_name into {variable_list | record_variable}.
such as: FETCH c_cursor into V_ename, v_sal
(4) Close the cursor. Syntax: CLOSE cursor_name
Cases:
DECLARE
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Cursor C_cursor is a SELECT ename, sal from EMP WHERE rownum<11;--The last part of the declaration defines the cursor
BEGIN
Open c_cursor;--The start of the execution section of the cursor
Fetch c_cursor into V_ename, v_sal;--once opened to extract a cursor
While C_cursor%found LOOP
Dbms_output. Put_Line (v_ename| | ' ---' | | To_char (v_sal));
Fetch c_cursor into V_ename, v_sal;--loop last fetch cursor
END LOOP;
Close c_cursor;--cursors are closed after the cursor extraction ends
END;

Four: Parameterized Cursors
Defines the name of the cursor after the addition (variable name type). Variables are used to compare with SQL conditional statements
Cases:
DECLARE
V_ename Emp.ename%type;
V_sal Emp.sal%type;
CURSOR c_cursor (p_sal emp.sal%type)
Is SELECT ename, sal from emp WHERE sal >= p_sal;
BEGIN
OPEN c_cursor (1000);
FETCH c_cursor into V_ename, v_sal;
While C_cursor%found LOOP
Dbms_output. Put_Line (v_ename| | ' ---' | | To_char (v_sal));
FETCH c_cursor into V_ename, v_sal;
END LOOP;
CLOSE C_cursor;
END;

V: Implicit Cursors
The explicit cursor is mainly used for the processing of query statements, especially the query results are multiple records;
For non-query statements, cursors are automatically set by the system,
Called an implicit cursor, the name of an implicit cursor is SQL and is defined by the system.
The operation of an implicit cursor, such as definition, opening, value, and shutdown, is done automatically by the system and requires no user processing.
The user can only complete the corresponding operation through the related properties of the implicit cursor.
1: Implicit cursor properties
(1)%found
(2)%notfound
(3)%isopen
(4)%rowcount
Cases:
DECLARE
V_deptno Emp.deptno%type:=&p_deptno;
BEGIN
DELETE from EMP WHERE Deptno=v_deptno;
IF Sql%notfound Then
DELETE from dept WHERE deptno= 20;
END IF;
END;

Six: Cursor Retrieval loops
Cases:
DECLARE
V_empno Emp.empno%type;
V_sal Emp.sal%type;
CURSOR C_cursor is a SELECT empno, Sal from EMP;
BEGIN
OPEN C_cursor;
LOOP
FETCH c_cursor into V_empno, v_sal;
EXIT when C_cursor%notfound;
IF v_sal<=1200 Then
UPDATE emp SET sal=sal+50 WHERE empno=v_empno;
Dbms_output. Put_Line (' encoded as ' | | v_empno| | ' Salary has been updated! ');
END IF;
Dbms_output. Put_Line (' Number of records: ' | | c_cursor%rowcount);
END LOOP;
CLOSE C_cursor;
END;

Seven: A For loop for cursors
A cursor for loop statement that automatically executes the function of the cursor's open, FETCH, close statement, and Loop statement;
When entering a loop, the cursor is automatically opened and the first row of cursor data is fetched.
The next row of data is automatically fetched when the current extracted data is processed and the next loop is entered.
Ends the loop after extracting all the data in the result set and automatically closes the cursor.

1: Syntax:
For index variable name in cursor name LOOP
--Cursor Data processing code
END LOOP;

Cases:
DECLARE
CURSOR C_sal is a SELECT empno, ename, Sal from EMP;
BEGIN
--Implicitly open cursors
For v_sal in C_sal LOOP
--implicitly executes a fetch statement. Extracting cursor data from indexed variables
Dbms_output. Put_Line (To_char (v_sal.empno) | | ---' | | v_sal.ename| | ' ---' | | To_char (v_sal.sal));
--Implicit monitoring c_sal%notfound
END LOOP;
--Implicitly closing cursors
END;

Cases:
DECLARE
Cursor C_cursor (dept_no number DEFAULT) is--parameterized cursor
SELECT dname, loc from dept WHERE Deptno <= Dept_no;
BEGIN
Dbms_output. Put_Line (' dept_no parameter value is 30: ');
For C1_rec in C_cursor (loop--for) loop from index variable to cursor activity set
Dbms_output. Put_Line (c1_rec.dname| | ' ---' | | C1_REC.LOC);--loop body extracts cursor data based on index
END LOOP;
Dbms_output. Put_Line (CHR (10) | | Use the default Dept_no parameter value 10: ');
For C1_rec in C_cursor LOOP
Dbms_output. Put_Line (c1_rec.dname| | ' ---' | | C1_REC.LOC);
END LOOP;
END;

Eight: Cursor variable

The cursor variable is dynamic, and the cursor is static. The cursor can only be connected to the specified query.
That is, fixed to the memory processing area of a query, and cursor variables can be connected to different query statements.
You can point to the memory processing areas of different query statements as long as the return types of these query statements are compatible.

Detailed use of PL/SQL cursors