CODEWITHSUNDEEP
sqloracle-databaseplsql
Safwen Soker
Safwen Soker

Reputation: 35

PL/SQL encountered the symbol "CURSOR"

I have this anonymous block that creates a function and a cursor in the declaration part and uses them :

accept idarticle prompt 'Donner lid article : '
declare
FUNCTION cal_approv(code_art article.idart%type)
    RETURN number IS qte_app number;
        qtot number;
        qtestk number;
        qtemin number;
    begin
        select sum(qte_com) into qtot from lig_com where idart=code_art;
        select qtestk into qtestk from article where idart=code_art;
        select qtemin into qtemin from article where idart=code_art;
        if (qtot/qtestk)>(2*qtemin) then
            qte_app := (qtot-qtestk)*1.5;
        else    
            qte_app := qtot*1.2;
        end if;
        return qte_app;
    end;
    CURSOR arts is select idart, desart, pu from article where qtestk <= qtemin;
    ida number;
    da number;
    qa number;
    pa number;
begin
    for art in arts loop
        qa :=  cal_approv(art.idart);
        pa := art.pu*cal_approv(art.idart);
        exit when(arts%notfound);   
        insert into resultat values

(art.idart,art.desart, qa,pa);
    end loop;
end;
/

select * from resultat;

I tried to create a function on its own in a CREATE OR REPLACE block, and it worked of me, but I have to delcare it within the declaration block alongside the cursor, so the problem arises with cursor not the function. Anyhelp is appreciated!

Upvotes: 0

Views: 495

Answers (2)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

Let me summarize it for you. When you start defining your subprograms you can not declare anything after that. That means when you started defining

FUNCTION cal_approv(code_art article.idart%type)
    ....
    end;

you can not declare anything after that

CURSOR arts is select idart, desart, pu from article where qtestk <= qtemin;
    ida number;
    da number;
    qa number;
    pa number;

Just declare variables and cursor before your subprogram.

And as mentioned by @littlefoot, you are using a cursor for loop. Cursor for loop implicitly opens, fetches and closes the records for you. you don't need to write exit condition.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143063

Huh, you declared stuff where you shouldn't, used stuff where you shouldn't, declared variables you never used. I'd suggest you to use a cursor FOR loop (easier to maintain, you don't have to worry when to exit the loop).

Such a code compiles; see if it helps.

SQL> DECLARE
  2    qa  NUMBER;
  3    pa  NUMBER;
  4
  5    FUNCTION cal_approv(
  6      code_art article.idart%TYPE
  7    )RETURN NUMBER IS
  8      qte_app  NUMBER;
  9      qtot     NUMBER;
 10      qtestk   NUMBER;
 11      qtemin   NUMBER;
 12    BEGIN
 13      SELECT SUM(qte_com)
 14      INTO qtot
 15      FROM lig_com
 16      WHERE idart = code_art;
 17
 18      SELECT qtestk
 19      INTO qtestk
 20      FROM article
 21      WHERE idart = code_art;
 22
 23      SELECT qtemin
 24      INTO qtemin
 25      FROM article
 26      WHERE idart = code_art;
 27
 28      IF(qtot / qtestk)>(2 * qtemin)THEN
 29        qte_app :=(qtot - qtestk)* 1.5;
 30      ELSE
 31        qte_app := qtot * 1.2;
 32      END IF;
 33
 34      RETURN qte_app;
 35    END;
 36
 37  BEGIN
 38    FOR art IN(SELECT idart,
 39                      desart,
 40                      pu
 41               FROM article
 42               WHERE qtestk <= qtemin
 43              )LOOP
 44      qa := cal_approv(art.idart);
 45      pa := art.pu * cal_approv(art.idart);
 46      INSERT INTO resultat VALUES(
 47        art.idart,
 48        art.desart,
 49        qa,
 50        pa
 51      );
 52    END LOOP;
 53  END;
 54  /

PL/SQL procedure successfully completed.

SQL>

Declare cursor before the function:

SQL> set serveroutput on
SQL> declare
  2    cursor c1 is select * from dept;
  3
  4    function cal_approv return number is
  5    begin
  6      return 0;
  7    end cal_approv;
  8  begin
  9    for c1r in c1 loop
 10      dbms_output.put_line(c1r.dname ||': function value = ' || cal_approv);
 11    end loop;
 12  end;
 13  /
ACCOUNTING: function value = 0
RESEARCH: function value = 0
SALES: function value = 0
OPERATIONS: function value = 0

PL/SQL procedure successfully completed.

SQL>

If it were vice versa (as you did it):

SQL> declare
  2    function cal_approv return number is
  3    begin
  4      return 0;
  5    end cal_approv;
  6
  7    cursor c1 is select * from dept;
  8  begin
  9    for c1r in c1 loop
 10      dbms_output.put_line(c1r.dname ||': function value = ' || cal_approv);
 11    end loop;
 12  end;
 13  /
  cursor c1 is select * from dept;
  *
ERROR at line 7:
ORA-06550: line 7, column 3:
PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following:
begin function pragma procedure


SQL>

Upvotes: 1

Related Questions