SQL> set serveroutput on 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> select last_name 2 from employees 3 where department_id=10; LAST_NAME ------------------------- Whalen SQL> ed Wrote file afiedt.buf 1 select last_name 2 from employees 3* where department_id=40 SQL> / LAST_NAME ------------------------- Mavris SQL> select department_id, count(*) 2 from employees 3 group by department_id; DEPARTMENT_ID COUNT(*) ------------- ---------- 100 6 30 6 1 90 3 20 2 70 1 110 2 50 45 80 34 40 1 60 5 DEPARTMENT_ID COUNT(*) ------------- ---------- 10 1 12 rows selected. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name 4 from employees 5 where department_id=30; 6 --data type of v_last_name is the same as that of last_name column of employees table 7 v_last_name employees.last_name%type; 8 begin 9 open elist; 10 loop 11 fetch elist into v_last_name; 12 exit when elist%notfound; 13 dbms_output.put_line(v_last_name); 14 end loop; 15 close elist; 16* end; 17 / Raphaely Khoo Baida Tobias Himuro Colmenares 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=30; 6 --data type of v_last_name is the same as that of last_name column of employees table 7 v_last_name employees.last_name%type; 8 v_salary employees.salary%type; 9 begin 10 open elist; 11 loop 12 fetch elist into v_last_name, v_salary; 13 exit when elist%notfound; 14 dbms_output.put_line(v_last_name||' '||v_salary); 15 end loop; 16 close elist; 17* end; SQL> / Raphaely 11000 Khoo 3100 Baida 2900 Tobias 2800 Himuro 2600 Colmenares 2500 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=30 6 order by salary desc; 7 --data type of v_last_name is the same as that of last_name column of employees table 8 v_last_name employees.last_name%type; 9 v_salary employees.salary%type; 10 begin 11 open elist; 12 loop 13 fetch elist into v_last_name, v_salary; 14 exit when elist%notfound; 15 dbms_output.put_line(v_last_name||' '||v_salary); 16 end loop; 17 close elist; 18* end; 19 / Raphaely 11000 Khoo 3100 Baida 2900 Tobias 2800 Himuro 2600 Colmenares 2500 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=30 6 order by salary asc; 7 --data type of v_last_name is the same as that of last_name column of employees table 8 v_last_name employees.last_name%type; 9 v_salary employees.salary%type; 10 begin 11 open elist; 12 loop 13 fetch elist into v_last_name, v_salary; 14 exit when elist%notfound; 15 dbms_output.put_line(v_last_name||' '||v_salary); 16 end loop; 17 close elist; 18* end; SQL> / Colmenares 2500 Himuro 2600 Tobias 2800 Baida 2900 Khoo 3100 Raphaely 11000 PL/SQL procedure successfully completed. SQL> --list the 3 lowest earning employees in department 30 SQL> ed Wrote file afiedt.buf 1 declare 2 cursor elist is 3 select last_name, salary 4 from employees 5 where department_id=30 6 order by salary asc; 7 --data type of v_last_name is the same as that of last_name column of employees table 8 v_last_name employees.last_name%type; 9 v_salary employees.salary%type; 10 c number :=0; 11 begin 12 open elist; 13 loop 14 fetch elist into v_last_name, v_salary; 15 exit when elist%notfound or c>3; 16 c := c+1; 17 dbms_output.put_line(v_last_name||' '||v_salary); 18 end loop; 19 close elist; 20* end; 21 / Colmenares 2500 Himuro 2600 Tobias 2800 Baida 2900 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=30 6 order by salary asc; 7 --data type of v_last_name is the same as that of last_name column of employees table 8 v_last_name employees.last_name%type; 9 v_salary employees.salary%type; 10 c number :=0; 11 begin 12 open elist; 13 loop 14 fetch elist into v_last_name, v_salary; 15 exit when elist%notfound or c>=3; 16 c := c+1; 17 dbms_output.put_line(v_last_name||' '||v_salary); 18 end loop; 19 close elist; 20* end; SQL> / Colmenares 2500 Himuro 2600 Tobias 2800 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=30 6 order by salary asc; 7 --data type of v_last_name is the same as that of last_name column of employees table 8 v_last_name employees.last_name%type; 9 v_salary employees.salary%type; 10 begin 11 open elist; 12 loop 13 fetch elist into v_last_name, v_salary; 14 exit when elist%notfound or elist%rowcount >3; 15 dbms_output.put_line(v_last_name||' '||v_salary); 16 end loop; 17 close elist; 18* end; 19 / Colmenares 2500 Himuro 2600 Tobias 2800 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=30 6 order by salary asc; 7 --data type of v_last_name is the same as that of last_name column of employees table 8 v_last_name employees.last_name%type; 9 v_salary employees.salary%type; 10 begin 11 open elist; 12 loop 13 fetch elist into v_last_name, v_salary; 14 exit when elist%notfound or elist%rowcount >3; 15 dbms_output.put_line(elist%rowcount||' '||v_last_name||' '||v_salary); 16 end loop; 17 close elist; 18* end; 19 / 1 Colmenares 2500 2 Himuro 2600 3 Tobias 2800 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=30 6 order by salary asc; 7 --data type of v_last_name is the same as that of last_name column of employees table 8 v_last_name employees.last_name%type; 9 v_salary employees.salary%type; 10 begin 11 dbms_output.put_line(elist%rowcount); 12 open elist; 13 loop 14 fetch elist into v_last_name, v_salary; 15 exit when elist%notfound or elist%rowcount >3; 16 dbms_output.put_line(elist%rowcount||' '||v_last_name||' '||v_salary); 17 end loop; 18 close elist; 19* end; 20 / declare * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at line 11 SQL> --you cannot access the cursor attrżbute rowcożunt unless you open the cursor SQL> -- żf yożu try to USE a cursor without openning it, you 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=30 6 order by salary asc; 7 --data type of v_last_name is the same as that of last_name column of employees table 8 v_last_name employees.last_name%type; 9 v_salary employees.salary%type; 10 begin 11 open elist; 12 dbms_output.put_line('before loop**'||elist%rowcount||'**'); 13 loop 14 fetch elist into v_last_name, v_salary; 15 exit when elist%notfound or elist%rowcount >3; 16 dbms_output.put_line(elist%rowcount||' '||v_last_name||' '||v_salary); 17 end loop; 18 dbms_output.put_line('after loop**'||elist%rowcount||'**'); 19 close elist; 20* end; 21 / before loop**0** 1 Colmenares 2500 2 Himuro 2600 3 Tobias 2800 after loop**4** PL/SQL procedure successfully completed. SQL> spool off