PL/SQL功能的家庭作业 [英] Homework on PL/SQL FUNCTIONS

问题描述

我想知道我的答案是否正确，请帮助我，并提前致谢

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

a)创建一个函数"Display_Employee_Name_In_Uppercase"，该函数接受"Empoyees"表中的"Employee_ID"，并以大写形式返回该雇员的名字和姓氏.

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)编写一个小的PL/SQL程序来显示Employee_ID为107、200和205的雇员的姓名.

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; 

推荐答案

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.

此外，代码块"b"将不起作用.您不能只将逗号分隔的字符串传递给期望数字的函数，并期望它可以神奇地工作(我想从技术上来说可以，但是您会失望的).最简单的方法是调用该过程3次，每个值一次.如果您想进一步努力，可以创建用户-定义的类型，它是数字的集合，然后遍历这些值，每次都调用该函数.

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.

这篇关于PL/SQL功能的家庭作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！