Mega Code Archive

Implicit and Explicit Cursors

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> CREATE TABLE JOBS ( 2 JOB_ID VARCHAR2 (10) NOT NULL, 3 JOB_TITLE VARCHAR2 (35) CONSTRAINT JOB_TITLE_NN NOT NULL, 4 MIN_SALARY NUMBER (6), 5 MAX_SALARY NUMBER (6), 6 CONSTRAINT JOB_ID_PK PRIMARY KEY ( JOB_ID ) ) ; Table created. SQL> SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'DBA', 'President', 20000, 40000); 1 row created. SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'PROG', 'Administration Vice President', 15000, 30000); 1 row created. SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'MGR', 'Administration Assistant', 3000, 6000); 1 row created. SQL> SQL> SQL> SQL> declare 2 cursor cursorValue 3 is select e.last_name name, e.salary 4 from employees e, jobs j 5 order by salary; 6 7 name varchar2(200); 8 salary number(9,2); 9 begin 10 for c1 in (select j.job_title, j.min_salary, j.max_salary, 11 avg(e.salary) avg_salary 12 from employees e, jobs j 13 where e.job_id = j.job_id 14 group by j.job_title, j.min_salary, j.max_salary 15 order by j.job_title) loop 16 dbms_output.put_line(c1.job_title||'s, average $'||c1.avg_salary); 17 end loop; 18 19 open cursorValue; 20 fetch cursorValue into name, salary; 21 dbms_output.put_line(chr(13) || chr(13)); 22 dbms_output.put_line('cursorValue'); 23 while cursorValue%FOUND loop 24 dbms_output.put_line(name || ' makes $' || salary); 25 fetch cursorValue into name, salary; 26 end loop; 27 close cursorValue; 28 end; 29 / Administration Assistants, average $27500 Administration Vice Presidents, average $21000 Presidents, average $20000 cursorValue Wells makes $20000 Java makes $20000 Beck makes $20000 Viper makes $20000 Wells makes $20000 Oracle makes $20000 Java makes $20000 Beck makes $20000 Viper makes $20000 Oracle makes $20000 Viper makes $20000 Beck makes $20000 Java makes $20000 Oracle makes $20000 Wells makes $20000 Bliss makes $24000 Bliss makes $24000 Bliss makes $24000 Kyte makes $25000 Kyte makes $25000 Kyte makes $25000 Lawson makes $30000 Lawson makes $30000 Lawson makes $30000 PL/SQL procedure successfully completed. SQL> SQL> drop table employees; Table dropped. SQL> drop table jobs; Table dropped. SQL> SQL> --