Following the example in Lecture 3, write a program to print a record from HR.EM
ID: 3877158 • Letter: F
Question
Following the example in Lecture 3, write a program to print a record from HR.EMPLOYEES.
Set echo on
SET SERVEROUT ON
Set up a spool file to receive your output for submission. I would suggest c:CS4210wa3spool.txt .
DECLARE variables for Department_ID, Employee_ID, First_Name, Last_Name, and Salary
In the BEGIN block add a select statement to read a record into the declared variables from HR.EMPLOYEES for an Employee_ID to be determined at run time
Add DBMS_OUTPUT lines to print each variable for the select record
Add a EXCEPTION block to report when no data is found
Compile and run the procedure for the Employee, 114
Close the spool file
Explanation / Answer
SET ECHO ON
SET SERVEROUTPUT ON FORMAT WRAPPED
SET TERMOUT OFF
DBMS_OUTPUT.ENABLE(1000000);
SPOOL c:CS4210wa3spool.txt
create or replace PROCEDURE get_employee_details
(
p_employee_id NUMBER DEFAULT -1
)
AS
-- You need to query the values you're showing into variables. The
-- variables can have the same name as the column names. Oracle won't
-- be confused by this, but I usually am - that's why I have the "p_"
-- prefix for the variable names here. Finally, when declaring the
-- variable's type, you can reference table.column%TYPE to use the
-- type of an existing column.
p_Department_ID Employee.Department_ID%TYPE;
p_First_Name Employee.First_Name%TYPE;
Last_Name Employee.Last_Name%TYPE;
Salary Employee.Salary%TYPE;
BEGIN
-- Just SELECT away, returning column values into the variables. If
-- the employee ID isn't found, Oracle will throw and you can pick
-- up the pieces in the EXCEPTION block below.
SELECT Department_ID, First_Name, Last_Name, Salary
INTO p_Department_ID, p_First_Name, p_Last_Name, p_Salary
FROM Employee
WHERE Employee_ID = p_employee_id;
-- The query above found one (and only one)
-- row, and therefore it put values into the variables.
-- Print out the variables.
-- Also note there wasn't a p_employee_id variable defined, because
-- you can use your parameter value (p_employee_id) for that.
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id);
DBMS_OUTPUT.PUT_LINE('Department_ID: ' || p_Department_ID);
DBMS_OUTPUT.PUT_LINE('First Name: ' || p_First_Name);
DBMS_OUTPUT.PUT_LINE('Last_Name: ' || p_Last_Name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || p_Salary);
EXCEPTION
-- If the query didn't find a row you'll end up here. In this case
-- there's no need for any type of fancy exception handling;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Number : ' || p_employee_id || ' not found.');
END;
SPOOL OFF