In PL/SQL programs, cursors are often used to handle transactions with multiple rows of records.

Among them, cursors are divided into explicit cursors and implicit cursors. Here, the use of explicit cursors is emphasized.

Explicit Cursor

Processing of Display Cursor

Four PL/SQL steps:

1) Define a cursor: Define a cursor name and the corresponding SELECT statement.

Format:

CURSOR cursor_name[(parameter[, parameter]...)] IS select_statement;

Cursor parameters can only be input parameters in the form of:

parameter_name [IN] datatype [{:= | DEFAULT} expression]

Length constraints cannot be used when specifying data types. For example, NUMBER(4), CHAR(10) and so on are all wrong.

2) Open the cursor: that is to execute the SELECT statement corresponding to the cursor, put its query results into the workspace, and point the pointer to the head of the workspace to identify the set of cursor results. If the cursor query statement has the FOR UPDATE option, the OPEN statement will also lock the data rows corresponding to the cursor result set in the database table.

Format:

OPEN cursor_name[([parameter =>] value[, [parameter =>] value]...)];

When passing parameters to a cursor, you can use the same value transfer methods as function parameters, namely position representation and name representation. PL/SQL programs cannot open a cursor repeatedly with OPEN statements.

3) Extraction of cursor data: that is to retrieve the data rows in the result set and put them into the specified output variables.

Format:

FETCH cursor_name INTO {variable_list | record_variable };

4) Processing the record until there is no record in the active set;

5) Close the cursor: When the data of the cursor result set is extracted and processed, the cursor should be closed in time to release the system resources occupied by the cursor, and make the workspace of the cursor invalid, so that the data can not be retrieved by FETCH statement. The closed cursor can be reopened using the OPEN statement.

Format:

CLOSE cursor_name;

Note: Defined cursors cannot have INTO clauses

Cursor properties:

% The value of FOUND Boolean property is TRUE when it is successfully returned from the last record reading.

% NOTFOUND Boolean property, contrary to% FOUND;

% ISOPEN Boolean property, returns TRUE when the cursor is open;

% ROWCOUNT numeric property that returns the number of records read from the cursor.

Call way of cursor attribute: cursor name% attribute

SET SERVEROUTPUT ON

-- Print out the salaries of all employees in 80 departments salary
DECLARE
   --Declare a record type
	 TYPE EMP_RECORD IS RECORD(
        V_SAL EMPLOYEES.SALARY%TYPE,
        V_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE
	 );
	 
   --Declare a variable of record type
	 V_EMP_RECORD EMP_RECORD;
	 
   --Define cursors
   CURSOR EMP_SAL_CURSOR IS SELECT E.SALARY, E.EMPLOYEE_ID FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = 80;

BEGIN
  --open
  OPEN EMP_SAL_CURSOR;
  
  --Extraction of cursors
  FETCH EMP_SAL_CURSOR INTO V_EMP_RECORD;
  
  WHILE EMP_SAL_CURSOR%FOUND LOOP
     DBMS_OUTPUT.put_line(V_EMP_RECORD.V_EMPID || ':' || V_EMP_RECORD.V_SAL);
     FETCH EMP_SAL_CURSOR INTO V_EMP_RECORD;
  END LOOP;
  
  --Close the cursor
  CLOSE EMP_SAL_CURSOR;
	
END;
/  

Cursor FOR loop

The cursor FOR loop statement automatically executes the functions of the cursor OPEN, FETCH, CLOSE statement and loop statement; when entering the loop, the cursor FOR loop statement automatically opens the cursor and extracts the first row of cursor data. When the program processes the current extracted data and enters the next loop, the cursor FOR loop language Sentences automatically extract the next row of data for processing by the program. When all data rows in the result set are extracted, the loop ends and the cursor is automatically closed.

Here, all kinds of operation are encapsulated. It is very convenient. In general, the cursor is recycled with FOR.

SET SERVEROUTPUT ON

-- Print out the salaries of all employees in 80 departments salary
DECLARE
   --Define cursors
   CURSOR EMP_SAL_CURSOR(DEP_ID NUMBER DEFAULT 80) IS SELECT E.SALARY, E.EMPLOYEE_ID FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = DEP_ID;

BEGIN
   FOR C IN EMP_SAL_CURSOR(DEP_ID => 100) LOOP
		 DBMS_OUTPUT.put_line(C.EMPLOYEE_ID || ':' || C.SALARY);
   END LOOP;
  
END;
/  

All of the above are read with cursors. If you need to update the data, you need to add FOR UPDATE after the cursor declaration.

Here's an example: Update all employees whose wages are less than 5,000 to 10,000.

SET SERVEROUTPUT ON

DECLARE 
		CURSOR EMP_SAL_CURSOR IS SELECT E.EMPLOYEE_ID, E.SALARY FROM EMPLOYEES E FOR UPDATE;

		V_EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
		V_SAL EMPLOYEES.SALARY%TYPE;
		V_NEW_SALARY EMPLOYEES.SALARY%TYPE;
BEGIN
	  FOR C IN EMP_SAL_CURSOR LOOP
			  IF C.SALARY < 5000 THEN
					 DBMS_OUTPUT.put_line('Before adjustment:'||C.SALARY);
				   UPDATE EMPLOYEES E SET E.SALARY = 10000
				   WHERE CURRENT OF EMP_SAL_CURSOR;
					 
					 SELECT E2.SALARY INTO V_NEW_SALARY FROM EMPLOYEES E2 WHERE E2.EMPLOYEE_ID = C.EMPLOYEE_ID;
					 
					 DBMS_OUTPUT.put_line('After adjustment:'|| V_NEW_SALARY);
		    END IF;
		END LOOP;
END;  
/

Implicit Cursor

Explicit cursors are mainly used to process query statements, especially when the query results are multiple records. For non-query statements, such as modification and deletion operations, the ORACLE system automatically sets cursors for these operations and creates their workspaces. These cursors implicitly created by the system are called implicit cursors. The name of the implicit cursor is SQL, which is defined by the ORACLE system.

The attributes of implicit cursor are the same as those of explicit cursor, and the invocation method is: SQL% attribute name.