代码之家 › 专栏 › 技术社区 › John Perez

MySQL WorkBench-将自己的函数应用于存储表的列

function mysql

John Perez · 技术社区 · 7 年前

我正在使用MySQL Workbench 57。

id |X_LV03  |Y_LV03 |longitud |latitud
1  |790000  |125560 |NULL     |NULL
2  |550000  |235400 |NULL     |NULL
3  |485000  |80000  |NULL     |NULL
4  |750000  |80000  |NULL     |NULL

我需要把“XúLV03”和“YúLV03”坐标自动转换成地理坐标。我需要在将数据上传到我的数据库之后,相应表中的坐标会发生变化,每次将新行上传到表中时都会运行一个函数。

为了我的愿望我做了这个:

Delimiter // 
CREATE FUNCTION X_LV03ToLong (lon decimal)
returns decimal
BEGIN 
#declare longitud decimal;
declare east int; 
declare lon  dec ;    

 # constants that will be changed
set  
lon = 2.6779094;  

# select variables from the table
 SELECT X_LV03 into east FROM am.locdum; 

#logic of function 
 # Convert origin to "civil" system, where Bern has coordinates 0,0. 
 Set east = east - 600000,                                           
 # Express distances in 1000km units. 
 east = east /1E6,                                                

 # Calculate longitude in 10000" units. 
 lon = 2.6779094,                                       
 lon = lon + 4.728982 * east,
 lon = lon + 0.791484 * east * north,
 lon = lon + 0.1306 * east * north * north,
 lon = lon - 0.0436 * east * east * east,

  # Convert longitude and latitude back in degrees. 
 lon = lon * 100 / 36;                                            

 return lon; # Return new coordinates. 

 end; //

然后我尝试将其应用到我的领域:

 call X_LV03ToLong()    Error Code: 1305. PROCEDURE am.X_LV03ToLong does not exist  0.000 sec

SELECT X_LV03ToLong(locdum.longitud)    Error Code: 1109. Unknown table 'locdum' in field list  0.000 sec

SELECT X_LV03ToLong(longitud)   Error Code: 1054. Unknown column 'longitud' in 'field list' 0.000 sec

它总是给我错误:要么函数不存在,要么表/列不存在。

提前感谢您的任何提示或帮助。