Oracle物流信息管理系统.doc

1、2015-2016 学年第二学期大型数据库技术 大作业课题名称：物流信息管理系统班 级： 14 级软工云计算 2 班 学 号： 姓 名： 成 绩： 2016 年 5 月一）ORACLE11g 的安装与配置（20 分）在自己的电脑上配置安装 ORACLE 11g，并创建数据库。具体要求如下：（1）安装版本为企业版（2）在安装时， 只安装 Oralce 服务器。在安装完成后再创建数据库。安装数据库软件（3）利用 DBCA 创建数据库，数据库名称为自己姓名的拼音首字母，如某学生的姓名为“张三”，则数据库的名称则为 zs。在 CMD 下面输入 DBCA 就可以弹出创建数据库的对话框,是那种图示化的界面

2、创建数据库设置数据库名称：XP设置所有用户密码为我的学号：1415925675（4）数据库为共享数据库。（5）创建数据库时选择示例模式。数据库创建中数据库创建完成（6）数据库所有的用户使用相同的口令，口令为学生的学号。登录到 system 用户中登录到 scott 用户中（二）用户的创建与权限管理（20 分）（1）新建一个用户表空间 MYTBS，要求大小为 500M。MYTBS 表空间下有两个数据文件 MYTBS1.DBF 和 MyTBS2.DBF，文件分别放在 E 和 F 两个不同的磁盘分区。（因为在虚拟机里安装的 oracle11g 所以两个文件都放在了 C盘里）SQL connect s

3、ystem/1415925675已连接。SQL create tablespace mytbs datafile c:mytbsl.dbf size 30m,c:mytbs2.dbf size 20m;表空间已创建。（2）创建用户配置文件 userprofile，要求密码的生存周期为 30 天，允许输入错误的密码不得超过 5 次，超过 5 次密码锁 2 天。（用语句查看该配置文件中的资源配置结果，dba_profiles 数据字典表，截图语句及查询结果）SQL create profile lock_count limit password_life_time 30 failed_login_

4、attempts5 password_lock_time 2;配置文件已创建SQL select * from dba_profiles where profile=LOCK_COUNT;（3）新建一个用户，用户名称为学生姓名的首字母，密码为学生的学号。如学生姓名为“张三”，则用户名称则为 zs。为新帐号设置默认表空间为MYTBS。系统所有对象必须建立在该帐号下，所有对象必须存储在 MYTBS 表空间中。用户的配置文件为 userprofile; （用语句查看新建的用户默认表空间，dba_users 数据字典表，截图语句及查询结果）SQL create user xp identified b

5、y 1415925675 default tablespace MYTBS;用户已创建。SQL grant connect,resource to xp;授权成功。SQL alter user xp profile lock_count;用户已更改。SQL conn xp/1415925675;已连接。SQL show user;USER 为 “XP“SQL select username,account_status,lock_date,expiry_date from dba_users a wherea.username=XP;USERNAME ACCOUNT_STATUS LOCK_D

6、ATE- - -EXPIRY_DATE-XP OPEN27-6 月 -16（4）为用户赋予登录 Oracle 的权限；为用户赋予创建任何表、创建任何索引的权限；将 DBA 的角色给新建用户；查看对应的用户获得的权限和角色（用数据字典表 user_sys_privs,user_tab_privs,user_role_等，截图语句及查询结果）SQL grant connect,resource to xp;授权成功。SQL select username,default_tablespace from user_users;SQL select * from user_sys_privs;SQL

7、select * from user_tab_privs;SQL select * from user_role_privs;（5）用新建的帐号登录。并执行 show user 命令，显示当前登录用户。完成以上命令，并写入文档中。（三）Oracle 对象的创建与管理（20 分）物流管理系统1.需求分析该系统目标就是为物流业提供方便快捷管理方式。主要设计：建立业务信息、货物信息、卖家信息、消费者信息；完成业务信息、货物信息、卖家信息、消费者信息；业务实体包括操作员、发货城市、发货站、费用、接洽时间、收货站、收货城市，一个收货人选择业务，收货人包括姓名、身份证号码、年龄、电话、地址，一个收货人要网

8、购一种货物，一个货物有标签、名称、体积、重量、包装类型。货物由发货人统一发送，发货人包括姓名、身份证号码、电话、地址。2.数据库设计卖家信息（ID 号，姓名，年龄，电话，住址）； 货物信息（货物名，货号，体积，重量，包装类型）； 消费者信息（ID 号，姓名，年龄，电话，住址）； 业务信息（业务员号，接洽时间，发货站，发货城市，收货站，收货城市）； 选择信息（选择号，业务员号，消费者 ID 号，货物名，费用）； 运输信息（运输号，卖家 ID 号，货物名，发货时间）Seller（ ID 号，姓名，年龄，电话，住址）； SQL edit已写入 file afiedt.buf1 create tabl

9、e x_Seller(2 SeID char(20) primary key,3 Sname char(10)not null,4 Age char(10),5 Tell char(20) not null,6 Adr char(20)not null7* )8SQL /表已创建。Goods（货物名，货号，体积，重量，包装类型）；SQL edit已写入 file afiedt.buf1 create table x_Goods(2 Gname char(10) primary key,3 Gno char(10) not null,4 Volume char(10),5 Weight char

10、(10)，6 Pack char(10)7* )SQL /表已创建。Consume（ID 号，姓名，年龄，电话，住址）； SQL edit已写入 file afiedt.buf1 create table x_Consume(2 CID char(20) primary key,3 Cname char(10)not null,4 Age char(10),5 Tell char(20) not null,6 Adr char(20)7* )SQL /表已创建。Business（业务员号，接洽时间，发货站，发货城市，收货站，收货城市）； SQL edit已写入 file afiedt.buf1

11、 create table x_Business(2 Ono char(4) primary key,3 Jtime char(10) not null,4 Fhz char(10) not null,5 Fhcity char(10) not null,6 Shz char(10) not null,7 Shcity char(10) not null8* )9 ;表已创建。Select（选择号，业务员号，消费者 ID 号，货物名，费用）； SQL edit已写入 file afiedt.buf1 create table x_Select(2 Xno char(10),3 Ono char

12、(4),4 CID char(20),5 Gname char(10),6 Cost char(10),7 primary key (Xno,Ono,CID,Gname),8 foreign key (Ono)references x_Business(Ono),9 foreign key (CID)references x_Consume(CID),10 foreign key (Gname)references x_Goods(Gname)11* )SQL /表已创建。Transport（运输号，卖家 ID 号，货物名，发货时间）SQL edit已写入 file afiedt.buf1 c

13、reate table x_Transport(2 Yno char(10),3 SeID char(20),4 Gname char(10),5 Ftime char(10) not null,6 primary key(Yno,SeID,Gname),7 foreign key (SeID)references x_Seller(SeID),8 foreign key (Gname)references x_Goods(Gname)9* )SQL /表已创建。3.添加数据信息添加卖家信息已写入 file afiedt.buf1 insert into x_Seller(SeID,Sname

14、,Age,Tell,Adr)2* values(20162016,徐棚,22,15238101067,南阳理工学院)添加消费者信息insert into x_Consume values(20172017,许仙,21,15238101066,东南山)insert into x_Goods values(、,1415925,2,3kg, 精装)insert into x_Business values(110,2016525,圆通站 1,郑州 ,圆通站 2,南京)insert into x_Select values(0,110,20172017,枇杷果,500$)insert into x_T

15、ransport values(2016618,20162016,枇杷果,2016520)4.建立索引由于上面好多表创建的同时设置的有主键，创建主键的时候会创建同名索引SQL create index sy1 on x_Transport(Ftime);5.视图创建SQL create view xb as select Tell,Adr from x_Seller where Sname=徐棚;SQL edit已写入 file afiedt.buf1 create view xb2(SeID,Sname,Gname)2 as3 select x_Seller.SeID,x_Seller.Sn

16、ame,x_Transport.Gname4 from x_Seller,x_Transport5* where x_Seller.SeID=x_Transport.SeIDSQL edit已写入 file afiedt.buf1 create view xb3(Ono,Fhcity,CID,Cost)2 as3 select x_Business.Ono,x_Business.Fhcity,x_Select.CID,x_Select.Cost4 from x_Business,x_Select5* where x_Business.Ono=x_Select.OnoSQL /（四）PL/SQL

17、 编程（20 分）系统功能必须通过函数、存储过程、触发器或包实现。至少创建一个函数、一个触发器（实现级联更新功能）、2 个过程。不能用程序设计语言实现。先描述功能，再写实现代码。例如：用户登录验证，可以创建一个存储过程，输入参数是用户帐号、用户密码，输出参数是整型值。 -1 表示登录失败， 0 表示没有此用户，1 表示登录成功。SQL create user xbp identified by 1415925675 account unlock;grant create session,create table,create view,create any procedure,create r

18、ole,create sequence to xbp;连接 xbp 用户，然后建立四张表（关于学生管理系统的表）create table studentInfo(stuID char(10) primary key,stuName char(20) not null,sex char(8)not null,spec char(20)not null,class char(20)not null,brithday char(20)not null,address char(20)not null)SQL insert into studentInfo values(20161,路人甲,女,软件学

19、院,云计算 2 班,1994- 5-25,南阳);建立 courses 表create table Courses (courID char(10) primary key,courName char(15) not null,creditHour number(2,0) not null,creditScore number(3,1)not null);insert into courses values(1201,数据库一班,26,2.0);insert into courses values(1202,软件设计一班,32,2.5);insert into courses values(1

20、203,数据库二班,26,2.0);insert into courses values(1204,软件设计二班,32,3.0);insert into courses values(1205,数据库计三班,26,2.0);建立 scores create table scores (stuID char(10),stuName char(20) not null,class char(20) not null, courID char(10),usualScore number(3,1) not null,testScore number(3,1) not null,constraint s

21、cores_stuID foreign key(stuID) references studentInfo(stuID), constraint scores_courseID foreign key(courID) references courses(courID)insert into scores values(201301,李力,软件设计一班,1202,90.0,95.0);insert into scores values(201302,王雪,软件设计二班,1204,95.0,96.0);insert into scores values(201303,李冰,数据库一班,1201,

22、80.0,90.0);insert into scores values(201304,张丽,数据库二班,1203,85.0,90.0);insert into scores values(201305,赵水,数据库计三班,1205,60.0,95.0);建立 users 表insert into users values(student,14159256,学生);insert into users values(admin,14159256,管理员);insert into users values(teacher,14159256,教师);创建一个陆的存储过程，判断登陆用户的权限，包括学生

23、、教师、系统管理员。 create or replace procedure login(p_name in users.userName%type,p_password in users.password%type)Asv_password users.password%type; /*判别输入的密码是否正确*/v_usertype users.usertype%type; /*判别该用户的管理等级*/beginselect password,usertype into v_password,v_usertype from users where userName=p_name; /*核对用

24、户是否存在*/beginif v_password=p_password then /*验证密码*/beginif v_usertype=学生 then dbms_output.put_line(学生|p_name|登陆成功); elsif v_usertype=教师 then dbms_output.put_line(教师|p_name|登陆成功); elsif v_usertype=系统管理员 then dbms_output.put_line(系统管理员|p_name|登陆成功);end if;end;else dbms_output.put_line(密码输入错误，该用户登录失败);e

25、nd if;end;exception when no_data_found then dbms_output.put_line(该用户不存在!); end login;SQL set serveroutput onSQL declare2 begin3 login(student,14159256);4 end;5 /学生 student 登陆成功PL/SQL 过程已成功完成。（五）Oracle 备份与导出（20 分）利用备份和还原的有关知识。设置数据库的备份方案。设计数据库备份和恢复的方法，并写出相关的命令和操作。（1）设置归档模式。查看当前文档归档模式SQL select log_mod

26、e from v$database;SQL shutdown immediate;SQL startup mount;（2）归档当前日志。（3）数据库完全备份及实现。数据库查询文件 datafile 的所在目录SQL select name from v$datafile;关闭数据库，可以直接拷贝数据库所有文件shutdown immediate;（4）数据库完全恢复及实现。recover datafile （5）利用数据泵实现逻辑导出与导入。SQL create directory dump_dir as c:dump;SQL grant write,read on directory du

27、mp_dir to xp;SQL select table_name from user_tables;C:UsersXPexpdp xp/1415925675 directory=dump_dir dumpfile=tab.dmp tables=X_GOODSC:UsersXPimpdp xp/1415925675 directory=dump_dir dumpfile=tab.dmp tablespaces=X_GOODS（6）举例说明闪回技术使用（闪回查询、闪回表、闪回删除等）。在 xp 里新建一个 emp 表SQL create table emp(id int,name char(2

28、0) );SQL conn / as sysdba已连接。SQL grant execute on dbms_flashback to xp;授权成功。闪回查询：1、 alter database flashback on;SQL set time on;17:54:46 SQL select * from emp as of timestamp to_timestamp(2016-5-30 17:52:26,YYYY-MM-DD HH24:MI:SS);ID NAME- -1 aaaaa闪回表：17:58:49 SQL delete from emp;已删除 1 行。17:59:24 SQL alter table emp enable row movement;表已更改。18:00:03 SQL flashback table emp to timestamp to_timestamp(2016-5-30 17:58:49,yyyy-mm-ddhh24:mi:ss);闪回完成。闪回删除：18:02:30 SQL drop table emp;表已删除。18:04:13 SQL flashback table emp to before drop;