exception in user defined functions and two select query in one user defined function-CodePudding

i actually only want to throw an exception if there is no rating for the tutor. so i thought i would count how many ratings he has received so that i can also output how many ratings the tutor has received and if it is 0 or only 1 rating is, there will be thrown an exception that the average cannot be calculated.

I cant output both, it outputs the average in both lines i also can't get the exception to throw out, is there maybe another method to do it

CREATE OR REPLACE FUNCTION averageRating (tutorrating NUMBER)
RETURN NUMBER
IS
countRating NUMBER;
averages NUMBER;
BEGIN 
SELECT AVG(NumberStars) INTO averages 
FROM Rating 
WHERE Tutor_ID  = tutorrating ;
SELECT count(customer_ID) INTO countRating
FROM Rating 
WHERE Tutor_ID  = tutorrating ;
Return averages;
Return countRating;
---EXCEPTION too few data
END averageRating;


DECLARE
averages number;
countRating NUMBER;
BEGIN
averages := averageRating(1);
countRating := averageRating(1);
dbms_output.put_line('The averageRating is:' || averages);
dbms_output.put_line('The number of ratings is:' || countRating);
END;

CodePudding user response：

The output you want from your function, is not the expected behavior of the Oracle function. Though you can create one like below -

CREATE OR REPLACE FUNCTION averageRating (tutorrating        IN  NUMBER,    -- By default parameter type is IN.
                                          total_count_Rating OUT NUMBER)    -- Since you need to return count_rating along with average and function can return only 1 value, 
                                                                            -- So you need 1 additional Out parameter for total_count_rating.
RETURN NUMBER
IS
    countRating    NUMBER;
    averages       NUMBER;
    too_few_data   EXCEPTION;   -- Declared EXCEPTION for checking too_few_count.
BEGIN
     SELECT AVG(NumberStars), count(customer_ID)
       INTO averages, countRating
       FROM Rating 
      WHERE Tutor_ID  = tutorrating;

     IF countRating <= 1 THEN
        RAISE too_few_data;
     END IF;

     total_count_Rating := countRating;
     
Return averages;

EXCEPTION
         WHEN too_few_data THEN
              DBMS_OUTPUT.PUT_LINE('Rating is too few.');
              total_count_Rating := 0;    -- When there is too_few_Rating, Only then you nned to return total_count_Rating as 0.
              RETURN 0;                   -- Return 0 will ensure to retun average as 0 when there is too_few_data to calculate the average.
END averageRating;

Then you can call your function as -

DECLARE
       averages number;
       countRating NUMBER;
BEGIN
     averages := averageRating(1, countRating);
     dbms_output.put_line('The averageRating is:' || averages);
     dbms_output.put_line('The number of ratings is:' || countRating);
END;

Demo.