甲骨文半圆 [英] Oracle ROUND HALF EVEN

问题描述

是否有任何Oracle函数可以执行半个回合?

Is there any Oracle function to perform the ROUND HALF EVEN?

我发现了这篇帖子 MySQL绕了一半，但是我无法使其在Oracle中工作

I've found this post MySQL round half however I couldn't make it work in Oracle

CREATE FUNCTION roundHalfEven (numberToRound IN NUMBER, roundPrecision IN NUMBER) 
   RETURN NUMBER 
   IS roundedNumber NUMBER;
   BEGIN 
      DECLARE digitEvenOdd NUMBER;
      DECLARE digitPosition NUMBER;
      DECLARE digitToRound NUMBER;
      DECLARE roundedNumber DECIMAL(20,6) DEFAULT 0;

      SET digitPosition = INSTR(numberToRound, '.');

      IF (roundingPrecision < 1) THEN
        SET digitPosition = digitPosition + roundingPrecision;
      ELSE
        SET digitPosition = digitPosition + roundingPrecision + 1;
      END IF;

      IF (digitPosition > 0 AND digitPosition <= CHAR_LENGTH(numberToRound)) THEN
        SET digitToRound = CAST(SUBSTR(numberToRound, digitPosition, 1) AS UNSIGNED);
        SET digitPosition = digitPosition - 1;

        IF (digitPosition > 0 AND digitPosition <= CHAR_LENGTH(numberToRound)) THEN
            SET digitEvenOdd = CAST(SUBSTR(numberToRound, digitPosition, 1) AS UNSIGNED);
        END IF;
      END IF;

      IF (digitToRound > -1) THEN
        IF (digitToRound >= 5 AND digitEvenOdd IN (1,3,5,7,9)) THEN
          SET roundedNumber = ROUND(numberToRound, roundingPrecision);
        ELSE
          SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
        END IF;
      ELSE IF (roundingPrecision > 0) THEN
        SET roundedNumber = numberToRound;
      END IF;

      RETURN(roundedNumber); 
    END;
/

推荐答案

SQL提琴

Oracle 11g R2架构设置:

CREATE TABLE test_data( value ) AS
SELECT (LEVEL -11)/2 FROM DUAL CONNECT BY LEVEL <= 21;

查询1 :

SELECT value,
       ROUND( value ),
       CASE 
       WHEN MOD( ABS( value ), 2 ) = 0.5
       THEN TRUNC( value )
       ELSE ROUND( value )
       END AS round_half_even
FROM   test_data

结果 :

Results:

| VALUE | ROUND(VALUE) | ROUND_HALF_EVEN |
|-------|--------------|-----------------|
|    -5 |           -5 |              -5 |
|  -4.5 |           -5 |              -4 |
|    -4 |           -4 |              -4 |
|  -3.5 |           -4 |              -4 |
|    -3 |           -3 |              -3 |
|  -2.5 |           -3 |              -2 |
|    -2 |           -2 |              -2 |
|  -1.5 |           -2 |              -2 |
|    -1 |           -1 |              -1 |
|  -0.5 |           -1 |               0 |
|     0 |            0 |               0 |
|   0.5 |            1 |               0 |
|     1 |            1 |               1 |
|   1.5 |            2 |               2 |
|     2 |            2 |               2 |
|   2.5 |            3 |               2 |
|     3 |            3 |               3 |
|   3.5 |            4 |               4 |
|     4 |            4 |               4 |
|   4.5 |            5 |               4 |
|     5 |            5 |               5 |

或作为功能:

SQL小提琴

Oracle 11g R2架构设置:

CREATE TABLE test_data( value ) AS
SELECT (LEVEL -11)/20 FROM DUAL CONNECT BY LEVEL <= 21
/

CREATE FUNCTION round_half_even(
  value NUMBER,
  prec  INTEGER DEFAULT 0
) RETURN NUMBER
IS
  whole NUMBER := POWER( 10, -prec );
BEGIN
  RETURN CASE
         WHEN ABS( MOD( value, 2*whole ) ) = 0.5*whole
         THEN TRUNC( value, prec )
         ELSE ROUND( value, prec )
         END;
END;
/

查询1 :

SELECT value,
       ROUND( value , 1),
       round_half_even( value, 1 )
FROM   test_data

结果 :

Results:

| VALUE | ROUND(VALUE,1) | ROUND_HALF_EVEN(VALUE,1) |
|-------|----------------|--------------------------|
|  -0.5 |           -0.5 |                     -0.5 |
| -0.45 |           -0.5 |                     -0.4 |
|  -0.4 |           -0.4 |                     -0.4 |
| -0.35 |           -0.4 |                     -0.4 |
|  -0.3 |           -0.3 |                     -0.3 |
| -0.25 |           -0.3 |                     -0.2 |
|  -0.2 |           -0.2 |                     -0.2 |
| -0.15 |           -0.2 |                     -0.2 |
|  -0.1 |           -0.1 |                     -0.1 |
| -0.05 |           -0.1 |                        0 |
|     0 |              0 |                        0 |
|  0.05 |            0.1 |                        0 |
|   0.1 |            0.1 |                      0.1 |
|  0.15 |            0.2 |                      0.2 |
|   0.2 |            0.2 |                      0.2 |
|  0.25 |            0.3 |                      0.2 |
|   0.3 |            0.3 |                      0.3 |
|  0.35 |            0.4 |                      0.4 |
|   0.4 |            0.4 |                      0.4 |
|  0.45 |            0.5 |                      0.4 |
|   0.5 |            0.5 |                      0.5 |

这篇关于甲骨文半圆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！