SQL Oracle Database 11g question: Following the example in Lecture 4 Set echo on
ID: 3785530 • Letter: S
Question
SQL Oracle Database 11g question:
Following the example in Lecture 4
Set echo on
SET SERVEROUT ON
Set up a spool file to receive your output for submission. I would suggest c:cs422awa4spool.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 an Employee_ID to be determined at run time
Add DBMS_OUTPUT lines to print DEPARTMENT, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record
Use a CASE statement to print the associated Department Name for DEPARTMENT_ID
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 for the Employee, 114
Close the spool file
Submit your spool output file (wa4spool.txt) to the form inside this folder.
Explanation / Answer
SET echo ON
SET SERVEROUTPUT ON
SPOOL c:cs422awa4spool.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;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.