我试了一下,,如果是简单的视图,可以更新,我的视图是select `a`.`id` AS `id`, `b`.`id` AS `EntryID`, `a`.`Date` AS `Date`, `a`.`Type` AS `Type`, `b`.`MaterialID` AS `MaterialID`, `d`.`ItemName` AS `MaterialName`, `d`.`Uomid` AS `UomID`, `d`.`UomName` AS `UomName`, `e`.`UomName` AS `BillUomName`, `e`.`Rate` AS `Rate`, `e`.`Method` AS `Method`, `b`.`BillQty` AS `BillQty`, (case when (`e`.`Method` = '*') then (`b`.`BillQty` * `e`.`Rate`) when (`e`.`Method` = '/') then (`b`.`BillQty` / `e`.`Rate`) else `b`.`BillQty` end) AS `Qty`, `b`.`Amount` AS `Amount` from (((`stockbill` `a` left join `stockbillentry` `b` on((`a`.`id` = `b`.`MainID`))) left join `material_view` `d` on((`b`.`MaterialID` = `d`.`ID`))) left join `m_uom` `e` on((`b`.`BillUomID` = `e`.`id`))) 存储过程中的更新语句是:set _price=@amount/@qty; UPDATE stockbill_view SET Amount=Qty*_price where MaterialID=_mid 这段更新语句如果直接更新是完全没有问题的 比如这样:UPDATE stockbill_view SET Amount=12 where MaterialID=1当然。。就算是这样写,不做运算,在存储过程中还是执行不了。。 我看了下,,这个视图和其它的视图区别就是Qty字段是运算出来的。但是这个又是必须要运算,也是存储过程中必须要用的。 有没有什么好办法??
不是这样吧。。那为什么直接执行更新视图的语句又可以,,比如说在程序中直接执行是可以的,在MYSQL中直接执行也是可以的,就是在存储过程中不行你的mysql版本号是多少啊?我这里真的不行。5.6.17。。我怀疑是存储过程中的限制。。(case when (`e`.`Method` = '*') then (`b`.`BillQty` * `e`.`Rate`) when (`e`.`Method` = '/') then (`b`.`BillQty` / `e`.`Rate`) else `b`.`BillQty` end) AS `Qty`,主要是这视图中这个地方是运算出来的,所以更新不了,但为什么直接执行又可以更新呢。。 比如这样UPDATE stockbill_view SET Amount=Qty*12 where MaterialID=1你试试
不是这样吧。。那为什么直接执行更新视图的语句又可以,,比如说在程序中直接执行是可以的,在MYSQL中直接执行也是可以的,就是在存储过程中不行你的mysql版本号是多少啊?我这里真的不行。5.6.17。。我怀疑是存储过程中的限制。。(case when (`e`.`Method` = '*') then (`b`.`BillQty` * `e`.`Rate`) when (`e`.`Method` = '/') then (`b`.`BillQty` / `e`.`Rate`) else `b`.`BillQty` end) AS `Qty`,主要是这视图中这个地方是运算出来的,所以更新不了,但为什么直接执行又可以更新呢。。 比如这样UPDATE stockbill_view SET Amount=Qty*12 where MaterialID=1你试试 把你的全部建表建视图sql 贴出来,这样猜测也不是很好的交流方式啊。
存储过程语句CREATE DEFINER = 'root'@'%' PROCEDURE `s_cost_out`( IN date_in CHAR(255), OUT ret_str CHAR(255) ) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE err INTEGER DEFAULT 0; DECLARE done INTEGER default 0; DECLARE _id INTEGER; DECLARE _mid INTEGER; DECLARE _price double(50,10); DECLARE _qty double(50,10); DECLARE _date Date; DECLARE cur_1 CURSOR FOR select id,MaterialID from stockbal where date=LAST_DAY(date_in); #定义游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1; inner_block: BEGIN START TRANSACTION; set _date=LAST_DAY(date_in); OPEN cur_1; FETCH cur_1 INTO _id,_mid; WHILE done<>1 DO SELECT count(*),SUM(qty),SUM(amount) INTO @iss,@qty,@amount FROM stockbill_view where MaterialID=_mid and type in (1,2,3,4,5,6) and DATE>=BeginMonthDay(_date) and DATE<=_date and VerifyUserID!=0 and qty>0 and amount!=0; if @iss > 0 then set _price=@amount/@qty; #这里出错,语句更新不了,但直接执行可以执行 UPDATE stockbill_view SET Amount=12 where MaterialID=_mid and type in (1,2,3,4,5,6) and DATE>=BeginMonthDay(_date) and DATE<=_date and VerifyUserID!=0 and Qty<0; end if; FETCH cur_1 INTO _id,_mid; END WHILE; close cur_1; end inner_block; #完成 if err = 0 then set ret_str='OK'; commit; else set ret_str=_date; rollback; end if; END; 视图语句select `a`.`id` AS `id`, `b`.`id` AS `EntryID`, `a`.`Date` AS `Date`, `a`.`Type` AS `Type`, `b`.`MaterialID` AS `MaterialID`, `d`.`ItemName` AS `MaterialName`, `d`.`Uomid` AS `UomID`, `d`.`UomName` AS `UomName`, `e`.`UomName` AS `BillUomName`, `e`.`Rate` AS `Rate`, `e`.`Method` AS `Method`, `b`.`BillQty` AS `BillQty`, (case when (`e`.`Method` = '*') then (`b`.`BillQty` * `e`.`Rate`) when (`e`.`Method` = '/') then (`b`.`BillQty` / `e`.`Rate`) else `b`.`BillQty` end) AS `Qty`, `b`.`Amount` AS `Amount` from (((`stockbill` `a` left join `stockbillentry` `b` on((`a`.`id` = `b`.`MainID`))) left join `material_view` `d` on((`b`.`MaterialID` = `d`.`ID`))) left join `m_uom` `e` on((`b`.`BillUomID` = `e`.`id`))) 表的关联太多了,没法发。。但是这样应该够了吧。主要是Qty字段是运算的,其它都是表中有的字段,
我试了一下,,如果是简单的视图,可以更新,我的视图是select
`a`.`id` AS `id`,
`b`.`id` AS `EntryID`,
`a`.`Date` AS `Date`,
`a`.`Type` AS `Type`,
`b`.`MaterialID` AS `MaterialID`,
`d`.`ItemName` AS `MaterialName`,
`d`.`Uomid` AS `UomID`,
`d`.`UomName` AS `UomName`,
`e`.`UomName` AS `BillUomName`,
`e`.`Rate` AS `Rate`,
`e`.`Method` AS `Method`,
`b`.`BillQty` AS `BillQty`,
(case when (`e`.`Method` = '*') then (`b`.`BillQty` * `e`.`Rate`) when (`e`.`Method` = '/') then (`b`.`BillQty` / `e`.`Rate`) else `b`.`BillQty` end) AS `Qty`,
`b`.`Amount` AS `Amount`
from
(((`stockbill` `a` left join `stockbillentry` `b` on((`a`.`id` = `b`.`MainID`))) left join `material_view` `d` on((`b`.`MaterialID` = `d`.`ID`))) left join `m_uom` `e` on((`b`.`BillUomID` = `e`.`id`)))
存储过程中的更新语句是:set _price=@amount/@qty;
UPDATE stockbill_view SET Amount=Qty*_price where MaterialID=_mid
这段更新语句如果直接更新是完全没有问题的
比如这样:UPDATE stockbill_view SET Amount=12 where MaterialID=1当然。。就算是这样写,不做运算,在存储过程中还是执行不了。。
我看了下,,这个视图和其它的视图区别就是Qty字段是运算出来的。但是这个又是必须要运算,也是存储过程中必须要用的。
有没有什么好办法??
不是这样吧。。那为什么直接执行更新视图的语句又可以,,比如说在程序中直接执行是可以的,在MYSQL中直接执行也是可以的,就是在存储过程中不行
不是这样吧。。那为什么直接执行更新视图的语句又可以,,比如说在程序中直接执行是可以的,在MYSQL中直接执行也是可以的,就是在存储过程中不行你的mysql版本号是多少啊?我这里真的不行。
不是这样吧。。那为什么直接执行更新视图的语句又可以,,比如说在程序中直接执行是可以的,在MYSQL中直接执行也是可以的,就是在存储过程中不行你的mysql版本号是多少啊?我这里真的不行。5.6.17。。我怀疑是存储过程中的限制。。(case when (`e`.`Method` = '*') then (`b`.`BillQty` * `e`.`Rate`) when (`e`.`Method` = '/') then (`b`.`BillQty` / `e`.`Rate`) else `b`.`BillQty` end) AS `Qty`,主要是这视图中这个地方是运算出来的,所以更新不了,但为什么直接执行又可以更新呢。。
比如这样UPDATE stockbill_view SET Amount=Qty*12 where MaterialID=1你试试
不是这样吧。。那为什么直接执行更新视图的语句又可以,,比如说在程序中直接执行是可以的,在MYSQL中直接执行也是可以的,就是在存储过程中不行你的mysql版本号是多少啊?我这里真的不行。5.6.17。。我怀疑是存储过程中的限制。。(case when (`e`.`Method` = '*') then (`b`.`BillQty` * `e`.`Rate`) when (`e`.`Method` = '/') then (`b`.`BillQty` / `e`.`Rate`) else `b`.`BillQty` end) AS `Qty`,主要是这视图中这个地方是运算出来的,所以更新不了,但为什么直接执行又可以更新呢。。
比如这样UPDATE stockbill_view SET Amount=Qty*12 where MaterialID=1你试试
把你的全部建表建视图sql 贴出来,这样猜测也不是很好的交流方式啊。
存储过程语句CREATE DEFINER = 'root'@'%' PROCEDURE `s_cost_out`(
IN date_in CHAR(255),
OUT ret_str CHAR(255)
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE err INTEGER DEFAULT 0;
DECLARE done INTEGER default 0;
DECLARE _id INTEGER;
DECLARE _mid INTEGER;
DECLARE _price double(50,10);
DECLARE _qty double(50,10);
DECLARE _date Date;
DECLARE cur_1 CURSOR FOR select id,MaterialID
from stockbal where date=LAST_DAY(date_in); #定义游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;
inner_block: BEGIN
START TRANSACTION;
set _date=LAST_DAY(date_in);
OPEN cur_1;
FETCH cur_1 INTO _id,_mid;
WHILE done<>1 DO
SELECT count(*),SUM(qty),SUM(amount) INTO @iss,@qty,@amount FROM stockbill_view
where MaterialID=_mid and type in (1,2,3,4,5,6)
and DATE>=BeginMonthDay(_date) and DATE<=_date
and VerifyUserID!=0 and qty>0 and amount!=0;
if @iss > 0 then
set _price=@amount/@qty;
#这里出错,语句更新不了,但直接执行可以执行
UPDATE stockbill_view SET Amount=12 where MaterialID=_mid
and type in (1,2,3,4,5,6)
and DATE>=BeginMonthDay(_date) and DATE<=_date
and VerifyUserID!=0 and Qty<0;
end if;
FETCH cur_1 INTO _id,_mid;
END WHILE;
close cur_1;
end inner_block;
#完成
if err = 0 then
set ret_str='OK';
commit;
else
set ret_str=_date;
rollback;
end if;
END;
视图语句select
`a`.`id` AS `id`,
`b`.`id` AS `EntryID`,
`a`.`Date` AS `Date`,
`a`.`Type` AS `Type`,
`b`.`MaterialID` AS `MaterialID`,
`d`.`ItemName` AS `MaterialName`,
`d`.`Uomid` AS `UomID`,
`d`.`UomName` AS `UomName`,
`e`.`UomName` AS `BillUomName`,
`e`.`Rate` AS `Rate`,
`e`.`Method` AS `Method`,
`b`.`BillQty` AS `BillQty`,
(case when (`e`.`Method` = '*') then (`b`.`BillQty` * `e`.`Rate`)
when (`e`.`Method` = '/') then (`b`.`BillQty` / `e`.`Rate`)
else `b`.`BillQty` end) AS `Qty`,
`b`.`Amount` AS `Amount`
from
(((`stockbill` `a` left join `stockbillentry` `b` on((`a`.`id` = `b`.`MainID`)))
left join `material_view` `d` on((`b`.`MaterialID` = `d`.`ID`)))
left join `m_uom` `e` on((`b`.`BillUomID` = `e`.`id`)))
表的关联太多了,没法发。。但是这样应该够了吧。主要是Qty字段是运算的,其它都是表中有的字段,