Written Assignment-Printing a Record Using an If Statement

Set echo on

SET SERVEROUT ON

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

DECLARE a record variable (Emp_rec) using %ROWTYPE

In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES

Add If Statement to print record

Add DBMS_OUTPUT lines to print EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record

Use TO_CHAR to format the salary as $999,999

Add a EXCEPTION block to report when no data is found

Compile and run the procedure.

Close the spool file

SQL> spool off SQL> SET echo ON SQL> SET serveroutput ON SQL> SET verify OFF SQL> Create or replace procedure data_in_spool is 2 Emp_rec HR.Employees%rowtype; 3 BEGIN 4 spool c:\CS\wa7spool.txt 5 select EMPLOYEE_ID, FIRST_NAME, LAST_NAME,to_char( SALARY, '$999,999' ) as sal into emp_rec from HR.employess; 6 if emp_rec.employee_id=123 then 7 dbms_output.put_line('EMPLOYEE_ID'||emp_rec.employee_id||', FIRST_NAME'||emp_rec.first_name||', LAST_NAME'||emp_rec.last_name||'and SALARY'||emp_rec.sal); 8 end if; 9 exception 10 when no_data_found then 11 dbms_output.put_line('NO data is there'); 12 spool off 13 END; 14 end data_in_spool; 15 begin 16 data_in_spool; 17 end; 18 /

Warning: Procedure created with compilation errors.