Oracle大作业

《Oracle数据库管理与维护》

课 程 大 作 业

专业班级 计算机 姓 名 学 号 完成日期 2016-06-30

得分 评阅人 1 / 42

将下面的题目在Oracle11g中实现，电子版包括题目编号、所有实现功能的SQL*PLUS命令或SQL或PL/SQL语句及运行结果。

在每位同学自己的用户下，完成以下功能：

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

zyb）。（作业：可理解为对油/水井进行大修） 2) 成本的消耗包括预算、结算、入账三个状态。

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

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

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

3) 预算状态时需要录入的数据：（★：字符型 ◆数值型 ▲日期

型）

★单据号：某一作业项目的编号 ★预算单位：指需要对油水井实施作业的采油队 ★井号：需要实施作业的油水井

◆预算金额 ★预算人 ▲预算日期 4) 结算状态需要录入的数据：（★：字符型 ◆数值型 ▲日期型）

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

▲开工日期 ▲完工日期 ★施工单位 ★施工内容 ◆ 材料费（要求另外建表单独记录材料消耗的明细）

（★物码 ◆消耗数量 ◆单价） ◆ 人工费 ◆设备费 ◆其它费用

◆ 结算金额（材料费+人工费+设备费+其它费用） ★结算人 ▲结算日期 5) 入账状态需要录入的数据：（★：字符型 ◆数值型 ▲日期型）

调出某条结算记录，录入以下数据： ◆入账金额 ★入账人 ▲入账日期

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

字符型）

单位代码表：★单位代码 ★单位名称

油水井表：★井号 ★井别：油井/水井 ★单位代码（表示某口井由哪个单位负责管理） 施工单位表：★施工单位名称

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

2 / 42

1． 根据对以上题意的理解，利用SQL PLUS，定义所需的数据表，包括

定义相关约束条件（主码、外码及其他约束）。 2. 利用SQL PLUS，录入以下相关的基础数据：

单位代码表：

单位代码 1122 112201 112202 112201001 112201002 112201003 112202001 112202002 油水井表： 井号 y001 y002 y003 s001 y004 s002 s003 y005 井别 油 油 油 水 油 水 水 油 单位代码 112201001 112201001 112201002 112201002 112201003 112202001 112202001 112202002 单位名称 采油厂 采油一矿 采油二矿 采油一矿1队 采油一矿2队 采油一矿3队 采油二矿1队 采油二矿2队 施工单位表： 施工单位名称 作业公司作业一队 作业公司作业二队 作业公司作业三队 物码表： 物码 wm001 wm002 3 / 42

名称规格 材料一 材料二 计量单位 吨 米 wm003 wm004 材料三 材料四 桶 袋

某作业项目编号 zy2016001

预算单位：采油一矿1队 井号：y001 预算金额：10000.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 预算人：张强 预算日期: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-6 完工日期：2016-5-23 施工单位：作业公司作业二队 施工内容：检泵

材料费：6500.00(其中材料一：2000.00 材料二：2000.00 材料三：2500.00)

人工费：2000.00 设备费:500.00 其它费用:1400.00 结算金额：

4 / 42

10400.00

结算人：李想 结算日期：2016-5-26

入账金额：10400.00 入账人：王丽 入账日期：2016-5-28

某作业项目编号 zy2016004

预算单位：采油二矿1队 井号：s002 预算金额：12000.00 预算人：张强 预算日期:2016-5-1

开工日期：2016-5-4 完工日期：2016-5-24 施工单位：作业公司作业三队 施工内容：防砂

材料费：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.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

5 / 42

2 (

3 单位代码 varchar2(20) constraint pk_unitno primary key, 4 单位名称 varchar2(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_tb values('112201001','采油一矿1队');

已创建 1 行。

提交完成。

SQL> insert into unitno_tb values('112201002','采油一矿2队');

已创建 1 行。

提交完成。

SQL> insert into unitno_tb values('112201003','采油一矿3队');

已创建 1 行。

6 / 42

提交完成。

SQL> insert into unitno_tb values('112202001','采油二矿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 table 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 /

表已创建。

7 / 42

SQL> insert into oilwell_tb values('y001','油','112201001');

已创建 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 行。

提交完成。

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 行。

提交完成。

SQL> insert into oilwell_tb values('y005','水','112202002');

8 / 42

已创建 1 行。

提交完成。

SQL> select * from oilwell_tb;

井号 井别 单位代码 -------------------- -------------------- --------------------

y001 油 112201001 y002 油 112201001 y003 油 112201002 s001 水 112201002 y004 油 112201003 s002 水 112202001 s003 水 112202001 y005 水 112202002

已选择8行。

(3) 施工单位表

SQL> 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 行。

9 / 42

提交完成。

SQL> insert into workunit_tb values('作业公司作业三队');

已创建 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','材料一','吨');

已创建 1 行。

提交完成。

SQL> insert into object_tb values('wm002','材料二','米');

已创建 1 行。

提交完成。

SQL> insert into object_tb values('wm003','材料三','桶');

10 / 42

已创建 1 行。

提交完成。

SQL> insert into object_tb values('wm004','材料四','袋');

已创建 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 预算金额 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),

11 / 42

18 结算人 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','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('2016-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-dd'),'作业公司作业二队','检

12 / 42

泵',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队','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-

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','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 行。

提交完成。

13 / 42

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('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;

作业项目编号 预算单位 井号 预算金额

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

预算人 预算日期 开工日期 完工日期 施工单位 -------------------- ---------- ---------- ---------- --------------------

施工内容 材料费 人工费 设备费 其它费用 结算金额

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

结算人 结算日期 入账金额 入账人 入账日期

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

zy2016001 采油一矿1队 y001

14 / 42

10000

张强 2016-05-01 2016-05-04 2016-05-25 作业公司作业一队

堵漏 7000 2500 1000 1400 11900

李想 2016-05-26 11900 王丽 2016-05-28

作业项目编号 预算单位 井号 预算金额

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

预算人 预算日期 开工日期 完工日期 施工单位 -------------------- ---------- ---------- ---------- --------------------

施工内容 材料费 人工费 设备费 其它费用 结算金额

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

结算人 结算日期 入账金额 入账人 入账日期

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

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

作业项目编号 预算单位 井号 预算金额

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

预算人 预算日期 开工日期 完工日期 施工单位 -------------------- ---------- ---------- ---------- --------------------

施工内容 材料费 人工费 设备费 其它费用 结算金额

15 / 42

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

结算人 结算日期 入账金额 入账人 入账日期

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

zy2016003 采油一矿2队 s001 10500

张强 2016-05-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 2000 1000 1600 10600

李想 2016-05-26 10600 赵六 2016-05-28

作业项目编号 预算单位 井号 预算金额

16 / 42

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

预算人 预算日期 开工日期 完工日期 施工单位 -------------------- ---------- ---------- ---------- --------------------

施工内容 材料费 人工费 设备费 其它费用 结算金额

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

结算人 结算日期 入账金额 入账人 入账日期

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

zy2016005 采油二矿2队 y005 12000

张强 2016-05-01 2016-05-04 2016-05-28 作业公司作业三队

防砂 7000 1000 2000 1300 11300

李想 2016-05-28

(6) 材料费表

SQL> 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','材料一',2000.00);

已创建 1 行。

提交完成。

SQL> insert into goodscost_tb values('zy2016001','wm002','材料二',2000.00);

17 / 42

已创建 1 行。

提交完成。

SQL> insert into goodscost_tb values('zy2016001','wm003','材料三',2000.00);

已创建 1 行。

提交完成。

SQL> insert into goodscost_tb values('zy2016001','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> 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','材料二',2000.00);

18 / 42

已创建 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 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);

已创建 1 行。

提交完成。

SQL> insert into goodscost_tb values('zy2016005','wm002','材料二',2000.00);

已创建 1 行。

提交完成。

SQL> insert into goodscost_tb values('zy2016005','wm004','材料四',3000.00);

19 / 42

已创建 1 行。

提交完成。

SQL> select * from goodscost_tb;

作业项目编号 物码 名称规格

材料费

-------------------- -------------------- -------------------- ---------- zy2016001 wm001 2000

zy2016001 wm002 2000

zy2016001 wm003 2000

zy2016001 wm004 1000

zy2016002 wm001 2000

zy2016002 wm002 2000

zy2016002 wm003 2000

zy2016003 wm001 2000

zy2016003 wm002 2000

zy2016003 wm003 2500

zy2016004 wm001 2000

作业项目编号 物码 材料费

-------------------- -------------------- -------------------- ---------- zy2016004 wm002 2000

zy2016004 wm004

20 / 42

材料一 材料二 材料三 材料四 材料一 材料二 材料三 材料一 材料二 材料三 材料一 名称规格 材料二 材料四

2000

zy2016005 wm001 材料一 2000

zy2016005 wm002 材料二 2000

zy2016005 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 材料一 2000

zy2016002 wm002 材料二 2000

zy2016002 wm003 材料三 2000

zy2016003 wm001 材料一 2000

zy2016003 wm002 材料二 2000

zy2016003 wm003 材料三 2500

21 / 42

已选择6行。

2) 采油一矿2队2016-5-1到2016-5-28总的入账金额。

SQL> select sum(入账金额) from zyb_tb where 入账日期 between to_date('2016-05

-01','yyyy-mm-dd') and to_date('2016-5-28','yyyy-mm-dd') and 预算单位='采油一

矿2队';

SUM(入账金额) -------------

21300

3) 采油一矿2队2016-5-1到2016-5-28总的预算金额。

SQL> select sum(预算金额) from zyb_tb where 预算日期 between to_date('2016-05

-01','yyyy-mm-dd') and to_date('2016-5-28','yyyy-mm-dd') and 预算单位='采油一

矿2队';

SUM(预算金额) -------------

21500

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

SQL> select distinct 入账人 from zyb_tb;

入账人

-------------------- 王丽 赵六

22 / 42

5) 2016-5-1到2016-5-28有结算未入帐的项目。

SQL> select 作业项目编号 from zyb_tb where 结算日期>=to_date('2016-05-01'

,'yyyy-mm-dd') and 结算日期<=to_date('2016-05-28','yyyy-mm-dd') and 入账

人 is null;

作业项目编号 -------------------- zy2016005

6) 列出采油一矿2队的所有项目，按入账金额从高到低排列。 SQL> select 作业项目编号,入账金额 from zyb_tb where 预算单位='采油一矿2队

' order by 入账金额 desc;

作业项目编号 入账金额 -------------------- ----------

zy2016002 10900 zy2016003 10400

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

和。

SQL> select 预算单位,sum(结算金额) from zyb_tb group by 预算单位;

预算单位 SUM(结算金额) -------------------- -------------

采油一矿1队 11900 采油一矿2队 21300 采油二矿2队 11300 采油二矿1队 10600

8) 找出消耗了材料三且消耗超过了2000元的项目，列出相应明细

(利用子查询)。

SQL> select * from zyb_tb where 作业项目编号 in (select 作业项目编号 from

23 / 42

goodscost_tb where 材料费>2000 and 名称规格='材料三');

作业项目编号 预算单位 井号 预算金额

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

预算人 预算日期 开工日期 完工日期 施工单位

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

施工内容 材料费 人工费 设备费 其它费用 结算金额

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

结算人 结算日期 入账金额 入账人 入账日期

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

zy2016003 采油一矿2队 s001 10500

张强 2016-05-01 2016-05-06 2016-05-23 作业公司作业二队

检泵 6500 2000 500 1400 10400

李想 2016-05-26 10400 王丽 2016-05-28

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

SQL> select 作业项目编号 from zyb_tb where 施工单位='作业公司作业二队';

作业项目编号 -------------------- zy2016002 zy2016003

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

SQL> select 作业项目编号 from zyb_tb where 施工单位='作业公司作业一队' union

select 作业项目编号 from zyb_tb where 施工单位='作业公司作业二

24 / 42

队';

作业项目编号 -------------------- zy2016001 zy2016002 zy2016003

4. 利用SQL PLUS完成以下操作：

1) 将编号为zy2016005的项目的人工费和结算金额增加100元。 SQL> select 作业项目编号,人工费,结算金额 from zyb_tb;

作业项目编号 人工费 结算金额 -------------------- ---------- ----------

zy2016001 2500 11900 zy2016002 1500 10900 zy2016003 2000 10400 zy2016004 2000 10600 zy2016005 1000 11300

SQL> update zyb_tb set 人工费=人工费+100,结算金额=结算金额+100 where 作业项目

编 号='zy2016005';

已更新 1 行。

提交完成。

SQL> select 作业项目编号,人工费,结算金额 from zyb_tb; ;

作业项目编号 人工费 结算金额 -------------------- ---------- ----------

zy2016001 2500 11900 zy2016002 1500 10900 zy2016003 2000 10400 zy2016004 2000 10600 zy2016005 1100 11400

25 / 42

2) 删除已结算未入帐的项目。

SQL> select 作业项目编号,人工费,结算金额 from zyb_tb; ;

作业项目编号 人工费 结算金额 -------------------- ---------- ----------

zy2016001 2500 11900 zy2016002 1500 10900 zy2016003 2000 10400 zy2016004 2000 10600 zy2016005 1100 11400

SQL> delete from zyb_tb where 结算金额 is not null and 入账金额 is null;

已删除 1 行。

提交完成。

SQL> select 作业项目编号,人工费,结算金额 from zyb_tb;

作业项目编号 人工费 结算金额 -------------------- ---------- ----------

zy2016001 2500 11900 zy2016002 1500 10900 zy2016003 2000 10400 zy2016004 2000 10600

5. 利用SQL PLUS进行如下事务处理练习（把下列五条语句作为一个事

务处理，只有五条语句全部成功执行才做提交，并给出提示信息；否则就做回滚处理，并给出提示信息）：

insert into zyb values('zy2016006','112202002','y005',10000,'张强', '05-01-2016' ,

'05-04-2016','05-25-2016','作业公司作业一队','堵漏',7000,2500,1000,1400,11900,'李想','05-26-2016',11900,'王丽','05-28-2016')

insert into clfb values('zy2016006','wm001',200,10) insert into clfb values('zy2016006','wm002',200,10) insert into clfb values('zy2016006','wm003',200,10) insert into clfb values('zy2016006','wm004',100,10)

26 / 42

SQL> set serverout on; SQL> begin

2 insert into zyb_tb values('zy2016006','112202002','y005',10000,'张强',

'05-01-2016' ,'05-04-2016','05-25-2016','作业公司作业一队','堵

漏',7000,2500,1000,1400,11900,'李想','05-26-2016',11900,'王丽','05-28-2016');

3 insert into goodscost_tb values('zy2016006','wm001','材料一',2000); 4 insert into goodscost_tb values('zy2016006','wm002','材料二',2000); 5 insert into goodscost_tb values('zy2016006','wm003','材料三',2000); 6 insert into goodscost_tb values('zy2016006','wm004','材料四',1000); 7 commit;

8 dbms_output.PUT_LINE('All commited!'); 9 exception

10 when others then 11 rollback;

12 dbms_output.PUT_LINE('Error!'); 13 end; 14 / Error!

PL/SQL 过程已成功完成。

提交完成。

SQL> select * from zyb_tb where 作业项目编号='zy2016006';

未选定行

6. 利用SQL PLUS进行如下游标练习：

定义一个游标，用于存放作业表的全部行数据。并打印以下表头和各行数据。

表头：单据号 消耗单位 井号 预算金额 预算人 预算日期 开工日期 完工日期 施工单位 施工内容 材料费 人工费 设备费 其它费用 结算金额 结算人 结算日期 入账金额 入账人 入账日期

执行以上所定义的游标，查看是否能正确输出结果。

27 / 42

SQL> declare

2 作业项目编号 varchar2(20); 3 预算单位 varchar2(20); 4 井号 varchar2(20); 5 预算金额 number(10); 6 预算人 varchar2(20); 7 预算日期 date; 8 开工日期 date; 9 完工日期 date;

10 施工单位 varchar2(20); 11 施工内容 varchar2(20); 12 材料费 number(10); 13 人工费 number(10); 14 设备费 number(10); 15 其它费用 number(10); 16 结算金额 number(10); 17 结算人 varchar2(20); 18 结算日期 date;

19 入账金额 number(10); 20 入账人 varchar2(20); 21 入账日期 date; 22 cursor zyb_cur 23 is

24 select * from zyb_tb; 25 begin

26 open zyb_cur;

27 fetch zyb_cur into 作业项目编号,预算单位,井号,预算金额,预算人,预算日期,开

工日期,完工日期,施工单位,施工内容,材料费,人工费,设备费,其它费用,结算金额,结算

人,结算日期,入账金额,入账人,入账日期; 28

29 dbms_output.put_line('作业项目编号 '||'预算单位 '||'井号 '||'预算金额

28 / 42

'||' 预算人 '||'预算日期 '||'开工日期 '||'完工日期 '||'施工单位 '||'施工内容

'||'材料费 '||'人工费 '||'设备费 '||'其它费用 '||'结算金额 '||'结算人 '||'结算

日期 '||' 入账金额 '||'入账人 '||'入账日期'); 30 while zyb_cur%found 31 loop

32 dbms_output.put_line(作业项目编号||' '||预算单位||' '||井号||' '||预算金

额||' '||预算人||' '||预算日期||' '||开工日期||' '||完工日期||' '||施工单位||'

'|| 施工内容||' '||材料费||' '||人工费||' '||设备费||' '||其它费用||' '||结算

金额||' '||结算人||' '||结算日期||' '||入账金额||' '||入账人||' '||入账日期); 33

34 fetch zyb_cur into 作业项目编号,预算单位,井号,预算金额,预算人,预算日期,开

工日期,完工日期,施工单位,施工内容,材料费,人工费,设备费,其它费用,结算金额,结算

人,结算日期,入账金额,入账人,入账日期; 35

36 end loop;

37 close zyb_cur; 38 end; 39 /

作业项目编号 预算单位 井号 预算金额 预算人 预算日期 开工日期 完工日期 施工单位

施工内容 材料费 人工费 设备费 其它费用 结算金额 结算人 结算日期 入账金额 入账

人

入账日期

29 / 42

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

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-05-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 2000 1000 1600 10600 李想 2016-05-26 10600 赵六

2016-05-28

PL/SQL 过程已成功完成。

提交完成。

7. 利用SQL PLUS进行如下视图练习：

利用SQL PLUS定义一个视图，用于保存作业表和材料费表的全部列。 利用SQL PLUS查询该视图，可任意选择查询条件，构造出2个查询。

SQL> create or replace view zyb_goodscost_view 2 as 3 select

4 zyb_tb.作业项目编号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工

日期,施工单位,施工内容,zyb_tb.材料费,人工费,设备费,其它费用,结算金额,结算人,结

30 / 42

算日期,入账金额,入账人,入账日期,goodscost_tb.物码,goodscost_tb.名称规格

5 from zyb_tb,goodscost_tb

6 where zyb_tb.作业项目编号=goodscost_tb.作业项目编号;

视图已创建。

SQL> select 预算单位,井号,预算金额 from zyb_goodscost_view where 作业项目编号

='zy2016003';

预算单位 井号 预算金额 -------------------- -------------------- ----------

采油一矿2队 s001 10500 采油一矿2队 s001 10500 采油一矿2队 s001 10500

8. 利用SQL PLUS定义一个存储过程，要求完成以下功能：

生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况（输入参数：单位代码 起始日期 结束日期）

输出格式 ***单位**时间---**时间成本运行情况

预算金额 结算金额 入账金额 未结算金额 未入账金额 ****.** ****.** ****.** ****.** ****.**

其中未结算金额=预算金额-结算金额 未入账金额=结算金额-入账金额 SQL> create or replace procedure costwork

2 (单位代码1 in varchar2,starttime in date,finishtime in date) 3 is

4 f varchar2(20); 5

6 预算日期2 date; 7 结算日期2 date; 8 入账日期2 date;

31 / 42

9 预算金额2 number(10); 10 结算金额2 number(10); 11 入账金额2 number(10); 12 未结算金额 number(10); 13 未入账金额 number(10); 14 begin

15 select 单位名称 into f from unitno_tb where 单位代码1=单位代码; 16 select distinct 预算日期 into 预算日期2 from zyb_tb where f=zyb_tb.预算单 位;

17 select distinct 结算日期 into 结算日期2 from zyb_tb where f=zyb_tb.预算单 位;

18 select distinct 入账日期 into 入账日期2 from zyb_tb where f=zyb_tb.预算单 位; 19

20 select sum(预算金额) into 预算金额2 from zyb_tb where f=zyb_tb.预算单位

and (预算日期2 between starttime and finishtime); 21

22 if(结算日期2>starttime or 结算日期2=starttime) and (结算日期2

or 结算日期2=finishtime) then select sum(结算金额) into 结算金额2 from zyb_tb

where f=zyb_tb.预算单位; 23 else 结算金额2:=0; 24 end if; 25

26 if(入账日期2>starttime or 入账日期2=starttime) and (入账日期2

32 / 42

or 入账日期2=finishtime) then select sum(入账金额) into 入账金额2 from zyb_tb

where f=zyb_tb.预算单位; 27 else 入账金额2:=0; 28 end if; 29

30 未结算金额:=预算金额2-结算金额2; 31 未入账金额:=结算金额2-入账金额2; 32

33 dbms_output.put_line(f||'单位'||starttime||'时间'||'---'||finishtime||'时

间成本运行情况');

34 dbms_output.put_line('预算金额 '||'结算金额 '||'入账金额 '||'未结算金

额 '||'未入账金额 ');

35 dbms_output.put_line(预算金额2||' '||结算金额2||' '||入账金额2||' '||

未 结算金额||' '||未入账金额); 36

37 end; 38 /

过程已创建。

SQL> exec costwork('112201002',to_date('2016-05-01','yyyy-mm-dd'),to_date

('2016-05-28','yyyy-mm-dd'));

采油一矿2队单位2016-05-01时间---2016-05-28时间成本运行情况 预算金额 结算金额 入账金额 未结算金额 未入账金额 21500 21300 21300 200 0

PL/SQL 过程已成功完成。

提交完成。

33 / 42

9. 利用SQL PLUS针对zyb定义三个触发器，分别完成以下功能：

1) 对zyb插入一行数据时，自动计算并插入结算金额字段（结算金

额=材料费+人工费+设备费+其它费用）

2) 当修改作业表的某行数据时自动修改结算金额字段

3) 当删除作业表中一行数据时，自动删除材料费表中相应明细数据 (1)

SQL> create or replace trigger insert_jsje 2 before insert on zyb_tb for each row 3 begin

4 :new.结算金额:=:new.材料费+:new.人工费+:new.设备费+:new.其它费用;

5 end insert_jsje; 6 /

触发器已创建

//结算金额:=0;

SQL> insert into zyb_tb values('zy2016007','采油二矿1队','s002',12000.00,'张强

',to_date('2016-05-01','yyyy-mm-dd'),to_date('2016-05-04','yyyy-mm-

dd'),to_date('2016-05-24','yyyy-mm-dd'),'作业公司作业三队','防 砂',6000.00,2000.00,1000.00,1600.00,0,'李想',to_date('2016-05-26','yyyy-mm-

dd'),10600.00,'赵六',to_date('2016-05-28','yyyy-mm-dd'));

已创建 1 行。

提交完成。

SQL> select * from zyb_tb where 作业项目编号='zy2016007';

作业项目编号 预算单位 井号

34 / 42

预算金额

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

预算人 预算日期 开工日期 完工日期 施工单位

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

施工内容 材料费 人工费 设备费 其它费用 结算金额

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

结算人 结算日期 入账金额 入账人 入账日期

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

zy2016007 采油二矿1队 s002 12000

张强 2016-05-01 2016-05-04 2016-05-24 作业公司作业三队

防砂 6000 2000 1000 1600 10600

李想 2016-05-26 10600 赵六 2016-05-28

//结算金额:=10600;

(2)

SQL> create or replace trigger update_jsje 2 before update on zyb_tb for each row 3 begin

4 :new.结算金额:=:new.材料费+:new.人工费+:new.设备费+:new.其它费用;

5 end update_jsje; 6 /

触发器已创建

SQL> update zyb_tb set 材料费=5000 where 作业项目编号='zy2016007';

已更新 1 行。

35 / 42

提交完成。

SQL> select * from zyb_tb where 作业项目编号='zy2016007';

作业项目编号 预算单位 井号 预算金额

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

预算人 预算日期 开工日期 完工日期 施工单位

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

施工内容 材料费 人工费 设备费 其它费用 结算金额

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

结算人 结算日期 入账金额 入账人 入账日期

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

zy2016007 采油二矿1队 s002 12000

张强 2016-05-01 2016-05-04 2016-05-24 作业公司作业三队

防砂 5000 2000 1000 1600 9600

李想 2016-05-26 10600 赵六 2016-05-28

(3)

SQL> select * from goodscost_tb;

作业项目编号 物码 名称规格 材料费

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

zy2016001 wm001 材料一 2000

zy2016001 wm002 材料二 2000

zy2016001 wm003 材料三 2000

zy2016001 wm004 材料四

36 / 42

1000

zy2016002 wm001 材料一 2000

zy2016002 wm002 材料二 2000

zy2016002 wm003 材料三 2000

zy2016003 wm001 材料一 2000

zy2016003 wm002 材料二 2000

zy2016003 wm003 材料三 2500

zy2016004 wm001 材料一 2000

作业项目编号 物码 名称规格 材料费

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

zy2016004 wm002 材料二 2000

zy2016004 wm004 材料四 2000

zy2016005 wm001 材料一 2000

zy2016005 wm002 材料二 2000

zy2016005 wm004 材料四 3000

已选择16行。

SQL> create or replace trigger delete_row 2 before delete on zyb_tb for each row 3 begin

4 delete from goodscost_tb where 作业项目编号=:old.作业项目编号;

37 / 42

5 end delete_row; 6 /

触发器已创建

SQL> delete from zyb_tb where 作业项目编号='zy2016003';

已删除 1 行。

提交完成。

SQL> select * from goodscost_tb;

作业项目编号 物码 材料费

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

zy2016001 wm001 2000

zy2016001 wm002 2000

zy2016001 wm003 2000

zy2016001 wm004 1000

zy2016002 wm001 2000

zy2016002 wm002 2000

zy2016002 wm003 2000

zy2016004 wm001 2000

zy2016004 wm002 2000

zy2016004 wm004 2000

zy2016005 wm001 38 / 42

名称规格 材料一 材料二 材料三 材料四 材料一 材料二 材料三 材料一 材料二 材料四 材料一

2000

作业项目编号 物码 名称规格 材料费

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

zy2016005 wm002 材料二 2000

zy2016005 wm004 材料四 3000

已选择13行。

10. 权限管理

利用SQL PLUS新建立一个用户（名称自定义），要求授予他查询/增加/删除/修改 单位代码表/油水井表/施工单位表/物码表的权限并只授予他查询作业表/材料费表的权限，并且利用SQL PLUS对该新建用户进行测试（通过做DML操作来检测该用户的权限）。

SQL> create user violet identified by violet default tablespace users

temporary tablespace temp;

用户已创建。

SQL> grant select,insert,delete,update on unitno_tb to violet;

授权成功。

SQL> grant select,insert,delete,update on oilwell_tb to violet;

授权成功。

SQL> grant select,insert,delete,update on workunit_tb to violet;

授权成功。

SQL> grant select,insert,delete,update on object_tb to violet;

39 / 42

授权成功。

SQL> grant select on zyb_tb to violet;

授权成功。

SQL> grant select on goodscost_tb to violet;

授权成功。

SQL> grant create session to violet;

授权成功。

SQL> conn violet /violet 已连接。

SQL> select * from system.oilwell_tb;

井号 井别 单位代码 -------------------- -------------------- --------------------

y001 油 112201001 y002 油 112201001 y003 油 112201002 s001 水 112201002 y004 油 112201003 s002 水 112202001 s003 水 112202001 y005 水 112202002

已选择8行。

SQL> select * from system.goodscost_tb;

作业项目编号 物码 名称规格 材料费

40 / 42

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

zy2016001 wm001 材料一 2000

zy2016001 wm002 材料二 2000

zy2016001 wm003 材料三 2000

zy2016001 wm004 材料四 1000

zy2016002 wm001 材料一 2000

zy2016002 wm002 材料二 2000

zy2016002 wm003 材料三 2000

zy2016004 wm001 材料一 2000

zy2016004 wm002 材料二 2000

zy2016004 wm004 材料四 2000

zy2016005 wm001 材料一 2000

作业项目编号 物码 名称规格 材料费

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

zy2016005 wm002 材料二 2000

zy2016005 wm004 材料四 3000

已选择13行。

SQL> insert into system.goodscost_tb values('zy2016003','wm001','材料

一',2000.00);

41 / 42

insert into system.goodscost_tb values('zy2016003','wm001','材料一',2000.00)

* 第 1 行出现错误: ORA-01031: 权限不足

42 / 42

本文来源：https://www.bwwdw.com/article/6ew8.html