create or replace trigger BillMadeUpdate
before update of made on bill
for each row
DECLARE
CURSOR curBF(billUUID bf.billuuid%type) IS SELECT BillUUID, FoodId, Copies FROM bf WHERE BillUUID = :new.billUUID;
CURSOR curFM(foodId fm.foodid%type) IS SELECT * FROM fm WHERE FoodId = foodId;
foodId bf.foodid%type; -- 菜编号
copies bf.copies%type; -- 份数
materialID fm.materialid%type; -- 食材编号
materialNum fm.materialnumber%type; -- 食材量
BEGIN
-- 一个订单会有多道菜
FOR r in curBF(:new.BillUUID)
LOOP
foodId := r.FoodId;
copies := r.copies;
-- 一道菜对应多种食材
FOR r1 IN curFM(foodId)
LOOP
materialID := r1.MaterialId;
materialNum := r1.Materialnumber * copies;
UPDATE Material SET Remain = Remain - materialNum WHERE r1.MaterialId = materialId;
END LOOP;
END LOOP;
end BillMadeUpdate;
上面运行不明白多减去了什么
before update of made on bill
for each row
DECLARE
CURSOR curBF(billUUID bf.billuuid%type) IS SELECT BillUUID, FoodId, Copies FROM bf WHERE BillUUID = :new.billUUID;
CURSOR curFM(foodId fm.foodid%type) IS SELECT * FROM fm WHERE FoodId = foodId;
foodId bf.foodid%type; -- 菜编号
copies bf.copies%type; -- 份数
materialID fm.materialid%type; -- 食材编号
materialNum fm.materialnumber%type; -- 食材量
BEGIN
-- 一个订单会有多道菜
FOR r in curBF(:new.BillUUID)
LOOP
foodId := r.FoodId;
copies := r.copies;
-- 一道菜对应多种食材
FOR r1 IN curFM(foodId)
LOOP
materialID := r1.MaterialId;
materialNum := r1.Materialnumber * copies;
UPDATE Material SET Remain = Remain - materialNum WHERE r1.MaterialId = materialId;
END LOOP;
END LOOP;
end BillMadeUpdate;
上面运行不明白多减去了什么
解决方案 »
- Toad for oracle中如何像PL/SQL一样直接编辑数据
- 现在求助 ORA-01461: can bind a LONG value only for insert into a LONG column
- 求教,生成订单流水号.
- 请教一个ORACLE_HOME的配置问题
- R11i系統報表,'FND FLEXSQL '等等是什麼東東?
- 帮忙改错!急。
- oracle中 table collection expression是做什么用的?谁有示例语句?
- 很好的问题
- sql/plus语句问题求教
- 我用PL/SQL DEVELOP把存储过程导出来了,但我不知道怎样可以把它导到数据库里,如果要拷贝到sql/plus里执行的话,文字太长了,要分很多次
- 请问如何调用oracle的存储过程和函数啊?
- java.sql.SQLException: 无法从套接字读取更多的数据
按要求应该是改为1才执行,所以在for each row后面加一个判断条件
WHEN (NEW.made = 1)
BEGIN
FOR r in curBF(:new.BillUUID)
LOOP
foodId := r.FoodId;
copies := r.copies;
FOR r1 IN curFM(foodId)
LOOP
materialID := r1.MaterialId;
materialNum := r1.Materialnumber * copies;
UPDATE Material SET Remain = Remain - materialNum WHERE r1.MaterialId = materialId;
END LOOP;
END LOOP;
这里面的逻辑好像在实际中多减去了点东西