PL/SQL Practice explicit Cursors

The cursor--------needs to be defined by the user and can be used to process multiple rows of records at select time

1. Declare declares a cursor

2. Open cursor (implicit cursor auto Open)

3. Fetch cursor read record to variable (multiple rows can be read in a loop at select time)

4. Determine if the cursor is empty (to reach the last row of records)

5. Close Cusor Closing cursors

%isopen determines whether the cursor is open%found determines whether the cursor is non-null%notfound determines whether the cursor is empty%rowcount the number of data rows processed in the cursor

① case: Read multiple rows of data with an explicit cursor select

Sql> Declare  2   cursorCur_emp is  3    Select *  fromEmpwhereDeptno=&No; 4    5Emp_rec EMP%RowType; 6    7   begin  8     if  notCur_emp%IsOpen Then  9         Opencur_emp;Ten     End if;  One    ALoop -         FetchCur_emp intoEmp_rec; -     Exit  whenCur_emp%NotFound; the    -Dbms_output.put_line (emp_rec.ename||' , '||Emp_rec.sal||' , '||Emp_rec.deptno);  -     EndLoop; -   Closecur_emp; +    -  End;

② reading cursor data through a For loop:

Sql> Declare  2      cursorCur_emp is  3     Select *  fromEmpwhereDeptno=&No; 4    5    begin  6         forEmp_recinchCur_emp Loop7Dbms_output.put_line (emp_rec.ename||' , '||Emp_rec.sal||' , '||Emp_rec.deptno); 8        EndLoop; 9   Ten    End;

③ cursors with parameters: passing parameters to Cursors

Sql> Declare  2    3     cursorEmp_cur (V_deptno Number) is  4       Select *  fromEmpwhereDeptno=V_deptno; 5Emp_rec EMP%RowType; 6    7    begin  8      if  notEmp_cur%IsOpen Then  9          OpenEmp_cur ( -); Ten      End if;  OneLoop A          FetchEmp_cur intoEmp_rec; -          Exit  whenEmp_cur%NotFound; -Dbms_output.put_line (emp_rec.ename||' , '||Emp_rec.sal||' , '||Emp_rec.deptno);  the      EndLoop; -      End;

Sql>  Declare  2    3     cursorEmp_cur (V_deptno Number) is  4       Select *  fromEmpwhereDeptno=V_deptno; 5Emp_rec EMP%RowType; 6    7    begin  8      if  notEmp_cur%IsOpen Then  9          OpenEmp_cur ( -); Ten      End if;  OneLoop A          FetchEmp_cur intoEmp_rec; -          Exit  whenEmp_cur%NotFound; -Dbms_output.put_line (emp_rec.ename||' , '||Emp_rec.sal||' , '||Emp_rec.deptno);  the      EndLoop; -      End;

④ nesting cursors in the For loop (cursors do not require declare)

Sql> begin  2           forEmp_recinch(Select *  fromEmpwhereDeptno=&no) Loop3Dbms_output.put_line (emp_rec.ename||' , '||Emp_rec.sal||' , '||Emp_rec.deptno); 4          EndLoop; 5    6  End;

PL/SQL Practice explicit Cursors