Oracle数据库管理与维护大作业.docx
《Oracle数据库管理与维护大作业.docx》由会员分享，可在线阅读，更多相关《Oracle数据库管理与维护大作业.docx（35页珍藏版）》请在冰豆网上搜索。
Oracle数据库管理与维护大作业
Oracle数据库管理与维护大作业
大作业
专业班级软件11XX班
姓名XXX
学号XXXX
完成日期2013-XX-XX
题目要求：
将下面的题目在Oracle11g中实现，并手写纸质版报告一份，包括所有实现功能的SQL*PLUS命令或SQL或PL/SQL语句及运行结果。
新建一个数据库，命名为oraclezy，并完成以下功能：
记录某采油厂对油/水井实施作业时所消耗的成本（命名为作业表zyb）。
（作业：
可明白得为对油/水井进行大修）
成本的消耗包括预算、结算、入账三个状态。
预算：
采油队向治理部门提出作业申请，并经治理部门批准后，由治理部门负责录入。
结算：
某次作业施工终止后，由治理部门与施工单位共同核算各种成本，由治理部门负责录入。
入账：
结算后，财务部门将成本计入采油厂账目，由财务部门录入。
按照对以上题意的明白得，利用SQLPLUS，定义所需的数据表，包括定义有关约束条件（主码、外码及其他约束）。
--Createtable
createtableZYB
（
单据号VARCHAR2（20）notnull,
预算单位VARCHAR2（20）notnull,
井号VARCHAR2（20）notnull,
预算金额NUMBER（10）notnull,
预算人VARCHAR2（20）notnull,
预算日期DATEnotnull,
开工日期DATE,
完工日期DATE,
施工单位VARCHAR2（20）,
施工内容VARCHAR2（20）,
材料费NUMBER（10）,
人工费NUMBER（10）,
设备费NUMBER（10）,
其它费用NUMBER（10）,
结算金额NUMBER（10）,
结算人VARCHAR2（20）,
结算日期DATE,
入账人VARCHAR2（20）,
入账日期DATE,
入账金额NUMBER
）
tablespaceSYSTEM
pctfree10
pctused40
initrans1
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableZYB
addconstraintDJNOprimarykey（单据号）
usingindex
tablespaceSYSTEM
pctfree10
initrans2
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Grant/Revokeobjectprivileges
grantselect,insert,update,delete,references,alter,indexonZYBtoLIYAN;
--Createtable单位代码表
--由于表名用汉字表示操作时专门苦恼，就取表名的每个字的首字母并冠以Z组成表名，如本例ZDWDMB
createtableZDWDMB
（
单位代码VARCHAR2（20）notnull,
单位名称VARCHAR2（20）notnull
）
tablespaceSYSTEM
pctfree10
pctused40
initrans1
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableZDWDMB
addconstraintDWNAMEunique（单位名称）
usingindex
tablespaceSYSTEM
pctfree10
initrans2
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Grant/Revokeobjectprivileges
grantselect,insert,update,delete,references,alter,indexonZDWDMBtoLIYAN;
--Createtable油水井表
createtableZYSJB
（
井号VARCHAR2（20）notnull,
井不VARCHAR2（20）notnull,
单位代码VARCHAR2（20）notnull
）
tablespaceSYSTEM
pctfree10
pctused40
initrans1
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableZYSJB
addconstraintJNOprimarykey（井号）
usingindex
tablespaceSYSTEM
pctfree10
initrans2
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Grant/Revokeobjectprivileges
grantselect,insert,update,delete,references,alter,indexonZYSJBtoLIYAN;
--Createtable
--施工单位表
createtableZSGDWB
（
施工单位名称VARCHAR2（20）notnull,
施工单位号VARCHAR2（20）notnull
）
tablespaceSYSTEM
pctfree10
pctused40
initrans1
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableZSGDWB
addconstraintSGNOprimarykey（施工单位号）
usingindex
tablespaceSYSTEM
pctfree10
initrans2
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Grant/Revokeobjectprivileges
grantselect,insert,update,delete,references,alter,indexonZSGDWBtoLIYAN;
--物码表
--Createtable
createtableZWMB
（
物码VARCHAR2（20）notnull,
名称规格VARCHAR2（20）notnull,
计量单位VARCHAR2（20）notnull
）
tablespaceSYSTEM
pctfree10
pctused40
initrans1
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableZWMB
addconstraintWNOprimarykey（物码）
usingindex
tablespaceSYSTEM
pctfree10
initrans2
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
--Grant/Revokeobjectprivileges
grantselect,insert,update,delete,references,alter,indexonZWMBtoLIYAN;
--材料费表
--Createtable
createtableZCLFB
（
作业项目编号VARCHAR2（20）notnull,
物码VARCHAR2（20）notnull,
消耗数量NUMBERnotnull,
单价NUMBERnotnull
）
tablespaceSYSTEM
pctfree10
pctused40
initrans1
maxtrans255
storage
（
initial64K
next1M
minextents1
maxextentsunlimited
）;
利用SQLPLUS，录入以下有关的基础数据：
单位代码表：
1122采油厂
112201采油一矿112202采油二矿
112201001采油一矿1队112201002采油一矿2队112201003采油一矿3队
112202001采油二矿1队112202002采油二矿2队
油水井表：
（y001油112201001）（y002油112201001）
（y003油112201002）（s001水112201002）
（y004油112201003）（s002水112202001）
（s003水112202001）（y005油112202002）
施工单位表：
作业公司作业一队作业公司作业二队作业公司作业三队
物码表：
wm001材料一吨wm002材料二米wm003材料三桶wm004材料四袋
ZYB:
//某作业项目编号zy2009001
//开工日期：
2009-5-4完工日期：
2009-5-25施工单位：
作业公司作业一队施工内容：
堵漏
材料费：
7000.00（其中材料一：
2000.00材料二：
2000.00材料三：
2000.00材料四：
1000.00）
人工费：
2500.00设备费:
1000.00其它费用:
1400.00结算金额：
11900.00
//某作业项目编号zy2009002
//开工日期：
2009-5-4完工日期：
2009-5-23施工单位：
作业公司作业二队施工内容：
检泵
材料费：
6000.00（其中材料一：
2000.00材料二：
2000.00材料三：
2000.00）//隐含单价和数量
人工费：
1500.00设备费:
1000.00其它费用:
2400.00结算金额：
10900.00
//某作业项目编号zy2009003
//开工日期：
2009-5-6完工日期：
2009-5-23施工单位：
作业公司作业二队施工内容：
检泵
材料费：
6500.00（其中材料一：
2000.00材料二：
2000.00材料三：
2500.00）
人工费：
2000.00设备费:
500.00其它费用:
1400.00结算金额：
10400.00
//某作业项目编号zy2009004
//开工日期：
2009-5-4完工日期：
2009-5-24施工单位：
作业公司作业三队施工内容：
防砂
材料费：
6000.00（其中材料一：
2000.00材料二：
2000.00材料四：
2000.00）
人工费：
2000.00设备费:
1000.00其它费用:
1600.00结算金额：
10600.00
//某作业项目编号zy2009005
//开工日期：
2009-5-4完工日期：
2009-5-28施工单位：
作业公司作业三队施工内容：
防砂
材料费：
7000.00（其中材料一：
2000.00材料二：
2000.00材料四：
3000.00）
人工费：
1000.00设备费:
2000.00其它费用:
1300.00结算金额：
11300.00
//（未入账！
！
！
）
那个地点只列举一个导出的数据表：
"施工单位名称","施工单位号","ROWID"
"作业公司作业一队","zy01001","AAAR7YAABAAAVChAAA"
"作业公司作业二队","zy01002","AAAR7YAABAAAVChAAB"
"作业公司作业三队","zy01003","AAAR7YAABAAAVChAAC"
利用SQLPLUS查询以下数据：
采油一矿2队2009-5-1到2009-5-28有哪些项目处于结算状态，列出相应材料费明细。
结算状态有值，不用管入账是否有值。
SQL>select*fromzclfbwhere作业项目编号in
2（select单据号fromzybwhere预算单位='采油一矿2队'
3and结算日期>=to_date（'2009-5-1','yyyy-mm-dd'）
4*and结算日期<=to_date（'2009-5-28','yyyy-mm-dd'））
作业项目编号物码消耗数量单价
------------------------------------------------------------
zy2009002wm00120010
zy2009002wm00220010
zy2009002wm00320010
zy2009003wm00120010
zy2009003wm00220010
zy2009003wm00325010
已选择6行。
已用时刻:
00:
00:
00.31
采油一矿2队2009-5-1到2009-5-28总的入账金额。
SQL>selectSUM（入帐金额）fromzyb
2where预算单位='采油一矿2队'
3and入账日期>=TO_DATE（'2009-5-1','YYYY-MM-DD'）
4*AND入账日期<=TO_DATE（'2009-5-28','YYYY-MM-DD'）
SUM（入帐金额）
-------------
21300
采油一矿2009-5-1到2009-5-28总的入账金额。
SQL>selectsum（入帐金额）fromzyb
2where预算单位like'采油一矿%'
3and入账日期>=to_date（'2009-5-1','yyyy-mm-dd'）
4*and入账日期<=to_date（'2009-5-28','yyyy-mm-dd'）
SUM（入帐金额）
-------------
33200
有哪些人员参与了入账操作。
SQL>selectdistinct入账人fromzyb;
入账人
--------------------
王五
赵六
2009-5-1到2009-5-28有结算未入帐的项目。
项目-单据号
SQL>SELECT单据号FROMZYB
2WHERE结算日期<=TO_DATE（'2009-5-28','YYYY-MM-DD'）
3AND结算日期>=TO_DATE（'2009-5-1','YYYY-MM-DD'）
4*AND入账人isNULL
5AND结算人isnotNULL
单据号
--------------
zy2009005
列出采油一矿2队的所有项目，按入账金额从高到低排列。
项目-项目号
SQL>select单据号，入账金额fromzyb
2where预算单位='采油一矿2队'
3*orderby入账金额desc
单据号入账金额
------------------------------
zy200900210900
zy200900310400
列出有哪些单位实施了项目，并运算各单位所有项目结算金额总和。
SQL>SELECT施工单位，sum（结算金额）FROMZYB
2GROUPBY施工单位;
施工单位SUM（结算金额）
---------------------------------
作业公司作业一队11900
作业公司作业二队21300
作业公司作业三队21900
已用时刻:
00:
00:
00.04
找出消耗了材料三且消耗超过了2000元的项目，列出相应明细（利用子查询）。
//谁的明细？
-该项目的
SQL>select*fromzybwhere单据号in
2（select作业项目编号fromzclfbwhere（消耗数量*单价）>2000
3*and物码='wm003'）
单据号预算单位井号预算金额预算人预算日期开工日期完工日期施工单位施工内容材料费人工费设备费其它费用结算金额结算人结算日期入账人入账日期入账金额
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
zy2009003采油一矿2队s00110500张三01-5月-0906-5月-0923-5月-09作业公司作业二队检泵65002100500140010500李四26-5月-09王五28-5月-0910400
已用时刻:
00:
00:
00.28
作业公司二队参与了哪些项目。
SQL>select单据号,施工单位FROMZYB
2*WHERE施工单位='作业公司作业二队'
单据号施工单位
----------------------------------------
zy2009002作业公司作业二队
zy2009003作业公司作业二队
已用时刻:
00:
00:
00.01
作业公司一队和二队参与了哪些项目（利用union）。
//集合查询
SQL>select单据号FROMZYB
2WHERE施工单位='作业公司作业一队'
3UNION
4select单据号FROMZYB
5*WHERE施工单位='作业公司作业二队'
单据号
--------------------
zy2009001
zy2009002
zy2009003
已用时刻:
00:
00:
00.01
利用SQLPLUS完成以下操作：
将编号为zy2009005的项目的人工费和结算金额增加100元。
--增加前的数据
SQL>select单据号,人工费,结算金额fromzyb;
单据号人工费结算金额
----------------------------------------
zy2009001250011900
zy2009002150010900
zy2009003200010400
zy2009004200010600
zy2009005100011300
已用时刻:
00:
00:
00.00
SQL>updatezyb
2*set人工费=人工费+100,结算金额=结算金额+100
已更新5行。
已用时刻:
00:
00:
00.14
--操作后的数据
SQL>select单据号,人工费,结算金额fromzyb;
单据号人工费结算金额
----------------------------------------
zy2009001260012000
zy2009002160011000
zy2009003210010500
zy2009004210010700
zy2009005110011400
已用时刻:
00:
00:
00.00
删除已结算未入帐的项目。
（可结合查询5）
--删除前的数据
SQL>select单据号fromzyb;
单据号
--------------------
zy2009001
zy2009002
zy2009003
zy2009004
zy2009005zy2009006
SQL>deletefromzybwhere单据号=
2（select单据号fromzyb
3*where结算金额isnotnulland入账金额isnull）
已删除1行。
已用时刻:
00:
00:
00.01
--删除后结果
SQL>select单据号fromzyb;
单据号
--------------------
zy2009001
zy2009002
zy2009003
zy2009004
zy2009006
已用时刻:
00:
00:
00.00
利用SQLPLUS进行如下事
升级会员 