软件081李岩08083122.docx

软件081李岩08083122.docx

《软件081李岩08083122.docx》由会员分享，可在线阅读，更多相关《软件081李岩08083122.docx（34页珍藏版）》请在冰豆网上搜索。

软件081李岩08083122

题目要求：

将下面的题目在Oracle11g中实现，并手写纸质版报告一份，包括所有实现功能的SQL*PLUS命令或SQL或PL/SQL语句及运行结果。

新建一个数据库，命名为oraclezy，并完成以下功能：

1）记录某采油厂对油/水井实施作业时所消耗的成本（命名为作业表zyb）。

（作业：

可理解为对油/水井进行大修）

2）成本的消耗包括预算、结算、入账三个状态。

预算：

采油队向管理部门提出作业申请，并经管理部门批准后，由管理部门负责录入。

结算：

某次作业施工结束后，由管理部门与施工单位共同核算各种成本，由管理部门负责录入。

入账：

结算后，财务部门将成本计入采油厂账目，由财务部门录入。

3）预算状态时需要录入的数据：

（★：

字符型◆数值型▲日期型）

★单据号：

某一作业项目的编号

★预算单位：

指需要对油水井实施作业的采油队★井号：

需要实施作业的油水井

◆预算金额★预算人▲预算日期

4）结算状态需要录入的数据：

（★：

字符型◆数值型▲日期型）

调出某条预算记录，录入以下数据：

▲开工日期▲完工日期★施工单位★施工内容

◆材料费（要求另外建表单独记录材料消耗的明细）

（★物码◆消耗数量◆单价）

◆人工费◆设备费◆其它费用

◆结算金额（材料费+人工费+设备费+其它费用）

★结算人▲结算日期

5）入账状态需要录入的数据：

（★：

字符型◆数值型▲日期型）

调出某条结算记录，录入以下数据：

◆入账金额★入账人▲入账日期

6）为了避免出现有歧义的错误，要求定义以下辅助的数据表：

（★：

字符型）

单位代码表：

★单位代码★单位名称

油水井表：

★井号★井别：

油井/水井★单位代码（表示某口井由哪个单位负责管理）

施工单位表：

★施工单位名称

物码表：

★物码★名称规格★计量单位

1．根据对以上题意的理解，利用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

）;

2.利用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

预算单位：

采油一矿1队井号：

y001预算金额：

10000.00预算人：

张三预算日期:

2009-5-1

//开工日期：

2009-5-4完工日期：

2009-5-25施工单位：

作业公司作业一队施工内容：

堵漏

材料费：

7000.00（其中材料一：

2000.00材料二：

2000.00材料三：

2000.00材料四：

1000.00）

人工费：

2500.00设备费:

1000.00其它费用:

1400.00结算金额：

11900.00

结算人：

李四结算日期：

2009-5-26

//入账金额：

11900.00入账人：

王五入账日期：

2009-5-28

//某作业项目编号zy2009002

预算单位：

采油一矿2队井号：

y003预算金额：

11000.00预算人：

张三预算日期:

2009-5-1

//开工日期：

2009-5-4完工日期：

2009-5-23施工单位：

作业公司作业二队施工内容：

检泵

材料费：

6000.00（其中材料一：

2000.00材料二：

2000.00材料三：

2000.00）//隐含单价和数量

人工费：

1500.00设备费:

1000.00其它费用:

2400.00结算金额：

10900.00

结算人：

李四结算日期：

2009-5-26

//入账金额：

10900.00入账人：

王五入账日期：

2009-5-28

//某作业项目编号zy2009003

预算单位：

采油一矿2队井号：

s001预算金额：

10500.00预算人：

张三预算日期:

2009-5-1

//开工日期：

2009-5-6完工日期：

2009-5-23施工单位：

作业公司作业二队施工内容：

检泵

材料费：

6500.00（其中材料一：

2000.00材料二：

2000.00材料三：

2500.00）

人工费：

2000.00设备费:

500.00其它费用:

1400.00结算金额：

10400.00

结算人：

李四结算日期：

2009-5-26

//入账金额：

10400.00入账人：

王五入账日期：

2009-5-28

//某作业项目编号zy2009004

预算单位：

采油二矿1队井号：

s002预算金额：

12000.00预算人：

张三预算日期:

2009-5-1

//开工日期：

2009-5-4完工日期：

2009-5-24施工单位：

作业公司作业三队施工内容：

防砂

材料费：

6000.00（其中材料一：

2000.00材料二：

2000.00材料四：

2000.00）

人工费：

2000.00设备费:

1000.00其它费用:

1600.00结算金额：

10600.00

结算人：

李四结算日期：

2009-5-26

//入账金额：

10600.00入账人：

赵六入账日期：

2009-5-28

//某作业项目编号zy2009005

预算单位：

采油二矿2队井号：

y005预算金额：

12000.00预算人：

张三预算日期:

2009-5-1

//开工日期：

2009-5-4完工日期：

2009-5-28施工单位：

作业公司作业三队施工内容：

防砂

材料费：

7000.00（其中材料一：

2000.00材料二：

2000.00材料四：

3000.00）

人工费：

1000.00设备费:

2000.00其它费用:

1300.00结算金额：

11300.00

结算人：

李四结算日期：

2009-5-28

//（未入账）

这里只列举一个导出的数据表：

"施工单位名称","施工单位号","ROWID"

"作业公司作业一队","zy01001","AAAR7YAABAAAVChAAA"

"作业公司作业二队","zy01002","AAAR7YAABAAAVChAAB"

"作业公司作业三队","zy01003","AAAR7YAABAAAVChAAC"

3.利用SQLPLUS查询以下数据：

1）采油一矿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）采油一矿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

3）采油一矿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

4）有哪些人员参与了入账操作。

SQL>selectdistinct入账人fromzyb;

入账人

--------------------

王五

赵六

5）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

6）列出采油一矿2队的所有项目，按入账金额从高到低排列。

项目-项目号

SQL>select单据号，入账金额fromzyb

2where预算单位='采油一矿2队'

3*orderby入账金额desc

单据号入账金额

------------------------------

zy200900210900

zy200900310400

7）列出有哪些单位实施了项目，并计算各单位所有项目结算金额总和。

SQL>SELECT施工单位，sum（结算金额）FROMZYB

2GROUPBY施工单位;

施工单位SUM（结算金额）

---------------------------------

作业公司作业一队11900

作业公司作业二队21300

作业公司作业三队21900

已用时间:

00:

00:

00.04

8）找出消耗了材料三且消耗超过了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

9）作业公司二队参与了哪些项目。

SQL>select单据号,施工单位FROMZYB

2*WHERE施工单位='作业公司作业二队'

单据号施工单位

----------------------------------------

zy2009002作业公司作业二队

zy2009003作业公司作业二队

已用时间:

00:

00:

00.01

10）作业公司一队和二队参与了哪些项目（利用union）。

//集合查询

SQL>select单据号FROMZYB

2WHERE施工单位='作业公司作业一队'

3UNION

4select单据号FROMZYB

5*WHERE施工单位='作业公司作业二队'

单据号

--------------------

zy2009001

zy2009002

zy2009003

已用时间:

00:

00:

00.01

4.利用SQLPLUS完成以下操作：

1）将编号为zy2009005的项目的人工费和结算金额增加100元。

--增加前的数据

SQL>select单据号,人工费,结算金额fromzyb;