Oracle 练习题（包含物化视图，索引，分区，查询，触发器自增列）

--1，商品
create  table shangpin(
        spnum  number(30)  generated always  as   identity(
        start with 1  increment  by 1  maxvalue  99  minvalue  1 order)   ,
        typenum  number(30) ,
        spname  varchar2(200) not null,
        spmoney  number(30)  not null     
)
--分区
partition  by hash(spnum)(
             partition  p_hash_spnum1,
             partition  p_hash_spnum2,
             partition  p_hash_spnum3,
             partition  p_hash_spnum4        
    )  
--加主键
alter  table  shangpin add  constraint  constraint_sp  primary key(spnum)
--建外键
alter  table  shangpin add  foreign key(typenum)  references sptype(typenum) 
insert into shangpin(
                     typenum,
                     spname,
                     spmoney)
              values(100000085,'sf',131)
--2，客户
create  table kehu(
        khnum  number(30) generated by  default  on  null  as  identity (
         start with 1  increment  by 1  maxvalue  99  minvalue  1 order),       
        khname  varchar2(200) not null,
        khdiqu  number(30) ,
        chuqijine  number(30),
        kaihuyinhag  varchar2(200) ,
        shuihao   varchar2(200) ,
        lianxiren  varchar2(200),
        dianhua    number(30),
        shouji     number(30),
        zhujima    varchar2(200)
)
--加主键
alter  table  kehu add    primary key(khnum)
partition  by hash(khdiqu)(
             partition  hash_khdiqu1,
             partition  hash_khdiqu2,
             partition  hash_khdiqu3,
             partition  hash_khdiqu4，         
);
insert into kehu(
                 khname,
                 khdiqu,
                 chuqijine,
                 kaihuyinhag,
                 shuihao,
                 lianxiren,
                 dianhua,
                 shouji,
                 zhujima)
         values ('宋扬','北京'，50,'建行','a00002','宋扬',123456,123456,'a2')
--3，用户
create  table yonghu(
        yhnum  number(30)  generated always  as   identity(
        start with 1  increment  by 1  maxvalue  99  minvalue  1 order),
        yhzhanghao  varchar2(200)           
)
alter  table  yonghu add    primary key(yhnum)
插入
insert into yonghu(yhzhanghao)  values('所发生的')
--4，货站
create  table huozhan(
        hznum  number(30) generated always  as   identity(
        start with 1  increment  by 1  maxvalue  99  minvalue  1 order),
        hzname  varchar2(200) not null,
        lianxiren  varchar2(200),
        dianhua    number(30),
        shouji     number(30),
        zhujima    varchar2(200)     
)
alter  table  huozhan add    primary key(hznum)
insert into huozhan(
                    hzname,
                    lianxiren,
                    dianhua,
                    shouji,
                    zhujima)
           values('尔特','124',412,456,'adsa')
--5，仓库
create  table cangku(
        ccnum  varchar2(200) primary key,
        ccdizhi    varchar2(200)     not  null ,
        zhujima    varchar2(200)       
)
--创建序列
create sequence  sq_cangku  
start  with 1
increment  by  1
minvalue 1
maxvalue  200
cache  30  order
--仓库的触发器
create or replace trigger trck_pk
  before insert
  on cangku 
  for each row
declare
  -- local variables here
begin
  :new.ccnum:='insert'|| seq_demo1_pk.nextval;
end tr_stu_pk;
--插入
insert into cangku(
                   ccdizhi,
                   zhujima)
           values('ad','sdas')  
--6，库存
create  table kucun(
        kcnum  number(30) primary key,
        spnum  number(30) ,
        ccnum  varchar2(200) ,
        chuliang  number(30)  not null     
)
--分区
partition  by hash(spnum，ccnum)(
             partition  p_hash_spnum1,
             partition  p_hash_spnum2,
             partition  p_hash_cknum3,
             partition  p_hash_cknum4        
    )
--外键
alter table kucun  add  foreign key(spnum)  references  shangpin (spnum) 
alter table kucun  add  foreign key(ccnum)  references  cangku (ccnum) 
--库存的触发器
create or replace trigger tr_kucun_pk
  before insert
  on kucun 
  for each row
declare
  -- local variables here
begin
  :new.kcnum:=sq_kucun.nextval;
end tr_kucun_pk;
--插入
insert into kucun(
                  spnum,
                  ccnum,
                  chuliang)
           values(2,'insert100000063',12)
--7，销售
create  table xiaoshou(
        xsjlnum  varchar2(200) primary key,
        kcnum  number(30) ,
        shuliang number(30) not null,
        lianxiren  number(30)  not null,
        shouji        number(30)  not null
)
partition  by hash(kcnum)(
             partition  p_hash_kcnum1,
             partition  p_hash_kcnum2,
             partition  p_hash_kcnum3,
             partition  p_hash_kcnum4        
    )
--外键？
alter table xiaoshou  add  foreign key(kcnum)  references  kucun(kcnum)
--销售触发器     
create  or replace trigger  tr_xs_pk
before insert  on xiaoshou 
for  each   row  
begin
  :new.xsjlnum:=sq_cangku.nextval;
插入
insert into xiaoshou(
                     kcnum,
                     shuliang,
                     lianxiren,
                     shouji)
                     values(2,15,15,1)
--8，商品类型
create  table sptype(
        typenum number(30) primary key,
        typename  varchar2(200) not null,
        zhujima    varchar2(200) 
)
--触发器
create or replace trigger tr_sptype_pk
  before insert
  on sptype 
  for each row
declare
  -- local variables here
begin
  :new.typenum:= seq_demo1_pk.nextval;
end tr_stu_pk;
插入语句
insert into sptype(
                   typename,
                   zhujima)                   
values('sd','sad')    
触发器
1，下半年禁止删除仓库表
create or replace trigger tr_nodel
  before delete
  on student 
  for each row
begin
  if  deleting and (to_char(sysdate,'MM'))>6
   then raise_application_error(-20001,'下半年禁止删除仓库表数据');
   end if;
end tr_nodel;
2，结算期禁止修改商品表
create or replace trigger tr_update_sp
  before update
  on shangpin 
  for each row
begin
  if  to_char(sysdate,'mm/dd')  between '12/28' and '12/31'
    then raise_application_error(-20001,'结算期禁止修改商品表');
    end if;
end tr_update_sp;
3，工作日外禁止修改表
create or replace trigger tr_update_yh
  before update
  on yonghu 
  for each row
begin
  if  to_char(sysdate,'D')  ='1'
     then   raise_application_error(-20002,'工作日外禁止修改用户表');
       elsif to_char(sysdate,'D')  ='7'
             then   raise_application_error(-20003,'工作日外禁止修改用户表');
    end if;
end tr_update_yh;
4,
上班时间外禁止对货站表任何操作
create or replace trigger tr_all_hz
  before insert or update or delete
  on huozhan 
  for each row
begin
  if inserting  and to_char(sysdate,'hh24') in(18,19,80,21,22,23,24,1,2,3,4,5,6,7)
   then raise_application_error(-20003,'上班时间外禁止对货站表任何操作');
  elsif updating  and to_char(sysdate,'hh24') in(18,19,80,21,22,23,24,1,2,3,4,5,6,7)
   then raise_application_error(-20003,'上班时间外禁止对货站表任何操作');
  elsif deleting  and to_char(sysdate,'hh24') in(18,19,80,21,22,23,24,1,2,3,4,5,6,7)
   then raise_application_error(-20001,'上班时间外禁止对货站表任何操作');
   end if;
end tr_all_hz;
5，记录对库存的操作。
create or replace trigger tr_kc_jl
  before insert or update or delete
  on kucun 
  for each row
begin
  if inserting  and  to_char(sysdate,'hh24')  in(18,19,20,21,22,23,24,1,2,3,4,5,6,7)
    then 
      insert into jilu values('insert'||:new.kcnum||:new.spnum||:new.ccnum||:new.chuliang,sysdate);    
 elsif updating  and  to_char(sysdate,'hh24')  in(18,19,20,21,22,23,24,1,2,3,4,5,6,7)
    then 
      insert into jilu values('updating'||:old.kcnum||:old.spnum||:old.ccnum||:old.chuliang,sysdate);
   elsif deleting  and  to_char(sysdate,'hh24')  in(18,19,20,21,22,23,24,1,2,3,4,5,6,7)
    then 
      insert into jilu values('deleting'||:old.kcnum||:old.spnum||:old.ccnum||:old.chuliang,sysdate);     
   end if;
end tr_kc_jl;
--物化视图
create materialized view  mv_ztjb
refresh  complete on demand
start with sysdate
next sysdate+30
with primary key
as 
 select sp.spname  商品名称,t.typename  类型名称,
 sum(x.shuliang)  销售数量, sum(k.chuliang) 储量 ,
  sum(spmoney*shuliang) 营业额
    from xiaoshou  x 
    join kucun k on x.kcnum=k.kcnum 
     join shangpin sp on k.spnum=sp.spnum 
     join sptype t on sp.typenum=t.typenum
     group by sp.spname,t.typename
     order by 营业额 desc
--2
create materialized view  mv_dqtjb
refresh  complete on demand
start with sysdate
next sysdate+30
with primary key
as 
 select distinct c.ccdizhi 仓库地址 ，sp.spname 商品名称，
 t.typename  类型名称, sp.spmoney 商品金额, 
 sum(x.shuliang)  销售数量  ,k.chuliang 该库剩余
  from xiaoshou x full  join  kucun k on x.kcnum=k.kcnum
  full join shangpin sp on sp.spnum=k.spnum 
  full join sptype t on t.typenum=sp.typenum
  full join cangku c on c.ccnum=k.ccnum
  group by c.ccdizhi,sp.spname,t.typename,sp.spmoney,
  k.chuliang  order by   仓库地址
--索引
create bitmap index  in_bm_tname on 
 mv_ztjb(类型名称);
create  index  in_bm_xszl on 
 mv_ztjb(销售数量);
create bitmap index  in_bm_ckdz on 
 mv_dqtjb(仓库地址);
create bitmap index  in_bm_lxmc on 
 mv_dqtjb(类型名称);
create  index  in_bm_xssl on 
 mv_dqtjb(销售数量 desc);
--Dql
1.1
select k.chuliang,k.kcnum from kucun k
left  join shangpin s on s.spnum=k.kcnum
where s.spmoney in (select  min(s.spmoney) from shangpin) 
2.1
select distinct k.kcnum from kucun k,xiaoshou x
where k.kcnum <> x.kcnum 
1.2
select * from (select d.类型名称 from mv_dqtjb d  group by d.类型名称
order by sum(d.销售数量)  desc)
where rownum=1;
2.2
前3个登记(商品编号排在前三的)的商品销量分别为多少
select kc.shangpbh,sum(xs.shul)
from xiaos xs join kuc kc on xs.kucbh=kc.kucbh
where kc.shangpbh in(
  select * from (
    select sp.shangpbh spbh
    from shangp sp
    order by spbh)
  where rownum<4)
group by kc.shangpbh
order by sum(xs.shul) desc
1.3
哪个仓库商品数量最多
select * from (
  select dqtjb.仓库地址
  from mv_dqtjb dqtjb
  where dqtjb.仓库地址 is not null
  group by dqtjb.仓库地址
  order by sum(dqtjb.该库剩余) desc
) where rownum=1;


--老师版本
商品表最便宜商品的各仓库储量是多少
select kc.cangkbh,kc.chul 
from kuc kc left join shangp sp on kc.shangpbh = sp.shangpbh
where sp.shangpje in (
select min(shangpje) from shangp
)
 
全国哪种类型商品销量最高
select * from (
  select dqtjb.类型名称
  from mv_dqtjb dqtjb
  group by dqtjb.类型名称
  order by sum(dqtjb.销售数量) desc
) where rownum=1;
 
 
哪个仓库商品数量最多
select * from (
  select dqtjb.仓库地址
  from mv_dqtjb dqtjb
  where dqtjb.仓库地址 is not null
  group by dqtjb.仓库地址
  order by sum(dqtjb.该库剩余) desc
) where rownum=1;
 
 
仓库中未出售过的商品编号有哪些
  select distinct dqtjb.商品名称
  from mv_dqtjb dqtjb
  where dqtjb.仓库地址 is not null and dqtjb.销售数量 is null
 
 
 
前3个登记(商品编号排在前三的)的商品销量分别为多少
select kc.shangpbh,sum(xs.shul)
from xiaos xs join kuc kc on xs.kucbh=kc.kucbh
where kc.shangpbh in(
  select * from (
    select sp.shangpbh spbh
    from shangp sp
    order by spbh)
  where rownum<4)
group by kc.shangpbh
order by sum(xs.shul) desc
 
 
 
前3个入库(库存编号排在前三的)的商品销量分别为多少 
 
select kc.shangpbh,sum(xs.shul)
from xiaos xs join kuc kc on xs.kucbh=kc.kucbh
where kc.kucbh in(
  select * from (
    select kc.kucbh kcbh
    from kuc kc
    order by kcbh)
  where rownum<4)
group by kc.shangpbh
order by sum(xs.shul) desc

--补自己的
--全国哪种销量最高
select  * from (select m.类型名称 from mv_ztjb m group by m.类型名称 order by sum(m.销售数量) desc)
where  rownum=1
--前3个登记的商品销量分别为多少
select  x.shuliang  from xiaoshou  x left join kucun k on x.kcnum=k.kcnum
where  rownum  <4
--哪个仓库商品数量最多
select *  from (select d.仓库地址  from mv_dqtjb d
 where d.仓库地址 is not null group by d.仓库地址
 order by sum(d.该库剩余)) 
where  rownum=1 
--前3个入库的商品销量分别为多少
select sum(x.shuliang),k.spnum  from xiaoshou x  join kucun k on k.kcnum=x.kcnum
where k.kcnum in (select *  from (select k.kcnum from kucun k  order by k.kcnum) where rownum<4)
group by  k.spnum 
--仓库中未出售过的商品编号有哪些
select dis b.商品名称 from mv_dqtjb b  where b.仓库地址 is not null and b.销售数量 is null

练习时候出现的问题：
1，在建立hash分区的时候要和表一起创建，

2，在使用12c以后的方法写触发器，在建立表的时候直接创建，如果使用default 要先建立序列。然后后写语句加主键。

3，所有的外键都在创建完主键以后后加。防止出错。

4，触发器写的时候，不要忘了最后的结尾 end

5，触发器的 时间判断，用to_char(sysdate,’…’) 格式来进行判断，选取

6，往 另一个表插入值的时候 注意格式 ：new. :old

7，创建物化视图的时候。默认是with primary key 方便查。as select 里面的查询，判断好使用什么类型的查询。内联是两个表里面有

相同的字段。全连是根据需求把一些非空等别的值显示出来，

8，索引，根据物化视图创建，边界，on 索引名（索引里的字段名）

9，查询的时候根据需求去选择是不是用视图，如果视图是好多表查询，就没必要使用，不如自己写。

10， 多表的连接 from 表明 join 表名 on 条件 join 表明 on 条件，需要的字段直接从前面的表里获取。

11，分组： 查询里出现了非聚合的字敦就把这些字段分组。

12，使用触发器做自增的时候千万不要用一个序列，不要懒！如果都用一个序列的话，，下一个表会接上一个表继续，比如说第一个是1，2，下一个表如果还用这个序列，他的开始记就是3，4