Homework on PL/SQL FUNCTIONS [closed]

Question

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.

Closed 8 years ago.

I would like to know if my answers is correct, please help me, and thanks in advance

a) Create a function ‘Display_Employee_Name_In_Uppercase’ that accepts the ‘Employee_ID’ from the ‘Empoyees’ table and returns the first and the last name of the employee in uppercase.

CREATE OR REPLACE FUNCTION DISPLAY_EMPLOYEE_NAME
(EMP_ID IN NUMBER) RETURN VARCHAR2
IS
EMPNAME VARCHAR(25);
BEGIN
SELECT FNAME ||' '|| LNAME INTO EMP_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID = EMP_ID;
RETURN UPPER(EMPNAME);
EXCEPTION 
WHEN OTHERS THEN NULL;
END DISPLAY_EMPLOYEE_NAME;

b) Write a small PL/SQL program to display the names of the employees whose Employee_IDs are 107, 200 and 205.

SET SERVEROUTPUT ON;
DECLARE 
EMP_ID VARCHAR2(25);
entEMPNAME VARCHAR2(25);
BEGIN 
EMP_ID :=107,200,205;
EMPNAME :=DISPLAY_EMPLOYEE_NAME(EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME: '||EMPNAME);
END;

as the first answer is not correct, I did this code and it's work but it is too long, can it be shorter?

and sorry I did not post it in new page I don't know how to post the answer vary well.

SET SERVEROUTPUT ON;
DECLARE 
    EMP_ID VARCHAR2(25);
    EMP_ID2 VARCHAR2(25);
    EMP_ID3 VARCHAR2(25);
    EMPNAME VARCHAR2(25);
    EMPNAME2 VARCHAR2(25);
    EMPNAME3 VARCHAR2(25);
BEGIN 
    EMP_ID :='107';
    EMP_ID2 :='200';
    EMP_ID3 :='205';
    EMPNAME :=DISPLAY_EMPLOYEE_NAME(EMP_ID);
    EMPNAME2 :=DISPLAY_EMPLOYEE_NAME(EMP_ID2);
    EMPNAME3 :=DISPLAY_EMPLOYEE_NAME(EMP_ID3);
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME: '|| EMPNAME || ' ' || EMPNAME2 ||' ' || EMPNAME3);
END; 

Answer 1

EXCEPTION 
WHEN OTHERS THEN NULL;

This is almost always wrong (and certainly is in this case). You are stating that your function could fail entirely and you don't care. The best option is to log the error somewhere and then re-raise it. If you don't need to log the error, you should just leave the error-handling section out altogether and let the error percolate up to the calling layer.

In addition, code block "b" won't work. You can't just pass a comma-separated string to a function that's expecting a number and expect it to magically work (I suppose that, technically, you can, but you'll be disappointed). The simplest way to do this would be to call the procedure three times, once with each value. If you wanted to go the extra mile, you could create a user-defined type that is a collection of numbers, then loop through those values, calling the function each time.

Answer 2

Since your requirements says

...that accepts the ‘Employee_ID’ from the ‘Empoyees’

I'd rather anchor input parameter :

CREATE OR REPLACE FUNCTION DISPLAY_EMPLOYEE_NAME 
(EMP_ID IN Empoyees.Employee_ID%type)  
....