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

一个写着存储过程的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 DATABEGIN    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 DATABEGIN    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 // 
 
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 ; 

 

查看更多 下一篇