pl/sql: You have an error in SQL function syntax how to fix this error?-CodePudding

an error named PL/SQL: Statement ignored

I define a variable c_c to count how many times the loop will be work, the error happen in increase statement (c_c := c_c 1;)

create or replace function number_of_course(num_stu STUDENTS.STUNO%TYPE) 
return number is
 c_c number :=0;
 cursor c1 is SELECT stuno from reg; 
begin
for i in c1 loop
if i = num_stu then 
c_c := c_c   1 ;
end if;
end loop;
       RETURN c_c;
end number_of_course;

CodePudding user response：

It is easy to fix the error - you can't compare a cursor variable to number. But, I have no idea what is that piece of code supposed to do.

For sample tables (just to make the function compile):

SQL> SELECT * FROM students;

     STUNO
----------
         5

SQL> SELECT * FROM reg;

     STUNO
----------
         1
         3
         5

Fixed function:

SQL> CREATE OR REPLACE FUNCTION number_of_course (num_stu STUDENTS.STUNO%TYPE)
  2     RETURN NUMBER
  3  IS
  4     c_c  NUMBER := 0;
  5
  6     CURSOR c1 IS SELECT stuno FROM reg;
  7  BEGIN
  8     FOR i IN c1
  9     LOOP
 10        IF i.stuno = num_stu          --> instead of "IF i = num_stu"
 11        THEN
 12           c_c := c_c   1;
 13        END IF;
 14     END LOOP;
 15
 16     RETURN c_c;
 17  END number_of_course;
 18  /

Function created.

Does it return anything? Yes, it does. Is it what you wanted? As I said, no idea.

SQL> SELECT number_of_course (1) result_1,
  2         number_of_course (2) result_2
  3  FROM DUAL;

  RESULT_1   RESULT_2
---------- ----------
         1          0

SQL>

CodePudding user response：

Rather than using a cursor loop (which will be slower and more complicated), you can use the COUNT aggregation function:

CREATE FUNCTION number_of_course(
  num_stu STUDENTS.STUNO%TYPE
) RETURN NUMBER
IS
  c_c number;
BEGIN
  SELECT COUNT(*) INTO c_c FROM reg WHERE stuno = num_stu; 
  RETURN c_c;
END number_of_course;

If you did have a requirement to use a loop (which seems pointless) then you can filter in the cursor and count the number of rows in the cursor:

CREATE FUNCTION number_of_course(
  num_stu STUDENTS.STUNO%TYPE
) RETURN NUMBER
IS
  c_c NUMBER :=0;
  CURSOR c1 is SELECT stuno from reg WHERE stuno = num_stu; 
BEGIN
  FOR i IN c1
  LOOP
    c_c := c_c   1 ;
  END LOOP;
  RETURN c_c;
END number_of_course;
/