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!

Post the errors. Off the top of my head "DOUBLE PRECISION" is not a data type in PL/SQL. and I'd expect that you'd want `cash` and `price` to be declared as local variables not as parameters to your procedure. — Justin Cave, Sep 06 '22 at 21:29
@JustinCave I had to check the [manual](https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-data-types.html) and some [SO questions](https://stackoverflow.com/q/332492/230471) myself as I've never seen it used, but turns out `double precision` is a synonym for `float`. I've never seen that used either, but turns out it's a synonym for `number`. The more exotic numeric types seem to become relevant when passing values to C etc. — William Robertson, Sep 07 '22 at 17:44

score 0 · Accepted Answer · answered Sep 07 '22 at 04:03

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 Answers1