PLSQL编程详解

2017-03-12 本文已影响130人慢清尘

1.1 基本结构

PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。

DECLARE 
   --声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数
BEGIN
   -- 执行部分:  过程及SQL 语句  , 即程序的主要部分
EXCEPTION
   -- 执行异常部分: 错误处理
END;

1.2 命名规则

标识符	命名规则	例子
程序变量	V_name	V_name
程序常量	C_Name	C_company_name
游标变量	Cursor_Name	Cursor_Emp
异常标识	E_name	E_too_many
表类型	Name_table_type	Emp_record_type
表	Name_table	Emp
记录类型	Name_record	Emp_record
SQL*Plus 替代变量	P_name	P_sa-
绑定变量	G_name	G_year_sa-

1.3 记录类型

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表中的这几个列相同的记录数据类型
   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);
--声明一个该记录数据类型的记录变量
   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('雇员名称：'||v_emp_record.f_name
             ||'  雇佣日期：'||v_emp_record.h_date
             ||'  岗位：'||v_emp_record.j_id);
END;

1.4 数组类型

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

范例

DECLARE
--定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型
   TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25);
--声明一个该VARRAY数据类型的变量
   v_reg_varray REG_VARRAY_TYPE;
 
BEGIN
--用构造函数语法赋予初值
   v_reg_varray := reg_varray_type
         ('中国', '美国', '英国', '日本', '法国');
 
   DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_varray(1)||'、'
                                    ||v_reg_varray(2)||'、'
                                    ||v_reg_varray(3)||'、'
                                    ||v_reg_varray(4));
   DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值：'||v_reg_varray(5));
--用构造函数语法赋予初值后就可以这样对成员赋值
   v_reg_varray(5) := '法国';
   DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_varray(5));
END;

1.5 %TYPE

使用%TYPE定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致

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操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。

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||'工作时间:'||rec.hiredate);
END;

1.7 TABLE

使用TABLE定义记录表数据类型。它与记录类型相似，它可以处理多行记录，类似于二维数组来模仿数据库中的表。

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

方法	描述
EXISTS(n)	如果集合的第n个成员存在，则返回true
COUNT	返回已经分配了存储空间即赋值了的成员数量
FIRSTLAST	FIRST：返回成员的最低下标值LAST：返回成员的最高下标值
PRIOR(n)	返回下标为n的成员的前一个成员的下标。如果没有则返回NUL-
NEXT(N)	返回下标为n的成员的后一个成员的下标。如果没有则返回NUL-
TRIM	TRIM：删除末尾一个成员TRIM(n) ：删除末尾n个成员
DELETE	DELETE：删除所有成员DELETE(n) ：删除第n个成员DELETE(m, n) ：删除从n到m的成员
EXTEND	EXTEND：添加一个null成员EXTEND(n)：添加n个null成员EXTEND(n,i)：添加n个成员，其值与第i个成员相同
LIMIT	返回在varray类型变量中出现的最高下标值

范例如下

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 运算符

-- 变量赋值
variable  := expression ;

-- 空值加数字仍是空值
NULL + <数字> = NULL 

-- 空值加（连接）字符，结果为字符
NULL || <字符串> = < 字符串> 

-- CHAR 转换为 NUMBER
v_total := TO_NUMBER('100.0') + sal;

--  NUMBER 转换为CHAR
v_comm := TO_CHAR('123.45') || '元' ;

-- 字符转换为日期
v_date := TO_DATE('2001.07.03','yyyy.mm.dd');

-- 日期转换为字符
v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;

1.9 注释

使用双 ‘-‘ ( 减号) 加注释
使用 /* */ 来加一行或多行注释

条件语句IF

IF <布尔表达式> THEN
  PL/SQL 和 SQL语句
END IF;
-----------------------
IF <布尔表达式> THEN
  PL/SQL 和 SQL语句
ELSE
  其它语句
END IF;
-----------------------
IF <布尔表达式> THEN
  PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
  其它语句
ELSIF < 其它布尔表达式> THEN
  其它语句
ELSE
  其它语句
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:= '太少了,加点吧~!';
   ELSIF v_salary <3000 THEN
      V_comment:= '多了点,少点吧~!';
   ELSE
      V_comment:= '没有薪水~!';
   END IF;
   DBMS_OUTPUT.PUT_LINE(V_comment);
   exception
     when no_data_found then
        DBMS_OUTPUT.PUT_LINE('没有数据~!');
     when others then
        DBMS_OUTPUT.PUT_LINE(sqlcode || '---' || sqlerrm);        
END;

2.1 条件语句CASE

CASE 条件表达式
  WHEN 条件表达式结果1 THEN 
     语句段1
  WHEN 条件表达式结果2 THEN
     语句段2
  ......
  WHEN 条件表达式结果n THEN
     语句段n
  [ELSE 条件表达式结果]
END;

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

CASE 
  WHEN 条件表达式1 THEN
     语句段1
  WHEN 条件表达式2 THEN
     语句段2
  ......
  WHEN 条件表达式n THEN 
     语句段n
  [ELSE 语句段]
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 循环语句LOOP

 LOOP
      要执行的语句;
      EXIT WHEN <条件语句> --条件满足，退出循环语句
  END LOOP;

范例

DECLARE
    int NUMBER(2) :=0;
BEGIN
   LOOP
      int := int + 1;
      DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int);
      EXIT WHEN int =10;
   END LOOP;
END;

2.3 循环语句While

WHILE <布尔表达式> LOOP
    要执行的语句;
END LOOP;

范例

DECLARE 
  x NUMBER :=1;
BEGIN
   WHILE x<=10 LOOP
      DBMS_OUTPUT.PUT_LINE('X的当前值为:'||x);
       x:= x+1;
   END LOOP;
END;

2.4 循环语句For

[<<循环标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
  要执行的语句;
END LOOP [循环标签];

范例

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 显式游标

显式游标处理需四个 PL/SQL步骤：

定义/声明游标：就是定义一个游标名，以及与其相对应的SELECT 语句

打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行
提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中
关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开

-- 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 游标属性

Cursor_name%FOUND：布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；
Cursor_name%NOTFOUND：布尔型属性，与%FOUND相反；
Cursor_name%ISOPEN：布尔型属性，当游标已打开时返回 TRUE；
Cursor_name%ROWCOUNT：数字型属性，返回已从游标中读取的记录数。

范例

-- 给工资低于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('记录数:'|| c_cursor %ROWCOUNT);
   END LOOP;
   CLOSE c_cursor;
END; 

-- 没有参数且没有返回值的游标
DECLARE
   v_f_name employees.first_name%TYPE;
   v_j_id   employees.job_id%TYPE;
   CURSOR c1       --声明游标,没有参数没有返回值
   IS
      SELECT first_name, job_id FROM employees 
      WHERE department_id = 20;
BEGIN
   OPEN c1;        --打开游标
   LOOP
      FETCH c1 INTO v_f_name, v_j_id;    --提取游标
      IF c1%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_f_name||'的岗位是'||v_j_id);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c1;   --关闭游标
END;

-- 有参数且没有返回值的游标
DECLARE
   v_f_name employees.first_name%TYPE;
   v_h_date employees.hire_date%TYPE;
   CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值
   IS
      SELECT first_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;
BEGIN
   OPEN c2(90, 'AD_VP');  --打开游标,传递参数值
   LOOP
      FETCH c2 INTO v_f_name, v_h_date;    --提取游标
      IF c2%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇佣日期是'||v_h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c2;   --关闭游标
END;

-- 有参数且有返回值的游标
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) --声明游标,有参数有返回值
          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);  --打开游标,传递参数值
   LOOP
      FETCH c3 INTO v_emp_record;    --提取游标
      IF c3%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
                            ||v_emp_record.h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c3;   --关闭游标
END;

-- 基于游标定义记录变量
DECLARE
   CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值
   IS
      SELECT first_name f_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;
    --基于游标定义记录变量，比声明记录类型变量要方便，不容易出错
    v_emp_record c4%ROWTYPE;
BEGIN
   OPEN c4(90, 'AD_VP');  --打开游标,传递参数值
   LOOP
      FETCH c4 INTO v_emp_record;    --提取游标
      IF c4%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
                            ||v_emp_record.hire_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c4;   --关闭游标
END;

3.3 游标FOR循环

游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 游标数据处理代码
END LOOP;

index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。

范例

-- ex1
DECLARE
   CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary
   FROM employees ;
BEGIN
   --隐含打开游标
   FOR v_sal IN c_sal LOOP
   --隐含执行一个FETCH语句
      DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ;
   --隐含监测c_sal%NOTFOUND
   END LOOP;
--隐含关闭游标
END;

-- ex2 当所声明的游标带有参数时，通过游标FOR 循环语句为游标传递参数
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('当dept_no参数值为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)||'使用默认的dept_no参数值10：');
    FOR c1_rec IN c_cursor LOOP        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
    END LOOP;
END;

不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR循环的记录

3.4 处理隐式游标

对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。

格式调用为： SQL%

范例

--  删除EMPLOYEES表中某部门的所有员工，如果该部门中已没有员工，则在DEPARTMENT表中删除该部门
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;

-- 通过隐式游标SQL的%ROWCOUNT属性来了解修改了多少行
DECLARE
   v_rows NUMBER;
BEGIN
--更新数据
   UPDATE employees SET salary = 30000
   WHERE department_id = 90 AND job_id = 'AD_VP';
--获取默认游标的属性值
   v_rows := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
--回退更新，以便使数据库的数据保持原样
   ROLLBACK;
END;

3.5 NO_DATA_FOUND 和 %NOTFOUND的区别

SELECT … INTO 语句触发 NO_DATA_FOUND；
当一个显式游标的WHERE子句未找到时触发%NOTFOUND；
当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND；
在提取循环中要用 %NOTFOUND 或%FOUND来确定循环的退出条件，不要用 NO_DATA_FOUND

3.6 使用游标更新和删除数据

为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。

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

范例

-- 从EMPLOYEES表中查询某部门的员工情况，将其工资最低定为 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表中部门编码为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;  --更新当前游标行对应的数据行
      END IF;
   END LOOP;
   COMMIT;   --提交已经修改的数据
   CLOSE c1;
END;

3.7 游标变量

游标变量操作也包括打开、提取和关闭三个步骤。

1．打开游标变量
打开游标变量时使用的是OPEN…FOR 语句。格式为：

OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;

cursor_variable_name 为游标变量，host_cursor_variable_name 为PL/SQL主机环境（如OCI: ORACLE Call Interface，Pro*c 程序等）中声明的游标变量。
OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量，而不会导致 CURSOR_ALREAD_OPEN 异常错误。新打开游标变量时，前一个查询的内存处理区将被释放。

2 . 提取游标变量数据
使用FETCH语句提取游标变量结果集合中的数据。格式为：

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

cursor_variable_name 和 host_cursor_variable_name 分别为游标变量和宿主游标变量名称； variable 和 record_variable 分别为普通变量和记录变量名称。

3．关闭游标变量
CLOSE语句关闭游标变量，格式为：

CLOSE {cursor_variable_name | :host_cursor_variable_name}

cursor_variable_name 和 host_cursor_variable_name 分别为游标变量和宿主游标变量名称，如果应用程序试图关闭一个未打开的游标变量，则将导致 INVALID_CURSOR 异常错误

-- 强类型参照游标变量类型
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;

-- 弱类型参照游标变量类型
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;

-- 使用游标变量（没有RETURN子句）
DECLARE
--定义一个游标数据类型
   TYPE emp_cursor_type IS REF CURSOR;
--声明一个游标变量
   c1 EMP_CURSOR_TYPE;
--声明两个记录变量
   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;
--将同一个游标变量对应到另一个SELECT语句
   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;

-- 使用游标变量（有RETURN子句）
DECLARE
--定义一个与employees表中的这几个列相同的记录数据类型
   TYPE emp_record_type IS RECORD(
        f_name   employees.first_name%TYPE,
        h_date   employees.hire_date%TYPE,
        j_id     employees.job_id%TYPE);
--声明一个该记录数据类型的记录变量
   v_emp_record EMP_RECORD_TYPE;
--定义一个游标数据类型
   TYPE emp_cursor_type IS REF CURSOR
        RETURN EMP_RECORD_TYPE;
--声明一个游标变量
   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('雇员名称：'||v_emp_record.f_name
                ||'  雇佣日期：'||v_emp_record.h_date
                ||'  岗位：'||v_emp_record.j_id);
   END LOOP;
   CLOSE c1;
END;

4.1 异常处理概念

有三种类型的异常错误：

1．预定义错误：ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
2． 非预定义错误：即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
3． 用户定义错误：程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

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 预定义的异常处理

只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	违反了唯一性限制
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-CURSOR	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found	SELECT INTO没有找到数据
ORA-1422	Too_many_rows	SELECT INTO 返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	内部错误
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	宿主游标变量与 PL/SQL变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已处于打开状态的游标
ORA-6530	Access-INTO-nul-	试图为null 对象的属性赋值
ORA-6531	Collection-is-nul-	试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上
ORA-6532	Subscript-outside-limit	对嵌套或varray索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或varray 索引得引用大于集合中元素的个数.

-- 更新指定员工工资，如工资小于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||'员工工资已经超过规定值!');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
      DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

4.3 非预定义的异常处理

必须对非定义的ORACLE错误进行定义。步骤如下：

1 . 定义异常情况 <异常情况> EXCEPTION;
2 . 将定义异常情况，与标准的ORACLE错误联系起来： PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);
3 . 在异常情况处理部分对异常情况做出相应的处理。

-- 删除指定部门的记录信息，以确保该部门没有员工。
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 是违反一致性约束的错误代码 */
BEGIN
   DELETE FROM departments WHERE department_id = v_deptno;
EXCEPTION
   WHEN deptno_remaining THEN 
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

4.4 用户自定义的异常处理

用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。对于这类异常情况的处理，步骤如下：

1 . 定义异常情况 <异常情况> EXCEPTION;
2 . RAISE <异常情况>；
3 . 在异常情况处理部分对异常情况做出相应的处理

-- 更新指定员工工资，增加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('你的数据更新语句失败了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

4.5 自定义异常代码

调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。
RAISE_APPLICATION_ERROR 的语法如下：

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );

这里的 error_number 是从 –20,000 到 –20,999 之间的参数，
error_message 是相应的提示信息(< 2048 字节)，
keep_errors 为可选，如果 keep_errors =TRUE ,则新错误将被添加到已经引发的错误列表中。如果 keep_errors=FALSE(缺省), 则新错误将替换当前的错误列表。

-- 创建一个函数get_salary, 该函数检索指定部门的工资总和，其中定义了-20991和-20992号错误，分别处理参数为空和非法部门代码两种错误：
 
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, ’部门代码为空’);
  ELSIF p_deptno<0 THEN
    RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’);
  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号部门工资：' || 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('部门号为20的工资为：'||TO_CHAR(V_salary));

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

  V_salary := get_salary(30);
  DBMS_OUTPUT.PUT_LINE('部门号为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;


-- 定义触发器，使用RAISE_APPLICATION_ERROR阻止没有员工姓名的新员式记录插入：
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 异常错误处理编程

由于ORACLE 的错信息最大长度是512字节，为了得到完整的错误提示信息，我们可用 SQLERRM和 SUBSTR 函数一起得到错误提示信息，方便进行错误，特别是如果WHEN OTHERS异常处理器时更为方便。

SQLCODE  返回遇到的Oracle错误号,
SQLERRM  返回遇到的Oracle错误信息.
 
如:  SQLCODE=-100   è SQLERRM=’no_data_found ‘
 SQLCODE=0      è SQLERRM=’normal, successfual completion’

范例

-- 将ORACLE错误代码及其信息存入错误代码表
CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100));

DECLARE
   err_msg  VARCHAR2(100);
BEGIN
   /*  得到所有 ORACLE 错误信息  */
   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;

 
 
-- 查询ORACLE错误代码；
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(2222, 'Eric','Hu', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
   
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(2222, '胡','勇', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

 
-- 利用ORACLE错误代码，编写异常错误处理代码；
DECLARE
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
   /* -1 是违反唯一约束条件的错误代码 */
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, 'Eric','Hu', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
   
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, '胡','勇',SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
   WHEN empno_remaining THEN 
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

5.1 过程与函数

过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

5.2 创建函数

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
    <类型.变量的声明部分>
BEGIN
    执行部分
    RETURN expression
EXCEPTION
    异常处理部分
END function_name;

IN, OUT, IN OUT是形参的模式。若省略，则为 IN 模式。 IN 模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。 OUT 模式的形参会忽略调用时的实参值（或说该形参的初始值总是 NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。 IN OUT 具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于 IN 模式的实参可以是常量或变量，但对于 OUT 和 IN OUT模式的实参必须是变量。只有在确认function_name函数是新函数或是要更新的函数时，才使用OR REPALCE关键字，否则容易删除有用的函数。

--获取某部门的工资总和
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('你需要的数据不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END get_salary;

5.3 函数调用

在调用函数时，可以使用以下三种方法：

位置表示法：按形参的排列顺序，依次写出实参的名称，而将形参与实参关联起来进行传递 argument_value1[,argument_value2 …]
名称表示法：写出实参对应的形参，而将形参与实参关联起来进行传递 argument => parameter [,…]
组合传递：同时使用位置表示法和名称表示法为函数传递参数，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。

-- 位置表示法
DECLARE
  V_num NUMBER;
  V_sum NUMBER;
BEGIN
  V_sum :=get_salary(10, v_num);
  DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);
END;

-- 名称表示法
DECLARE
  V_num NUMBER;
    V_sum NUMBER;
BEGIN
    V_sum :=get_salary(emp_count => v_num, dept_no => 10);
    DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);
END;

-- 组合传递
CREATE OR REPLACE FUNCTION demo_fun(
  Name VARCHAR2,--注意VARCHAR2不能给精度，如：VARCHAR2(10)，其它类似
  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 参数默认值

在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。

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;

具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。

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 存储过程

创建过程语法:

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
  <执行部分>
EXCEPTION
  <可选的异常错误处理程序>
END procedure_name;

范例

-- 用户连接登记记录；  
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('温馨提示:你需要的数据不存在!');
   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 是违反唯一约束条件的错误代码 */
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('温馨提示:插入数据记录成功!');
EXCEPTION
   WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END InsertEmp;

-- 使用存储过程向departments表中插入数据。 
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('插入部门'||v_dept_id||'成功');
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      RAISE_APPLICATION_ERROR(-20000, '部门编码不能重复');
   WHEN ept_null_error THEN
      RAISE_APPLICATION_ERROR(-20001, '部门编码、部门名称不能为空');
   WHEN ept_no_loc_id THEN
      RAISE_APPLICATION_ERROR(-20002, '没有该地点');
END insert_dept;
 
/** 调用实例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, '部门300', 100, 2400);
   insert_dept(310, NULL, 100, 2400);
   insert_dept(310, '部门310', 100, 900);
EXCEPTION
   WHEN ept_20000 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');
   WHEN ept_20001 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');
   WHEN ept_20002 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');
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 => '部门310', v_dept_id => 310,
               v_mgr_id => 100, v_loc_id => 2400);
   insert_dept(320, '部门320', v_mgr_id => 100, v_loc_id => 900);
EXCEPTION
   WHEN ept_20000 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');
   WHEN ept_20001 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');
   WHEN ept_20002 THEN
      DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');
END;
**/

5.6 调用存储过程

存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。对于参数的传递也有三种：按位置传递、按名称传递和组合传递，传递方法与函数的一样。ORACLE 使用EXECUTE 语句来实现对存储过程的调用： EXEC[UTE] procedure_name( parameter1, parameter2…);

-- 查询指定员工记录； 
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||'的员工已经查到!');
EXCEPTION
       WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END QueryEmp;
--调用
 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;


-- 计算指定部门的工资总和，并统计其中的职工数量。 
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('温馨提示:你需要的数据不存在!');
   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号部门工资总和：'||v_sum||',人数：'||v_num);
  Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num);
END;

在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。

-- 建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量； 
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('你需要的数据不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;
--调用方法：
BEGIN
    Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);
    Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num);
END;

5.7 AUTHID

过程中的AUTHID 指令可以告诉ORACLE ，这个过程使用谁的权限运行．默任情况下，存储过程会作为调用者的过程运行，但是具有设计者的特权．这称为设计者权利运行．

-- 建立过程，使用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;

-- 建立过程，使用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可以支持事务处理中的事务处理的概念。这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行。

-- 建立过程，使用自动事务处理进行日志记录；
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;

-- 建立过程，没有使用自动事务处理进行日志记录； 
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 开发存储过程步骤

开发存储过程、函数、包及触发器的步骤如下：
1 . 使用文字编辑处理软件编辑存储过程源码
2 . 在SQLPLUS或用调试工具将存储过程程序进行解释 START c:\stat.sql
3 . 调试源码直到正确：a)使用SHOW ERROR提示错误位置；b)使用 user_errors数据字典查看各存储过程的错误位置
4 . 授权执行权给相关的用户或角色：如果存储过程没有授权，只有建立者才可以运行。可以用GRANT命令来进行存储过程的运行授权。

-- GRANT语法 
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 删除过程和函数

1．删除过程 DROP PROCEDURE [user.]Procudure_name;
2 . 删除函数 DROP FUNCTION [user.]Function_name;

5.11 过程与函数的比较

**过程与函数具有如下优点： **

共同使用的代码可以只需要被编写和测试一次，而被需要该代码的任何应用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL库）调用。
这种集中编写、集中维护更新、大家共享（或重用）的方法，简化了应用程序的开发和维护，提高了效率与性能。
这种模块化的方法，使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写、调试。因此使程序的结构清晰、简单，也容易实现
可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。
节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。并且，如果多个用户要执行相同的过程或函数时，就只需要在内存中加载一个该过程或函数。
提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中，就可以通过是否授予用户有执行该过程或的权限，来限制某些用户对数据进行这些操作。

过程与函数的相同功能有：

都使用IN模式的参数传入数据、OUT模式的参数返回数据。
输入参数都可以接受默认值，都可以传值或传引导。
调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
都有声明部分、执行部分和异常处理部分。
其管理过程都有创建、编译、授权、删除、显示依赖关系等。

使用过程与函数的原则：

如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。
过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。
可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。

6.1 触发器类型

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

DML触发器：在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。
替代触发器：不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE专门为进行视图操作的一种处理方法。
系统触发器：可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

6.2 触发器组成

触发事件：引起触发器被触发的事件。例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。
触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。
触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL 块。
** 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。
触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。
** 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；
行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

6.3 触发器注意点

触发器不接受参数
一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。
触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。
在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。
触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。
在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。
在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。
不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

6.4 创建触发器

创建触发器的一般语法是:

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 指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

FOR EACH ROW选项说明触发器为行触发器。
行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。
当省略FOR EACH ROW选项时，BEFORE 和 AFTER 触发器为语句触发器，而 INSTEAD OF 触发器则只能为行触发器。
REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和 WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为 OLD 和 NEW 。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号:，但在 WHEN 子句中则不能加冒号:。
WHEN 子句说明触发约束条件。 Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中，不能用在 INSTEAD OF 行触发器和其它类型的触发器中。

当一个基表被修改( `INSERT` ,  `UPDATE`,  `DELETE` )时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

每张表最多可建立12 种类型的触发器，它们是:

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 触发器触发次序

执行 BEFORE语句级触发器;
对与受语句影响的每一行
2.1 执行 BEFORE行级触发器
2.2 执行 DML语句
2.3 执行 AFTER行级触发器
执行 AFTER语句级触发器

6.6 创建DML触发器

触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。

DML触发器的限制

CREATE TRIGGER语句文本的字符长度不能超过32KB；
触发器体内的 SELECT 语句只能为 SELECT … INTO …结构，或者为定义游标所使用的 SELECT 语句。
触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；
由触发器所调用的过程或函数也不能使用数据库事务控制语句；
触发器中不能使用 LONG, LONG RAW 类型；
触发器内可以参照 LOB 类型列的列值，但不能通过 : NEW 修改 LOB 列中的数据；

DML触发器基本要点

触发时机：指定触发器的触发时间。如果指定为 BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。
触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。
条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。
1） INSERTING ：当触发事件是 INSERT 时，取值为TRUE，否则为FALSE
2） UPDATING [（column_1,column_2,…,column_x）]：当触发事件是 UPDATE 时，如果修改了 column_x 列，则取值为 TRUE，否则为 FALSE 。其中 column_x 是可选的
3） DELETING ：当触发事件是 DELETE 时，则取值为 TRUE ，否则为 FALSE
解发对象：指定触发器是创建在哪个表、视图上。
触发类型：是语句级还是行级触发器。
触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、操作后列的值
:NEW 修饰符访问操作完成后列的值，:OLD 修饰符访问操作完成前列的值

特性	INSERT	UPDATE	DELETE
OLD	NULL	实际值	实际值
NEW	实际值	实际值	NULL

-- 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去
CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
   BEFORE DELETE --指定触发时机为删除操作前触发
   ON scott.emp
   FOR EACH ROW   --说明创建的是行级触发器
BEGIN
   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。
   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;

-- 限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，
-- 即不允许在非工作时间修改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, '不是上班时间，不能修改departments表');
 END IF;
END;

-- 限定只对部门号为80的记录进行行触发器操作。 
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, '部门80的人员的工资不能降');
        END IF;
     WHEN UPDATING ('commission_pct') THEN
 
        IF :NEW.commission_pct < :old.commission_pct THEN
           RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');
        END IF;
     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');
     END CASE;
END;
 

-- 利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），
-- 级联的、自动的更新子表countries表中原来在该地区的国家的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;

-- 在触发器中调用过程。 
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;
 
--创建触发器调用存储过程...
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 创建替代(INSTEAD OF)触发器

创建替代触发器的一般语法是:

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 ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。

FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略 FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。
REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和 WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为 OLD 和 NEW 。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号:，但在WHEN子句中则不能加冒号:
WHEN 子句说明触发约束条件。 Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。 WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。
INSTEAD_OF 用于对视图的DML触发，由于视图有可能是由多个表进行联结 join 而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：

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: 此视图的数据操纵操作非法 
-- 但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：  

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;

创建复杂视图，针对INSERT操作创建INSTEAD OF触发器，向复杂视图插入数据。

-- 创建视图：
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;

-- 创建触发器： 
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;

创建INSTEAD OF触发器需要注意以下几点：

只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
不能指定BEFORE 或 AFTER选项。
FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。

6.8 创建系统事件触发器

ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：

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 ：一个或多个DDL 事件，事件间用 OR 分开；

database_event_list ：一个或多个数据库事件，事件间用 OR 分开；
系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。

系统触发器的种类和事件出现的时机表（前或后）

事件	允许的时机	说明
STARTUP	AFTER	启动数据库实例之后触发
SHUTDOWN	BEFORE	关闭数据库实例之前触发（非正常关闭不触发）
SERVERERROR	AFTER	数据库服务器发生错误之后触发
LOGON	AFTER	成功登录连接到数据库后触发
LOGOFF	BEFORE	开始断开数据库连接之前触发
CREATE	BEFORE，AFTER	在执行CREATE语句创建数据库对象之前、之后触发
DROP	BEFORE，AFTER	在执行DROP语句删除数据库对象之前、之后触发
ALTER	BEFORE，AFTER	在执行ALTER语句更新数据库对象之前、之后触发
DDL	BEFORE，AFTER	在执行大多数DDL语句之前、之后触发
GRANT	BEFORE，AFTER	执行GRANT语句授予权限之前、之后触发
REVOKE	BEFORE，AFTER	执行REVOKE语句收权限之前、之后触犯发
RENAME	BEFORE，AFTER	执行RENAME语句更改数据库对象名称之前、之后触犯发
AUDIT / NOAUDIT	BEFORE，AFTER	执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

6.9 系统触发器事件属性

事件属性\事件	Startup/Shutdown	Servererror	Logon/Logoff	DDL	DML
事件名称	＊	＊	＊	＊	＊
数据库名称	＊
数据库实例号	＊
错误号		＊
用户名			＊	＊
模式对象类型				＊	＊
模式对象名称				＊	＊
列					＊

除DML语句的列属性外，其余事件属性值可通过调用ORACLE定义的事件属性函数来读取

函数名称	数据类型	说明
Ora_sysevent	VARCHAR2（20）	激活触发器的事件名称
Instance_num	NUMBER	数据库实例名
Ora_database_name	VARCHAR2（50）	数据库名称
Server_error(posi)	NUMBER	错误信息栈中posi指定位置中的错误号
Is_servererror(err_number)	BOOLEAN	检查err_number指定的错误号是否在错误信息栈中，如果在则返回TRUE，否则返回FALSE。在触发器内调用此函数可以判断是否发生指定的错误。
Login_user	VARCHAR2(30)	登陆或注销的用户名称
Dictionary_obj_type	VARCHAR2(20)	DDL语句所操作的数据库对象类型
Dictionary_obj_name	VARCHAR2(30)	DDL语句所操作的数据库对象名称
Dictionary_obj_owner	VARCHAR2(30)	DDL语句所操作的数据库对象所有者名称
Des_encrypted_password	VARCHAR2(2)	正在创建或修改的经过DES算法加密的用户口令

-- ex1. 创建触发器，存放有关事件信息
DESC ora_sysevent
DESC ora_login_user
 
--创建用于记录事件用的表
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));
 
--创建触犯发器
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. 创建登录、退出触发器。 
CREATE TABLE log_event
(user_name VARCHAR2(10),
 address VARCHAR2(20),
 logon_date timestamp,
 logoff_date timestamp);
 
--创建登录触发器
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;

--创建退出触发器
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 使用触发器谓词

ORACLE 提供三个参数INSERTING, UPDATING, DELETING 用于判断触发了哪些操作。

谓词	行为
INSERTING	如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE
UPDATING	如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE
DELETING	如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE

6.11 重新编译触发器

如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。可以调用ALTER TRIGGER语句重新编译已经创建的触发器，格式为：

ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]

6.12 删除和使能触发器

删除触发器：DROP TRIGGER trigger_name;

当删除其他用户模式中的触发器名称，需要具有DROP ANY TRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTER DATABASE TRIGGER系统权限。
此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。

禁用或启用触发器： ALTER TIGGER trigger_name [DISABLE | ENABLE ];

(1) 有效状态ENABLE：当触发事件发生时，处于有效状态的数据库触发器 TRIGGER 将被触发。
(2) 无效状态DISABLE：当触发事件发生时，处于无效状态的数据库触发器 TRIGGER 将不会被触发，此时就跟没有这个数据库触发器TRIGGER 一样。

ALTER TRIGGER语句一次只能改变一个触发器的状态，而ALTER TABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。

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

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

6.13 触发器的应用举例

例1. 创建一个DML语句级触发器，当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。

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;
 
--创建一个PL/SQL过程disp_dept_summary
--在触发器中调用该过程显示dept_summary标中的数据。
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(''正在执行trig1 触发器…'');
      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：创建DML语句行级触发器。当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。

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(''正在执行trig2_update 触发器…'');
      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(''正在执行trig2_insert 触发器…'');
      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(''正在执行trig2_delete 触发器…'');
      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创建与例2具有相同功能的触发器。

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(''正在执行trig2 触发器…'');
                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(''正在执行trig2触发器…'');
                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(''正在执行trig2触发器…'');
        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：创建INSTEAD OF 触发器。首先创建一个视图myview, 由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作。

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
    ');
    -- 创建INSTEAD OF 触发器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(''正在执行trig3触发器…'');
            IF :n.type = ''D'' THEN
                SELECT COUNT(*) INTO rows
                    FROM dept WHERE deptno = :n.empno;
                IF rows = 0 THEN
                    DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…'');
                    INSERT INTO dept(deptno, dname, loc)
                        VALUES (:n.empno, :n.ename, ''none’’);
                ELSE
                    DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||
                     ''的部门已存在，插入操作失败！'');
                 END IF;
            ELSE
                SELECT COUNT(*) INTO rows
                    FROM emp WHERE empno = :n.empno;
                IF rows = 0 THEN
                    DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’);
                    INSERT INTO emp(empno, ename)
                        VALUES(:n.empno, :n.ename);
                ELSE
                    DBMS_OUTPUT.PUT_LINE(''编号为''|| :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('员工编号：'||TO_CHAR(no)||'姓名：'||name);
    SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;
    DBMS_OUTPUT.PUT_LINE('部门编号：'||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事件属性函数，创建一个系统事件触发器。首先创建一个事件日志表eventlog，由它存储用户在当前数据库中所创建的数据库对象，以及用户的登陆和注销、数据库的启动和关闭等事件，之后创建trig4_ddl、trig4_before和trig4_after触发器，它们调用事件属性函数将各个事件记录到eventlog数据表中。

BEGIN
    -- 创建用于记录事件日志的数据表
    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
        )
    ');
 
    -- 创建DDL触发器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;
    ');
 
    -- 创建LOGON、STARTUP和SERVERERROR 事件触发器
    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;
  ');
 
  -- 创建LOGOFF和SHUTDOWN 事件触发器
  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. 复杂的审计功能，将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. 增强数据的完整性管理，修改DEPT表的DEPTNO列时，同时把EMP表中相应的DEPTNO也作相应的修改；

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. 帮助实现安全控制；保证对EMP表的修改仅在工作日的工作时间；

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;

PLSQL编程详解

1.1 基本结构

1.2 命名规则

1.3 记录类型

1.4 数组类型

1.5 %TYPE

1.6 %ROWTYPE

1.7 TABLE

1.8 运算符

1.9 注释

条件语句IF

2.1 条件语句CASE

2.2 循环语句LOOP

2.3 循环语句While

2.4 循环语句For

3.1 显式游标

3.2 游标属性

3.3 游标FOR循环

3.4 处理隐式游标

3.5 NO_DATA_FOUND 和 %NOTFOUND的区别

3.6 使用游标更新和删除数据

3.7 游标变量

4.1 异常处理概念

4.2 预定义的异常处理

4.3 非预定义的异常处理

4.4 用户自定义的异常处理

4.5 自定义异常代码

4.6 异常错误处理编程

5.1 过程与函数

5.2 创建函数

5.3 函数调用

5.4 参数默认值

5.5 存储过程

5.6 调用存储过程

5.7 AUTHID

5.8 PRAGMA AUTONOMOUS_TRANSACTION

5.9 开发存储过程步骤

5.10 删除过程和函数

5.11 过程与函数的比较

6.1 触发器类型

6.2 触发器组成

6.3 触发器注意点

6.4 创建触发器

6.5 触发器触发次序

6.6 创建DML触发器

6.7 创建替代(INSTEAD OF)触发器

6.8 创建系统事件触发器

6.9 系统触发器事件属性

6.10 使用触发器谓词

6.11 重新编译触发器

6.12 删除和使能触发器

6.13 触发器的应用举例

猜你喜欢

热点阅读