帮忙看下这个存储过程是否有问题？谢谢了

2013-05-29 09:47:04 131 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

phpMyAdmin设置存储过程及其问题这几天需要用到数据库，使用的是MySQL，数据库管理软件使用的是phpMyAdmin，要创建一个存储过程，就需要执行一个SQL语句，语句如下，结果报错了，报错如下： “#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for th 阅读详情

CREATE PROCEDURE HD_Insert_Every_Acount_Every_Commidity_All_Things(
Acount_Id bigint(11),
Spread_profits_sum float(11),
Market_price_profits_sum double,
Total_reward_ratio float(10),
Acount_Num int(10),
Acount_Suc_Num int(10),
Win_rate float(10),
Avg_profit_point float(11),
Avg_loss_point float(11),
Profit_loss_ratio float(11),
Annual_return float(11),
IAcount_Type int(11),
Commodity_id int(10)
)
BEGIN
declare iCount INT default 0;
IF(IAcount_Type = 0)THEN
set iCount = (select count(*) from t_account_commodity_all where account_id = Acount_Id);
IF (iCount = 0) THEN
insert into t_account_commodity_all ( account_id,total_profit_points,profit_loss_money,total_reward_ratio,trade_count,profit_trade_count,win_rate,avg_profit_point,avg_loss_point,profit_loss_ratio,annual_return,commodity_id ) values
( Acount_Id,Spread_profits_sum,Market_price_profits_sum,Total_reward_ratio,Acount_Num,Acount_Suc_Num,Win_rate,Avg_profit_point,Avg_loss_point,Profit_loss_ratio,Annual_return,Commodity_id);
ELSE
update t_account_commodity_all
set total_profit_points = Spread_profits_sum, profit_loss_money = Market_price_profits_sum, total_reward_ratio =Total_reward_ratio,trade_count =Acount_Num,
profit_trade_count = Acount_Suc_Num,win_rate = Win_rate,avg_profit_point = Avg_profit_point,avg_loss_point = Avg_loss_point,profit_loss_ratio = Profit_loss_ratio,annual_return = Annual_return
where account_id = Acount_Id and commodity_id = Commodity_id;
END IF;
END IF;
END

...全文

使用cartographer纯定位替换amcl定位经过实际使用，发现cartographer定位对于重定位的表现比amcl好。如果机器人的起始位置离原点较远，amcl很难自行重定位到正确的位置，只能手动修改初始位置。然而cartographer定位的表现则很好，在行驶一段距离或原地旋转一段时间后能够正确重定位。阅读详情

一魅

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

一魅 2013-05-29

打赏
举报

引用 4 楼 YI_MQ 的回复:

[quote=引用 3 楼 YI_MQ 的回复:] [quote=引用 2 楼 ACMAIN_CHM 的回复:]

IF(IAcount_Type = 0)THEN
	set iCount = (select count(*) from t_account_commodity_all where account_id = Acount_Id);
	
	IF (iCount = 0) THEN
		insert into t_account_commodity_all ( account_id,total_profit_points,profit_loss_money,total_reward_ratio,trade_count,profit_trade_count,win_rate,avg_profit_point,avg_loss_point,profit_loss_ratio,annual_return,commodity_id ) values
			( Acount_Id,Spread_profits_sum,Market_price_profits_sum,Total_reward_ratio,Acount_Num,Acount_Suc_Num,Win_rate,Avg_profit_point,Avg_loss_point,Profit_loss_ratio,Annual_return,Commodity_id);
	ELSE
		update t_account_commodity_all 
			set total_profit_points = Spread_profits_sum, profit_loss_money = Market_price_profits_sum, total_reward_ratio =Total_reward_ratio,trade_count =Acount_Num,
			profit_trade_count = Acount_Suc_Num,win_rate = Win_rate,avg_profit_point = Avg_profit_point,avg_loss_point = Avg_loss_point,profit_loss_ratio = Profit_loss_ratio,annual_return = Annual_return
			where account_id = Acount_Id  and commodity_id = Commodity_id;
	END IF;
END IF;

第一个生成后，执行第二个，第二个的数据会覆盖第一个，而不会生成新的一条记录你的代码不就是实现这个功能吗？IF iCount = 0 就生成，否则就 update t_accoun 覆盖

可根据条件判断，则会生成第二条的呀，因为account_id 虽然一致，但是commodity_id 这个是不同的，所以它应该生成第二条的呀，可现在它直接覆盖第一条了[/quote] 抱歉，那个条件改下：set iCount = (select count(*) from t_account_commodity_all where account_id = Acount_Id and commodity_id = Commodity_id);[/quote] 效果一样的，没有任何变化，是不是我的表设计有问题呢？

一魅 2013-05-29

打赏
举报

引用 3 楼 YI_MQ 的回复:

[quote=引用 2 楼 ACMAIN_CHM 的回复:]

IF(IAcount_Type = 0)THEN
	set iCount = (select count(*) from t_account_commodity_all where account_id = Acount_Id);
	
	IF (iCount = 0) THEN
		insert into t_account_commodity_all ( account_id,total_profit_points,profit_loss_money,total_reward_ratio,trade_count,profit_trade_count,win_rate,avg_profit_point,avg_loss_point,profit_loss_ratio,annual_return,commodity_id ) values
			( Acount_Id,Spread_profits_sum,Market_price_profits_sum,Total_reward_ratio,Acount_Num,Acount_Suc_Num,Win_rate,Avg_profit_point,Avg_loss_point,Profit_loss_ratio,Annual_return,Commodity_id);
	ELSE
		update t_account_commodity_all 
			set total_profit_points = Spread_profits_sum, profit_loss_money = Market_price_profits_sum, total_reward_ratio =Total_reward_ratio,trade_count =Acount_Num,
			profit_trade_count = Acount_Suc_Num,win_rate = Win_rate,avg_profit_point = Avg_profit_point,avg_loss_point = Avg_loss_point,profit_loss_ratio = Profit_loss_ratio,annual_return = Annual_return
			where account_id = Acount_Id  and commodity_id = Commodity_id;
	END IF;
END IF;

一魅 2013-05-29

打赏
举报

引用 2 楼 ACMAIN_CHM 的回复:

IF(IAcount_Type = 0)THEN
	set iCount = (select count(*) from t_account_commodity_all where account_id = Acount_Id);
	
	IF (iCount = 0) THEN
		insert into t_account_commodity_all ( account_id,total_profit_points,profit_loss_money,total_reward_ratio,trade_count,profit_trade_count,win_rate,avg_profit_point,avg_loss_point,profit_loss_ratio,annual_return,commodity_id ) values
			( Acount_Id,Spread_profits_sum,Market_price_profits_sum,Total_reward_ratio,Acount_Num,Acount_Suc_Num,Win_rate,Avg_profit_point,Avg_loss_point,Profit_loss_ratio,Annual_return,Commodity_id);
	ELSE
		update t_account_commodity_all 
			set total_profit_points = Spread_profits_sum, profit_loss_money = Market_price_profits_sum, total_reward_ratio =Total_reward_ratio,trade_count =Acount_Num,
			profit_trade_count = Acount_Suc_Num,win_rate = Win_rate,avg_profit_point = Avg_profit_point,avg_loss_point = Avg_loss_point,profit_loss_ratio = Profit_loss_ratio,annual_return = Annual_return
			where account_id = Acount_Id  and commodity_id = Commodity_id;
	END IF;
END IF;

可根据条件判断，则会生成第二条的呀，因为account_id 虽然一致，但是commodity_id 这个是不同的，所以它应该生成第二条的呀，可现在它直接覆盖第一条了

ACMAIN_CHM 2013-05-29

打赏
举报

IF(IAcount_Type = 0)THEN
	set iCount = (select count(*) from t_account_commodity_all where account_id = Acount_Id);
	
	IF (iCount = 0) THEN
		insert into t_account_commodity_all ( account_id,total_profit_points,profit_loss_money,total_reward_ratio,trade_count,profit_trade_count,win_rate,avg_profit_point,avg_loss_point,profit_loss_ratio,annual_return,commodity_id ) values
			( Acount_Id,Spread_profits_sum,Market_price_profits_sum,Total_reward_ratio,Acount_Num,Acount_Suc_Num,Win_rate,Avg_profit_point,Avg_loss_point,Profit_loss_ratio,Annual_return,Commodity_id);
	ELSE
		update t_account_commodity_all 
			set total_profit_points = Spread_profits_sum, profit_loss_money = Market_price_profits_sum, total_reward_ratio =Total_reward_ratio,trade_count =Acount_Num,
			profit_trade_count = Acount_Suc_Num,win_rate = Win_rate,avg_profit_point = Avg_profit_point,avg_loss_point = Avg_loss_point,profit_loss_ratio = Profit_loss_ratio,annual_return = Annual_return
			where account_id = Acount_Id  and commodity_id = Commodity_id;
	END IF;
END IF;

一魅 2013-05-29

打赏
举报

引用楼主 YI_MQ 的回复:

CREATE PROCEDURE HD_Insert_Every_Acount_Every_Commidity_All_Things( Acount_Id bigint(11), Spread_profits_sum float(11), Market_price_profits_sum double, Total_reward_ratio float(10), Acount_Num int(10), Acount_Suc_Num int(10), Win_rate float(10), Avg_profit_point float(11), Avg_loss_point float(11), Profit_loss_ratio float(11), Annual_return float(11), IAcount_Type int(11), Commodity_id int(10) ) BEGIN declare iCount INT default 0; IF(IAcount_Type = 0)THEN set iCount = (select count(*) from t_account_commodity_all where account_id = Acount_Id); IF (iCount = 0) THEN insert into t_account_commodity_all ( account_id,total_profit_points,profit_loss_money,total_reward_ratio,trade_count,profit_trade_count,win_rate,avg_profit_point,avg_loss_point,profit_loss_ratio,annual_return,commodity_id ) values ( Acount_Id,Spread_profits_sum,Market_price_profits_sum,Total_reward_ratio,Acount_Num,Acount_Suc_Num,Win_rate,Avg_profit_point,Avg_loss_point,Profit_loss_ratio,Annual_return,Commodity_id); ELSE update t_account_commodity_all set total_profit_points = Spread_profits_sum, profit_loss_money = Market_price_profits_sum, total_reward_ratio =Total_reward_ratio,trade_count =Acount_Num, profit_trade_count = Acount_Suc_Num,win_rate = Win_rate,avg_profit_point = Avg_profit_point,avg_loss_point = Avg_loss_point,profit_loss_ratio = Profit_loss_ratio,annual_return = Annual_return where account_id = Acount_Id and commodity_id = Commodity_id; END IF; END IF; END

我用这个存储过程第一个就能够存储成功，而第二个则会更新成第一个，而不会生成新的记录，比如： CALL HD_Insert_Every_Acount_Every_Commidity_All_Things(1,94.3,501.46,0.0019,8,7,0.8750,13.7,-1.5,-9.12,0.0019,0,1) CALL HD_Insert_Every_Acount_Every_Commidity_All_Things(1,441.9,2070.14,0.0088,17,16,0.9412,27.8,-2.3,-12.07,0.0078,0,2) 第一个生成后，执行第二个，第二个的数据会覆盖第一个，而不会生成新的一条记录，是不是我的存储过程有问题，还是我的表设计有问题？求助，谢谢了

帮忙看下这个存储过程是否有问题？谢谢了

相关推荐