I can't display the final output once the user has entered a negative number.

I tried creating a program that will ask the user for numbers continuously. If the user enters a negative number, the program will stop running and will display the total, average, number of zeroes entered, highest and lowest number. Below is my written code:

SET SERVEROUTPUT ON;

DECLARE
    n NUMBER(3);
    total NUMBER(3) := 0;
    numZeros NUMBER(3) := 0;
    average NUMBER(3);
    highest NUMBER(3) := 0;
    lowest NUMBER(3) := 0;
BEGIN
    LOOP
        n := &n;
        total := total   n;
        IF n = 0 THEN
           numZeros := numZeros   1;
        ELSIF n > highest THEN
            high := n;
        ELSIF n < lowest THEN
            low := n;
        END IF;
        EXIT WHEN n < 0;
    END LOOP;
    average := total / n;
    DBMS_OUTPUT.PUT_LINE('Total: ' || total);
    DBMS_OUTPUT.PUT_LINE('Average: ' || average);
    DBMS_OUTPUT.PUT_LINE('Number of zeros: ' || numZeros);
    DBMS_OUTPUT.PUT_LINE('Highest number: ' || highest);
    DBMS_OUTPUT.PUT_LINE('Lowest number: ' || lowest);
END;

The output throws an error saying how 'N' is not a procedure. I would appreciate some help in solving this issue.

CodePudding user response：

Change this line:

n := &n;

Remove n;. It should be:

n := &

I don't know if you made a mistake with this line:

high := n;

It should be:

highest := n;

Same with this line:

low := n;

which should be:

lowest := n;

After that, when I run the code, I get this compilation error:

ORA-06502: PL/SQL: numeric or value error: number precision too large

This is because PL/SQL has no capabilities for getting input from the user during execution of PL/SQL code. Indeed the &amp is a feature of SQL*Plus and not of PL/SQL. The loop simply keeps repeating with the first entered value (for &amp) until total becomes larger than 999. Refer to Taking user input 'n' times PL/SQL and PL/SQL: how do I prompt user input in a procedure?

CodePudding user response：

There is a fundamental issue with your code, & is a substitution variable and is evaluated by the client application (i.e. SQL*Plus, SQL Developer or another client that supports substitution variables, which not all Oracle clients do) and is effectively implemented as if a find-replace was done on the source code.

Once the client has substituted the substitution variable then the statement is sent to the database where the PL/SQL engine will parse it. The PL/SQL engine never sees the substitution variable (because it is processed by the client application) and even if it did it would not understand how to handle it.

This means that you will be prompted for the replacement ONCE (and only once) and then that value will be used in every loop iteration so the loop will either never exit, if you enter a positive number first, or exit immediately, if you enter a negative number first.

If you want to enter a list of values then you need to enter them all at once:

SET SERVEROUTPUT ON;

DECLARE
  items    SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(0,1,2,3,4,5);

  total    NUMBER(3,0) := 0;
  numZeros NUMBER(3,0) := 0;
  average  NUMBER(5,2) := NULL;
  highest  NUMBER(3,0) := NULL;
  lowest   NUMBER(3,0) := NULL;
BEGIN
  FOR i IN 1 .. items.COUNT LOOP
    total := total   items(i);
    IF items(i) = 0 THEN
      numZeros := numZeros   1;
    END IF;
    IF highest IS NULL OR items(i) > highest THEN
      highest := items(i);
    END IF;
    IF lowest IS NULL OR items(i) < lowest THEN
      lowest := items(i);
    END IF;
  END LOOP;
  IF items.COUNT > 0 THEN
    average := total / items.COUNT;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Total: ' || total);
  DBMS_OUTPUT.PUT_LINE('Average: ' || average);
  DBMS_OUTPUT.PUT_LINE('Number of zeros: ' || numZeros);
  DBMS_OUTPUT.PUT_LINE('Highest number: ' || highest);
  DBMS_OUTPUT.PUT_LINE('Lowest number: ' || lowest);
END;
/

or, using SQL:

DECLARE
  items    SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(0,1,2,3,4,5);

  total    NUMBER(3,0) := 0;
  numZeros NUMBER(3,0) := 0;
  average  NUMBER(5,2) := NULL;
  highest  NUMBER(3,0) := NULL;
  lowest   NUMBER(3,0) := NULL;
BEGIN
  SELECT COALESCE(SUM(column_value),0),
         COUNT(CASE column_value WHEN 0 THEN 1 END),
         AVG(column_value),
         MAX(column_value),
         MIN(column_value)
  INTO   total,
         numZeros,
         average,
         highest,
         lowest
  FROM   TABLE(items);

  DBMS_OUTPUT.PUT_LINE('Total: ' || total);
  DBMS_OUTPUT.PUT_LINE('Average: ' || average);
  DBMS_OUTPUT.PUT_LINE('Number of zeros: ' || numZeros);
  DBMS_OUTPUT.PUT_LINE('Highest number: ' || highest);
  DBMS_OUTPUT.PUT_LINE('Lowest number: ' || lowest);
END;
/

fiddle

If you want to take user input then you can replace the initial lines with:

SET SERVEROUTPUT ON;

ACCEPT number_list CHAR PROMPT 'Enter a comma-delimited list of numbers:'

DECLARE
  items    SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(&&number_list);