Handling decimal precision, rounding, total discard, and full rounding in business systems

In business systems, different customers have different approaches to decimal processing

By setting the number of reserved decimal places, as well as the number of decimal digits after the processing method, you can flexibly meet customer requirements

Processing method Tables

ID Number (1),--Set ID

Amountdot number (4,2) DEFAULT 0.01--The amount of precision 1: Accurate to $0.1: accurate to angular 0.01: accurate to rounding number (1), --Rounding demarcation--9: whole House 0: Full into 4: four shekels 5 into

Establish an Oracle function, pass in the set ID, incoming amount, and return the processed amount

The stored procedure implementation code is as follows

Create or Replace function Get_real_number ( -The amount to be processed based on the processing method ID-calculated amounts              id in number, --Treatment Method id             n_amount in number)       --------------------------------------- -------------------------------- --  use   path    custom data precision processing.  --  founder  :andrew -----------------------------------------------------------------------  --  modified record list: (sorted by date sequence)  --  modification time      modifier       Implementation of the functional description   -9 Full House   0 full into   44 5 into    ------------------------------------------------------ The amount returned by the-----------------return number--is       V_amountdot VARCHAR2 (5);      --decimal digit character        v_keep      number ;          --Number of decimal places reserved        v_rounding  Number;       --Rounding demarcation        v_Amount  Number:=0;        --The amount returned is begin

     select  amountdot,  NVL (rounding,0) into the v_amountdot,v_rounding from   processing method table              WHERE id = sale_place_id;      V_keep: = Length (V_amountdot)-  InStr (V_amountdot, '. '); --Number of decimal places to keep      IF v_rounding = 9 then   --processing total Discard         V_amount: = Trunc (N_amount, V_keep);     End if;          IF v_rounding = 4 then     --Processing Rounding         V_amount: = Round (n_amount,v_keep);      End if;          IF v_rounding = 0 then  --processing full carry & nbsp;           v_amount: = Trunc (n_amount,v_keep);   --Decimal numbers just equal to the number of required reservations            IF V_amount <> N_amount then  --If the number after the interception is different from the original number, enter 1            V_amount: = V_amount + to_number (v_amountdot);      end if;      return v_amount;

Exception when others then return n_amount;

End Get_real_number;