CODEWITHSUNDEEP
oracle-databaseplsql
jaouadi
jaouadi

Reputation: 5

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

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

Upvotes: 0

Views: 63

Answers (2)

MT0
MT0

Reputation: 167774

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

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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;

  ...

Upvotes: 2

Related Questions