Oracle一些小作业

create table product(

pCode int primary key,

pType varchar(20) not null,

pName varchar(20) not null,

cost number(10,1) not null,

price number(10,1) not null

)

create table porder(

orderNo int primary key,

custNo int not null,

orderDate Date not null,

dilivDate Date not null

)

create table orderDetail(

orderNo int ,

itemNo int not null,

pCode int ,

qty int not null,

discount number(3,2) not null,

primary key(orderNo,pCode),

constraint orderDetail_porder foreign key (orderNo) references porder(orderNo),

constraint orderDetail_product foreign key (pCode) references product(pCode)

)

 

SQL> alter   session   set  nls_date_format= 'YYYY-MM-DD';

alter table porder add check(dilivDate>=orderDate)

 

insert into product values('101','足球类','足球','85.0','110.0')

insert into product values('102','足球类','手套','90.0','12.0');

insert into product values('201','羽毛球类','羽球鞋','28.0','38.0');

insert into product values('202','羽毛球类','球拍','200.0','250.0');

insert into product values('301','游泳类','泳镜','85.0','102.0');

insert into product values('302','游泳类','泳帽','50.0','63.0');

insert into product values('401','健美类','拉力器','40.5','92.0');

insert into product values('402','健美类','十磅哑铃','70.0','92.0');

insert into product values('403','健美类','跳绳','20.0','20.0');

 

insert into porder values('21','10001','2002-1-5','2002-1-5');

insert into porder values('22','10002','2002-1-15','2002-1-16');

insert into porder values('23','10003','2002-2-10','2002-2-20');

insert into porder values('24','10002','2002-3-4','2002-3-5');

 

insert into orderDetail values('21','1','101','100','0.00');

insert into orderDetail values('21','2','102','60','0.05');

insert into orderDetail values('21','3','202','200','0.10');

insert into orderDetail values('22','1','301','1000','0.25');

insert into orderDetail values('22','2','302','100','0.00');

insert into orderDetail values('23','1','202','20','0.00');

insert into orderDetail values('24','1','401','800','0.15');

insert into orderDetail values('24','2','402','500','0.20');

insert into orderDetail values('24','3','403','500','0.00');

insert into orderDetail values('24','4','101','200','0.00');

 

 

3. 查询每个订单的销售量。

select orderNo ,sum(qty) from orderDetail group by orderNo disc;

 

4. 查询未曾购买过101号产品的客户的编号。

select custNo from porder where orderNo not in (select orderNo from orderDetail where pCode='101');

 

5. 创建一个存储过程orderProduct，用于查询某个订单所订购的产品的数量，并输出订货数量。

 

create or replace procedure orderProduct (or_num  int) 

as qty_num int ;

begin 

select sum(qty) into qty_num from orderDetail  where orderNo = or_num ; dbms_output.put_line('你所选择的订单的产品的总数量为:'|| qty_num);

 end orderProduct;

 

6. 在表product上创建一个触发器change_price实现如下功能：避免修改产品价格时，修改后的价格低于其成本价，若修改的价格低于成本加，让其修改后的产品的价格等于成本价。

 

create or replace trigger change_price 

before update on product 

for each row

declare c number(10,1);

begin 

select cost into c from product ;

if :new.price < c then 

:new.price:=c;

end if;

end change_price;

/

 

create trigger change_price

 

before update 

on product

 

for each row

 

begin

 

when(newtable.price<oldtable.cost)

 

 set newtable.price=oldtable.cost;

 

end change_price;

 

create or replace trigger change_price 

before update of price

on product 

for each row

begin 

if :new.price < :old.cost then 

:new.price:=:old.cost;

end if;

end change_price;

/

 

7. 创建一个函数total_price，返回某个订单订购产品的总金额。

create or replace function total_price (orderNo_in int)

return number

as

totalprice number;

begin

select sum(qty*price*(1-discount)) into totalprice from product,orderDetail 

where product.pCode=orderDetail.pCode and orderNo=orderNo_in;

return totalprice;

end total_price;

/

 

create or replace function total_price (orderNo_in int)

 

return number

 

 

as

 

totalprice number;

 

 

begin

 

select sum(qty*price*(1-discount)) into totalprice 

from product,orderDetail

 

where  product.pCode=orderDetail.pCode and orderNo=orderNo_in;

 

 

return totalprice;

 

exception

 

when NO_DATA_FOUND

 

then

 

DBMS_OUTPUT.PUT_LINE('订单没有找到.');

 

end ;

 

declare

totalprice number(10);

 

begin

totalprice :=total_price(22);

 

DBMS_OUTPUT.PUT_LINE('你所选择的单号总金额是:'||totalprice);

 

end;