数据库oracle实习作业完整版

地信09—1班
彭女佳
-创建作业表：
CREATE TABLE "ADMIN"."ZYB" ( "DJH" VARCHAR2(10), "YSDW" VARCHAR2(12), "JH" VARCHAR2(5), "YSJE" FLOAT(126), "YSR" VARCHAR2(10), "YSRQ" DATE, "KGRQ" DATE, "WGRQ" DATE, "SGDW" VARCHAR2(20), "SGNR" VARCHAR2(20), "CLF" FLOAT(126), "RGF" FLOAT(126), "SBF" FLOAT(126), "QTFY" FLOAT(126), "JSJE" FLOAT(126), "JSR" VARCHAR2(10), "JSRQ" DATE, "RZJE" FLOAT(126), "RZR" VARCHAR2(10), "RZRQ" DATE, PRIMARY KEY("DJH") VALIDATE) TABLESPACE"USERS" PCTFREE10 INITRANS1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
创建单位代码表：
CREATE TABLE "ADMIN".”DWDMB” ( "DWDM" VARCHAR2(10), "DWMC" VARCHAR2(20), PRIMARY KEY ("DWDM") VALIDATE ) TABLESPACE "SYSTEM" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING
创建油水井表：
CREATE TABLE"ADMIN".”YSJB”( "JH" VARCHAR2(5), "JB" CHAR(2), "DWDM" VARCHAR2(10), PRIMARY KEY ("JH") VALIDATE ) TABLESPACE "SYSTEM" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING
创建施工单位表：
CREATE TABLE"ADMIN".”SGDWB”( "SGDWMC" VARCHAR2(20), PRIMARY KEY("SGDWMC") VALIDATE ) TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE
( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
创建物码表：
CREATE TABLE "ADMIN".”WMB” ( "WM" VARCHAR2(5), "MCGG" VARCHAR2(10), "JLDW" VARCHAR2(10), PRIMARY KEY ("WM") VALIDATE ) TABLESPACE "SYSTEM" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING
创建材料费：
CREATE TABLE "ADMIN". “CLF”( "DJH" VARCHAR2(10), "CLY" FLOAT(126), "CLE" FLOAT(126), "CLSA" FLOAT(126), "CLSI" FLOAT(126), PRIMARY KEY("DJH") VALIDATE) TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
创建材料费表：
CREATE TABLE "ADMIN".“CLFB” ( "DJH" VARCHAR2(10), "WM" VARCHAR2(5), "XHSL" NUMBER, "DJ" FLOAT(126), PRIMARY KEY ("DJH") VALIDATE ) TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
插入数据（每个举一例，不再赘述）：
插入作业表数据（eg2）:
INSERT INTO ZYB V ALUES('zy2002002','采油一矿2队','y003','11000.00','张三',TO_DA TE('20020501','YYYYMMDD'),TO_DA TE('20020504','YYYYMMDD'),TO_DA TE('200 20523','YYYYMMDD'),'作业公司作业二队','检泵','6000.00','1500.00','1000.00','2400.00','10900.00','李四',TO_DA TE('20020526','YYYYMMDD'),'10900.00','王五',TO_DA TE('20020528','YYYYMMDD'));
插入单位代码表数据（eg1）:
INSERT INTO DWDMB V ALUES('1122','采油厂');
插入油水井表数据（eg1）：
INSERT INTO YSJB V ALUES('y001’,’油’,’112201001');
插入施工单位表数据（eg1）:
INSERT INTO SGDWB V ALUES('作业公司作业一队');
插入物码表数据(eg1):
INSERT INTO WMB V ALUES('wm001’,’材料一’,’吨');
插入材料费数据（eg1）:
INSERT INTO CLF V ALUES('zy2002001','2000.00’,’2000.00’,’2000.00’,’1000.00');
五、查询数据：
1.
采油一矿2队2002-5-1到2002-5-28有哪些项目处于预算状态，列出相应明细。

SELECT * FROM ZYB WHERE YSDW='采油一矿2队' AND YSRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND TO_DA TE('20020528','YYYYMMDD');
结果：
2.
采油一矿2队2002-5-1到2002-5-28有哪些项目处于结算状态，列出相应明细。

SELECT * FROM ZYB WHERE YSDW='采油一矿2队' AND JSRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND TO_DA TE('20020528','YYYYMMDD');
结果：
3.
采油一矿2队2002-5-1到2002-5-28有哪些项目处于结算状态，列出相应材料费明细。

SELECT * FROM CLF WHERE DJH IN (SELECT DJH FROM ZYB WHERE YSDW='采油一矿2队' AND JSRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND
TO_DA TE('20020528','YYYYMMDD'));
结果：
DJH CLY CLE CLSA CLSI
---------- ---------- ---------- ---------- ----------
zy2002002 2000 2000 2000
zy2002003 2000 2000 2500
4.
采油一矿2队2002-5-1到2002-5-28有哪些项目处于入账状态，列出相应明细。

SELECT * FROM ZYB WHERE YSDW='采油一矿2队' AND RZRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND TO_DA TE('20020528','YYYYMMDD');
结果：
5.
采油一矿2队2002-5-1到2002-5-28总的预算金额。

SELECT SUM(YSJE) FROM ZYB WHERE YSDW='采油一矿2队' AND YSRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND TO_DA TE('20020528','YYYYMMDD');
结果：
SUM(YSJE)
----------
21500
6.
采油一矿2队2002-5-1到2002-5-28总的结算金额。

SELECT SUM(JSJE) FROM ZYB WHERE YSDW='采油一矿2队' AND JSRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND TO_DA TE('20020528','YYYYMMDD');
结果：
SUM(JSJE)
----------
21300
7.
采油一矿2队2002-5-1到2002-5-28总的入账金额。

SELECT SUM(RZJE) FROM ZYB WHERE YSDW='采油一矿2队' AND RZRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND TO_DA TE('20020528','YYYYMMDD');
结果：
SUM(RZJE)
----------
21300
8.
采油一矿2002-5-1到2002-5-28总的入账金额。

SELECT SUM(RZJE) FROM ZYB WHERE YSDW LIKE '采油一矿%' AND RZRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND TO_DA TE('20020528','YYYYMMDD');
结果：
SUM(RZJE)
----------
33200
9.
有哪些人员参与了入账操作。

SELECT DISTINCT RZR FROM ZYB;
结果：
RZR
----------
王五
赵六
10.
2002-5-1到2002-5-28有结算未入帐的项目。

SELECT DJH FROM ZYB WHERE JSRQ BETWEEN TO_DA TE('20020501','YYYYMMDD') AND TO_DA TE('20020528','YYYYMMDD') AND RZRQ > TO_DA TE('20020528','YYYYMMDD') OR RZRQ IS NULL;
结果：
DJH
----------
zy2002005
11.
列出采油一矿2队的所有项目，按入账金额从高到低排列。

SELECT DJH FROM ZYB WHERE YSDW='采油一矿2队' ORDER BY RZJE DESC;
结果：
DJH
----------
zy2002002
zy2002003
12.
列出有哪些单位实施了项目，并计算各单位所有项目结算金额总和。

SELECT SGDW,SUM(JSJE) FROM ZYB GROUP BY SGDW;
结果：
SGDW SUM(JSJE)
-------------------- ----------
作业公司作业二队21300
作业公司作业一队11900
作业公司作业三队21900
13.
找出消耗了材料三且消耗超过了2000元的项目，列出相应明细(利用子查询)。

SELECT * FROM ZYB WHERE DJH IN (SELECT DJH FROM CLF WHERE CLSA>2000);
结果：
14.
作业公司二队参与了哪些项目。

SELECT DJH FROM ZYB WHERE SGDW=’作业公司作业二队’;
结果：
DJH
----------
zy2002002
zy2002003
15.
作业公司一队和二队参与了哪些项目（利用union）。

SELECT DJH FROM ZYB WHERE SGDW=’作业公司作业一队’UNION SELECT DJH FROM ZYB WHERE SGDW=’作业公司作业二队’;
结果：
DJH
----------
zy2002001
zy2002002
zy2002003
六、利用SQL PLUS完成以下操作：
1)将编号为zy2002005的项目的人工费和结算金额增加100元。

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

1.
UPDA TE ZYB SET RGF=RGF+100,JSJE=JSJE+100 WHERE DJH=’zy2002005’;
2.
DELETE FROM ZYB WHERE RZRQ IS NULL;
七、利用SQL PLUS进行如下事务处理练习（把下列五条语句作为一个事务处理，只有五条语句全部成功执行才做提交，并给出提示信息；否则就做回滚处理，并给出提示信息）：begin
insert into zyb values('zy2002001','112201001','y001',10000,'张三','05-01-2002','05-04-2002','05-25-2002','作业公司作业一队','堵漏',7000,2500,1000,1400,11900,'李四','05-26-2002',11900,'王五','05-28-2002');
insert into clfb values('zy2002001','wm001',200,10);
insert into clfb values('zy2002001','wm002',200,10);
insert into clfb values('zy2002001','wm003',200,10);
insert into clfb values('zy2002001','wm004',100,10);
dbms_output.put_line('已经全部执行完毕');
commit;
exception when others then dbms_output.put_line('未全部执行完毕');
rollback;
end;
/
八、利用SQL PLUS进行如下游标练习：
定义一个游标，用于存放作业表的全部行数据。

并打印以下表头和各行数据。

表头：单据号消耗单位井号预算金额预算人预算日期开工日期完工日期施工单位施工内容材料费人工费设备费其它费用结算金额结算人结算日期入账金额入账人入账日期
执行以上所定义的游标，查看是否能正确输出结果。

DECLARE
V_DJH V ARCHAR2(10);
V_YSDW V ARCHAR2(12);
V_JH V ARCHAR2(5);
V_YSJE FLOA T;
V_YSR V ARCHAR2(10);
V_YSRQ DA TE;
V_KGRQ DA TE;
V_WGRQ DA TE;
V_SGDW V ARCHAR2(20);
V_SGNR V ARCHAR2(20);
V_CLF FLOA T;
V_RGF FLOA T;
V_SBF FLOA T;
V_QTFY FLOA T;
V_JSJE FLOA T;
V_JSR V ARCHAR2(10);
V_JSRQ DA TE;
V_RZJE FLOA T;
V_RZR V ARCHAR2(10);
V_RZRQ DA TE;
CURSOR ZYB_CUR
IS
SELECT * FROM ZYB;
BEGIN
OPEN ZYB_CUR;
FETCH ZYB_CUR INTO V_DJH,V_YSDW,V_JH,V_YSJE,V_YSR,V_YSRQ,V_KGRQ,V_WGRQ,V_SGDW,V_SGNR,V _CLF,V_RGF,V_SBF,V_QTFY,V_JSJE,V_JSR,V_JSRQ,V_RZJE,V_RZR,V_RZRQ;
WHILE ZYB_CUR%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE('单据号'||V_DJH||' 预算单位'||V_YSDW||' 井号'||V_JH||' 预算金额'||V_YSJE||' 预算人'||V_YSR||' 预算日期'||V_YSRQ||' 开工日期'||V_KGRQ||' 完工日期'||V_WGRQ||'施工单位'||V_SGDW||' 施工内容'||V_SGNR||' 材料费'||V_CLF||' 人工费'||V_RGF||' 设备费'||V_SBF||' 其它费用'||V_QTFY||' 结算金额'||V_JSJE||' 结算人'||V_JSR||' 结算日期'||V_JSRQ||' 入账金额'||V_RZJE||' 入账人'||V_RZR||' 入账日期'||V_RZRQ);
FETCH ZYB_CUR INTO V_DJH,V_YSDW,V_JH,V_YSJE,V_YSR,V_YSRQ,V_KGRQ,V_WGRQ,V_SGDW,V_SGNR,V _CLF,V_RGF,V_SBF,V_QTFY,V_JSJE,V_JSR,V_JSRQ,V_RZJE,V_RZR,V_RZRQ;
END LOOP;
CLOSE ZYB_CUR;
END;
/
九、利用SQL PLUS进行如下视图练习：
利用SQL PLUS定义一个视图，用于保存作业表和材料费的全部列。

利用SQL PLUS查询该视图，可任意构造一些查询条件。

1.
CREA TE OR REPLACE VIEW ZYB_CLFB
AS
SELECT
ZYB.DJH,YSDW,JH,YSJE,YSR,YSRQ,KGRQ,WGRQ,SGDW,SGNR,CLF,RGF,SBF,QTFY,JSJ E,JSR,JSRQ,RZJE,RZR,RZRQ,WM,XHSL,DJ
FROM ZYB,CLFB
WHERE ZYB.DJH=CLFB.DJH;
2.
SELECT DJH,JSR FROM ZYB_CLFB WHERE DJH=’zy2002001’;
十、利用OEM或SQL PLUS定义一个存储过程，要求完成以下功能：
生成某单位某段时间内的成本运行情况（输入参数：单位代码起始日期结束日期）输出格式***单位**时间---**时间成本运行情况
预算金额结算金额入账金额未结算金额未入账金额
****.** ****.** ****.** ****.** ****.** 其中未结算金额=预算金额-结算金额未入账金额=结算金额-入账金额
create or replace procedure cb
(dwdm1 in varchar2,sdate in date,edate in date)
As f varchar(20);ysrq2 date;jsrq2 date;rzrq2 date;ys float;js float;rz float;wjs float;wrz float;
begin
select dwmc into f from dwdmb where dwdm=dwdm1;
select distinct(ysrq) into ysrq2 from zyb where f=zyb.ysdw;
select distinct(jsrq) into jsrq2 from zyb where f=zyb.ysdw;
select distinct(rzrq) into rzrq2 from zyb where f=zyb.ysdw;
Select sum(ysje) into ys from zyb where f=zyb.ysdw and (ysrq2 between sdate and edate) ;
if (jsrq2>sdate or jsrq2=sdate) and (jsrq2<edate or jsrq2=edate)
then Select sum(jsje) into js from zyb where f=zyb.ysdw;
else js:=0; end if;
if (rzrq2>sdate or rzrq2=sdate) and (rzrq2<edate or rzrq2=edate)
then Select sum(rzje) into rz from zyb where f=zyb.ysdw;
else rz:=0;end if;
dbms_output.put_line( f ||sdate||'时间---'||edate||'时间成本运行情况');
dbms_output.put_line('预算金额'||''||' 结算金额'||''||' 入账金额'||''||' 未结算金额'||''||'未入账金额');
wjs:=ys-js;
wrz:=js-rz;
dbms_output.put_line(ys||' '||js||' '||rz||' '||wjs||' '||wrz);
end ;
示例：
exec
cb('112201002',TO_DA TE('20020501','YYYYMMDD'),TO_DA TE('20020520','YYYYMMDD'));
exec
cb('112201002',TO_DA TE('20020501','YYYYMMDD'),TO_DA TE('20020528','yyyymmdd'));
十一、利用OEM或SQL PLUS针对zyb定义三个触发器，分别完成以下功能：
1.对zyb插入一行数据时，自动计算并插入结算金额字段（结算金额=材料费+人工费+
设备费+其它费用）
2.当修改作业表的某行数据时自动修改结算金额字段
3.当删除作业表中一行数据时，自动删除材料表中相应明细数据。

1.
CREA TE OR REPLACE TRIGGER INSERT_JSJE
before INSERT ON ZYB FOR EACH ROW
BEGIN
:new.jsje:=:new.clf+:new.rgf+:new.sbf+:new.qtfy;
End INSERT_JSJE;
Eg 插入：
INSERT INTO ZYB V ALUES('zy2012012','采油一矿2队','y003','11000.00','张三',TO_DA TE('20020501','YYYYMMDD'),TO_DA TE('20020504','YYYYMMDD'),TO_DA TE('200 20523','YYYYMMDD'),'作业公司作业二队','检泵','6000.00','1500.00','1000.00','2400.00','','李四',TO_DA TE('20020526','YYYYMMDD'),'10900.00','王五',TO_DA TE('20020528','YYYYMMDD'));
2.
CREA TE OR REPLACE TRIGGER update_JSJE
before update ON ZYB FOR EACH ROW
BEGIN
:new.jsje:=:new.clf+:new.rgf+:new.sbf+:new.qtfy;
End update_JSJE;
Eg 修改：
Update zyb
Set rgf=’1000’
Where djh=’zy2002005’;
3.
Create or replace trigger delete_row
Before delete on zyb for each row
Begin
Delete from clfb where djh=:old.djh;
End delete_row;
十二、权限管理
利用OEM或SQL PLUS新建立一个用户，要求授予他查询/增加/删除/修改单位代码表/油水井表/施工单位表/物码表的权限并只授予他查询作业表/材料费表的权限，并且利用SQL PLUS对该新建用户进行测试。

CREATE USER"GUEST" PROFILE"DEFAULT" IDENTIFIED BY"*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT SELECT ON "ADMIN"."CLFB" TO "GUEST"
GRANT DELETE ON "ADMIN"."DWDMB" TO "GUEST"
GRANT INSERT ON "ADMIN"."DWDMB" TO "GUEST"
GRANT SELECT ON "ADMIN"."DWDMB" TO "GUEST"
GRANT UPDATE ON "ADMIN"."DWDMB" TO "GUEST"
GRANT DELETE ON "ADMIN"."SGDWB" TO "GUEST"
GRANT INSERT ON "ADMIN"."SGDWB" TO "GUEST"
GRANT SELECT ON "ADMIN"."SGDWB" TO "GUEST"
GRANT UPDATE ON "ADMIN"."SGDWB" TO "GUEST"
GRANT DELETE ON "ADMIN"."WMB" TO "GUEST"
GRANT INSERT ON "ADMIN"."WMB" TO "GUEST"
GRANT SELECT ON "ADMIN"."WMB" TO "GUEST"
GRANT UPDATE ON "ADMIN"."WMB" TO "GUEST"
GRANT DELETE ON "ADMIN"."YSJB" TO "GUEST"
GRANT INSERT ON "ADMIN"."YSJB" TO "GUEST"
GRANT SELECT ON "ADMIN"."YSJB" TO "GUEST"
GRANT UPDATE ON "ADMIN"."YSJB" TO "GUEST"
GRANT SELECT ON "ADMIN"."ZYB" TO "GUEST"
GRANT "CONNECT" TO "GUEST"。