User-Defined Functions «Prev  Next»

Returning Functions Syntax in Oracle – Quiz

Each question is worth one point. Select the best answer or answers for each question.
 
1. You wish to create a function named FIND_LOWEST that returns the lowest insurance rate (in dollars and cents) for a customer, given the customer’s home state. Which code snippet would be valid for the CREATE … AS portion of your function?
Please select the best answer.
  A. CREATE FIND_LOWEST (CUSTOMER_STATE IN VARCHAR2) RETURN NUMBER AS
  B. CREATE FUNCTION FIND_LOWEST (HOMESTATE IN VARCHAR2) RETURN NUMBER(10,2) AS
  C. CREATE FUNCTION FIND_LOWEST (STATE_NAME IN VARCHAR2) RETURN NUMBER AS
  D. CREATE FUNCTION FIND_LOWEST (STATE_NAME IN VARCHAR2) RETURN RATE_NUMBER AS

2. Examine the following PL/SQL function and select all statements that are true.
Please select all the correct answers. 

CREATE FUNCTION calc_discount (
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
) RETURN NUMBER AS
    v_discount NUMBER;
BEGIN
    SELECT discount_rate
    INTO   v_discount
    FROM   customer_discounts
    WHERE  customer_id = p_customer_id;

    RETURN p_amount * v_discount;
END;
  A. This function has two parameters.
  B. This function returns a number.
  C. This function uses an implicit cursor.
  D. This function declares one local variable.

3. Which of the following scenarios is appropriate for a function that returns a Boolean value?
Please select the best answer.
  A. You need a function that can be used in the WHERE clause of an SQL query to determine whether a person has a valid driver’s license.
  B. You want to create a function that determines whether an insurance claim is within your company’s guidelines.
  C. You create a function that calculates the telephone rate for a phone call based on date, time, and the customer’s long-distance plan.
  D. You need a function that returns the due date of a customer’s invoice.