{"id":59,"date":"2023-11-29T11:05:57","date_gmt":"2023-11-29T03:05:57","guid":{"rendered":"http:\/\/www.ooboou.com\/?p=59"},"modified":"2023-11-29T11:05:57","modified_gmt":"2023-11-29T03:05:57","slug":"oracle%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e6%b8%b8%e6%a0%87%e7%94%a8%e6%b3%95","status":"publish","type":"post","link":"http:\/\/www.ooboou.com\/?p=59","title":{"rendered":"oracle\u5b58\u50a8\u8fc7\u7a0b\u6e38\u6807\u7528\u6cd5"},"content":{"rendered":"\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<pre class=\"wp-block-code\"><code>create or replace procedure zx_proc_chaifen_temp is\r\n\r\n  p_placepointid number(10);\r\n  p_goodsid     number(10);\r\n  p_chayiQty    NUMBER(16, 6);\r\n  p_chayiMoney  NUMBER(12, 4);\r\n  p_selldate    date;\r\n  p_curQty      NUMBER(16, 6);\r\n  p_currealmoney NUMBER(12, 4);\r\n  p_unitPrice   NUMBER(16, 6);\r\n\r\n  CURSOR p_goodschayi_cur IS\r\n    select placepointid, goodsid, chayiQty, chayiMoney\r\n      from zx_chayi_alan_temp;\r\n\r\nbegin\r\n  open p_goodschayi_cur;\r\n  fetch p_goodschayi_cur\r\n    into p_placepointid, p_goodsid, p_chayiQty, p_chayiMoney;\r\n  while p_goodschayi_cur%found loop\r\n  \r\n    p_unitPrice := round(p_chayiMoney \/ p_chayiQty, 4);\r\n  \r\n    if p_chayiQty > 0 then\r\n    \r\n      while p_chayiQty > 0 loop\r\n     \r\n       SELECT to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_char(to_date('20190101',\r\n                                                             'yyyymmdd'),\r\n                                                      'J')),\r\n                                       to_number(to_char(to_date('20191231',\r\n                                                             'yyyymmdd') + 1,\r\n                                                      'J')))),\r\n                    'J') + DBMS_RANDOM.VALUE(1, 3600) \/ 3600\r\n         into p_selldate\r\n         FROM dual;\r\n     \r\n       if p_chayiqty >= 1 then\r\n         p_curQty      := 1;\r\n         p_currealmoney := round(p_unitPrice * p_curQty, 2);\r\n       else\r\n         p_curQty      := p_chayiqty;\r\n         p_currealmoney := p_chayiMoney;\r\n       end if;\r\n     \r\n       insert into zx_chaifen_alan_temp\r\n       values\r\n         (p_placepointid, p_goodsid, p_curQty, p_currealmoney, p_selldate);\r\n     \r\n       p_chayiQty  := p_chayiQty - 1;\r\n       p_chayiMoney := p_chayiMoney - p_currealmoney;\r\n     \r\n      end loop;\r\n    \r\n    else\r\n      while p_chayiQty &lt; 0 loop\r\n     \r\n       SELECT to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_char(to_date('20190101',\r\n                                                             'yyyymmdd'),\r\n                                                      'J')),\r\n                                       to_number(to_char(to_date('20191231',\r\n                                                             'yyyymmdd') + 1,\r\n                                                      'J')))),\r\n                    'J') + DBMS_RANDOM.VALUE(1, 3600) \/ 3600\r\n         into p_selldate\r\n         FROM dual;\r\n     \r\n       if p_chayiqty &lt;= -1 then\r\n         p_curQty      := -1;\r\n         p_currealmoney := round(p_unitPrice * p_curQty, 2);\r\n       else\r\n         p_curQty      := p_chayiqty;\r\n         p_currealmoney := p_chayiMoney;\r\n       end if;\r\n     \r\n       insert into zx_chaifen_alan_temp\r\n       values\r\n         (p_placepointid, p_goodsid, p_curQty, p_currealmoney, p_selldate);\r\n     \r\n       p_chayiQty  := p_chayiQty + 1;\r\n       p_chayiMoney := p_chayiMoney - p_currealmoney;\r\n      end loop;\r\n    \r\n    end if;\r\n  \r\n    fetch p_goodschayi_cur\r\n      into p_placepointid, p_goodsid, p_chayiQty, p_chayiMoney;\r\n  end loop;\r\n  close p_goodschayi_cur;\r\n\r\nend;<\/code><\/pre>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"","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-59","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\/59","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=59"}],"version-history":[{"count":1,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=\/wp\/v2\/posts\/59\/revisions"}],"predecessor-version":[{"id":60,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=\/wp\/v2\/posts\/59\/revisions\/60"}],"wp:attachment":[{"href":"http:\/\/www.ooboou.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=59"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=59"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ooboou.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=59"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}