Using MySQL... 1. Set echo on 2. SET SERVEROUT ON 3. Set up a spool file to rece

Question

Using MySQL...

1.            Set echo on

2.            SET SERVEROUT ON

3.            Set up a spool file to receive your output for submission. I would suggest c:CS4210wa4spool.txt .

4.            DECLARE a record variable (Emp_rec) using %ROWTYPE

5.            In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES for an Employee_ID to be determined at run time

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

7.            Use a CASE statement to print the associated Department Name for DEPARTMENT_ID

8.            Use TO_CHAR to format the salary as $999,999

9.            Add a EXCEPTION block to report when no data is found

10.          Compile and run the procedure for the Employee, 114

11.          Close the spool file

Explanation / Answer

DELIMITER //
CREATE PROCEDURE getemployee( IN i int,IN d char(9))
BEGIN
DECLARE emp_rec INT;
SELECT name+''+dept+''+age+''+SALARY into emp_rec from employee where id=i ;
CASE deptno
WHEN 1 THEN
select dept from employee where deptno = 1;
WHEN 2 THEN
select dept from employee where deptno = 2;
ELSE
set emp_rec='';
END CASE;
END //
DELIMITER ;

CALL `cheggtbl`.`getemployee`(1, 'it');

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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