Oracle系列：觸發器、作業、序列、串連

標籤：別人   增刪改   觸發事件   address   顯示   ken   手冊   數字   months   

 

• 1、觸發器
• 2、作業
  • 2.1、作業調度功能和應用
  • 2.2、通過 DBMS_JOB 來調度作業
• 3、序列
  • 3.1、建立序列
  • 3.2、使用序列 & 刪除序列
• 4、串連
  • 4.1、建立串連
  • 4.2、使用串連 & 刪除串連
• 5、總結

1、觸發器

可以把觸發器看做是一種特殊的預存程序。觸發器也是一種定義存放在資料字典中的模式對象，和預存程序一樣，都包含聲明和執行的 PL/SQL 塊。觸發器和預存程序最大的不同在於調用方式，預存程序是由使用者或應用程式來調用和執行的，而觸發器由 Oracle 系統來調用的，當觸發事件發生時，觸發器即被觸發，然後隱式運行。

一般來說，在資料庫專案中得慎用觸發器，盡量少用，因為過度的使用觸發器會導致項目難以維護，用不好還會影響應用程式效能。因此本文只簡要介紹觸發器，並處給出建立觸發器的文法和一個樣本。

建立觸發器

文法：

CREATE OR REPLACE TRIGGER trigger_name[BEFORE | AFTER] [INSERT | DELETE | UPDATE ] ON table_name[FOR EACH ROW]DECLARE  -- 定義變數BEGIN  -- 觸發器作業碼END;

樣本：

CREATE OR REPLACE TRIGGER trg_aiAFTER INSERT ON demo.t_courseFOR EACH ROWDECLARE  v_today DATE;BEGIN  v_today := SYSDATE;  DBMS_OUTPUT.PUT_LINE(‘在 ‘||TO_CHAR(v_today,‘yyyy-mm-dd‘)||‘ 添加了《‘||:NEW.course_name||‘》課程‘);END;

測試：

INSERT INTO t_course(course_id,course_name,course_desc) VALUES(5,‘C/C++‘,‘電腦專業課程‘);

刪除觸發器

DROP TRIGGER trg_ai; -- 刪除名為 trg_ai 的觸發器

2、作業

特殊說明：在上一版中，我將本節取名為任務，主要是本人之前看過的資料中大多將作業調度稱之為任務，一開始我還納悶兒呢，SQL Server 中好像是叫作業啊，難道 Oracle 又製造了一個獨特而又不遵守業界規範的東西？後來看過官方手冊才知道，原來是被國內網友帶偏了！

我仔細想過這個中文名的問題，後來我覺得還是叫作業比較妥當一些。因為管理資料庫是要完成大量任務的，決定何時何處完成任務是需要計劃的，這些都是由 Oracle Scheduler 來調度和完成的，那麼由發送器建立的 JOB 叫任務或計劃都不太合理，因為容易讓人混淆，而作業似乎是一個比較貼切的稱呼。

2.1、作業調度功能和應用

一般關係型資料庫都提供了作業調度功能，Oracle 也提供了可用於 PL/SQL 的發送器。發送器使得資料庫管理員和應用程式開發人員能夠控制在資料庫環境中何時何處發生各種任務。這些任務可以是耗時且複雜的，例如備份或夜間資料倉儲載入和提取等，使有限的計算資源能夠在競爭的作業中適當分配。

作業發送器的最準系統是能夠安排作業在特定日期和時間或特定事件發生時運行。例如，需要讓補丁應用於正在生產的資料庫，為了盡量減少幹擾，此任務需要在非高峰時段進行，當然可以讓 IT人員在非高峰時段手動執行此任務；但為了降低運營成本，也可以使用作業發送器輕鬆完成。

Oracle 先後提供了DBMS_JOB和DBMS_SCHEDULER兩個作業發送器包，後者比前者功能更強大、定義更靈活、也增強了與系統的互動性。Oracle 也建議從DBMS_JOB切換到DBMS_SCHEDULER，但仍然支援DBMS_JOB以實現向後相容，且作業發送器套件共用相同的作業協調器。

可以在命令視窗輸入show parameter job_queue_processes查看資料庫中定時任務的最多並發數，一般設定為 10，如果設為 0，那麼資料庫定時作業是不會啟動並執行。更改並發數的方法是alter system set job_queue_processes=10。

可以通過USER_JOBS視圖來查詢目前使用者的作業調度情況。如下：

SELECT t.job jobno,t.what,to_char(t.next_date, ‘yyyy-mm-dd hh24:mi:ss‘) next_date,t.interval FROM USER_JOBS t;

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 日期的小時，分鐘和秒。

2.2、通過 DBMS_JOB 來調度作業

實際項目開發中，多數作業調度都會通過後台代碼來完成，只有個別情況下會使用資料庫中的作業調度功能。在 Oracle 10g 中，用DBMS_JOB來管理作業是比較方便的，接下來就詳細說說如何使用DBMS_JOB。

建立作業：

文法：

DBMS_JOB.SUBMIT(  job OUT BINARY_INTEGER,  what IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE,  [next_date] IN DATE,  [interval] IN VARCHAR2 DEFAULT ‘NULL‘,  [no_parse] IN BOOLEAN DEFAULT FALSE,  [instance] IN BINARY_INTEGER DEFAULT ANY_INSTANCE,  [force] IN BOOLEAN DEFAULT FALSE);

參數說明：

• job：作業編號，輸出參數，系統會自動分配，無法更改。
• what：作業要完成的操作，如調用過程等。可通過DBMS_JOB.WHAT(job, what)來修改。
• next_date：作業的下一次已耗用時間。可通過DBMS_JOB.NEXT_DATE(job,next_date)來修改。
• interval：作業啟動並執行時間間隔。可通過DBMS_JOB.INTERVAL(job,interval)來修改。

interval 參數設定案例：

• 每分鐘（的0秒）執行一次：TRUNC(SYSDATE,‘‘mi‘‘) + 1/(24*60)
• 每小時（的0分0秒）執行一次：TRUNC(SYSDATE,‘‘hh24‘‘) + 1/24
• 每隔 7 天執行一次：TRUNC(SYSDATE) + 7 + 1/24
• 每天淩晨兩點執行一次：TRUNC(SYSDATE) + 1 + 2/24
• 每周一淩晨 1 點執行一次：TRUNC(NEXT_DAY(SYSDATE,‘‘monday‘‘)) + 1/24
• 每月 5 號淩晨 1 點執行一次：TRUNC(LAST_DAY(SYSDATE)) + 5 + 1/24
• 每季度第一天淩晨 1 點執行一次：TRUNC(ADD_MONTHS(SYSDATE,3),‘‘Q‘‘) + 1/24
• 每年 1 月 1 號淩晨 1 點執行一次：ADD_MONTHS(TRUNC(SYSDATE,‘‘yyyy‘‘),12) + 1/24
• 不再運行該作業並刪除它：NULL
• 每星期六、星期日早上6點10分：TRUNC(LEAST(NEXT_DAY(SYSDATE, ‘‘SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY")))+(6×60+10)/(24×60)

樣本：

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

刪除作業：

文法：

DBMS_JOB.REMOVE(job);

啟用作業：

文法：

DBMS_JOB.RUN(job);

禁用作業：

文法：

DBMS_JOB.BROKEN(  job IN BINARY_INTEGER, -- 為 true 表示禁用任務，為 false 表示啟用任務  broken IN  BOOLEAN,  [next_date] IN DATE DEFAULT SYSDATE);

樣本：

BEGIN  DBMS_JOB.BROKEN(135,TRUE); -- 禁用 job 為 135 的任務  COMMIT;END;

3、序列

可以把序列當成是數字工廠，因為它唯一的功能就是生產等間隔的數值。Oracle 中序列最典型的用法是生產主鍵的值。有 Oracle 開發經驗的人應該都知道，Oracle 沒有提供類似於 SQL Server 或 MySQL 中自動成長列的功能。如果需要使用自動成長列，則可以通過序列來實作類別似功能。

我共參與過兩個基於 Oracle 開發的項目，但都不用 Oracle 的序列（一個用 C# 代碼產生序號，另一個直接用 GUID），以至於到目前為止我都還沒有使用序列的實戰經驗。下面只簡單介紹下我所瞭解的序列。

3.1、建立序列

建立序列的標準文法如下：

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 表示不對序列進行記憶體緩衝。將序列 CACHE（預先產生一部分序號，放入到記憶體中）到記憶體中，可以加速對序列的訪問。

3.2、使用序列 & 刪除序列

使用序列

SELECT seq_name.CURRVAL FROM DUAL; -- 獲得指定序列的當前值SELECT seq_name.NEXTVAL FROM DUAL; -- 獲得指定序列的下一個值

注意（有以下情況之一時不能使用序列）：

• 1、在 DELETE、SELECT、UPDATE 的子查詢中。
• 2、在視圖或物化事物的查詢中。
• 3、SELECT 查詢中使用了 DISTINCT 操作符。
• 4、SELECT 查詢中有 GROUP BY 或 ORDER BY。

如果想查詢一下資料中到底有那些序列，文法如下：

SELECT * FROM USER_SEQUENCES; -- 目前使用者所有序列

刪除序列，樣本：

DROP SEQUENCE seq_name;

4、串連

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

串連有兩種類型的，分別是公用的和私人的。一個公用資料庫連結對於資料庫中的所有使用者都是可用的，而一個私人連結僅對建立它的使用者可用。由一個使用者給另外一個使用者授權私人資料庫連結是不可能的，一個資料庫連結要麼是公用的，要麼是私人的。在任何分布式環境裡，串連都是必要的。另外要注意的是串連是單向的串連。

4.1、建立串連

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

實踐告訴我，建立串連的正確文法如下：

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)    ))‘;

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

4.2、使用串連 & 刪除串連

使用串連

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

SELECT COUNT(1) FROM [email protected] t WHERE t.gender=0;

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

SELECT SYSDATE FROM [email protected]; -- 結果顯示出來的時間仍是本機資料庫的伺服器時間

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

CREATE VIEW v_name AS SELECT * FROM [email protected]_name;CREATE SYNONYM table_name FOR [email protected]_name;

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

SELECT * FROM DBA_DB_LINKS; -- 查詢當前資料庫執行個體中所有 dblinkSELECT * FROM V$DBLINK;     -- 查詢當前資料庫樣本中正在開啟狀態的 dblinkSELECT * FROM USER_SYS_PRIVS t WHERE t.privilege LIKE ‘%LINK%‘; -- 查詢跟 dblink 有關的系統許可權

刪除串連

對於非 PUBLIC 類型的串連，只有 owner 自己才能刪除，非 PUBLIC 類型的串連沒有這個要求。刪除串連的樣本：

DROP [PUBLIC] DATABASE LINK dblink168;

5、總結

本文主要介紹了 Oracle 中相對不那麼常用的一些對象的 SQL 操作，但在實際做項目過程中，有時候它們又非常實用，所以有必要總結一下，以免用到的時候不記得文法，還得去查資料。

Oracle系列：觸發器、作業、序列、串連