oracle测试题二

一 建立3个表，分别是商品表，会员表，订单表，商品表的属性有商品号，商品名，
单价，数量，厂名，产地。会员表属性有会员号，姓名，性别，年龄，电话。订单
表的属性有会员号，商品号，数量，单价，日期（注意定义主键与外键），性别字段只
能取’男’或’女’，通过insert语句在每个表插入5条数据。
create table 商品表
(商品号 number primary key,
 商品名 varchar2(20),
 单价 number,
 数量 number,
 厂名 varchar2(20),
 产地 varchar2(20)
);

create table 会员表
(会员号 number primary key,
 姓名 varchar2(20),
 性别 varchar2(10) check(性别='男' or 性别='女'),
 年龄 number,
 电话 number
);

create table 订单表
(会员号 number,
 商品号 number,
 数量 number,
 单价 number,
 日期 date);
alter table 订单表 add constraint fk_add1 foreign key(会员号) references 会员表(会员号);
alter table 订单表 add constraint fk_add2 foreign key(商品号) references 商品表(商品号);

insert into 商品表 values(1,'1商品',1,1,'1厂','1产地');
insert into 商品表 values(2,'2商品',2,2,'2厂','2产地');
insert into 商品表 values(3,'3商品',3,3,'3厂','3产地');
insert into 商品表 values(4,'4商品',4,3,'4厂','4产地');
insert into 商品表 values(5,'5商品',5,5,'5厂','5产地');

insert into 会员表 values(11,'1会员','男',11,111);
insert into 会员表 values(22,'2会员','男',22,222);
insert into 会员表 values(33,'3会员','男',33,333);
insert into 会员表 values(44,'4会员','男',44,444);
insert into 会员表 values(55,'5会员','男',55,555);

insert into 订单表 values(11,1,1,1,'01-1月-2000');
insert into 订单表 values(22,2,1,1,'01-1月-2000');
insert into 订单表 values(33,3,1,1,'01-1月-2000');
insert into 订单表 values(44,4,1,1,'01-1月-2000');
insert into 订单表 values(55,5,1,1,'01-1月-2000');

二 对会员表表建立以姓名为索引关键字的降序索引。

create index ind_xm on 会员表(姓名 desc);

三 建立一个序列从1000开始，每次递增1。利用序列给会员表插入3条记录（序列作为会员号）。

create sequence seq_hyh  start with 1000 increment by 1;

insert into 会员表 values(seq_hyh.nextval,'6会员','男',66,666);
insert into 会员表 values(seq_hyh.nextval,'7会员','男',77,777);
insert into 会员表 values(seq_hyh.nextval,'8会员','男',88,888);

四 建立会员表的同义词customer。

create synonym customer for 会员表;

五 基于订单表建立视图，该视图可以统计各商品的的数量（以商品和数量作为结果列）。
create view view1
as select 商品号,sum(数量) as 总数量 from 订单表 group by 商品号;

六 基于订单表建立存储过程，通过商品的种类来统计商品的平均价格(要求
传入商品号，输出平均价格)。
create or replace procedure pjjg(sph in number,v_jg out number)
is
begin
select avg(单价) into v_jg from 订单表 where 商品号=sph;
end;

七 在商品表上创建一个触发器，当更新时候，商品的价格大于1000元的时候，价格下降
到90%，不大于1000的时候下降到95%。(scott下)
create or replace trigger spb_update
before update on 商品表
for each row
when (new.单价>old.单价)
begin
if :old.单价>1000 then
:new.单价:=0.9*:new.单价;
else
:new.单价:=0.95*:new.单价;
end if;
end;

在商品表上建立触发器，当对数据进行更新时，
记录更新的时间，动作（提示：建立一张表updatetime，包括更新时间和行为两个字段，
最后请更新一条记录验证触发器是否建立成功）。（10分）
create table updatetime
(opertime date,iperation varchar2(20));

create or replace trigger time_update
after update on 商品表
begin
if updating then
insert into updatetime values(sysdate,'update');
end if;
end;

/

八 定义一个游标访问商品表中的价格最高的前3条的信息（输出商品名，价格）。
 declare
 v_name varchar2(20);
 v_price number;
 cursor spb_cursor is select 商品名,单价 from 商品表 order by 单价 desc;
 begin
 open spb_cursor;
 for i in 1..3 loop
 fetch spb_cursor into v_name,v_price;
 dbms_output.put_line(v_name||','||v_price);
 end loop;
 close spb_cursor;
 end;
 /

九 定义一个函数如果商品的价格大于等于1500元,则显示“价格很贵”，否则 显示
“价格适中”(传入参数是商品名) 。
 create or replace function pri_ce
 (spm varchar2) return varchar2
 is
 price number;
 begin
 select 单价 into price from 商品表 where 商品名=spm;
 if price>=1500 then
 return '价格很贵';
 else
 return '价格适中';
 end if;
 end;
 /