an explicit cursor that selects data

  
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL>
SQL> declare
  2    cursor emps
  3    is select *
  4         from employees
  5        where rownum < 6
  6        order by 1;
  7
  8    emp employees%rowtype;
  9    row number := 1;
 10  begin
 11    open emps;
 12    fetch emps into emp;
 13
 14    loop
 15      if emps%FOUND then
 16        dbms_output.put_line('Looping over record ' ||row|| ' of ' ||
 17                             emps%ROWCOUNT);
 18        fetch emps into emp;
 19        row := row + 1;
 20      elsif emps%NOTFOUND then
 21        exit; -- EXIT statement exits the LOOP, not the IF stmt
 22      end if;
 23    end loop;
 24
 25    if emps%ISOPEN then
 26      close emps;
 27    end if;
 28  end;
 29  /
Looping over record 1 of 1
Looping over record 2 of 2
Looping over record 3 of 3
Looping over record 4 of 4
Looping over record 5 of 5

PL/SQL procedure successfully completed.

SQL>
SQL> drop table employees;

Table dropped.

SQL>
SQL> --

Related examples in the same category

1.	Explicit Cursor Demo
2.	Implicit and Explicit Cursors
3.	An explicit cursor fetch loop.
4.	Use cursor to store the row count
5.	Column value indexed cursor
6.	Combine for loop and if statement to check the value in cursor
7.	Cursor performance
8.	Delete from table where current of cursor
9.	If statement and single piece value in cursor
10.	Use explicit cursor to fetch and store value to number variable
11.	Write an explicit cursor in a FOR loop and use the data

an explicit cursor that selects data : Explicit Cursor « Cursor « Oracle PL / SQL

Related examples in the same category