大型数据库大作业源代码

建立一个表空间cx
create tablespace cx datafile 'E:\ORL\cx.dbf'
size 50M
extent management local uniform size 512k;


/*[1]建立一个最高管理员用户，密码学号，在cx表空间下*/
create user cx_admin identified by 1515925156
default tablespace cx;
/*给管理用户设置权限*/
grant connect to cx_admin;
grant dba to cx_admin;
//创建一个商品表该表包含一个商品编号列，一个商品名称列，商品类型列，一个单价列
create table goods(gno varchar(5),gname varchar(20),gtype varchar(20),gprice number(3,2));
//创建一个零售表包含一个销售编号列，一个商品编号列，一个商品名称列，一个销售数量列和一个销售金额列
create table retail(rno varchar(5),gno varchar(5),gname varchar(20),rsum number(4),rmoney number(6,2));
//给goods增加主键(gno,gname)
alter table goods add constraint g_p primary key(gno,gname);
//给retail增加主键(rno,gno)
alter table retail add constraint r_p primary key(rno,gno);
//给retail增加外键
alter table retail add constraint r_f foreign key(gno,gname) references goods(gno,gname);

//非空
alter table retail modify rmoney number(6,2) not null;
alter table retail modify rsum number(4) not null;
---------------------------------------------------------
//[2]创建一个数据管理人员账户，账户名为“自己的姓名”，密码为自己的学号。并且设置密码生命周期为30天，允许输入错误

的密码不得超过5次，超过5次密码锁1天。
create profile cx_profile limit
failed_login_attempts 5
password_lock_time 1
password_life_time 30;
//数据管理员
create user cx_cx identified by 1515925156
default tablespace cx
profile cx_profile;
//进入用户
grant connect to cx_cx;
//给cx-cx增加，查询，修改，更新，删除权限
grant select,alter,delete,update on cx_admin.goods to cx_cx;
grant select,alter,delete,update on cx_admin.retail to cx_cx;

//[3]创建一个业务用户，在cx表空间下，密码是学号
create user cx_work identified by 1515925156
default tablespace cx;
//连接的权限
grant connect to cx_work with admin option;
//创建视图的权限
grant create view to cx_work with admin option;
//[4]访问用户，密码学号
create user cx_query identified by 1515925156
default tablespace cx;
//连接的权限，查询表的权限
grant connect to cx_query;
grant select on cx_admin.goods to cx_query;
grant select on cx_admin.retail to cx_query;
-----------------------------------------------------------
//为“零售信息”表添加商品单价列
//给retail表增加将“商品信息”表中的商品单价写入“零售信息”表的单价列；
alter table retail add gprice number(3,2);
//增加日期字段
alter table retail add gyear date;
alter table goods add gyear date;
//进入work用户，增加创建视图的权限
conn cx_work/1515925156
grant create view to cx_cx;
//进入sys用户

conn sys/oracle as sysdba
//删除work人员
drop user cx_work;
//1）创建一个视图，该视图中只保存销售编号和销售总额。
create or replace view retail_view as select rno,rmoney from retail;
//2）创建一个函数，实现输入销售编号显示该次销售的总额。
create function retail_fun(v_no varchar) return varchar is v_money number(4);
begin
select rmoney into v_money from retail where v_no=rno;
return v_money;
end;
//用这个函数
var c_fun number;
call retail_fun('20001') into:c_fun;

SELECT file_name FROM dba_data_files;
E:\ORL\CX.DBF

//连接
conn sys/oracle as sysdba;
shutdown immediate;(cmd)
//创建一个把2017的数据放在 "2017年销售情况"
create table "2017年销售情况" as select * from retail where to_char(gyear,'yyyy-mm-dd')>=('2017-01-01');
/*使用触发器实现“2017年销售情况”表中数据的同步更新。*/
create or replace trigger retail_tri after insert or update or delete
on retail for each row
declare
begin
if inserting then
insert into "2017年销售情况"(rno,gno,gname,rsum,rmoney,gprice,gyear) values

(:new.rno,:new.gno,:new.gname,:new.rsum,:new.rmoney,:new.gprice,:new.gyear);
elsif updating then
update "2017年销售情况" set

rno=:new.rno,gno=:new.gno,gname=:new.gname,rsum=:new.rsum,rmoney=:new.rmoney,gprice=:new.gprice,gyear=:new.gyear;
elsif deleting then
delete from "2017年销售情况" where rno=:old.rno;
end if;
end;

插入数据
insert into goods values('10001','樱桃','水果','7.00',to_date('2016-04-01','yyyy-mm-dd'));
insert into goods values('10002','榴莲','水果','6.00',to_date('2017-01-01','yyyy-mm-dd'));
insert into goods values('10003','哈密瓜','水果','5.00',to_date('2016-05-01','yyyy-mm-dd'));
insert into goods values('10004','香蕉','水果','8.00',to_date('2017-01-01','yyyy-mm-dd'));
insert into goods values('10005','橘子','水果','3.00',to_date('2016-06-01','yyyy-mm-dd'));
insert into goods values('10006','秋衣','服饰','9.00',to_date('2017-01-01','yyyy-mm-dd'));
insert into goods values('10007','裤子','服饰','4.00',to_date('2017-04-01','yyyy-mm-dd'));
insert into goods values('10008','背心','服饰','3.00',to_date('2017-04-01','yyyy-mm-dd'));

insert into retail values('20001','10001','樱桃','5000','5000.00','7.00',to_date('2017-01-01','yyyy-mm-dd'));
insert into retail values('20002','10002','榴莲','4000','3000.00','6.00',to_date('2016-08-01','yyyy-mm-dd'));
insert into retail values('20003','10003','哈密瓜','3000','2000.00','5.00',to_date('2017-01-01','yyyy-mm-dd'));
insert into retail values('20004','10004','香蕉','3000','3000.00','8.00',to_date('2016-09-01','yyyy-mm-dd'));
insert into retail values('20005','10005','橘子','2000','4000.00','3.00',to_date('2017-02-01','yyyy-mm-dd'));
insert into retail values('20006','10006','秋衣','10','100','9.00',to_date('2017-01-01','yyyy-mm-dd'));
insert into retail values('20007','

10007','裤子','10','100','9.00',to_date('2017-01-01','yyyy-mm-dd'));
insert into retail values('20008','10008','背心','10','100','9.00',to_date('2017-04-01','yyyy-mm-dd'));