create procedure PRO_update_Goods_group_stock is

  v_min_stock NUMBER(10, 2);

  v_gg_stock  NUMBER(10, 2);

  v_goods_no  number;

  v_sum_cost  NUMBER(10, 2);

begin

  for g_goods_no in (select ggi.goods_no

                       from goods_group_item ggi

                      where ggi.is_delete = 'N'

                      GROUP by ggi.goods_no) loop

    select count(*)

      into v_goods_no

      from goods_no_stock gn

     where gn.goods_no = g_goods_no.goods_no;

    if v_goods_no > 0 then

      continue;

    end if;

    --查询组合子商品的库存再除以绑定的数量 取最小值    gg主商品 g子商品

    select min(TRUNC(g.available_stock /

                     decode(gg1.group_goods_amount,

                            0,

                            1,

                            gg1.group_goods_amount),

                     0))

      into v_min_stock

      from goods_group_item gg1, goods g, goods gg

     where gg1.goods_no = g_goods_no.goods_no

       and gg1.is_delete = 'N'

       and gg.goods_id = gg1.goods_id

       and gg.is_delete = 'N'

       and g.goods_id = gg1.GROUP_GOODS_ID;

    --计算成本价格    gg主商品 g子商品

    select trunc(sum(g.cost_price * gg1.group_goods_amount), 2)

      into v_sum_cost

      from goods_group_item gg1, goods g, goods gg

     where gg1.goods_no = g_goods_no.goods_no

       and gg1.is_delete = 'N'

       and gg.goods_id = gg1.goods_id

       and gg.is_delete = 'N'

       and g.goods_id = gg1.GROUP_GOODS_ID;

    update goods

       set available_stock = v_min_stock, COST_PRICE = v_sum_cost

     where goods_no = g_goods_no.goods_no

       and is_delete = 'N';

    commit;

  end loop;

  --下架问题组合码

  insert into sys_user_message

    (MESSAGE_ID,

     PRODUCT_CODE,

     TARGET_MODUL_ID,

     MESSAGE_BODY,

     MESSAGE_STAGE,

     IS_DELETE)

    select sys_user_message_seq.nextval,

           p.product_code,

           254,

           '商品：'||p.product_code||' 没有维护组合码',

           '',

           'N'

      from product p

     where not exists (select 1

              from goods_group_item ggi

             where ggi.goods_no = p.product_code)

       and p.is_delete = 'N'

       and p.is_onsale = 'Y'

       and p.product_code like 'A%';

     update product p set p.is_onsale = 'N'

     where not exists (select 1

              from goods_group_item ggi

             where ggi.goods_no = p.product_code)

      and p.is_delete = 'N'

       and p.is_onsale = 'Y'

       and p.product_code like 'A%';

 commit;

end;

select *

      from goods_no_stock gn

     where gn.goods_no in  (select ggi.goods_no

                       from goods_group_item ggi

                      where ggi.is_delete = 'N'

                      GROUP by ggi.goods_no);

select  * from goods g where  g.GOODS_NO='02-00056';

  select  * from goods g where g.GOODS_NO='';

select  * from goods g where g.goods_no='';

select gn.goods_no

      from goods_no_stock gn;

goods_no_stock:库存同步黑名单表,在这张表中的数据,都不会进行相关的更新操作