視圖：是一個封裝了各種復雜查詢的語句，就稱為視圖。不存儲數據，只存儲定義，定義被保存在數據字典中
作用：1、可以保證安全，隱藏一些數據，保證數據不會被誤刪；
2、多表連接，可以使復雜的查詢易於理解和使用
15.1、創建視圖
需要權限才能創建
grant create view to scott；
CREATE OR REPLACE VIEW 視圖名字(字段) AS 子查
CREATE OR REPLACE VIEW v_表名_業務 AS 查詢語句
建立一個只包含 20 部門雇員信息的視圖（雇員的編號、姓名、工資）
CREATE VIEW empv20 (empno,ename,sal) AS SELECT empno,ename,sal FROM emp WHERE deptno=20 ;
現在直接更新視圖裡的數據 將 7369 的部門編號修改為 30。此操作在視圖中完成。
update empv20 SET deptno=30 where empno=7369 ; 此時，提示更新完成。
默認情況下創建的視圖，如果更新了，則會自動將此數據從視圖中刪除，之後會更新原本的數據。
在建立視圖的時候有兩個參數：
· WITH CHECK OPTION à 保護視圖的創建規則
CREATE OR REPLACE VIEW empv20
AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20
WITH CHECK OPTION CONSTRAINT empv20_ck;—-約束條件
ALTER TABLE emp1 add CONSTRAINT empv20_ck check(約束條件);
再執行更新操作：
update empv20 SET deptno=30 where empno=7369 ; à 此處更新的是部門編號，失敗
|- 之前是按照部門編號建立的視圖，所以不能修改部門編號
update empv20 SET ename=’tom’ where empno=7369 ; à 可以更新，更新的是名字，成功
· WITH READ ONLY（只讀，不可修改），視圖最好不要輕易的修改
CREATE OR REPLACE VIEW empv20
AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20
WITH READ ONLY;
現在任意的字段都不可更改，所以現在的視圖是只讀的。
如果視圖的基表有多行查詢（比如：group by,distinct）那麼該視圖也是只讀的
15.1、查看視圖
SELECT * FROM v_emp;
update v_emp SET deptno=20 where empno=7369;
DELETE FROM v_emp WHERE empno=7369;
ROLLBACK;
DROP VIEW v_emp;
在視圖終不能被修改刪除，1、多表構成的視圖；2、group by
工作原理：rowid
用執行計劃檢測索引是否起作用（set autot on exp）
執行計劃 cbo（選擇）和cro(規則)
16.1、索引
索引是一種用於提升查詢效率的數據庫對象，通過快速定位數據的方法，索引信息與表獨立存放，Oracle數據庫自動使用和維護索引
索引的存儲
索引和表都是獨立存在的。在為索引指定表空間的時候，不要將被索引的表和索引指向同一個表空間，這樣可以避免產生 IO 沖突。使 Oracle 能夠並行訪問存放在不同硬盤中的索引數據和表數據，更好的提高查詢速度。
16.2、索引優缺點
建立索引的優點
1.加快數據的檢索速度;
2.創建唯一性索引，保證數據庫表中每一行數據的唯一性;
3.加速表和表之間的連接;
4.在使用分組和排序子句進行數據檢索時，可以顯著減少查詢中分組和排序的時間。
索引的缺點
1.索引需要占物理空間。
2.當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，降低了數據的維護速度。
16.3、創建索引
創建索引的方式
1、自動創建：在定義主鍵或唯一鍵約束時系統會自動在相應的字段上創建唯一性索引
2、手動創建：用戶可以在其他列上創建非唯一索引，以加速查詢。
創建索引：創建索引一般有以下兩個目的：維護被索引列的唯一性和提供快速訪問表中數據的策略。
–在 select 操作占大部分的表上創建索引；
–在 where 子句中出現最頻繁的列上創建索引；
–在選擇性高的列上創建索引（補充索引選擇性，最高是 1，eg：primary key）
–復合索引的主列應該是最有選擇性的和 where 限定條件最常用的列，並以此類推第二列……。
–小於 5M 的表，最好不要使用索引來查詢，表越小，越適合用全表掃描。
Create [UNIQUE|BITMAP] index
[schema.]index_name on [schema.]table_name(column_name[ASC|DESC],…n,[column_expression])|CLUSTER [schema.]cluster_name
[INITRANS integer]
[MAXTRANS integer]
[PCTFREE integer]
[PCTUESD integer]
[TABLESPACE tablespace_name]
[STORAGE storage_clause]
[NOSORT]
[REVERSE]
UNIQUE 指定索引所基於的列（或多列）值必須唯一。默認的索引是非唯一的。
BITMAP 指定建立位映射索引而不是B*索引。位映射索引保存的行標識符與作為位映射的鍵值有關。位映射中的每一位都對應於一個可能的行標識符，位設置意味著具有對應行標識符的行包含該鍵值。
ON table_name 建立基於函數的索引。用table_name的列、常數、SQL函數和自定義函數創建的表達式。指定column_expression，以後用基於函數的索引查詢時，必須保證查詢該column_expression不為空。
CLUSTER 創建cluster_name簇索引。若表不用schema限制，oracle假設簇包含在你自己的方案中。不能為散列簇創建簇索引。
NOSORT 數據庫中的行以升序保存，在創建索引時不必對行排序。若索引列或多列的行不以升序保存，oracle會返回錯誤。
REVERSE 指定以反序索引塊的字節，不包含行標識符。NOSORT不能與REVERSE一起指定。
CREATE INDEX idx_表名_列名 on 表名（列1，列2…）
create index abc on student(sid,sname);
create index abc1 on student(sname,sid);
這兩種索引方式是不一樣的
索引 abc 對 Select * from student where sid=1; 這樣的查詢語句更有效索引 abc1 對 Select * from student where sname=?louis?; 這樣的查詢語句更有效
因此建立索引的時候，字段的組合順序是非常重要的。一般情況下，需要經常訪問的字段放在組合字段的前面
16.4、使用索引的原則
–查詢結果是所有數據行的 5%以下時，使用 index 查詢效果最好；
–where 條件中經常用到表的多列時，使用復合索引效果會好於幾個單列索引。因為當 sql 語句所查詢的列，全部都出現在復合索引中時，此時由於 Oracle 只需要查詢索引塊即可獲得所有數據，當然比使用多個單列索引要快得多；
–索引利於 select，但對經常 insert，delte 尤其 update 的表，會降低效率。
eg：試比較下面兩條 SQL 語句(emp 表的 deptno 列上建有 ununique index)：
語句 A：SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
語句 B：SELECT dname, deptno FROM dept WHERE NOT EXISTS(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
這兩條查詢語句實現的結果是相同的，但是執行語句 A 的時候，ORACLE 會對整個 emp 表進行掃描，沒有使用建立在 emp 表上的 deptno 索引，執行語句 B 的時候，由於在子查詢中使用了聯合查詢，ORACLE 只是對 emp 表進行的部分數據掃描，並利用了 deptno 列的索引，所以語句 B 的效率要比語句 A 的效率高。
—-where 子句中的這個字段，必須是復合索引的第一個字段；
eg：一個索引是按 f1, f2, f3 的次序建立的，若 where 子句是 f2 = : var2, 則因為 f2 不是索引的第 1 個字段，無法使用該索引。
—- where 子句中的這個字段，不應該參與任何形式的計算：任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊。 —-應盡量熟悉各種操作符對 Oracle 是否使用索引的影響：以下這些操作會顯式
（explicitly）地阻止 Oracle 使用索引： is null ; is not null ; not in; !=; like ; numeric_col+0;date_col+0; char_col||’ ‘; to_char; to_number，to_date 等。
Eg：
Select jobid from mytabs where isReq=’0’ and to_date (updatedate) >= to_Date ( ‘2001-7-18’,
‘YYYY-MM-DD’)；–updatedate 列的索引也不會生效。
在正確使用索引的前提下，索引可以提高檢索相應的表的速度。當用戶考慮在表中使用索引時，應遵循下列一些基本原則。
（1）在表中插入數據後創建索引。在表中插入數據後，創建索引效率將更高。如果在裝載數據之前創建索引，那麼插入每行時oracle都必須更改索引。
（2）索引正確的表和列。如果經常檢索包含大量數據的表中小於15%的行，就需要創建索引。為了改善多個表的相互關系，常常使用索引列進行關系連接。
（3）主鍵和唯一關鍵字所在的列自動具有索引，但應該在與之關聯的表中的外部關鍵字所在的列上創建索引。
（4）合理安排索引列。在create index 語句中，列的排序會影響查詢的性能，通常將最常用的列放在前面。創建一個索引來提高多列的查詢效率時，應該清楚地了解這個多列的索引對什麼列的存取有效，對什麼列的存取無效。
（5）限制表中索引的數量。盡管表可以有任意數量的索引，可是索引越多，在修改表中的數據時對索引做出相應更改的工作量也越大，效率也就越低。同樣，目前不用的索引應該及時刪除。
（6）指定索引數據塊空間的使用。創建索引時，索引的數據塊是用表中現存的值填充的，直到達到PCTFREE為止。如果打算將許多行插入到被索引的表中，PCTFREE就應設置得大一點，不能給索引指定PCTUSED。
（7）根據索引大小設置存儲參數。創建索引之前應先估計索引的大小，以便更好地促進規劃和管理磁盤空間。單個索引項的最大值大約是數據塊大小的一半。
16.6、刪除索引
drop index PK_DEPT1;
16.7、索引類型
B樹索引 B-tree indexes；
B樹索引又可分為以下子類：
索引組織表Index-organized tables；
反轉索引Reverse key indexes；
降序索引Descending indexes；
B樹聚簇索引B-tree cluster indexes；
位圖和位圖聯合索引Bitmap and bitmap join indexes；
基於函數的索引Function-based indexes；
應用域索引Application domain indexes；
B-Tree是一個平衡樹的結構【注意這裡的B表示Balanced平衡的意思，而不是Binary二叉】，B樹索引也是Oracle裡最為常見的索引類型。B樹索引裡的數據是已經按照關鍵字或者是被索引字段事先排好序存放的，默認是升序存放。
對於這幅B樹存儲結構圖作以下幾點介紹：
1 、索引高度是指從根塊到達葉子塊時所遍歷的數據塊的個數，而索引層次=索引高度-1；本圖中的索引的高度是3，索引層次等於2；通常，索引的高度是2或者3，即使表中有上百萬條記錄，也就意味著，從索引中定位一個鍵字只需要2或3次I/O,索引越高，性能越差；
2、 B樹索引包含兩種數據塊兒：分枝塊(Branch Block)和葉子塊(Leaf Block)；
3 、分枝塊裡存放指向下級分枝塊(索引高度大於2，即有超過兩層分枝塊的情況)或者直接指向葉子塊的指針(索引高度等於2，即層次為1的索引)；
4 、葉子塊，就是位於B樹結構裡最底層的數據塊。葉子塊裡存放的是索引條目，即索引關鍵字和rowid，rowid用來精確定位表裡的記錄；索引條目都是按照索引關鍵字+rowid已經排好序存放的；同一個葉子塊裡的索引條目同時又和左右兄弟條目形成鏈表，並且是一個雙向鏈表；
5 、B樹索引的所有葉子塊一定位於同一層上，這是由B樹的數據結構定義的。因此，從根塊到達任何一個葉子塊的遍歷代價都是相同的；
B 樹索引（B-Tree Index）
創建索引的默認類型，結構是一顆樹，采用的是平衡 B 樹算法：
l 右子樹節點的鍵值大於等於父節點的鍵值 l 左子樹節點的鍵值小於等於父節點的鍵值
位圖索引(BitMap Index)
如果表中的某些字段取值范圍比較小，比如職員性別、分數列 ABC 級等。只有兩個值。
這樣的字段如果建 B 樹索引沒有意義，不能提高檢索速度。這時我們推薦用位圖索引
Create BitMap Index student on(sex);
索引按功能和索引對象分還有以下類型。
（1）唯一索引意味著不會有兩行記錄相同的索引鍵值。唯一索引表中的記錄沒有RowID，不能再對其建立其他索引。在oracle10g中，要建立唯一索引，必須在表中設置主關鍵字，建立了唯一索引的表只按照該唯一索引結構排序。
（2）非唯一索引不對索引列的值進行唯一性限制。
（3）分區索引是指索引可以分散地存在於多個不同的表空間中，其優點是可以提高數據查詢的效率。
（4）未排序索引也稱為正向索引。Oracle10g數據庫中的行是按升序排序的，創建索引時不必指定對其排序而使用默認的順序。
（5）逆序索引也稱反向索引。該索引同樣保持列按順序排列，但是顛倒已索引的每列的字節。
按照索引所包含的列數可以把索引分為單列索引和復合索引。索引列只有一列的索引為單列索引，對多列同時索引稱為復合索引。
16.8、管理索引
1）先插入數據後創建索引
向表中插入大量數據之前最好不要先創建索引，因為如果先建立索引。那麼在插入每行數據的時候都要更改索引。這樣會大大降低插入數據的速度。
2）設置合理的索引列順序
3）限制每個表索引的數量
4）刪除不必要的索引
5）為每個索引指定表空間
6）經常做 insert，delete 尤其是 update 的表最好定期 exp/imp 表數據，整理數據，降低碎片（缺點：要停應用，以保持數據一致性，不實用）；
有索引的最好定期 rebuild 索引（rebuild期間只允許表的 select 操作，可在數據庫較空閒時間提交），以降低索引碎片，提高效率
16.8、索引問題
1.一個表的查詢語句可以同時用到兩個索引。
2.索引是以獨立於表存在的一種數據庫對象，它是對基表的一種排序（默認是 B 樹索引就是二叉樹的排序方式），比如：
3.這樣的查詢效率，肯定是大於沒有索引情況的全表掃描（table access full)，但是有兩個問題。
問題一：建立索引將占用額外的數據庫空間，更重要的是增刪改操作的時候，索引的排序也必須改變，加大的維護的成本 問題二：如果經常查詢 x=?和 y=?，那推薦使用組合 index(x,y)，這種情況下組合索引的效率是遠高於兩個單獨的索引的。
同時在用組合索引的時候，大家一定要注意一個細節：建立組合索引 index(x,y,z)的時候，那在查詢條件中出現 x,xy,xyz,yzx 都是可以用到該組合索引，但是 y,yz,z 是不能用到該索引的。
第十三章 序列、同義詞
13.1、 創建序列(sequence)
CREATE SEQUENCE seq
INCREMENT BY 2–增量
START WITH 2–起始值 不能小於min
MAXVALUE 10–最大值
MINVALUE 1–最小值
CYCLE–/NOCYCLE 序列號是否可循環（到了maxvalue在從min開始）
CACHE 5–/NOCACHE 緩存下一個值，必須滿足大於1，小於等於（MAXVALUE-MINVALUE）/INCREMENT
NOORDER–/NOORDER 序列號是否順序產生
13.2、 屬性 NextVal,CurrVal
–當前值 —–currval
SELECT seq.currval FROM dual;
–下一個值——nextval
SELECT seq.nextval FROM dual;
(必須先有 nextval，才能有 currval)
–在向表中插入數據時使用
INSERT INTO emp1(empno) VALUES(seq.nextval);
使用 cache 或許會跳號， 比如數據庫突然不正常 down 掉
（shutdown abort),cache中的sequence就會丟失. 所以可以在create sequence的時候用nocache防止這種情況
13.3、 修改
不能改變當前值，但是可以改變增量
ALTER SEQUENCE seq INCREMENT BY 3;
ALTER SEQUENCE seq CACHE 3;
13.4、 刪除
DROP SEQUENCE seq；
13.5、 同義詞 (synonym)
Select * from dual;
為什麼？因為同義詞的存在
Dual 其實是 sys 用戶下的一張表
select table_name from user_tables where lower(table_name) = ‘dual’;
1、概念
同義詞是數據庫方案對象的一個別名，經常用於簡化對象訪問和提高對象訪問的安全性。在使用同義詞時，Oracle數據庫將它翻譯成對應方案對象的名字。與視圖類似，同義詞並不占用實際存儲空間，只有在數據字典中保存了同義詞的定義。在Oracle數據庫中的大部分數據庫對象，如表、視圖、同義詞、序列、存儲過程、包等等，數據庫管理員都可以根據實際情況為他們定義同義詞。
同義詞，顧名思義就是兩個詞的意思一樣，可以互相替換.
2、作用：
1) 多用戶協同開發中，可以屏蔽對象的名字及其持有者。如果沒有同義詞，當操作其他用戶的表時，必須通過user名.object名的形式，采用了Oracle同義詞之後就可以隱蔽掉user名，當然這裡要注意的是：public同義詞只是為數據庫對象定義了一個公共的別名，其他用戶能否通過這個別名訪問這個數據庫對象，還要看是否已經為這個用戶授權。
2) 簡化sql語句。上面的一條其實就是一種簡化sql的體現，同時如果自己建的表的名字很長，可以為這個表創建一個Oracle同義詞來簡化sql開發。
3)為分布式數據庫的遠程對象提供位置透明性。
4）Oracle同義詞在數據庫鏈接中的作用
數據庫鏈接是一個命名的對象，說明一個數據庫到另一個數據庫的路徑，通過其可以實現不同數據庫之間的通信。
Create database link 數據庫鏈名 connect to user名 identified by 口令 using ‘Oracle連接串’;
訪問對象要通過 object名@數據庫鏈名。同義詞在數據庫鏈中的作用就是提供位置透明性。
3、分類：
Create synonym dept for soctt.dept;(這樣創建的同義詞是私有的，只有創建者才能用)
Drop synonym dept;
Create public synonym dept for soctt.dept;（這樣創建的同義詞才是公有的）
Drop public synonym dept;
4、權限管理
與同義詞相關的權限有CREATE SYNONYM、CREATE ANY SYNONYM、CREATE PUBLIC SYNONYM權限。
1：用戶在自己的模式下創建私有同義詞，這個用戶必須擁有CREATE SYNONYM權限，否則不能創建私有同義詞。
2：如果需要在其它模式下創建同義詞，則必須具有CREATE ANY SYNONYM的權限。
3：創建公有同義詞則需要CREATE PUBLIC SYNONYM系統權限。
5、查看同義詞
SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME IN ( ‘SYSN_TEST’,’PUBLIC_TEST’);
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
PUBLIC PUBLIC_TEST ETL TEST
ETL SYSN_TEST ETL TEST
SQL> SELECT * FROM USER_SYNONYMS
6、使用同義詞
SELECT * FROM SYSN_TEST;
使用同義詞可以保證當數據庫的位置或對象名稱發生改變時，應用程序的代碼保持穩定不變，僅需要改變同義詞；
當使用一個沒有指定schema的同義詞是，首先在用戶自己的schema中尋找，然後再公共同義詞中尋找
7、刪除同義詞
DROP [ PUBLIC ] SYNONYM [ schema. ] 同義詞名稱 [ FORCE ];
DROP SYNONYM SYSN_TEST;
DROP PUBLIC SYNONYM PUBLIC_TEST;–當同義詞的原對象被刪除是，同義詞並不會被刪除
8、編譯同義詞
ALTER SYNONYM T COMPILE; –當同義詞的原對象被重新建立時，同義詞需要重新編譯
對原對象進行DDL操作後，同義詞的狀態會變成INVALID；當再次引用這個同義詞時，同義詞會自動編譯，狀態會變成VALID，無需人工干預，當然前提是不改變原對象的名稱
問題錦集
1：公用同義詞與私有同義詞能否同名呢？如果可以，訪問同義詞時，是共有同義詞還是私有同義詞優先？
可以，如果存在公用同義詞和私有同義詞同名的情況，在訪問同義詞是，訪問的是私有同義詞的指向的對象。
2：為啥OE用戶創建的公用同義詞，HR用戶不能訪問呢？
因為HR沒有訪問OE模式下對象的權限，如果OE模式給HR用戶賦予了SELECT對象等權限，那麼HR用戶即可訪問。
3：對象、私有同義詞、公共同義詞是否可以存在三者同名的情況？
存在同名對象和公共同義詞時，數據庫優先選擇對象作為目標，存在同名私有對象和公共對象時，數據庫優先選擇私有同義詞作為目標
in 只讀
out 只寫
in out 可讀寫
函數就是一個有返回值的過程。
定義一個函數：此函數可以根據雇員的編號查詢出雇員的年薪
CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE)
RETURN NUMBER
AS
rsal NUMBER ;
BEGIN
SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;
RETURN rsal ;
END ;
/
直接寫 SQL 語句，調用此函數：
SELECT myfun(7369) FROM dual ;
15.1分類：
DML觸發器———–基於表的（insert、alter、update）
替代觸發器———–基於VIEW的
系統觸發器———–基於系統的
好處：自動調用、記錄日志、保證數據安全、用數據庫觸發器可以保證數據的一致性和完整性。
語法：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}…]
ON [schema.]table_name | [schema.]view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;
BEFORE 和AFTER指出觸發器的觸發時序分別為前觸發和後觸發方式，前觸發是在執行觸發事件之前觸發當前所創建的觸發器，後觸發是在執行觸發事件之後觸發當前所創建的觸發器。
FOR EACH ROW選項說明觸發器為行觸發器。行觸發器和語句觸發器的區別表現在：行觸發器要求當一個DML語句操走影響數據庫中的多行數據時，對於其中的每個數據行，只要它們符合觸發約束條件，均激活一次觸發器；而語句觸發器將整個語句操作作為觸發事件，當它符合約束條件時，激活一次觸發器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則只能為行觸發器。
REFERENCING 子句說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照當前的新、舊列值，默認的相關名稱分別為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。
WHEN 子句說明觸發約束條件。Condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL 函數。WHEN 子句指定的觸發約束條件只能用在BEFORE 和AFTER行觸發器中，不能用在INSTEAD OF 行觸發器和其它類型的觸發器中。
15.2、 觸發器觸發次序
1. 執行 BEFORE語句級觸發器;
2. 對與受語句影響的每一行：
l 執行 BEFORE行級觸發器
l 執行 DML語句
l 執行 AFTER行級觸發器
3. 執行 AFTER語句級觸發器
15.3、語句觸發器
after 語句觸發器
Before 語句觸發器
例如：禁止工作人員在休息日改變雇員信息
create or replace trigger tr_src_emp
before insert or update or delete
on emp
begin
if to_char(sysdate,’DY’,’nls_date_language=AMERICAN’) in( ‘SAT’,’SUN’) then
raise_application_error(-20001,’can?t modify user information in weekend’);
end if;
end;
/
使用條件謂語———inserting、updating、deleting
create or replace trigger tr_src_emp
before insert or update or delete
on emp
begin
if to_char(sysdate,’DY’) in( ‘星期六’,’星期天’) then
case
when inserting then
raise_application_error(-20001,’fail to insert’);
when updating then
raise_application_error(-20001,’fail to update’);
when deleting then
raise_application_error(-20001,’fail to delete’);
end case;
end if;
end;
/
15.4、行觸發器
執行 DML 操作時，每作用一行就觸發一次觸發器。
Bofre 行觸發器
例如：確保員工工資不能低於原有工資
Create or replace trigger tr_emp_sal
before update of sal
on emp
for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20010,’sal should not be less’);
end if;
end;
/
after 行觸發器
例如：統計員工工資變化
Create table audit_emp_change(
Name varchar2(10),
Oldsal number(6,2),
Newsal number(6,2),
Time date);
Create or replace trigger tr_sal_sal after update of sal on emp for each row declare v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename; if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate); else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
end if; end;
/
限制行觸發器
Create or replace trigger tr_sal_sal
after update of sal
on emp
for each row when (old.job=?SALESMAN?)
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
end if;
end;
/
注意：
例如：如果要基於 EMP 表建立觸發器。那麼該觸發器的執行代碼不能包含對 EMP 表的查詢操作編寫 DML 觸發器的時，觸發器代碼不能從觸發器所對應的基表中讀取數據。
Create or replace trigger tr_emp_sal
Before update of sal
on emp
For each row
declare
Maxsal number(6,2);
Begin
If :new.sal>maxsal then Select max(sal) into maxsal from emp;
Raise_application_error(-21000,?error?);
End if;
End;
/
創建的時候不會報錯。但是一旦執行就報錯了
update emp set sal=sal*1.1 where deptno=30
DML觸發器的限制
l CREATE TRIGGER語句文本的字符長度不能超過32KB；
l 觸發器體內的SELECT 語句只能為SELECT … INTO …結構，或者為定義游標所使用的SELECT語句。
l 觸發器中不能使用數據庫事務控制語句 COMMIT; ROLLBACK, SVAEPOINT 語句；
l 由觸發器所調用的過程或函數也不能使用數據庫事務控制語句；
l 觸發器中不能使用LONG, LONG RAW 類型；
l 觸發器內可以參照LOB 類型列的列值，但不能通過 :NEW 修改LOB列中的數據；
:NEW 修飾符訪問操作完成後列的值
:OLD 修飾符訪問操作完成前列的值
特性
INSERT
UPDATE
DELETE
OLD
NULL
實際值
實際值
NEW
實際值
實際值
NULL
15.4 替代觸發器
語法：
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}…]
ON [schema.] view_name –只能定義在視圖上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] –因為INSTEAD OF觸發器只能在行級上觸發,所以沒有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;
例2：創建復雜視圖，針對INSERT操作創建INSTEAD OF觸發器，向復雜視圖插入數據。
l 創建視圖：
CREATE OR REPLACE FORCE VIEW “HR”.”V_REG_COU” (“R_ID”, “R_NAME”, “C_ID”, “C_NAME”)
AS
SELECT r.region_id,
r.region_name,
c.country_id,
c.country_name
FROM regions r,
countries c
WHERE r.region_id = c.region_id;
l 創建觸發器：
CREATE OR REPLACE TRIGGER “HR”.”TR_I_O_REG_COU”
INSTEAD OF INSERT
ON v_reg_cou
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id;
IF v_count = 0 THEN
INSERT INTO regions
(region_id, region_name
) VALUES
(:new.r_id, :new.r_name
);
END IF;
SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id;
IF v_count = 0 THEN
INSERT
INTO countries
(
country_id,
country_name,
region_id
)
VALUES
(
:new.c_id,
:new.c_name,
:new.r_id
);
END IF;
END;
創建INSTEAD OF觸發器需要注意以下幾點：
l 只能被創建在視圖上，並且該視圖沒有指定WITH CHECK OPTION選項。
l 不能指定BEFORE 或 AFTER選項。
l FOR EACH ROW子可是可選的，即INSTEAD OF觸發器只能在行級上觸發、或只能是行級觸發器，沒有必要指定。
l 沒有必要在針對一個表的視圖上創建INSTEAD OF觸發器，只要創建DML觸發器就可以了。
15.5、系統事件觸發器
語法：
CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE
{BEFORE|AFTER}
{ddl_event_list
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.]SCHEMA }
[WHEN condition]
PL/SQL_block | CALL procedure_name;
其中: ddl_event_list：一個或多個DDL 事件，事件間用 OR 分開；
database_event_list：一個或多個數據庫事件，事件間用 OR 分開；
下面給出系統觸發器的種類和事件出現的時機（前或後）：
事件
允許的時機
說明
STARTUP
AFTER
啟動數據庫實例之後觸發
SHUTDOWN
BEFORE
關閉數據庫實例之前觸發（非正常關閉不觸發）
SERVERERROR
AFTER
數據庫服務器發生錯誤之後觸發
LOGON
AFTER
成功登錄連接到數據庫後觸發
LOGOFF
BEFORE
開始斷開數據庫連接之前觸發
CREATE
BEFORE，AFTER
在執行CREATE語句創建數據庫對象之前、之後觸發
DROP
BEFORE，AFTER
在執行DROP語句刪除數據庫對象之前、之後觸發
ALTER
BEFORE，AFTER
在執行ALTER語句更新數據庫對象之前、之後觸發
DDL
BEFORE，AFTER
在執行大多數DDL語句之前、之後觸發
GRANT
BEFORE，AFTER
執行GRANT語句授予權限之前、之後觸發
REVOKE
BEFORE，AFTER
執行REVOKE語句收權限之前、之後觸犯發
RENAME
BEFORE，AFTER
執行RENAME語句更改數據庫對象名稱之前、之後觸犯發
AUDIT / NOAUDIT
BEFORE，AFTER
執行AUDIT或NOAUDIT進行審計或停止審計之前、之後觸發
/*create or replace trigger tr_emp1_1
–before update on emp1
after update on emp1
for each row
declare
– local variables here
begin
if :new.sal<:old.sal then
Raise_application_error(-20000, ‘:new.sal>:old.sal’);
end if;
end tr;*/
/*create or replace trigger tr_emp1_2
after delete on emp1
–before delete on emp1
for each row
begin
Raise_application_error(-20001, ‘不能刪’);
end;*/
create or replace trigger tr_emp1_3
after delete or update or insert on emp1
for each ROW
begin
if deleting then
if :new.sal>0 then
Raise_application_error(-20000, ‘deleting’);
end IF;
/*elsif updating then
if :new.sal<:old.sal then
Raise_application_error(-20001, ‘updating’);
end if;
else
if :new.sal<:old.sal then
Raise_application_error(-20002, ‘inserting’);
end if;*/
end if;
end;
16.1、存儲過程
CREATE OR REPLACE PROCEDURE procedure_name AS PL/SQL塊
現在定義一個簡單的過程，就是打印一個數字
CREATE OR REPLACE PROCEDURE myproc AS
i NUMBER ;
BEGIN
i := 100 ;
DBMS_OUTPUT.put_line(‘i = ‘||i) ;
END ;
/
執行過程： exec 過程名字———set serveroutput on
下面編寫一個過程，要求，可以傳入部門的編號，部門的名稱，部門的位置，之後調用此過程就可以完成部門的增加操作。
CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE,
name dept.dname%TYPE,
dl dept.loc%TYPE)
AS
cou NUMBER ;
BEGIN
– 判斷插入的部門編號是否存在，如果存在則不能插入
SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ;
IF cou=0 THEN
– 可以增加新的部門
INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ; DBMS_OUTPUT.put_line(‘部門插入成功！’) ;
ELSE
DBMS_OUTPUT.put_line(‘部門已存在，無法插入！’) ;
END IF ;
END ;
/
16.2 過程的參數類型：
? IN：值傳遞，默認的—–只讀
? IN OUT：帶值進，帶值出—可讀寫
? OUT：不帶值進，帶值出—-只寫
IN OUT 類型：
CREATE OR REPLACE PROCEDURE myproc(dno IN OUT dept.deptno%TYPE,
name dept.dname%TYPE,
dl dept.loc%TYPE) AS
cou NUMBER ;
BEGIN
– 判斷插入的部門編號是否存在，如果存在則不能插入
SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ;
IF cou=0 THEN
– 可以增加新的部門
INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ;
DBMS_OUTPUT.put_line(‘部門插入成功！’) ;
– 修改 dno 的值
dno := 1 ;
ELSE
DBMS_OUTPUT.put_line(‘部門已存在，無法插入！’) ; dno := -1 ;
END IF ;
END ;
/
編寫 PL/SQL 塊驗證過程：
DECLARE
deptno dept.deptno%TYPE ; BEGIN
deptno := 12 ;
myproc(deptno,’開發’,’南京’) ;
DBMS_OUTPUT.put_line(deptno) ;
END ;
/
OUT 類型 不帶任何值進，只把值帶出來。
CREATE OR REPLACE PROCEDURE myproc(dno OUT dept.deptno%TYPE)
AS
I number
BEGIN
I:= dno;
END ;
/
執行上面的存儲過程
DECLARE
deptno dept.deptno%TYPE ;
BEGIN
deptno :=myproc(deptno) ;
DBMS_OUTPUT.put_line(deptno) ;
END ;
/
17.1 定義
包——規范和主體
包就是將一系列的相關聯的PLSQL類型、項目和子程序等有計劃的組織起來封裝在一起
規范（包頭）———一個操作或應用的接口部分
主體———-對包的規范部分進行實現
語法;
包的規范
CREATE OR REPLACE PACKAGE package_name AS|IS
參數、類型(type)、異常(exception)、游標（cursor）、procedure、function
END[package_name];
包的主體
CREATE OR REPLACE PACKAGE BODY package_name AS|IS
參數、類型(type)、異常(exception)、游標（cursor）、procedure、function
END[package_name];
調用:
package_name.type_name;
package_name.item_name;
package_name.call_spec_name;
刪除：
drop package package_name;
drop package body package_name;
好處：
1、模塊化；—–提高應用程序的交互性
2、信息隱藏
例：
CREATE OR REPLACE PACKAGE DEMO_PKG
IS
DEPTREC DEPT%ROWTYPE;
–Add dept…
FUNCTION add_dept(
dept_no NUMBER,
dept_name VARCHAR2,
location VARCHAR2)
RETURN NUMBER;
–delete dept…
FUNCTION delete_dept(dept_no NUMBER)
RETURN NUMBER;
–query dept…
PROCEDURE query_dept(dept_no IN NUMBER);
END DEMO_PKG;
CREATE OR REPLACE PACKAGE BODY DEMO_PKG
IS
FUNCTION add_dept
(
dept_no NUMBER,
dept_name VARCHAR2,
location VARCHAR2
)
RETURN NUMBER
IS
empno_remaining EXCEPTION; –自定義異常
PRAGMA EXCEPTION_INIT(empno_remaining, -1);
/* -1 是違反唯一約束條件的錯誤代碼 */
BEGIN
INSERT INTO dept VALUES(dept_no, dept_name, location);
IF SQL%FOUND THEN
RETURN 1;
END IF;
EXCEPTION
WHEN empno_remaining THEN
RETURN 0;
WHEN OTHERS THEN
RETURN -1;
END add_dept;
FUNCTION delete_dept(dept_no NUMBER)
RETURN NUMBER
IS
BEGIN
DELETE FROM dept WHERE deptno = dept_no;
IF SQL%FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END delete_dept;
PROCEDURE query_dept
(dept_no IN NUMBER)
IS
BEGIN
SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘溫馨提示:數據庫中沒有編碼為’||dept_no||’的部門’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘程序運行錯誤,請使用游標進行操作!’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||’—-‘||SQLERRM);
END query_dept;
BEGIN
Null;
END DEMO_PKG;
