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

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

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, 名稱規(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, 消耗數量 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）施工單位表：作業(yè)公司作業(yè)一隊 作業(yè)公司作業(yè)二隊 作業(yè)公司作業(yè)三隊物碼表：wm001 材料一 噸 wm002 材料二 米 wm003 材料三 桶 wm004 材料四 袋ZYB:/某作業(yè)項目編號 zy2009001預算單位：采油一礦1隊 井號：y001 預算金額：10000.00 預算人：張三 預算日期:2009-5-1/開工日期：2009-5-4 完工日期：2009-5

16、-25 施工單位：作業(yè)公司作業(yè)一隊 施工內容：堵漏材料費：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/某作業(yè)項目編號 zy2009002預算單位：采油一礦2隊 井號：y003 預算金額：11000.00 預算人：張三 預算日期:2009-5-1/開工日期：2009-5-4 完工日期：2009-5-23 施工單位

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

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/某作業(yè)項目編號 zy2009004預算單位：采油二礦1隊 井號：s002 預算金額：12000.00 預算人：張三 預算日期:2009-5-1/開工日期：2009-5-4 完工日期：2009-5-24 施工單位：作業(yè)公司作業(yè)三隊 施工內容：防砂材料費：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/某作業(yè)項目編號 zy2009005預算單位：采油二礦2隊 井號：y005 預算金額：12000.00 預算人：張三 預算日期:2009-5-1/開工日期：2009-5-4 完工日期：2009-5-28 施工單位：作業(yè)公司作業(yè)三隊 施工內容：防砂材料費：7000.00(其中材料一：2000.00 材料二：2000.0

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

21、t;"作業(yè)公司作業(yè)三隊","zy01003","AAAR7YAABAAAVChAAC"3. 利用SQL PLUS查詢以下數據：1) 采油一礦2隊2009-5-1到2009-5-28有哪些項目處于結算狀態(tài)，列出相應材料費明細。結算狀態(tài)有值，不用管入賬是否有值。SQL> select * from zclfb where 作業(yè)項目編號 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')作業(yè)項目編號 物碼 消耗數量 單價 - - - - 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