SQL> set serveroutput on SQL> set pagesize 100 SQL> --write an anonymous block that reads a region_id from keyboard and prints name of that region on screen SQL> ed Wrote file afiedt.buf 1 declare 2 v regions.region_name%type; 3 begin 4 select region_name into v 5 from regions 6 where region_id=&x; 7 dbms_output.put_line('The name of the region is '||v); 8* end; SQL> / Enter value for x: 3 old 6: where region_id=&x; new 6: where region_id=3; The name of the region is Asia PL/SQL procedure successfully completed. SQL> --the substitution variable is replaced by the value entered from the keyboard before compilation SQL> ed Wrote file afiedt.buf 1 declare 2 v regions.region_name%type; 3 begin 4 select region_name into v 5 from regions 6 where region_id=&x; 7 dbms_output.put_line('The name of the region is '||v); 8* end; SQL> ed Wrote file afiedt.buf 1 declare 2 v regions.region_name%type; 3 begin 4 select region_name into v 5 where region_id=&x; 6 dbms_output.put_line('The name of the region is '||v); 7* end; SQL> / Enter value for x: 4 old 5: where region_id=&x; new 5: where region_id=4; where region_id=4; * ERROR at line 5: ORA-06550: line 5, column 4: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 4, column 4: PL/SQL: SQL Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name regions.region_name%type; 3 begin 4 select region_name into v_region_name 5 from regions 6 where region_id=&id_of_region; 7 dbms_output.put_line('The name of the region is '||v_region_name); 8* end; SQL> / Enter value for id_of_region: 1 old 6: where region_id=&id_of_region; new 6: where region_id=1; The name of the region is Europe PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name regions.region_name%type; 3 v_region_id regions.region_id%type; 4 begin 5 v_region_id := &id_of_region; 6 select region_name into v_region_name 7 from regions 8 where region_id=v_region_id; 9 dbms_output.put_line('The name of the region is '||v_region_name); 10* end; 11 / Enter value for id_of_region: 2 old 5: v_region_id := &id_of_region; new 5: v_region_id := 2; The name of the region is Americas PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name regions.region_name%type; 3 v_region_id regions.region_id%type; 4 begin 5 v_region_id := &id_of_region; 6 select region_name into v_region_name 7 from regions 8 where region_id=v_region_id; 9 dbms_output.put_line('The name of the region is '||v_region_name); 10 dbms_output.put_line(sql%rowcount||' regions were displayed'); 11* end; SQL> / Enter value for id_of_region: 4 old 5: v_region_id := &id_of_region; new 5: v_region_id := 4; The name of the region is Middle East and Africa 1 regions were displayed PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name regions.region_name%type; 3 v_region_id regions.region_id%type; 4 begin 5 v_region_id := &id_of_region; 6 select region_name into v_region_name 7 from regions 8 where region_id=v_region_id; 9 dbms_output.put_line('The name of the region is '||v_region_name); 10 dbms_output.put_line(sql%rowcount||' regions were displayed'); 11* end; SQL> / Enter value for id_of_region: 9 old 5: v_region_id := &id_of_region; new 5: v_region_id := 9; declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 6 SQL> --select into crashes when the select statement returns no rows!!!!!! SQL> --in that case we get the error (exception) with error number:ORA-01403 SQL> --and name of this exception is no_data_found SQL> SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name regions.region_name%type; 3 v_region_id regions.region_id%type; 4 begin 5 v_region_id := &id_of_region; 6 select region_name into v_region_name 7 from regions 8 where region_id > v_region_id; 9 dbms_output.put_line('The name of the region is '||v_region_name); 10 dbms_output.put_line(sql%rowcount||' regions were displayed'); 11* end; 12 / Enter value for id_of_region: 3 old 5: v_region_id := &id_of_region; new 5: v_region_id := 3; The name of the region is Middle East and Africa 1 regions were displayed PL/SQL procedure successfully completed. SQL> / Enter value for id_of_region: 0 old 5: v_region_id := &id_of_region; new 5: v_region_id := 0; declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 6 SQL> --select into crashes with exception number ORA-01422 SQL> --when the select statement returns more than one row SQL> --the exceptýon message is ORA-01422: exact fetch returns more than requested number of rows SQL> --the name of this wexception is too_many_rows SQL> SQL> SQL> e3xcercises SP2-0734: unknown command beginning "e3xcercise..." - rest of line ignored. SQL> --exercises SQL> --1- write an anonymous block to print number of employees in a department SQL> --read department number 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> select count(*) 2 from employees 3 where department_id=30; COUNT(*) ---------- 6 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=30; COUNT(*) ---------- 6 SQL> ed Wrote file afiedt.buf 1 select count(*) 2 from employees 3* where department_id=&department_id SQL> / Enter value for department_id: 10 old 3: where department_id=&department_id new 3: where department_id=10 COUNT(*) ---------- 1 SQL> / Enter value for department_id: 20 old 3: where department_id=&department_id new 3: where department_id=20 COUNT(*) ---------- 2 SQL> / Enter value for department_id: 100 old 3: where department_id=&department_id new 3: where department_id=100 COUNT(*) ---------- 6 SQL> / Enter value for department_id: 33 old 3: where department_id=&department_id new 3: where department_id=33 COUNT(*) ---------- 0 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=&department_id; 7 dbms_output.put_line('There are '||v_count||' employees'); 8* end; 9 / Enter value for department_id: 10 old 6: where department_id=&department_id; new 6: where department_id=10; There are 1 employees PL/SQL procedure successfully completed. 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 SQL> -- "Medium sized department" if the number of employees is between 11 and 30 SQL> -- "Big department" otherwise SQL> SQL> --write an anonymous block that will read a depoartment id from keyboard and print SQL> --as many starts as theer are employees in the department SQL> spool off