Oracle Cursor Parsing

First, the cursor:
1. Concept:
The essence of a cursor is a result set resultset, which is mainly used to temporarily store data blocks extracted from the database.
Second, the classification of cursors:
1, explicit cursor: defined by the user, the required operations: define the cursor, open the cursor, extract data, close the cursor, mainly used for the processing of the query statement.
Attribute: %FOUND %NOTFOUND %ISOPEN %ROWCOUNT
Example: Print employee information for the emp table

DECLARE
  CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;
  v_empno emp.empno%TYPE;
  v_name emp.ename%TYPE;
  v_job emp.job%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_empno,v_name,v_job;
    DBMS_OUTPUT.PUT_LINE(‘employee ID :‘||v_empno||‘name:‘||v_name||‘position：‘||v_job);
    EXIT WHEN emp_cursor%NOTFOUND;
  END LOOP;
  CLOSE emp_cursor;
END; 

Here strictly follow the rules for displaying the cursor: DECLARE emp_cursor defines the cursor OPEN emp_cursor open the cursor FETCH emp_cursor INTO... extracts the data CLOSE emp_cursor closes the cursor, because the extracted data belongs to multiple lines, so it can be printed by loop loop.
Example 2: Verify that the cursor is open, if open, display the number of rows fetched

DECLARE
   CURSOR emp_cursor IS SELECT empno, ename, job FROM emp;
   V_empno emp.empno%TYPE;
   V_name emp.ename%TYPE;
   V_job emp.job%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
        FETCH emp_cursor INTO v_empno, v_name, v_job;
        EXIT WHEN emp_cursor%NOTFOUND;
   END LOOP;
   IF emp_cursor%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE (‘The cursor is open ‘);
     DBMS_OUTPUT.PUT_LINE (‘Read ‘||emp_cursor%ROWCOUNT||‘row’);
   ELSE
     DBMS_OUTPUT.PUT_LINE (‘The cursor is not open ‘);
   END IF;
   CLOSE emp_cursor;
END;

The %ISOPEN attribute is used to determine whether the cursor is open, and %ROWCOUNT determines the number of rows.
2, implicit cursor: defined by the system and create a work area for it, and the implicit definition open extraction close, the cursor name of the implicit cursor is 'SQL', the property and display cursor are the same, mainly used for single-line select statement or The dml operation is processed.
Example: The user enters the employee number to modify the employee's salary. If successful, the printout success flag is printed.
In order to try not to change the original table, create a new table emp_new and the original table data is the same:

CREATE TABLE emp_new
AS
SELECT * FROM emp;
BEGIN
   UPDATE emp_new SET sal = sal+500 WHERE empno=&empno;
   IF SQL%FOUND THEN
     DBMS_OUTPUT.PUT_LINE (‘successfully modified ‘);
     COMMIT;
   ELSE
     DBMS_OUTPUT.PUT_LINE (‘Modification failed ‘);
     ROLLBACK;
   END IF;
END;

Note here that after the addition, deletion, change, you must commit the commit operation. If the operation fails, rollback rolls back the previous operation.
3, parameter cursor:
A cursor that adds a parameter when defining a cursor can quickly find the data you need with the cursor for loop. Here first talk about a downstream label for loop
A, cursor FOR loop:
Implicitly executed to open the extraction to close the data, the code is much more streamlined. Expression:
FOR table_record IN table_cursor LOOP
STATEMENT;
END LOOP;
  Example: Print out employee information using the cursor For loop:

DECLARE
CURSOR emp_cursor IS SELECT empno, ename, job FROM emp;
BEGIN
   FOR emp_record IN emp_cursor LOOP
     DBMS_OUTPUT.PUT_LINE (‘employee number: ‘||emp_record.empno||‘employee name ‘||emp_record.ename||‘employee position ‘||emp_record.job);
   END LOOP;
END;

Here the cursor FOR loop saves the naming and assignment of the variables of the fetched data, and if all prints, there is no need to write loop conditions, the code is much more streamlined.
If you want to make the code more streamlined, you can remove the declaration of the cursor to introduce a subquery, as follows.

BEGIN
   FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP
     DBMS_OUTPUT.PUT_LINE (‘employee number: ‘||emp_record.empno||‘employee name ‘||emp_record.ename||‘employee position ‘||emp_record.job);
   END LOOP;
END;

The code is more streamlined and the results are the same. And implicit cursors are a bit like, but implicit cursors are mainly used for single-row select and dml statements, pay attention to the difference between the two.
Continue with the example of the parameter cursor:

BEGIN
   FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP
     DBMS_OUTPUT.PUT_LINE (‘employee number: ‘||emp_record.empno||‘employee name ‘||emp_record.ename||‘employee position ‘||emp_record.job);
   END LOOP;
END;

Since there are parameters here, there must be a declaration of the cursor, in combination with the cursor FOR loop to quickly find the required data.
Third, the use of cursors to modify the data of the precautions
1. When using the cursor to modify the data, in order to prevent others from modifying the data when they manipulate the data, Oracle provides a for update clause to lock.
Also, when you use update or delete, you must use the where current of+name_cursor statement and remember to commit at the end. in case
For cascading operations, you can use for update of to lock the related tables.

CREATE TABLE emp_new
AS
SELECT * FROM emp;
DECLARE
CURSOR empnew_cursor IS SELECT ename, job FROM emp_new FOR UPDATE;
BEGIN
   FOR empnew_record IN empnew_cursor LOOP
     DBMS_OUTPUT.PUT_LINE(‘name ‘||empnew_record.ename||‘position ‘||empnew_record.job);
     IF empnew_record.job=‘PRESIDENT’ THEN
       UPDATE emp_new SET sal=sal+1000 WHERE CURRENT OF empnew_cursor;
     ELSIF empnew_record.job=‘MANAGER‘ THEN
       UPDATE emp_new SET sal=sal+500 WHERE CURRENT OF empnew_cursor;
     END IF;
   END LOOP;
   COMMIT;
END;
SELECT * FROM EMP WHERE job in(‘PRESIDENT‘, ‘MANAGER’);
SELECT * FROM EMP_NEW WHERE job in(‘PRESIDENT‘, ‘MANAGER’);

It can be seen that there has been a corresponding change in wages here.
 
 
At this point, the Oracle cursor is parsed. In summary, the cursor is just a part of the data we extracted from the database. We do a series of operations on this result set.