Write a PL/SQL program using WHILE loop for calculating the average of the numbers entered by user. Stop the entry of numbers whenever the user enters the number 0.
I have tried below code in SQL developer in sample scott user. I have tried many variations of trying to get input but after 2 inputs it throws the error.
Sorry if not understood or not posted in a proper way as it is my first post on this site.
DECLARE
num NUMBER := 0; -- number entered by the user
sum NUMBER := 0; -- sum of all entered numbers
count NUMBER := 0; -- count of entered numbers
average NUMBER := 0; -- average of entered numbers
BEGIN
WHILE num != 0 LOOP
DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
num := &input_number; -- Prompt the user to enter a number
IF num != 0 THEN
sum := sum + num; -- add the entered number to the sum
count := count + 1; -- increment the count of entered numbers
END IF;
END LOOP;
-- Calculate the average
IF count > 0 THEN
average := sum / count;
DBMS_OUTPUT.PUT_LINE('Average: ' || average);
ELSE
DBMS_OUTPUT.PUT_LINE('No numbers entered!');
END IF;
END;
After that getting below error in SQL developer.
old:DECLARE
num NUMBER := 0; -- number entered by the user
sum NUMBER := 0; -- sum of all entered numbers
count NUMBER := 0; -- count of entered numbers
average NUMBER := 0; -- average of entered numbers
BEGIN
WHILE num != 0 LOOP
DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
num := # -- Prompt the user to enter a number
IF num != 0 THEN
sum := sum + num; -- add the entered number to the sum
count := count + 1; -- increment the count of entered numbers
END IF;
END LOOP;
-- Calculate the average
IF count > 0 THEN
average := sum / count;
DBMS_OUTPUT.PUT_LINE('Average: ' || average);
ELSE
DBMS_OUTPUT.PUT_LINE('No numbers entered!');
END IF;
END;
new:DECLARE
num NUMBER := 0; -- number entered by the user
sum NUMBER := 0; -- sum of all entered numbers
count NUMBER := 0; -- count of entered numbers
average NUMBER := 0; -- average of entered numbers
BEGIN
WHILE num != 0 LOOP
DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
num := 4; -- Prompt the user to enter a number
IF num != 0 THEN
sum := sum + num; -- add the entered number to the sum
count := count + 1; -- increment the count of entered numbers
END IF;
END LOOP;
-- Calculate the average
IF count > 0 THEN
average := sum / count;
DBMS_OUTPUT.PUT_LINE('Average: ' || average);
ELSE
DBMS_OUTPUT.PUT_LINE('No numbers entered!');
END IF;
END;
Error starting at line : 1 in command -
DECLARE
num NUMBER := 0; -- number entered by the user
sum NUMBER := 0; -- sum of all entered numbers
count NUMBER := 0; -- count of entered numbers
average NUMBER := 0; -- average of entered numbers
BEGIN
WHILE num != 0 LOOP
DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
num := # -- Prompt the user to enter a number
IF num != 0 THEN
sum := sum + num; -- add the entered number to the sum
count := count + 1; -- increment the count of entered numbers
END IF;
END LOOP;
-- Calculate the average
IF count > 0 THEN
average := sum / count;
DBMS_OUTPUT.PUT_LINE('Average: ' || average);
ELSE
DBMS_OUTPUT.PUT_LINE('No numbers entered!');
END IF;
END;
Error report -
ORA-06550: line 12, column 18:
PLS-00103: Encountered the symbol "+" when expecting one of the following:
(
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
You have chosen variable names that clash with PL/SQL keywords, sum and count - those have special meanings.
If you change the names this will work, e.g. prefixing all of the variable names with l_ (for local):
DECLARE
l_num NUMBER := 0; -- number entered by the user
l_sum NUMBER := 0; -- sum of all entered numbers
l_count NUMBER := 0; -- count of entered numbers
l_average NUMBER := 0; -- average of entered numbers
BEGIN
WHILE l_num != 0 LOOP
DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
l_num := &input_number; -- Prompt the user to enter a number
IF l_num != 0 THEN
l_sum := l_sum + l_num; -- add the entered number to the sum
l_count := l_count + 1; -- increment the count of entered numbers
END IF;
END LOOP;
-- Calculate the average
IF l_count > 0 THEN
l_average := l_sum / l_count;
DBMS_OUTPUT.PUT_LINE('Average: ' || l_average);
ELSE
DBMS_OUTPUT.PUT_LINE('No numbers entered!');
END IF;
END;
/
Or at least it won't error... it won't really do anything. You're declaring l_num as:
DECLARE
l_num NUMBER := 0; -- number entered by the user
and then you loop with:
WHILE l_num != 0 LOOP
So that immediately fails the test and the loop exits without doing anything. If you initialise l_num to, say, -1 then it will go into the loop and use the number you supplied. But it will loop forever.
You can't prompt repeatedly in a PL/SQL block. PL/SQL is not interactive. You are being prompted for the substitution variable value before the PL/SQL is parsed/compiled - that's why you are asked for the value and then it errors.
So SQL Developer sees the & placeholder in the block, prompts you for the value, substitutes it - as you can see in the old/new text - and only then compiles and executes the block.
That's why in your 'new' text you can see:
new:DECLARE
num NUMBER := 0; -- number entered by the user
sum NUMBER := 0; -- sum of all entered numbers
count NUMBER := 0; -- count of entered numbers
average NUMBER := 0; -- average of entered numbers
BEGIN
WHILE num != 0 LOOP
DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
num := 4; -- Prompt the user to enter a number
...
The 4 is already set and fixed - it will not prompt you again. And as that is fixed as 4, the loop condition is never met as that is not zero, and it will loop forever, or until it runs out of resources. (Assuming you did serveroutput on to see the results, with this code you'll hit the DBMS_OPUTPUT buffer limit after it's tried to output the "Enter a number" prompt a few thousand times.)
You could do something like this but I doubt you're being asked for something that complicated. It's unclear what you're expected to do though. You could prompt for a single list of number but the language used doesn't suggest that's what's required either, and you wouldn't need to end with a zero. So I don't really have a suggestion of how to do what you've been asked for...