Compare two value in Oracle Procedure

Question

i'm very noob at that, can you help me? I have to see if cash>=price and cash is taken from "portafoglio" from "studente" table and price is taken from "costo" from "corso" table but it gave me several error

CREATE OR REPLACE FUNCTION PAGA_CORSO 
(
  MAT IN NUMBER 
, COR IN NUMBER 
, DAT IN DATE 
, cash DOUBLE PRECISION
, price DOUBLE PRECISION
) RETURN BOOLEAN AS 

BEGIN
    SELECT portafoglio
    INTO cash
    FROM studente
    WHERE matricola=MAT;
    
    SELECT costo
    INTO price
    FROM corso
    WHERE codicecorso=COR;
    
    IF cash >= price THEN
            RETURN FALSE;
        ELSE
            RETURN TRUE;
    END IF;
END PAGA_CORSO;

Thanks!

Connor McDonald · Answer

You are missing OUT clauses, because you want to return values "out" of the function,

SQL> CREATE OR REPLACE FUNCTION PAGA_CORSO
  2  (
  3    MAT IN NUMBER
  4  , COR IN NUMBER
  5  , DAT IN DATE
  6  , cash DOUBLE PRECISION
  7  , price DOUBLE PRECISION
  8  ) RETURN BOOLEAN AS
  9  BEGIN
 10      SELECT sal
 11      INTO cash
 12      FROM emp
 13      WHERE empno=MAT;
 14
 15      SELECT deptno
 16      INTO price
 17      FROM dept
 18      WHERE deptno=COR;
 19
 20      IF cash >= price THEN
 21              RETURN FALSE;
 22          ELSE
 23              RETURN TRUE;
 24      END IF;
 25  END PAGA_CORSO;
 26  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION PAGA_CORSO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/5     PL/SQL: SQL Statement ignored
11/10    PLS-00403: expression 'CASH' cannot be used as an INTO-target of
         a SELECT/FETCH statement

12/5     PL/SQL: ORA-00904: : invalid identifier
15/5     PL/SQL: SQL Statement ignored
16/10    PLS-00403: expression 'PRICE' cannot be used as an INTO-target of
         a SELECT/FETCH statement

17/5     PL/SQL: ORA-00904: : invalid identifier

Now we add OUT to the definition

SQL> CREATE OR REPLACE FUNCTION PAGA_CORSO
  2  (
  3    MAT IN NUMBER
  4  , COR IN NUMBER
  5  , DAT IN DATE
  6  , cash out DOUBLE PRECISION
  7  , price out DOUBLE PRECISION
  8  ) RETURN BOOLEAN AS
  9  BEGIN
 10      SELECT sal
 11      INTO cash
 12      FROM emp
 13      WHERE empno=MAT;
 14
 15      SELECT deptno
 16      INTO price
 17      FROM dept
 18      WHERE deptno=COR;
 19
 20      IF cash >= price THEN
 21              RETURN FALSE;
 22          ELSE
 23              RETURN TRUE;
 24      END IF;
 25  END PAGA_CORSO;
 26  /

Function created.

Also, "double precision" is rare - I'd suggest you probably just want to go with NUMBER.

Compare two value in Oracle Procedure

1 Answers