Oracle PL/SQL - Looping through a Cursor by Using the LOOP Command

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursor FOR LOOP Statement

Description

Looping through a Cursor by Using the LOOP Command

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w  ww  .j  a  v  a  2  s.co  m

Elapsed: 00:00:00.02
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.

SQL>
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
SQL>
SQL>
SQL> declare
  2       cursor c_emp (ci_deptNo NUMBER) is
  3          select *
  4            from emp
  5            where deptNo = ci_deptNo;
  6       r_emp c_emp%ROWTYPE;
  7  begin
  8       open c_emp(10);
  9       loop
 10            fetch c_emp into r_emp;
 11            exit when c_emp%NOTFOUND;
 12            update emp
 13            set sal=sal*1.5
 14            where empNo=r_emp.empNo;
 15            DBMS_OUTPUT.put_line('Emp '||r_emp.eName||
 16              ' - salary change:'||r_emp.sal||
 17              '->'||r_emp.sal*1.5);
 18       end loop;
 19       close c_emp;
 20  end;
 21  /
Emp KING - salary change:5000->7500
Emp CLARK - salary change:2450->3675

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

Related Topic