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

1、26 七月 20221第6章: 其他數(shù)據(jù)庫對象設(shè)計(jì)主要內(nèi)容6.1視圖設(shè)計(jì)6.2函數(shù)和存儲過程設(shè)計(jì)6.3觸發(fā)器設(shè)計(jì)6.4斷言設(shè)計(jì) 26 七月 20222數(shù)據(jù)庫系統(tǒng)概念-導(dǎo)論6.1視圖設(shè)計(jì)在某些情況下，讓所有的用戶看到整個邏輯模型（即所有實(shí)際存儲在數(shù)據(jù)庫中的關(guān)系）是不可取的 考慮一個職員需要知道教師的標(biāo)識、姓名和所在系名，但是沒有權(quán)限看到教師的工資值 此人應(yīng)該看到的關(guān)系由如下SQL語句所描述： select tno, tname, dno from T視圖提供一個對某些用戶從視圖中隱藏某些數(shù)據(jù)的機(jī)制 任何不是邏輯模型的一部分，但作為虛關(guān)系對用戶可見的關(guān)系稱為視圖 6.1視圖設(shè)計(jì)用 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è)計(jì)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è)計(jì)一個視圖可以用于定義另一個視圖的表達(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è)計(jì)視圖擴(kuò)展一種通過其他視圖的定義來定義視圖含義的方法 設(shè)視圖v1是由表達(dá)式e1定義的，可能它本身就包含對視圖關(guān)系的使用 一個表達(dá)式中的視圖擴(kuò)展重復(fù)以下替換步驟:repeat找到e1中的關(guān)系vi 使用定義vi的表達(dá)式替換vi until 在e1中沒有視圖關(guān)系只要視圖定義不是遞歸的,此循環(huán)就能終止 26 七月 202266.1視圖設(shè)計(jì)視圖更新大部分的 SQL實(shí)現(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è)計(jì)視圖作用：視圖在一定程度上保證了數(shù)據(jù)的邏輯獨(dú)立性視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)26 七月 202286.1視圖設(shè)計(jì)物化視圖: 創(chuàng)建一個物理表，此表包含定義視圖的查詢結(jié)果的所有元組 如果查詢中使用的關(guān)系發(fā)生了更新，則物化視圖中的結(jié)果就會過期 每當(dāng)視圖的底層關(guān)系進(jìn)行更新時要更新視圖，以此來維護(hù)視圖 26 七月 2022910數(shù)據(jù)庫系統(tǒng)概念-SQL6.1視圖設(shè)計(jì)使用物化視圖的目的是為了提高查詢性能，是以空間換時間的一種有效手段，更少的物理讀/寫，更少的cpu時間，更快的響應(yīng)速度；物化視圖對應(yīng)用透明；物化視圖需要占用存儲空間；當(dāng)基表發(fā)生變化時，物化視

6、圖也應(yīng)當(dāng)刷新。 規(guī)模較大的報表適合使用物化視圖來提高查詢性能。 11數(shù)據(jù)庫系統(tǒng)概念-SQL6.1視圖設(shè)計(jì)建立物化視圖之前的準(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è)計(jì)為物化視圖所在的表建立日志：-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è)計(jì)建立物

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

10、在數(shù)據(jù)庫以外，但是把它們定義成數(shù)據(jù)庫中的函數(shù)和過程有下列優(yōu)點(diǎn)：在數(shù)據(jù)庫中的函數(shù)和過程允許多個應(yīng)用訪問允許當(dāng)業(yè)務(wù)規(guī)則發(fā)生變化是進(jìn)行單個點(diǎn)的改變，而不必改變應(yīng)用系統(tǒng)的其他部分應(yīng)用系統(tǒng)可以調(diào)用存儲過程，而不是直接更新數(shù)據(jù)庫26 七月 2022176.2函數(shù)和存儲過程設(shè)計(jì)定義一個函數(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è)計(jì)表函數(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è)計(jì)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è)計(jì)可以在一個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è)計(jì)警

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