1.1 基本結(jié)構(gòu)

PL/SQL程序由三個(gè)塊組成漓库，即聲明部分恶座、執(zhí)行部分搬瑰、異常處理部分臣咖。

DECLARE 
   --聲明部分: 在此聲明PL/SQL用到的變量,類(lèi)型及游標(biāo)涩金，以及局部的存儲(chǔ)過(guò)程和函數(shù)
BEGIN
   -- 執(zhí)行部分:  過(guò)程及SQL 語(yǔ)句  , 即程序的主要部分
EXCEPTION
   -- 執(zhí)行異常部分: 錯(cuò)誤處理
END;

1.2 命名規(guī)則

1.3 記錄類(lèi)型

TYPE record_name IS RECORD(
   v1 data_type1  [NOT NULL]  [:= default_value ],
   v2 data_type2  [NOT NULL]  [:= default_value ],
   ......
   vn data_typen  [NOT NULL]  [:= default_value ] );

范例如下：

DECLARE
--定義與hr.employees表中的這幾個(gè)列相同的記錄數(shù)據(jù)類(lèi)型
   TYPE RECORD_TYPE_EMPLOYEES IS RECORD(
        f_name   hr.employees.first_name%TYPE,
        h_date   hr.employees.hire_date%TYPE,
        j_id     hr.employees.job_id%TYPE);
--聲明一個(gè)該記錄數(shù)據(jù)類(lèi)型的記錄變量
   v_emp_record RECORD_TYPE_EMPLOYEES;
 
BEGIN
   SELECT first_name, hire_date, job_id INTO v_emp_record
   FROM employees
   WHERE employee_id = &emp_id;
 
   DBMS_OUTPUT.PUT_LINE('雇員名稱(chēng)：'||v_emp_record.f_name
             ||'  雇傭日期：'||v_emp_record.h_date
             ||'  崗位：'||v_emp_record.j_id);
END;

1.4 數(shù)組類(lèi)型

TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];

范例

DECLARE
--定義一個(gè)最多保存5個(gè)VARCHAR(25)數(shù)據(jù)類(lèi)型成員的VARRAY數(shù)據(jù)類(lèi)型
   TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25);
--聲明一個(gè)該VARRAY數(shù)據(jù)類(lèi)型的變量
   v_reg_varray REG_VARRAY_TYPE;
 
BEGIN
--用構(gòu)造函數(shù)語(yǔ)法賦予初值
   v_reg_varray := reg_varray_type
         ('中國(guó)', '美國(guó)', '英國(guó)', '日本', '法國(guó)');
 
   DBMS_OUTPUT.PUT_LINE('地區(qū)名稱(chēng)：'||v_reg_varray(1)||'谱醇、'
                                    ||v_reg_varray(2)||'、'
                                    ||v_reg_varray(3)||'步做、'
                                    ||v_reg_varray(4));
   DBMS_OUTPUT.PUT_LINE('賦予初值NULL的第5個(gè)成員的值：'||v_reg_varray(5));
--用構(gòu)造函數(shù)語(yǔ)法賦予初值后就可以這樣對(duì)成員賦值
   v_reg_varray(5) := '法國(guó)';
   DBMS_OUTPUT.PUT_LINE('第5個(gè)成員的值：'||v_reg_varray(5));
END;

1.5 %TYPE

使用%TYPE定義一個(gè)變量副渴，其數(shù)據(jù)類(lèi)型與已經(jīng)定義的某個(gè)數(shù)據(jù)變量(尤其是表的某一列)的數(shù)據(jù)類(lèi)型相一致

DECLARE
   v_empno emp.empno%TYPE :=&no;
   Type t_record is record (
        v_name   emp.ename%TYPE,
        v_sa-   emp.sal%TYPE,
        v_date   emp.hiredate%TYPE);
   Rec t_record;
BEGIN
   SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno;
   DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date);
END;

1.6 %ROWTYPE

使用%ROWTYPE操作符, 返回一個(gè)記錄類(lèi)型, 其數(shù)據(jù)類(lèi)型和數(shù)據(jù)庫(kù)表的數(shù)據(jù)結(jié)構(gòu)相一致。

DECLARE
    v_empno emp.empno%TYPE :=&no;
    rec emp%ROWTYPE;
BEGIN
    SELECT * INTO rec FROM emp WHERE empno=v_empno;
    DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工資:'||rec.sal||'工作時(shí)間:'||rec.hiredate);
END;

1.7 TABLE

使用TABLE定義記錄表數(shù)據(jù)類(lèi)型全度。它與記錄類(lèi)型相似煮剧，它可以處理多行記錄，類(lèi)似于二維數(shù)組來(lái)模仿數(shù)據(jù)庫(kù)中的表将鸵。

TYPE table_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2];

范例如下

DECLARE
  TYPE dept_table_type IS TABLE OF
       dept%ROWTYPE INDEX BY BINARY_INTEGER;
  my_dname_table dept_table_type;
  v_count number(2) :=4;
BEGIN
  FOR int IN 1 .. v_count LOOP
    SELECT * INTO my_dname_table(int) FROM dept WHERE deptno=int*10;
  END LOOP;
  FOR int IN my_dname_table.FIRST .. my_dname_table.LAST LOOP
  DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(int).deptno);
  DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(int).dname);
  END LOOP;
END;

1.8 運(yùn)算符

-- 變量賦值
variable  := expression ;

-- 空值加數(shù)字仍是空值
NULL + <數(shù)字> = NULL 

-- 空值加（連接）字符，結(jié)果為字符
NULL || <字符串> = < 字符串> 

-- CHAR 轉(zhuǎn)換為 NUMBER
v_total := TO_NUMBER('100.0') + sal;

--  NUMBER 轉(zhuǎn)換為CHAR
v_comm := TO_CHAR('123.45') || '元' ;

-- 字符轉(zhuǎn)換為日期
v_date := TO_DATE('2001.07.03','yyyy.mm.dd');

-- 日期轉(zhuǎn)換為字符
v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;

1.9 注釋

• 使用雙 ‘-‘ ( 減號(hào)) 加注釋
• 使用 /* */ 來(lái)加一行或多行注釋

條件語(yǔ)句IF

IF <布爾表達(dá)式> THEN
  PL/SQL 和 SQL語(yǔ)句
END IF;
-----------------------
IF <布爾表達(dá)式> THEN
  PL/SQL 和 SQL語(yǔ)句
ELSE
  其它語(yǔ)句
END IF;
-----------------------
IF <布爾表達(dá)式> THEN
  PL/SQL 和 SQL語(yǔ)句
ELSIF < 其它布爾表達(dá)式> THEN
  其它語(yǔ)句
ELSIF < 其它布爾表達(dá)式> THEN
  其它語(yǔ)句
ELSE
  其它語(yǔ)句
END IF;

范例如下

DECLARE
    v_empno  employees.employee_id%TYPE :=&empno;
    V_salary employees.salary%TYPE;
    V_comment VARCHAR2(35);
BEGIN
   SELECT salary INTO v_salary FROM employees 
   WHERE employee_id = v_empno;
   IF v_salary < 1500 THEN
       V_comment:= '太少了,加點(diǎn)吧~!';
   ELSIF v_salary <3000 THEN
      V_comment:= '多了點(diǎn),少點(diǎn)吧~!';
   ELSE
      V_comment:= '沒(méi)有薪水~!';
   END IF;
   DBMS_OUTPUT.PUT_LINE(V_comment);
   exception
     when no_data_found then
        DBMS_OUTPUT.PUT_LINE('沒(méi)有數(shù)據(jù)~!');
     when others then
        DBMS_OUTPUT.PUT_LINE(sqlcode || '---' || sqlerrm);        
END;

2.1 條件語(yǔ)句CASE

CASE 條件表達(dá)式
  WHEN 條件表達(dá)式結(jié)果1 THEN 
     語(yǔ)句段1
  WHEN 條件表達(dá)式結(jié)果2 THEN
     語(yǔ)句段2
  ......
  WHEN 條件表達(dá)式結(jié)果n THEN
     語(yǔ)句段n
  [ELSE 條件表達(dá)式結(jié)果]
END;

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

CASE 
  WHEN 條件表達(dá)式1 THEN
     語(yǔ)句段1
  WHEN 條件表達(dá)式2 THEN
     語(yǔ)句段2
  ......
  WHEN 條件表達(dá)式n THEN 
     語(yǔ)句段n
  [ELSE 語(yǔ)句段]
END;

范例如下

DECLARE
  V_grade char(1) := UPPER('&p_grade');
  V_appraisal VARCHAR2(20);
BEGIN
  V_appraisal :=
  CASE v_grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Very Good'
    WHEN 'C' THEN 'Good'
    ELSE 'No such grade'
  END;
  DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||'  Appraisal: '|| v_appraisal);
END;

DECLARE
   v_first_name employees.first_name%TYPE;
   v_job_id employees.job_id%TYPE;
   v_salary employees.salary%TYPE;
   v_sal_raise NUMBER(3,2);
BEGIN
   SELECT first_name,   job_id,   salary INTO
          v_first_name, v_job_id, v_salary
   FROM employees WHERE employee_id = &emp_id;
   CASE
      WHEN v_job_id = 'PU_CLERK' THEN
         IF v_salary < 3000 THEN v_sal_raise := .08;
         ELSE v_sal_raise := .07;
         END IF;
      WHEN v_job_id = 'SH_CLERK' THEN
         IF v_salary < 4000 THEN v_sal_raise := .06;
         ELSE v_sal_raise := .05;
         END IF;
      WHEN v_job_id = 'ST_CLERK' THEN
         IF v_salary < 3500 THEN v_sal_raise := .04;
         ELSE v_sal_raise := .03;
         END IF;
      ELSE
         DBMS_OUTPUT.PUT_LINE('該崗位不漲工資: '||v_job_id);
   END CASE;
   DBMS_OUTPUT.PUT_LINE(v_first_name||'的崗位是'||v_job_id
                                    ||'一喘、的工資是'||v_salary
                                    ||'驱还、工資漲幅是'||v_sal_raise);
END;

2.2 循環(huán)語(yǔ)句LOOP

 LOOP
      要執(zhí)行的語(yǔ)句;
      EXIT WHEN <條件語(yǔ)句> --條件滿(mǎn)足，退出循環(huán)語(yǔ)句
  END LOOP;

范例

DECLARE
    int NUMBER(2) :=0;
BEGIN
   LOOP
      int := int + 1;
      DBMS_OUTPUT.PUT_LINE('int 的當(dāng)前值為:'||int);
      EXIT WHEN int =10;
   END LOOP;
END;

2.3 循環(huán)語(yǔ)句While

WHILE <布爾表達(dá)式> LOOP
    要執(zhí)行的語(yǔ)句;
END LOOP;

范例

DECLARE 
  x NUMBER :=1;
BEGIN
   WHILE x<=10 LOOP
      DBMS_OUTPUT.PUT_LINE('X的當(dāng)前值為:'||x);
       x:= x+1;
   END LOOP;
END;

2.4 循環(huán)語(yǔ)句For

[<<循環(huán)標(biāo)簽>>]
FOR 循環(huán)計(jì)數(shù)器 IN [ REVERSE ] 下限 .. 上限 LOOP
  要執(zhí)行的語(yǔ)句;
END LOOP [循環(huán)標(biāo)簽];

范例

CREATE TABLE temp_table(num_col NUMBER);

DECLARE
    V_counter NUMBER := 10;
BEGIN
   INSERT INTO temp_table(num_col) VALUES (v_counter );
   FOR v_counter IN 20 .. 25 LOOP
      INSERT INTO temp_table (num_col ) VALUES ( v_counter );
   END LOOP;
   INSERT INTO temp_table(num_col) VALUES (v_counter );
   FOR v_counter IN REVERSE 20 .. 25 LOOP
      INSERT INTO temp_table (num_col ) VALUES ( v_counter );
   END LOOP;
END ;

DROP TABLE temp_table;

3.1 顯式游標(biāo)

顯式游標(biāo)處理需四個(gè) PL/SQL步驟：

1. 定義/聲明游標(biāo)：就是定義一個(gè)游標(biāo)名凸克，以及與其相對(duì)應(yīng)的SELECT 語(yǔ)句

• 打開(kāi)游標(biāo)：就是執(zhí)行游標(biāo)所對(duì)應(yīng)的SELECT 語(yǔ)句议蟆，將其查詢(xún)結(jié)果放入工作區(qū)，并且指針指向工作區(qū)的首部萎战，標(biāo)識(shí)游標(biāo)結(jié)果集合咐容。如果游標(biāo)查詢(xún)語(yǔ)句中帶有FOR UPDATE選項(xiàng)，OPEN 語(yǔ)句還將鎖定數(shù)據(jù)庫(kù)表中游標(biāo)結(jié)果集合對(duì)應(yīng)的數(shù)據(jù)行
• 提取游標(biāo)數(shù)據(jù)：就是檢索結(jié)果集合中的數(shù)據(jù)行蚂维，放入指定的輸出變量中
• 關(guān)閉游標(biāo)：當(dāng)提取和處理完游標(biāo)結(jié)果集合數(shù)據(jù)后戳粒，應(yīng)及時(shí)關(guān)閉游標(biāo)路狮，以釋放該游標(biāo)所占用的系統(tǒng)資源，并使該游標(biāo)的工作區(qū)變成無(wú)效蔚约，不能再使用FETCH 語(yǔ)句取其中數(shù)據(jù)奄妨。關(guān)閉后的游標(biāo)可以使用OPEN 語(yǔ)句重新打開(kāi)

-- define cursor
CURSOR cursor_name[(parameter[, parameter]…)] 
    [RETURN datatype]
IS 
    select_statement;

-- open cursor
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

-- fetch cursor
FETCH cursor_name INTO {variable_list | record_variable };

--close cursor
CLOSE cursor_name;

范例

--ex1
DECLARE
   CURSOR c_cursor 
   IS SELECT first_name || last_name, Salary 
   FROM EMPLOYEES 
   WHERE rownum<11;   
   v_ename  EMPLOYEES.first_name%TYPE;
   v_sa-   EMPLOYEES.Salary%TYPE;   
BEGIN
  OPEN c_cursor;
  FETCH c_cursor INTO v_ename, v_sal;
  WHILE c_cursor%FOUND LOOP
     DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
     FETCH c_cursor INTO v_ename, v_sal;
  END LOOP;
  CLOSE c_cursor;
END;

-- ex2
DECLARE
  DeptRec    DEPARTMENTS%ROWTYPE;
  Dept_name  DEPARTMENTS.DEPARTMENT_NAME%TYPE;
  Dept_loc   DEPARTMENTS.LOCATION_ID%TYPE;
  CURSOR c1 IS 
  SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS 
  WHERE DEPARTMENT_ID <= 30;
  
  CURSOR c2(dept_no NUMBER DEFAULT 10) IS
    SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS 
    WHERE DEPARTMENT_ID <= dept_no;
  CURSOR c3(dept_no NUMBER DEFAULT 10) IS 
    SELECT * FROM DEPARTMENTS 
    WHERE DEPARTMENTS.DEPARTMENT_ID <=dept_no;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO dept_name, dept_loc;
    EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
    END LOOP;
    CLOSE c1;

    OPEN c2;
    LOOP
        FETCH c2 INTO dept_name, dept_loc;
        EXIT WHEN c2%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
    END LOOP;
    CLOSE c2;

    OPEN c3(dept_no =>20);
    LOOP
        FETCH c3 INTO deptrec;
        EXIT WHEN c3%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID);
    END LOOP;
    CLOSE c3;
END;

3.2 游標(biāo)屬性

• Cursor_name%FOUND：布爾型屬性，當(dāng)最近一次提取游標(biāo)操作FETCH成功則為 TRUE,否則為FALSE苹祟；
• Cursor_name%NOTFOUND：布爾型屬性砸抛，與%FOUND相反；
• Cursor_name%ISOPEN：布爾型屬性树枫，當(dāng)游標(biāo)已打開(kāi)時(shí)返回 TRUE直焙；
• Cursor_name%ROWCOUNT：數(shù)字型屬性，返回已從游標(biāo)中讀取的記錄數(shù)砂轻。

范例

-- 給工資低于1200 的員工增加工資50
DECLARE
   v_empno  EMPLOYEES.EMPLOYEE_ID%TYPE;
   v_sa-     EMPLOYEES.Salary%TYPE;
   CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES; 
BEGIN
   OPEN c_cursor;
   LOOP
      FETCH c_cursor INTO v_empno, v_sal;
      EXIT WHEN c_cursor%NOTFOUND; 
      IF v_sal<=1200 THEN
            UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno;
            DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'工資已更新!');
      END IF;
   DBMS_OUTPUT.PUT_LINE('記錄數(shù):'|| c_cursor %ROWCOUNT);
   END LOOP;
   CLOSE c_cursor;
END; 

-- 沒(méi)有參數(shù)且沒(méi)有返回值的游標(biāo)
DECLARE
   v_f_name employees.first_name%TYPE;
   v_j_id   employees.job_id%TYPE;
   CURSOR c1       --聲明游標(biāo),沒(méi)有參數(shù)沒(méi)有返回值
   IS
      SELECT first_name, job_id FROM employees 
      WHERE department_id = 20;
BEGIN
   OPEN c1;        --打開(kāi)游標(biāo)
   LOOP
      FETCH c1 INTO v_f_name, v_j_id;    --提取游標(biāo)
      IF c1%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_f_name||'的崗位是'||v_j_id);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已經(jīng)處理完結(jié)果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c1;   --關(guān)閉游標(biāo)
END;

-- 有參數(shù)且沒(méi)有返回值的游標(biāo)
DECLARE
   v_f_name employees.first_name%TYPE;
   v_h_date employees.hire_date%TYPE;
   CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --聲明游標(biāo),有參數(shù)沒(méi)有返回值
   IS
      SELECT first_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;
BEGIN
   OPEN c2(90, 'AD_VP');  --打開(kāi)游標(biāo),傳遞參數(shù)值
   LOOP
      FETCH c2 INTO v_f_name, v_h_date;    --提取游標(biāo)
      IF c2%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇傭日期是'||v_h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已經(jīng)處理完結(jié)果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c2;   --關(guān)閉游標(biāo)
END;

-- 有參數(shù)且有返回值的游標(biāo)
DECLARE
   TYPE emp_record_type IS RECORD(
        f_name   employees.first_name%TYPE,
        h_date   employees.hire_date%TYPE);
   v_emp_record EMP_RECORD_TYPE;

   CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --聲明游標(biāo),有參數(shù)有返回值
          RETURN EMP_RECORD_TYPE
   IS
      SELECT first_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;
BEGIN
   OPEN c3(j_id => 'AD_VP', dept_id => 90);  --打開(kāi)游標(biāo),傳遞參數(shù)值
   LOOP
      FETCH c3 INTO v_emp_record;    --提取游標(biāo)
      IF c3%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇傭日期是'
                            ||v_emp_record.h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已經(jīng)處理完結(jié)果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c3;   --關(guān)閉游標(biāo)
END;

-- 基于游標(biāo)定義記錄變量
DECLARE
   CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --聲明游標(biāo),有參數(shù)沒(méi)有返回值
   IS
      SELECT first_name f_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;
    --基于游標(biāo)定義記錄變量奔誓，比聲明記錄類(lèi)型變量要方便，不容易出錯(cuò)
    v_emp_record c4%ROWTYPE;
BEGIN
   OPEN c4(90, 'AD_VP');  --打開(kāi)游標(biāo),傳遞參數(shù)值
   LOOP
      FETCH c4 INTO v_emp_record;    --提取游標(biāo)
      IF c4%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇傭日期是'
                            ||v_emp_record.hire_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已經(jīng)處理完結(jié)果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c4;   --關(guān)閉游標(biāo)
END;

3.3 游標(biāo)FOR循環(huán)

游標(biāo)FOR循環(huán)語(yǔ)句舔清，自動(dòng)執(zhí)行游標(biāo)的OPEN丝里、FETCH、CLOSE語(yǔ)句和循環(huán)語(yǔ)句的功能体谒；當(dāng)進(jìn)入循環(huán)時(shí)杯聚，游標(biāo)FOR循環(huán)語(yǔ)句自動(dòng)打開(kāi)游標(biāo)，并提取第一行游標(biāo)數(shù)據(jù)抒痒，當(dāng)程序處理完當(dāng)前所提取的數(shù)據(jù)而進(jìn)入下一次循環(huán)時(shí)幌绍，游標(biāo)FOR循環(huán)語(yǔ)句自動(dòng)提取下一行數(shù)據(jù)供程序處理，當(dāng)提取完結(jié)果集合中的所有數(shù)據(jù)行后結(jié)束循環(huán)故响，并自動(dòng)關(guān)閉游標(biāo)傀广。

FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 游標(biāo)數(shù)據(jù)處理代碼
END LOOP;

index_variable為游標(biāo)FOR 循環(huán)語(yǔ)句隱含聲明的索引變量，該變量為記錄變量彩届，其結(jié)構(gòu)與游標(biāo)查詢(xún)語(yǔ)句返回的結(jié)構(gòu)集合的結(jié)構(gòu)相同伪冰。在程序中可以通過(guò)引用該索引記錄變量元素來(lái)讀取所提取的游標(biāo)數(shù)據(jù)，index_variable中各元素的名稱(chēng)與游標(biāo)查詢(xún)語(yǔ)句選擇列表中所制定的列名相同樟蠕。如果在游標(biāo)查詢(xún)語(yǔ)句的選擇列表中存在計(jì)算列贮聂，則必須為這些計(jì)算列指定別名后才能通過(guò)游標(biāo)FOR 循環(huán)語(yǔ)句中的索引變量來(lái)訪問(wèn)這些列數(shù)據(jù)。

范例

-- ex1
DECLARE
   CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary
   FROM employees ;
BEGIN
   --隱含打開(kāi)游標(biāo)
   FOR v_sal IN c_sal LOOP
   --隱含執(zhí)行一個(gè)FETCH語(yǔ)句
      DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ;
   --隱含監(jiān)測(cè)c_sal%NOTFOUND
   END LOOP;
--隱含關(guān)閉游標(biāo)
END;

-- ex2 當(dāng)所聲明的游標(biāo)帶有參數(shù)時(shí)寨辩，通過(guò)游標(biāo)FOR 循環(huán)語(yǔ)句為游標(biāo)傳遞參數(shù)
DECLARE
  CURSOR c_cursor(dept_no NUMBER DEFAULT 10) 
  IS
    SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
BEGIN
    DBMS_OUTPUT.PUT_LINE('當(dāng)dept_no參數(shù)值為30：');
    FOR c1_rec IN c_cursor(30) LOOP        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默認(rèn)的dept_no參數(shù)值10：');
    FOR c1_rec IN c_cursor LOOP        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
    END LOOP;
END;

不要在程序中對(duì)游標(biāo)進(jìn)行人工操作吓懈；不要在程序中定義用于控制FOR循環(huán)的記錄

3.4 處理隱式游標(biāo)

對(duì)于非查詢(xún)語(yǔ)句，如修改靡狞、刪除操作耻警，則由ORACLE 系統(tǒng)自動(dòng)地為這些操作設(shè)置游標(biāo)并創(chuàng)建其工作區(qū)，這些由系統(tǒng)隱含創(chuàng)建的游標(biāo)稱(chēng)為隱式游標(biāo)，隱式游標(biāo)的名字為SQL甘穿，這是由ORACLE 系統(tǒng)定義的腮恩。對(duì)于隱式游標(biāo)的操作，如定義扒磁、打開(kāi)庆揪、取值及關(guān)閉操作，都由ORACLE 系統(tǒng)自動(dòng)地完成妨托，無(wú)需用戶(hù)進(jìn)行處理。用戶(hù)只能通過(guò)隱式游標(biāo)的相關(guān)屬性吝羞，來(lái)完成相應(yīng)的操作兰伤。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶(hù)自定義的顯示游標(biāo)無(wú)關(guān)的钧排、最新處理的一條SQL 語(yǔ)句所包含的數(shù)據(jù)敦腔。

格式調(diào)用為： SQL%

范例

--  刪除EMPLOYEES表中某部門(mén)的所有員工，如果該部門(mén)中已沒(méi)有員工恨溜，則在DEPARTMENT表中刪除該部門(mén)
DECLARE
    V_deptno department_id%TYPE :=&p_deptno;
BEGIN
    DELETE FROM employees WHERE department_id=v_deptno;
    IF SQL%NOTFOUND THEN
        DELETE FROM departments WHERE department_id=v_deptno;
    END IF;
END;

-- 通過(guò)隱式游標(biāo)SQL的%ROWCOUNT屬性來(lái)了解修改了多少行
DECLARE
   v_rows NUMBER;
BEGIN
--更新數(shù)據(jù)
   UPDATE employees SET salary = 30000
   WHERE department_id = 90 AND job_id = 'AD_VP';
--獲取默認(rèn)游標(biāo)的屬性值
   v_rows := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'個(gè)雇員的工資');
--回退更新符衔，以便使數(shù)據(jù)庫(kù)的數(shù)據(jù)保持原樣
   ROLLBACK;
END;

3.5 NO_DATA_FOUND 和 %NOTFOUND的區(qū)別

• SELECT … INTO 語(yǔ)句觸發(fā) NO_DATA_FOUND；
• 當(dāng)一個(gè)顯式游標(biāo)的WHERE子句未找到時(shí)觸發(fā)%NOTFOUND糟袁；
• 當(dāng)UPDATE或DELETE 語(yǔ)句的WHERE 子句未找到時(shí)觸發(fā) SQL%NOTFOUND判族；
• 在提取循環(huán)中要用 %NOTFOUND 或%FOUND來(lái)確定循環(huán)的退出條件，不要用 NO_DATA_FOUND

3.6 使用游標(biāo)更新和刪除數(shù)據(jù)

為了對(duì)正在處理(查詢(xún))的行不被另外的用戶(hù)改動(dòng)项戴，ORACLE 提供一個(gè) FOR UPDATE 子句來(lái)對(duì)所選擇的行進(jìn)行鎖住形帮。該需求迫使ORACLE鎖定游標(biāo)結(jié)果集合的行，可以防止其他事務(wù)處理更新或刪除相同的行周叮，直到您的事務(wù)處理提交或回退為止辩撑。

SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]

范例

-- 從EMPLOYEES表中查詢(xún)某部門(mén)的員工情況，將其工資最低定為 1500仿耽；
DECLARE 
    V_deptno employees.department_id%TYPE :=&p_deptno;
    CURSOR emp_cursor 
  IS 
  SELECT employees.employee_id, employees.salary 
    FROM employees WHERE employees.department_id=v_deptno
  FOR UPDATE NOWAIT;
BEGIN
    FOR emp_record IN emp_cursor LOOP
    IF emp_record.salary < 1500 THEN
        UPDATE employees SET salary=1500
    WHERE CURRENT OF emp_cursor;
    END IF;
    END LOOP;
--    COMMIT;
END; 
 
-- 將EMPLOYEES表中部門(mén)編碼為90合冀、崗位為AD_VP的雇員的工資都更新為2000元；
DECLARE
   v_emp_record employees%ROWTYPE;
   CURSOR c1
   IS
      SELECT * FROM employees FOR UPDATE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO v_emp_record;
      EXIT WHEN c1%NOTFOUND;
      IF v_emp_record.department_id = 90 AND
         v_emp_record.job_id = 'AD_VP'
      THEN
         UPDATE employees SET salary = 20000
         WHERE CURRENT OF c1;  --更新當(dāng)前游標(biāo)行對(duì)應(yīng)的數(shù)據(jù)行
      END IF;
   END LOOP;
   COMMIT;   --提交已經(jīng)修改的數(shù)據(jù)
   CLOSE c1;
END;

3.7 游標(biāo)變量

游標(biāo)變量操作也包括打開(kāi)项贺、提取和關(guān)閉三個(gè)步驟君躺。

1． 打開(kāi)游標(biāo)變量
打開(kāi)游標(biāo)變量時(shí)使用的是OPEN…FOR 語(yǔ)句。格式為：

OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;

cursor_variable_name 為游標(biāo)變量敬扛，host_cursor_variable_name 為PL/SQL主機(jī)環(huán)境（如OCI: ORACLE Call Interface晰洒，Pro*c 程序等）中聲明的游標(biāo)變量。
OPEN…FOR 語(yǔ)句可以在關(guān)閉當(dāng)前的游標(biāo)變量之前重新打開(kāi)游標(biāo)變量啥箭，而不會(huì)導(dǎo)致 CURSOR_ALREAD_OPEN 異常錯(cuò)誤谍珊。新打開(kāi)游標(biāo)變量時(shí)，前一個(gè)查詢(xún)的內(nèi)存處理區(qū)將被釋放急侥。

2 . 提取游標(biāo)變量數(shù)據(jù)
使用FETCH語(yǔ)句提取游標(biāo)變量結(jié)果集合中的數(shù)據(jù)砌滞。格式為：

FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};

cursor_variable_name 和 host_cursor_variable_name 分別為游標(biāo)變量和宿主游標(biāo)變量名稱(chēng)侮邀； variable 和 record_variable 分別為普通變量和記錄變量名稱(chēng)。

3． 關(guān)閉游標(biāo)變量
CLOSE語(yǔ)句關(guān)閉游標(biāo)變量贝润，格式為：

CLOSE {cursor_variable_name | :host_cursor_variable_name}

cursor_variable_name 和 host_cursor_variable_name 分別為游標(biāo)變量和宿主游標(biāo)變量名稱(chēng)绊茧，如果應(yīng)用程序試圖關(guān)閉一個(gè)未打開(kāi)的游標(biāo)變量，則將導(dǎo)致 INVALID_CURSOR 異常錯(cuò)誤

-- 強(qiáng)類(lèi)型參照游標(biāo)變量類(lèi)型
DECLARE
    TYPE emp_job_rec IS RECORD(
        Employee_id employees.employee_id%TYPE,
        Employee_name employees.first_name%TYPE,
        Job_title employees.job_id%TYPE
    );
    TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;
    Emp_refcur emp_job_refcur_type ;
    Emp_job emp_job_rec;
BEGIN
    OPEN emp_refcur FOR 
    SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id 
  FROM employees 
  ORDER BY employees.department_id;
  
    FETCH emp_refcur INTO emp_job;
    WHILE emp_refcur%FOUND LOOP
       DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_title);
    FETCH emp_refcur INTO emp_job;
    END LOOP;
END;

-- 弱類(lèi)型參照游標(biāo)變量類(lèi)型
DECLARE
    Type refcur_t IS REF CURSOR;
    Refcur refcur_t;
    TYPE sample_rec_type IS RECORD (
        Id number,
        Description VARCHAR2 (30)
    );
    sample sample_rec_type;
    selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
BEGIN
    IF selection='D' THEN
        OPEN refcur FOR 
    SELECT departments.department_id, departments.department_name FROM departments;
        DBMS_OUTPUT.PUT_LINE('Department data');
    ELSIF selection='E' THEN
        OPEN refcur FOR 
    SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees;
        DBMS_OUTPUT.PUT_LINE('Employee data');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E''');
        RETURN;
    END IF;
    DBMS_OUTPUT.PUT_LINE('----------------------');
    FETCH refcur INTO sample;
    WHILE refcur%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description);
        FETCH refcur INTO sample;
    END LOOP;
    CLOSE refcur;
END;

-- 使用游標(biāo)變量（沒(méi)有RETURN子句）
DECLARE
--定義一個(gè)游標(biāo)數(shù)據(jù)類(lèi)型
   TYPE emp_cursor_type IS REF CURSOR;
--聲明一個(gè)游標(biāo)變量
   c1 EMP_CURSOR_TYPE;
--聲明兩個(gè)記錄變量
   v_emp_record employees%ROWTYPE;
   v_reg_record regions%ROWTYPE;

BEGIN
   OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
   LOOP
      FETCH c1 INTO v_emp_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇傭日期是'
                            ||v_emp_record.hire_date);
   END LOOP;
--將同一個(gè)游標(biāo)變量對(duì)應(yīng)到另一個(gè)SELECT語(yǔ)句
   OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1打掘，2）;
   LOOP
      FETCH c1 INTO v_reg_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'
                            ||v_reg_record.region_name);
   END LOOP;
   CLOSE c1;
END;

-- 使用游標(biāo)變量（有RETURN子句）
DECLARE
--定義一個(gè)與employees表中的這幾個(gè)列相同的記錄數(shù)據(jù)類(lèi)型
   TYPE emp_record_type IS RECORD(
        f_name   employees.first_name%TYPE,
        h_date   employees.hire_date%TYPE,
        j_id     employees.job_id%TYPE);
--聲明一個(gè)該記錄數(shù)據(jù)類(lèi)型的記錄變量
   v_emp_record EMP_RECORD_TYPE;
--定義一個(gè)游標(biāo)數(shù)據(jù)類(lèi)型
   TYPE emp_cursor_type IS REF CURSOR
        RETURN EMP_RECORD_TYPE;
--聲明一個(gè)游標(biāo)變量
   c1 EMP_CURSOR_TYPE;
BEGIN
   OPEN c1 FOR SELECT first_name, hire_date, job_id
               FROM employees WHERE department_id = 20;
   LOOP
      FETCH c1 INTO v_emp_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('雇員名稱(chēng)：'||v_emp_record.f_name
                ||'  雇傭日期：'||v_emp_record.h_date
                ||'  崗位：'||v_emp_record.j_id);
   END LOOP;
   CLOSE c1;
END;

4.1 異常處理概念

有三種類(lèi)型的異常錯(cuò)誤：

1．預(yù)定義錯(cuò)誤：ORACLE預(yù)定義的異常情況大約有24個(gè)华畏。對(duì)這種異常情況的處理，無(wú)需在程序中定義尊蚁，由ORACLE自動(dòng)將其引發(fā)亡笑。
2． 非預(yù)定義錯(cuò)誤：即其他標(biāo)準(zhǔn)的ORACLE錯(cuò)誤。對(duì)這種異常情況的處理横朋，需要用戶(hù)在程序中定義仑乌，然后由ORACLE自動(dòng)將其引發(fā)。
3． 用戶(hù)定義錯(cuò)誤：程序執(zhí)行過(guò)程中琴锭，出現(xiàn)編程人員認(rèn)為的非正常情況晰甚。對(duì)這種異常情況的處理，需要用戶(hù)在程序中定義决帖，然后顯式地在程序中將其引發(fā)厕九。

EXCEPTION
   WHEN first_exception THEN  <code to handle first exception >
   WHEN second_exception THEN  <code to handle second exception >
   WHEN OTHERS THEN  <code to handle others exception >
END;

4.2 預(yù)定義的異常處理

只需在PL/SQL塊的異常處理部分，直接引用相應(yīng)的異常情況名古瓤，并對(duì)其完成相應(yīng)的異常錯(cuò)誤處理即可止剖。

-- 更新指定員工工資，如工資小于1500落君，則加100穿香；
DECLARE
   v_empno employees.employee_id%TYPE := &empno;
   v_sa-  employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno;
   IF v_sal<=1500 THEN 
        UPDATE employees SET salary = salary + 100 WHERE employee_id=v_empno; 
        DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已更新!');     
   ELSE
        DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已經(jīng)超過(guò)規(guī)定值!');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
      DBMS_OUTPUT.PUT_LINE('數(shù)據(jù)庫(kù)中沒(méi)有編碼為'||v_empno||'的員工');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('程序運(yùn)行錯(cuò)誤!請(qǐng)使用游標(biāo)');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END; 

4.3 非預(yù)定義的異常處理

必須對(duì)非定義的ORACLE錯(cuò)誤進(jìn)行定義。步驟如下：

1 . 定義異常情況 <異常情況> EXCEPTION;
2 . 將定義異常情況绎速，與標(biāo)準(zhǔn)的ORACLE錯(cuò)誤聯(lián)系起來(lái)： PRAGMA EXCEPTION_INIT(<異常情況>, <錯(cuò)誤代碼>);
3 . 在異常情況處理部分對(duì)異常情況做出相應(yīng)的處理皮获。

-- 刪除指定部門(mén)的記錄信息，以確保該部門(mén)沒(méi)有員工纹冤。
INSERT INTO departments VALUES(50, 'FINANCE', 'CHICAGO');

DECLARE
   v_deptno departments.department_id%TYPE := &deptno;
   deptno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);
   /* -2292 是違反一致性約束的錯(cuò)誤代碼 */
BEGIN
   DELETE FROM departments WHERE department_id = v_deptno;
EXCEPTION
   WHEN deptno_remaining THEN 
      DBMS_OUTPUT.PUT_LINE('違反數(shù)據(jù)完整性約束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

4.4 用戶(hù)自定義的異常處理

用戶(hù)定義的異常錯(cuò)誤是通過(guò)顯式使用 RAISE 語(yǔ)句來(lái)觸發(fā)洒宝。當(dāng)引發(fā)一個(gè)異常錯(cuò)誤時(shí)，控制就轉(zhuǎn)向到 EXCEPTION塊異常錯(cuò)誤部分萌京，執(zhí)行錯(cuò)誤處理代碼雁歌。對(duì)于這類(lèi)異常情況的處理，步驟如下：

1 . 定義異常情況 <異常情況> EXCEPTION;
2 . RAISE <異常情況>知残；
3 . 在異常情況處理部分對(duì)異常情況做出相應(yīng)的處理

-- 更新指定員工工資靠瞎，增加100；
DECLARE
   v_empno employees.employee_id%TYPE :=&empno;
   no_result  EXCEPTION;
BEGIN
   UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
EXCEPTION
   WHEN no_result THEN 
      DBMS_OUTPUT.PUT_LINE('你的數(shù)據(jù)更新語(yǔ)句失敗了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

4.5 自定義異常代碼

調(diào)用DBMS_STANDARD(ORACLE提供的包)包所定義的RAISE_APPLICATION_ERROR過(guò)程，可以重新定義異常錯(cuò)誤消息乏盐，它為應(yīng)用程序提供了一種與ORACLE交互的方法佳窑。
RAISE_APPLICATION_ERROR 的語(yǔ)法如下：

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );

這里的 error_number 是從 –20,000 到 –20,999 之間的參數(shù)，
error_message 是相應(yīng)的提示信息(< 2048 字節(jié))父能，
keep_errors 為可選神凑，如果 keep_errors =TRUE ,則新錯(cuò)誤將被添加到已經(jīng)引發(fā)的錯(cuò)誤列表中罕模。如果 keep_errors=FALSE(缺省), 則新錯(cuò)誤將替換當(dāng)前的錯(cuò)誤列表窖铡。

-- 創(chuàng)建一個(gè)函數(shù)get_salary, 該函數(shù)檢索指定部門(mén)的工資總和，其中定義了-20991和-20992號(hào)錯(cuò)誤烈菌，分別處理參數(shù)為空和非法部門(mén)代碼兩種錯(cuò)誤：
 
CREATE TABLE errlog(
  Errcode NUMBER,
  Errtext CHAR(40));

CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER)
RETURN NUMBER 
AS
  v_sal NUMBER;
BEGIN
  IF p_deptno IS NULL THEN
    RAISE_APPLICATION_ERROR(-20991, ’部門(mén)代碼為空’);
  ELSIF p_deptno<0 THEN
    RAISE_APPLICATION_ERROR(-20992, ’無(wú)效的部門(mén)代碼’);
  ELSE
    SELECT SUM(employees.salary) INTO v_sal FROM employees 
    WHERE employees.department_id=p_deptno;
    RETURN v_sal;
  END IF;
END;

DECLARE 
  V_salary NUMBER(7,2);
  V_sqlcode NUMBER;
  V_sqlerr VARCHAR2(512);
  Null_deptno EXCEPTION;
  Invalid_deptno EXCEPTION;
  PRAGMA EXCEPTION_INIT(null_deptno,-20991);
  PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);
BEGIN
  V_salary :=get_salary(10);
  DBMS_OUTPUT.PUT_LINE('10號(hào)部門(mén)工資：' || TO_CHAR(V_salary));

  BEGIN
    V_salary :=get_salary(-10);
  EXCEPTION
    WHEN invalid_deptno THEN
      V_sqlcode :=SQLCODE;
      V_sqlerr  :=SQLERRM;
      INSERT INTO errlog(errcode, errtext) 
      VALUES(v_sqlcode, v_sqlerr);
      COMMIT;
  END inner1;

  V_salary :=get_salary(20);
  DBMS_OUTPUT.PUT_LINE('部門(mén)號(hào)為20的工資為：'||TO_CHAR(V_salary));

  BEGIN
    V_salary :=get_salary(NULL);
  END inner2;

  V_salary := get_salary(30);
  DBMS_OUTPUT.PUT_LINE('部門(mén)號(hào)為30的工資為：'||TO_CHAR(V_salary));

  EXCEPTION
    WHEN null_deptno THEN
      V_sqlcode :=SQLCODE;
      V_sqlerr  :=SQLERRM;
      INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);
      COMMIT;
    WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END outer;


-- 定義觸發(fā)器岔霸，使用RAISE_APPLICATION_ERROR阻止沒(méi)有員工姓名的新員式記錄插入：
CREATE OR REPLACE TRIGGER tr_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :new.first_name IS NULL OR :new.last_name is null THEN
    RAISE_APPLICATION_ERROR(-20000,'Employee must have a name.');
  END IF;
END;

4.6 異常錯(cuò)誤處理編程

由于ORACLE 的錯(cuò)信息最大長(zhǎng)度是512字節(jié)薛躬，為了得到完整的錯(cuò)誤提示信息，我們可用 SQLERRM和 SUBSTR 函數(shù)一起得到錯(cuò)誤提示信息呆细，方便進(jìn)行錯(cuò)誤，特別是如果WHEN OTHERS異常處理器時(shí)更為方便八匠。

SQLCODE  返回遇到的Oracle錯(cuò)誤號(hào),
SQLERRM  返回遇到的Oracle錯(cuò)誤信息.
 
如:  SQLCODE=-100   è SQLERRM=’no_data_found ‘
 SQLCODE=0      è SQLERRM=’normal, successfual completion’

范例

-- 將ORACLE錯(cuò)誤代碼及其信息存入錯(cuò)誤代碼表
CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100));

DECLARE
   err_msg  VARCHAR2(100);
BEGIN
   /*  得到所有 ORACLE 錯(cuò)誤信息  */
   FOR err_num IN -100 .. 0 LOOP
      err_msg := SQLERRM(err_num);
      INSERT INTO errors VALUES(err_num, err_msg);
   END LOOP;
END;
DROP TABLE errors;

 
 
-- 查詢(xún)ORACLE錯(cuò)誤代碼絮爷；
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(2222, 'Eric','Hu', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入數(shù)據(jù)記錄成功!');
   
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(2222, '胡','勇', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入數(shù)據(jù)記錄成功!');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

 
-- 利用ORACLE錯(cuò)誤代碼，編寫(xiě)異常錯(cuò)誤處理代碼梨树；
DECLARE
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
   /* -1 是違反唯一約束條件的錯(cuò)誤代碼 */
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, 'Eric','Hu', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入數(shù)據(jù)記錄成功!');
   
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, '胡','勇',SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入數(shù)據(jù)記錄成功!');
EXCEPTION
   WHEN empno_remaining THEN 
      DBMS_OUTPUT.PUT_LINE('違反數(shù)據(jù)完整性約束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

5.1 過(guò)程與函數(shù)

過(guò)程和函數(shù)統(tǒng)稱(chēng)為PL/SQL子程序坑夯，他們是被命名的PL/SQL塊，均存儲(chǔ)在數(shù)據(jù)庫(kù)中抡四，并通過(guò)輸入柜蜈、輸出參數(shù)或輸入/輸出參數(shù)與其調(diào)用者交換信息。過(guò)程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回?cái)?shù)據(jù)指巡，而過(guò)程則不返回?cái)?shù)據(jù)淑履。

5.2 創(chuàng)建函數(shù)

CREATE [OR REPLACE] FUNCTION function_name
 (arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1],
 [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],
 ......
 [argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])
 [ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
 IS | AS
    <類(lèi)型.變量的聲明部分>
BEGIN
    執(zhí)行部分
    RETURN expression
EXCEPTION
    異常處理部分
END function_name; 　　

IN, OUT, IN OUT是形參的模式。若省略藻雪，則為 IN 模式秘噪。 IN 模式的形參只能將實(shí)參傳遞給形參，進(jìn)入函數(shù)內(nèi)部勉耀，但只能讀不能寫(xiě)指煎，函數(shù)返回時(shí)實(shí)參的值不變。 OUT 模式的形參會(huì)忽略調(diào)用時(shí)的實(shí)參值（或說(shuō)該形參的初始值總是 NULL）便斥，但在函數(shù)內(nèi)部可以被讀或?qū)懼寥溃瘮?shù)返回時(shí)形參的值會(huì)賦予給實(shí)參。 IN OUT 具有前兩種模式的特性枢纠，即調(diào)用時(shí)像街，實(shí)參的值總是傳遞給形參，結(jié)束時(shí)，形參的值傳遞給實(shí)參宅广。調(diào)用時(shí)葫掉，對(duì)于 IN 模式的實(shí)參可以是常量或變量，但對(duì)于 OUT 和 IN OUT模式的實(shí)參必須是變量跟狱。只有在確認(rèn)function_name函數(shù)是新函數(shù)或是要更新的函數(shù)時(shí)俭厚，才使用OR REPALCE關(guān)鍵字，否則容易刪除有用的函數(shù)驶臊。

--獲取某部門(mén)的工資總和
CREATE OR REPLACE
FUNCTION get_salary(
  Dept_no NUMBER,
  Emp_count OUT NUMBER)
  RETURN NUMBER
IS
  V_sum NUMBER;
BEGIN
  SELECT SUM(SALARY), count(*) INTO V_sum, emp_count
    FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no;
  RETURN v_sum;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('你需要的數(shù)據(jù)不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END get_salary;

5.3 函數(shù)調(diào)用

在調(diào)用函數(shù)時(shí)挪挤，可以使用以下三種方法：

• 位置表示法：按形參的排列順序，依次寫(xiě)出實(shí)參的名稱(chēng)关翎，而將形參與實(shí)參關(guān)聯(lián)起來(lái)進(jìn)行傳遞 argument_value1[,argument_value2 …]
• 名稱(chēng)表示法：寫(xiě)出實(shí)參對(duì)應(yīng)的形參扛门，而將形參與實(shí)參關(guān)聯(lián)起來(lái)進(jìn)行傳遞 argument => parameter [,…]
• 組合傳遞：同時(shí)使用位置表示法和名稱(chēng)表示法為函數(shù)傳遞參數(shù)，使用位置表示法所傳遞的參數(shù)必須放在名稱(chēng)表示法所傳遞的參數(shù)前面纵寝。

-- 位置表示法
DECLARE
  V_num NUMBER;
  V_sum NUMBER;
BEGIN
  V_sum :=get_salary(10, v_num);
  DBMS_OUTPUT.PUT_LINE('部門(mén)號(hào)為:10的工資總和：'||v_sum||'论寨，人數(shù)為：'||v_num);
END;

-- 名稱(chēng)表示法
DECLARE
  V_num NUMBER;
    V_sum NUMBER;
BEGIN
    V_sum :=get_salary(emp_count => v_num, dept_no => 10);
    DBMS_OUTPUT.PUT_LINE('部門(mén)號(hào)為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);
END;

-- 組合傳遞
CREATE OR REPLACE FUNCTION demo_fun(
  Name VARCHAR2,--注意VARCHAR2不能給精度爽茴，如：VARCHAR2(10)葬凳，其它類(lèi)似
  Age INTEGER,
  Sex VARCHAR2)
  RETURN VARCHAR2
AS
  V_var VARCHAR2(32);
BEGIN
  V_var := name||'：'||TO_CHAR(age)||'歲.'||sex;
  RETURN v_var;
END;
 
DECLARE
  Var VARCHAR(32);
BEGIN
  Var := demo_fun('user1', 30, sex => '男');
  DBMS_OUTPUT.PUT_LINE(var);
 
  Var := demo_fun('user2', age => 40, sex => '男');
  DBMS_OUTPUT.PUT_LINE(var);
 
  Var := demo_fun('user3', sex => '女', age => 20);
  DBMS_OUTPUT.PUT_LINE(var);
END;

5.4 參數(shù)默認(rèn)值

在CREATE OR REPLACE FUNCTION 語(yǔ)句中聲明函數(shù)參數(shù)時(shí)可以使用DEFAULT關(guān)鍵字為輸入?yún)?shù)指定默認(rèn)值。

CREATE OR REPLACE FUNCTION demo_fun(
  Name VARCHAR2,
  Age INTEGER,
  Sex VARCHAR2 DEFAULT '男')
  RETURN VARCHAR2
AS
  V_var VARCHAR2(32);
BEGIN
  V_var := name||'：'||TO_CHAR(age)||'歲.'||sex;
  RETURN v_var;
END;

具有默認(rèn)值的函數(shù)創(chuàng)建后室奏，在函數(shù)調(diào)用時(shí)火焰，如果沒(méi)有為具有默認(rèn)值的參數(shù)提供實(shí)際參數(shù)值，函數(shù)將使用該參數(shù)的默認(rèn)值胧沫。但當(dāng)調(diào)用者為默認(rèn)參數(shù)提供實(shí)際參數(shù)時(shí)昌简，函數(shù)將使用實(shí)際參數(shù)值。在創(chuàng)建函數(shù)時(shí)绒怨，只能為輸入?yún)?shù)設(shè)置默認(rèn)值纯赎，而不能為輸入/輸出參數(shù)設(shè)置默認(rèn)值。

DECLARE
 varVARCHAR(32);
BEGIN
 Var := demo_fun('user1', 30);
 DBMS_OUTPUT.PUT_LINE(var);
 Var := demo_fun('user2', age => 40);
 DBMS_OUTPUT.PUT_LINE(var);
 Var := demo_fun('user3', sex => '女', age => 20);
 DBMS_OUTPUT.PUT_LINE(var);
END;

5.5 存儲(chǔ)過(guò)程

創(chuàng)建過(guò)程語(yǔ)法:

CREATE [OR REPLACE] PROCEDURE procedure_name
([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1],
 [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],
 ......
 [argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])
    [ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
  <聲明部分>
BEGIN
  <執(zhí)行部分>
EXCEPTION
  <可選的異常錯(cuò)誤處理程序>
END procedure_name;

范例

-- 用戶(hù)連接登記記錄窖逗；  
CREATE TABLE logtable (userid VARCHAR2(10), logdate date);
 
CREATE OR REPLACE PROCEDURE logexecution
IS
BEGIN
INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;

-- 刪除指定員工記錄址否； 
CREATE OR REPLACE
PROCEDURE DelEmp
(v_empno IN employees.employee_id%TYPE)
AS
No_result EXCEPTION;
BEGIN
   DELETE FROM employees WHERE employee_id = v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
   DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'的員工已被刪除!');
EXCEPTION
   WHEN no_result THEN
      DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END DelEmp;

-- 插入員工記錄: 
CREATE OR REPLACE
PROCEDURE InsertEmp(
   v_empno     in employees.employee_id%TYPE,
   v_firstname in employees.first_name%TYPE,
   v_lastname  in employees.last_name%TYPE,
   v_deptno    in employees.department_id%TYPE
   )
AS
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
   /* -1 是違反唯一約束條件的錯(cuò)誤代碼 */
BEGIN
   INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)
   VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno);
   DBMS_OUTPUT.PUT_LINE('溫馨提示:插入數(shù)據(jù)記錄成功!');
EXCEPTION
   WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('溫馨提示:違反數(shù)據(jù)完整性約束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END InsertEmp;

-- 使用存儲(chǔ)過(guò)程向departments表中插入數(shù)據(jù)。 
CREATE OR REPLACE
PROCEDURE insert_dept
  (v_dept_id IN departments.department_id%TYPE,
   v_dept_name IN departments.department_name%TYPE,
   v_mgr_id IN departments.manager_id%TYPE,
   v_loc_id IN departments.location_id%TYPE)
IS
   ept_null_error EXCEPTION;
   PRAGMA EXCEPTION_INIT(ept_null_error, -1400);
   ept_no_loc_id EXCEPTION;
   PRAGMA EXCEPTION_INIT(ept_no_loc_id, -2291);
BEGIN
   INSERT INTO departments
   (department_id, department_name, manager_id, location_id)
   VALUES
   (v_dept_id, v_dept_name, v_mgr_id, v_loc_id);
   DBMS_OUTPUT.PUT_LINE('插入部門(mén)'||v_dept_id||'成功');
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      RAISE_APPLICATION_ERROR(-20000, '部門(mén)編碼不能重復(fù)');
   WHEN ept_null_error THEN
      RAISE_APPLICATION_ERROR(-20001, '部門(mén)編碼碎紊、部門(mén)名稱(chēng)不能為空');
   WHEN ept_no_loc_id THEN
      RAISE_APPLICATION_ERROR(-20002, '沒(méi)有該地點(diǎn)');
END insert_dept;
 
/** 調(diào)用實(shí)例1
DECLARE
   ept_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(ept_20000, -20000);
   ept_20001 EXCEPTION;
   PRAGMA EXCEPTION_INIT(ept_20001, -20001);
   ept_20002 EXCEPTION;
   PRAGMA EXCEPTION_INIT(ept_20002, -20002);
BEGIN
   insert_dept(300, '部門(mén)300', 100, 2400);
   insert_dept(310, NULL, 100, 2400);
   insert_dept(310, '部門(mén)310', 100, 900);
EXCEPTION
   WHEN ept_20000 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20000部門(mén)編碼不能重復(fù)');
   WHEN ept_20001 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20001部門(mén)編碼佑附、部門(mén)名稱(chēng)不能為空');
   WHEN ept_20002 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20002沒(méi)有該地點(diǎn)');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯(cuò)誤');
END;
 
DECLARE
   ept_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(ept_20000, -20000);
   ept_20001 EXCEPTION;
   PRAGMA EXCEPTION_INIT(ept_20001, -20001);
   ept_20002 EXCEPTION;
   PRAGMA EXCEPTION_INIT(ept_20002, -20002);
BEGIN
   insert_dept(v_dept_name => '部門(mén)310', v_dept_id => 310,
               v_mgr_id => 100, v_loc_id => 2400);
   insert_dept(320, '部門(mén)320', v_mgr_id => 100, v_loc_id => 900);
EXCEPTION
   WHEN ept_20000 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20000部門(mén)編碼不能重復(fù)');
   WHEN ept_20001 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20001部門(mén)編碼、部門(mén)名稱(chēng)不能為空');
   WHEN ept_20002 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20002沒(méi)有該地點(diǎn)');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯(cuò)誤');
END;
**/

5.6 調(diào)用存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程建立完成后仗考，只要通過(guò)授權(quán)音同，用戶(hù)就可以在SQLPLUS 、ORACLE開(kāi)發(fā)工具或第三方開(kāi)發(fā)工具中來(lái)調(diào)用運(yùn)行秃嗜。對(duì)于參數(shù)的傳遞也有三種：按位置傳遞权均、按名稱(chēng)傳遞和組合傳遞顿膨，傳遞方法與函數(shù)的一樣。ORACLE 使用EXECUTE 語(yǔ)句來(lái)實(shí)現(xiàn)對(duì)存儲(chǔ)過(guò)程的調(diào)用： EXEC[UTE] procedure_name( parameter1, parameter2…);

-- 查詢(xún)指定員工記錄叽赊； 
CREATE OR REPLACE
PROCEDURE QueryEmp
(v_empno IN  employees.employee_id%TYPE,
 v_ename OUT employees.first_name%TYPE,
 v_sa-  OUT employees.salary%TYPE)
AS
BEGIN
       SELECT last_name || last_name, salary INTO v_ename, v_sal
    FROM employees
    WHERE employee_id = v_empno;
       DBMS_OUTPUT.PUT_LINE('溫馨提示:編碼為'||v_empno||'的員工已經(jīng)查到!');
EXCEPTION
       WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END QueryEmp;
--調(diào)用
 DECLARE
    v1 employees.first_name%TYPE;
    v2 employees.salary%TYPE;
 BEGIN
   QueryEmp(100, v1, v2);
   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
   DBMS_OUTPUT.PUT_LINE('工資:'||v2);
   QueryEmp(103, v1, v2);
   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
   DBMS_OUTPUT.PUT_LINE('工資:'||v2);
   QueryEmp(104, v1, v2);
   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
   DBMS_OUTPUT.PUT_LINE('工資:'||v2);
END;


-- 計(jì)算指定部門(mén)的工資總和恋沃，并統(tǒng)計(jì)其中的職工數(shù)量。 
CREATE OR REPLACE
PROCEDURE proc_demo
(
  dept_no NUMBER DEFAULT 10,
    sal_sum OUT NUMBER,
    emp_count OUT NUMBER
  )
IS
BEGIN
    SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
  FROM employees WHERE department_id = dept_no;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;
 
DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
BEGIN
  Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('溫馨提示:30號(hào)部門(mén)工資總和：'||v_sum||',人數(shù)：'||v_num);
  Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('溫馨提示:10號(hào)部門(mén)工資總和：'||v_sum||',人數(shù)：'||v_num);
END;

在PL/SQL 程序中還可以在塊內(nèi)建立本地函數(shù)和過(guò)程必指，這些函數(shù)和過(guò)程不存儲(chǔ)在數(shù)據(jù)庫(kù)中囊咏，但可以在創(chuàng)建它們的PL/SQL 程序中被重復(fù)調(diào)用。本地函數(shù)和過(guò)程在PL/SQL 塊的聲明部分定義塔橡，它們的語(yǔ)法格式與存儲(chǔ)函數(shù)和過(guò)程相同梅割，但不能使用CREATE OR REPLACE 關(guān)鍵字。

-- 建立本地過(guò)程葛家，用于計(jì)算指定部門(mén)的工資總和户辞，并統(tǒng)計(jì)其中的職工數(shù)量； 
DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
PROCEDURE proc_demo
  (
    Dept_no NUMBER DEFAULT 10,
    Sal_sum OUT NUMBER,
    Emp_count OUT NUMBER
  )
IS
BEGIN
    SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
    FROM employees WHERE department_id=dept_no;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('你需要的數(shù)據(jù)不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;
--調(diào)用方法：
BEGIN
    Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('30號(hào)部門(mén)工資總和：'||v_sum||'癞谒，人數(shù)：'||v_num);
    Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('10號(hào)部門(mén)工資總和：'||v_sum||'底燎，人數(shù)：'||v_num);
END;

5.7 AUTHID

過(guò)程中的AUTHID 指令可以告訴ORACLE ，這個(gè)過(guò)程使用誰(shuí)的權(quán)限運(yùn)行．默任情況下弹砚，存儲(chǔ)過(guò)程會(huì)作為調(diào)用者的過(guò)程運(yùn)行书蚪，但是具有設(shè)計(jì)者的特權(quán)．這稱(chēng)為設(shè)計(jì)者權(quán)利運(yùn)行．

-- 建立過(guò)程，使用AUTOID DEFINER迅栅； 
Connect HR/qaz
DROP TABLE logtable;
CREATE table logtable (userid VARCHAR2(10), logdate date);
 
CREATE OR REPLACE PROCEDURE logexecution
    AUTHID DEFINER
IS
BEGIN
   INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;
 
GRANT EXECUTE ON logexecution TO PUBLIC;
 
CONNECT / AS SYSDBA
GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1;
 
CONNECT testuser1/userpwd1
INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE);
EXECUTE HR.logexecution
 
CONNECT HR/qaz
SELECT * FROM HR.logtable;

-- 建立過(guò)程，使用AUTOID CURRENT_USER晴玖； 
CONNECT HR/qaz
 
CREATE OR REPLACE PROCEDURE logexecution
  AUTHID CURRENT_USER
IS
BEGIN
   INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;
 
GRANT EXECUTE ON logexecution TO PUBLIC;
 
CONNECT testuser1/userpwd1
INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE);
EXECUTE HR.logexecution

5.8 PRAGMA AUTONOMOUS_TRANSACTION

ORACLE可以支持事務(wù)處理中的事務(wù)處理的概念读存。這種子事務(wù)處理可以完成它自己的工作，獨(dú)立于父事務(wù)處理進(jìn)行提交或者回滾．通過(guò)使用這種方法呕屎，開(kāi)發(fā)者就能夠這樣的過(guò)程让簿，無(wú)論父事務(wù)處理是提交還是回滾，它都可以成功執(zhí)行秀睛。

-- 建立過(guò)程尔当，使用自動(dòng)事務(wù)處理進(jìn)行日志記錄；
DROP TABLE logtable;
 
CREATE TABLE logtable(
  Username varchar2(20),
  Dassate_time date,
  Mege varchar2(60)
);
 
CREATE TABLE temp_table( N number );
 
CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
  AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO logtable VALUES ( user, sysdate, p_message );
  COMMIT;
END log_message;
 
BEGIN
  Log_message (‘About to insert into temp_table‘);
  INSERT INTO temp_table VALUES (1);
  Log_message (‘Rollback to insert into temp_table‘);
  ROLLBACK;
END;
 
SELECT * FROM logtable;
SELECT * FROM temp_table;

-- 建立過(guò)程蹂安，沒(méi)有使用自動(dòng)事務(wù)處理進(jìn)行日志記錄椭迎； 
CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
  AS
BEGIN
  INSERT INTO logtable VALUES ( user, sysdate, p_message );
  COMMIT;
END log_message;
 
BEGIN
  Log_message ('About to insert into temp_table');
  INSERT INTO temp_table VALUES (1);
  Log_message ('Rollback to insert into temp_table');
  ROLLBACK;
END;
 
SELECT * FROM logtable;
SELECT * FROM temp_table;

5.9 開(kāi)發(fā)存儲(chǔ)過(guò)程步驟

開(kāi)發(fā)存儲(chǔ)過(guò)程、函數(shù)田盈、包及觸發(fā)器的步驟如下：
1 . 使用文字編輯處理軟件編輯存儲(chǔ)過(guò)程源碼
2 . 在SQLPLUS或用調(diào)試工具將存儲(chǔ)過(guò)程程序進(jìn)行解釋 START c:\stat.sql
3 . 調(diào)試源碼直到正確：a)使用SHOW ERROR提示錯(cuò)誤位置畜号；b)使用 user_errors數(shù)據(jù)字典查看各存儲(chǔ)過(guò)程的錯(cuò)誤位置
4 . 授權(quán)執(zhí)行權(quán)給相關(guān)的用戶(hù)或角色：如果存儲(chǔ)過(guò)程沒(méi)有授權(quán)，只有建立者才可以運(yùn)行允瞧〖蛉恚可以用GRANT命令來(lái)進(jìn)行存儲(chǔ)過(guò)程的運(yùn)行授權(quán)蛮拔。

-- GRANT語(yǔ)法 
GRANT system_privilege | role
TO user | role | PUBLIC [WITH ADMIN OPTION]
 
GRANT object_privilege | ALL ON schema.object
TO user | role | PUBLIC [WITH GRANT OPTION]
 
-- 例子
CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

5.10 刪除過(guò)程和函數(shù)

1．刪除過(guò)程 DROP PROCEDURE [user.]Procudure_name;
2 . 刪除函數(shù) DROP FUNCTION [user.]Function_name;

5.11 過(guò)程與函數(shù)的比較

**過(guò)程與函數(shù)具有如下優(yōu)點(diǎn)： **

• 共同使用的代碼可以只需要被編寫(xiě)和測(cè)試一次，而被需要該代碼的任何應(yīng)用程序（如：.NET痹升、C++建炫、JAVA、VB程序疼蛾，也可以是DLL庫(kù)）調(diào)用肛跌。
• 這種集中編寫(xiě)、集中維護(hù)更新据过、大家共享（或重用）的方法惋砂，簡(jiǎn)化了應(yīng)用程序的開(kāi)發(fā)和維護(hù)，提高了效率與性能绳锅。
• 這種模塊化的方法西饵，使得可以將一個(gè)復(fù)雜的問(wèn)題、大的程序逐步簡(jiǎn)化成幾個(gè)簡(jiǎn)單的鳞芙、小的程序部分眷柔，進(jìn)行分別編寫(xiě)、調(diào)試原朝。因此使程序的結(jié)構(gòu)清晰驯嘱、簡(jiǎn)單，也容易實(shí)現(xiàn)
• 可以在各個(gè)開(kāi)發(fā)者之間提供處理數(shù)據(jù)喳坠、控制流程鞠评、提示信息等方面的一致性。
• 節(jié)省內(nèi)存空間壕鹉。它們以一種壓縮的形式被存儲(chǔ)在外存中剃幌，當(dāng)被調(diào)用時(shí)才被放入內(nèi)存進(jìn)行處理。并且晾浴，如果多個(gè)用戶(hù)要執(zhí)行相同的過(guò)程或函數(shù)時(shí)负乡，就只需要在內(nèi)存中加載一個(gè)該過(guò)程或函數(shù)。
• 提高數(shù)據(jù)的安全性與完整性脊凰。通過(guò)把一些對(duì)數(shù)據(jù)的操作放到過(guò)程或函數(shù)中抖棘，就可以通過(guò)是否授予用戶(hù)有執(zhí)行該過(guò)程或的權(quán)限，來(lái)限制某些用戶(hù)對(duì)數(shù)據(jù)進(jìn)行這些操作狸涌。

過(guò)程與函數(shù)的相同功能有：

• 都使用IN模式的參數(shù)傳入數(shù)據(jù)切省、OUT模式的參數(shù)返回?cái)?shù)據(jù)。
• 輸入?yún)?shù)都可以接受默認(rèn)值杈抢，都可以傳值或傳引導(dǎo)数尿。
• 調(diào)用時(shí)的實(shí)際參數(shù)都可以使用位置表示法、名稱(chēng)表示法或組合方法惶楼。
• 都有聲明部分右蹦、執(zhí)行部分和異常處理部分诊杆。
• 其管理過(guò)程都有創(chuàng)建、編譯何陆、授權(quán)晨汹、刪除、顯示依賴(lài)關(guān)系等贷盲。

使用過(guò)程與函數(shù)的原則：

• 如果需要返回多個(gè)值和不返回值淘这，就使用過(guò)程；如果只需要返回一個(gè)值巩剖，就使用函數(shù)铝穷。
• 過(guò)程一般用于執(zhí)行一個(gè)指定的動(dòng)作，函數(shù)一般用于計(jì)算和返回一個(gè)值佳魔。
• 可以SQL語(yǔ)句內(nèi)部（如表達(dá)式）調(diào)用函數(shù)來(lái)完成復(fù)雜的計(jì)算問(wèn)題曙聂，但不能調(diào)用過(guò)程。所以這是函數(shù)的特色鞠鲜。

6.1 觸發(fā)器類(lèi)型

觸發(fā)器在數(shù)據(jù)庫(kù)里以獨(dú)立的對(duì)象存儲(chǔ)宁脊，它與存儲(chǔ)過(guò)程和函數(shù)不同的是，存儲(chǔ)過(guò)程與函數(shù)需要用戶(hù)顯示調(diào)用才執(zhí)行贤姆，而觸發(fā)器是由一個(gè)事件來(lái)啟動(dòng)運(yùn)行榆苞。即觸發(fā)器是當(dāng)某個(gè)事件發(fā)生時(shí)自動(dòng)地隱式運(yùn)行。并且霞捡，觸發(fā)器不能接收參數(shù)坐漏。所以運(yùn)行觸發(fā)器就叫觸發(fā)或點(diǎn)火（firing）。ORACLE事件指的是對(duì)數(shù)據(jù)庫(kù)的表進(jìn)行的INSERT碧信、UPDATE及DELETE操作或?qū)σ晥D進(jìn)行類(lèi)似的操作仙畦。ORACLE將觸發(fā)器的功能擴(kuò)展到了觸發(fā)ORACLE，如數(shù)據(jù)庫(kù)的啟動(dòng)與關(guān)閉等音婶。所以觸發(fā)器常用來(lái)完成由數(shù)據(jù)庫(kù)的完整性約束難以完成的復(fù)雜業(yè)務(wù)規(guī)則的約束，或用來(lái)監(jiān)視對(duì)數(shù)據(jù)庫(kù)的各種操作莱坎，實(shí)現(xiàn)審計(jì)的功能衣式。

1. DML觸發(fā)器：在DML語(yǔ)句進(jìn)行觸發(fā)，可以在DML操作前或操作后進(jìn)行觸發(fā)檐什，并且可以對(duì)每個(gè)行或語(yǔ)句操作上進(jìn)行觸發(fā)碴卧。
2. 替代觸發(fā)器：不能直接對(duì)由兩個(gè)以上的表建立的視圖進(jìn)行操作。所以給出了替代觸發(fā)器乃正。它就是ORACLE專(zhuān)門(mén)為進(jìn)行視圖操作的一種處理方法住册。
3. 系統(tǒng)觸發(fā)器：可以在ORACLE數(shù)據(jù)庫(kù)系統(tǒng)的事件中進(jìn)行觸發(fā)，如ORACLE系統(tǒng)的啟動(dòng)與關(guān)閉等瓮具。

6.2 觸發(fā)器組成

觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件荧飞。 例如：DML語(yǔ)句(INSERT, UPDATE, DELETE語(yǔ)句對(duì)表或視圖執(zhí)行數(shù)據(jù)處理操作)凡人、DDL語(yǔ)句（如CREATE、ALTER叹阔、DROP語(yǔ)句 在數(shù)據(jù)庫(kù)中創(chuàng)建挠轴、修改、刪除模式對(duì)象）耳幢、數(shù)據(jù)庫(kù)系統(tǒng)事件（如系統(tǒng)啟動(dòng)或退出岸晦、異常錯(cuò)誤）、用戶(hù)事件（如登錄或退出數(shù)據(jù)庫(kù)）睛藻。
觸發(fā)時(shí)間：即該TRIGGER 是在觸發(fā)事件發(fā)生之前（BEFORE）還是之后(AFTER)觸發(fā)启上，也就是觸發(fā)事件和該TRIGGER 的操作順序。
觸發(fā)操作：即該TRIGGER 被觸發(fā)之后的目的和意圖店印，正是觸發(fā)器本身要做的事情冈在。 例如：PL/SQL 塊。
** 觸發(fā)對(duì)象：包括表吱窝、視圖讥邻、模式、數(shù)據(jù)庫(kù)院峡。只有在這些對(duì)象上發(fā)生了符合觸發(fā)條件的觸發(fā)事件兴使，才會(huì)執(zhí)行觸發(fā)操作。
觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式照激。只有當(dāng)該表達(dá)式的值為T(mén)RUE時(shí)发魄，遇到觸發(fā)事件才會(huì)自動(dòng)執(zhí)行觸發(fā)器，使其執(zhí)行觸發(fā)操作俩垃。
** 觸發(fā)頻率：說(shuō)明觸發(fā)器內(nèi)定義的動(dòng)作被執(zhí)行的次數(shù)励幼。即語(yǔ)句級(jí)(STATEMENT)觸發(fā)器和行級(jí)(ROW)觸發(fā)器。

語(yǔ)句級(jí)(STATEMENT)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)口柳，該觸發(fā)器只執(zhí)行一次苹粟；
行級(jí)(ROW)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)，對(duì)受到該操作影響的每一行數(shù)據(jù)跃闹，觸發(fā)器都單獨(dú)執(zhí)行一次嵌削。

6.3 觸發(fā)器注意點(diǎn)

• 觸發(fā)器不接受參數(shù)
• 一個(gè)表上最多可有12個(gè)觸發(fā)器，但同一時(shí)間望艺、同一事件苛秕、同一類(lèi)型的觸發(fā)器只能有一個(gè)。并各觸發(fā)器之間不能有矛盾找默。
• 在一個(gè)表上的觸發(fā)器越多艇劫，對(duì)在該表上的DML操作的性能影響就越大。
• 觸發(fā)器最大為32KB惩激。若確實(shí)需要店煞，可以先建立過(guò)程蟹演，然后在觸發(fā)器中用CALL語(yǔ)句進(jìn)行調(diào)用。
• 在觸發(fā)器的執(zhí)行部分只能用DML語(yǔ)句（SELECT浅缸、INSERT轨帜、UPDATE、DELETE）衩椒，不能使用DDL語(yǔ)句（CREATE蚌父、ALTER、DROP）毛萌。
• 觸發(fā)器中不能包含事務(wù)控制語(yǔ)句(COMMIT苟弛，ROLLBACK，SAVEPOINT)阁将。因?yàn)橛|發(fā)器是觸發(fā)語(yǔ)句的一部分膏秫，觸發(fā)語(yǔ)句被提交、回退時(shí)做盅，觸發(fā)器也被提交缤削、回退了。
• 在觸發(fā)器主體中調(diào)用的任何過(guò)程吹榴、函數(shù)亭敢，都不能使用事務(wù)控制語(yǔ)句。
• 在觸發(fā)器主體中不能申明任何Long和blob變量图筹。新值new和舊值old也不能向表中的任何long和blob列帅刀。
• 不同類(lèi)型的觸發(fā)器(如DML觸發(fā)器、INSTEAD OF觸發(fā)器远剩、系統(tǒng)觸發(fā)器)的語(yǔ)法格式和作用有較大區(qū)別扣溺。

6.4 創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器的一般語(yǔ)法是:

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 指出觸發(fā)器的觸發(fā)時(shí)序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在執(zhí)行觸發(fā)事件之前觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器瓜晤，后觸發(fā)是在執(zhí)行觸發(fā)事件之后觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器锥余。

• FOR EACH ROW選項(xiàng)說(shuō)明觸發(fā)器為行觸發(fā)器。
• 行觸發(fā)器和語(yǔ)句觸發(fā)器的區(qū)別表現(xiàn)在：行觸發(fā)器要求當(dāng)一個(gè)DML語(yǔ)句操走影響數(shù)據(jù)庫(kù)中的多行數(shù)據(jù)時(shí)痢掠，對(duì)于其中的每個(gè)數(shù)據(jù)行哈恰，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器志群；而語(yǔ)句觸發(fā)器將整個(gè)語(yǔ)句操作作為觸發(fā)事件，當(dāng)它符合約束條件時(shí)蛔钙，激活一次觸發(fā)器锌云。
• 當(dāng)省略FOR EACH ROW選項(xiàng)時(shí)，BEFORE 和 AFTER 觸發(fā)器為語(yǔ)句觸發(fā)器吁脱，而 INSTEAD OF 觸發(fā)器則只能為行觸發(fā)器桑涎。
• REFERENCING 子句說(shuō)明相關(guān)名稱(chēng)彬向，在行觸發(fā)器的PL/SQL塊和 WHEN 子句中可以使用相關(guān)名稱(chēng)參照當(dāng)前的新、舊列值攻冷，默認(rèn)的相關(guān)名稱(chēng)分別為 OLD 和 NEW 。觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱(chēng)時(shí)，必須在它們之前加冒號(hào):锁摔，但在 WHEN 子句中則不能加冒號(hào):损离。
• WHEN 子句說(shuō)明觸發(fā)約束條件。 Condition 為一個(gè)邏輯表達(dá)時(shí)禁谦，其中必須包含相關(guān)名稱(chēng)胁黑，而不能包含查詢(xún)語(yǔ)句，也不能調(diào)用PL/SQL 函數(shù)州泊。WHEN 子句指定的觸發(fā)約束條件只能用在 BEFORE 和 AFTER 行觸發(fā)器中丧蘸，不能用在 INSTEAD OF 行觸發(fā)器和其它類(lèi)型的觸發(fā)器中。

• 當(dāng)一個(gè)基表被修改( `INSERT` ,  `UPDATE`,  `DELETE` )時(shí)要執(zhí)行的存儲(chǔ)過(guò)程遥皂，執(zhí)行時(shí)根據(jù)其所依附的基表改動(dòng)而自動(dòng)觸發(fā)力喷，因此與應(yīng)用程序無(wú)關(guān)，用數(shù)據(jù)庫(kù)觸發(fā)器可以保證數(shù)據(jù)的一致性和完整性演训。 
  

每張表最多可建立12 種類(lèi)型的觸發(fā)器弟孟，它們是:

BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
 
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
 
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW 

6.5 觸發(fā)器觸發(fā)次序

1. 執(zhí)行 BEFORE語(yǔ)句級(jí)觸發(fā)器;
2. 對(duì)與受語(yǔ)句影響的每一行
   2.1 執(zhí)行 BEFORE行級(jí)觸發(fā)器
   2.2 執(zhí)行 DML語(yǔ)句
   2.3 執(zhí)行 AFTER行級(jí)觸發(fā)器
3. 執(zhí)行 AFTER語(yǔ)句級(jí)觸發(fā)器

6.6 創(chuàng)建DML觸發(fā)器

觸發(fā)器名與過(guò)程名和包的名字不一樣，它是單獨(dú)的名字空間仇祭，因而觸發(fā)器名可以和表或過(guò)程有相同的名字披蕉，但在一個(gè)模式中觸發(fā)器名不能相同。

DML觸發(fā)器的限制

• CREATE TRIGGER語(yǔ)句文本的字符長(zhǎng)度不能超過(guò)32KB乌奇；
• 觸發(fā)器體內(nèi)的 SELECT 語(yǔ)句只能為 SELECT … INTO …結(jié)構(gòu)没讲，或者為定義游標(biāo)所使用的 SELECT 語(yǔ)句。
• 觸發(fā)器中不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句 COMMIT; ROLLBACK, SVAEPOINT 語(yǔ)句礁苗；
• 由觸發(fā)器所調(diào)用的過(guò)程或函數(shù)也不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句爬凑；
• 觸發(fā)器中不能使用 LONG, LONG RAW 類(lèi)型；
• 觸發(fā)器內(nèi)可以參照 LOB 類(lèi)型列的列值试伙，但不能通過(guò) : NEW 修改 LOB 列中的數(shù)據(jù)嘁信；

DML觸發(fā)器基本要點(diǎn)

• 觸發(fā)時(shí)機(jī)：指定觸發(fā)器的觸發(fā)時(shí)間。如果指定為 BEFORE疏叨，則表示在執(zhí)行DML操作之前觸發(fā)潘靖，以便防止某些錯(cuò)誤操作發(fā)生或?qū)崿F(xiàn)某些業(yè)務(wù)規(guī)則；如果指定為AFTER蚤蔓，則表示在執(zhí)行DML操作之后觸發(fā)卦溢，以便記錄該操作或做某些事后處理。
• 觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件，即DML操作（INSERT单寂、UPDATE贬芥、DELETE）。既可以是單個(gè)觸發(fā)事件宣决，也可以是多個(gè)觸發(fā)事件的組合（只能使用OR邏輯組合蘸劈，不能使用AND邏輯組合）。
• 條件謂詞：當(dāng)在觸發(fā)器中包含多個(gè)觸發(fā)事件（INSERT尊沸、UPDATE威沫、DELETE）的組合時(shí)，為了分別針對(duì)不同的事件進(jìn)行不同的處理椒丧，需要使用ORACLE提供的如下條件謂詞壹甥。
  1） INSERTING ：當(dāng)觸發(fā)事件是 INSERT 時(shí)，取值為T(mén)RUE壶熏，否則為FALSE
  2） UPDATING [（column_1,column_2,…,column_x）]：當(dāng)觸發(fā)事件是 UPDATE 時(shí)句柠，如果修改了 column_x 列，則取值為 TRUE棒假，否則為 FALSE 溯职。其中 column_x 是可選的
  3） DELETING ：當(dāng)觸發(fā)事件是 DELETE 時(shí)，則取值為 TRUE 帽哑，否則為 FALSE
  解發(fā)對(duì)象：指定觸發(fā)器是創(chuàng)建在哪個(gè)表谜酒、視圖上。
• 觸發(fā)類(lèi)型：是語(yǔ)句級(jí)還是行級(jí)觸發(fā)器妻枕。
• 觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式僻族，只允許在行級(jí)觸發(fā)器上指定觸發(fā)條件，指定UPDATING后面的列的列表屡谐。

當(dāng)觸發(fā)器被觸發(fā)時(shí)述么，要使用被插入、更新或刪除的記錄中的列值愕掏，有時(shí)要使用操作前度秘、操作后列的值
:NEW 修飾符訪問(wèn)操作完成后列的值，:OLD 修飾符訪問(wèn)操作完成前列的值

-- 建立一個(gè)觸發(fā)器, 當(dāng)職工表 emp 表被刪除一條記錄時(shí)饵撑，把被刪除記錄寫(xiě)到職工表刪除日志表中去
CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
   BEFORE DELETE --指定觸發(fā)時(shí)機(jī)為刪除操作前觸發(fā)
   ON scott.emp
   FOR EACH ROW   --說(shuō)明創(chuàng)建的是行級(jí)觸發(fā)器
BEGIN
   --將修改前數(shù)據(jù)插入到日志記錄表 del_emp ,以供監(jiān)督使用剑梳。
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

-- 限制對(duì)Departments表修改（包括INSERT,DELETE,UPDATE）的時(shí)間范圍，
-- 即不允許在非工作時(shí)間修改departments表滑潘。 
CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
BEGIN
 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') 
     NOT BETWEEN '08:30' AND '18:00') THEN
     RAISE_APPLICATION_ERROR(-20001, '不是上班時(shí)間垢乙，不能修改departments表');
 END IF;
END;

-- 限定只對(duì)部門(mén)號(hào)為80的記錄進(jìn)行行觸發(fā)器操作。 
CREATE OR REPLACE TRIGGER tr_emp_sal_comm
BEFORE UPDATE OF salary, commission_pct
       OR DELETE
ON HR.employees
FOR EACH ROW
WHEN (old.department_id = 80)
BEGIN
 CASE
     WHEN UPDATING ('salary') THEN
        IF :NEW.salary < :old.salary THEN
 
           RAISE_APPLICATION_ERROR(-20001, '部門(mén)80的人員的工資不能降');
        END IF;
     WHEN UPDATING ('commission_pct') THEN
 
        IF :NEW.commission_pct < :old.commission_pct THEN
           RAISE_APPLICATION_ERROR(-20002, '部門(mén)80的人員的獎(jiǎng)金不能降');
        END IF;
     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20003, '不能刪除部門(mén)80的人員記錄');
     END CASE;
END;
 

-- 利用行觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)更新语卤。在修改了主表regions中的region_id之后（AFTER）追逮，
-- 級(jí)聯(lián)的蓖租、自動(dòng)的更新子表countries表中原來(lái)在該地區(qū)的國(guó)家的region_id。 
CREATE OR REPLACE TRIGGER tr_reg_cou
AFTER update OF region_id
ON regions
FOR EACH ROW
BEGIN
 DBMS_OUTPUT.PUT_LINE('舊的region_id值是'||:old.region_id
                  ||'羊壹、新的region_id值是'||:new.region_id);
 UPDATE countries SET region_id = :new.region_id
 WHERE region_id = :old.region_id;
END;

-- 在觸發(fā)器中調(diào)用過(guò)程。 
CREATE OR REPLACE PROCEDURE add_job_history
 ( p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
  , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
 INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
  VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
 
--創(chuàng)建觸發(fā)器調(diào)用存儲(chǔ)過(guò)程...
CREATE OR REPLACE TRIGGER update_job_history
 AFTER UPDATE OF job_id, department_id ON employees
 FOR EACH ROW
BEGIN
 add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;

6.7 創(chuàng)建替代(INSTEAD OF)觸發(fā)器

創(chuàng)建替代觸發(fā)器的一般語(yǔ)法是:

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 ] --因?yàn)镮NSTEAD OF觸發(fā)器只能在行級(jí)上觸發(fā),所以沒(méi)有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

• INSTEAD OF 選項(xiàng)使ORACLE激活觸發(fā)器齐婴，而不執(zhí)行觸發(fā)事件油猫。只能對(duì)視圖和對(duì)象視圖建立INSTEAD OF觸發(fā)器，而不能對(duì)表柠偶、模式和數(shù)據(jù)庫(kù)建立INSTEAD OF 觸發(fā)器情妖。

• FOR EACH ROW選項(xiàng)說(shuō)明觸發(fā)器為行觸發(fā)器。行觸發(fā)器和語(yǔ)句觸發(fā)器的區(qū)別表現(xiàn)在：行觸發(fā)器要求當(dāng)一個(gè)DML語(yǔ)句操走影響數(shù)據(jù)庫(kù)中的多行數(shù)據(jù)時(shí)诱担，對(duì)于其中的每個(gè)數(shù)據(jù)行毡证，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器蔫仙；而語(yǔ)句觸發(fā)器將整個(gè)語(yǔ)句操作作為觸發(fā)事件料睛，當(dāng)它符合約束條件時(shí)，激活一次觸發(fā)器摇邦。當(dāng)省略 FOR EACH ROW 選項(xiàng)時(shí)恤煞，BEFORE 和AFTER 觸發(fā)器為語(yǔ)句觸發(fā)器，而INSTEAD OF 觸發(fā)器則為行觸發(fā)器施籍。
• REFERENCING 子句說(shuō)明相關(guān)名稱(chēng)居扒，在行觸發(fā)器的PL/SQL塊和 WHEN 子句中可以使用相關(guān)名稱(chēng)參照當(dāng)前的新、舊列值丑慎，默認(rèn)的相關(guān)名稱(chēng)分別為 OLD 和 NEW 喜喂。觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱(chēng)時(shí)，必須在它們之前加冒號(hào):竿裂，但在WHEN子句中則不能加冒號(hào):
• WHEN 子句說(shuō)明觸發(fā)約束條件玉吁。 Condition 為一個(gè)邏輯表達(dá)時(shí)，其中必須包含相關(guān)名稱(chēng)铛绰，而不能包含查詢(xún)語(yǔ)句诈茧，也不能調(diào)用PL/SQL 函數(shù)。 WHEN 子句指定的觸發(fā)約束條件只能用在 BEFORE 和 AFTER 行觸發(fā)器中捂掰，不能用在INSTEAD OF 行觸發(fā)器和其它類(lèi)型的觸發(fā)器中敢会。
• INSTEAD_OF 用于對(duì)視圖的DML觸發(fā)，由于視圖有可能是由多個(gè)表進(jìn)行聯(lián)結(jié) join 而成这嚣，因而并非是所有的聯(lián)結(jié)都是可更新的鸥昏。但可以按照所需的方式執(zhí)行更新，例如下面情況：

CREATE OR REPLACE VIEW emp_view AS
SELECT deptno, count(*) total_employeer, sum(sal) total_salary
FROM emp GROUP BY deptno;

-- 在此視圖中直接刪除是非法： 
SQL>DELETE FROM emp_view WHERE deptno=10;
DELETE FROM emp_view WHERE deptno=10

-- ERROR 位于第 1 行:
-- ORA-01732: 此視圖的數(shù)據(jù)操縱操作非法 
-- 但是我們可以創(chuàng)建INSTEAD_OF觸發(fā)器來(lái)為 DELETE 操作執(zhí)行所需的處理姐帚，即刪除EMP表中所有基準(zhǔn)行：  

CREATE OR REPLACE TRIGGER emp_view_delete
   INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
   DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete;
 
DELETE FROM emp_view WHERE deptno=10;
DROP TRIGGER emp_view_delete;
DROP VIEW emp_view;

創(chuàng)建復(fù)雜視圖吏垮，針對(duì)INSERT操作創(chuàng)建INSTEAD OF觸發(fā)器，向復(fù)雜視圖插入數(shù)據(jù)。

-- 創(chuàng)建視圖：
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;

-- 創(chuàng)建觸發(fā)器： 
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;

創(chuàng)建INSTEAD OF觸發(fā)器需要注意以下幾點(diǎn)：

• 只能被創(chuàng)建在視圖上膳汪，并且該視圖沒(méi)有指定WITH CHECK OPTION選項(xiàng)唯蝶。
• 不能指定BEFORE 或 AFTER選項(xiàng)。
• FOR EACH ROW子可是可選的遗嗽，即INSTEAD OF觸發(fā)器只能在行級(jí)上觸發(fā)粘我、或只能是行級(jí)觸發(fā)器，沒(méi)有必要指定痹换。
• 沒(méi)有必要在針對(duì)一個(gè)表的視圖上創(chuàng)建INSTEAD OF觸發(fā)器征字，只要?jiǎng)?chuàng)建DML觸發(fā)器就可以了。

6.8 創(chuàng)建系統(tǒng)事件觸發(fā)器

ORACLE10G提供的系統(tǒng)事件觸發(fā)器可以在DDL或數(shù)據(jù)庫(kù)系統(tǒng)上被觸發(fā)娇豫。DDL指的是數(shù)據(jù)定義語(yǔ)言匙姜，如CREATE 、ALTER及DROP 等冯痢。而數(shù)據(jù)庫(kù)系統(tǒng)事件包括數(shù)據(jù)庫(kù)服務(wù)器的啟動(dòng)或關(guān)閉氮昧，用戶(hù)的登錄與退出、數(shù)據(jù)庫(kù)服務(wù)錯(cuò)誤等系羞。創(chuàng)建系統(tǒng)觸發(fā)器的語(yǔ)法如下：

CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE|AFTER}
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.]SCHEMA }
[WHEN condition]
PL/SQL_block | CALL procedure_name;

• ddl_event_list ：一個(gè)或多個(gè)DDL 事件郭计，事件間用 OR 分開(kāi)；

• database_event_list ：一個(gè)或多個(gè)數(shù)據(jù)庫(kù)事件椒振，事件間用 OR 分開(kāi)昭伸；
• 系統(tǒng)事件觸發(fā)器既可以建立在一個(gè)模式上，又可以建立在整個(gè)數(shù)據(jù)庫(kù)上澎迎。當(dāng)建立在模式(SCHEMA)之上時(shí)庐杨，只有模式所指定用戶(hù)的DDL操作和它們所導(dǎo)致的錯(cuò)誤才激活觸發(fā)器, 默認(rèn)時(shí)為當(dāng)前用戶(hù)模式。當(dāng)建立在數(shù)據(jù)庫(kù)(DATABASE)之上時(shí)夹供，該數(shù)據(jù)庫(kù)所有用戶(hù)的DDL操作和他們所導(dǎo)致的錯(cuò)誤灵份，以及數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉均可激活觸發(fā)器。要在數(shù)據(jù)庫(kù)之上建立觸發(fā)器時(shí)哮洽，要求用戶(hù)具有ADMINISTER DATABASE TRIGGER權(quán)限填渠。

系統(tǒng)觸發(fā)器的種類(lèi)和事件出現(xiàn)的時(shí)機(jī)表（前或后）

6.9 系統(tǒng)觸發(fā)器事件屬性

除DML語(yǔ)句的列屬性外，其余事件屬性值可通過(guò)調(diào)用ORACLE定義的事件屬性函數(shù)來(lái)讀取

-- ex1. 創(chuàng)建觸發(fā)器，存放有關(guān)事件信息
DESC ora_sysevent
DESC ora_login_user
 
--創(chuàng)建用于記錄事件用的表
CREATE TABLE ddl_event
(crt_date timestamp PRIMARY KEY,
 event_name VARCHAR2(20),
 user_name VARCHAR2(10),
 obj_type VARCHAR2(20),
 obj_name VARCHAR2(20));
 
--創(chuàng)建觸犯發(fā)器
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON SCHEMA
BEGIN
   INSERT INTO ddl_event VALUES
   (systimestamp,ora_sysevent, ora_login_user,
    ora_dict_obj_type, ora_dict_obj_name);
END tr_ddl;

-- ex2. 創(chuàng)建登錄川尖、退出觸發(fā)器登下。 
CREATE TABLE log_event
(user_name VARCHAR2(10),
 address VARCHAR2(20),
 logon_date timestamp,
 logoff_date timestamp);
 
--創(chuàng)建登錄觸發(fā)器
CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logon_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logon;

--創(chuàng)建退出觸發(fā)器
CREATE OR REPLACE TRIGGER tr_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logoff_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logoff;

6.10 使用觸發(fā)器謂詞

ORACLE 提供三個(gè)參數(shù)INSERTING, UPDATING, DELETING 用于判斷觸發(fā)了哪些操作。

6.11 重新編譯觸發(fā)器

如果在觸發(fā)器內(nèi)調(diào)用其它函數(shù)或過(guò)程馍悟，當(dāng)這些函數(shù)或過(guò)程被刪除或修改后畔濒，觸發(fā)器的狀態(tài)將被標(biāo)識(shí)為無(wú)效。當(dāng)DML語(yǔ)句激活一個(gè)無(wú)效觸發(fā)器時(shí)锣咒，ORACLE將重新編譯觸發(fā)器代碼侵状，如果編譯時(shí)發(fā)現(xiàn)錯(cuò)誤，這將導(dǎo)致DML語(yǔ)句執(zhí)行失敗毅整∪ば郑可以調(diào)用ALTER TRIGGER語(yǔ)句重新編譯已經(jīng)創(chuàng)建的觸發(fā)器，格式為：

ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]

6.12 刪除和使能觸發(fā)器

• 刪除觸發(fā)器：DROP TRIGGER trigger_name;

當(dāng)刪除其他用戶(hù)模式中的觸發(fā)器名稱(chēng)悼嫉，需要具有DROP ANY TRIGGER系統(tǒng)權(quán)限艇潭，當(dāng)刪除建立在數(shù)據(jù)庫(kù)上的觸發(fā)器時(shí)，用戶(hù)需要具有ADMINISTER DATABASE TRIGGER系統(tǒng)權(quán)限戏蔑。
此外蹋凝，當(dāng)刪除表或視圖時(shí)，建立在這些對(duì)象上的觸發(fā)器也隨之刪除辛臊。

• 禁用或啟用觸發(fā)器： ALTER TIGGER trigger_name [DISABLE | ENABLE ];

(1) 有效狀態(tài)ENABLE：當(dāng)觸發(fā)事件發(fā)生時(shí)仙粱，處于有效狀態(tài)的數(shù)據(jù)庫(kù)觸發(fā)器 TRIGGER 將被觸發(fā)。
(2) 無(wú)效狀態(tài)DISABLE：當(dāng)觸發(fā)事件發(fā)生時(shí)彻舰，處于無(wú)效狀態(tài)的數(shù)據(jù)庫(kù)觸發(fā)器 TRIGGER 將不會(huì)被觸發(fā)伐割，此時(shí)就跟沒(méi)有這個(gè)數(shù)據(jù)庫(kù)觸發(fā)器TRIGGER 一樣候味。

ALTER TRIGGER語(yǔ)句一次只能改變一個(gè)觸發(fā)器的狀態(tài)，而ALTER TABLE語(yǔ)句則一次能夠改變與指定表相關(guān)的所有觸發(fā)器的使用狀態(tài)隔心。

--格式為
ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;

--例：使表EMP 上的所有TRIGGER 失效：
ALTER TABLE emp DISABLE ALL TRIGGERS;

6.13 觸發(fā)器的應(yīng)用舉例

例1. 創(chuàng)建一個(gè)DML語(yǔ)句級(jí)觸發(fā)器白群，當(dāng)對(duì)emp表執(zhí)行INSERT, UPDATE, DELETE 操作時(shí)，它自動(dòng)更新dept_summary 表中的數(shù)據(jù)硬霍。由于在PL/SQL塊中不能直接調(diào)用DDL語(yǔ)句帜慢，所以，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過(guò)程唯卖，由它執(zhí)行DDL語(yǔ)句創(chuàng)建觸發(fā)器粱玲。

CREATE TABLE dept_summary(
 Deptno NUMBER(2),
 Sal_sum NUMBER(9, 2),
 Emp_count NUMBER);
 
INSERT INTO dept_summary(deptno, sal_sum, emp_count)
 SELECT deptno, SUM(sal), COUNT(*)
FROM emp
GROUP BY deptno;
 
--創(chuàng)建一個(gè)PL/SQL過(guò)程disp_dept_summary
--在觸發(fā)器中調(diào)用該過(guò)程顯示dept_summary標(biāo)中的數(shù)據(jù)。
CREATE OR REPLACE PROCEDURE disp_dept_summary
IS
 Rec dept_summary%ROWTYPE;
 CURSOR c1 IS SELECT * FROM dept_summary;
BEGIN
 OPEN c1;
 FETCH c1 INTO REC;
 DBMS_OUTPUT.PUT_LINE('deptno    sal_sum    emp_count');
 DBMS_OUTPUT.PUT_LINE('-------------------------------------');
 WHILE c1%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6)||
      To_char(rec.sal_sum, '$999,999.99')||
      LPAD(rec.emp_count, 13));
    FETCH c1 INTO rec;
 END LOOP;
 CLOSE c1;
END;
BEGIN
 DBMS_OUTPUT.PUT_LINE('插入前');
 Disp_dept_summary();
 DBMS_UTILITY.EXEC_DDL_STATEMENT('
    CREATE OR REPLACE TRIGGER trig1
      AFTER INSERT OR DELETE OR UPDATE OF sal ON emp
    BEGIN
      DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig1 觸發(fā)器…'');
      DELETE FROM dept_summary;
      INSERT INTO dept_summary(deptno, sal_sum, emp_count)
      SELECT deptno, SUM(sal), COUNT(*)
      FROM emp GROUP BY deptno;
    END;
 ');
 
 
 INSERT INTO dept(deptno, dname, loc)
 VALUES(90, ‘demo_dept’, ‘none_loc’);
 INSERT INTO emp(ename, deptno, empno, sal)
 VALUES(USER, 90, 9999, 3000);
 
 DBMS_OUTPUT.PUT_LINE('插入后');
 Disp_dept_summary();
 
 UPDATE emp SET sal=1000 WHERE empno=9999;
 DBMS_OUTPUT.PUT_LINE('修改后');
 Disp_dept_summary();
 
 DELETE FROM emp WHERE empno=9999;
 DELETE FROM dept WHERE deptno=90;
 
 DBMS_OUTPUT.PUT_LINE('刪除后');
 Disp_dept_summary();
 DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig1’);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
 
END;

例2：創(chuàng)建DML語(yǔ)句行級(jí)觸發(fā)器拜轨。當(dāng)對(duì)emp表執(zhí)行INSERT, UPDATE, DELETE 操作時(shí)抽减，它自動(dòng)更新dept_summary 表中的數(shù)據(jù)。由于在PL/SQL塊中不能直接調(diào)用DDL語(yǔ)句橄碾，所以卵沉，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過(guò)程，由它執(zhí)行DDL語(yǔ)句創(chuàng)建觸發(fā)器法牲。

BEGIN
  DBMS_OUTPUT.PUT_LINE('插入前');
  Disp_dept_summary();
  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    'CREATE OR REPLACE TRIGGER trig2_update
      AFTER UPDATE OF sal ON emp
      REFERENCING OLD AS old_emp NEW AS new_emp
      FOR EACH ROW
      WHEN (old_emp.sal != new_emp.sal)
    BEGIN
      DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_update 觸發(fā)器…'');
      DBMS_OUTPUT.PUT_LINE(''sal 舊值：''|| :old_emp.sal);
      DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal);
      UPDATE dept_summary
        SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal
        WHERE deptno = :new_emp.deptno;
    END;'
  );
   
  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    'CREATE OR REPLACE TRIGGER trig2_insert
      AFTER INSERT ON emp
      REFERENCING NEW AS new_emp
      FOR EACH ROW
    DECLARE
      I NUMBER;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_insert 觸發(fā)器…'');
      SELECT COUNT(*) INTO I
      FROM dept_summary WHERE deptno = :new_emp.deptno;
      IF I > 0 THEN
        UPDATE dept_summary
        SET sal_sum=sal_sum+:new_emp.sal,
        Emp_count=emp_count+1
        WHERE deptno = :new_emp.deptno;
      ELSE
        INSERT INTO dept_summary
        VALUES (:new_emp.deptno, :new_emp.sal, 1);
      END IF;
    END;'
  );
 
  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    'CREATE OR REPLACE TRIGGER trig2_delete
      AFTER DELETE ON emp
      REFERENCING OLD AS old_emp
      FOR EACH ROW
    DECLARE
      I NUMBER;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_delete 觸發(fā)器…'');
      SELECT emp_count INTO I
      FROM dept_summary WHERE deptno = :old_emp.deptno;
      IF I >1 THEN
        UPDATE dept_summary
        SET sal_sum=sal_sum - :old_emp.sal,
        Emp_count=emp_count - 1
        WHERE deptno = :old_emp.deptno;
      ELSE
        DELETE FROM dept_summary WHERE deptno = :old_emp.deptno;
      END IF;
    END;'
  );
 
  INSERT INTO dept(deptno, dname, loc)
    VALUES(90, 'demo_dept', 'none_loc');
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9999, 3000);
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9998, 2000);
  DBMS_OUTPUT.PUT_LINE('插入后');
  Disp_dept_summary();
 
  UPDATE emp SET sal = sal*1.1 WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE('修改后');
  Disp_dept_summary();
 
  DELETE FROM emp WHERE deptno=90;
  DELETE FROM dept WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE('刪除后');
  Disp_dept_summary();
 
  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update');
  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert');
  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

例3：利用ORACLE提供的條件謂詞INSERTING史汗、UPDATING和DELETING創(chuàng)建與例2具有相同功能的觸發(fā)器。

BEGIN
    DBMS_OUTPUT.PUT_LINE('插入前');
    Disp_dept_summary();
    DBMS_UTILITY.EXEC_DDL_STATEMENT(
        'CREATE OR REPLACE TRIGGER trig2
            AFTER INSERT OR DELETE OR UPDATE OF sal
ON emp
            REFERENCING OLD AS old_emp NEW AS new_emp
            FOR EACH ROW
        DECLARE
            I NUMBER;
        BEGIN
            IF UPDATING AND :old_emp.sal != :new_emp.sal THEN
            DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2 觸發(fā)器…'');
                DBMS_OUTPUT.PUT_LINE(''sal 舊值：''|| :old_emp.sal);
                DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal);
                UPDATE dept_summary
                    SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal
                WHERE deptno = :new_emp.deptno;
            ELSIF INSERTING THEN
                DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2觸發(fā)器…'');
                SELECT COUNT(*) INTO I
        FROM dept_summary
        WHERE deptno = :new_emp.deptno;
                IF I > 0 THEN
                    UPDATE dept_summary
          SET sal_sum=sal_sum+:new_emp.sal,
              Emp_count=emp_count+1
          WHERE deptno = :new_emp.deptno;
            ELSE
          INSERT INTO dept_summary
            VALUES (:new_emp.deptno, :new_emp.sal, 1);
        END IF;
      ELSE
        DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2觸發(fā)器…'');
        SELECT emp_count INTO I
        FROM dept_summary WHERE deptno = :old_emp.deptno;
      IF I > 1 THEN
        UPDATE dept_summary
        SET sal_sum=sal_sum - :old_emp.sal,
        Emp_count=emp_count - 1
        WHERE deptno = :old_emp.deptno;
      ELSE
          DELETE FROM dept_summary
          WHERE deptno = :old_emp.deptno;
      END IF;
    END IF;
    END;'
  );
 
  INSERT INTO dept(deptno, dname, loc)
    VALUES(90, 'demo_dept', 'none_loc');
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9999, 3000);
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9998, 2000);
  DBMS_OUTPUT.PUT_LINE('插入后');
  Disp_dept_summary();
 
  UPDATE emp SET sal = sal*1.1 WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE('修改后');
  Disp_dept_summary();
 
  DELETE FROM emp WHERE deptno=90;
  DELETE FROM dept WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE('刪除后');
  Disp_dept_summary();
 
  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

例4：創(chuàng)建INSTEAD OF 觸發(fā)器拒垃。首先創(chuàng)建一個(gè)視圖myview, 由于該視圖是復(fù)合查詢(xún)所產(chǎn)生的視圖停撞，所以不能執(zhí)行DML語(yǔ)句。根據(jù)用戶(hù)對(duì)視圖所插入的數(shù)據(jù)判斷需要將數(shù)據(jù)插入到哪個(gè)視圖基表中悼瓮，然后對(duì)該基表執(zhí)行插入操作怜森。

DECLARE
    No NUMBER;
    Name VARCHAR2(20);
BEGIN
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE OR REPLACE VIEW myview AS
            SELECT empno, ename, ''E'' type FROM emp
            UNION
            SELECT dept.deptno, dname, ''D'' FROM dept
    ');
    -- 創(chuàng)建INSTEAD OF 觸發(fā)器trigger3;
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE OR REPLACE TRIGGER trig3
            INSTEAD OF INSERT ON myview
            REFERENCING NEW n
            FOR EACH ROW
        DECLARE
            Rows INTEGER;
        BEGIN
            DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig3觸發(fā)器…'');
            IF :n.type = ''D'' THEN
                SELECT COUNT(*) INTO rows
                    FROM dept WHERE deptno = :n.empno;
                IF rows = 0 THEN
                    DBMS_OUTPUT.PUT_LINE(''向dept表中插入數(shù)據(jù)…'');
                    INSERT INTO dept(deptno, dname, loc)
                        VALUES (:n.empno, :n.ename, ''none’’);
                ELSE
                    DBMS_OUTPUT.PUT_LINE(''編號(hào)為''|| :n.empno||
                     ''的部門(mén)已存在，插入操作失敯怠副硅！'');
                 END IF;
            ELSE
                SELECT COUNT(*) INTO rows
                    FROM emp WHERE empno = :n.empno;
                IF rows = 0 THEN
                    DBMS_OUTPUT.PUT_LINE('’向emp表中插入數(shù)據(jù)…’’);
                    INSERT INTO emp(empno, ename)
                        VALUES(:n.empno, :n.ename);
                ELSE
                    DBMS_OUTPUT.PUT_LINE(''編號(hào)為''|| :n.empno||
                      ''的人員已存在，插入操作失敗!'');
                END IF;
            END IF;
        END;
    ');
 
    INSERT INTO myview VALUES (70, 'demo', 'D');
    INSERT INTO myview VALUES (9999, USER, 'E');
    SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70;
    DBMS_OUTPUT.PUT_LINE('員工編號(hào)：'||TO_CHAR(no)||'姓名：'||name);
    SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;
    DBMS_OUTPUT.PUT_LINE('部門(mén)編號(hào)：'||TO_CHAR(no)||'姓名：'||name);
  DELETE FROM emp WHERE empno=9999;
  DELETE FROM dept WHERE deptno=70;
    DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3');
END;

例5：利用ORACLE事件屬性函數(shù)翅萤，創(chuàng)建一個(gè)系統(tǒng)事件觸發(fā)器恐疲。首先創(chuàng)建一個(gè)事件日志表eventlog，由它存儲(chǔ)用戶(hù)在當(dāng)前數(shù)據(jù)庫(kù)中所創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象套么，以及用戶(hù)的登陸和注銷(xiāo)培己、數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉等事件，之后創(chuàng)建trig4_ddl胚泌、trig4_before和trig4_after觸發(fā)器省咨，它們調(diào)用事件屬性函數(shù)將各個(gè)事件記錄到eventlog數(shù)據(jù)表中。

BEGIN
    -- 創(chuàng)建用于記錄事件日志的數(shù)據(jù)表
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE TABLE eventlog(
            Eventname VARCHAR2(20) NOT NULL,
            Eventdate date default sysdate,
            Inst_num NUMBER NULL,
            Db_name VARCHAR2(50) NULL,
            Srv_error NUMBER NULL,
            Username VARCHAR2(30) NULL,
            Obj_type VARCHAR2(20) NULL,
            Obj_name VARCHAR2(30) NULL,
            Obj_owner VARCHAR2(30) NULL
        )
    ');
 
    -- 創(chuàng)建DDL觸發(fā)器trig4_ddl
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE OR REPLACE TRIGGER trig4_ddl
            AFTER CREATE OR ALTER OR DROP
ON DATABASE
        DECLARE
            Event VARCHAR2(20);
            Typ VARCHAR2(20);
            Name VARCHAR2(30);
            Owner VARCHAR2(30);
        BEGIN
            -- 讀取DDL事件屬性
            Event := SYSEVENT;
            Typ := DICTIONARY_OBJ_TYPE;
            Name := DICTIONARY_OBJ_NAME;
            Owner := DICTIONARY_OBJ_OWNER;
            --將事件屬性插入到事件日志表中
            INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner)
                VALUES(event, typ, name, owner);
        END;
    ');
 
    -- 創(chuàng)建LOGON玷室、STARTUP和SERVERERROR 事件觸發(fā)器
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE OR REPLACE TRIGGER trig4_after
            AFTER LOGON OR STARTUP OR SERVERERROR
      ON DATABASE
        DECLARE
            Event VARCHAR2(20);
            Instance NUMBER;
            Err_num NUMBER;
            Dbname VARCHAR2(50);
            User VARCHAR2(30);
        BEGIN
            Event := SYSEVENT;
            IF event = ''LOGON'' THEN
                User := LOGIN_USER;
                INSERT INTO eventlog(eventname, username)
                    VALUES(event, user);
            ELSIF event = ''SERVERERROR'' THEN
                Err_num := SERVER_ERROR(1);
                INSERT INTO eventlog(eventname, srv_error)
                    VALUES(event, err_num);
            ELSE
                Instance := INSTANCE_NUM;
                Dbname := DATABASE_NAME;
                INSERT INTO eventlog(eventname, inst_num, db_name)
                    VALUES(event, instance, dbname);
      END IF;
    END;
  ');
 
  -- 創(chuàng)建LOGOFF和SHUTDOWN 事件觸發(fā)器
  DBMS_UTILITY.EXEC_DDL_STATEMENT('
    CREATE OR REPLACE TRIGGER trig4_before
      BEFORE LOGOFF OR SHUTDOWN
      ON DATABASE
    DECLARE
      Event VARCHAR2(20);
      Instance NUMBER;
      Dbname VARCHAR2(50);
      User VARCHAR2(30);
    BEGIN
      Event := SYSEVENT;
      IF event = ''LOGOFF'' THEN
        User := LOGIN_USER;
        INSERT INTO eventlog(eventname, username)
          VALUES(event, user);
      ELSE
        Instance := INSTANCE_NUM;
        Dbname := DATABASE_NAME;
        INSERT INTO eventlog(eventname, inst_num, db_name)
          VALUES(event, instance, dbname);
      END IF;
    END;
  ');
END;
 
CREATE TABLE mydata(mydate NUMBER);
CONNECT SCOTT/TIGER
 
COL eventname FORMAT A10
COL eventdate FORMAT A12
COL username FORMAT A10
COL obj_type FORMAT A15
COL obj_name FORMAT A15
COL obj_owner FORMAT A10
SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error
  FROM eventlog;
 
DROP TRIGGER trig4_ddl;
DROP TRIGGER trig4_before;
DROP TRIGGER trig4_after;
DROP TABLE eventlog;
DROP TABLE mydata;

例6. 復(fù)雜的審計(jì)功能零蓉，將EMP 表的變化情況記錄到AUDIT_TABLE和AUDIT_TABLE_VALUES中笤受。

CREATE TABLE audit_table(
    Audit_id     NUMBER,
    User_name VARCHAR2(20),
    Now_time DATE,
    Terminal_name VARCHAR2(10),
    Table_name VARCHAR2(10),
    Action_name VARCHAR2(10),
    Emp_id NUMBER(4));
 
CREATE TABLE audit_table_val(
    Audit_id NUMBER,
    Column_name VARCHAR2(10),
    Old_val NUMBER(7,2),
    New_val NUMBER(7,2));
 
CREATE SEQUENCE audit_seq
    START WITH 1000
    INCREMENT BY 1
    NOMAXVALUE
    NOCYCLE NOCACHE;
 
CREATE OR REPLACE TRIGGER audit_emp
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW
DECLARE
    Time_now DATE;
    Terminal CHAR(10);
BEGIN
    Time_now:=sysdate;
    Terminal:=USERENV('TERMINAL');
    IF INSERTING THEN
        INSERT INTO audit_table
    VALUES(audit_seq.NEXTVAL, user, time_now,
           terminal, 'EMP', 'INSERT', :new.empno);
    ELSIF DELETING THEN
        INSERT INTO audit_table
    VALUES(audit_seq.NEXTVAL, user, time_now,
           terminal, 'EMP', 'DELETE', :old.empno);
    ELSE
        INSERT INTO audit_table
    VALUES(audit_seq.NEXTVAL, user, time_now,
           terminal, 'EMP', 'UPDATE', :old.empno);
        IF UPDATING('SAL') THEN
            INSERT INTO audit_table_val
                VALUES(audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal);
        ELSE UPDATING('DEPTNO')
            INSERT INTO audit_table_val
                VALUES(audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno);
        END IF;
    END IF;
END;

例7. 增強(qiáng)數(shù)據(jù)的完整性管理，修改DEPT表的DEPTNO列時(shí)敌蜂，同時(shí)把EMP表中相應(yīng)的DEPTNO也作相應(yīng)的修改箩兽；

CREATE SEQUENCE update_sequence
    INCREMENT BY 1
    START WITH 1000
    MAXVALUE 5000 CYCLE;
 
ALTER TABLE emp
    ADD update_id NUMBER;
 
CREATE OR REPLACE PACKAGE integritypackage AS
    Updateseq NUMBER;
END integritypackage;
 
CREATE OR REPLACE PACKAGE BODY integritypackage AS
END integritypackage;
 
CREATE OR REPLACE TRIGGER dept_cascade1
    BEFORE UPDATE OF deptno ON dept
DECLARE
    Dummy NUMBER;
BEGIN
    SELECT update_sequence.NEXTVAL INTO dummy FROM dual;
    Integritypackage.updateseq:=dummy;
END;
 
CREATE OR REPLACE TRIGGER dept_cascade2
    AFTER DELETE OR UPDATE OF deptno ON dept
    FOR EACH ROW
BEGIN
    IF UPDATING THEN
        UPDATE emp SET deptno=:new.deptno,
     update_id=integritypackage.updateseq
        WHERE emp.deptno=:old.deptno AND update_id IS NULL;
    END IF;
    IF DELETING THEN
        DELETE FROM emp
            WHERE emp.deptno=:old.deptno;
    END IF;
END;
 
CREATE OR REPLACE TRIGGER dept_cascade3
    AFTER UPDATE OF deptno ON dept
BEGIN
    UPDATE emp SET update_id=NULL
        WHERE update_id=integritypackage.updateseq;
END;
 
SELECT * FROM EMP ORDER BY DEPTNO;
UPDATE dept SET deptno=25 WHERE deptno=20;

例8. 幫助實(shí)現(xiàn)安全控制；保證對(duì)EMP表的修改僅在工作日的工作時(shí)間章喉；

CREATE TABLE company_holidays(day DATE);
 
INSERT INTO company_holidays
    VALUES(sysdate);
INSERT INTO company_holidays
VALUES(TO_DATE('21-10月-01', 'DD-MON-YY'));
 
CREATE OR REPLACE TRIGGER emp_permit_change
    BEFORE INSERT OR DELETE OR UPDATE ON emp
DECLARE
    Dummy NUMBER;
    Not_on_weekends EXCEPTION;
    Not_on_holidays EXCEPTION;
    Not_working_hours EXCEPTION;
BEGIN
    /* check for weekends */
IF TO_CHAR(SYSDATE, 'DAY') IN ('星期六', '星期日') THEN
    RAISE not_on_weekends;
END IF;
    /* check for company holidays */
SELECT COUNT(*) INTO dummy FROM company_holidays
    WHERE TRUNC(day)=TRUNC(SYSDATE);
IF dummy >0 THEN
    RAISE not_on_holidays;
END IF;
    /* check for work hours(8:00 AM to 18:00 PM */
IF (TO_CHAR(SYSDATE,'HH24')<8 OR TO_CHAR(SYSDATE, 'HH24')>18) THEN
  RAISE not_working_hours;
END IF;
EXCEPTION
  WHEN not_on_weekends THEN
    RAISE_APPLICATION_ERROR(-20324,
'May not change employee table during the weekends');
  WHEN not_on_holidays THEN
    RAISE_APPLICATION_ERROR(-20325,
'May not change employee table during a holiday');
  WHEN not_working_hours THEN
    RAISE_APPLICATION_ERROR(-20326,
'May not change employee table during no_working hours');
END;