oracle觸發(fā)器遞歸

一 觸發(fā)器介紹

專注于為中小企業(yè)提供成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)北京免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了近千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。

觸發(fā)器是一種特殊的存儲過程，它在插入，刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)庫本身標(biāo)準(zhǔn)的功能有更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。數(shù)據(jù)庫觸發(fā)器有以下的作用：

* 安全性。可以基于數(shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利。

# 可以基于時間限制用戶的操作，例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫數(shù)據(jù)。

# 可以基于數(shù)據(jù)庫中的數(shù)據(jù)限制用戶的操作，例如不允許股票的價格的升幅一次超過10%。

* 審計。可以跟蹤用戶對數(shù)據(jù)庫的操作。

# 審計用戶操作數(shù)據(jù)庫的語句。

# 把用戶對數(shù)據(jù)庫的更新寫入審計表。

* 實現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則。

# 實現(xiàn)非標(biāo)準(zhǔn)的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則更為復(fù)雜的限制。與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫對象。例如，觸發(fā)器可回退任何企圖吃進超過自己保證金的期貨。

# 提供可變的缺省值。

* 實現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫相關(guān)完整性規(guī)則。觸發(fā)器可以對數(shù)據(jù)庫中相關(guān)的表進行連環(huán)更新。例如，在auths表author_code列上的刪除觸發(fā)器可導(dǎo)致相應(yīng)刪除在其它表中的與之匹配的行。

# 在修改或刪除時級聯(lián)修改或刪除其它表中的與之匹配的行。

# 在修改或刪除時把其它表中的與之匹配的行設(shè)成NULL值。

# 在修改或刪除時把其它表中的與之匹配的行級聯(lián)設(shè)成缺省值。

# 觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試圖進行數(shù)據(jù)更新的事務(wù)。當(dāng)插入一個與其主健不匹配的外部鍵時，這種觸發(fā)器會起作用。例如，可以在 books.author_code列上生成一個插入觸發(fā)器，如果新值與auths.author_code列中的某值不匹配時，插入被回退。

* 同步實時地復(fù)制表中的數(shù)據(jù)。

* 自動計算數(shù)據(jù)值，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務(wù)人員發(fā)送警告數(shù)據(jù)。

ORACLE與SYBASE數(shù)據(jù)庫的觸發(fā)器有一定的區(qū)別，下面將分別講述這兩種數(shù)據(jù)庫觸發(fā)器的作用和寫法。

二 ORACLE 觸發(fā)器

ORACLE產(chǎn)生數(shù)據(jù)庫觸發(fā)器的語法為：

create [or replace] trigger 觸發(fā)器名 觸發(fā)時間 觸發(fā)事件

on 表名

[for each row]

pl/sql 語句

其中：

觸發(fā)器名：觸發(fā)器對象的名稱。由于觸發(fā)器是數(shù)據(jù)庫自動執(zhí)行的，因此該名稱只是一個名稱，沒有實質(zhì)的用途。

觸發(fā)時間：指明觸發(fā)器何時執(zhí)行，該值可取：

before---表示在數(shù)據(jù)庫動作之前觸發(fā)器執(zhí)行；

after---表示在數(shù)據(jù)庫動作之后出發(fā)器執(zhí)行。

觸發(fā)事件：指明哪些數(shù)據(jù)庫動作會觸發(fā)此觸發(fā)器：

insert：數(shù)據(jù)庫插入會觸發(fā)此觸發(fā)器；

update：數(shù)據(jù)庫修改會觸發(fā)此觸發(fā)器；

delete：數(shù)據(jù)庫刪除會觸發(fā)此觸發(fā)器。

表 名：數(shù)據(jù)庫觸發(fā)器所在的表。

for each row：對表的每一行觸發(fā)器執(zhí)行一次。如果沒有這一選項，則只對整個表執(zhí)行一次。

舉例：下面的觸發(fā)器在更新表auths之前觸發(fā)，目的是不允許在周末修改表：

create trigger auth_secure

before insert or update or delete //對整表更新前觸發(fā)

on auths

begin

if(to_char(sysdate,'DY')='SUN'

RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');

end if;

end

三 SYBASE數(shù)據(jù)庫觸發(fā)器

SYBASE數(shù)據(jù)庫觸發(fā)器的作用與ORACLE非常類似，僅有較小的差異。

SYBASE產(chǎn)生觸發(fā)器的語法為：

CREATE TRIGGER 觸發(fā)器名

ON 表名

FOR INSERT，UPDATE，DELETE

AS

SQL_statement |

FOR INSERT，UPDATE

AS

IF UPDATE(column_name) [AND|OR UPDATE(column_name)]...

SQL_statements

上面FOR子句用來指定在觸發(fā)器上的哪些數(shù)據(jù)更新命令可激活該觸發(fā)器。IF UPDATE子句檢查對指定列的操作類型，在IF UPDATE子句中可指定多個列。

與ORACLE不同，對于每條SQL語句，觸發(fā)器只執(zhí)行一次。觸發(fā)器在數(shù)據(jù)更新語句完成以后立即執(zhí)行。觸發(fā)器和啟動它的語句被當(dāng)作一個事務(wù)處理，事務(wù)可以在觸發(fā)器中回退。

下面舉例說明SYBASE觸發(fā)器的寫法。

create trigger forinsert_books

on books

for insert

as

if(select count(*) from auths,inserted

where auths.author_code=insert.author_code)!=@@rowcount

begin

rollback transaction

print "books 表中 author_code 列的值在auths 表中不存在。"

ORACLE遞歸

about connect by

SELECT empno, ename, job, mgr, deptno, LEVEL, sys_connect_by_path(ename,'\'), connect_by_root(ename) FROM emp START WITH mgr IS NULL CONNECT BY mgr =? PRIOR empno

WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必須把結(jié)構(gòu)寫出來

SELECT empno, ename, job, mgr, deptno? ---- 先寫錨點查詢，用START WITH的條件

,1 AS the_level? ? ---- 遞歸起點，第一層

,'\'||ename? ? ? ? ---- 路徑的第一截

,ename AS top_manager ---- 原來的CONNECT_BY_ROOT

FROM scott.EMP

WHERE mgr IS NULL ---- 原來的START WITH條件

UNION ALL? ---- 下面是遞歸部分

SELECT e.empno, e.ename, e.job, e.mgr, e.deptno? ---- 要加入的新一層數(shù)據(jù)，來自要遍歷的emp表

,1 + t.the_level? ? ? ? ? ? ---- 遞歸層次，在原來的基礎(chǔ)上加1。這相當(dāng)于CONNECT BY查詢中的LEVEL偽列

,t.path||'\'||e.ename? ? ? ? ---- 把新的一截路徑拼上去

,t.top_manager? ? ? ? ? ? ? ---- 直接繼承原來的數(shù)據(jù)，因為每個路徑的根節(jié)點只有一個

FROM t, scott.emp e? ? ? ? ? ? ? ? ? ? ---- 典型寫法，把子查詢本身和要遍歷的表作一個連接

WHERE t.empno = e.mgr? ? ? ? ? ? ---- 原來的CONNECT BY條件

) ---- WITH定義結(jié)束

SELECT * FROM T

EMPNO ENAME? ? ? JOB? ? ? ? MGR DEPTNO? THE_LEVEL PATH? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TOP_MANAGER

----- ---------- --------- ----- ------ ---------- -------------------------------------------------------------------------------- -----------

7839 KING? ? ? PRESIDENT? ? ? ? ? 10? ? ? ? ? 1 \KING? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7566 JONES? ? ? MANAGER? ? 7839? ? 20? ? ? ? ? 2 \KING\JONES? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7698 BLAKE? ? ? MANAGER? ? 7839? ? 30? ? ? ? ? 2 \KING\BLAKE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7782 CLARK? ? ? MANAGER? ? 7839? ? 10? ? ? ? ? 2 \KING\CLARK? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7999 MIKE? ? ? ANALYST? ? 7566? ? 30? ? ? ? ? 3 \KING\JONES\MIKE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7499 ALLEN? ? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\ALLEN? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7521 WARD? ? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\WARD? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7654 MARTIN? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\MARTIN? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7788 SCOTT? ? ? ANALYST? ? 7566? ? 20? ? ? ? ? 3 \KING\JONES\SCOTT? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7844 TURNER? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\TURNER? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7900 JAMES? ? ? CLERK? ? ? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\JAMES? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7902 FORD? ? ? ANALYST? ? 7566? ? 20? ? ? ? ? 3 \KING\JONES\FORD? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7934 MILLER? ? CLERK? ? ? 7782? ? 10? ? ? ? ? 3 \KING\CLARK\MILLER? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7369 SMITH? ? ? CLERK? ? ? 7902? ? 20? ? ? ? ? 4 \KING\JONES\FORD\SMITH? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7876 ADAMS? ? ? CLERK? ? ? 7788? ? 20? ? ? ? ? 4 \KING\JONES\SCOTT\ADAMS? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

oracle 存儲過程 遞歸實現(xiàn) 依賴分析

下面是用oracle數(shù)據(jù)庫解決不用start?with?來查詢子父數(shù)據(jù)查詢方法，里面主要用到了substr?和instr?函數(shù)（這兩個函數(shù)，其他數(shù)據(jù)庫也有相對應(yīng)的函數(shù)），游標(biāo)（其他數(shù)據(jù)庫也有游標(biāo)）。

-- 1 前提：創(chuàng)建表以及插入數(shù)據(jù)

CREATE TABLE TMP_TEST(MAIN_COLUMN VARCHAR2(10),PARENT_COLUMN VARCHAR2(10));

INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('A',NULL);

INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('B','A');

INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('C','A');

INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('D','A');

INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('E','B');

INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('F','C');

INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('G','E');

-- 2 創(chuàng)建存儲過程

CREATE OR REPLACE PROCEDURE GET_TREE(IS_PARENT?? IN NUMBER /** 子父查詢 **/,

SEARCH_ID?? IN VARCHAR2 /** 查詢條件節(jié)點 **/,

TREE_RESOUT OUT VARCHAR2 /** 輸出結(jié)果集合 **/)

AS

V_TEMP VARCHAR2(4000);

V_SEARCH VARCHAR2(4000);

V_INDEX INTEGER;

BEGIN

V_TEMP :=SEARCH_ID||'-';

TREE_RESOUT := '';

WHILE length(V_TEMP) 0 LOOP

V_INDEX := instr(V_TEMP,'-');

V_SEARCH := substr(V_TEMP,0,V_INDEX-1);

V_TEMP := substr(V_TEMP,V_INDEX+1);

/*DBMS_OUTPUT.put_line('V_INDEX:'|| V_INDEX ||'V_TEMP:' ||V_TEMP||'V_SEARCH:'|| V_SEARCH);*/

/** 查詢子節(jié)點 **/

if(IS_PARENT = 1) THEN

FOR C1 IN (SELECT * FROM TMP_TEST T1 WHERE T1.PARENT_COLUMN = V_SEARCH) LOOP

TREE_RESOUT := TREE_RESOUT || C1.MAIN_COLUMN;

V_TEMP := V_TEMP || C1.MAIN_COLUMN || '-';

END LOOP;

ELSE

/** 查詢父節(jié)點 **/

FOR C1 IN (SELECT * FROM TMP_TEST T1 WHERE T1.MAIN_COLUMN = V_SEARCH) LOOP

TREE_RESOUT := TREE_RESOUT || C1.PARENT_COLUMN;

V_TEMP := V_TEMP || C1.PARENT_COLUMN || '-';

END LOOP;

END IF;

END LOOP;

/*DBMS_OUTPUT.put_line('TREE_RESOUT:'||TREE_RESOUT);*/

END;

-- 3 調(diào)用存儲過程

declare

TREE_RESULT VARCHAR2(4000);

SEARCH_ID VARCHAR2(4000);

begin

get_tree(1,'A',TREE_RESULT);

dbms_output.put_line('查詢子節(jié)點:' || TREE_RESULT);

get_tree(0,'G',TREE_RESULT);

dbms_output.put_line('查詢父節(jié)點:' || TREE_RESULT);

end;

ORACLE select 遞歸查詢

START WITH 定義數(shù)據(jù)行查詢的初始起點；

CONNECT BY prior 定義表中的各個行是如何聯(lián)系的；

connect by 后面的"prior" 如果缺省，則只能查詢到符合條件的起始行，并不進行遞歸查詢；

條件2：col_1 = col_2，col_1是父鍵（它標(biāo)識父），col_2是子鍵（它標(biāo)識子）。

條件3過濾遞歸前相應(yīng)節(jié)點及其子節(jié)點，如果上級節(jié)點不滿足則下級節(jié)點自動過濾掉；

條件4過濾遞歸后相應(yīng)的節(jié)點或子節(jié)點，如果上級節(jié)點不滿足則下級結(jié)點自動提升一級。

系統(tǒng)偽列：

CURRVAL AND NEXTVAL 使用序列號的保留字

ROWID 記錄的唯一標(biāo)識

ROWNUM 限制查詢結(jié)果集的數(shù)量

LEVEL 顯示層次樹中特定行的層次或級別

CONNECT_BY_ROOT 返回當(dāng)前層的根節(jié)點(當(dāng)前行數(shù)據(jù)所對應(yīng)的最高等級節(jié)點的內(nèi)容)

SYS_CONNECT_BY_PATH(column, char) 函數(shù)實現(xiàn)將從父節(jié)點到當(dāng)前行內(nèi)容以"path"或者層次元素列表的形式顯示出來

CONNECT_BY_ISCYCLE 須帶參數(shù)NOCYCLE，當(dāng)前行中引用了某個父親節(jié)點的內(nèi)容并在樹中出現(xiàn)了循環(huán)，如果循環(huán)顯示"1"，否則就顯示"0"。

CONNECT_BY_ISLEAF 判斷當(dāng)前行是不是葉子。如果是葉子顯示"1"，如果不是葉子而是一個分支（例如當(dāng)前內(nèi)容是其他行的父親）就顯示"0"

而在 Oracle 10g 中，只要指定"NOCYCLE"就可以進行任意的查詢操作。與這個關(guān)鍵字相關(guān)的還有一個偽列——CONNECT_BY_ISCYCLE， 如果在當(dāng)前行中引用了某個父親節(jié)點的內(nèi)容并在樹中出現(xiàn)了循環(huán)，那么該行的偽列中就會顯示"1"，否則就顯示"0"。

【實例】

--創(chuàng)建測試表，增加測試數(shù)據(jù)

create table test(superid varchar2(20),id varchar2(20),mc varchar2(20));

insert into test values('0','1','A1');

insert into test values('0','2','A2');

insert into test values('1','11','A11');

insert into test values('1','12','A12');

insert into test values('2','21','A21');

insert into test values('2','22','A22');

insert into test values('11','111','A111');

insert into test values('11','112','A112');

insert into test values('12','121','A121');

insert into test values('12','122','A122');

insert into test values('21','211','A211');

insert into test values('21','212','A212');

insert into test values('22','221','A221');

insert into test values('22','222','A222');

commit;

--層次查詢示例

select level||'級' jc,lpad(' ',(level-1)*4)||id id,mc

from test

start with superid = '0' connect by prior id=superid;

select level||'級' jc,connect_by_isleaf mxf,lpad(' ',(level-1)*4)||id id,mc

from test

start with superid = '0' connect by prior id=superid;

--給出兩個以前在"數(shù)據(jù)庫字符串分組相加之四"中的例子來理解start with ... connect by ...

--功能：實現(xiàn)按照superid分組,把id用";"連接起來

--實現(xiàn)：以下兩個例子都是通過構(gòu)造2個偽列來實現(xiàn)connect by連接的。

Oracle遞歸查詢：使用prior實現(xiàn)樹操作

select * from tableName

start with ?條件A ? -- 開始遞歸的根節(jié)點,可多個條件

connect ?by prior ?條件B ?--?prior ?決定查詢的索引順序

where 條件 C

select t.empno,t.mgr,t.deptno ,level

from emp t

connect by prior t.empno=t.mgr

order by level,t.mgr,t.deptno;

找到empno為7369的所有領(lǐng)導(dǎo)。

select t.*,t.rowid from emp t

start with t.empno = 7369 ? ? ? --從empno為7369的開始查找

connect by prior t.mgr = t.empno ;? ? --上一條數(shù)據(jù)（這里就是empno為7369）的mgr == 當(dāng)前遍歷這一條數(shù)據(jù)的empno（那么就會找到empno為7902的用戶）

找到empno為7566的所有下屬

select t.*,t.rowid from emp t

start with t.empno = 7566

connect by prior t.empno = t.mgr ; --注意：connect by? t.mgr =prior t.empno與左邊寫法含義一樣

start with ：設(shè)置起點，省略后默認(rèn)以全部行為起點。

connect by [condition] ：與一般的條件一樣作用于當(dāng)前列，但是在滿足條件后，會以全部列作為下一層級遞歸（沒有其他條件的話）。

prior ： 表示上一層級的標(biāo)識符。經(jīng)常用來對下一層級的數(shù)據(jù)進行限制。不可以接偽列。

level ：偽列，表示當(dāng)前深度。

connect_by_root() ：顯示根節(jié)點列。經(jīng)常用來分組。

connect_by_isleaf ：1是葉子節(jié)點，0不是葉子節(jié)點。在制作樹狀表格時必用關(guān)鍵字。

sys_connect_by_path() ：將遞歸過程中的列進行拼接。

nocycle , connect_by_iscycle : 在有循環(huán)結(jié)構(gòu)的查詢中使用。

siblings : 保留樹狀結(jié)構(gòu)，對兄弟節(jié)點進行排序

;request_id=162538763316780265474850biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_v2~rank_v29-22-52652111.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187

;request_id=162538763316780269872688biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~baidu_landing_v2~default-5-108683534.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187

;request_id=162538763316780265474850biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_v2~rank_v29-10-105773226.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187