Oracle數(shù)據(jù)庫管理與維護(hù)大作業(yè)

1、Oracle數(shù)據(jù)庫管理與維護(hù)大作業(yè) 專業(yè)班級 軟件11XX班 姓 名 XXX 學(xué) 號 XXXX 完成日期 2013-XX-XX 題目要求：將下面的題目在Oracle11g中實現(xiàn)，并手寫紙質(zhì)版報告一份，包括所有實現(xiàn)功能的SQL*PLUS命令或SQL或PL/SQL語句及運行結(jié)果。新建一個數(shù)據(jù)庫，命名為oraclezy，并完成以下功能：1) 記錄某采油廠對油/水井實施作業(yè)時所消耗的成本（命名為作業(yè)表zyb）。（作業(yè)：可理解為對油/水井進(jìn)行大修）2) 成本的消耗包括預(yù)算、結(jié)算、入賬三個狀態(tài)。預(yù)算：采油隊向管理部門提出作業(yè)申請，并經(jīng)管理部門批準(zhǔn)后，由管理部門負(fù)責(zé)錄入。結(jié)算：某次作業(yè)施工結(jié)束后，由管理部門

2、與施工單位共同核算各種成本，由管理部門負(fù)責(zé)錄入。入賬：結(jié)算后，財務(wù)部門將成本計入采油廠賬目，由財務(wù)部門錄入。1 根據(jù)對以上題意的理解，利用SQL PLUS，定義所需的數(shù)據(jù)表，包括定義相關(guān)約束條件（主碼、外碼及其他約束）。- Create tablecreate table ZYB( 單據(jù)號 VARCHAR2(20) not null, 預(yù)算單位 VARCHAR2(20) not null, 井號 VARCHAR2(20) not null, 預(yù)算金額 NUMBER(10) not null, 預(yù)算人 VARCHAR2(20) not null, 預(yù)算日期 DATE not null, 開工日期

3、 DATE, 完工日期 DATE, 施工單位 VARCHAR2(20), 施工內(nèi)容 VARCHAR2(20), 材料費 NUMBER(10), 人工費 NUMBER(10), 設(shè)備費 NUMBER(10), 其它費用 NUMBER(10), 結(jié)算金額 NUMBER(10), 結(jié)算人 VARCHAR2(20), 結(jié)算日期 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 (單據(jù)號) 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, 名稱規(guī)格 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( 作業(yè)項目編號 VARCHAR2(20) not null, 物碼 VARCHAR2(20) not null, 消耗數(shù)量 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，錄入以下相關(guān)的基礎(chǔ)數(shù)據(jù)：單位代碼表：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）施工單位表：作業(yè)公司作業(yè)一隊 作業(yè)公司作業(yè)二隊 作業(yè)公司作業(yè)三隊物碼表：wm001 材料一 噸 wm002 材料二 米 wm003 材料三 桶 wm004 材料四 袋ZYB:/某作業(yè)項目編號 zy2009001預(yù)算單位：采油一礦1隊 井號：y001 預(yù)算金額：10000.00 預(yù)算人：張三 預(yù)算日期:2009-5-1/開工日期：2009-5-4 完工日期：2009-5

16、-25 施工單位：作業(yè)公司作業(yè)一隊 施工內(nèi)容：堵漏材料費：7000.00(其中材料一：2000.00 材料二：2000.00 材料三：2000.00 材料四：1000.00)人工費：2500.00 設(shè)備費:1000.00 其它費用:1400.00 結(jié)算金額：11900.00結(jié)算人：李四 結(jié)算日期：2009-5-26/入賬金額：11900.00 入賬人：王五 入賬日期：2009-5-28/某作業(yè)項目編號 zy2009002預(yù)算單位：采油一礦2隊 井號：y003 預(yù)算金額：11000.00 預(yù)算人：張三 預(yù)算日期:2009-5-1/開工日期：2009-5-4 完工日期：2009-5-23 施工單位

17、：作業(yè)公司作業(yè)二隊 施工內(nèi)容：檢泵材料費：6000.00(其中材料一：2000.00 材料二：2000.00 材料三：2000.00)/隱含單價和數(shù)量人工費：1500.00 設(shè)備費:1000.00 其它費用:2400.00 結(jié)算金額：10900.00結(jié)算人：李四 結(jié)算日期：2009-5-26/入賬金額：10900.00 入賬人：王五 入賬日期：2009-5-28/某作業(yè)項目編號 zy2009003預(yù)算單位：采油一礦2隊 井號：s001 預(yù)算金額：10500.00 預(yù)算人：張三 預(yù)算日期:2009-5-1/開工日期：2009-5-6 完工日期：2009-5-23 施工單位：作業(yè)公司作業(yè)二隊 施工

18、內(nèi)容：檢泵材料費：6500.00(其中材料一：2000.00 材料二：2000.00 材料三：2500.00)人工費：2000.00 設(shè)備費:500.00 其它費用:1400.00 結(jié)算金額：10400.00結(jié)算人：李四 結(jié)算日期：2009-5-26/入賬金額：10400.00 入賬人：王五 入賬日期：2009-5-28/某作業(yè)項目編號 zy2009004預(yù)算單位：采油二礦1隊 井號：s002 預(yù)算金額：12000.00 預(yù)算人：張三 預(yù)算日期:2009-5-1/開工日期：2009-5-4 完工日期：2009-5-24 施工單位：作業(yè)公司作業(yè)三隊 施工內(nèi)容：防砂材料費：6000.00(其中材料

19、一：2000.00 材料二：2000.00 材料四：2000.00)人工費：2000.00 設(shè)備費:1000.00 其它費用:1600.00 結(jié)算金額：10600.00結(jié)算人：李四 結(jié)算日期：2009-5-26/入賬金額：10600.00 入賬人：趙六 入賬日期：2009-5-28/某作業(yè)項目編號 zy2009005預(yù)算單位：采油二礦2隊 井號：y005 預(yù)算金額：12000.00 預(yù)算人：張三 預(yù)算日期:2009-5-1/開工日期：2009-5-4 完工日期：2009-5-28 施工單位：作業(yè)公司作業(yè)三隊 施工內(nèi)容：防砂材料費：7000.00(其中材料一：2000.00 材料二：2000.0

20、0 材料四：3000.00)人工費：1000.00 設(shè)備費:2000.00 其它費用:1300.00 結(jié)算金額：11300.00結(jié)算人：李四 結(jié)算日期：2009-5-28/(未入賬！)這里只列舉一個導(dǎo)出的數(shù)據(jù)表："施工單位名稱","施工單位號","ROWID""作業(yè)公司作業(yè)一隊","zy01001","AAAR7YAABAAAVChAAA""作業(yè)公司作業(yè)二隊","zy01002","AAAR7YAABAAAVChAAB&quo

21、t;"作業(yè)公司作業(yè)三隊","zy01003","AAAR7YAABAAAVChAAC"3. 利用SQL PLUS查詢以下數(shù)據(jù)：1) 采油一礦2隊2009-5-1到2009-5-28有哪些項目處于結(jié)算狀態(tài)，列出相應(yīng)材料費明細(xì)。結(jié)算狀態(tài)有值，不用管入賬是否有值。SQL> select * from zclfb where 作業(yè)項目編號 in 2 (select 單據(jù)號 from zyb where 預(yù)算單位='采油一礦2隊' 3 and 結(jié)算日期>=to_date('2009-5-1','

22、;yyyy-mm-dd') 4* and 結(jié)算日期<=to_date('2009-5-28','yyyy-mm-dd')作業(yè)項目編號 物碼 消耗數(shù)量 單價 - - - - 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 預(yù)算單位='采油一礦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 預(yù)算單位 l