Written Assignment-Printing Report from Table

Set echo on

SET SERVEROUT ON

Set up a spool file to receive your output for submission. I would suggest c:\CS\wa6spool.txt

DECLARE a record variable, a variable to keep track of a change in Department_ID, and two accumulators

Add procedures to the DECLARE block to print a Head of Form, subtotals, and a grand total

In the BEGIN block add a select statement to read all employee records from HR.EMPLOYEES where DEPARTMENT_ID < 50, and a LOOP

Add DBMS_OUTPUT.PUT lines to build up the output line one field at a time

Add a DBMS_OUTPUT.NEW_LINE at the end to print the finished line

After the LOOP be sure to print the final subtotal and the grand total

Add a EXCEPTION block to report when no data is found

Compile and run the procedure

Close the spool file

SQL> Declare 2 Cursor employees_cursor 3 Is 4 select * from employees where DEPARTMENT_ID<20; 5 BEGIN 6 FOR T 7 IN employees_cursor 8 LOOP 9 dbms_output.put(t.employee_id); 10 dbms_output.put(t.first_name); 11 dbms_output.put(t.salary); 12 dbms_output.put_line(''); 13 end loop; 14 select sum(salary) into t1 from employees where department_id<10; 15 dbms_output.put_line('Total is '||t1); 16 exception 17 when no_data_found then 18 dbms_output.put_line('Sorry No data found'); 19 end; 20 / select sum(salary) into t1 from employees where department_id<10; * ERROR at line 14: ORA-06550: line 14, column 25: PLS-00201: identifier 'T1' must be declared ORA-06550: line 14, column 28: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 14, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 15, column 35: PLS-00201: identifier 'T1' must be declared ORA-06550: line 15, column 1: PL/SQL: Statement ignored