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

for all employees WHERE DEPARTMENT_ID = 30

Add a LOOP to print all the records

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

Hi,

This solution is only possible in Oracle as MySQL does not support ROWTYPE.

Below is the oracle code for the problem-

Set echo on;
SET SERVEROUT ON;
spool c:CS4210wa5spool.txt;
DECLARE
Emp_rec EMPLOYEES%ROWTYPE;
BEGIN
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
INTO Emp_rec
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID = 30;
FOR i IN Emp_rec.FIRST .. Emp_rec.LAST
LOOP
DBMS_OUTPUT.put_line(Emp_rec.EMPLOYEE_ID);
DBMS_OUTPUT.put_line(Emp_rec.FIRST_NAME);
DBMS_OUTPUT.put_line(Emp_rec.LAST_NAME);
DBMS_OUTPUT.put_line(TO_CHAR(Emp_rec.SALARY,'999.999'));
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line("No Data Exists");
END;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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