Oracle数据库管理与维护大作业

1、Oracle数据库管理与维护大作业 专业班级 软件11XX班 姓 名 XXX 学 号 XXXX 完成日期 2013-XX-XX 题目要求：将下面的题目在Oracle11g中实现，并手写纸质版报告一份，包括所有实现功能的SQL*PLUS命令或SQL或PL/SQL语句及运行结果。新建一个数据库，命名为oraclezy，并完成以下功能：1) 记录某采油厂对油/水井实施作业时所消耗的成本（命名为作业表zyb）。（作业：可理解为对油/水井进行大修）2) 成本的消耗包括预算、结算、入账三个状态。预算：采油队向管理部门提出作业申请，并经管理部门批准后，由管理部门负责录入。结算：某次作业施工结束后，由管理部门

2、与施工单位共同核算各种成本，由管理部门负责录入。入账：结算后，财务部门将成本计入采油厂账目，由财务部门录入。1 根据对以上题意的理解，利用SQL PLUS，定义所需的数据表，包括定义相关约束条件（主码、外码及其他约束）。- Create tablecreate table ZYB( 单据号 VARCHAR2(20) not null, 预算单位 VARCHAR2(20) not null, 井号 VARCHAR2(20) not null, 预算金额 NUMBER(10) not null, 预算人 VARCHAR2(20) not null, 预算日期 DATE not null, 开工日期

3、 DATE, 完工日期 DATE, 施工单位 VARCHAR2(20), 施工内容 VARCHAR2(20), 材料费 NUMBER(10), 人工费 NUMBER(10), 设备费 NUMBER(10), 其它费用 NUMBER(10), 结算金额 NUMBER(10), 结算人 VARCHAR2(20), 结算日期 DATE, 入账人 VARCHAR2(20), 入账日期 DATE, 入账金额 NUMBER)tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 64K next 1

4、M minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table ZYB add constraint DJNO primary key (单据号) using index tablespace SYSTEM pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );- Grant/Revoke

5、object privileges grant select, insert, update, delete, references, alter, index on ZYB to LIYAN;- Create table 单位代码表- 由于表名用汉字表示操作时很麻烦，就取表名的每个字的首字母并冠以Z组成表名，如本例ZDWDMBcreate table ZDWDMB( 单位代码 VARCHAR2(20) not null, 单位名称 VARCHAR2(20) not null)tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans

6、 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table ZDWDMB add constraint DWNAME unique (单位名称) using index tablespace SYSTEM pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxe

7、xtents unlimited );- Grant/Revoke object privileges grant select, insert, update, delete, references, alter, index on ZDWDMB to LIYAN;- Create table 油水井表create table ZYSJB( 井号 VARCHAR2(20) not null, 井别 VARCHAR2(20) not null, 单位代码 VARCHAR2(20) not null)tablespace SYSTEM pctfree 10 pctused 40 initrans

8、 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table ZYSJB add constraint JNO primary key (井号) using index tablespace SYSTEM pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minexte

9、nts 1 maxextents unlimited );- Grant/Revoke object privileges grant select, insert, update, delete, references, alter, index on ZYSJB to LIYAN;- Create table- 施工单位表create table ZSGDWB( 施工单位名称 VARCHAR2(20) not null, 施工单位号 VARCHAR2(20) not null)tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtra

10、ns 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table ZSGDWB add constraint SGNO primary key (施工单位号) using index tablespace SYSTEM pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents

11、1 maxextents unlimited );- Grant/Revoke object privileges grant select, insert, update, delete, references, alter, index on ZSGDWB to LIYAN;- 物码表- Create tablecreate table ZWMB( 物码 VARCHAR2(20) not null, 名称规格 VARCHAR2(20) not null, 计量单位 VARCHAR2(20) not null)tablespace SYSTEM pctfree 10 pctused 40 i

12、nitrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table ZWMB add constraint WNO primary key (物码) using index tablespace SYSTEM pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M m

13、inextents 1 maxextents unlimited );- Grant/Revoke object privileges grant select, insert, update, delete, references, alter, index on ZWMB to LIYAN;- 材料费表- Create tablecreate table ZCLFB( 作业项目编号 VARCHAR2(20) not null, 物码 VARCHAR2(20) not null, 消耗数量 NUMBER not null, 单价 NUMBER not null)tablespace SYST

14、EM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );2. 利用SQL PLUS，录入以下相关的基础数据：单位代码表：1122 采油厂112201 采油一矿 112202 采油二矿112201001 采油一矿1队 112201002 采油一矿2队 112201003 采油一矿3队112202001 采油二矿1队 112202002 采油二矿2队油水井表： （y001 油 112201001） （y002 油 112201

15、001）（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

16、-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 施工单位

17、：作业公司作业二队 施工内容：检泵材料费：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 施工单位：作业公司作业二队 施工

18、内容：检泵材料费：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(其中材料

19、一：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.0

20、0 材料四：3000.00)人工费：1000.00 设备费:2000.00 其它费用:1300.00 结算金额：11300.00结算人：李四 结算日期：2009-5-28/(未入账！)这里只列举一个导出的数据表："施工单位名称","施工单位号","ROWID""作业公司作业一队","zy01001","AAAR7YAABAAAVChAAA""作业公司作业二队","zy01002","AAAR7YAABAAAVChAAB&quo

21、t;"作业公司作业三队","zy01003","AAAR7YAABAAAVChAAC"3. 利用SQL PLUS查询以下数据：1) 采油一矿2队2009-5-1到2009-5-28有哪些项目处于结算状态，列出相应材料费明细。结算状态有值，不用管入账是否有值。SQL> select * from zclfb where 作业项目编号 in 2 (select 单据号 from zyb where 预算单位='采油一矿2队' 3 and 结算日期>=to_date('2009-5-1','

22、;yyyy-mm-dd') 4* and 结算日期<=to_date('2009-5-28','yyyy-mm-dd')作业项目编号 物码 消耗数量 单价 - - - - zy2009002 wm001 200 10 zy2009002 wm002 200 10 zy2009002 wm003 200 10 zy2009003 wm001 200 10 zy2009003 wm002 200 10 zy2009003 wm003 250 10 已选择6行。已用时间: 00: 00: 00.312) 采油一矿2队2009-5-1到2009-5-28总的入账金额。SQL> select SUM(入帐金额) from zyb 2 where 预算单位='采油一矿2队' 3 and 入账日期>=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> select sum(入帐金额) from zyb 2 where 预算单位 l