Help Please having problem please:

Set echo on

SET SERVEROUT ON

Set up a spool file to receive your output for submission. I would suggest c:\CS4210\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

You can use the IF Statement to determine the Department they work for, like we did in the past with a CASE.

You can use the IF Statement to print a message if they make over a certain amount of money.

Maybe they are due a raise if they were hired before a certain date. IF Statement

SQL> SET SERVEROUT ON SQL> DECLARE 2 EMP_REC HR.EMPLOYEES%ROWTYPE; 3 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE ('---------------------------------------'); 6 FOR EMP_REC IN 7 (SELECT * 8 FROM HR.EMPLOYEES 9 WHERE DEPARTMENT_ID = 30) 10 LOOP 11 CASE EMP_REC.DEPARTMENT_ID 12 WHEN 10 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: Administration'); 13 WHEN 20 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: MARKETING'); 14 WHEN 30 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: PURCHASING'); 15 WHEN 30 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: HUMAN RESOURCES'); 16 17 WHEN 50 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: SHIPPING'); 18 WHEN 60 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: IT'); 19 WHEN 70 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: PUBLIC RELATIONS') 20 ; 21 WHEN 80 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: SALES'); 22 WHEN 90 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: EXECUTIVE'); 23 WHEN 100 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: FINANCE'); 24 WHEN 110 THEN DBMS_OUTPUT.PUT_LINE ('EMPLOYEE DEPARTMENT: ACCOUNTING'); 25 END CASE; 26 DBMS_OUTPUT.PUT_LINE ('EMPLOYEE_ID: ' || EMP_REC.EMPLOYEE_ID) ; 27 DBMS_OUTPUT.PUT_LINE ('FIRST_NAME: ' || EMP_REC.FIRST_NAME) ; 28 DBMS_OUTPUT.PUT_LINE ('LAST_NAME: ' || EMP_REC.LAST_NAME) ; 29 DBMS_OUTPUT.PUT_LINE ('SALARY: ' || TO_CHAR (EMP_REC.SALARY, '$999,999')); 30 DBMS_OUTPUT.PUT_LINE ('---------------------------------------'); 31 END LOOP; 32 EXCEPTION 33 WHEN NO_DATA_FOUND THEN 34 DBMS_OUTPUT.PUT_LINE ('NO DATA FOUND.'); 35 36 END; 37 /