.Net程式員學用Oracle系列(8)：觸發器、任務、序列、連接

《.Net程式員學用Oracle系列：導航目錄》本文大綱 1、 " 觸發器 " 1.1、 "創建觸發器" 1.2、 "禁用觸發器 & 啟用觸發器 & 刪除觸發器" 2、 " 任務 " 2.1、 "DBMS_JOB 包介紹" 3、 " 序列 " 3.1、 "創建序列" 3.2、 "使用序列 & 刪除 ...

《.Net程式員學用Oracle系列：導航目錄》

本文大綱

1、觸發器
- 1.1、創建觸發器
- 1.2、禁用觸發器 & 啟用觸發器 & 刪除觸發器
2、任務
- 2.1、DBMS_JOB 包介紹
3、序列
- 3.1、創建序列
- 3.2、使用序列 & 刪除序列
4、連接
- 4.1、創建 dblink
- 4.2、使用 dblink & 刪除 dblink
5、總結

觸發器

創建觸發器

CREATE OR REPLACE TRIGGER trg_sync_staff90
-- 功能說明
AFTER INSERT OR UPDATE OR DELETE ON t_staff
FOR EACH ROW
DECLARE
  -- 定義變數
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF INSERTING THEN
    -- 定義DML語句
  ELSIF UPDATING THEN
    -- 定義DML語句
  ELSIF DELETING THEN
    -- 定義DML語句
  END IF;
END;

禁用觸發器 & 啟用觸發器 & 刪除觸發器

ALTER TABLE t_staff DISABLE ALL TRIGGERS; -- 禁用 t_staff 表上所有的觸發器
ALTER TABLE t_staff ENABLE ALL TRIGGERS;  -- 啟用 t_staff 表上所有的觸發器
ALTER TRIGGER trg_sync_staff90 DISABLE;   -- 禁用觸發器 trg_sync_staff90
ALTER TRIGGER trg_sync_staff90 ENABLE;    -- 啟用觸發器 trg_sync_staff90
DROP TRIGGER trg_sync_staff90;            -- 刪除觸發器 trg_sync_staff90

任務

DBMS_JOB 包介紹

創建任務的方法有很多，在實際使用過程中，我發現用 DBMS_JOB.SUBMIT 包來創建是比較方便的，語法是 DBMS_JOB.SUBMIT(jobno, what, next_date, interval)，示例：

DECLARE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(jobno, 'sp_sync_staff90;', SYSDATE, 'TRUNC(SYSDATE) + 1 + 2/24');
  COMMIT;
END;

估計有人會感到疑惑，為什麼創建任務的語句中 job 參數不給具體值，下麵就來簡要介紹下 DBMS_JOB.SUBMIT 各個參數及方法的用途和使用說明，如下：

job：任務編號，系統會自動分配。我嘗試過給一個自己喜歡的數字，但系統不認，還是自動分配了。
what：任務要執行的操作（如調用過程等），可通過 DBMS_JOB.WHAT(jobno,what) 修改。
next_date：任務的下一次執行時間，可通過 DBMS_JOB.NEXT_DATE(jobno,next_date) 修改。
interval：任務執行時間間隔，可通過 DBMS_JOB.INTERVAL(jobno,interval) 修改。
啟動 job：DBMS_JOB.RUN(jobno)。
禁用 job：DBMS_JOB.BROKEN(jobno,broken,next_date)，broken 參數需要提供布爾值，next_date 參數有預設值(SYSDATE)，不需要給值，broken 為 true 表示禁用任務，為 false 表示啟用任務，如 DBMS_JOB.BROKEN(135,TRUE) 表示禁用 jobno 為 135 的任務。
刪除 job：DBMS_JOB.REMOVE(jobno)。

以上關於 DBMS_JOB 的這些 PLSQL 都不能像普通 SQL 語句一樣直接運行，需要寫在 BEGIN 和 END 中間，示例：

BEGIN
  DBMS_JOB.BROKEN(135,TRUE);
  COMMIT;
END;

如果想看一下整個資料庫目前的任務情況，可以通過 user_jobs 視圖來查詢，示例：

SELECT job jobno,what,to_char(next_date, 'yyyy-mm-dd hh24:mi:ss') next_date,interval FROM user_jobs;

下麵來簡要說明一下 user_jobs 視圖各主要欄位的含義，如下：

job：NUMBER 類型，任務的唯一編號。
what：VARCHAR2(4000)，任務操作內容。
next_date：DATE 類型，下一次執行任務的時間。
interval：VARCHAR2(200)，任務執行時間間隔。
log_user：提交任務的用戶。
priv_user：賦予任務許可權的用戶。
schema_user：對任務作語法分析的用戶模式。
last_date：最後一次成功運行任務的時間。
last_sec：如 hh24:mm:ss 格式的 last_date 日期的小時，分鐘和秒。
this_date：正在運行任務的開始時間，如果沒有運行任務則為 null。
this_sec：如 hh24:mm:ss 格式的 this_date 日期的小時，分鐘和秒。

interval 參數設置案例：

每分鐘（的0秒）執行一次：interval => 'TRUNC(SYSDATE,''mi'') + 1/(24*60)'
每小時（的0分0秒）執行一次：interval => 'TRUNC(SYSDATE,''hh24'') + 1/24'
每隔 7 天執行一次：interval => 'TRUNC(SYSDATE) + 7 + 1/24'
每天凌晨兩點執行一次：interval => 'TRUNC(SYSDATE) + 1 + 2/24'
每周一凌晨 1 點執行一次：interval => 'TRUNC(NEXT_DAY(SYSDATE,''monday'')) + 1/24'
每月 5 號凌晨 1 點執行一次：interval => 'TRUNC(LAST_DAY(SYSDATE)) + 5 + 1/24'
每季度第一天凌晨 1 點執行一次：interval => 'TRUNC(ADD_MONTHS(SYSDATE,3),''Q'') + 1/24'
每年 1 月 1 號凌晨 1 點執行一次：interval => 'ADD_MONTHS(TRUNC(SYSDATE,''yyyy''),12) + 1/24'

序列

我接觸的兩個基於 Oracle 開發的項目都從不用序列，以至於我都沒有使用序列的實戰經驗。寫這個章節前我特意查了下序列的定義，我覺得我們可以把序列當成是數字工廠，因為它唯一的功能就是生產等間隔的數值。

用過 Oracle 的人應該都知道，Oracle 沒有提供類似於 SQL Server 或 MySQL 中自動增長列的功能，如果我們出於對性能或空間等其它因素的考慮，需要使用自動增長列，則可以通過序列來實現類似功能。

創建序列

創建序列的標準語法如下：

CREATE SEQUENCE seq_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n/NOMAXVALUE}] [{MINVALUE n/NOMINVALUE}] [{CYCLE/NOCYCLE}] [{CACHE n/NOCACHE}];

語法選項說明：

INCREMENT BY n：n 表示序列中連續兩個值之間的間隔，也稱作步長。如果 n 是正數則表示遞增，如果 n 是負數則表示遞減，預設是 1。
START WITH n：n 表示序列的起始值，即序列的第一個值，預設是 1，遞增時 n 是 minValue，遞減時 n 是 maxValue。
MAXVALUE n：n 表示序列的最大值，也可以選擇 NOMAXVALUE，即不設置最大值，預設是 999999999999999999999999999。
MINVALUE n：n表示序列的最小值，也可以選擇 NOMINVALUE，即不設置最小值，預設是 1。
CYCLE 和 NOCYCLE 分別表示當序列的值達到極限值後迴圈取值和不迴圈取值。
CACHE n：n 定義存放序列的記憶體塊的大小，預設為 20（個數字）。NOCACHE 表示不對序列進行記憶體緩衝。對序列進行記憶體緩衝，可以改善序列的性能。

使用序列 & 刪除序列

使用序列
- seq_name.CURRVAL：返回序列的當前值。
- seq_name.NEXTVAL：返回序列的下一個值。
以下情況之一不能使用序列：
- 1、在 DELETE、SELECT、UPDATE 的子查詢中。
- 2、在視圖或物化事物的查詢中。
- 3、SELECT 查詢中使用了 DISTINCT 操作符。
- 4、SELECT 查詢中有 GROUP BY 或 ORDER BY。

如果想查詢一下數據中到底有那些序列，語法如下：

SELECT * FROM USER_SEQUENCES;
SELECT * FROM ALL_SEQUENCES;
SELECT * FROM DBA_SEQUENCES;

刪除序列，示例：

DROP SEQUENCE seq_name;

連接

Oracle 中有個叫做 Database link 的東東，翻譯成中文應該是資料庫連接，為了稱呼方便，下文統一稱之為 dblink。在我跟公司一個技術專家對話時，對方提到“資料庫連接”，於是我上網查了下：dblink 是定義一個資料庫到另一個資料庫的路徑的對象，dblink 允許你查詢遠程表及執行遠程程式。

dblink 有兩種類型的，分別是公用的和私有的。一個公用資料庫鏈接對於資料庫中的所有用戶都是可用的，而一個私有鏈接僅對創建它的用戶可用。由一個用戶給另外一個用戶授權私有資料庫鏈接是不可能的，一個資料庫鏈接要麼是公用的，要麼是私有的。在任何分散式環境里，dblink 都是必要的。另外要註意的是 dblink 是單向的連接。

創建 dblink

創建 dblink 之前先得確定三件事，第一本地資料庫和遠程資料庫之間的網路是可以正常連接的，第二創建 dblink 的賬號必須得有 CREATE DATABASE LINK 或 CREATE PUBLIC DATABASE LINK 的許可權，第三用來登錄到遠程資料庫的帳號必須得有 CREATE SESSION 許可權。

實踐告訴我，創建 dblink 的正確語法如下：

CREATE [PUBLIC] DATABASE LINK dblink_name CONNECT TO user_name IDENTIFIED BY user_pwd USING 'connect_string';

其中 connect_string 有兩種寫法，示例：

-- 第一種寫法
CREATE PUBLIC DATABASE LINK dblink168 
CONNECT TO office 
IDENTIFIED BY 123456 
USING '192.168.1.168:1521/orcl';
-- 第二種寫法
CREATE PUBLIC DATABASE LINK dblink168 
CONNECT TO office 
IDENTIFIED BY 123456 
USING '(DESCRIPTION = (
        ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.168)(PORT = 1521))
    )(
        CONNECT_DATA = (SERVICE_NAME = orcl)
    )
)';

網上很多文章中給出的創建語法里都不包含指定密碼這一項，還說如果不指定，則使用當前的用戶名和口令登錄到遠程資料庫，我反覆實驗了好多次，都是直接報語法錯誤。不過我倒是發現了一個特點，指定密碼創建成功之後，再去查看 dblink 定義的 SQL，會發現密碼指令項不見了。我猜應該是 Oracle 做了特殊的加密處理，所以如果你要創建 dblink，不必擔心顯示指定密碼後別人會看到，造成安全泄漏。

使用 dblink & 刪除 dblink

使用 dblink

我覺得 dblink 真正的魅力之一便是使用方便，無論增刪改查那種語句，只需要在表名後面跟上 @dblink_name 就能操作遠程資料庫了。如要查詢 168 上用戶表中女員工的數量，示例：

SELECT COUNT(1) FROM t_staff@dblink168 t WHERE t.gender=0;

測試中我也發現一個小問題，假如我要查詢 168 上的伺服器時間，按理說寫法應該如下：

SELECT SYSDATE FROM DUAL@dblink168; -- 結果顯示出來的時間仍是本地資料庫的伺服器時間

如果為了命名更加統一，或者不想讓對方知道 dblink 的名字，也可以通過視圖或同義詞包裝一下，示例：

CREATE VIEW v_name AS SELECT * FROM table_name@dblink_name;
CREATE SYNONYM table_name FOR table_name@dblink_name;

跟 dblink 有關的幾個視圖，介紹如下：

SELECT * FROM dba_db_links; -- 查詢當前資料庫實例中所有 dblink
SELECT * FROM v$dblink;     -- 查詢當前資料庫示例中正在打開狀態的 dblink
SELECT * FROM user_sys_privs WHERE PRIVILEGE LIKE '%LINK%'; -- 查詢跟 dblink 有關的系統許可權