plsql procedure doesn't do what i programmed it to do

Question

my procedure is supposed to change 2 values but when i call it it shows the same values entered

code

CREATE OR REPLACE PROCEDURE commande_remise(pourcentage_rem IN Decimal,
                        c_client IN commande.code_client%type,
                        c_reg IN commande.reglement%type,
                        c_montantht IN OUT commande.montant_ht%type,
                        c_montantttc IN OUT commande.montant_ttc%type)
IS
c_ref commande.ref_commande%type;
BEGIN
    SELECT COUNT(ref_commande) INTO c_ref FROM commande;
        c_ref := c_ref + 1;
    c_montantht := c_montantht-c_montantht*pourcentage_rem;
    c_montantttc := c_montantttc-c_montantttc*pourcentage_rem;
    INSERT INTO commande(ref_commande, code_client, reglement, montant_ht, montant_ttc) 
    VALUES(c_ref, c_client, c_reg, c_montantht, c_montantttc);
    COMMIT;
END commande_remise;
/

procedure call

    DECLARE
    c_remise DECIMAL :=0.2;
    c_code commande.code_client%type :=2;
    c_reglement commande.reglement%type :='oui';
    c_montantht commande.montant_ht%type :=3080.12;
    c_montantttc commande.montant_ttc%type :=3530.56;
    c_com commande%rowtype;
    CURSOR c_cur IS SELECT ref_commande, code_client, reglement, montant_ht, montant_ttc FROM commande;
BEGIN
    commande_remise(c_remise, c_code, c_reglement, c_montantht, c_montantttc);
OPEN c_cur;
    LOOP
    FETCH c_cur INTO c_com;
        exit when c_cur%notfound;
        dbms_output.put_line(c_com.ref_commande || ' ' || c_com.code_client || ' ' || c_com.reglement || ' ' || c_com.montant_ht || ' ' || c_com.montant_ttc);
     END LOOP;
    CLOSE c_cur;
END;
/

the values are c_montantht and c_montantttc

the result is the third:, please help.

Answer 1

Works for me (I'll tell you a secret later).

Table first:

SQL> create table commande
  2    (ref_commande  number,
  3     code_client   number,
  4     reglement     varchar2(10),
  5     montant_ht    number,
  6     montant_ttc   number);

Table created.

SQL>

Procedure:

SQL> CREATE OR REPLACE PROCEDURE commande_remise
  2    (pourcentage_rem IN NUMBER,
  3     c_client        IN commande.code_client%type,
  4     c_reg           IN commande.reglement%type,
  5     c_montantht     IN OUT commande.montant_ht%type,
  6     c_montantttc    IN OUT commande.montant_ttc%type)
  7  IS
  8    c_ref commande.ref_commande%type;
  9  BEGIN
 10      SELECT COUNT(ref_commande) INTO c_ref FROM commande;
 11      c_ref := c_ref + 1;
 12      dbms_output.put_line('pourcentage_rem = ' || pourcentage_rem);
 13      c_montantht  := c_montantht  - c_montantht  * pourcentage_rem;
 14      c_montantttc := c_montantttc - c_montantttc * pourcentage_rem;
 15
 16      INSERT INTO commande
 17         (ref_commande, code_client, reglement, montant_ht, montant_ttc)
 18      VALUES
 19         (c_ref, c_client, c_reg, c_montantht, c_montantttc);
 20  END commande_remise;
 21  /

Procedure created.

SQL>

Anonymous PL/SQL block:

SQL> set serveroutput on;
SQL> DECLARE
  2      c_remise NUMBER :=0.2;
  3      c_code commande.code_client%type :=2;
  4      c_reglement commande.reglement%type :='oui';
  5      c_montantht commande.montant_ht%type :=3080.12;
  6      c_montantttc commande.montant_ttc%type :=3530.56;
  7      c_com commande%rowtype;
  8      CURSOR c_cur IS SELECT ref_commande, code_client, reglement, montant_ht, montant_ttc FROM commande;
  9  BEGIN
 10      commande_remise(c_remise, c_code, c_reglement, c_montantht, c_montantttc);
 11  OPEN c_cur;
 12      LOOP
 13      FETCH c_cur INTO c_com;
 14          exit when c_cur%notfound;
 15          dbms_output.put_line(c_com.ref_commande || ' ' || c_com.code_client
 16            || ' ' || c_com.reglement || ' ' || c_com.montant_ht
 17            || ' ' || c_com.montant_ttc);
 18       END LOOP;
 19      CLOSE c_cur;
 20  END;
 21  /
pourcentage_rem = ,2
1 2 oui 2464,096 2824,448

PL/SQL procedure successfully completed.

SQL>

Table contents:

SQL> select * From commande;

REF_COMMANDE CODE_CLIENT REGLEMENT  MONTANT_HT MONTANT_TTC
------------ ----------- ---------- ---------- -----------
           1           2 oui          2464,096    2824,448

SQL>

Looks OK, right?

---

The secret: don't use DECIMAL in

• procedure's parameter declaration: pourcentage_rem IN Decimal
• anonymous PL/SQL block's variable declaration: c_remise DECIMAL :=0.2;

Use NUMBER instead. Because, if you use DECIMAL, then procedure's line #12 displays

pourcentage_rem = 0

so - when you subtract something that is multiplied by zero, you subtract zero and get the input value itself.