2015-oracle超市管理系统

2015-2016学年第二学期《大型数据库技术》大作业课题名称：超市管理系统班级：学号：姓名：成绩：2016年5月11数据库设计新建一个用户表空间mahuihuang，大小为500m，表空间下有1个数据文件'E:\mahuihuang.dbf'文件放在E盘。SQLcreatetablespacemahuihuangdatafile'E:\mahuihuang.dbf'size500mextentmanagementlocalautoallocate;2用户设计1.创建一个数据管理人员账户，账户名为“mhh”临时表表空间是temp。SQLcreateusermhhidentifiedbymadefaulttablespacemahuihuangtemporarytablespacetemp;2.创建用户配置文件mhh_profile，密码的生存周期为30天，允许输入错误的密码不得超过5次，超过5次密码锁1天。SQLcreateprofilemhh_profilelimitpassword_life_time30failed_login_attempts5password_lock_time1;配置文件设置用户mhhSQLalterusermhhprofilemhh_profile;用户已更改。SQLselect*fromdba_profileswhereprofile='MHH_PROFILE';23.为用户授权，同时登陆用户mhh(connmhh/ma)SQLgrantconnect,resourcetomhh;授权成功。SQLgrantcreateview,createtable,DBAtomhh;授权成功。SQLconnmhh/ma已连接。SQLshowuserUSER为MHH3数据表设计1.创建表零售信息（编号，商品名称，商品类型，单价，销售时间，销售数量，销售金额）3mhh_lsxx(goodsno,goodsname,goodstype,goodsprice,selltime,sellcount,sellmoney)createtablemhh_lsxx(goodsnochar(5),goodsnamechar(20),goodstypechar(20),goodspriceintnotnull,selltimedatenotnull,sellcountintnotnull,sellmoneyintnotnull,constraintmhh_pkprimarykey(goodsno,goodsname)//编号列和商品名称列设置为联合主键)插入数据SQLinsertintomhh_lsxxvalues(0001,'枇杷果','水果',34,to_date('2016-06-05','yyyy-mm-dd'),110,3740)SQLinsertintomhh_lsxxvalues(0002,'恒大冰泉','饮料',5,to_date('2016-05-05','yyyy-mm-dd'),120,600);SQLinsertintomhh_lsxxvalues(0003,'五粮液','酒水',500,to_date('2015-05-25','yyyy-mm-dd'),100,50000);SQLinsertintomhh_lsxxvalues(0004,'100年润发','洗发水',50,to_date('2015-12-25','yyyy-mm-dd'),200,10000);SQLinsertintomhh_lsxxvalues(0005,'欧莱雅','护肤品',100,to_date('2016-06-5','yyyy-mm-dd'),20,2000);查询SQLselectgoodsno编号,goodsname商品名称frommhh_lsxx;42.将表中数据按照商品类型进行分区。创建两个表空间：SQLedit已写入fileafiedt.bufcreatetablespacem_ts01nologgingdatafile'E:\m_ts01.def'size100mSQLcreatetablespacem_ts02nologgingdatafile'E:\m_ts02.def'size100m;表空间已创建。对商品类型进行分区，把酒水，饮料，水果分到m_ts01，洗发水，护肤品分到m_ts02空间中。(oracle对表进行分区的时候，在创建的时候可以进行分区，如果创建后分区，该表需要是个分区表)。SQLdroptablemhh_lsxx;//删过后重新创建分区已写入fileafiedt.buf1createtablemhh_lsxx(2goodsnochar(5),3goodsnamechar(20),4goodstypechar(20),5goodspriceintnotnull,6selltimedatenotnull,7sellcountintnotnull,8sellmoneyintnotnull,9constraintmhh_pkprimarykey(goodsno,goodsname)10)511partitionbylist(goodstype)12(13partitionm_1values('水果','饮料','酒水')tablespacem_ts01,14partitionm_2values('洗发水','护肤品')tablespacem_ts0215*)联合查询m_1，m_2分区数据。SQLselect*frommhh_lsxxpartition(m_1)2union3select*frommhh_lsxxpartition(m_2);3.将表中的销售金额列删除，改为使用视图实现销售金额的存储和查询。6SQLaltertablemhh_lsxxdropcolumnsellmoney;创建视图存储createorreplaceviewm_viewasselect(goodsprice*sellcount)sellmoneyfrommhh_lsxx；4使用函数实现输入指定商品类别，输出该类别的总销售金额。createorreplacefunctionget_money(gnamechar)returncharissellmoneynumber;beginselect(goodsprice*sellcount)intosellmoneyfrommhh_lsxxwheregoodsname=gname;returnsellmoney;end;SQLsetserveroutputon;//运行函数declarem_sellmoneynumber;beginm_sellmoney:=get_money('枇杷果');dbms_output.put_line('枇杷果的销售金额：'||m_sellmoney);end;7枇杷果的销售金额：37405.使用存储过程实现输入指定商品名称，输出该商品的总销售数量和销售金额。（表重新建的，因为前面删除了一列）createorreplaceprocedureselect_sm(gnameinmhh_lsxx.goodsname%type,scountoutmhh_lsxx.sellcount%type,smoneyoutmhh_lsxx.sellmoney%type)isbeginselectsellcount,sellmoneyintoscount,smoneyfrommhh_lsxxwheregoodsname=gname;exceptionwhenno_data_foundthendbms_output.put_line('该商品不存在');endselect_sm;8declarescountmhh_lsxx.sellcount%type;smoneymhh_lsxx.sellmoney%type;beginselect_sm('枇杷果',scount,smoney);dbms_output.put_line('销售数量：'||scount||'销售金额；'||smoney);end;6.从2016年开始，将“零售信息”表中的数据存入新的表“2016年销售情况”，请给出数据转存的方案和关键代码。SQLedit已写入fileafiedt.bufcreatetablemhh_newlsxxasselect*frommhh_lsxxwhereselltimeto_date('2016-01-01','yyyy-mm-dd')表已创建。SQLselect*frommhh_newlsxx;97.现有的超市前台系统仍然将数据写入“零售信息”表，请使用触发器实现“2016年销售情况”表中数据的同步更新。SQLedit已写入fileafiedt.bufcreateorreplacetriggerma_updateafterinsertonmhh_lsxxforeachrowbegininsertintomhh_newlsxx(goodsno,goodsname,goodstype,goodsprice,selltime,sellcount,sellmoney)values(:new.goodsno,:new.goodsname,:new.goodstype,:new.goodsprice,:new.selltime,:new.sellcount,:new.sellmoney);endma_update;10SQLinsertintomhh_lsxxvalues(0006,'古龙','香水',100,to_date('2016-06-5','yyyy-mm-dd'),2,200);已创建1行。SQLselect*frommhh_lsxx;SQLselect*frommhh_newlsxx;