Oracle大作业

1、Oracle数据库管理与维护课 程 大 作 业 专业班级 计算机 姓 名 学 号 完成日期 2016-06-30 得分评阅人将下面的题目在Oracle11g中实现，电子版包括题目编号、所有实现功能的SQL*PLUS命令或SQL或PL/SQL语句及运行结果。在每位同学自己的用户下，完成以下功能：1) 记录某采油厂对油/水井实施作业时所消耗的成本（命名为作业表zyb）。（作业：可理解为对油/水井进行大修）2) 成本的消耗包括预算、结算、入账三个状态。预算：采油队向管理部门提出作业申请，并经管理部门批准后，由管理部门负责录入。结算：某次作业施工结束后，由管理部门与施工单位共同核算各种成本，由管理部门

2、负责录入。入账：结算后，财务部门将成本计入采油厂账目，由财务部门录入。3) 预算状态时需要录入的数据：（：字符型 数值型 日期型） 单据号：某一作业项目的编号预算单位：指需要对油水井实施作业的采油队 井号：需要实施作业的油水井预算金额 预算人 预算日期4) 结算状态需要录入的数据：（：字符型 数值型 日期型）调出某条预算记录，录入以下数据：开工日期 完工日期 施工单位 施工内容 材料费（要求另外建表单独记录材料消耗的明细）（物码 消耗数量 单价） 人工费 设备费 其它费用 结算金额（材料费+人工费+设备费+其它费用）结算人 结算日期5) 入账状态需要录入的数据：（：字符型 数值型 日期型）调出

3、某条结算记录，录入以下数据：入账金额 入账人 入账日期6) 为了避免出现有歧义的错误，要求定义以下辅助的数据表：（：字符型）单位代码表：单位代码 单位名称油水井表：井号 井别：油井/水井 单位代码（表示某口井由哪个单位负责管理）施工单位表：施工单位名称物码表：物码 名称规格 计量单位1 根据对以上题意的理解，利用SQL PLUS，定义所需的数据表，包括定义相关约束条件（主码、外码及其他约束）。2. 利用SQL PLUS，录入以下相关的基础数据：单位代码表：单位代码单位名称1122采油厂112201采油一矿112202采油二矿112201001采油一矿1队112201002采油一矿2队11220

4、1003采油一矿3队112202001采油二矿1队112202002采油二矿2队油水井表：井号井别单位代码y001油112201001y002油112201001y003油112201002s001水112201002y004油112201003s002水112202001s003水112202001y005油112202002 施工单位表：施工单位名称作业公司作业一队作业公司作业二队作业公司作业三队 物码表：物码名称规格计量单位wm001材料一吨wm002材料二米wm003材料三桶wm004材料四袋某作业项目编号 zy2016001预算单位：采油一矿1队 井号：y001 预算金额：10000

5、.00预算人：张强 预算日期:2016-5-1 开工日期：2016-5-4 完工日期：2016-5-25 施工单位：作业公司作业一队施工内容：堵漏材料费：7000.00(其中材料一：2000.00 材料二：2000.00 材料三：2000.00 材料四：1000.00)人工费：2500.00 设备费:1000.00 其它费用:1400.00 结算金额：11900.00结算人：李想 结算日期：2016-5-26入账金额：11900.00 入账人：王丽 入账日期：2016-5-28某作业项目编号 zy2016002预算单位：采油一矿2队 井号：y003 预算金额：11000.00 预算人：张强 预

6、算日期:2016-5-1开工日期：2016-5-4 完工日期：2016-5-23 施工单位：作业公司作业二队 施工内容：检泵材料费：6000.00(其中材料一：2000.00 材料二：2000.00 材料三：2000.00)人工费：1500.00 设备费:1000.00 其它费用:2400.00 结算金额：10900.00结算人：李想 结算日期：2016-5-26入账金额：10900.00 入账人：王丽 入账日期：2016-5-28某作业项目编号 zy2016003预算单位：采油一矿2队 井号：s001 预算金额：10500.00 预算人：张强 预算日期:2016-5-1开工日期：2016-5

7、-6 完工日期：2016-5-23 施工单位：作业公司作业二队 施工内容：检泵材料费：6500.00(其中材料一：2000.00 材料二：2000.00 材料三：2500.00)人工费：2000.00 设备费:500.00 其它费用:1400.00 结算金额：10400.00结算人：李想 结算日期：2016-5-26入账金额：10400.00 入账人：王丽 入账日期：2016-5-28某作业项目编号 zy2016004预算单位：采油二矿1队 井号：s002 预算金额：12000.00 预算人：张强 预算日期:2016-5-1开工日期：2016-5-4 完工日期：2016-5-24 施工单位：作

8、业公司作业三队 施工内容：防砂材料费：6000.00(其中材料一：2000.00 材料二：2000.00 材料四：2000.00)人工费：2000.00 设备费:1000.00 其它费用:1600.00 结算金额：10600.00结算人：李想 结算日期：2016-5-26入账金额：10600.00 入账人：赵六 入账日期：2016-5-28某作业项目编号 zy2016005预算单位：采油二矿2队 井号：y005 预算金额：12000.00 预算人：张强 预算日期:2016-5-1开工日期：2016-5-4 完工日期：2016-5-28 施工单位：作业公司作业三队 施工内容：防砂材料费：7000

9、.00(其中材料一：2000.00 材料二：2000.00 材料四：3000.00)人工费：1000.00 设备费:2000.00 其它费用:1300.00 结算金额：11300.00结算人：李想 结算日期：2016-5-28单位代码表: unitno_tb油水井表: oilwell_tb施工单位表: workunit_tb物码表: object_tb作业表: zyb_tb(1) 单位代码表SQL> create table unitno_tb 2 ( 3 单位代码 varchar2(20) constraint pk_unitno primary key, 4 单位名称 varchar

10、2(20) not null 5 ) 6 /表已创建。SQL> insert into unitno_tb values('1122','采油厂');已创建 1 行。提交完成。SQL> insert into unitno_tb values('112201','采油一矿');已创建 1 行。提交完成。SQL> insert into unitno_tb values('112202','采油二矿');已创建 1 行。提交完成。SQL> insert into unitno_

11、tb values('112201001','采油一矿1队');已创建 1 行。提交完成。SQL> insert into unitno_tb values('112201002','采油一矿2队');已创建 1 行。提交完成。SQL> insert into unitno_tb values('112201003','采油一矿3队');已创建 1 行。提交完成。SQL> insert into unitno_tb values('112202001','采

12、油二矿1队');已创建 1 行。提交完成。SQL> insert into unitno_tb values('112202002','采油二矿2队');已创建 1 行。提交完成。SQL> select * from unitno_tb;单位代码 单位名称- -1122 采油厂112201 采油一矿112202 采油二矿112201001 采油一矿1队112201002 采油一矿2队112201003 采油一矿3队112202001 采油二矿1队112202002 采油二矿2队已选择8行。(2) 油水井表SQL> create tabl

13、e oilwell_tb 2 ( 3 井号 varchar2(20) constraint pk_oilwell primary key, 4 井别 varchar2(20) not null check(井别 in('油','水'), 5 单位代码 varchar2(20) references unitno_tb(单位代码) on delete cascade 6 ) 7 /表已创建。SQL> insert into oilwell_tb values('y001','油','112201001');已创

14、建 1 行。提交完成。SQL> insert into oilwell_tb values('y002','油','112201001');已创建 1 行。提交完成。SQL> insert into oilwell_tb values('y003','油','112201002');已创建 1 行。提交完成。SQL> insert into oilwell_tb values('s001','水','112201002');已创建 1

15、 行。提交完成。SQL> insert into oilwell_tb values('y004','油','112201003');已创建 1 行。提交完成。SQL> insert into oilwell_tb values('s002','水','112202001');已创建 1 行。提交完成。SQL> insert into oilwell_tb values('s003','水','112202001');已创建 1 行。

16、提交完成。SQL> insert into oilwell_tb values('y005','水','112202002');已创建 1 行。提交完成。SQL> select * from oilwell_tb;井号 井别 单位代码- - -y001 油 112201001y002 油 112201001y003 油 112201002s001 水 112201002y004 油 112201003s002 水 112202001s003 水 112202001y005 水 112202002已选择8行。(3) 施工单位表SQL&g

17、t; create table workunit_tb 2 ( 3 施工单位名称 varchar2(20) constraint pk_workunit primary key 4 ) 5 /表已创建。SQL> insert into workunit_tb values('作业公司作业一队');已创建 1 行。提交完成。SQL> insert into workunit_tb values('作业公司作业二队');已创建 1 行。提交完成。SQL> insert into workunit_tb values('作业公司作业三队

18、9;);已创建 1 行。提交完成。SQL> select * from workunit_tb;施工单位名称-作业公司作业一队作业公司作业二队作业公司作业三队(4) 物码表SQL> create table object_tb 2 ( 3 物码 varchar2(20) constraint pk_object primary key, 4 名称规格 varchar2(20) not null, 5 计量单位 varchar2(20) not null 6 ) 7 /表已创建。SQL> insert into object_tb values('wm001',

19、'材料一','吨');已创建 1 行。提交完成。SQL> insert into object_tb values('wm002','材料二','米');已创建 1 行。提交完成。SQL> insert into object_tb values('wm003','材料三','桶');已创建 1 行。提交完成。SQL> insert into object_tb values('wm004','材料四','袋&

20、#39;);已创建 1 行。提交完成。SQL> select * from object_tb;物码 名称规格 计量单位- - -wm001 材料一 吨wm002 材料二 米wm003 材料三 桶wm004 材料四 袋(5) 作业表SQL> create table zyb_tb 2 ( 3 某作业项目编号 varchar2(20) constraint pk_zyb primary key, 4 预算单位 varchar2(20) not null, 5 井号 varchar2(20) references oilwell_tb(井号) on delete cascade, 6

21、预算金额 number(10) not null, 7 预算人 varchar2(20) not null, 8 预算日期 date not null, 9 开工日期 date, 10 完工日期 date, 11 施工单位 varchar2(20) references workunit_tb(施工单位名称) on delete cascade, 12 施工内容 varchar2(20), 13 材料费 number(10), 14 人工费 number(10), 15 设备费 number(10), 16 其它费用 number(10), 17 结算金额 number(10), 18 结算人

22、 varchar2(20), 19 结算日期 date, 20 入账金额 number(10), 21 入账人 varchar2(20), 22 入账日期 date 23 ) 24 /表已创建。SQL> alter session set nls_date_format='yyyy-mm-dd'会话已更改。SQL> insert into zyb_tb values('zy2016001','采油一矿1队','y001',10000.00,'张强',to_date('2016-05-01'

23、;,'yyyy-mm-dd'),to_date('2016-05-04','yyyy-mm-dd'),to_date('2016-05-25','yyyy-mm-dd'),'作业公司作业一队','堵漏',7000.00,2500.00,1000.00,1400.00,11900.00,'李想',to_date('2016-05-26','yyyy-mm-dd'),11900.00,'王丽',to_date('20

24、16-05-28','yyyy-mm-dd');已创建 1 行。提交完成。SQL> insert into zyb_tb values('zy2016002','采油一矿2队','y003',11000.00,'张强',to_date('2016-05-01','yyyy-mm-dd'),to_date('2016-05-04','yyyy-mm-dd'),to_date('2016-05-23','yyyy-mm

25、-dd'),'作业公司作业二队','检泵',6000.00,1500.00,1000.00,2400.00,10900.00,'李想',to_date('2016-05-26','yyyy-mm-dd'),10900.00,'王丽',to_date('2016-05-28','yyyy-mm-dd');已创建 1 行。提交完成。SQL> insert into zyb_tb values('zy2016003','采油一矿2队&#

26、39;,'s001',10500.00,'张强',to_date('2016-05-01','yyyy-mm-dd'),to_date('2016-05-06','yyyy-mm-dd'),to_date('2016-05-23','yyyy-mm-dd'),'作业公司作业二队','检泵',6500.00,2000.00,500.00,1400.00,10400.00,'李想',to_date('2016-05-

27、26','yyyy-mm-dd'),10400.00,'王丽',to_date('2016-05-28','yyyy-mm-dd');已创建 1 行。提交完成。SQL> insert into zyb_tb values('zy2016004','采油二矿1队','s002',12000.00,'张强',to_date('2016-05-01','yyyy-mm-dd'),to_date('2016-05-04&#

28、39;,'yyyy-mm-dd'),to_date('2016-05-24','yyyy-mm-dd'),'作业公司作业三队','防砂',6000.00,2000.00,1000.00,1600.00,10600.00,'李想',to_date('2016-05-26','yyyy-mm-dd'),10600.00,'赵六',to_date('2016-05-28','yyyy-mm-dd');已创建 1 行。提交完成。

29、SQL> insert into zyb_tb(作业项目编号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费,人工费,设备费,其它费用,结算金额,结算人,结算日期) values('zy2016005','采油二矿2队','y005',12000.00,'张强',to_date('2016-05-01','yyyy-mm-dd'),to_date('2016-05-04','yyyy-mm-dd'),to_date(&

30、#39;2016-05-28','yyyy-mm-dd'),'作业公司作业三队','防砂',7000.00,1000.00,2000.00,1300.00,11300.00,'李 想',to_date('2016-05-28','yyyy-mm-dd');已创建 1 行。提交完成。SQL> select * from zyb_tb;作业项目编号 预算单位 井号 预算金额- - - -预算人 预算日期 开工日期 完工日期 施工单位- - - - -施工内容 材料费 人工费 设备费 其它费

31、用 结算金额- - - - - -结算人 结算日期 入账金额 入账人 入账日期- - - - -zy2016001 采油一矿1队 y001 10000张强 2016-05-01 2016-05-04 2016-05-25 作业公司作业一队堵漏 7000 2500 1000 1400 11900李想 2016-05-26 11900 王丽 2016-05-28作业项目编号 预算单位 井号 预算金额- - - -预算人 预算日期 开工日期 完工日期 施工单位- - - - -施工内容 材料费 人工费 设备费 其它费用 结算金额- - - - - -结算人 结算日期 入账金额 入账人 入账日期- -

32、 - - -zy2016002 采油一矿2队 y003 11000张强 2016-05-01 2016-05-04 2016-05-23 作业公司作业二队检泵 6000 1500 1000 2400 10900李想 2016-05-26 10900 王丽 2016-05-28作业项目编号 预算单位 井号 预算金额- - - -预算人 预算日期 开工日期 完工日期 施工单位- - - - -施工内容 材料费 人工费 设备费 其它费用 结算金额- - - - - -结算人 结算日期 入账金额 入账人 入账日期- - - - -zy2016003 采油一矿2队 s001 10500张强 2016-0

33、5-01 2016-05-06 2016-05-23 作业公司作业二队检泵 6500 2000 500 1400 10400李想 2016-05-26 10400 王丽 2016-05-28作业项目编号 预算单位 井号 预算金额- - - -预算人 预算日期 开工日期 完工日期 施工单位- - - - -施工内容 材料费 人工费 设备费 其它费用 结算金额- - - - - -结算人 结算日期 入账金额 入账人 入账日期- - - - -zy2016004 采油二矿1队 s002 12000张强 2016-05-01 2016-05-04 2016-05-24 作业公司作业三队防砂 6000

34、2000 1000 1600 10600李想 2016-05-26 10600 赵六 2016-05-28作业项目编号 预算单位 井号 预算金额- - - -预算人 预算日期 开工日期 完工日期 施工单位- - - - -施工内容 材料费 人工费 设备费 其它费用 结算金额- - - - - -结算人 结算日期 入账金额 入账人 入账日期- - - - -zy2016005 采油二矿2队 y005 12000张强 2016-05-01 2016-05-04 2016-05-28 作业公司作业三队防砂 7000 1000 2000 1300 11300李想 2016-05-28(6) 材料费表S

35、QL> create table goodscost_tb 2 ( 3 作业项目编号 varchar2(20) not null, 4 物码 varchar2(20) not null, 5 名称规格 varchar2(20) not null, 6 材料费 number(10) not null, 7 constraint pk_goodscost primary key(作业项目编号,物码) 8 ) 9 /表已创建。SQL> insert into goodscost_tb values('zy2016001','wm001','材料一&

36、#39;,2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb values('zy2016001','wm002','材料二',2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb values('zy2016001','wm003','材料三',2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb values('zy20

37、16001','wm004','材料四',1000.00);已创建 1 行。提交完成。SQL>SQL> insert into goodscost_tb values('zy2016002','wm001','材料一',2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb values('zy2016002','wm002','材料二',2000.00);已创建 1 行。提交完成。SQL>

38、; insert into goodscost_tb values('zy2016002','wm003','材料三',2000.00);已创建 1 行。提交完成。SQL>SQL> insert into goodscost_tb values('zy2016003','wm001','材料一',2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb values('zy2016003','wm002'

39、,'材料二',2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb values('zy2016003','wm003','材料三',2500.00);已创建 1 行。提交完成。SQL>SQL> insert into goodscost_tb values('zy2016004','wm001','材料一',2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb

40、 values('zy2016004','wm002','材料二',2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb values('zy2016004','wm004','材料四',2000.00);已创建 1 行。提交完成。SQL>SQL> insert into goodscost_tb values('zy2016005','wm001','材料一',2000.00);已创

41、建 1 行。提交完成。SQL> insert into goodscost_tb values('zy2016005','wm002','材料二',2000.00);已创建 1 行。提交完成。SQL> insert into goodscost_tb values('zy2016005','wm004','材料四',3000.00);已创建 1 行。提交完成。SQL> select * from goodscost_tb;作业项目编号 物码 名称规格 材料费- - - -zy201

42、6001 wm001 材料一 2000zy2016001 wm002 材料二 2000zy2016001 wm003 材料三 2000zy2016001 wm004 材料四 1000zy2016002 wm001 材料一 2000zy2016002 wm002 材料二 2000zy2016002 wm003 材料三 2000zy2016003 wm001 材料一 2000zy2016003 wm002 材料二 2000zy2016003 wm003 材料三 2500zy2016004 wm001 材料一 2000作业项目编号 物码 名称规格 材料费- - - -zy2016004 wm002

43、材料二 2000zy2016004 wm004 材料四 2000zy2016005 wm001 材料一 2000zy2016005 wm002 材料二 2000zy2016005 wm004 材料四 3000已选择16行。-3. 利用SQL PLUS查询以下数据：1) 采油一矿2队2016-5-1到2016-5-28有哪些项目处于结算状态，列出相应材料费明细。SQL> select * from goodscost_tb where 作业项目编号 in(select 作业项目编号 from zyb_tb where 结算日期 between to_date('2016-05-01','yyyy-mm-dd') and to_date('2016-5-28','yyyy-mm-dd') and 预算单位='采油一矿2队');作业项目编号 物码 名称规格 材料费- - - -zy2016002 wm001 材料一 2000zy2016002 wm002 材料二 2000zy2016002 wm003 材料三 2000zy2016003 wm