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

一魅 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
...全文
84 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
一魅 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) 第一个生成后，执行第二个，第二个的数据会覆盖第一个，而不会生成新的一条记录，是不是我的存储过程有问题，还是我的表设计有问题？求助，谢谢了
PL/SQL 中文破解版
　pl/sql developer正是解决这个问题的:功能强，方便，快。用了之后绝对离不开它! 　它的主要强项如下： 　一.编辑表数据(浏览表数数据自然不在话下) 　二.在sql plus下写长的语句时是否有些心烦，在这里一样搞定...
od脱壳软件破解工具
但愿大家互相进修进修，大家对于破解都不是很了解，人们想学破解，可是去无从入手，所以决议为大家写1个破解初级读物的教程，但愿能大家了解破解有一些帮忙，但愿能有更多的人踏入破解的大门 　1.低级,修改步伐,用...
oracle存储过程到底是什么,，请帮忙看一个Oracle存储过程到底有什么东东，多谢...
求助，请帮忙看一个Oracle存储过程到底有什么错误，谢谢！该存储过程，传入一个用户帐号，然后先去SYS_USER检查这个帐号有没有存在，如果不存，就根据这个帐号去其他表查询出我要的信息然后分别插入SYS_USER和SYS_...
php sqlsrv 取值,PHP PDO 怎么获取SQLSRV 存储过程的OUTPUT值？
不知道是不是我的代码有问题，麻烦大神帮忙看看，以下为具体说明。存储过程的OUTPUT值为 @flag INT，成功为1，失败为0，单步调试是有值的，我感觉应该不是SQL部分的问题。这里的类型也试过BIT、TINYINT、VARCHAR，...
oracle存储过程调用失败,Mybatis调用oracle存储过程错误问题
在调用存储过程的时候，不知道下面这段代码到底是哪里错了，总是报in或out参数索引丢失，但是我看着好像哪里都没错，想让各位大神帮忙看下，这段代码中是哪里除了问题，谢谢。oracle存储过程：...
MySQL

56,677

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章