PL/SQL: can't find the error in this function-CodePudding

I'm a student and I have this exercise: we have to write a function with 2 parameters, account number and withdrawal, and return the new balance of a bank account only if the account balance - withdrawal > Flow threshold

This is my code:

SET
  serveroutput ON CREATE
  OR REPLACE FONCTION Retrait (f_numcomp IN VARCHAR2, f_montant NUMBER(38, 3)) RETURN NUMBER(38, 3) AS v_compte compte % ROWTYPE;
v_solde compte.Solde % TYPE;
BEGIN
SELECT
  * INTO v_compte
FROM
  compte
WHERE
  f_numcomp = compte.NUMEROCOMPTE;
IF (v_compte.Solde - f_montant) > v_compte.SeuilDebit
/*and compte.Etat != 'desactiver'*/
THEN v_solde := v_compte.Solde - f_montant;
UPDATE
  compte
SET
  Solde = Solde - f_montant
WHERE
  f_numcomp = compte.NumeroCompte;
ELSE dbms_output.put_line('solde insufusant!');
END IF;
RETURN(v_solde);
END Retrait;
/

This is what I get:

Rapport d'erreur -

ORA-06550: Ligne 9, colonne 16 :
PLS-00103: Symbole "(" rencontré à la place d'un des symboles suivants :

. ;
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

I'm new here; I read some articles here but still didn't find the error

CodePudding user response：

FONCTION must be FUNCTION, but that was obviously just a typo in the request here, because the error you got is something else.

In the function declaration Oracle wants types without precision. I.e. NUMBER instead of NUMBER(38, 3).

CREATE OR REPLACE FUNCTION Retrait (f_numcomp IN VARCHAR2, f_montant NUMBER)
  RETURN NUMBER
AS
  v_compte compte%ROWTYPE;
  v_solde  compte.Solde%TYPE;
BEGIN
  SELECT * INTO v_compte
  FROM compte
  WHERE f_numcomp = compte.NUMEROCOMPTE;

  ...

CodePudding user response：

You should be able to simplify the function to use a single UPDATE statement with a RETURNING clause (rather than SELECT and then UPDATE):

CREATE FUNCTION Retrait (
  f_numcomp IN COMPTE.NUMEROCOMPTE%TYPE,
  f_montant IN COMPTE.SOLDE%TYPE
) RETURN COMPTE.SOLDE%TYPE
AS
  v_solde COMPTE.SOLDE%TYPE;
BEGIN
  UPDATE compte
  SET    Solde = Solde - f_montant
  WHERE  f_numcomp = NumeroCompte
  AND    solde - f_montant > SeuilDebit
  RETURNING solde INTO v_solde;

  IF SQL%ROWCOUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('solde insufusant!');
  END IF;

  RETURN v_solde;
END Retrait;
/

However, it is not usual to have DML statements in a function; you would normally use a PROCEDURE and have an OUT parameter to return the value.

fiddle