使用触发器填充SDO_POINT_TYPE时Oracle浮点数精度丢失问题

使用触发器从同一张表的另外两列填充SDO_POINT_TYPE或SDO_GEOMETRY列时，发现与直接插入操作相比，触发器会导致浮点数值的精度丢失。测试代码及结果如下：

DROP TABLE test;
CREATE TABLE test (LONGITUDE BINARY_DOUBLE, LATITUDE BINARY_DOUBLE, p SDO_point_type);

CREATE OR REPLACE TRIGGER TEST_TRIGGER 
BEFORE UPDATE OF LATITUDE, LONGITUDE ON TEST
FOR EACH ROW  
BEGIN
    :new.p := sdo_point_type(:new.LONGITUDE, :new.LATITUDE, NULL);
END;

INSERT INTO test(p) VALUES(NULL);
UPDATE test SET longitude=-14.19, latitude=48.33; -- 使用触发器

INSERT INTO test(p, longitude, latitude) VALUES(sdo_point_type(-14.19, 48.33, NULL), -14.19, 48.33); -- 不使用触发器
SELECT CAST(latitude AS VARCHAR2(100)) AS lat, CAST(t.p.y AS VARCHAR2(100)) AS point_lat FROM test t;

-- 查询结果：
-- LAT                      POINT_LAT
-- 4,8329999999999998E+001  48,329999999999998  -- 触发器生成
-- 4,8329999999999998E+001  48,33               -- 直接插入

根本原因 #

问题出在BINARY_DOUBLE类型的特性：它是二进制浮点类型，无法精确表示所有十进制小数（比如48.33这类值）。当你把48.33存入BINARY_DOUBLE列时，实际存储的是近似值48.329999999999998。

• 直接插入时，传入的是十进制字面量48.33，Oracle会直接将其解析为NUMBER类型的精确值（SDO_POINT_TYPE的X/Y/Z参数为NUMBER类型），所以最终得到48.33。
• 触发器中使用的是BINARY_DOUBLE列的近似值，传给SDO_POINT_TYPE构造函数后，自然保留了这个不精确的结果。

方案1：修改列类型为NUMBER（推荐） #

将LONGITUDE和LATITUDE列的类型从BINARY_DOUBLE改为NUMBER（指定合适的精度，比如NUMBER(10,6)），这样可以精确存储十进制坐标值，触发器直接使用列值构造SDO_POINT_TYPE即可得到和直接插入一致的结果：

DROP TABLE test;
CREATE TABLE test (LONGITUDE NUMBER(10,6), LATITUDE NUMBER(10,6), p SDO_point_type);

CREATE OR REPLACE TRIGGER TEST_TRIGGER 
BEFORE UPDATE OF LATITUDE, LONGITUDE ON TEST
FOR EACH ROW  
BEGIN
    :new.p := sdo_point_type(:new.LONGITUDE, :new.LATITUDE, NULL);
END;

-- 重新执行测试，会发现触发器生成的POINT_LAT也是48.33

方案2：在触发器中转换精度（若必须保留BINARY_DOUBLE） #

如果无法修改列类型，可以在触发器中将BINARY_DOUBLE值转换为精确的NUMBER类型。根据坐标精度需求，使用以下两种方式修正：

方法A：使用ROUND指定精度

如果坐标是两位小数，直接对列值取两位小数：

CREATE OR REPLACE TRIGGER TEST_TRIGGER 
BEFORE UPDATE OF LATITUDE, LONGITUDE ON TEST
FOR EACH ROW  
BEGIN
    :new.p := sdo_point_type(ROUND(:new.LONGITUDE, 2), ROUND(:new.LATITUDE, 2), NULL);
END;

方法B：通过字符串转换还原近似值

利用字符串转换将二进制浮点的近似值转回最接近的十进制表示：

CREATE OR REPLACE TRIGGER TEST_TRIGGER 
BEFORE UPDATE OF LATITUDE, LONGITUDE ON TEST
FOR EACH ROW  
BEGIN
    :new.p := sdo_point_type(
        TO_NUMBER(TO_CHAR(:new.LONGITUDE, 'FM9999999999.99')),
        TO_NUMBER(TO_CHAR(:new.LATITUDE, 'FM9999999999.99')),
        NULL
    );
END;

这样处理后，触发器生成的SDO_POINT_TYPE值就会和直接插入的结果一致，避免精度丢失。

内容的提问来源于stack exchange，提问作者Kiruahxh