You need to create a PL/SQL procedure that accepts a customer's code, retrieves their first name and last name, concatenates them into a full name, and then displays this full name. The procedure should handle the case where no customer is found for the given code by displaying a specific message. Which of the following PL/SQL procedures correctly implement this, including proper exception handling for NO_DATA_FOUND?

Question

BlackTom AI · Accepted Answer

SELECT ... INTO semantics: a SELECT that returns no rows raises the NO_DATA_FOUND exception; to handle that case you must include an EXCEPTION block with WHEN NO_DATA_FOUND THEN. The correct procedure therefore performs the SELECT ... INTO to get CUS_FNAME || ' ' || CUS_LNAME into a local VARCHAR2, prints it when a row is returned, and provides a WHEN NO_DATA_FOUND handler that prints the "not found" message.

Step-by-step reasoning (matching the given correct procedure):
1. Declaration: v_fullname VARCHAR2(100); — reserve space to hold the concatenated name.
2. Execution: SELECT CUS_FNAME || ' ' || CUS_LNAME INTO v_fullname FROM CUSTOMER WHERE CUS_CODE = p_cus_code;
   - If a matching row exists, Oracle assigns the concatenated first and last name to v_fullname and continues.
   - If no matching row exists, Oracle raises NO_DATA_FOUND immediately and control jumps to the EXCEPTION section.
3. Success path: DBMS_OUTPUT.PUT_LINE('Customer Full Name: ' || v_fullname); — prints the full name when the SELECT returned a row.
4. Exception path: EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Customer with code ' || p_cus_code || ' not found.'); — prints the required message when no customer is found.
5. END; — ends the procedure.

Why this is correct:
- It uses SELECT ... INTO to retrieve exactly one row and concatenates first and last name correctly with || and a space.
- It explicitly handles the NO_DATA_FOUND exception, which is required by the question to display a specific message when no customer exists.

Why the other options are incorrect:
- Option that checks IF v_fullname IS NULL after the SELECT: incorrect because if no row exists the SELECT will raise NO_DATA_FOUND before the IF is reached; that IF cannot catch the absence of rows without an exception handler.
- Option lacking any EXCEPTION block: incorrect because NO_DATA_FOUND would propagate as an unhandled exception instead of displaying the required message.
- Option using WHEN OTHERS THEN with a generic message: while WHEN OTHERS would catch NO_DATA_FOUND indirectly, it does not specifically handle NO_DATA_FOUND as requested and returns a non-specific "An error occurred." message rather than the required "customer with code ... not found."

Conclusion: The provided procedure (SELECT INTO with EXCEPTION WHEN NO_DATA_FOUND and DBMS_OUTPUT messages) is correct, and this matches the authoritative answer.

类似问题

Consider the following PL/SQL procedure designed to retrieve customer information and handle potential errors: If get_customer_info(0) is executed, what will be the output?

When evaluating a new project, firms should include in the projected cash flows all of the following EXCEPT:

Provide the best rationale for what's wrong with supply chains today?

What is Blockchain and how is it relevant to supply chain management?

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单