SQL> --explicit cursors SQL> --1-Write an anonymous block that will read a department number from keyboard and print the last name of all employees in that department sorted alphabetically 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=&any_deptid 4 order by LAST_NAME; Enter value for any_deptid: 10 old 3: where department_id=&any_deptid new 3: where department_id=10 LAST_NAME ------------------------- Whalen SQL> / Enter value for any_deptid: 20 old 3: where department_id=&any_deptid new 3: where department_id=20 LAST_NAME ------------------------- Fay Hartstein SQL> / Enter value for any_deptid: 60 old 3: where department_id=&any_deptid new 3: where department_id=60 LAST_NAME ------------------------- Austin Ernst Hunold Lorentz Pataballa SQL> ed Wrote file afiedt.buf 1 declare 2 cursor ali is 3 select LAST_NAME, salary 4 from employees 5 where department_id=&any_deptid 6 order by LAST_NAME; 7 ugurcan employees.last_name%type; 8 molhim employees.salary%type; 9 begin 10 open ali; 11 loop 12 fetch ali into ugurcan, molhim; 13 exit when ali%notfound; 14 dbms_output.put_line(ugurcan||'-'||molhim); 15 end loop; 16 dbms_output.put_line('The number of employees is '||ali%rowcount); 17 close ali; 18* end; 19 / Enter value for any_deptid: 10 old 5: where department_id=&any_deptid new 5: where department_id=10 PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> / Enter value for any_deptid: 10 old 5: where department_id=&any_deptid new 5: where department_id=10 Whalen-4400 The number of employees is 1 PL/SQL procedure successfully completed. SQL> / Enter value for any_deptid: 60 old 5: where department_id=&any_deptid new 5: where department_id=60 Austin-4800 Ernst-6000 Hunold-9000 Lorentz-4200 Pataballa-4800 The number of employees is 5 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor ali is 3 select LAST_NAME, salary 4 from employees 5 where department_id=&any_deptid 6 order by LAST_NAME; 7 ugurcan employees.last_name%type; 8 molhim employees.salary%type; 9 begin 10 open ali; 11 loop 12 fetch ali into ugurcan, molhim; 13 exit when ali%notfound; 14 dbms_output.put_line(ugurcan||'-'||molhim); 15 end loop; 16 dbms_output.put_line('The number of employees is '||ali%rowcount); 17 close ali; 18* end; SQL> --2-list name of 10 highest paid employees SQL> select last_name.salary 2 . SQL> pagesize 14 pagesize 14 linesize 80 ed Wrote file afiedt.buf 1 select last_name,salary 2 from employees 3* order by salary SQL> / LAST_NAME SALARY ------------------------- ---------- Olson 2100 Markle 2200 Philtanker 2200 Landry 2400 Gee 2400 Colmenares 2500 Marlow 2500 Patel 2500 Vargas 2500 Sullivan 2500 Perkins 2500 Himuro 2600 Matos 2600 OConnell 2600 Grant 2600 Mikkilineni 2700 Seo 2700 Tobias 2800 Atkinson 2800 Geoni 2800 Jones 2800 Baida 2900 Rogers 2900 Gates 2900 Cabrio 3000 Feeney 3000 Khoo 3100 Davies 3100 Fleaur 3100 Walsh 3100 Nayer 3200 Stiles 3200 Taylor 3200 McCain 3200 Bissot 3300 Mallin 3300 Dellinger 3400 Rajs 3500 Ladwig 3600 Dilly 3600 Chung 3800 Everett 3900 Bell 4000 Bull 4100 Lorentz 4200 Sarchand 4200 Whalen 4400 Austin 4800 Pataballa 4800 Mourgos 5800 Ernst 6000 Fay 6000 Kumar 6100 Banda 6200 Johnson 6200 Ande 6400 Vollman 6500 Mavris 6500 Lee 6800 Popp 6900 Tuvault 7000 Sewall 7000 Grant 7000 Marvins 7200 Bates 7300 Smith 7400 Cambrault 7500 Doran 7500 Sciarra 7700 Urman 7800 Kaufling 7900 Weiss 8000 Olsen 8000 Smith 8000 Chen 8200 Fripp 8200 Gietz 8300 Livingston 8400 Taylor 8600 Hutton 8800 Hunold 9000 Faviet 9000 Hall 9000 McEwen 9000 Sully 9500 Greene 9500 Bernstein 9500 Fox 9600 Baer 10000 Bloom 10000 King 10000 Tucker 10000 Vishney 10500 Zlotkey 10500 Cambrault 11000 Raphaely 11000 Abel 11000 Ozer 11500 Errazuriz 12000 Higgins 12008 Greenberg 12008 Hartstein 13000 Partners 13500 Russell 14000 De Haan 17000 Kochhar 17000 King 24000 107 rows selected. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_elist is 3 select last_name 4 from employees 5 order by salary desc; 6 v_last_name employees.last_name%type; 7 begin 8 open cur_elist; 9 loop 10 fetch cur_elist into v_last_name; 11 exit when cur_elist%rowcount > 10; 12 dbms_output.put_line(v_last_name); 13 end loop; 14 close cur_elist; 15* end; 16 / King Kochhar De Haan Russell Partners Hartstein Greenberg Higgins Errazuriz Ozer PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_elist is 3 select last_name 4 from employees 5 where department_id = &anydeptid 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 begin 9 open cur_elist; 10 loop 11 fetch cur_elist into v_last_name; 12 exit when cur_elist%rowcount > 10; 13 dbms_output.put_line(v_last_name); 14 end loop; 15 close cur_elist; 16* end; SQL> / Enter value for anydeptid: 10 old 5: where department_id = &anydeptid new 5: where department_id = 10 Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen Whalen declare * ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 32 ORA-06512: at "SYS.DBMS_OUTPUT", line 97 ORA-06512: at "SYS.DBMS_OUTPUT", line 112 ORA-06512: at line 13 SQL> / Enter value for anydeptid: 60 old 5: where department_id = &anydeptid new 5: where department_id = 60 Hunold Ernst Austin Pataballa Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz Lorentz declare * ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 32 ORA-06512: at "SYS.DBMS_OUTPUT", line 97 ORA-06512: at "SYS.DBMS_OUTPUT", line 112 ORA-06512: at line 13 SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_elist is 3 select last_name 4 from employees 5 where department_id = &anydeptid 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 begin 9 open cur_elist; 10 loop 11 fetch cur_elist into v_last_name; 12 exit when cur_elist%rowcount > 10 or cur_elist%notfound; 13 dbms_output.put_line(v_last_name); 14 end loop; 15 close cur_elist; 16* end; SQL> / Enter value for anydeptid: 60 old 5: where department_id = &anydeptid new 5: where department_id = 60 Hunold Ernst Austin Pataballa Lorentz PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_elist is 3 select last_name 4 from employees 5 where department_id = &anydeptid 6 order by salary desc; 7 v_last_name employees.last_name%type; 8 begin 9 open cur_elist; 10 loop 11 fetch cur_elist into v_last_name; 12 exit when cur_elist%rowcount > 10 or cur_elist%notfound; 13 dbms_output.put_line(v_last_name); 14 end loop; 15 close cur_elist; 16* end; SQL> spool off