Oracle 舍入问题

Question

我使用此代码将十进制值向下舍入为 25 的下一个倍数。 即，如果值为 33.60，则应舍入为 33.50

create or replace
PROCEDURE         "TEST1" (PQUERY IN VARCHAR2) as
 prNspValue number(14,2) :='';
 p_percentage_Value number(4,2) :='';
  begin
  prNspValue:=33.60;
  dbms_output.put_line(prNspValue); 
  p_percentage_Value:=substr(prNspValue,instr(prNspValue,'.')+1,length(prNspValue));
  dbms_output.put_line(p_percentage_value); 
  p_percentage_Value:=p_percentage_Value-mod(p_percentage_Value,25);
  dbms_output.put_line(p_percentage_value);
  if(p_percentage_Value!=0)then
    prNspValue:=substr(prNspValue,1,instr(prNspValue,'.'))+p_percentage_Value/100;
  else 
  prNspValue:=substr(prNspValue,1,instr(prNspValue,'.'));
  end if;
 dbms_output.put_line(prNspValue);     
end;

，但问题是当值为 33.60 时，它会被视为 33.6。所以它四舍五入为 33.0。 我该如何更正此代码？

Answer 1

您可以直接使用round函数FLOOR(your_number*4)/4：

SQL> WITH my_data AS (
  2     SELECT 33.00 num FROM dual
  3     UNION ALL SELECT 33.10 FROM dual
  4     UNION ALL SELECT 33.20 FROM dual
  5     UNION ALL SELECT 33.30 FROM dual
  6     UNION ALL SELECT 33.40 FROM dual
  7     UNION ALL SELECT 33.50 FROM dual
  8     UNION ALL SELECT 33.60 FROM dual
  9     UNION ALL SELECT 33.70 FROM dual
 10     UNION ALL SELECT 33.80 FROM dual
 11     UNION ALL SELECT 33.90 FROM dual
 12  ) SELECT num,
 13           floor (num * 4) / 4 rounded
 14      FROM my_data;

       NUM    ROUNDED
---------- ----------
        33         33
      33,1         33
      33,2         33
      33,3      33,25
      33,4      33,25
      33,5       33,5
      33,6       33,5
      33,7       33,5
      33,8      33,75
      33,9      33,75

10 rows selected