6其他數(shù)據(jù)庫對象設(shè)計

1、26 七月 20221第6章: 其他數(shù)據(jù)庫對象設(shè)計主要內(nèi)容6.1視圖設(shè)計6.2函數(shù)和存儲過程設(shè)計6.3觸發(fā)器設(shè)計6.4斷言設(shè)計 26 七月 20222數(shù)據(jù)庫系統(tǒng)概念-導(dǎo)論6.1視圖設(shè)計在某些情況下，讓所有的用戶看到整個邏輯模型（即所有實際存儲在數(shù)據(jù)庫中的關(guān)系）是不可取的 考慮一個職員需要知道教師的標(biāo)識、姓名和所在系名，但是沒有權(quán)限看到教師的工資值 此人應(yīng)該看到的關(guān)系由如下SQL語句所描述： select tno, tname, dno from T視圖提供一個對某些用戶從視圖中隱藏某些數(shù)據(jù)的機制 任何不是邏輯模型的一部分，但作為虛關(guān)系對用戶可見的關(guān)系稱為視圖 6.1視圖設(shè)計用 create v

2、iew 命令定義視圖，命令的格式為create view v as 其中 可以是任何合法的SQL表達(dá)式 v表示視圖名 一旦定義了一個視圖，我們就可以用視圖名指代該視圖生成的虛關(guān)系 定義視圖時并不是由查詢表達(dá)式的執(zhí)行結(jié)果創(chuàng)造一個新關(guān)系 相反，一個視圖的定義導(dǎo)致存儲一個查詢表達(dá)式，當(dāng)該視圖被使用時，它就被這個已存儲的查詢表達(dá)式替換 26 七月 202236.1視圖設(shè)計T 關(guān)系的沒有salary 屬性的視圖 create view faculty as select tno, tname, dno from T找出Biology系的所有教師的姓名 select tname from faculty

3、where dno = d01定義一個每個系工資總和的視圖 create view departments_total_salary(dno, total_salary) as select dno, sum (salary) from T group by dno;26 七月 202246.1視圖設(shè)計一個視圖可以用于定義另一個視圖的表達(dá)式中 如果一個視圖關(guān)系 v2 用于定義另一個視圖關(guān)系v1 的表達(dá)式中，則稱v1 直接依賴于v2如果一個視圖關(guān)系v1 直接依賴于另一個視圖關(guān)系v2，或通過其他視圖間接依賴于v2，則稱v1 依賴于v2一個視圖關(guān)系如果依賴于它自身，則被稱為遞歸的 26 七月 202

4、256.1視圖設(shè)計視圖擴展一種通過其他視圖的定義來定義視圖含義的方法 設(shè)視圖v1是由表達(dá)式e1定義的，可能它本身就包含對視圖關(guān)系的使用 一個表達(dá)式中的視圖擴展重復(fù)以下替換步驟:repeat找到e1中的關(guān)系vi 使用定義vi的表達(dá)式替換vi until 在e1中沒有視圖關(guān)系只要視圖定義不是遞歸的,此循環(huán)就能終止 26 七月 202266.1視圖設(shè)計視圖更新大部分的 SQL實現(xiàn)只允許在簡單視圖上的更新from子句中只有一個數(shù)據(jù)庫關(guān)系 select子句中只包含關(guān)系的屬性名，不包含任何表達(dá)式、聚集或distinct聲明 任何沒有出現(xiàn)在select子句中的屬性可以取空值 查詢中不含有g(shù)roup by或h

5、aving子句 26 七月 202276.1視圖設(shè)計視圖作用：視圖在一定程度上保證了數(shù)據(jù)的邏輯獨立性視圖能夠?qū)C密數(shù)據(jù)提供安全保護26 七月 202286.1視圖設(shè)計物化視圖: 創(chuàng)建一個物理表，此表包含定義視圖的查詢結(jié)果的所有元組 如果查詢中使用的關(guān)系發(fā)生了更新，則物化視圖中的結(jié)果就會過期 每當(dāng)視圖的底層關(guān)系進行更新時要更新視圖，以此來維護視圖 26 七月 2022910數(shù)據(jù)庫系統(tǒng)概念-SQL6.1視圖設(shè)計使用物化視圖的目的是為了提高查詢性能，是以空間換時間的一種有效手段，更少的物理讀/寫，更少的cpu時間，更快的響應(yīng)速度；物化視圖對應(yīng)用透明；物化視圖需要占用存儲空間；當(dāng)基表發(fā)生變化時，物化視

6、圖也應(yīng)當(dāng)刷新。 規(guī)模較大的報表適合使用物化視圖來提高查詢性能。 11數(shù)據(jù)庫系統(tǒng)概念-SQL6.1視圖設(shè)計建立物化視圖之前的準(zhǔn)備工作： 1、創(chuàng)建存放mvlog的表空間 create tablespace ts_mvlog datafile e:oraclets_mvlog_1.dat size 1000M default storage(initial 128k next 128k minextents 2 maxextents 500) online; alter tablespace ts_mvlog online;12數(shù)據(jù)庫系統(tǒng)概念-SQL6.1視圖設(shè)計為物化視圖所在的表建立日志：-si.

7、ab01 drop MATERIALIZED VIEW LOG on si.ab01; CREATE MATERIALIZED VIEW LOG ON SI.AB01 TABLESPACE TS_MVLOG WITH ROWID EXCLUDING NEW VALUES; -si.ab02 drop MATERIALIZED VIEW LOG on si.ab02; CREATE MATERIALIZED VIEW LOG ON SI.AB02 TABLESPACE TS_MVLOG WITH ROWID EXCLUDING NEW VALUES;13數(shù)據(jù)庫系統(tǒng)概念-SQL6.1視圖設(shè)計建立物

8、化視圖：CREATE MATERIALIZED VIEW SISO_SD.AB01 TABLESPACE TS_MV BUILD IMMEDIATE -建物化視圖的同時，刷新數(shù)據(jù) REFRESH Fast WITH ROWID -fast增量刷新，force完全刷新 START WITH SYSDATE -從創(chuàng)建時開始刷新 NEXT SYSDATE + 1/24 -下一次刷新時間，現(xiàn)在每一小時刷新一次 AS select bab005 BAB200,aab300 BAB201, BAB202,aab034 from si.ab62si_SISO_SD_link;14數(shù)據(jù)庫系統(tǒng)概念-SQL6.1

9、視圖設(shè)計為物化視圖建立主碼alter table SISO_SD.AB01 add constraints PK_SS20 primary key(BAB200)15數(shù)據(jù)庫系統(tǒng)概念-SQL6.1視圖設(shè)計調(diào)整物化視圖結(jié)構(gòu)：alter table siso_sd.ab01 modify(AAB034 VARCHAR2(8) null)6.2函數(shù)和存儲過程設(shè)計函數(shù)和存儲過程：函數(shù)/過程可以用SQL自身寫，也可以用外部編程語言寫函數(shù)和過程運行“業(yè)務(wù)邏輯”作為存儲過程記錄在數(shù)據(jù)庫中，并在數(shù)據(jù)庫中執(zhí)行26 七月 2022166.2函數(shù)和存儲過程設(shè)計盡管類似的“業(yè)務(wù)邏輯”能夠被寫成程序設(shè)計語言過程并完全存儲

10、在數(shù)據(jù)庫以外，但是把它們定義成數(shù)據(jù)庫中的函數(shù)和過程有下列優(yōu)點：在數(shù)據(jù)庫中的函數(shù)和過程允許多個應(yīng)用訪問允許當(dāng)業(yè)務(wù)規(guī)則發(fā)生變化是進行單個點的改變，而不必改變應(yīng)用系統(tǒng)的其他部分應(yīng)用系統(tǒng)可以調(diào)用存儲過程，而不是直接更新數(shù)據(jù)庫26 七月 2022176.2函數(shù)和存儲過程設(shè)計定義一個函數(shù)，輸入一個系的編號，返回該系的教師的數(shù)量 create or replace function dept_count (dept_no varchar(20) returns integer begin declare d_count integer; select count (* ) into d_count from

11、 T where T.dno = dept_no return d_count; end找出教師數(shù)大于12的所有系的名稱select dnamefrom Dwhere dept_count (dno ) 1226 七月 2022186.2函數(shù)和存儲過程設(shè)計表函數(shù)：SQL:2003 增加了返回關(guān)系作為結(jié)果的函數(shù)，稱為表函數(shù)(table functions) 示例：返回一個包含特定系的所有教師的表create function instructors_of (dno char(20)returns table ( ID varchar(5),name varchar(20), dno varcha

12、r(20),salary numeric(8,2)return table(select tno, tname, dno, salary from T where T.dno = instructors_of.dno)使用時需要加上函數(shù)名作為前綴select *from table (instructors_of (d01)26 七月 2022196.2函數(shù)和存儲過程設(shè)計SQL也支持過程dept_count函數(shù)可以寫成一個過程 ：create procedure dept_count_proc (in dno varchar(20), out d_count integer)begin sel

13、ect count(*) into d_count from T where T.dno = dept_count_proc.dno end26 七月 2022206.2函數(shù)和存儲過程設(shè)計可以在一個SQL過程中或者在嵌入式SQL中使用call語句調(diào)用過程 declare d_count integer;call dept_count_proc( d11, d_count);過程和函數(shù)可以通過動態(tài)SQL觸發(fā) SQL:1999 允許使用多個同名過程/函數(shù)（稱為名字重載），只要參數(shù)的個數(shù)不同，或?qū)τ谀切┯邢嗤瑓?shù)個數(shù)的函數(shù)，至少有一個參數(shù)的類型不同 26 七月 2022216.2函數(shù)和存儲過程設(shè)計警

14、告：大多數(shù)數(shù)據(jù)庫系統(tǒng)實現(xiàn)他們自己的語法標(biāo)準(zhǔn)閱讀你的系統(tǒng)手冊，看看在你的系統(tǒng)上怎樣工作復(fù)合語句： begin end, begin 和 end之間會包含復(fù)雜的SQL語句可以在復(fù)合語句中聲明局部變量While和repeat 語句：declare n integer default 0;while n 1.1 * orow.sal)beginsignal SQLSTATE 7500 (“Salary increase 10%)end31數(shù)據(jù)庫系統(tǒng)概念-高級6.3觸發(fā)器設(shè)計當(dāng)帳戶透支時，將帳戶余額設(shè)為0，并建一筆貸款，其金額為透支額create trigger overdraft-trigger af

15、ter update on accountreferencing new row as nrow for each rowwhen nrow.balance 0begin atomic insert into borrower (select customer-name, account-number from depositor where nrow.account-number = depositor.account-number); insert into loan values(nrow.account-number, nrow.branch-name, nrow.balance);

16、update account set balance = 0 where account.account-number = nrow.account-numberend32數(shù)據(jù)庫系統(tǒng)概念-高級6.3Oracle中的觸發(fā)器實例CREATE OR REPLACE TRIGGER SI.TRIG_MEDI_ACCOUNT_UPDATE BEFORE UPDATE OF DWJZE,FSRQ,GRJZE,JFJS,SBJGBH,XZBZ ON SI.MEDI_ACCOUNT REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW begin if nvl(:new

17、.fsrq,sysdate)nvl(:old.fsrq,sysdate + 1) then raise_application_error(-20100,試圖修改醫(yī)療帳戶信息！(Try update fsrq ); end if; if nvl(:new.xzbz,*)nvl(:old.xzbz,#) then raise_application_error(-20100,試圖修改醫(yī)療帳戶信息！(Try update xzbz ); end if; if nvl(:new.jfjs,0)nvl(:old.jfjs,1) then raise_application_error(-20100,試

18、圖修改醫(yī)療帳戶信息！(Try update jfjs ); end if; end;6.3觸發(fā)器設(shè)計觸發(fā)器練習(xí)：設(shè)計觸發(fā)器，當(dāng)學(xué)生的出生日期有改變時，自動修改學(xué)生的年齡。26 七月 20223334數(shù)據(jù)庫系統(tǒng)概念-高級6.4斷言設(shè)計斷言定義CREATE ASSERTION CHECK 斷言是謂詞，表達(dá)數(shù)據(jù)庫總應(yīng)該滿足的條件一旦定義了斷言，DBMS驗證其有效性，并且對每個可能違反該斷言的更新操作都進行檢查這種檢查會帶來巨大的系統(tǒng)負(fù)載，因此應(yīng)該謹(jǐn)慎使用斷言對斷言“所有X, P(X)”，是通過檢查“not exists X， P(X)”來實現(xiàn)的35數(shù)據(jù)庫系統(tǒng)概念-高級6.4斷言設(shè)計示例：不允許男同學(xué)選修001老師課程create assertion ASSE1 check(not exists(