Mega Code Archive

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> --