我的mysql版本是5.5.21的,下面这个存储过程是需要更加另外3张表的数据来更新strategycontracttemp中数据,但是每次更新到中途报错,先代码:CREATE PORCEDURE spInsST()
BEGIN
DECLARE v_ContractID INT;
DECLARE v_SecurityTypeID INT;
DECLARE v_ExchangeID INT;
DECLARE v_VarietyID INT;
DECLARE v_CurrencyID INT;
DECLARE v_underlying text;
DECLARE v_sec text;
DECLARE v_exch INT;
DECLARE v_exchangeName text;
DECLARE v_currency INT;
DECLARE v_fxrate DECIMAL(20,8);
DECLARE v_commfee DECIMAL(20,8);
DECLARE v_commFlag INT;
DECLARE fig INT;
DECLARE cur1 CURSOR FOR SELECT ContractID,SecurityTypeID,ExchangeID,VarietyID,CurrencyID,IFNULL(CommissionFee,0) AS comm1 FROM contractinfo ORDER BY ContractID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fig=1;
OPEN cur1;
REPEAT
FETCH cur1 INTO v_ContractID,v_SecurityTypeID,v_ExchangeID,v_VarietyID,v_CurrencyID,v_commfee;
SELECT VarietyNum INTO v_underlying FROM contractvariety WHERE VarietyID=v_VarietyID;
UPDATE strategycontracttemp SET underlying=v_underlying WHERE id=v_ContractID;
SELECT DValue INTO v_sec FROM dictionary WHERE DID=v_SecurityTypeID;
UPDATE strategycontracttemp SET sec=v_sec WHERE id=v_ContractID;
SELECT DValue,DName INTO v_exch,v_exchangeName FROM dictionary WHERE DID=v_ExchangeID;
UPDATE strategycontracttemp SET exch=v_exch WHERE id=v_ContractID;
UPDATE strategycontracttemp SET exchangeName=v_exchangeName WHERE id=v_ContractID;
SELECT DValue INTO v_currency FROM dictionary WHERE DID=v_CurrencyID;
UPDATE strategycontracttemp SET currency=v_currency WHERE id=v_ContractID;
SET v_fxrate=(CASE v_CurrencyID WHEN 150001 THEN '6.239'
WHEN 150002 THEN '7.761'
WHEN 150003 THEN '1'
WHEN 150004 THEN '77.61'
WHEN 150005 THEN '0.6376'
WHEN 150006 THEN '0.7655'
WHEN 150007 THEN '0.9354'
WHEN 150008 THEN '0.989'
WHEN 150009 THEN '0.9129'
WHEN 150010 THEN '1.2603' END);
IF v_commfee=0 THEN SET v_commFlag=1;
ELSE SET v_commFlag=2;
END IF;
UNTIL fig=1
END REPEAT;
CLOSE cur1;
END;Procedure execution failed
1172 - Result consisted of more than one row
郁闷啊,用游标实现的,但也不知道错在哪里。strategycontracttemp表中有1000多行数据的。
BEGIN
DECLARE v_ContractID INT;
DECLARE v_SecurityTypeID INT;
DECLARE v_ExchangeID INT;
DECLARE v_VarietyID INT;
DECLARE v_CurrencyID INT;
DECLARE v_underlying text;
DECLARE v_sec text;
DECLARE v_exch INT;
DECLARE v_exchangeName text;
DECLARE v_currency INT;
DECLARE v_fxrate DECIMAL(20,8);
DECLARE v_commfee DECIMAL(20,8);
DECLARE v_commFlag INT;
DECLARE fig INT;
DECLARE cur1 CURSOR FOR SELECT ContractID,SecurityTypeID,ExchangeID,VarietyID,CurrencyID,IFNULL(CommissionFee,0) AS comm1 FROM contractinfo ORDER BY ContractID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fig=1;
OPEN cur1;
REPEAT
FETCH cur1 INTO v_ContractID,v_SecurityTypeID,v_ExchangeID,v_VarietyID,v_CurrencyID,v_commfee;
SELECT VarietyNum INTO v_underlying FROM contractvariety WHERE VarietyID=v_VarietyID;
UPDATE strategycontracttemp SET underlying=v_underlying WHERE id=v_ContractID;
SELECT DValue INTO v_sec FROM dictionary WHERE DID=v_SecurityTypeID;
UPDATE strategycontracttemp SET sec=v_sec WHERE id=v_ContractID;
SELECT DValue,DName INTO v_exch,v_exchangeName FROM dictionary WHERE DID=v_ExchangeID;
UPDATE strategycontracttemp SET exch=v_exch WHERE id=v_ContractID;
UPDATE strategycontracttemp SET exchangeName=v_exchangeName WHERE id=v_ContractID;
SELECT DValue INTO v_currency FROM dictionary WHERE DID=v_CurrencyID;
UPDATE strategycontracttemp SET currency=v_currency WHERE id=v_ContractID;
SET v_fxrate=(CASE v_CurrencyID WHEN 150001 THEN '6.239'
WHEN 150002 THEN '7.761'
WHEN 150003 THEN '1'
WHEN 150004 THEN '77.61'
WHEN 150005 THEN '0.6376'
WHEN 150006 THEN '0.7655'
WHEN 150007 THEN '0.9354'
WHEN 150008 THEN '0.989'
WHEN 150009 THEN '0.9129'
WHEN 150010 THEN '1.2603' END);
IF v_commfee=0 THEN SET v_commFlag=1;
ELSE SET v_commFlag=2;
END IF;
UNTIL fig=1
END REPEAT;
CLOSE cur1;
END;Procedure execution failed
1172 - Result consisted of more than one row
郁闷啊,用游标实现的,但也不知道错在哪里。strategycontracttemp表中有1000多行数据的。
解决方案 »
- 数据插入乱码问题
- 如何用Powerdesigner自动生成向数据库中批量插入数据的代码啊?
- mysql group by 排序问题
- 一个mysql建树的问题
- postgresql数据库 select for update 问题
- 请问mysql的存储过程如何返回一个结果集,多谢了
- 知道一个字段的一个数据,怎么查询到这个数据在哪个表里
- 选取记录中姓名相同的记录 并将除了第一条之外的姓名加上星号
- 根据起止时间统计各销售部门首次下单顾客人数及总订单数
- mysql5.7.22主从无法连接的问题
- 这样的价格表,应该如何建产品以及价格表,还需要建别的什么表?
- 安装mysql 5.0时出现了问题,请帮忙看看,谢谢
UPDATE strategycontracttemp SET underlying=v_underlying WHERE id=v_ContractID;
SELECT DValue INTO v_sec FROM dictionary WHERE DID=v_SecurityTypeID;
UPDATE strategycontracttemp SET sec=v_sec WHERE id=v_ContractID;
SELECT DValue,DName INTO v_exch,v_exchangeName FROM dictionary WHERE DID=v_ExchangeID;
UPDATE strategycontracttemp SET exch=v_exch WHERE id=v_ContractID;
UPDATE strategycontracttemp SET exchangeName=v_exchangeName WHERE id=v_ContractID;
SELECT DValue INTO v_currency FROM dictionary WHERE DID=v_CurrencyID;
UPDATE strategycontracttemp SET currency=v_currency WHERE id=v_ContractID;\这个select中可能有一句返回多条记录了
检查返回结果,只能是单一值
SELECT DValue INTO v_sec FROM dictionary WHERE DID=v_SecurityTypeID;
SELECT DValue,DName INTO v_exch,v_exchangeName FROM dictionary WHERE DID=v_ExchangeID;
SELECT DValue INTO v_currency FROM dictionary WHERE DID=v_CurrencyID;检查你的这些语句,应该是返回了多个符合条件的记录。