PL/SQL學習筆記-6

PL/SQL學習筆記-6

第11-12章

[@more@]

第11章 資料庫觸發器
11.1 觸發器的型別
1. DML觸發器
2. Instead-Of觸發器 只可以定義為檢視(關係型或是物件的)的觸發器。
3. 系統觸發器

11.2 建立觸發器
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} triggering_event
[referencing_clause]
[WHEN trigger_condition]
[FOR EACH ROW]
trigger_body;

觸發器體不能超過32K。
觸發器總共有12種可能的型別，即：3個語句*2個計時*2個級別

虛擬碼
觸發語句 :old :new
INSERT NULL 當語句完成時，將要被插入的值
UPDATE 更新前的原始值 當語句完成時，將要被插入的值
DELETE 行被刪除前的原始值 NULL

REFERENCING [OLD AS old_name] [NEW AS new_name]
可以用REFERENCING子句來為:old和:new指定不同的名稱。
WHEN子句只對行級別有效。
在觸發器裡面有INSERT、UPDATE和DELETE三個布林函式可以用來確定操作是什麼。

Instead-of觸發器
該觸發器用於以下兩種情況：
1. 允許修改一個本來不可修改的檢視。
2. 修改檢視中某巢狀表列的列。
所有的Instead-of觸發器都是針對行級別的。

建立系統觸發器
CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
{BEFORE |AFTER}
{ddl_event_list|database_event_list}
ON {DATABASE | [schema.]SCHEMA}
[when_clause]
trigger_body;
ddl_event_list是一個或者多個DDL事件(用OR分開)
database_event_list是一個或者多個資料庫事件(用OR分開)
系統觸發器沒有instead-of觸發器。truncate沒有資料庫事件。
STARTUP AFTER
SHUTDOWN BEFORE
SERVERERROR AFTER
LOGON AFTER
LOGOFF BEFORE
CREATE BEFORE,AFTER
DROP BEFORE,AFTER
ALTER BEFORE,AFTER
系統觸發器可以在資料庫級別或模式級別進行定義。
STARTUP和SHUTDOWN觸發器只在資料庫級別上市相關的。
在系統觸發器中可以使用的屬性函式
SYSEVENT、INSTANCE_NUM、DATABASE_NAME、SERVER_ERROR、IS_SERVERERROR、LOGIN_USER、DICTIONARY_OBJ_TYPE、
DICTIONARY_OBJ_NAME、DICTIONARY_OBJ_OWNERDES_ENCRYPTED_PASSWORD

觸發器的限制：
* 觸發器不可以發出任何事務控制語句：COMMIT、ROLLBACK、SAVEPOINT或SETTRANSACTION。
* 觸發器呼叫的儲存過程也都不能發出任何事務控制語句。(除非它們被宣告為自治的)
* 觸發器不能宣告所有LONG或LONG RAW變數。
* 在Oralce8以上版本中，觸發器可以引用和使用LOB和物件列，但是不能修改。

觸發器的原始碼儲存在user_triggers中。
DROP TRIGGER triggername;
ALTER TRIGGER triggername {DISABLE|ENABLE};
可以使用ENABLE ALL TRIGGERS或DISABLE ALL TRIGGERS子句，禁用或啟用指定表的所有觸發器。

11.3 變異表
觸發器體中的SQL語句不可以：
* 讀取或修改任何觸發語句的變異表。
* 讀取或修改觸發表的一個約束表的primary、unique或foreign關鍵字的列。
這些限制適合用於所有行級別的觸發器。

第12章 高階特性
12.1 語言特性
1.外部例程
C外部例程
(1)編寫C函式，編譯進檔案系統中的共享庫。例如寫了一個函式sendMail編譯到 /libs/libmail.so
(2)建立庫物件 create or replace library SendMailLibrary as '/libs/libmail.so'
(3)建立過程能 create or replace procedure SendMailC(p_subject IN VARCHAR2,p_message IN VARCHAR2)
AS EXTERNAL
LIBRARY SendMailLibrary
NAME "sendMail"
PARAMETERS(p_subject STRING,p_message STRING)
伺服器會呼叫一個extproc的程式來執行。
Java外部例程
(1) 編寫一個java類
(2) create or replace procedure SendMailJava(p_subject IN VARCHAR2,p_message IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'sendMail.send(java.lang.String,java.lang.String)';
2.動態SQL
execute immediate 可以執行 DDL、DML和匿名PL/SQL語句塊。
execute immediate 還可以用來執行帶有繫結變數的語句。
execute immediate 'insert into test values(:id)' USING 1;
3.成批繫結
FORALL v_Count IN 1..100 [SAVE EXCEPTION]
....
當碰到錯誤的時候，立即會返回，而當前的SQL會被回滾，但是之前的SQL全部執行成功。
SAVE EXCEPTION 可以讓程式在碰到錯誤的時候儲存錯誤資訊，然後繼續往後執行。
SQL%BULK_EXCEPTIONS.COUNT 錯誤的個數
SQL%BULK_EXCEPTIONS(v_count).error_index 出錯的索引數
SQL%BULK_EXCEPTIONS(v_count).error_code 出錯的錯誤號
SQLERRM(0-SQL%BULK_EXCEPTIONS(v_count).error_code) 顯示錯誤資訊

BULK COLLECT子句
SELECT num_col,char_col BULK COLLECT INTO v_Numbers,v_Strings FROM temp_tarble
OPEN c_char; FETCH c_char BULK COLLECT INTO v_String2; CLOSE c_char;
DELETE FROM temp_table WHERE num_col=v_number RETURNING char_col BULK COLLECT INTO v_Strings;

4.物件型別
CREATE OR REPLACE TYPE Point AS OBJECT{
x NUMBER,
y NUMBER,
MEMBER FUNCTION ToString RETURN VARCHAR2,
MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0)) RETURN NUMBER
}

CREATE TABLE point_object_tabl OF Point;
CREATE TABLE point_column_tab ( key VARCHAR2(20),value Point);
將返回結果作為一個物件(而不是單個列)，你必須使用VALUE運算子。

5.大物件
CLOB CNLOB BLOB BFILE
當大物件不超過4000個位元組的時候，可以直接操作，否則必須要使用專用的介面來操作了。

6.管道化表函式
CREATE OR REPLACE FUNCTION PipelineMe RETURN MyTypeList PIPELINED AS
v_MyType MyType;
BEGIN
FOR v_count IN 1..20 LOOP
v_MyType := ...
PIPE ROW(v_MyType);
END LOOP;
END PipelineMe;

12.2 高階包
DBMS_SQL
DBMS_PIPE
DBMS_ALERT
UTL_FILE
UTL_TCP
UTL_SMTP
UTL_HTTP
UTL_INADDR
DBMS_JOB
DBMS_LOB