Oracle_PLSQL_编程手册(SQL大全)

Question

1:
接收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:
contract_nonumber(12)
item_nonumber(6)
arrival_datedateorder:
order_idnumber(6)
product_idnumber(6)
qtynumber(3)答案：
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
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=&&contractnoanditem_no=&&itemno);
ifi_product_status='shipped'then
updatecontract_item
setarrival_date=sysdate+7
whereitem_no=&&itemnoandcontract_no=&&contractno;
elsif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;
2:
1.找出指定部门中的所有雇员
2.用带'&'的变量提示用户输入部门编号
3.把雇员姓名及工资存入prnttable表中，基结构为：
createtableprnttable
(seqnumber(7),linechar(80));
4.异常情况为，部门中奖金不为空值的雇员信息才能存入prnttable表中。答案：
declare
cursoremp_curis
selectename,sal,comm
fromempwheredeptno=&dno;
emp_recemp_cur%rowtype;
null_commissionexception;
begin
openemp_cur;
fetchemp_curintoemp_rec;
while(emp_cur%found)loop
ifemp_rec.commisnullthen
begin
closeemp_cur;
raisenull_commission;
end;
endif;
fetchemp_curintoemp_rec;
endloop;
closeemp_sur;
exception
whennull_commissionthen
openemp_cur;
fetchemp_curintoemp_rec;
while(emp_cur%found)loop
ifemp_rec.commisnotnullthen
insertintotempvalues(emp_rec.sal,emp_rec.ename);
endif;
fetchemp_curintoemp_rec;
endloop;
closeemp_cur;
commit;
end;

Answer 1

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
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=&&contractnoanditem_no=&&itemno);
ifi_product_status='shipped'then
updatecontract_item
setarrival_date=sysdate+7
whereitem_no=&&itemnoandcontract_no=&&contractno;
elsif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;

Answer 2

declare
cursoremp_curis
selectename,sal,comm
fromempwheredeptno=&dno;
emp_recemp_cur%rowtype;
null_commissionexception;
begin
openemp_cur;
fetchemp_curintoemp_rec;
while(emp_cur%found)loop
ifemp_rec.commisnullthen
begin
closeemp_cur;
raisenull_commission;
end;
endif;
fetchemp_curintoemp_rec;
endloop;
closeemp_sur;
exception
whennull_commissionthen
openemp_cur;
fetchemp_curintoemp_rec;
while(emp_cur%found)loop
ifemp_rec.commisnotnullthen
insertintotempvalues(emp_rec.sal,emp_rec.ename);
endif;
fetchemp_curintoemp_rec;
endloop;
closeemp_cur;
commit;
end;