一个写着存储过程的sql文件导入出错解决思路-MySQL教程-爱易网页

一个写着存储过程的sql文件导入出错解决思路

日期：2014-05-16　浏览次数：21005 次

一个写着存储过程的sql文件导入出错
一个写着函数以及存储过程的sql文件把里面的内容拿出来的时候执行是没有问题的
利用 source 导入的时候会报错误为什么？？？

SQL code


use ss_l;
drop  FUNCTION  if exists `fun_get_diff`;
CREATE FUNCTION `fun_get_diff`(rid INT ,time datetime) RETURNS int(11)
    READS SQL DATA
BEGIN
    DECLARE  cost_tot  INT ;
    DECLARE  exch_tot  INT ;
    DECLARE  diff  INT;
    SELECT  IFNULL(SUM(cost_money),0) INTO cost_tot FROM  role_money_cost  
    WHERE  role_id =  rid  AND  cost_time  BETWEEN '0000-00-00 00:00:00'  AND time ;
        
    SELECT IFNULL(SUM(game_money),0)S  INTO exch_tot FROM  role_money_exch
    WHERE  role_id  = rid  AND  exch_time  BETWEEN '0000-00-00 00:00:00'  AND time ;
    
    SET diff =  exch_tot - cost_tot ; 

    RETURN diff ;
END;

错误如下：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 4
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'DECLA
RE cost_tot INTEGER' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'DECLA
RE exch_tot INTEGER' at line 1
ERROR 1327 (42000): Undeclared variable: cost_tot
ERROR 1327 (42000): Undeclared variable: exch_tot
ERROR 1193 (HY000): Unknown system variable 'diff'
ERROR 1193 (HY000): Unknown system variable 'diff'
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'RETUR
N diff' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'END'
at line 1

------解决方案--------------------
直接 mysql -uroot -pxxx < xxx.sql
------解决方案--------------------
加上 demimiter 行不？

SQL code

delimiter //
use ss_l;
drop  FUNCTION  if exists `fun_get_diff`;
CREATE FUNCTION `fun_get_diff`(rid INT ,time datetime) RETURNS int(11)
    READS SQL DATA
BEGIN
    DECLARE  cost_tot  INT ;
    DECLARE  exch_tot  INT ;
    DECLARE  diff  INT;
    SELECT  IFNULL(SUM(cost_money),0) INTO cost_tot FROM  role_money_cost  
    WHERE  role_id =  rid  AND  cost_time  BETWEEN '0000-00-00 00:00:00'  AND time ;
        
    SELECT IFNULL(SUM(game_money),0)S  INTO exch_tot FROM  role_money_exch
    WHERE  role_id  = rid  AND  exch_time  BETWEEN '0000-00-00 00:00:00'  AND time ;
    
    SET diff =  exch_tot - cost_tot ; 

    RETURN diff ;
END;
delimiter ;

------解决方案--------------------
use ss_l;
drop  FUNCTION  if exists `fun_get_diff`;
delimiter $$
CREATE FUNCTION `fun_get_diff`(rid INT ,time datetime) RETURNS int(11)
   READS SQL DATA
BEGIN
   DECLARE  cost_tot  INT ;
   DECLARE  exch_tot  INT ;
   DECLARE  diff  INT;
   SELECT  IFNULL(SUM(cost_money),0) INTO cost_tot FROM  role_money_cost 
   WHERE  role_id =  rid  AND  cost_time  BETWEEN '0000-00-00 00:00:00'  AND time ;
      
   SELECT IFNULL(SUM(game_money),0)S  INTO exch_tot FROM  role_money_exch
   WHERE  role_id  = rid  AND  exch_time  BETWEEN '0000-00-00 00:00:00'  AND time ;
  
   SET diff =  exch_tot - cost_tot ;  

   RETURN diff ;
END$$
delimiter ;

------解决方案--------------------
use ss_l;
drop  FUNCTION  if exists `fun_get_diff`;
delimiter //

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

一个写着存储过程的sql文件导入出错解决思路

相关资料更多>

推荐阅读更多>

一个写着存储过程的sql文件 导入出错解决思路

相关资料更多>

推荐阅读更多>

一个写着存储过程的sql文件导入出错解决思路