PL/SQL编程详解

1.1 基本结构

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

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

1.2 命名规则

标识符	命名规则	例子
程序变量	V_Name	V_Date
程序常量	C_Name	C_Server
游标变量	Cursor_Name	Cursor_Emp
异常标识	E_Name	E_Dep
表类型	Name_table_type	Emp_record_type
表	Name_Table	Dep_
记录类型	Name_Record	Dep_record
SQL*PLUS代替变量	P_Name	P_Num
绑定变量	G_Name	G_Day

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 RECOAR_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 RECOAR_TYPE_EMPLOYEES；

BEGIN
   SELECT first_name,hire_date,job_id INTO v_emp_record
   FROM employees
   WHERE employees.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 array_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_type 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.PUTLINE('第五个成员的值是：' || 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_sal    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_PUTOUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date)

1.6 %ROWTYPE

使用%ROWTYPE操作符，返回一个记录类型，其数据类型和数据库表的数据结构一致。

DEcLARE
   v_empno emp.empno%TYPE;
   rec emp%ROWTYPE;
BEGIN
   SELECT * INTO rec
   FROM emp WHERE emp,empno = v_empno;
   DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);
END;

1.7 TABLE

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

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

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

范例如下

DECLARE
   TYPE dept_table_type IS TABLE OF
      dept%TYPE INDEX BY BINARY_INTEGER;
   my_dname_table dept_table_type;
   v_count NUMBER(2) :=4;
   v_i NUMBER(2);
BEGIN 
   FOR i in 1 .. v_count LOOP
      SELECT * INTO my_dname_table(i) 
      FROM dept WHERE deptno = i ;
   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 || <STR> = <STR>;
-- CHAR 转化为NUMBER
v_total := TO_NUMBER('100.00') + sal;
-- NUMBER转化为CHAR
v_comm := TO_CHAR('123.45') || '元'；
-- 字符转换为日期
v_date := TO_DATE('2023.07.03','yyyy.mm.dd');
-- 日期转换为字符
v_to_day := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') ;

1.9 注释

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

2.0 条件语句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 [循环标签];

范例

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 );
   --REVERSE 倒序遍历
   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
  --打开游标c1
  OPEN c1;
  --开始循环，这里是为了循环游标c1里的所有列
  LOOP
    --提取游标c1的成员的值放入变量dept_name和dept_loc中
    FETCH c1 INTO dept_name, dept_loc;
    --判断最近一次FETCT的c1是否存在，不存咋退出，结束循环。
    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;

    --游标c3可设置自定义变量，来控制游标的值
    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_sal    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-null	试图为null 对象的属性赋值
ORA-6531	Collection-is-null	试图将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
 var VARCHAR(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;

5.7 AUTHID

这里首先需要明白定义者权限和调用者权限的区别。

定义者权限（Difiner Right）：定义者权限是程序的默认权限。如果是在用户A下创建的程序，但其他用户只要能执行这个程序，那么这个程序所执行的任务都是以用户A的名义来执行的。因为用户A是程序的定义者。用户A能做什么，那这个程序就能做什么。

调用者权限（Invoker Right）：也叫执行者权限。如果某个程序中含有创建表的操作，且这个表只有用户A有创建权限，那么这个程序在用户A下面才执行成功，在其他用户下是不能成功执行的。

程序中没有AUTHID CURRENT_USER表示定义者权限，以定义者身份执行；程序中加上AUTHID CURRENT_USER表示调用者权限，以调用者身份执行。

调用者权限与定义者权限之间的差异主要体现在三个方面：

1. 执行的SCHEMA不同，操作的对象也不同

• 在定义者权限下，执行的用户为定义者，所操作的对象是定义者在编译时指定的对象。

• 在调用者权限下，执行的用户为当前用户，所操作的对象是当前模式下的对象。

2. 执行的权限不同

• 在定义者权限下，当前用户的权限为角色无效情况下所拥有的权限。

• 在调用者权限下，当前用户的权限为当前所拥有的权限（含角色）。

3. 执行的效率不同

• 在定义者权限下，过程被静态编译静态执行，所执行SQL语句在共享区池中是可被共享使用的。

• 在调用者权限下，过程静态编译，但动态执行，虽然执行的语句相同，但不同用户执行，其SQL语句在共享池中并不能共享。

[AUTHID DEFINER]

-- 建立过程，使用AUTOID DEFINER； 
--使用HR用户登录
Connect HR/qaz
--新建logtable日志表
DROP TABLE logtable;
CREATE table logtable (userid VARCHAR2(10), logdate date);
--新建过程/procedure,权限模式为定义者权限
CREATE OR REPLACE PROCEDURE logexecution
    AUTHID DEFINER
IS
BEGIN
   INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;
--将logexecution的权限更改为public
GRANT EXECUTE ON logexecution TO PUBLIC;
--使用SYSDBA用户登录
CONNECT / AS SYSDBA
--创建testuser1用户
GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1;
--使用testuser1登录
CONNECT testuser1/userpwd1
--向HR.LOGTABLE表中插入数据
INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE);
--执行HR.logexecution存储过程，插入数据库的为 HR 定义者的用户信息
EXECUTE HR.logexecution
--使用HR用户登录
CONNECT HR/qaz
--查看表内容
SELECT * FROM HR.logtable;

[AUTHID CURRENT_USER]

-- 建立过程，使用AUTOID 权限模式为定义者权限。
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);
--执行方法插入数据库的为 testuser1 当前用户信息
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 /home/stat.sql
3. 调试源码直到正确：使用SHOW ERROR提示错误位置；使用 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、++、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 触发器的触发次序

1. 执行 BEFORE语句级触发器;
2. 对与受语句影响的每一行
   1. 执行 BEFORE行级触发器
   2. 执行 DML语句
   3. 执行 AFTER行级触发器
3. 执行 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子句中则不能加冒号: