• 全部
  • 问答

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

一魅 2013-05-29 09:47:04
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
...全文
54 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
一魅 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;
第一个生成后,执行第二个,第二个的数据会覆盖第一个,而不会生成新的一条记录 你的代码不就是实现这个功能吗?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);
回复
一魅 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;
第一个生成后,执行第二个,第二个的数据会覆盖第一个,而不会生成新的一条记录 你的代码不就是实现这个功能吗?IF iCount = 0 就生成,否则就 update t_accoun 覆盖
可根据条件判断,则会生成第二条的呀,因为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;
第一个生成后,执行第二个,第二个的数据会覆盖第一个,而不会生成新的一条记录 你的代码不就是实现这个功能吗?IF iCount = 0 就生成,否则就 update t_accoun 覆盖
回复
一魅 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) 第一个生成后,执行第二个,第二个的数据会覆盖第一个,而不会生成新的一条记录,是不是我的存储过程有问题,还是我的表设计有问题?求助,谢谢了
回复
相关推荐
发帖
MySQL
创建于2007-09-28

5.5w+

社区成员

MySQL相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2013-05-29 09:47
社区公告
暂无公告