如果導師沒有評分，我實際上只想拋出一個例外。所以我想我會計算他收到了多少個評分，這樣我也可以輸出導師收到了多少個評分，如果它是 0 或只有 1 個評分，則會拋出無法計算平均值的例外。
我不能同時輸出，它輸出兩行的平均值我也無法拋出例外，是否有另一種方法可以做到這一點
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;
uj5u.com熱心網友回復：
您希望函式的輸出不是 Oracle 函式的預期行為。盡管您可以像下面這樣創建一個 -
CREATE OR REPLACE FUNCTION averageRating (tutorrating IN NUMBER,
total_count_Rating OUT NUMBER)
RETURN NUMBER
IS
countRating NUMBER;
averages NUMBER;
too_few_data EXCEPTION;
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;
RETURN 0;
END averageRating;
然后你可以將你的函式稱為 -
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;
演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/472530.html
上一篇：即使沒有資料也顯示表中的資料