{"id":55,"date":"2023-11-29T10:51:52","date_gmt":"2023-11-29T02:51:52","guid":{"rendered":"http:\/\/www.ooboou.com\/?p=55"},"modified":"2023-11-29T10:59:53","modified_gmt":"2023-11-29T02:59:53","slug":"oracle%e4%b8%addeclare%e7%a8%8b%e5%ba%8f%e5%9d%97%e7%94%a8%e6%b3%95","status":"publish","type":"post","link":"http:\/\/www.ooboou.com\/?p=55","title":{"rendered":"oracle\u4e2ddeclare\u7a0b\u5e8f\u5757\u7528\u6cd5"},"content":{"rendered":"
declare
  p_curmoneySum  number(18, 4);
  p_placepointid number(10);
  p_rsaid      number(10);
  p_goodsqtysum  number(18, 6);
  p_realmoneysum number(18, 4);

  cursor p_goods_sale_c IS
    select placepointid,
         rsaid,
         sum(goodsqty) as goodsqtysum,
         sum(realmoney) as realmoneysum
      from GRESA_SA_DETAIL_V
     where rsaid not in (select rsaid from ZX_RSAID_ALAN_TEMP)
      and sellDate between to_date('2019-01-01', 'yyyy-mm-dd') and
         to_date('2019-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
     group by placepointid, rsaid
     order by realmoneysum asc;

begin
  p_curmoneySum := 300000.0;
  open p_goods_sale_c;
  fetch p_goods_sale_c
    into p_placepointid, p_rsaid, p_goodsqtysum, p_realmoneysum;
  while p_goods_sale_c%found loop
  
    p_curmoneySum := p_curmoneySum - p_realmoneysum;
  
    if p_curmoneySum > 0 then
    
     insert into ZX_REDUCE_RSAID_TEMP (rsaid) values (p_rsaid);
    
    else
    
     exit;
    
    end if;
  
    fetch p_goods_sale_c
      into p_placepointid, p_rsaid, p_goodsqtysum, p_realmoneysum;
  
  end loop;

  close p_goods_sale_c;
end;<\/pre>\n","protected":false},"excerpt":{"rendered":"
declare  p_curmoneySum  number(18, 4);   … \u9605\u8bfb\u66f4\u591a<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[7,8,9],"class_list":["post-55","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-it","tag-oracle","tag-sql"],"_links":{"self":[{"href":"http:\/\/www.ooboou.com\/index.php?rest_route=\/wp\/v2\/posts\/55","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.ooboou.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ooboou.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=55"}],"version-history":[{"count":3,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=\/wp\/v2\/posts\/55\/revisions"}],"predecessor-version":[{"id":58,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=\/wp\/v2\/posts\/55\/revisions\/58"}],"wp:attachment":[{"href":"http:\/\/www.ooboou.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=55"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=55"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=55"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}