數(shù)據(jù)庫試驗上機復習資料整理中國石油大學龔安

1、實驗四SQL練習2一、實驗目的1 .掌握索引的建立、刪除及使用；2 .掌握單表查詢、連接查詢、嵌套查詢和集合查詢；3 .掌握插入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)語句的非常用形式。二、實驗學時2學時三、實驗內(nèi)容1 .利用Query Analyzer完成以下操作：在預算日期、結(jié)算日期和入賬日期上分別建立索引，并在查詢操作中體會索引的作用。在完成第2題的查詢操作后，刪除預算日期、結(jié)算日期和入賬 日期上的索引。2 .利用Query Analyzer完成以下操作：采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了預算， 列出相應明細。采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了

2、結(jié)算， 列出相應明細。采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了結(jié)算， 列出相應的材料費消耗明細。采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了入賬， 列出相應明細。列出采油一礦二隊2016-5-1到2016-5-28總的預算金額。列出采油一礦二隊2016-5-1到2016-5-28總的結(jié)算金額。列出采油一礦二隊2016-5-1到2016-5-28總的入賬金額。列出采油一礦2016-5-1到2016-5-28總的入賬金額。有哪些人員參與了入賬操作。(10)列出2016-5-1到2016-5-28進行了結(jié)算但未入賬的項目。(11)列出采油一礦二隊的所有項目

3、，按入賬金額從高到低排列。(12)列出有哪些施工單位實施了項目，并計算各單位所有項目結(jié)算 金額總和。(13)找出消耗了材料三且消耗超過了2000元的項目，列出相應消耗明細(利用子查詢)。(14)作業(yè)公司二隊參與了哪些項目。(15)作業(yè)公司一隊和二隊參與了哪些項目(利用 union)。(16)采油一礦的油井是哪些作業(yè)隊參與施工的。3 .利用Query Analyzer完成以下操作：建立數(shù)據(jù)表(包含3個屬性列：施工單位、年月、結(jié)算 金額)保存各個施工單位每月的結(jié)算金額總和。用子查詢將各個施工單位每月的結(jié)算金額總和插入到所建立 的數(shù)據(jù)表中。用帶子查詢的修改語句將采油一礦油井作業(yè)項目的結(jié)算人改 為“李

4、兵”。用帶子查詢的刪除語句刪除采油一礦油井作業(yè)項目撤消上述兩個操作。四、實驗報告提交實驗內(nèi)容中用SQL語句完成的題目的SQL語句文檔及相應 的執(zhí)行結(jié)果。實驗五SQL練習3一、實驗目的1 .掌握基本表的刪除與修改；2 .掌握實體完整性、參照完整性和用戶定義的完整性的定義、檢查 和違約處理；3 .掌握視圖的定義、查詢和更新，了解視圖的作用。二、實驗學時2學時三、實驗內(nèi)容1 .利用Query Analyzer完成以下操作：向在實驗四中所定義的數(shù)據(jù)表增加“備注”歹U,其數(shù)據(jù)類型為 字符型，并查看新增列的值。對上述數(shù)據(jù)表增加主碼約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù) 的情況下主碼約束是否創(chuàng)建成功，然后再次執(zhí)

5、行實驗四中實驗內(nèi)容 3.2的操作，并觀察記錄執(zhí)行結(jié)果。刪除上述數(shù)據(jù)表中的數(shù)據(jù)，然后再刪除該數(shù)據(jù)表，對這兩個操 作進行比較。2 .利用Query Analyzer完成以下任務：對實驗三中所定義的6個數(shù)據(jù)表增加主碼約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下主碼約束是否創(chuàng)建成功，然后執(zhí)行以下2個操作，觀察并記錄實體完整性的檢查和違約處理。 insert into 材料費表 values（zy2016001,wm004,100,10） insert into 材料費表 values（zy2016002,NULL,200,10）注：“材料費表”根據(jù)自己所命名的表名進行相應的替換。對實驗三中所定義的6個數(shù)

6、據(jù)表增加相應的參照完整性約束，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下參照完整性約束是否創(chuàng)建成功，然后執(zhí)行以下操作，觀察并記錄參照完整性的檢查和違約處理。將（y007油井112203002）插入到油水井表。 insert into 材料費表 values（zy2016007,wm006,100,10）將作業(yè)項目編號zy2016001的施工單位修改為“作業(yè)公司作業(yè)五隊”。將單位代碼表中的（112202002采油二礦二隊）刪除，查看油 水井表和作業(yè)項目表中的數(shù)據(jù)有何變化。將物碼表中的（wm004材料四袋）修改為（wm04材料四袋）。撤銷上述成功的更新操作。注：“材料費表”根據(jù)自己所命名的表名進行相應的替換

7、。對實驗三中所定義的 6個數(shù)據(jù)表按以下要求增加相應的完整性約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下完整性約束是否創(chuàng)建成功。單位代碼表的單位名稱不能取空值、且取值唯油水井表的井別只允許取“油井”或“水井”，單位代碼不能 取空值。物碼表的名稱規(guī)格不能取空值、且取值唯一，計量單位不能取 空值。材料費表的消耗數(shù)量不能取空值，單價不能取空值。對作業(yè)項目表根據(jù)實際應用的要求定義適當?shù)挠脩舳x的完整性約束條件。3.利用Query Analyzer完成以下操作：定義一個視圖，用于保存作業(yè)項目表和材料費表的全部列。查詢上面定義的視圖，可任意組合查詢條件，構(gòu)造出2個查詢。定義一個反映作業(yè)項目預算狀態(tài)的視圖，并向

8、該視圖插入(zy2016008,112202002,y005,10000張三,2016-07-02),查看作業(yè)項 目表的數(shù)據(jù)有何變化。撤銷上述成功的更新操作。四、實驗報告提交實驗內(nèi)容中用SQL語句完成的題目的SQL語句文檔及相應 的執(zhí)行結(jié)果。實驗六其它數(shù)據(jù)庫對象的管理一、實驗目的1 .掌握事務的概念、性質(zhì)、定義及使用；2 .掌握游標的概念、組成、創(chuàng)建及使用；3 .掌握存儲過程的概念、類型、特點、創(chuàng)建、執(zhí)行及管理。4 .掌握觸發(fā)器的概念、創(chuàng)建、管理及使用。二、實驗學時2學時三、實驗內(nèi)容1.利用Query Analyzer進行如下事務處理練習(把下列五條語句作為一個事務處理，只有五條語句全部成功執(zhí)

9、行才做提交，并給出成功的提示信息；否則就做回退處理，并給出具體的錯誤提示信息)：insert into 作業(yè)項目表 values(zy2016006,112202002,y005,10000張三，07-01-2016 ,07-04-2016,07-25-2016,作業(yè)公司作業(yè)一隊:堵漏,7000,2500,1000,1400,11900,李四,07-26-2016,11900，王五,07-28-2016)insert into 材料費表 values(zy2016006,wm001,200,10)insert into 材料費表 values(zy2016006,wm002,200,10)in

10、sert into 材料費表 values(zy2016006,wm003,200,10)insert into 材料費表 values(zy2016006,wm004,100,10)注：“作業(yè)項目表”、“材料費表”根據(jù)自己所命名的表名進行相應的替換。2 .利用Query Analyzer進行如下游標練習：定義一個游標，用于存放作業(yè)項目表的全部行數(shù)據(jù)，并打印以下表頭和各行數(shù)據(jù)。表頭：單據(jù)號預算單位井號預算金額預算人預算日期開工日期完工日期施工單位施工內(nèi)容材料費人工費設備費其它費用結(jié)算金額結(jié)算人結(jié)算日期入賬金額入賬人入賬日期執(zhí)行以上所定義的游標，查看是否能正確輸出結(jié)果。3 .利用Query An

11、alyzer定義一個存儲過程，要求完成以下功能：生成某單位（單位可以是采油廠或采油礦或采油隊）某段時間內(nèi)的成本運行情況（輸入?yún)?shù)：單位代碼起始日期結(jié)束日期）。輸出格式 *單位*時間-*時間成本運行情況預算金額結(jié)算金額入賬金額未結(jié)算金額未入賬金額* * * * * * * * * *其中：未必算金額=預算金額-結(jié)算金額未入賬金額=結(jié)算金額-入賬金額分三種情況（單位分別為：采油廠、采油礦、采油隊）執(zhí)行以上 定義的存儲過程，查看執(zhí)行輸出結(jié)果。4 .利用Query Analyzer針對作業(yè)項目表定義三個觸發(fā)器，分別 完成以下功能：對作業(yè)項目表插入一行數(shù)據(jù)時，自動計算并插入結(jié)算金額字段 （結(jié)算金額=材料

12、費+人工費+設備費+其它費用）。當修改作業(yè)表的某行數(shù)據(jù)時自動修改結(jié)算金額字段。當刪除作業(yè)表中一行數(shù)據(jù)時，自動刪除材料費表中相應明細數(shù)對上述3個觸發(fā)器用適當?shù)母抡Z句進行驗證，并查看結(jié)果是 否達到預期結(jié)果。四、實驗報告提交實驗內(nèi)容中用SQL語句完成的題目的SQL語句文檔及相應 的執(zhí)行結(jié)果。實驗七安全機制2.利用Query Analyzer完成以下操作：建立采油一礦的作業(yè)項目的視圖，把該視圖的查詢權(quán)限授予給 采油一礦的用戶userll,以userll的身份查詢該視圖，觀察執(zhí)行情況； 再以其他用戶的身份查詢該視圖，觀察執(zhí)行情況。創(chuàng)建一個用戶user12,以user12的身份執(zhí)行實驗六中所定義的 存儲

13、過程，觀察記錄是否成功執(zhí)行；然后把該存儲過程的執(zhí)行權(quán)限授 予給user12,再次以user12的身份執(zhí)行該存儲過程，觀察記錄是否成 功執(zhí)行。定義觸發(fā)器，實現(xiàn)只能在工作時間內(nèi)更新“作業(yè)項目表”的數(shù)據(jù)， 然后通過選擇不同的時間進行適當?shù)母虏僮鱽眚炞C。-實驗3createtabLEB代碼表(單位彳碼CHAR20),單位名稱CHAR(20);CREATETABfeK井表 （井號 CHAR20）, 井別 CHAR20）,單位彳碼CHAR20);CREATETABLET 單位表 (施工單位名稱CHAR20);CREATETABL 表 (物碼CHA呼)，| 名稱規(guī)格CHA(20), 計量單位CHA(20)

14、;createtable 材料費表( 單據(jù)號CHA R20), 物碼 CHA R20), 消耗數(shù)量INT,單價MONEY)；C REATETABLEdk 項目表 (單據(jù)號CHA&0), 預算單位CHA(20), 井號 CHA R20), 預算金額MONEY 預算人CHA R20), 預算日期DATETIME開工日期DATETIME 完工日期DATETIME 施工單位CHA(20), 施工內(nèi)容CHA (20), 材料費money 人工費MONEY 設備費MONEY 其他費用money 結(jié)算金額MONEY 結(jié)算人CHA R20), 結(jié)算日期DATETIME入賬金額MONEY 入賬人CHA R10)

15、, 入賬日期DATETIMEinsertinto 單位代碼表values （1122,采油廠）;insertinto 單位代碼表values （112201,采油一礦）；insertinto 單位代碼表values （112202,采油二礦）；insertinto 單位代碼表values （112201001,采油一礦一隊）;insertinto 單位代碼表values （112201002,采油一礦二隊）;insertinto 單位代碼表values （112201003,采油一礦三隊）;insertinto 單位代碼表values （112202001,采油二礦一隊）;insertinto

16、 油水井表values （y001,油井，112201001）;insertinto 油水井表values （y002,油井，112201001）;insertinto 油水井表values （y003,油井，112201002）;insertinto 油水井表values （s001,水井，112201002）;insertinto 油水井表values （y004,油井，112201003）;insertinto 油水井表values （s002,水井，112202001）;insertinto 油水井表values （s003,水井，112202001）;insertinto 油水井表v

17、alues （y005,油井，112202002）;insertinto施工單位表values （作業(yè)公司作業(yè)一隊）;insertinto施工單位表values （作業(yè)公司作業(yè)二隊）;insertinto施工單位表values （作業(yè)公司作業(yè)三隊）;insertinto 物碼表values （wm001,材料一，噸）;insertinto 物碼表values （wm002,材料二，米）；insertinto 物碼表values （wm003,材料三，桶）;insertinto 物碼表values （wm004,材料四,袋）;insertinto材料費表（單據(jù)號，物碼,消耗數(shù)量，單價）value

18、s （zy2016001 ,wm001, 200, 10）;insertinto材料費表（單據(jù)號，物碼,消耗數(shù)量，單價）values （zy2016001 ,wm002, 200, 10）;insertinto材料費表（單據(jù)號，物碼,消耗數(shù)量，單價）values （zy2016001 ,wm003, 200, 10）;insertinto 材料費表（單據(jù)號，物碼,消耗數(shù)量，單價） values （zy2016003 ,wm001, 200, 10）;insertinto 材料費表（單據(jù)號，物碼,消耗數(shù)量，單價） values （zy2016003 ,wm002, 200, 10）;insert

19、into 材料費表（單據(jù)號，物碼,消耗數(shù)量，單價） values （zy2016003 ,wm003, 250, 10）;insertinto 材料費表（單據(jù)號，物碼,消耗數(shù)量，單價） values （zy2016004 ,wm001, 200, 10）;insertinto 材料費表（單據(jù)號，物碼,消耗數(shù)量，單價） values （zy2016004 ,wm002, 200, 10）;insertinto 材料費表（單據(jù)號，物碼,消耗數(shù)量，單價） values （zy2016004 ,wm004, 200, 10）;insertinto 材料費表(單據(jù)號，物碼,消耗數(shù)量，單價) values

20、 (zy2016005 ,wm001, 200, 10);insertinto 材料費表(單據(jù)號，物碼,消耗數(shù)量，單價) values (zy2016005 ,wm002, 200, 10);insertinto 材料費表(單據(jù)號，物碼,消耗數(shù)量，單價) values (zy2016005 ,wm004, 300, 10);insertinto作業(yè)項目表values （zy2016001 ,112201001 ,y001 , 10000,張三，2016/5/1,2016/5/4 , 2016/5/23,作業(yè)公司作業(yè)一隊,堵漏，7000, 2500, 1000, 1400, 10900, 王五，

21、2016/5/26,10900,王五，2016/5/28）;insertinto作業(yè)項目表values （zy2016003 ,112201002 ,s001 , 10500,張三，2016/5/1,2016/5/6 , 2016/5/23,作業(yè)公司作業(yè)二隊,調(diào)剖，6500, 2000, 500, 1400, 10400,李 四，2016/5/26,10400,王五，2016/5/28）;insertinto作業(yè)項目表values （zy2016004 ,112202001 ,s002 , 12000,張三，2016/5/1,2016/5/4 , 2016/5/24,作業(yè)公司作業(yè)三隊,解堵，6

22、000, 2000, 1000, 1600, 10600, 李四，2016/5/26 , 10600,趙六，2016/5/28）;insertinto 作業(yè)項目表values （zy2016005 ,112202002 ,y005 , 12000,張三，2016/5/1,2016/5/4 , 2016/5/28,作業(yè)公司作業(yè)三隊,防砂，7000, 1000, 2000, 1300, 11300, 李四，2016/6/1,NULL,NULL,NULL）;begintranupdate作業(yè)項目表set人工費=人工費+200 where單據(jù)號=2丫2016005;update作業(yè)項目表set結(jié)算金額

23、=結(jié)算金額+200 where單據(jù)號=2丫2016005;deletefrom作業(yè)項目表where入賬金額isNULL;rollbacktran-實驗四-1createindex預算日期索引on作業(yè)項目表（預算日期）；createindex結(jié)算日期索引on作業(yè)項目表（結(jié)算日期）；createindex入賬日期索引on作業(yè)項目表（入賬日期）；-| select *from作業(yè)項目表where預算日期 between2016/5/1and2016/5/28 and預算單位=（select單位代碼from單位代碼表where單位名稱=采油一礦二隊）；-another solutionselect *

24、from作業(yè)項目表，單位代碼表where單位彳碼=預算單位and單位名稱=采油一礦二隊and預算日期between2016/5/1 and2016/5/28;-（2）select *from作業(yè)項目表where結(jié)算日期 between2016/5/1and2016/5/28 and預算單位=（select單位代碼from單位代碼表where單位名稱=采油一礦二隊）;-another solution-select *- -from作業(yè)項目表，單位代碼表結(jié)算- -where單位名稱=采油一礦二隊and 預算單位=單位代碼and結(jié)算日期=2016-5-1and 日期 =2016-5-28;- -（

25、3）select *from材料費表where單據(jù)號=（select單據(jù)號from作業(yè)項目表where預算日期 between2016/5/1 and2016/5/28 and預算單位=（select單位代碼from單位代碼表where單位名稱=采油一礦二隊）；- -（4）select *from作業(yè)項目表where入賬日期 between2016/5/1 and2016/5/28 and預算單位=（select單位代碼from單位代碼表where單位名稱=采油一礦二隊）；- -（5）select , sum（預算金額）預算金額總和from作業(yè)項目表where預算日期 between2016/

26、5/1 and2016/5/28 and預算單位=（select單位代碼from單位代碼表where單位名稱=采油一礦二隊）；- -（6）select , sum（結(jié)算金額）結(jié)算金額總和from作業(yè)項目表where結(jié)算日期 between2016/5/1 and2016/5/28 and預算單位=（select單位代碼from單位代碼表where單位名稱=采油一礦二隊）；- -（7）select sum（入賬金額）入賬金額總和from作業(yè)項目表where入賬日期 between2016/5/1 and2016/5/28 and預算單位=（select單位代碼from單位代碼表where單位名稱

27、=采油一礦二隊）；-（8）select sum（入賬金額）采油一礦入賬金額總和from作業(yè)項目表，單位代碼表|where入賬日期between2016/5/1 and2016/5/28 and預算單位=單位代碼andB位名稱like 采油 礦;-another solutionselect sum（入賬金額）入賬金額總和from作業(yè)項目表where入賬日期 between2016/5/1 and2016/5/28 and預算單位 in(select單位代碼from單位代碼表where單位名稱like 采油一礦%,);-(9)selectdistinct 入賬人from作業(yè)項目表where入賬人

28、 isnotnull;-(10)select單據(jù)號from作業(yè)項目表where結(jié)算日期 between2016/5/1 and2016/5/28 and結(jié)算金額 isnotnulland 入賬金額 isnull;-(11)select單據(jù)號from作業(yè)項目表where預算單位=(select單位代碼from單位代碼表where單位名稱=采油一礦二隊,)orderby入賬金額desc;-(12)select施工單位表.施工單位名稱，count(結(jié)算金額)項目個數(shù)，sum(結(jié)算金額)結(jié)算金額總和 from施工單位表leftjoin作業(yè)項目表on (施工單位表.施工單位名稱=作業(yè)項目表.施工單位)g

29、roupby施工單位表.施工單位名稱；- -(13)select *from材料費表where單據(jù)號=(select單據(jù)號from材料費表where物碼=(select 物碼from物碼表where名稱規(guī)格=材料三,) and消耗數(shù)量*單價2000);- -(14)selectdistinct 單據(jù)號from作業(yè)項目表where施工單位=作業(yè)公司作業(yè)二隊,;- -(15)selectdistinct 單據(jù)號from作業(yè)項目表where施工單位=作業(yè)公司作業(yè)一隊,unionselectdistinct 單據(jù)號from作業(yè)項目表where施工單位=作業(yè)公司作業(yè)二隊,;- -(16)selectdi

30、stinct施工單位from作業(yè)項目表，油水井表，單位代碼表where預算單位=單位代碼表.單位彳碼and單位代碼表.單位彳碼W由水井表.單位彳碼and單位名稱like 采油一礦%,;- -another solutionselectdistinct施工單位from作業(yè)項目表where井號 in( select 井號from油水井表where單位代碼in( select 單位代碼from單位代碼表where單位名稱like 采油一礦%,);dropindex預算日期索引on作業(yè)項目表；dropindex結(jié)算日期索引on作業(yè)項目表；dropindex入賬日期索引on作業(yè)項目表；- -3- -(1

31、)createtable 結(jié)算金額表(施工單位char (20),年月 varchar (7),結(jié)算金額money)；- -(2)insertinto結(jié)算金額表select 施工單位,convert (varchar (7),結(jié)算日期，111), sum(結(jié)算金額)from作業(yè)項目表groupby 施工單位,convert (varchar (7),結(jié)算日期,111);-another solution-insert- -into結(jié)算金額表- -select 施工單位，丫EAR(結(jié)算日期)*100+MONTH(吉算日期),sum(結(jié)算金額)- -from作業(yè)項目表- -group by施工單位

32、，丫EAR(結(jié)算日期)*100+MONTH算日期);- -(3)begintranupdate作業(yè)項目表set結(jié)算人=李兵where預算單位in( select單位代碼from單位代碼表where單位名稱like 采油一礦%,）；-（4） deletefrom作業(yè)項目表where預算單位in（ select單位代碼from單位代碼表where單位名稱like 采油一礦%,）；rollback實驗五一altertable結(jié)算金額表add備注 char (20); |select *from結(jié)算金額表-(2)altertable結(jié)算金額表altercolumn 施工單位 char (20)notn

33、ull;altertable結(jié)算金額表altercolumn 年月 varchar (7)notnull;altertable結(jié)算金額表addprimarykey (施工單位,年月);TRUNCATETABW 金額表insertinto結(jié)算金額表(施工單位，年月，結(jié)算金額)select 施工單位,convert (varchar (7),結(jié)算日期，111), sum(結(jié)算金額)from作業(yè)項目表groupby 施工單位,convert (varchar (7),結(jié)算日期,111);deletefrom結(jié)算金額表；delete結(jié)算金額表；-2-(1)altertable 材料費表altercol

34、umn 單據(jù)號 char (20)notnull;altertable材料費表altercolumn 物碼char (20)notnull;altertable材料費表addprimarykey (單據(jù)號，物碼)； altertable 作業(yè)項目表altercolumn 單據(jù)號 char (20)notnull;altertable 作業(yè)項目表 addprimarykey (單據(jù)號)；altertable單位代碼表altercolumn 單位彳碼 char (20)notnull;altertable單位代碼表addprimarykey (單位代碼);altertable施工單位表alterc

35、olumn 施工單位名稱 char (20)notnull;altertable施工單位表addprimarykey (施工單位名稱);altertable 物碼表altercolumn 物碼char (20)notnull;altertable 物碼表addprimarykey (物碼)；altertable 油水井表altercolumn 井號char (20)notNUll;altertable 油水井表 addprimarykey (井號)；begintraninsertinto 材料費表 values (zy2016001 , wm004, 100, 10);insertinto 材

36、料費表 values (zy2016002 ,NULL, 200, 10);rollback-（2）altertable 材料費表addconstraint al foreignkey（單據(jù)號）references 作業(yè)項目表（單據(jù)號）；altertable 材料費表addconstraint a2 foreignkey（物碼）references 物碼表（物碼）；altertable 作業(yè)項目表addconstraint b1 foreignkey （預算單位）references 單位代碼表（單位代碼）；altertable 作業(yè)項目表addconstraint b2 foreignkey

37、 （井號）references 油水井表（井號）；altertable 作業(yè)項目表addconstraint b3 foreignkey （施工單位）references 施工單位表（施工單位名稱 ）;insertinto油水井表values (y007 ，油井，112203002);insertinto材料費表values (zy2016007 , wm006, 100, 10);begintranupdate作業(yè)項目表set施工單位=作業(yè)公司彳業(yè)五隊where單據(jù)號=zy2016001;deletefrom單位代碼表where單位彳碼=112202002;update物碼表set 物碼=w

38、m04where名稱規(guī)格二材料四；rollbacktran-(3)altertable單位代碼表addconstraint aa1 check (單位名稱 isnotnull);altertable單位代碼表addconstraint uni unique (單位代碼)；altertable油水井表addconstraint aa2 check(井別 in(油井，水井);altertable油水井表altercolumn 單位彳碼 char (20)notnull;altertable 物碼表altercolumn 名稱規(guī)格 char (20)notnull;altertable 物碼表add

39、constraint uni2 unique (名稱規(guī)格)；altertable 物碼表altercolumn 計量單位 char (20)notnull;altertable材料費表altercolumn 消耗數(shù)量 int notnull;altertable材料費表altercolumn 單價 int notnull;altertable 作業(yè)項目表addconstraint asdaf check (材料費+人工費+設備費+其他費用=結(jié)算金額);-3一createview 視圖一asselect作業(yè)項目表:物碼，消耗數(shù)量，單價from作業(yè)項目表，材料費表where作業(yè)項目表.單據(jù)號=材料

40、費表.單據(jù)號；-(2)select結(jié)算金額，消耗數(shù)量from視圖一where物碼=wm003;select預算單位，施工內(nèi)容from視圖一where單據(jù)號=zy2016001 and物碼=wm001;-(3)createview預算狀態(tài)asselect單據(jù)號，預算單位，井號,預算金額，預算人，預算日期 from作業(yè)項目表 begintraninsertinto預算狀態(tài)values (zy2016008 ,112202002 ,y005 , 10000,張三，2016-07-02); rollbacktran-實驗K-1begintrangoinsertinto 作業(yè)項目表 values (zy

41、2016006 , 112202002 , y005 10000,張三，07-01-2016 , 07-04-2016 , 07-25-2016, 作業(yè)公司作業(yè)一隊,堵漏，7000, 2500, 1000, 1400, 11900, 李四，07-26-2016 , 11900,王五，07-28-2016)insertinto材料費表values(zy2016006,wm001,200,10)insertinto材料費表values(zy2016006,wm002,200,10)insertinto材料費表values(zy2016006,wm003,200,10)insertinto材料費表v

42、alues(zy2016006,wm004,100,10) if erro0beginprint 語句執(zhí)彳f失敗rollbackendelsebeginprint 語句執(zhí)彳f成功commitend-2begintrandeclare yb cursorforselect *from作業(yè)項目表open ybdeclare B據(jù)號 varchar (20)declare 算單位 varchar (20)declare 號varchar (20)declare 算金額 varchar (20) declare 算人 varchar (20)declare 算日期 varchar (20) decla

43、re FF工日期 varchar (20) declare 工日期 varchar (20) declare 缶工單位 varchar (20) declare 總工內(nèi)容 varchar (20) declare 料費 varchar (20) declare )AX費 varchar (20) declare S備費 varchar (20) declare 它費用 varchar (20) declare 吉算金額 varchar (20) declare 吉算人 varchar (20) declare 吉算日期 varchar (20) declare 賬金額 varchar (20)

44、 declare 賬人 varchar (20) declare 賬日期 varchar (20)print 單據(jù)號+預算單位+井號+預算金額+預算人+預算日期+ 開工日期+ 完工日期+ 施工單位+ 施工內(nèi)容+ 材料費+ 人工費+ 設備費+ 其它費用+ 結(jié)算金額+ 結(jié)算人+ 結(jié)算日期+ ,入賬金額+ 入賬人+ 入賬日期fetchnextfrom ybinto研據(jù)號，頜算單位，磯號，小算金額，中算人，顏算日期， 幽工日期， 觥工日期， 碩工單位， 碩工內(nèi)容， 材料費， 旗工費， 國備費， 演它費用， 吉算金額， 吉算人， 吉算日期， 賬金額， 賬人， 賬日期while (fetch_status

45、 = 0 ) beginprint1據(jù)號+ + 小算單位+ + 郵號+ + 頜算金額+ + 小算人+ + 頜算日期+ + 幽工日期+ + 觥工日期+ + 碩工單位+ + 碩工內(nèi)容+ + 斷料費+ + 旗工費+ + 國備費+ + 或它費用+ + 吉算金額+ + 吉算人+ + 吉算日期+ + 賬金額+ + 賬人+ + 賬日期fetchnextfrom yb into磅據(jù)號，預算單位，磯號，頤算金額，顏算人，顏算日期，幽工日期，觥工日期，碩工單位，碩工內(nèi)容，材料費，旗工費，國備費，演它費用，吉算金額，吉算人，吉算日期，賬金額，賬人，必賬日期；end-3if object_id (_someDepCo

46、st , p )isnotnull dropprocedure _someDepCost;gocreateprocedure _someDepCost酸位彳碼 varchar (40), | 碘始日期datetime , 結(jié)束日期datetimeasdeclare B位名稱 varchar (40);declare 算金額 money再吉算金額money |賬金額money未結(jié)算金額money未入賬金額moneyselect 單位名稱=單位名稱from單位代碼表 where單位彳碼=單位代碼 set 預算金額=(select sum(預算金額)from作業(yè)項目表where預算單位like 單位

47、彳碼+% and(預算日期between 起始日期and 吉束日期) set 吉算金額=(select sum(結(jié)算金額)from作業(yè)項目表 where預算單位like 單位彳碼+% and（結(jié)算日期between 起始日期and 吉束日期）set 入賬金額=（select sum（入賬金額）from作業(yè)項目表where預算單位like 單位彳碼+% and（入賬日期between 起始日期and 吉束日期）and（入賬金額isnotnull）set 未結(jié)算金額=（select sum（預算金額）from作業(yè)項目表where （結(jié)算金額isnull）and（ 預算日期between 始日期an

48、d 吉束日期）set 未入賬金額=（select sum（結(jié)算金額）from作業(yè)項目表where （入賬金額isnull）and（ 結(jié)算日期between 始日期and 吉束日期）set 未結(jié)算金額=預算金額-結(jié)算金額set 未入賬金額=吉算金額-入賬金額if 算金額 isnullset 算金額=0 |if 吉算金額isnullset 吉算金額=0if 賬金額 isnullset 入賬金額=0if H結(jié)算金額isnullset 未結(jié)算金額=0if H入賬金額isnullset 未入賬金額=0declare result varchar （100）set result =convert （var

49、char ,小算金額）+ +convert （varchar ,吉算金額）+convert （varchar , 賬金額）+ +convert （varchar ,參結(jié)算金額）+ +convert （varchar ,4 入賬金額）print 11位名稱 +單位+convert （varchar ,碘始日期,102）+ ,- +convert （varchar , 吉束日期，102）+成本運營狀況1|print ,預算金額結(jié)算金額入賬金額未結(jié)算金額未入賬金額print resultgoexecute _someDepCost斷位彳碼=1122，碘始日期=2016-5-1 ，吉束日期=2016-

50、5-29 ;execute _someDepCost頌位彳碼=,112201,硬始日期=,2016-5-1 , 吉束日期=,2016-5-29;execute _someDepCost頌位彳碼=112201001,建始日期=2016-5-1 , 吉束日期=2016-5-29 go- -4- -if OBJECTDtrmytr1, TR )isnotnulldroptrigger tr_mytrlgocreatetrigger trmytr1on 作業(yè)項目表afterinsertasdeclare 再吉算金額 moneydeclare temp1 money temp2money temp3mo

51、ney temp4moneyselect temp1 =材料費，temp2=人工費，temp3=設備費，temp4=其他費用from inserted ;set 吉算金額=temp1+temp+temp+temp4update作業(yè)項目表set結(jié)算金額=吉算金額where單據(jù)# =(select 單據(jù)號 from inserted );go- -(2)if OBJECTDtrmytr2, TR )isnotnulldroptrigger tr_mytr2gocreatetrigger trmytr2on 作業(yè)項目表after update asdeclare 吉算金額 moneydeclare temp1 money temp2money temp3money temp4moneyselect temp1 =材料費，temp2=人工費，temp3=設備費，temp4=其他費用from inserted ;set 吉算金額=temp1+temp+temp+temp4update作業(yè)項目表set結(jié)算金額=吉算金額where單據(jù)# =(select 單據(jù)號 from inserted );go- -(3)if OBJECTDtrmytr3, TR )isnotnulldroptrigger tr_mytr3gocreatetrigger trmytr3on 作業(yè)項目表ins