Help with PL SQL function

Question

Hello!

I am developing a function that receives as a parameter the customer and the value and checks if the customer has credit limit to effect a sale, but when calling the function is not returning correctly the result.

    CREATE OR REPLACE FUNCTION F_LIMITE_CREDITO(
    P_NOME IN TB_CLIENTES.NOME%TYPE
) 
RETURN NUMBER
IS
    P_LIMITE NUMBER;
BEGIN

    SELECT LIMITE INTO P_LIMITE
    FROM TB_CLIENTES
    WHERE NOME = P_NOME;

    RETURN P_LIMITE;
END F_LIMITE_CREDITO;
/

DECLARE
C_NOME VARCHAR2 := TB_CLIENTES.NOME;
BEGIN
    IF F_LIMITE_CREDITO (C_NOME) < 10 THEN
        dbms_output.put_line("LIMITE INDISPONIVEL");
    END IF;
END;
/

I would like your help because I’m starting in PL.

Answer 1

I recommend you use CLIENT_ID as a function parameter, not NAME, because if there are two people with the same name your function will give error (SELECT INTO can only return one line).

You also have to place an EXCEPTION for the case where SELECT does not find the customer.

For your code as it is today, you missed passing a valid name to filter, for example:

DECLARE
    C_NOME TB_CLIENTES.NOME%TYPE := 'Mario';
BEGIN
    IF F_LIMITE_CREDITO (C_NOME) < 10 THEN
        dbms_output.put_line('LIMITE INDISPONIVEL');
    END IF;
END;
/

And you can also do so right on SELECT:

SELECT NOME,
           CASE WHEN F_LIMITE_CREDITO(NOME) < 10 THEN 'LIMITE INDISPONIVEL' 
                ELSE 'APROVADO'
           END AS SITUACAO_LIMITE
    FROM TB_CLIENTES;

Well, I would rewrite your function like this: [I used -1 for error, but you could replace it with RAISE_APPLICATION_ERROR (documentation at this link to handle the error in the function itself)]:

CREATE OR REPLACE FUNCTION F_LIMITE_CREDITO
    ( P_ID IN TB_CLIENTES.CLIENTE_ID%TYPE ) 
RETURN NUMBER IS
     P_LIMITE NUMBER;
BEGIN

    IF P_ID IS NULL
    THEN
        RETURN -1;
    END IF;

    SELECT LIMITE INTO P_LIMITE
    FROM TB_CLIENTES
    WHERE CLIENTE_ID = P_ID;

    RETURN P_LIMITE;

    EXCEPTION WHEN NO_DATA_FOUND
    THEN
          RETURN -1;

END F_LIMITE_CREDITO;
/

To call:

SELECT NOME,
       CASE WHEN F_LIMITE_CREDITO(CLIENTE_ID) = -1 THEN 'ERRO'
            WHEN F_LIMITE_CREDITO(CLIENTE_ID) < 10 THEN 'LIMITE INDISPONIVEL' 
            ELSE 'APROVADO'
       END AS SITUACAO_LIMITE
FROM TB_CLIENTES;