1 自定义sql函数
还是一定一定要注意,oracle大小写问题,不加双引号代表不区分大小写,加了双引号代表区分大小写,写sql语句的时候就很可能会出现诸如标识符无效或者调用函数失败等问题
CREATE OR REPLACE FUNCTION distance(Lung1 IN NUMBER , Lat1 IN NUMBER, Lung2 IN NUMBER, Lat2 IN NUMBER )
RETURN NUMBER
AS
earth_padius NUMBER := 6378.137;
radLat1 NUMBER := Lat1 * 3.141592625 / 180;
radLat2 NUMBER := Lat2 * 3.141592625 / 180;
a NUMBER := radLat1 - radLat2;
b NUMBER := Lung1 * 3.141592625 / 180 - Lung2 * 3.141592625 / 180;
dis NUMBER := 0;
BEGIN
-- routine body goes here, e.g.
-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
dis := 2 *
Asin(Sqrt(power(sin(a / 2), 2) +cos(radLat1) * cos(radLat2) * power(sin(b / 2), 2)));
dis := dis * earth_padius;
dis := Round(dis * 10000) / 10000;
RETURN dis;
END;