SQL> set serveroutput on SQL> --write an anonynous block to display lastname and salary of all employees who work for a givenen department, read the departmentid from keyboard SQL> declare 2 cursor emp_det is 3 select last_name, salary 4 from employees 5 where department_id=&department_identify; 6 v_lastname employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_det 10 loop 11 fetch emp_det into v_last_name, v_salary; 12 exit when emp_det%notfound; 13 dbms_output.put_line(v_lastname ||' salary is '|| v_salary); 14 end loop; 15 close emp_det; 16 end; 17 / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; loop * ERROR at line 10: ORA-06550: line 10, column 1: PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: . ( % ; for The symbol "; was inserted before "LOOP" to continue. ORA-06550: line 15, column 1: PLS-00103: Encountered the symbol "CLOSE" SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary 4 from employees 5 where department_id=&department_identify; 6 v_lastname employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_det; 10 loop 11 fetch emp_det into v_last_name, v_salary; 12 exit when emp_det%notfound; 13 dbms_output.put_line(v_lastname ||' salary is '|| v_salary); 14 end loop; 15 close emp_det; 16* end; SQL> / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; fetch emp_det into v_last_name, v_salary; * ERROR at line 11: ORA-06550: line 11, column 20: PLS-00201: identifier 'V_LAST_NAME' must be declared ORA-06550: line 11, column 1: PL/SQL: SQL Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary 4 from employees 5 where department_id=&department_identify; 6 v_lastname employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_det; 10 loop 11 fetch emp_det into v_lastname, v_salary; 12 exit when emp_det%notfound; 13 dbms_output.put_line(v_lastname ||' salary is '|| v_salary); 14 end loop; 15 close emp_det; 16* end; SQL> / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; Whalen salary is 4400 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary 4 from employees 5 where department_id=&department_identify; 6 v_lastname employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_det; 10 loop 11 fetch emp_det into v_lastname, v_salary; 12 exit when emp_det%notfound; 13 dbms_output.put_line(v_lastname ||' salary is '|| v_salary); 14 end loop; 15 close emp_det; 16* end; 17 / Enter value for department_identify: 50 old 5: where department_id=&department_identify; new 5: where department_id=50; Weiss salary is 8000 Fripp salary is 8200 Kaufling salary is 7900 Vollman salary is 6500 Mourgos salary is 5800 Nayer salary is 3200 Mikkilineni salary is 2700 Landry salary is 2400 Markle salary is 2200 Bissot salary is 3300 Atkinson salary is 2800 Marlow salary is 2500 Olson salary is 2100 Mallin salary is 3300 Rogers salary is 2900 Gee salary is 2400 Philtanker salary is 2200 Ladwig salary is 3600 Stiles salary is 3200 Seo salary is 2700 Patel salary is 2500 Rajs salary is 3500 Davies salary is 3100 Matos salary is 2600 Vargas salary is 2500 Taylor salary is 3200 Fleaur salary is 3100 Sullivan salary is 2500 Geoni salary is 2800 Sarchand salary is 4200 Bull salary is 4100 Dellinger salary is 3400 Cabrio salary is 3000 Chung salary is 3800 Dilly salary is 3600 Gates salary is 2900 Perkins salary is 2500 Bell salary is 4000 Everett salary is 3900 McCain salary is 3200 Jones salary is 2800 Walsh salary is 3100 Feeney salary is 3000 OConnell salary is 2600 Grant salary is 2600 PL/SQL procedure successfully completed. SQL> l 1 declare 2 cursor emp_det is 3 select last_name, salary 4 from employees 5 where department_id=&department_identify; 6 v_lastname employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_det; 10 loop 11 fetch emp_det into v_lastname, v_salary; 12 exit when emp_det%notfound; 13 dbms_output.put_line(v_lastname ||' salary is '|| v_salary); 14 end loop; 15 close emp_det; 16* end; SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 v_lastname employees.last_name%type; 7 v_salary employees.salary%type; 8 v_departmentid employees.department_id%type; 9 begin 10 open emp_det; 11 loop 12 fetch emp_det into v_lastname, v_salary, v_departmentid; 13 exit when emp_det%notfound; 14 dbms_output.put_line(v_lastname ||' salary is '|| v_salary || ' deptid ' || v_departmentid); 15 end loop; 16 close emp_det; 17* end; SQL> / Enter value for department_identify: 5 old 5: where department_id=&department_identify; new 5: where department_id=5; PL/SQL procedure successfully completed. SQL> / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; Whalen salary is 4400 deptid 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 --v_lastname employees.last_name%type; 7 --v_salary employees.salary%type; 8 --v_departmentid employees.department_id%type; 9 emp_rec emp_det%rowtype; 10 begin 11 open emp_det; 12 loop 13 fetch emp_det into emp_rec; 14 exit when emp_det%notfound; 15 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 16 end loop; 17 close emp_det; 18* end; 19 / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; Whalen salary is 4400 deptid 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 --v_lastname employees.last_name%type; 7 --v_salary employees.salary%type; 8 --v_departmentid employees.department_id%type; 9 emp_rec emp_det%rowtype; 10 begin 11 --open emp_det; 12 loop 13 fetch emp_det into emp_rec; 14 exit when emp_det%notfound; 15 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 16 end loop; 17 close emp_det; 18 exception 19 when invalid_cursor then 20 dbms_output.put_line('cursor not openned'); 21 when cursor_already_openned then 22 dbms_output.put_line('already openned'); 23 when invalid_number or value_error then 24 dbms_output.put_line(''); 25 when others then 26 dbms_output.put_line('Error!'); 27* end; 28 / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; when cursor_already_openned then * ERROR at line 21: ORA-06550: line 21, column 6: PLS-00201: identifier 'CURSOR_ALREADY_OPENNED' must be declared ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 --v_lastname employees.last_name%type; 7 --v_salary employees.salary%type; 8 --v_departmentid employees.department_id%type; 9 emp_rec emp_det%rowtype; 10 begin 11 --open emp_det; 12 loop 13 fetch emp_det into emp_rec; 14 exit when emp_det%notfound; 15 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 16 end loop; 17 close emp_det; 18 exception 19 when invalid_cursor then 20 dbms_output.put_line('cursor not openned'); 21 when cursor_already_open then 22 dbms_output.put_line('already openned'); 23 when invalid_number or value_error then 24 dbms_output.put_line(''); 25 when others then 26 dbms_output.put_line('Error!'); 27* end; SQL> / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; cursor not openned PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 --v_lastname employees.last_name%type; 7 --v_salary employees.salary%type; 8 --v_departmentid employees.department_id%type; 9 emp_rec emp_det%rowtype; 10 begin 11 open emp_det; 12 open emp_det; 13 loop 14 fetch emp_det into emp_rec; 15 exit when emp_det%notfound; 16 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 17 end loop; 18 close emp_det; 19 exception 20 when invalid_cursor then 21 dbms_output.put_line('cursor not openned'); 22 when cursor_already_open then 23 dbms_output.put_line('already openned'); 24 when invalid_number or value_error then 25 dbms_output.put_line(''); 26 when others then 27 dbms_output.put_line('Error!'); 28* end; SQL> / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; already openned PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 --v_lastname employees.last_name%type; 7 --v_salary employees.salary%type; 8 --v_departmentid employees.department_id%type; 9 emp_rec emp_det%rowtype; 10 begin 11 open emp_det; 12 open emp_det; 13 loop 14 fetch emp_det into emp_rec; 15 exit when emp_det%notfound; 16 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 17 end loop; 18 close emp_det; 19 exception 20 when invalid_cursor then 21 dbms_output.put_line('cursor not openned'); 22 when cursor_already_open then 23 dbms_output.put_line('already openned'); 24 when invalid_number or value_error then 25 dbms_output.put_line('type mismatch'); 26 when others then 27 dbms_output.put_line('Error!'); 28* end; SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 --v_lastname employees.last_name%type; 7 --v_salary employees.salary%type; 8 --v_departmentid employees.department_id%type; 9 emp_rec emp_det%rowtype; 10 begin 11 open emp_det; 12 loop 13 fetch emp_det into emp_rec; 14 exit when emp_det%notfound; 15 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 16 end loop; 17 close emp_det; 18 exception 19 when invalid_cursor then 20 dbms_output.put_line('cursor not openned'); 21 when cursor_already_open then 22 dbms_output.put_line('already openned'); 23 when invalid_number or value_error then 24 dbms_output.put_line('type mismatch'); 25 when others then 26 dbms_output.put_line('Error!'); 27* end; 28 / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; Whalen salary is 4400 deptid 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 --v_lastname employees.last_name%type; 7 --v_salary employees.salary%type; 8 --v_departmentid employees.department_id%type; 9 emp_rec emp_det%rowtype; 10 begin 11 open emp_det; 12 loop 13 fetch emp_det into emp_rec; 14 exit when emp_det%notfound; 15 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 16 end loop; 17 dbms_output.put_line('number of rows returned is ' || emp_det%rowcount); 18 close emp_det; 19 dbms_output.put_line('********'); 20 open emp_det; 21 loop 22 fetch emp_det into emp_rec; 23 exit when emp_det%notfound; 24 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 25 end loop; 26 close emp_det; 27 exception 28 when invalid_cursor then 29 dbms_output.put_line('cursor not openned'); 30 when cursor_already_open then 31 dbms_output.put_line('already openned'); 32 when invalid_number or value_error then 33 dbms_output.put_line('type mismatch'); 34 when others then 35 dbms_output.put_line('Error!'); 36* end; 37 / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; Whalen salary is 4400 deptid 10 number of rows returned is 1 ******** Whalen salary is 4400 deptid 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det is 3 select last_name, salary, department_id 4 from employees 5 where department_id=&department_identify; 6 --v_lastname employees.last_name%type; 7 --v_salary employees.salary%type; 8 --v_departmentid employees.department_id%type; 9 emp_rec emp_det%rowtype; 10 begin 11 open emp_det; 12 loop 13 fetch emp_det into emp_rec; 14 exit when emp_det%notfound; 15 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary || ' deptid ' || emp_rec.department_id); 16 end loop; 17 dbms_output.put_line('number of rows returned is ' || emp_det%rowcount); 18 close emp_det; 19 dbms_output.put_line('********'); 20 open emp_det; 21 loop 22 fetch emp_det into emp_rec; 23 exit when emp_det%notfound; 24 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary ); 25 end loop; 26 close emp_det; 27 exception 28 when invalid_cursor then 29 dbms_output.put_line('cursor not openned'); 30 when cursor_already_open then 31 dbms_output.put_line('already openned'); 32 when invalid_number or value_error then 33 dbms_output.put_line('type mismatch'); 34 when others then 35 dbms_output.put_line('Error!'); 36* end; 37 / Enter value for department_identify: 10 old 5: where department_id=&department_identify; new 5: where department_id=10; Whalen salary is 4400 deptid 10 number of rows returned is 1 ******** Whalen salary is 4400 PL/SQL procedure successfully completed. SQL> --write an anonymous block that uses a cursor with parameter to display employees who earn more than a given salary salary should be pased as a parameter to the cursor use 3000 SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det (v_salary employees.salary%type) is 3 select last_name, salary 4 from employees 5 where salary>v_salary; 6 emp_rec emp_det%rowtype; 7 begin 8 open emp_det (3000); 9 loop 10 fetch emp_det into emp_rec; 11 exit when emp_det%notfound; 12 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary); 13 end loop; 14 dbms_output.put_line('number of rows returned is ' || emp_det%rowcount); 15 close emp_det; 16 exception 17 when invalid_cursor then 18 dbms_output.put_line('cursor not openned'); 19 when cursor_already_open then 20 dbms_output.put_line('already openned'); 21 when invalid_number or value_error then 22 dbms_output.put_line('type mismatch'); 23 when others then 24 dbms_output.put_line('Error!'); 25* end; 26 / King salary is 24000 Kochhar salary is 17000 De Haan salary is 17000 Hunold salary is 9000 Ernst salary is 6000 Austin salary is 4800 Pataballa salary is 4800 Lorentz salary is 4200 Greenberg salary is 12008 Faviet salary is 9000 Chen salary is 8200 Sciarra salary is 7700 Urman salary is 7800 Popp salary is 6900 Raphaely salary is 11000 Khoo salary is 3100 Weiss salary is 8000 Fripp salary is 8200 Kaufling salary is 7900 Vollman salary is 6500 Mourgos salary is 5800 Nayer salary is 3200 Bissot salary is 3300 Mallin salary is 3300 Ladwig salary is 3600 Stiles salary is 3200 Rajs salary is 3500 Davies salary is 3100 Russell salary is 14000 Partners salary is 13500 Errazuriz salary is 12000 Cambrault salary is 11000 Zlotkey salary is 10500 Tucker salary is 10000 Bernstein salary is 9500 Hall salary is 9000 Olsen salary is 8000 Cambrault salary is 7500 Tuvault salary is 7000 King salary is 10000 Sully salary is 9500 McEwen salary is 9000 Smith salary is 8000 Doran salary is 7500 Sewall salary is 7000 Vishney salary is 10500 Greene salary is 9500 Marvins salary is 7200 Lee salary is 6800 Ande salary is 6400 Banda salary is 6200 Ozer salary is 11500 Bloom salary is 10000 Fox salary is 9600 Smith salary is 7400 Bates salary is 7300 Kumar salary is 6100 Abel salary is 11000 Hutton salary is 8800 Taylor salary is 8600 Livingston salary is 8400 Grant salary is 7000 Johnson salary is 6200 Taylor salary is 3200 Fleaur salary is 3100 Sarchand salary is 4200 Bull salary is 4100 Dellinger salary is 3400 Chung salary is 3800 Dilly salary is 3600 Bell salary is 4000 Everett salary is 3900 McCain salary is 3200 Walsh salary is 3100 Whalen salary is 4400 Hartstein salary is 13000 Fay salary is 6000 Mavris salary is 6500 Baer salary is 10000 Higgins salary is 12008 Gietz salary is 8300 number of rows returned is 81 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det (v_salary employees.salary%type) is 3 select last_name, salary 4 from employees 5 where salary>v_salary; 6 emp_rec emp_det%rowtype; 7 begin 8 open emp_det (10000); 9 loop 10 fetch emp_det into emp_rec; 11 exit when emp_det%notfound; 12 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary); 13 end loop; 14 dbms_output.put_line('number of rows returned is ' || emp_det%rowcount); 15 close emp_det; 16 exception 17 when invalid_cursor then 18 dbms_output.put_line('cursor not openned'); 19 when cursor_already_open then 20 dbms_output.put_line('already openned'); 21 when invalid_number or value_error then 22 dbms_output.put_line('type mismatch'); 23 when others then 24 dbms_output.put_line('Error!'); 25* end; SQL> / King salary is 24000 Kochhar salary is 17000 De Haan salary is 17000 Greenberg salary is 12008 Raphaely salary is 11000 Russell salary is 14000 Partners salary is 13500 Errazuriz salary is 12000 Cambrault salary is 11000 Zlotkey salary is 10500 Vishney salary is 10500 Ozer salary is 11500 Abel salary is 11000 Hartstein salary is 13000 Higgins salary is 12008 number of rows returned is 15 PL/SQL procedure successfully completed. SQL> l 1 declare 2 cursor emp_det (v_salary employees.salary%type) is 3 select last_name, salary 4 from employees 5 where salary>v_salary; 6 emp_rec emp_det%rowtype; 7 begin 8 open emp_det (10000); 9 loop 10 fetch emp_det into emp_rec; 11 exit when emp_det%notfound; 12 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary); 13 end loop; 14 dbms_output.put_line('number of rows returned is ' || emp_det%rowcount); 15 close emp_det; 16 exception 17 when invalid_cursor then 18 dbms_output.put_line('cursor not openned'); 19 when cursor_already_open then 20 dbms_output.put_line('already openned'); 21 when invalid_number or value_error then 22 dbms_output.put_line('type mismatch'); 23 when others then 24 dbms_output.put_line('Error!'); 25* end; SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_det (v_salary employees.salary%type) is 3 select last_name, salary 4 from employees 5 where salary>v_salary; 6 emp_rec emp_det%rowtype; 7 begin 8 open emp_det (10000); 9 loop 10 fetch emp_det into emp_rec; 11 exit when emp_det%notfound; 12 dbms_output.put_line(emp_rec.last_name ||' salary is '|| emp_rec.salary); 13 end loop; 14 dbms_output.put_line('number of rows returned is ' || emp_det%rowcount); 15 close emp_det; 16 exception 17 when invalid_cursor then 18 dbms_output.put_line('cursor not openned'); 19 when cursor_already_open then 20 dbms_output.put_line('already openned'); 21 when invalid_number or value_error then 22 dbms_output.put_line('type mismatch'); 23 when others then 24 dbms_output.put_line('Error!'); 25* end; SQL> spool off