2015-2016学年第二学期《大型数据库技术》大作业课题名称：超市管理系统
班级：
学号：
姓名：
成绩：
2016年5月
1数据库设计
新建一个用户表空间mahuihuang，大小为500m，表空间下有1个数据文件'E:\mahuihuang.dbf'文件放在E盘。
SQL> create tablespace mahuihuang datafile 'E:\mahuihuang.dbf' size 500m extent management local autoallocate;
2用户设计
1.创建一个数据管理人员账户，账户名为“mhh”临时表表空间是temp。
SQL> create user mhh identified by ma default tablespace mahuihuang temporary tablespace temp;
2.创建用户配置文件mhh_profile，密码的生存周期为30天，允许输入错误的密码不得超过5次，超过5次密码锁1天。
SQL> create profile mhh_profile limit password_life_time 30 failed_login_attempts 5 password_lock_time 1;
配置文件设置用户mhh
SQL> alter user mhh profile mhh_profile;
用户已更改。
SQL> select * from dba_profiles where profile='MHH_PROFILE';
3.为用户授权，同时登陆用户mhh (conn mhh/ma)
SQL> grant connect,resource to mhh;
授权成功。
SQL> grant create view,create table,DBA to mhh;
授权成功。
SQL> conn mhh/ma
已连接。
SQL> show user
USER 为"MHH"
3数据表设计
1.创建表
零售信息（编号，商品名称，商品类型，单价，销售时间，销售数量，销售金额）
mhh_lsxx(goodsno,goodsname,goodstype,goodsprice,selltime,sellcount,sellmone y)
create table mhh_lsxx (
goodsno char(5),
goodsname char(20),
goodstype char(20),
goodsprice int not null,
selltime date not null,
sellcount int not null,
sellmoney int not null,
constraint mhh_pk primary key (goodsno,goodsname) //编号列和商品名称列设置为联合主键
)
插入数据
SQL> insert into mhh_lsxx values(0001,'枇杷果','水果',34,to_date('2016-06-05','y yyy-mm-dd'),110,3740)
SQL> insert into mhh_lsxx values(0002,'恒大冰泉','饮料',5,to_date('2016-05-05',' yyyy-mm-dd'),120,600);
SQL> insert into mhh_lsxx values(0003,'五粮液','酒水',500,to_date('2015-05-25',' yyyy-mm-dd'),100,50000);
SQL> insert into mhh_lsxx values(0004,'100年润发','洗发水',50,to_date('2015-12-2 5','yyyy-mm-dd'),200,10000);
SQL> insert into mhh_lsxx values(0005,'欧莱雅','护肤品',100,to_date('2016-06-5', 'yyyy-mm-dd'),20,2000);
查询
SQL> select goodsno 编号,goodsname 商品名称from mhh_lsxx;
2.将表中数据按照商品类型进行分区。
创建两个表空间：
SQL> edit
已写入file afiedt.buf
create tablespace m_ts01 nologging datafile 'E:\m_ts01.def'size 100m
SQL> create tablespace m_ts02 nologging datafile 'E:\m_ts02.def'size 100m;
表空间已创建。
对商品类型进行分区，把酒水，饮料，水果分到m_ts01，洗发水，护肤品分到m_ts02空间中。(oracle 对表进行分区的时候，在创建的时候可以进行分区，如果创建后分区，该表需要是个分区表)。
SQL> drop table mhh_lsxx;//删过后重新创建分区
已写入file afiedt.buf
1 create table mhh_lsxx (
2 goodsno char(5),
3 goodsname char(20),
4 goodstype char(20),
5 goodsprice int not null,
6 selltime date not null,
7 sellcount int not null,
8 sellmoney int not null,
9 constraint mhh_pk primary key (goodsno,goodsname)
10 )
11 partition by list(goodstype)
12 (
13 partition m_1 values('水果','饮料','酒水')tablespace m_ts01,
14 partition m_2 values('洗发水','护肤品')tablespace m_ts02
15* )
联合查询m_1，m_2分区数据。
SQL> select * from mhh_lsxx partition(m_1)
2 union
3 select * from mhh_lsxx partition(m_2);
3.将表中的销售金额列删除，改为使用视图实现销售金额的存储和查询。
SQL> alter table mhh_lsxx drop column sellmoney;
创建视图存储
create or replace view m_view as
select (goodsprice*sellcount) sellmoney from mhh_lsxx；
4使用函数实现输入指定商品类别，输出该类别的总销售金额。
create or replace function get_money(gname char) return char is sellmoney number;
begin
select (goodsprice*sellcount) into sellmoney from mhh_lsxx
where goodsname=gname;
return sellmoney;
end;
SQL> set serveroutput on;//运行函数
declare
m_sellmoney number;
begin
m_sellmoney:=get_money('枇杷果');
dbms_output.put_line('枇杷果的销售金额：'||m_sellmoney);
end;
枇杷果的销售金额：3740
5.使用存储过程实现输入指定商品名称，输出该商品的总销售数量和销售金额。（表重新建的，因为前面删除了一列）
create or replace procedure select_sm(
gname in mhh_lsxx.goodsname%type,
scount out mhh_lsxx.sellcount%type,
smoney out mhh_lsxx.sellmoney%type)is
begin
select sellcount,sellmoney into scount,smoney from mhh_lsxx where goodsname=gname;
exception
when no_data_found then
dbms_output.put_line('该商品不存在');
end select_sm;
declare
scount mhh_lsxx.sellcount%type;
smoney mhh_lsxx.sellmoney%type;
begin
select_sm('枇杷果',scount,smoney);
dbms_output.put_line('销售数量：'||scount||'销售金额；'||smoney);
end;
6.从2016年开始，将“零售信息”表中的数据存入新的表“2016年销售情况”，请给出数据转存的方案和关键代码。
SQL> edit
已写入file afiedt.buf
create table mhh_newlsxx as select * from mhh_lsxx where selltime>to_date('2016-01-01','yyyy-mm-dd')
表已创建。
SQL> select * from mhh_newlsxx;
7.现有的超市前台系统仍然将数据写入“零售信息”表，请使用触发器实现“2016年销售情况”表中数据的同步更新。
SQL> edit
已写入file afiedt.buf
create or replace trigger ma_update
after insert on mhh_lsxx
for each row
begin
insert into mhh_newlsxx(goodsno,goodsname,goodstype,goodsprice,selltime,sel lcount,sellmoney)
values(:new.goodsno,:new.goodsname,:new.goodstype,:new.goodsprice,:new.sell time,:new.sellcount,:new.sellmoney);
end ma_update;
SQL>insert into mhh_lsxx values(0006,'古龙','香水',100,to_date('2016-06-5','yyyy-mm-dd'),2,200);
已创建 1 行。
SQL> select * from mhh_lsxx;
SQL> select * from mhh_newlsxx;