接收contract_no和item_no值,在inventory表中查找,如果产品:? 已发货,在arrival_date中赋值为今天后的7天? 已订货,在arrival_date中赋值为今天后的一个月? 既无订货又无发货,则在arrival_date中赋值为今天后的两个月,? 并在order表中增加一条新的订单记录。? product_status的列值为'shipped'和'ordered'? inventory:? product_id?number(6)? product_description?char(30)? product_status?char(20)? std_shipping_qty?number(3)? contract_item:? product_id number(6)? contract_no?number(12)? item_no?number(6)? arrival_date?date? order:? order_id?number(6)? product_id?number(6)? qty?number(3)? 代码: <div class="codetitle"><a style="CURSOR: pointer" data="85158" class="copybut" id="copybut85158" onclick="doCopy('code85158')"> 代码如下:<div class="codebody" id="code85158"> declare i_product_id inventory.product_id%type; i_product_description inventory.product_description%type; i_product_status inventory.product_status%type; i_std_shipping_qty inventory.std_shipping_qty%type; begin //sql语句,将查询出来的值放到定义的变量中 select product_id,product_description,product_status,std_shipping_qty into i_product_id,i_product_description,i_product_status,i_std_shipping_qty from inventory where product_id=( select product_id from contract_item where contract_no=&&contract_no and item_no=&&item_no ); if i_product_status='shipped' then update contract_item set arrival_date=sysdate+7 contract_no=&&contract_no and item_no=&&item_no; //这里的elseif 是连着写的 elseif i_product_status='ordered'?then? update?contract_item? set?arrival_date=add_months(sysdate,1)?//加一个月 where?item_no=&&itemno?and?contract_no=&&contractno;? else update?contract_item? set?arrival_date=add_months(sysdate,2)? where?item_no=&&itemno?and?contract_no=&&contractno;? insert?into?orders? values(100,i_product_id,i_std_shipping_qty);? end if; end if; commit; end;
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|