Source-pl/sql from Getting started to mastering-tenth-Using Cursors-part 2

Although PL/SQL is used in the next project, it is unclear what specific points of knowledge need to be used. It is important to estimate the cursor, and if it can be changed on the basis of existing code to meet the requirements of the project, now the work is more valuable.

-Code 10.14 Basic loop loop structure declare Dept_row dept%rowtype;   --Define cursor result record variable cursor dept_cursor is SELECT * from dept;                           --Define the cursor variable begin OPEN dept_cursor;         --Open cursor loop--Simple loop FETCH dept_cursor into Dept_row;          --Extracting cursor data EXIT when dept_cursor%notfound; --Exit the loop control statement dbms_output. Put_Line (' Department Name: ' | |   Dept_row.dname);   END LOOP;                          CLOSE Dept_cursor;   --Close the cursor end;                      --code 10.15 uses a while loop to retrieve cursor data declare Dept_row dept%rowtype;   --Define cursor result record variable cursor dept_cursor is SELECT * from dept;                           --Define the cursor variable begin OPEN dept_cursor;            --Open cursor FETCH dept_cursor into Dept_row; --Extracting cursor data while Dept_cursor%found LOOP dbms_output. Put_Line (' Department Name: ' | |      Dept_row.dname);         FETCH dept_cursor into Dept_row;   --Extracting the cursor data END LOOP;                          CLOSE Dept_cursor;   --Close the cursor end; --code 10.16 Retrieving data using a cursor for loop DECLARE CURSOR dept_cursor is SELECT * from dept; --Define the cursor variable begin for Dept_row in Dept_cursor loop--Retrieves the data dbms_output in the cursor for loop. Put_Line (' Department Name: ' | |   Dept_row.dname); END LOOP;   END; --code 10.17 CURSOR FOR loop subquery statement (brevity is good!) BEGIN for Dept_row in (SELECT * from dept) Loop-Retrieves the data dbms_output in the cursor for loop. Put_Line (' Department Name: ' | |   Dept_row.dname); END LOOP;   END;  --code 10.18 uses cursors to update data DECLARE CURSOR emp_cursor (P_deptno in number) is SELECT * from EMP WHERE                              Deptno = P_deptno for UPDATE;                               --Use the FOR UPDATE clause to add a mutex begin for Emp_row in Emp_cursor (20)--use a cursor for loop to retrieve the cursor loop UPDATE EMP            SET Comm = Comm * 1.12 WHERE current of emp_cursor;   --Update the cursor data with the WHERE current of END LOOP;                                     COMMIT;           --Commit changes end;--code 10.18 use a cursor to delete data DECLARE CURSOR emp_cursor (p_empno in number) is a SELECT * from EMP WHEREEmpno = P_empno for UPDATE;       --Use the FOR UPDATE clause to add a mutex begin for Emp_row in Emp_cursor (7369)--use a cursor for loop to retrieve the cursor loop DELETE from EMP            WHERE Current of Emp_cursor; --use where current to delete the cursor data END LOOP;       end;--code 10.20 Cursor Variable uses example declare TYPE emp_type is REF cursor RETURN emp%rowtype;                                   --Define cursor variable type emp_cur emp_type;                                --Declares the cursor variable emp_row emp%rowtype;                   --Define cursor result value variable begin OPEN emp_cur for SELECT * from EMP;                        --Open the cursor LOOP FETCH emp_cur into Emp_row;                        --loop extraction of cursor data EXIT when emp_cur%notfound;   --Cyclic exit detection dbms_output.put_line (' Employee Name: ' | | emp_row.ename); END LOOP;      end;--Code 10.21 Defines the cursor variable declare TYPE emp_type is REF cursor RETURN emp%rowtype;                            --Define cursor type gen_type is REF cursor;                                   Emp_cur Emp_type; --Declares the cursor variable gen_cur gen_type; BegiN OPEN emp_cur for SELECT * from EMP WHERE deptno=20;      END; DECLARE Gen_type Sys_refcursor;       Beginend;                       --code 10.22 Big COFFEE CURSOR Variable example declare TYPE emp_curtype is REF cursor;                                  --Define cursor type emp_cur emp_curtype;                   --Declare a variable of cursor type begin OPEN Emp_cur for SELECT * from EMP;               --Open cursor, query emp All columns open emp_cur for SELECT empno from EMP;             --Open cursor, query EMP table empno column open emp_cur for SELECT deptno from dept;       --Open Cursor, Query dept Table deptno Column end;--Use FETCH statement to EXTRACT cursor variable data declare TYPE emp_type is REF cursor RETURN emp%rowtype;                                     --Define cursor type emp_cur emp_type; --Declares the cursor variable emp_row emp%rowtype; BEGIN if not Emp_cur%isopen then--if the cursor variable does not have open emp_cur for SELECT * from EMP WHERE Dept   no=20;   --Open the cursor variable END IF;                         LOOP FETCH emp_cur into Emp_row;                         --Extracting the cursor variable EXIT when emp_cur%notfound; --Exits the loop dbms_output if the fetch is complete. Put_Line (' Employee Name: ' | | Emp_row.ename | | ' employee position: ' | |  Emp_row.job); --Output employee information END LOOP;   END;       --Use the close statement to close the cursor variable (without closing nor error) DECLARE TYPE emp_type is REF cursor RETURN emp%rowtype;                                     --Define cursor type emp_cur emp_type; --Declares the cursor variable emp_row emp%rowtype;   BEGIN OPEN emp_cur for SELECT * from EMP WHERE deptno=20;                           --Open cursor FETCH emp_cur into Emp_row; --Extract cursor while emp_cur%found loop--loop fetch cursor dbms_output. Put_Line (' Employee Name: ' | |     Emp_row.ename);          FETCH emp_cur into Emp_row;   END LOOP;                                        CLOSE emp_cur;   --Close the cursor end;                       --The following code cannot be compiled (use the Open statement before using the FETCH statement) DECLARE TYPE Emp_curtype is REF CURSOR;                                  --Define cursor type emp_cur emp_curtype; --Declares the cursor type variable emp_row emp%rowtype; BEGIN FETCH emp_cur into Emp_row;                       end;--Code 10.25 handles Invalid_cursor exceptions (not quite understood) DECLARE TYPE Emp_curtype is REF CURSOR; --Defines the cursor type EMP_CUR1 emp_curtype;   --Declares the cursor type variable EMP_CUR2 emp_curtype;                                  Emp_row Emp%rowtype;  --Defines the record type that holds cursor data begin OPEN EMP_CUR1 for SELECT * from EMP WHERE deptno=20;                          --Open a 1th cursor FETCH emp_cur1 into Emp_row; --Extracts and displays cursor information dbms_output. Put_Line (' Employee Name: ' | | emp_row.ename| | ' department number: ' | |   EMP_ROW.DEPTNO);                          FETCH emp_cur2 into Emp_row;                                --Extracting the 2nd cursor variable will throw an exception exception when invalid_cursor then--Exception handling Emp_cur2:=emp_cur1;                       --Assigns the query area Emp_cur1 points to emp_cur2 FETCH emp_cur2 into Emp_row; --now Emp_cur1 and Emp_cur2 point to the same query dbms_output. Put_Line (' Employee Name: ' | | emp_row.ename| | ' department number: ' | |      EMP_ROW.DEPTNO); OPEN EMP_CUR2 for SELECT * from EMP WHERE deptno=30;                         --Reopen the EMP_CUR2 cursor variable and FETCH emp_cur1 into Emp_row using the same query area; --because EMP_CUR1 shares the same query area as EMP_CUR2, the result is the same dbms_output. Put_Line (' Employee Name: ' | | Emp_row.ename| | ' department number: ' | |      EMP_ROW.DEPTNO);                       end;--handles Rowtype_mismatch exception declare TYPE emp_curtype is REF CURSOR;                                  --Define cursor type emp_cur emp_curtype;                                  --Declares the cursor type variable emp_row emp%rowtype;                               --Declares the cursor data result type Dept_row dept%rowtype;   BEGIN OPEN emp_cur for SELECT * from EMP WHERE deptno=20;                          --Open cursor variable FETCH emp_cur into Dept_row; --extracting into a mismatched type exception when Rowtype_mismatch then--handling Rowtype_mismatch exception FETCH Emp_cur I                         NTO Emp_row; --Extract the cursor variable data again, the output result is dbms_output. Put_Line (' Employee Name: ' | | emp_row.ename| | ' department number: ' | |       EMP_ROW.DEPTNO);                               end;--using sys_refcursor type declare emp_cur sys_refcursor;   --Define the weakly typed cursor variable emp_row emp%rowtype; Dept_row Dept%rowtype;  BEGIN OPEN emp_cur for SELECT * from EMP WHERE deptno=20; --Open cursor data FETCH emp_cur into Dept_row; EXCEPTION when Rowtype_misMATCH then-handles Rowtype_mismatch exception FETCH emp_cur into Emp_row; --Re-extract and output the abnormal result dbms_output. Put_Line (' Employee Name: ' | | emp_row.ename| | ' department number: ' | |       EMP_ROW.DEPTNO); end;--code 10.28 using cursor variables in packages--Creating package specifications Create OR REPLACE packages emp_data_action as TYPE emp_type is REF CURSOR RETURN Emp%rowtyp         E --Define a strongly typed cursor type--defines the subroutine that makes the cursor variable PROCEDURE getempbydeptno (emp_cur in out emp_type,p_deptno number); END emp_data_action;--Implement the package body create OR REPLACE packages body emp_data_action AS--Create the procedure defined in the package specification PROCEDURE Getempbydeptno (Emp_cur in Out Emp_type,p_deptno number)   is Emp_row Emp%rowtype;  BEGIN OPEN emp_cur for SELECT * from EMP WHERE Deptno=p_deptno;                              --Open cursor variable LOOP FETCH emp_cur into Emp_row;       --Extract data EXIT when emp_cur%notfound; --Output cursor data dbms_output. Put_Line (' Employee Name: ' | | emp_row.ename| | ' department number: ' | |     EMP_ROW.DEPTNO);     END LOOP;   CLOSE emp_cur; END; END Emp_data_action;declare Emp_cursors emp_data_action.emp_type;   --Defines the type of cursor defined in the package begin Emp_data_action.getempbydeptno (Emp_cursors, 20);         --Call the procedure defined in the package to create a package specification for the end;--created OR REPLACE packages Emp_data_action_err as TYPE Emp_type is REF the CURSOR RETURN emp%rowtype;   --Define the strongly typed cursor type emp_cur emp_type;   --Defines the subroutine that makes the cursor variable PROCEDURE getempbydeptno (emp_cur in out emp_type,p_deptno number);                       END emp_data_action_err;declare TYPE Emp_curtype is REF CURSOR;                                  --Define cursor type emp_cur emp_curtype; --Declares the variable of the cursor type begin for Emp_row in Emp_cur LOOP dbms_output.   Put_Line (Emp_row.ename); END LOOP; END;

Source-pl/sql from Getting started to mastering-tenth-Using Cursors-part 2