Using MySQL... Set echo on SET SERVEROUT ON Set up a spool file to receive your

Question

Using MySQL...

Set echo on

SET SERVEROUT ON

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

Explanation / Answer

SET echo ON
SET SERVEROUTPUT ON
SPOOL c:CS4210wa5spool.txt;

DECLARE

Emp_rec HR.EMPLOYEES%ROWTYPE;

PROCEDURE pro_name(emp_id IN INTEGER) IS

BEGIN

SELECT * into Emp_rec
FROM HR.EMPLOYEES%ROWTYPE
WHERE employee_id = emp_id;

dbms_output.put_line('Employee Department :'|| Emp_rec.department_id||
'Employee ID : '|| Emp_rec.employee_id||
'Employee first name : '|| Emp_rec.First_name||
'Employee last name : '||Emp_rec.Last_name
'Salary : '||to_char(Emp_rec.salary,'$999,999'));

CASE Emp_rec.department_id
WHEN 10 THEN dbms_output.put_line('Employee Department :'|| Emp_rec.department_name);
WHEN 20 THEN dbms_output.put_line('Employee Department :'|| Emp_rec.department_name);
WHEN 30 THEN dbms_output.put_line('Employee Department :'|| Emp_rec.department_name);

ELSE dbms_output.put_line('No Match Found ');
END CASE
  
EXCEPTION NO_DATA_FOUND THEN
dbms_output.put_line('No data Found');
END;

SPOOL OFF;

Feel free to reach out if you have any doubts.
Rate if the answer was helpful.
Thanks

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.