Wanted to create a function that can return records containing the number data type which are prime number But getting warning of compilation error. What is the mistake in the code.I am a beginner in pl/sql.

CREATE OR REPLACE FUNCTION isPrime (num number)
RETURN number
IS
retVal number;
BEGIN
DECLARE
prime_or_notPrime number;
counter number;
    retVal:= 1;
    prime_or_notPrime:= 1
    counter:= 2
    WHILE (counter <= num/2)  LOOP
        IF (mod(num ,counter)= 0)  THEN
            prime_or_notPrime: = 0
            EXIT;
        END IF;
        IF (prime_or_notPrime = 1 ) THEN
            retVal: = 1;
        counter: = counter   1
        END IF;
    END LOOP;
    return retVal;
END;
/

CodePudding user response：

What is the mistake in the code

• The DECLARE is invalid syntax for a function/procedure. You want to declare the variables between the IS and BEGIN keywords.
• Then you are missing a ; statement terminator after prime_or_notPrime:= 1 and counter:= 2.
• Then you have:
  • prime_or_notPrime: = 0 instead of prime_or_notPrime := 0;
  • retVal: = 1; instead of retVal := 1; and
  • counter: = counter 1 instead of counter := counter 1; (they all have a space between : and = and some are missing ; again).
• You never set retVal to anything other than 1.
• You do not handle NULL values or values lower than 2.

Fixing that (and the indentation) gives:

CREATE OR REPLACE FUNCTION isPrime (num number)
RETURN number
IS
  retVal number;
  prime_or_notPrime number;
  counter number;
BEGIN
  IF NUM IS NULL OR NUM < 2 THEN
    RETURN 0;
  END IF;

  retVal:= 1;
  prime_or_notPrime:= 1;
  counter:= 2;
  WHILE (counter <= num/2)  LOOP
    IF (mod(num ,counter)= 0)  THEN
      prime_or_notPrime := 0;
      retVal := 0;
      EXIT;
    END IF;
    IF (prime_or_notPrime = 1 ) THEN
      counter := counter   1;
    END IF;
  END LOOP;
  return retVal;
END;
/

Note: the prime_or_notprime variable is not controlling anything as, as soon as you set it to 0 you EXIT from the loop so it will never be used again. Having noted that, you can get rid of the final IF statement and just always increment the counter.

You can simplify it to:

CREATE OR REPLACE FUNCTION isPrime (
  num number
) RETURN number DETERMINISTIC
IS
BEGIN
  IF NUM IS NULL OR NUM < 2 THEN
    RETURN 0;
  END IF;

  FOR counter IN 2 .. SQRT(num)  LOOP
    IF MOD(num, counter) = 0  THEN
      RETURN 0;
    END IF;
  END LOOP;
  RETURN 1;
END;
/

You can then consider improvements. Such as starting by checking 2 as a special case and then skipping all the other even numbers.

db<>fiddle here