SQL> --write an anonymous block to display name of region given the region numbner. SQL> --read the region number from keyboard SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> select REGION_NAME 2 from regions 3 where region_id=&x; Enter value for x: 10 old 3: where region_id=&x new 3: where region_id=10 no rows selected SQL> select * from regions 2 ; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> select REGION_NAME 2 from regions 3 where region_id=&x; Enter value for x: 3 old 3: where region_id=&x new 3: where region_id=3 REGION_NAME ------------------------- Asia SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name varchar2(25); 3 begin 4 select REGION_NAME into v_region_name 5 from regions 6 where region_id=®ion_id; 7 dbms_output.put_line(v_region_name||' is the answer'||sql%rowcount); 8* end; SQL> / Enter value for region_id: 3 old 6: where region_id=®ion_id; new 6: where region_id=3; Asia is the answer1 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name varchar2(25); 3 begin 4 select REGION_NAME into v_region_name 5 from regions 6 where region_id=®ion_id; 7 dbms_output.put_line(v_region_name||' is the answer'||sql%rowcount); 8* end; SQL> / Enter value for region_id: 1 old 6: where region_id=®ion_id; new 6: where region_id=1; Europe is the answer1 PL/SQL procedure successfully completed. SQL> / Enter value for region_id: 9 old 6: where region_id=®ion_id; new 6: where region_id=9; declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 SQL> --an exception is raised when select into finds no rows in the table SQL> --an exception is an error case SQL> --any exception that oracle can detect has a unique error number SQL> --in thgis case the error number is ORA-01403 SQL> --any exception that oracle can detect has a message SQL> --in this case it is ORA-01403: no data found SQL> --some exceptions have names SQL> --in this case it is no_data_found SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name varchar2(25); 3 begin 4 select REGION_NAME into v_region_name 5 from regions 6 where region_id > ®ion_id; 7 dbms_output.put_line(v_region_name||' is the answer'||sql%rowcount); 8* end; 9 / Enter value for region_id: 3 old 6: where region_id > ®ion_id; new 6: where region_id > 3; Middle East and Africa is the answer1 PL/SQL procedure successfully completed. SQL> / Enter value for region_id: 1 old 6: where region_id > ®ion_id; new 6: where region_id > 1; declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4 SQL> --when select into returns more than one row an exception is raised SQL> --the error number id ORA-01422: SQL> --the message is ORA-01422: exact fetch returns more than requested number of rows SQL> --the name of this exception is too_many_rows SQL> --display name of the employees in a given department. nread dept no from keyboard SQL> desc departments Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> select * from departments; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 27 rows selected. 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> --you must use employees table SQL> select LAST_NAME 2 from employees 3 where DEPARTMENT_ID=&department_id; Enter value for department_id: 10 old 3: where DEPARTMENT_ID=&department_id new 3: where DEPARTMENT_ID=10 LAST_NAME ------------------------- Whalen SQL> / Enter value for department_id: 20 old 3: where DEPARTMENT_ID=&department_id new 3: where DEPARTMENT_ID=20 LAST_NAME ------------------------- Hartstein Fay SQL> --we must use explıcıt cursor SQL> ed Wrote file afiedt.buf 1 declare 2 cursor my_1st_curs is 3 select LAST_NAME,first_name 4 from employees 5 where DEPARTMENT_ID=&department_id; 6 v_last_name EMPLOYEES.LAST_NAME%TYPE; 7 v_fIrst_name EMPLOYEES.fIRST_NAME%TYPE; 8 begin 9 OPEN my_1st_curs; 10 LOOP 11 FETCH my_1st_curs INTO V_LAST_NAME,V_FIRST_NAME; 12 EXIT WHEN my_1st_curs%NOTFOUND; 13 DBMS_OUTPUT.PUT_LINE(V_LAST_NAME||' '||V_FIRST_NAME); 14 END LOOP; 15 DBMS_OUTPUT.PUT_LINE('THERE ARE '||MY_1ST_CURS%rowcount||' EMPLOYEES IN THIS DEPATRMENT.'); 16 CLOSE my_1st_curs; 17* end; 18 / Enter value for department_id: 10 old 5: where DEPARTMENT_ID=&department_id; new 5: where DEPARTMENT_ID=10; Whalen Jennifer THERE ARE 1 EMPLOYEES IN THIS DEPATRMENT. PL/SQL procedure successfully completed. SQL> / Enter value for department_id: 30 old 5: where DEPARTMENT_ID=&department_id; new 5: where DEPARTMENT_ID=30; Raphaely Den Khoo Alexander Baida Shelli Tobias Sigal Himuro Guy Colmenares Karen THERE ARE 6 EMPLOYEES IN THIS DEPATRMENT. PL/SQL procedure successfully completed. SQL> --1- write an anonymous block to print number of employees in a department SQL> --read department number from keyboard SQL> SELECT COUNT(*) 2 FROM EMPLOYEES 3 WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; Enter value for any_department_id: 10 old 3: WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID new 3: WHERE DEPARTMENT_ID=10 COUNT(*) ---------- 1 SQL> / Enter value for any_department_id: 30 old 3: WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID new 3: WHERE DEPARTMENT_ID=30 COUNT(*) ---------- 6 SQL> ed Wrote file afiedt.buf 1 declare 2 v_count number; 3 begin 4 SELECT COUNT(*) into v_count 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; 7 dbms_output.put_line(v_count||' is the answer'); 8* end; 9 / Enter value for any_department_id: 10 old 6: WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; new 6: WHERE DEPARTMENT_ID=10; 1 is the answer PL/SQL procedure successfully completed. SQL> / Enter value for any_department_id: 30 old 6: WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; new 6: WHERE DEPARTMENT_ID=30; 6 is the answer PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_count number; 3 begin 4 SELECT COUNT(*) into v_count 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; 7 dbms_output.put_line(v_count||' is the answer'); 8* end; SQL> --write an anonymous block to read a depoartment id from keyboard and print SQL> "Small department" if the number of employees is less than 10 SP2-0734: unknown command beginning ""Small dep..." - rest of line ignored. SQL> "Medium sized department" if the number of employees is between 11 and 30 SP2-0734: unknown command beginning ""Medium si..." - rest of line ignored. SQL> "Big department" otherwise SP2-0734: unknown command beginning ""Big depar..." - rest of line ignored. SQL> --write an anonymous block to read a depoartment id from keyboard and print SQL> --"Small department" if the number of employees is less than or equal to 10 SQL> --"Medium sized department" if the number of employees is between 11 and 30 SQL> --"Big department" otherwise SQL> ed Wrote file afiedt.buf 1 declare 2 v_count number; 3 begin 4 SELECT COUNT(*) into v_count 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; 7 dbms_output.put_line(v_count||' is the answer'); 8 if v_count<=10 then 9 dbms_output.put_line('small'); 10 elsif v_count between 11 and 30 then 11 dbms_output.put_line('medium'); 12 else 13 dbms_output.put_line('large'); 14 end if; 15* end; 16 / Enter value for any_department_id: 30 old 6: WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; new 6: WHERE DEPARTMENT_ID=30; 6 is the answer small PL/SQL procedure successfully completed. SQL> / Enter value for any_department_id: 100 old 6: WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; new 6: WHERE DEPARTMENT_ID=100; 6 is the answer small PL/SQL procedure successfully completed. SQL> / Enter value for any_department_id: 110 old 6: WHERE DEPARTMENT_ID=&ANY_DEPARTMENT_ID; new 6: WHERE DEPARTMENT_ID=110; 2 is the answer small PL/SQL procedure successfully completed. SQL> ----write an anonymous block to read a depoartment id from keyboard and print SQL> -- as many stars (*) as the number of employees. SQL> spool off