復制代碼 代碼如下:

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'WthenW
updateWcontract_itemW
setWarrival_date=add_months(sysdate,1)W//加一個月
whereWitem_no=&&itemnoWandWcontract_no=&&contractno;W
else
updateWcontract_itemW
setWarrival_date=add_months(sysdate,2)W
whereWitem_no=&&itemnoWandWcontract_no=&&contractno;W
insertWintoWordersW
values(100,i_product_id,i_std_shipping_qty);W
end if;
end if;
commit;
end;