Introduction to oracle if else statements
- 2020-12-05 17:26:05
- OfStack
Receive the contract_no and item_no values and look them up in the inventory table if the product: �
Shipped, assigned 7 days after today in arrival_date
The order is made and assigned in arrival_date to 1 month after today
If there is neither order nor shipment, assign the value in arrival_date to two months after today, all right
Add a new order record to the order table. �
The column values for product_status are 'shipped' and '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)
Code:
Shipped, assigned 7 days after today in arrival_date
The order is made and assigned in arrival_date to 1 month after today
If there is neither order nor shipment, assign the value in arrival_date to two months after today, all right
Add a new order record to the order table. �
The column values for product_status are 'shipped' and '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)
Code:
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 Statement to place the queried value into the defined variable
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;
// Here, elseif It's attached to it
elseif i_product_status='ordered' � then �
update � contract_item �
set � arrival_date=add_months(sysdate,1) � // add 1 months
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;