高分求写存储过程，或SQL语句。分销行业的价格上下限修改

Question

商品信息表：product (pid, sort, grade,sale_price,...) 
价格表：price (pid, max_price, min_price,...) 外键：product表主键：pid, price_template表主键ptid
价格模板表：price_template(ptid,...)
价格模板明细表：price_tmp_dtl(ptdid, sort, grade, max_price, min_price,...) 外键：price_template表主键ptid-----------
业务是这样的。每个商品product都有一个sale_price“经销价”，sort 分类， grade 档次。
每个商品根据 分类sort， 档次grade，在 “价格模板明细表price_tmp_dtl中定义了max_price最高销售价， 最低销售价。（业务定义：最低销售价为product表中定义的sale_price).
商品信息表和价格模板明细表通过sort,grade来确定该商品的上下限现在要做的就是，当product表中的sale_price修改后，价格表中的max_price, min_price怎么修改。。
price表中的max_price = product表中的sale_price 乘以 price_tmp_dtl 表中的(max_price/min_price), 
price表中的min_price = product表中的sale_price...求SQL语句或存储过程
PS：这只是简单的一种情况先谢谢各位了，欢迎解答，有不清楚的欢迎提问谢谢了。。

Answer 1

没人么？？？
产品表：product
pid   sort分类   grade档次   sale_price经销价格（卖给经销商的价格）
1     2          2           100.0
2     3          2           120.0
-----------------------------价格模板表：price_template(ptid,...)
--------------------------------------价格模板明细表：price_tmp_dtl
ptdid  ptid(fk)  sort种类   grade档次  max_price最高上限价格   min_price最低价格
1        1         2          2            500.0                    100.0
2        1         3          2            600.0                    120.0----------------------------------------
门店销售价格表：price 
pid   productId(fk)  ptid(fk)   price零售价格   max_price最高上限价格   min_price最低价格
1          1            1           300.0           500.0                100.0
2          2            1           350.0           600.0                120.0---------------------------------------------现在商品表product中的pid=1的sale_price由100变成150元。pid=1的sort=2，grade=2,对应价格模板明细表：price_tmp_dtl中的ptdid=1,max_price最高上限价格=500,   min_price最低价格=100,
max/min=5
所以在price表中的max_price 应修改为150×5=750元。min_price=150元--------------
有没有达人过来解惑sql怎么关联。。怎么修改price表中的max_price, min_price

Answer 2

UPDATE price p
   SET (p.min_price, p.max_price) =
       (SELECT a.sale_price, b.max_price / b.min_price * a.sale_price
          FROM product a, price_template b
         WHERE a.pid = b.ptdid
           AND a.sort = b.sort
           AND a.grade = b.grade
           AND a.pid = p.pid)
 WHERE p.pid = 1

Answer 3

-----------------
谢谢回复价格模板表中没有定义max_price，min_price.而是在价格模板明细表price_temp_dtl表中定义的。而price_temp_dtl表中没有和product商品表关联。

Answer 4

没有主键关联，直接a.sort = b.sort， a.grade = b.grade会产生笛卡尔积吧

Answer 5

--得到品牌，中类，档次，最高上限，最低下限，建议最高上限，最低下限 价格
select pi.sc_tm_product_info_id, pi.sale_price, pi.bland "品牌", ptd.sc_tt_price_tmp_dtl_id, ptd.max_make_up_rate, ptd.min_make_up_rate, pi.sale_price*(ptd.max_make_up_rate/ptd.min_make_up_rate)
  from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_template pt, sc_tt_price_tmp_dtl ptd
  where 1=1
  and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
  and p.sc_tt_price_template_id = pt.sc_tt_price_template_id
  and pt.sc_tt_price_template_id = ptd.sc_tt_price_template_id
  and pi.bland = '1'                         --品牌
  and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
  and pi.grade = ptd.product_level           --档次
  
--得到总数
select count(p.rowid)
  from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_template pt, sc_tt_price_tmp_dtl ptd
  where 1=1
  and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
  and p.sc_tt_price_template_id = pt.sc_tt_price_template_id
  and pt.sc_tt_price_template_id = ptd.sc_tt_price_template_id
  and pi.bland = '1'                         --品牌
  and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
  and pi.grade = ptd.product_level           --档次
  
--修改操作：根据产品的经销价，得到产品定义价格中的最高和最低价
update sc_tt_price p set (p.max_price, p.min_price) = 
  (select pi.sale_price*(ptd.max_make_up_rate/ptd.min_make_up_rate), ptd.min_make_up_rate
  from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_template pt, sc_tt_price_tmp_dtl ptd
  where 1=1
  and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
  and p.sc_tt_price_template_id = pt.sc_tt_price_template_id
  and pt.sc_tt_price_template_id = ptd.sc_tt_price_template_id
  and pi.bland = '1'                         --品牌为罗莱
  and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
  and pi.grade = ptd.product_level)          --档次

Answer 6

--通过触发器实现：对product表的sale_price创建一个trigger
--注意需要申明为自治事务
--测试如下：Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --原始数据:
SQL> SELECT * FROM product;       PID       SORT      GRADE SALE_PRICE
---------- ---------- ---------- ----------
         1          2          2        100
         2          3          2        120SQL> SELECT * FROM price_tmp_dtl;     PTDID       PTID       SORT      GRADE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          3        500        100
         2          1          3          2        600        120SQL> SELECT * FROM price;       PID  PRODUCTID       PTID      PRICE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1        300        500        100
         2          2          1        350        600        120SQL> --创建触发器如下:
SQL> CREATE OR REPLACE TRIGGER u_product_trg
  2  BEFORE UPDATE OF sale_price ON product
  3  FOR EACH ROW
  4  DECLARE
  5  PRAGMA autonomous_transaction ;--申明自治事务
  6  BEGIN    
  7      UPDATE price p set (p.max_price, p.min_price) =
  8            (SELECT :NEW.sale_price*(ptd.max_price/ptd.min_price), :NEW.sale_price
  9            FROM  price pp, product pi, price_tmp_dtl ptd
 10            WHERE pp.productid = pi.pid AND p.ptid = ptd.ptid
 11              AND p.pid=pp.pid)
 12      WHERE p.pid=:OLD.pid ;
 13      COMMIT;
 14  EXCEPTION 
 15      WHEN OTHERS THEN 
 16          raise_application_error(-20102,'** u_product_trg error **' ||sqlerrm);
 17  END;
 18  /Trigger created.
SQL> UPDATE product SET sale_price=150 WHERE pid=1;     --修改为1501 row updated.SQL> SELECT * FROM product;       PID       SORT      GRADE SALE_PRICE
---------- ---------- ---------- ----------
         1          2          2        150        --已经改变
         2          3          2        120SQL> SELECT * FROM price_tmp_dtl;     PTDID       PTID       SORT      GRADE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          3        500        100
         2          1          3          2        600        120
SQL> select * from price;       PID  PRODUCTID       PTID      PRICE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1        300        750        150     --已经改变
         2          2          1        350        600        120
SQL> UPDATE product SET sale_price=200 WHERE pid=1;     --修改为2001 row updated.SQL> select * from product;       PID       SORT      GRADE SALE_PRICE
---------- ---------- ---------- ----------
         1          2          2        200      --已经改变
         2          3          2        120SQL> select * from price;       PID  PRODUCTID       PTID      PRICE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1        300       1000        200       --已经改变
         2          2          1        350        600        120SQL> --over!

Answer 7

--补充下：首先wck_168说的是正确的，自治事务会存在锁等待情况，这个需要具体来考虑
--另外，我上面的trigger中有个小错误
--WHERE p.pid=:OLD.pid ;   --这里应该用p.productid=:OLD.pid
--即 ：
CREATE OR REPLACE TRIGGER u_product_trg
BEFORE UPDATE OF sale_price ON product
FOR EACH ROW
DECLARE
    PRAGMA autonomous_transaction ;--申明自治事务
BEGIN    
     UPDATE price p set (p.max_price, p.min_price) =
             (SELECT :NEW.sale_price*(ptd.max_price/ptd.min_price), :NEW.sale_price
             FROM  price pp, product pi, price_tmp_dtl ptd
             WHERE pp.productid = pi.pid AND p.ptid = ptd.ptid
             AND p.pid=pp.pid)
     WHERE p.productid=:OLD.pid ;   --这里应该用p.productid=:OLD.pid
     COMMIT;
EXCEPTION 
     WHEN OTHERS THEN 
         raise_application_error(-20102,'** u_product_trg error **' ||sqlerrm);
END;
/

Answer 8

--最后再补充点，上面没考虑 sort和grade的关联，下面这个应该全了：
--测试如下：Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --原始数据:
SQL> SELECT * FROM product;       PID       SORT      GRADE SALE_PRICE
---------- ---------- ---------- ----------
         1          2          2        100
         2          3          2        120SQL> SELECT * FROM price_tmp_dtl;     PTDID       PTID       SORT      GRADE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          2          2        500        100
         2          1          3          2        600        120SQL> SELECT * FROM price;       PID  PRODUCTID       PTID      PRICE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1        300        500        100
         2          2          1        350        600        120SQL> --创建触发器如下:SQL> CREATE OR REPLACE TRIGGER u_product_trg
  2  BEFORE UPDATE OF sale_price ON product
  3  FOR EACH ROW
  4  DECLARE
  5      PRAGMA autonomous_transaction ;--申明自治事务
  6  BEGIN
  7       UPDATE price p set (p.max_price, p.min_price) =
  8               (SELECT :NEW.sale_price*(ptd.max_price/ptd.min_price), :NEW.sale_price
  9               FROM  price pp, product pi, price_tmp_dtl ptd
 10              WHERE pp.productid = pi.pid AND p.ptid = ptd.ptid
 11                AND pi.sort=ptd.sort AND pi.grade=ptd.grade   --增加 sort和grade的关联
 12                AND p.pid=pp.pid)
 13       WHERE p.productid=:OLD.pid ;   --这里应该用p.productid=:OLD.pid
 14       COMMIT;
 15  EXCEPTION
 16       WHEN OTHERS THEN
 17           raise_application_error(-20102,'** u_product_trg error **' ||sqlerrm);
 18  END;
 19  /Trigger created.
SQL> UPDATE product SET sale_price=150 WHERE pid=1;     --修改为1501 row updated.SQL> SELECT * FROM product;       PID       SORT      GRADE SALE_PRICE
---------- ---------- ---------- ----------
         1          2          2        150        --已经改变
          2          3          2        120SQL> SELECT * FROM price_tmp_dtl;     PTDID       PTID       SORT      GRADE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          2          2        500        100
         2          1          3          2        600        120
SQL> select * from price;       PID  PRODUCTID       PTID      PRICE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1        300        750        150     --已经改变
          2          2          1        350        600        120
SQL> UPDATE product SET sale_price=200 WHERE pid=1;     --修改为2001 row updated.SQL> select * from product;       PID       SORT      GRADE SALE_PRICE
---------- ---------- ---------- ----------
         1          2          2        200      --已经改变
          2          3          2        120SQL> select * from price;       PID  PRODUCTID       PTID      PRICE  MAX_PRICE  MIN_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1        300       1000        200       --已经改变
          2          2          1        350        600        120SQL> commit;Commit complete.SQL> --over!

Answer 9

1、关联关系你自己修改一下即可，关键在于思路
2、业务在前台实现可用我的思路，后台可以使用gelyon触发器方式

Answer 10

谢谢  
gelyon
。
安装你给的思路和代码创建了一个触发器。
运行时也正常，单当我执行一条update语句时，
触发器“product_sale_price_trg”无效且未通过验证。-------------
是因为要到系统管理员权限下创建“触发器”么？？？？
我现在用的是普通用户权限的帐号。。

Answer 11

pl/sql中看该“触发器”显示是打了一个红叉的上述问题请问怎么解决

Answer 12

可能是你创建trigger时候有问题，就在你当前用户下创建，也就是你product\price\等这些表所在的账户下创建trigger
你试试在SQLPLUS命令窗口创建呢，看看是否有编译错误，然后show error 看看什么错误再调试(注意在执行show error 的时候 要先设置 set serveroutput on )

Answer 13

谢谢～～～
现在我想删除这个错误的触发器，
delete triggers product_sale_price_trg
出现：are you sure you want to delete all records
这个是不是会把price,produce表中所有的数据都删除。。
是选no 吗？？？
现在不敢删除这个错误的触发器啊，怕把price,product等表中的所有数据都删除

Answer 14

删除触发器语法是：drop trigger product_sale_price_trg
删除触发器不影响原表数据，只是今后做的DML不会触发相应的动作了
如果你怕price,product等表中的所有数据都删除，你可以先将这些表数据备份下，再删除触发器