SQL> --cursor attributes: isopen, notfound,found,rowcount SQL> set serveroutput on SQL> --write an anonymous block to print last anem of all employees in department 100 SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100; 6 v_last_name employees.last_name%type; 7 --the above statemnent means the data type of the varưable v_last_name will be 8 --the same as the data type of last_name column of the employees table 9 --this is called anchorfed declaration 10 . SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100; 6 v_last_name employees.last_name%type; 7 --the above statemnent means the data type of the varưable v_last_name will be 8 --the same as the data type of last_name column of the employees table 9 --this is called anchored declaration 10 v_salary number; 11 --v_salary employees.salary%type; 12 begin 13 open elist; 14 loop 15 fetch elist into v_last_name,v_salary; 16 exit when elist%notfound; 17 dbms_output.put_line(v_salary||' '||v_last_name); 18 end loop; 19 close elist; 20* end; 21 / 12008 Greenberg 9000 Faviet 8200 Chen 7700 Sciarra 7800 Urman 6900 Popp PL/SQL procedure successfully completed. SQL> SQL> -- print name and salary of the three highest earning employees in department 100 SQL> -- correction print top 4 employees in terms of salary SQL> select last_name,salary 2 from employees 3 where department_id=100 4 order by salary desc; LAST_NAME SALARY ------------------------- ---------- Greenberg 12008 Faviet 9000 Chen 8200 Urman 7800 Sciarra 7700 Popp 6900 6 rows selected. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 --the above statemnent means the data type of the varưable v_last_name will be 9 --the same as the data type of last_name column of the employees table 10 --this is called anchored declaration 11 v_salary number; 12 --v_salary employees.salary%type; 13 c number :=0; 14 begin 15 open elist; 16 loop 17 fetch elist into v_last_name,v_salary; 18 exit when elist%notfound or c >=4; 19 c :=c+1; 20 dbms_output.put_line(v_salary||' '||v_last_name); 21 end loop; 22 close elist; 23* end; 24 / 12008 Greenberg 9000 Faviet 8200 Chen 7800 Urman PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 --the above statemnent means the data type of the varưable v_last_name will be 9 --the same as the data type of last_name column of the employees table 10 --this is called anchored declaration 11 v_salary number; 12 --v_salary employees.salary%type; 13 begin 14 open elist; 15 loop 16 fetch elist into v_last_name,v_salary; 17 exit when elist%notfound or elist%rowcount >=4; 18 dbms_output.put_line(v_salary||' '||v_last_name); 19 end loop; 20 close elist; 21* end; 22 / 12008 Greenberg 9000 Faviet 8200 Chen PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 --the above statemnent means the data type of the varưable v_last_name will be 9 --the same as the data type of last_name column of the employees table 10 --this is called anchored declaration 11 v_salary number; 12 --v_salary employees.salary%type; 13 begin 14 open elist; 15 loop 16 fetch elist into v_last_name,v_salary; 17 exit when elist%notfound or elist%rowcount >4; 18 dbms_output.put_line(v_salary||' '||v_last_name); 19 end loop; 20 close elist; 21* end; SQL> / 12008 Greenberg 9000 Faviet 8200 Chen 7800 Urman PL/SQL procedure successfully completed. SQL> --print the order number of the employees as well SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 --the above statemnent means the data type of the varưable v_last_name will be 9 --the same as the data type of last_name column of the employees table 10 --this is called anchored declaration 11 v_salary number; 12 --v_salary employees.salary%type; 13 begin 14 open elist; 15 loop 16 fetch elist into v_last_name,v_salary; 17 exit when elist%notfound or elist%rowcount >4; 18 dbms_output.put_line(elist%rowcount||'-'||v_salary||' '||v_last_name); 19 end loop; 20 close elist; 21* end; SQL> / 1-12008 Greenberg 2-9000 Faviet 3-8200 Chen 4-7800 Urman PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 v_salary number; 9 begin 10 dbms_output.put_line('before open:'||elist%rowcount); 11 open elist; 12 loop 13 fetch elist into v_last_name,v_salary; 14 exit when elist%notfound or elist%rowcount >4; 15 dbms_output.put_line(elist%rowcount||'-'||v_salary||' '||v_last_name); 16 end loop; 17 close elist; 18* end; 19 / declare * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at line 10 SQL> --we cannot use the cursor attribute rowcount bedfore openning the cursor SQL> --we get invalid cursor exception SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 v_salary number; 9 begin 10 open elist; 11 dbms_output.put_line('after open before fetch:'||elist%rowcount); 12 loop 13 fetch elist into v_last_name,v_salary; 14 exit when elist%notfound or elist%rowcount >4; 15 dbms_output.put_line(elist%rowcount||'-'||v_salary||' '||v_last_name); 16 end loop; 17 close elist; 18* end; 19 / after open before fetch:0 1-12008 Greenberg 2-9000 Faviet 3-8200 Chen 4-7800 Urman PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 v_salary number; 9 begin 10 open elist; 11 dbms_output.put_line('after open before fetch:'||elist%rowcount); 12 loop 13 fetch elist into v_last_name,v_salary; 14 exit when elist%notfound or elist%rowcount >4; 15 dbms_output.put_line(elist%rowcount||'-'||v_salary||' '||v_last_name); 16 end loop; 17 dbms_output.put_line('after loop:'||elist%rowcount); 18 close elist; 19* end; SQL> / after open before fetch:0 1-12008 Greenberg 2-9000 Faviet 3-8200 Chen 4-7800 Urman after loop:5 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 v_salary number; 9 begin 10 open elist; 11 dbms_output.put_line('after open before fetch:'||elist%rowcount); 12 loop 13 fetch elist into v_last_name,v_salary; 14 exit when elist%notfound; 15 dbms_output.put_line(elist%rowcount||'-'||v_salary||' '||v_last_name); 16 exit when elist%rowcount >=4; 17 end loop; 18 dbms_output.put_line('after loop:'||elist%rowcount); 19 close elist; 20* end; SQL> / after open before fetch:0 1-12008 Greenberg 2-9000 Faviet 3-8200 Chen 4-7800 Urman after loop:4 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name,salary 4 from employees 5 where department_id=100 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 v_salary number; 9 begin 10 open elist; 11 dbms_output.put_line('after open before fetch:'||elist%rowcount); 12 loop 13 fetch elist into v_last_name,v_salary; 14 exit when elist%notfound; 15 dbms_output.put_line(elist%rowcount||'-'||v_salary||' '||v_last_name); 16 exit when elist%rowcount >=4; 17 end loop; 18 dbms_output.put_line('after loop:'||elist%rowcount); 19 close elist; 20 dbms_output.put_line('after close:'||elist%rowcount); 21* end; 22 / after open before fetch:0 1-12008 Greenberg 2-9000 Faviet 3-8200 Chen 4-7800 Urman after loop:4 declare * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at line 20 SQL> spool off