【Oracle】PLSQL学习笔记

雨点打透心脏的1/2处 2023-10-02 01:25 24阅读 0赞

**目录**

1、PLSQL简介

1.1、优点

2、PLSQL块

2.1、命名规范实例

2.2、基本语法

2.3、结构

3、PLSQL变量

3.1、变量申明

3.2、%type声明

3.3、变量赋值

3.4、dbms\_output.put\_line()

3.5、数据库赋值

3.6、记录类型

3.7、可转换的类型赋值

4、流程控制语句

4.1、控制语句

4.2、循环语句

4.3、顺序语句

5、游标(cursor)使用

5.1、显示游标

5.2、游标属性

5.3、游标的for循环

5.4、携带参数

5.5、隐式游标

5.6、bulk collect into

6、异常(例外)处理

6.1、预定义异常处理

6.2、非预定义的异常处理

6.3、用户自定义的异常处理

6.4、使用SQLcode和SQLerrm

7、存储函数和过程

7.1、创建函数

7.1.1、建立内嵌函数

7.1.2、内嵌函数的调用

7.1.3、参数默认值

7.2、存储过程

7.2.1、创建过程

7.2.2、调用存储过程

7.3、AUTHID

7.4、查看错误信息

7.5、删除过程和函数

8、包的创建和应用

8.1、包的好处

8.2、包的定义

8.3、包的开发步骤

8.4、包的定义说明

8.5、删除包

9、触发器

9.1、触发器类型

9.1.1、DML触发器

9.1.2、替代触发器

9.1.3、系统触发器

9.2、创建触发器

9.2.1、创建DML触发器

9.2.2、创建替代（instead）触发器

9.2.3、创建系统事件触发器

9.2.4、删除触发器

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

# 1、PLSQL简介 #

PL/SQL也是一种程序语言（Procedural Language/SQL）。**PL/SQL是Oracle数据库对SQL语句的扩展**。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。

PLSQL提供了典型的高级语言特性，包括封装，例外处理机制，信息隐藏，面向对象等；并把最新的编程思想带到了数据库服务器和工具集中。

## 1.1、优点 ##

与Java, C\#相比 ，PLSQL的优势是：

*  SQL语言可以直接写到PLSQL的“块”中或者是PLSQL的过程、函数中。
 *  没有必要向java那样先创建Statement对象来执行SQL;
 *  这使得PLSQL成为很强大的事务处理语言，即：使用SQL来处理数据，使用控制结构来处理业务逻辑。

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_17_color_FFFFFF_t_70_g_se_x_16][]

# 2、PLSQL块 #

PL/SQL程序由三个块组成：

*  声明部分(DECLARE)
 *  执行部分(BEGIN.......END)
 *  异常处理部分(EXCEPTION)

**注意：执行部分是必要的**

## 2.1、命名规范实例 ##

<table> 
 <thead> 
  <tr> 
   <th>标识符</th> 
   <th>命名规则</th> 
   <th>例子</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>程序变量</td> 
   <td>v_name</td> 
   <td>v_name</td> 
  </tr> 
  <tr> 
   <td>程序常量</td> 
   <td>c_name</td> 
   <td>c_company_name</td> 
  </tr> 
  <tr> 
   <td>游标变量</td> 
   <td>name_cursor</td> 
   <td>emp_cursor</td> 
  </tr> 
  <tr> 
   <td>异常标识</td> 
   <td>e_name</td> 
   <td>e_too_many</td> 
  </tr> 
  <tr> 
   <td>表类型</td> 
   <td>name_table_type</td> 
   <td>emp_record_type</td> 
  </tr> 
  <tr> 
   <td>表</td> 
   <td>name_table</td> 
   <td>emp</td> 
  </tr> 
  <tr> 
   <td>记录类型</td> 
   <td>name_record</td> 
   <td>emp_record</td> 
  </tr> 
  <tr> 
   <td>SQL*Plus替代变量</td> 
   <td>p_name</td> 
   <td>p_sal</td> 
  </tr> 
  <tr> 
   <td>绑定变量</td> 
   <td>g_name</td> 
   <td>g_year_sal</td> 
  </tr> 
 </tbody> 
</table>

**PLSQL块分为三类：**

*  **无名块**：动态构造，只能执行一次
 *  **子程序**：存储在数据库中的**存储过程、函数**及包等。当在数据库上建立好后可以在其它程序中调用它们。
 *  **触发器**：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

## 2.2、基本语法 ##

--格式
    declare
      --申明的变量、类型、游标
    begin
      --程序的执行部分（相当于java的main（）方法）
      dbms_output.put_line('Hello World');
    exception
      --相当于java的try...catch ====> when...then...
    end;
    
    --例题
    begin
      dbms_output.put_line('Hello World')
    end;
    
    --执行
    SQL> ed
    SQL> /
    Hello World
    PL/SQL procedure successfully completed

**注意：在执行时，可能执行结果没有显示出来，那就要先执行【set serveroutput on;】就可以了。**

## 2.3、结构 ##

*  PLSQL块是可以嵌套的
 *  字块相当于父块的一条命令
 *  可与位于任何位置

# 3、PLSQL变量 #

PLSQL的变量类型：

*  系统内置的**常规变量类型**：如大多数数据库表的字段类型
 *  自定义**复杂变量类型**：如记录类型
 *  **引用类型**：保存了一个指针值
 *  **大对象类型**（LOB）：保存了一个指向大对象的地址

## 3.1、变量申明 ##

理解：就相当于java中的赋值（个人理解）

int i;  //相当于申明变量
    i = 5;  //相当于将数据库的select的值赋这这个变量

例：

...
    declare
    --申明变量
      v_sal number(9,2);--可以使用动态的--->employees.salary%type;
      v_email varchar2(20);-- ---->employees.email%type
      v_hire_date date;-- ---->employees.hire_date%type
    ...

*  变量命名建议遵循通用规则
    
     *  变量：v\_name
     *  常量：c\_name
 *  为了代码美观，一行申明一个变量
 *  申明变量可以对其赋值，如果没有赋值则默认为NULL
    
     *  v\_sal number(9,2) := 10; --给v\_sal赋值为10
 *  在同一个块中，避免与数据库表中的字段名相同

命名方法试例：

## 3.2、%type声明 ##

%type是PLSQL特有的变量声明

语法：

...
    declare
    --申明变量
      v_sal employees.salary%type;
      v_email employees.email%type;
      v_hire_date employees.hire_date%type;
    ...

相当于是你想赋值的那个属性一致的变量类型

## 3.3、变量赋值 ##

PLSQL的变量赋值语法如下：

/* variable := expression
       variable:变量  expression：表达式
    */
    declare
      v_num number(9,2) := 10;
    begin
      dbms_output.put_line(v_num);
    end;
    
    SQL> ed
    SQL> /
    10
    PL/SQL procedure successfully completed
    SQL>

## 3.4、dbms\_output.put\_line() ##

相当于java中的**System.out.print();**打印语句

例：

declare
      v_sal number(9,2) := &in_sal;
    begin
      v_sal := v_sal+1;
      dbms_output.put_line('the v_sal ='||v_sal);
    end;
    
    
    SQL> ed
    SQL> /
    the v_sal =6
    PL/SQL procedure successfully completed

注意：这里的&in\_sal就相当于与输入，只需要加 &+自定义名字

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_8_color_FFFFFF_t_70_g_se_x_16][]

## 3.5、数据库赋值 ##

**SELECT INTO** **语句： 用于把从数据库查询出内容存入变量**

数据库赋值是通过select语句完成的，每次执行select语句就赋值一次，**一般要求被赋值的变量与select中的列名要一一对应。**

例：

declare
    --申明变量
      v_sal number(9,2);--可以使用动态的--->employees.salary%type;
      v_email varchar2(20);-- ---->employees.email%type
      v_hire_date date;-- ---->employees.hire_date%type
    begin
    --SQL语句
      select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100;
      --打印
      dbms_output.put_line(v_sal||','||v_email||','||v_hire_Date);
    end;
    
    
    SQL> ed
    SQL> /
    24000,SKING,17-6月 -87
    PL/SQL procedure successfully completed

**注意：其他的方法如增删改也可以通过对变量赋值然后对数据库进行修改。**

## 3.6、记录类型 ##

记录类型是把逻辑相关的数据作为一个单元存储起来，称作 PLSQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。

相当于java中的类的概念。

语法如下：

TYPE record_type IS RECORD(
     Field1 type1 [NOT NULL] [:= exp1 ],
     Field2 type2 [NOT NULL] [:= exp2 ],
     . . . . . .
     Fieldn typen [NOT NULL] [:= expn ] ) ;

例：

declare
    --申明一个记录类型type.....is record(.....);
      type emp_record is record(
      v_sal number(9,2),--可以使用动态的--->employees.salary%type;
      v_email varchar2(20),-- ---->employees.email%type
      v_hire_date date-- ---->employees.hire_date%type
      );
      --定义记录类型的成员变量
      v_emp_record emp_record;
    begin
    --SQL语句,变量与记录类型的顺序一一对应
      select salary,email,hire_date into v_emp_record from employees where employee_id = 100;
      --打印
      dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_Date);
    end;
    
    
    
    SQL> ed
    SQL> /
    24000,SKING,17-6月 -87
    PL/SQL procedure successfully completed

## 3.7、可转换的类型赋值 ##

*  char转换为number：

使用to\_number函数使 **字符===》数字**

declare
      v_num number(9,2) := 10;
    begin
      v_num := TO_NUMBER('100') + v_num;
      dbms_output.put_line(v_num);
    end;
    
    SQL> ed
    SQL> /
    110
    PL/SQL procedure successfully completed

*  number转换为char

使用to\_char函数使 **数字===》字符**

declare
      v_char varchar2(10) := '这是';
    begin
      v_char := v_char || TO_CHAR(100);
      dbms_output.put_line(v_char);
    end;
    
    
    SQL> ed
    SQL> /
    这是100
    PL/SQL procedure successfully completed

*  字符转换为日期

使用to\_date函数使 **字符===》日期**

declare
      v_date date;
    begin
      v_date := TO_DATE('2021.07.13','yyyy.mm.dd');
      dbms_output.put_line(v_date);
    end;
    
    
    SQL> ed
    SQL> /
    13-7月 -21
    PL/SQL procedure successfully completed

*  日期转换为字符

使用to\_char函数使 **日期===》字符**

declare
      v_char varchar2(20);
    begin
      v_char := TO_CHAR(sysdate,'yyyy.mm.dd hh24:mi:ss');
      dbms_output.put_line(v_char);
    end;
    
    
    SQL> ed
    SQL> /
    2021.11.18 23:21:43
    PL/SQL procedure successfully completed

# 4、流程控制语句 #

PLSQL 的流程控制语句包括三类：**控制语句**、**循环语句**、**顺序语句**

## 4.1、控制语句 ##

*  if语句
    
        if ... then elsif then ... else ... end if;
        
        IF condition THEN
         statements;
        [ELSIF condition THEN 
         statements;]
        [ELSE 
         statements;]
        END IF;
 *  case语句
    
        case ... when ... then ... end;
        
        CASE selector
         WHEN expression1 THEN result1
         WHEN expression2 THEN result2
         ...
         WHEN expressionN THEN resultN
         [ELSE resultN+1;]
        END;

## 4.2、循环语句 ##

*  LOOP语句
    
        loop ... exit when ... end loop;
        
        LOOP 
         statement1;
         . . .
         EXIT [WHEN condition];
        END LOOP;
 *  while语句

while ... loop ... end loop;
    
    WHILE condition LOOP
     statement1;
     statement2;
     . . .
    END LOOP;

*  for语句
    
        FOR counter IN [REVERSE] 
         lower_bound..upper_bound LOOP 
         statement1;
         statement2;
         . . .
        END LOOP;
        for i in ... loop ... end loop;
    
    ## 4.3、顺序语句 ##
 *  goto语句:PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思
    
     *  goto label;
        
            declare
              v_num number := 1;
            begin
              loop
                dbms_output.put_line('v_num的当前值为：'||v_num);
                v_num := v_num+1;
                if v_num > 10 then
                  goto lable_EndofLoop;--跳转到指定标号
                  --如果不调到指定的地方的话，也算是跳出了循环，这个就会一直加下去，直到buffer overflow
                end if;
              end loop;
              <<lable_EndofLoop>> --跳转到此
                  dbms_output.put_line('v_num的当前值为：'||v_num);
            end;
            
            SQL> ed
            SQL> /
            v_num的当前值为：1
            v_num的当前值为：2
            v_num的当前值为：3
            v_num的当前值为：4
            v_num的当前值为：5
            v_num的当前值为：6
            v_num的当前值为：7
            v_num的当前值为：8
            v_num的当前值为：9
            v_num的当前值为：10
            v_num的当前值为：11
            PL/SQL procedure successfully completed
 *  NULL语句：
    
     *  在PL/SQL 程序中，NULL语句是一个可执行语句，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。
     *

# 5、游标(cursor)使用 #

游标概论(类似于java中的Iterator，遍历)：

为了处理 SQL 语句，ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括**要处理的行的数目**，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。**游标是一个指向上下文的句柄( handle)或指针**。通过游标，PL/SQL 可以控制上下文区和处理语句时上下文区会发生些什么事情。

游标是一个私有的SQL工作区域，Oracle数据库中有两种游标，分别是**隐式游标**和**显式游标**。

*  隐式游标
    
     *  不易被用户和程序员察觉和意识到，实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL语句；
 *  显式游标
    
     *  是程序员在程序中显式声明的；通常我们说的游标均指显式游标。

declare
      v_num number := 1;
    begin
      loop
        dbms_output.put_line('v_num的当前值为：'||v_num);
        v_num := v_num+1;
        if v_num > 10 then
          goto lable_EndofLoop;--跳转到指定标号
        end if;
      end loop;
      <<lable_EndofLoop>> --跳转到此
          null; --不需要处理任何数据
    end;
    
    SQL> ed
    SQL> /
    v_num的当前值为：1
    v_num的当前值为：2
    v_num的当前值为：3
    v_num的当前值为：4
    v_num的当前值为：5
    v_num的当前值为：6
    v_num的当前值为：7
    v_num的当前值为：8
    v_num的当前值为：9
    v_num的当前值为：10
    PL/SQL procedure successfully completed

对于不同的 SQL 语句，游标的使用情况不同：

<table> 
 <thead> 
  <tr> 
   <th>SQL语句</th> 
   <th>游标</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>非查询语句</td> 
   <td>隐式的</td> 
  </tr> 
  <tr> 
   <td>结果是单行的查询语句</td> 
   <td>隐式的或显示的</td> 
  </tr> 
  <tr> 
   <td>结果是多行的查询语句</td> 
   <td>显示的</td> 
  </tr> 
 </tbody> 
</table>

## 5.1、显示游标 ##

显示游标的相关函数可以：

*  一行一行的处理返回的数据
 *  保持当前处理行的一个跟踪，就像一个指针一样指示当前处理的记录
 *  允许程序员在PLSQL块中人为的控制游标的开启、关闭、上下移动

步骤：

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

例题：提取部门id为50的所有员工工资，通过游标

declare
    --如果变量多的话就可以用记录类型record
      v_sal employees.salary%type;
      v_emp_id employees.employee_id%type;
      
      --定义游标
      cursor emp_sal_cursor is select salary,employee_id from employees where department_id = 50;
      
    begin
        --打开游标
        open emp_sal_cursor;
        --提取游标，现将emp_sal_cursor内的一个值取给v_sal
        fetch emp_sal_cursor into v_sal,v_emp_id;
        --r%found看emp_sal_cursor内是否还有值，如果还有就循环
        while emp_sal_cursor%found loop
          dbms_output.put_line('employee_id='||v_emp_id||':'||'salary='||v_sal);
          --继续取值，知道取完为止
          fetch emp_sal_cursor into v_sal,v_emp_id;
          
        end loop;
        
        --关闭游标
        close emp_sal_cursor;
    end;
    
    
    
    SQL> ed
    SQL> /
    employee_id=120:salary=8000
    employee_id=121:salary=8200
    ...
    employee_id=198:salary=2600
    employee_id=199:salary=2600
    PL/SQL procedure successfully completed

相应的SQL语句：

select employee_id,salary from employees where department_id = 50;

## 5.2、游标属性 ##

*  %FOUND
    
     *  布尔型属性，当最近一次读记录时成功返回,则值为 TRUE
 *  %NOTFOUND
    
     *  布尔型属性，与%FOUND 相反
 *  %ISOPEN
    
     *  布尔型属性，当游标已打开时返回 TRUE
 *  %ROWCOUNT
    
     *  数字型属性，返回已从游标中读取的记录数。

## 5.3、游标的for循环 ##

如果你觉得像前面那个例子那样对一个游标的遍历很麻烦的话，可以考虑使用**For循环**。

*  For循环省去了游标的声明、打开、提取、测试、关闭等语句，自动执行游标的OPEN、FETCH、CLOSE 语句和循环语句的功能；
 *  当进入循环时，游标 FOR 循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR 循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标，对程序员来说很方便。

格式：

FOR record_name IN cursor_name LOOP 
     statement1;
     statement2;
     . . .
    END LOOP;

例题1：

declare
      --定义游标
      cursor emp_sal_cursor is select salary,employee_id from employees where department_id = 50;
      
    begin
      for c in emp_sal_cursor loop
       dbms_output.put_line('employee_id='||c.employee_id||':'||'salary='||c.salary);
      end loop;
    end;
    
    
    SQL> ed
    SQL> /
    employee_id=120:salary=8000
    employee_id=121:salary=8200
    ...
    employee_id=198:salary=2600
    employee_id=199:salary=2600
    PL/SQL procedure successfully completed

例题2：

DECLARE
     CURSOR emp_cursor IS
     SELECT last_name, department_id
     FROM employees;
    BEGIN
     FOR emp_record IN emp_cursor LOOP
     -- implicit open and implicit fetch occur
     IF emp_record.department_id = 80 THEN
     ...
     END LOOP; -- implicit close occurs
    END;

利用游标，调整公司员工工资

工资范围 调整基数

0 -5000 5%

5000-10000 3%

10000-15000 2%

15000-x 1%

declare
      cursor emp_cursor is
      select employee_id,last_name,salary
      from employees;
      
      --调整的基数
      v_temp number(9,2);
      
    begin
      for emp in emp_cursor loop
        if emp.salary < 5000 then v_temp := 0.05;
        elsif emp.salary < 10000 then v_temp := 0.03;
        elsif emp.salary < 15000 then v_temp := 0.02;
        else v_temp := 0.01;
        end if;
        
        dbms_output.put_line('调整前='||emp.last_name||':'|| emp.salary);
        update employees set salary = salary * (1 + v_temp)
        where employee_id = emp.employee_id;
      end loop;
    end;
    
    SQL> set serveroutput on
    SQL> ed
    SQL> /
    调整前=QQ:5000
    调整前=Steven:28000
    ...
    调整前=QQ:5000
    调整前=new_ww:6000
    PL/SQL procedure successfully completed
    SQL> select last_name,salary from employees;
    
    LAST_NAME                     SALARY
    ------------------------- ----------
    QQ                           5150.00
    Steven                      28280.00
    ...
    QQ                           5150.00
    new_ww                       6180.00
    110 rows selected

sql语句：

update employees set salary = salary * (1+(decode(trunc(salary/5000),0,0.05,
                                                      1,0.03,
                                                      2,0.02,
                                                      0.01)))

## 5.4、携带参数 ##

游标中是可以携带参数的：

格式：

CURSOR cursor_name
     [(parameter_name datatype, ...)]
    IS
     select_statement;

例：

declare
      cursor emp_cursor
      (p_deptno number,p_job varchar2) is
      select employee_id,last_name
      from employees
      where department_id = p_deptno
      and job_id = p_job;
    begin
      for c in emp_cursor(80,'SA_REP') loop
      dbms_output.put_line(c.employee_id||':'||c.last_name);
      end loop;
    end;
    
    
    SQL> ed
    SQL> /
    150:Tucker
    151:Bernstein
    ...
    177:Livingston
    179:Johnson
    PL/SQL procedure successfully completed

PLSQL 还允许在游标 FOR 循环语句中使用子查询来实现游标的功能。

begin
      for v_emp in(select last_name,salary from employees) loop
        dbms_output.put_line(v_emp.last_name||':'||v_emp.salary);
      end loop;
    end;
    
    
    SQL> ed
    SQL> /
    QQ:5150
    Steven:28280
    ...
    QQ:5150
    new_ww:6180
    PL/SQL procedure successfully completed

## 5.5、隐式游标 ##

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；**而对于非查询语句，如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL，这是由 ORACLE 系统定义的。**

对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE 系统自动地完成，无需用户进行处理。**用户只能通过隐式游标的相关属性，来完成相应的操作。**

在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。

格式调用为： SQL%

**隐式游标属性：**

*  SQL%FOUND
    
     *  布尔型属性,当最近一次读记录时成功返回，则值为 TRUE
 *  SQL%NOTFOUND
    
     *  布尔型属性,与%FOUND 相反
 *  SQL %ROWCOUNT
    
     *  数字型属性, 返回已从游标中读取得记录数
 *  SQL %ISOPEN
    
     *  布尔型属性, 取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标

例：更新指定员工信息，如果该员工没有找到，则打印”查无此人”信息。

declare
      v_name employees.last_name%type := 'update_name';
      v_id employees.employee_id%type := 173;
      
      cursor emp_cursor is select employee_id,last_name from employees where employee_id = v_id;
    begin
      update employees
      set last_name = v_name
      where employee_id = v_id;
      
      if sql%notfound then
        dbms_output.put_line('查无此人');
      else
        for v_emp in emp_cursor loop
        dbms_output.put_line(v_emp.last_name||':'||v_emp.employee_id);
        end loop;
      end if;
    end;
    
    
    SQL> ed
    SQL> /
    update_name:173
    PL/SQL procedure successfully completed

## 5.6、bulk collect into ##

作用：

*  采用bulk collect可以将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理。
 *  可以在select into,fetch into,returning into语句使用bulk collect。
 *  注意在使用bulk collect时，所有的into变量都必须是collections 。

例题：写一个匿名块，查询employees 表中数据，并通过dbsm\_output输出；要求使用table表变量进行实现

--通过select into 实现
    declare
        type emp is table of employees%rowtype;
        emp_1 emp;
        begin
            select * bulk collect into emp_1 from employees;
            for e in emp_1.first..emp_1.last loop
                dbms_output.put_line('姓名：'||emp_1(e).last_name);
            end loop;
        end;

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_4_color_FFFFFF_t_70_g_se_x_16][]

# 6、异常(例外)处理 #

一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。ORACLE 提供**异常情况**

(EXCEPTION)和**异常处理**(EXCEPTION HANDLER)来实现错误处理。

1.  预定义(Predefined)错误：

ORACLE 预定义的异常情况大约有 24 个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。

1.  非预定义(Predenfined)错误

即其他标准的 ORACLE 错误。对这种异常情况的处理，需要用户在程序中定义，然后由 ORACLE 自动将其引发。

1.  用户定义(User\_define)错误

程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

**PLSQL中的例外处理的一般语法：**

EXCEPTION
     WHEN exception1 [OR exception2 . . .] THEN
     statement1;
     statement2;
     . . .
     [WHEN exception3 [OR exception4 . . .] THEN
     statement1;
     statement2;
     . . .]
     [WHEN OTHERS THEN
     statement1;
     statement2;
     . . .]

**注意：异常处理可以按任意次序排列,但 OTHERS 必须放在最后**

## 6.1、预定义异常处理 ##

处理预定义的异常：有些常见异常，Oracle都已经预定义好了，使用时无需预先声明，比如：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_19_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_19_color_FFFFFF_t_70_g_se_x_16 1][]

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

**注意：No\_data\_found和Too\_many\_rows是最常见的异常，大多数Block中都建议对这两种例外有处理。**

例题：

更新指定员工工资，如工资小于 3000，则加 1000；对 No\_data\_found 和Too\_many\_rows异常进行处理.

declare
      v_empid employees.employee_id%type := &v_empid;
      v_sal employees.salary%type;
      
    begin
      select salary into v_sal
      from employees
      where employee_id = v_empid
      for update;
      
      if v_sal <= 3000 then
        update employees set salary = salary + 1000
        where employee_id = v_empid;
        dbms_output.put_line('id为'||v_empid||'员工工资已更新');
      else
        dbms_output.put_line('id为'||v_empid||'员工工资不需要更新');
      end if;
      exception
        when no_data_found then
          dbms_output.put_line('没有找到id为'||v_empid||'的员工');
        when too_many_rows then
          dbms_output.put_line('程序运行错误，请使用游标');
        when others then
          dbms_output.put_line('其他错误');
    end;
    
    SQL> ed
    SQL> /
    没有找到id为1的员工
    PL/SQL procedure successfully completed

**注意：others的处理**

others表明程序未能预计到这种错误，所以全部归入到others里。

如果发生这种情况，可以在others内提供两个内置函数：SQLcode和SQLerrm分别返回错误号和错误信息。

格式如下：

DECLARE
     v_error_code NUMBER;
     v_error_message VARCHAR2(255);
    BEGIN
    ...
    EXCEPTION
    ...
     WHEN OTHERS THEN
     ROLLBACK;
     v_error_code := SQLCODE ;
     v_error_message := SQLERRM ;
     INSERT INTO errors 
     VALUES(v_error_code, v_error_message);
    END;

例：

declare
      v_empid employees.employee_id%type := &v_empid;
      v_sal employees.salary%type;
      --返回错误信息
      v_error_code number;
      v_error_message varchar2(255);
      
    begin
      select salary into v_sal
      from employees
      where employee_id = v_empid
      for update;
      
      if v_sal <= 3000 then
        update employees set salary = salary + 1000
        where employee_id = v_empid;
        dbms_output.put_line('id为'||v_empid||'员工工资已更新');
      else
        dbms_output.put_line('id为'||v_empid||'员工工资不需要更新');
      end if;
      exception
        when no_data_found then
          dbms_output.put_line('没有找到id为'||v_empid||'的员工');
        when too_many_rows then
          dbms_output.put_line('程序运行错误，请使用游标');
        when others then
          v_error_code := sqlcode;
          v_error_message := sqlerrm;
          dbms_output.put_line(v_error_code);
          dbms_output.put_line(v_error_message); --可以不用申明变量直接输出sqlcode，sqlerrm即可
    end;

## 6.2、非预定义的异常处理 ##

对于这类异常情况的处理，首先必须对非定义的 ORACLE 错误进行定义。步骤如下：

1.  在PLSQL块的定义部分定义异常情况:
    
    <异常情况> Exception
2.  将其定义好的异常情况与标准的Oracle错误联系起来，使用Pragma Exception\_Init语句
    
    Pragma Exception\_Init(<异常情况>,<错误代码>);
3.  在PLSQL块的异常情况处理部分对异常情况作出相应的处理

例题：删除指定部门的记录信息，以确保该部门没有员工。

declare
      v_depno departments.department_id%type := 111;
      deptno_remaining exception;   --第一步：定义异常情况
      
      -- -2292是违反一致性约束的错误代码
      pragma exception_init(deptno_remaining,-2292);    --第二步：与oracle错误编码联系起来
      begin
        delete from departments where department_id = v_depno;
      exception
        when deptno_remaining then  --第三部：处理异常
          dbms_output.put_line('违反数据完整性约束');
        when others then
          dbms_output.put_line(sqlcode || '==='||sqlerrm);
    end;

## 6.3、用户自定义的异常处理 ##

处理用户自定义的错误: 这种错误一般是程序员根据具体的业务逻辑定义的应用类错误，需要先声明后使用，**自定义错误是通过显示使用Raise语句来触发。当引发一个异常错误时，控制就转向到Exception块异常错误部分，执行错误处理代码。**

定义和处理过程如下：

1.  给异常命名
    
    <异常情况> Exception
2.  使用Raise语句显示的抛出例外
    
    Raise<异常情况>;
3.  处理抛出的异常

例题：更新指定员工工资，增加 1000；若该员工不存在则抛出用户自定义异常: no\_result

declare
      v_empid employees.employee_id%type := &v_empid;
      v_sal employees.salary%type;
      no_result exception;               --第一步：给异常命名
      
    begin
      select salary into v_sal
      from employees
      where employee_id = v_empid
      for update;
      
      if sql%notfound then  --第二步：使用Raise语句显示的抛出例外
         raise no_result;
      end if;
      
      if v_sal <= 3000 then
        update employees set salary = salary + 1000
        where employee_id = v_empid;
        dbms_output.put_line('id为'||v_empid||'员工工资已更新');
      else
        dbms_output.put_line('id为'||v_empid||'员工工资不需要更新');
      end if;
      
      exception
        when no_result then         --第三步：处理抛出的异常
          dbms_output.put_line('没有找到id为'||v_empid||'的员工');
      
    end;

declare
      v_empid employees.employee_id%type := &v_empid;
      no_result exception;
    begin
      update employees
      set salary = salary+1000
      where employee_id = v_empid;
      
      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('其他异常');
    end；

--填没有的id号
    SQL> ed
    SQL> /
    数据更新失败
    PL/SQL procedure successfully completed

## 6.4、使用SQLcode和SQLerrm ##

在前面我们也将过SQLcode和SQLerrm，在这里就详细讲一下他的用法

**SQLcode：返回错误代码数字**

**SQLerrm：返回错误信息**

如：sqlcode=-100 ==> sqlerrm = 'no\_Data\_found'

sqlcode=0 ==> sqlerrm='normal,successfual completion'

例：将 ORACLE 错误代码及其信息存入错误代码表

--第一步，先建立errors表，存储error信息
    create table errors
    (
        errnum number(4),
        errmsg varchar2(100)
    )
    /*===========================================*/
    --第二步，得到所有oracle错误信息
    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;
    
    SQL> select * from errors;
    ERRNUM ERRMSG
    ------ --------------------------------------------------------------------------------
      -100 ORA-00100: 未找到任何数据
       -99 ORA-00099: 警告: 没有为  实例指定参数文件
        ...
        -3 ORA-00003: Message 3 not found;  product=RDBMS; facility=ORA
        -2 ORA-00002: Message 2 not found;  product=RDBMS; facility=ORA
        -1 ORA-00001: 违反唯一约束条件 (.)
         0 ORA-0000: normal, successful completion
    101 rows selected

查询错误代码:

begin
      insert into employees(employee_id,last_name,hire_date,department_id)
           values(1111,'tianci',sysdate,80);
      dbms_output.put_line('插入数据成功');
      insert into employees(employee_id,last_name,hire_date,department_id)
           values(1111,'tianci',sysdate,80);
      exception
        when others then
          dbms_output.put_line(sqlcode ||'====' || sqlerrm);
    end;
    
    
    SQL> ed
    SQL> /
    -2290====ORA-02290: 违反检查约束条件 (ORA1.EMP_JOB_NN)
    PL/SQL procedure successfully completed

# 7、存储函数和过程 #

**ORACLE提供可以把PLSQL程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。**过程和函数统称为PLSQL子程序，他们是被命名的PLSQL块，都存储在数据库中，并通过输入、输出参数与其调用者交换信息。

**过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据**

函数有return，过程没有

那些SQL语句中可以使用用户自定义的函数：

*  select语句
 *  where条件和having子句
 *  connect by，start with，order by，以及group by子句
 *  insert的values子句
 *  update的set子句

## 7.1、创建函数 ##

使用PLSQLDeveloper开发函数：

File--->New--->Program Window--->Function

系统文件会有默认的Function文件

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_13_color_FFFFFF_t_70_g_se_x_16][]

### 7.1.1、建立内嵌函数 ###

语法格式：

CREATE [OR REPLACE] FUNCTION function_name
    [ (argment [ { IN | IN OUT }] Type,
       argment [ { IN | OUT | IN OUT } ] Type ]
       [ AUTHID DEFINER | CURRENT_USER ]
       RETURN return_type 
       { IS | AS } <类型.变量的说明>
       BEGIN
       FUNCTION_body
       EXCEPTION
       其它语句
       END;
    /*================================================*/
       --存储函数
    create or replace function func_name(dept_id number,salary number);
    return number;
     is
       --函数使用过程中，需要申明的变量、记录类型、cursor
    begin
       --函数的执行体
    exception
       --处理函数执行过程中的异常
    end;

**格式说明：**

1.  【OR REPLACE】为可选，有了它，可以创建一个新函数或者替换同名函数，防止出现冲突
2.  函数名后面是一个可选的参数列表，有in，out和in out标记。参数之间要用逗号隔开。
    
     *  in参数标记：传递参数给函数的值在该函数执行中不变
     *  out参数标记：表示一个值在函数中进行计算并通过该参数传递给调用语句
     *  in out参数标记：表示传递给函数的值可以变化并传递给调用语句
     *  **默认参数为in**
3.  函数要返回值，所以使用return包含返回结果的数据类型，相当于java的return。
4.  is 是说明return返回的值的类型

例题1：创建简单的函数，打印helloworld

--存储函数
    create or replace function hello_world  --可以携带参数
    return varchar2   --返回varchar2类型
    is
    begin
        --可以写方法语句
      return 'Hello World'; --返回值
    end;
    
    /*执行函数*/
    begin
      dbms_output.put_line(hello_world);
    end;
    /*也可以这样*/
    select hello_world from dual;
    
    SQL> ed
    SQL> /
    Hello World
    PL/SQL procedure successfully completeded

![d75ee4ee87734f0097fb19223c960a42.png][]

例题2：获取某部门的工资总和

构建有参函数get\_dept\_salary

create or replace function get_dept_salary(
        dept_id employees.employee_id%type,
        emp_count out number)   --out型的参数：实现多个返回值
           return number
           is
           v_sum number;
    begin
      select sum(salary),count(*) into v_sum,emp_count
      from employees
      where department_id = dept_id;
    
      return v_sum;
    exception
      when no_data_found then
        dbms_output.put_line('数据不存在');
      when others then
        dbms_output.put_line(sqlcode||'---'||sqlerrm);
    end;

![6c71687f29744d86b0684a87bf929453.png][]

### 7.1.2、内嵌函数的调用 ###

**注意：本次调用的函数为上一步所声明的get\_dept\_salary函数**

*  形式参数：函数声明时所定义的参数
 *  实际参数：应用程序调用时为函数传递的参数

应用程序在调用函数时，有三种方法向函数传递参数（位置表示法，名称表示法，混合表示法）：

1.  位置表示法

格式：

argument_value1[,argument_value2,...]

例题：调用get\_dept\_salary函数计算某部门工资总和

declare
      v_num number;
      v_sum number;
    begin
      v_sum := get_dept_salary(80,v_num);
      dbms_output.put_line('id为80号部门的工资总和为：'||v_sum||','||'总人数为：'||v_num);
    end;
    
    
    SQL> /
    id为80号部门的工资总和为：304500,总人数为：34
    PL/SQL procedure successfully completed

1.  名称表示法

格式：

argument => parameter[,...]

argument为形式参数，必须与函数定义时所声明的形式参数名称相同。

parameter为实际参数

例题：调用get\_dept\_salary函数计算某部门工资总和

declare
      v_num number;
      v_sum number;
    begin
      v_sum := get_dept_salary(emp_count => v_num,dept_id => 80);
      dbms_output.put_line('id为80号部门的工资总和为：'||v_sum||','||'总人数为：'||v_num);
    end;
    
    SQL> /
    id为80号部门的工资总和为：304500,总人数为：34
    PL/SQL procedure successfully completed

1.  混合表示法

调用一个函数时，可以同时使用位置标示法和名称表示法为函数传递参数。

**注意：位置表示法所传递的参数必须放在名称表示法所传递的参数前面**

格式：

argument_value1[,argument_value2,...]，argument => parameter[,...]

例题：调用get\_dept\_salary函数计算某部门工资总和

declare
      v_num number;
      v_sum number;
    begin
      v_sum := get_dept_salary(80,emp_count => v_num);
      dbms_output.put_line('id为80号部门的工资总和为：'||v_sum||','||'总人数为：'||v_num);
    end;

### 7.1.3、参数默认值 ###

在创建函数语句中声明函数参数时，**可以使用default关键字输入参数，为指定默认值**

例题：获取默认id为90号的部门的工资总和

create or replace function get_dept_salary(
        dept_id employees.employee_id%type default 90,  --默认id
        emp_count out number)   --out型的参数：实现多个返回值
           return number
           is
           v_sum number;
    begin
      select sum(salary),count(*) into v_sum,emp_count
      from employees
      where department_id = dept_id;
    
      return v_sum;
    exception
      when no_data_found then
        dbms_output.put_line('数据不存在');
      when others then
        dbms_output.put_line(sqlcode||'---'||sqlerrm);
    end;
    declare
      v_num number;
      v_sum number;
    begin
      v_sum := get_dept_salary(emp_count => v_num);
      dbms_output.put_line('默认id为90号部门的工资总和为：'||v_sum||','||'总人数为：'||v_num);
    end;
    
    SQL> /
    默认id为90号部门的工资总和为：62000,总人数为：3
    PL/SQL procedure successfully completed

**注意：如果使用默认参数的话，就得指明里面的参数是那个，不能使用位置表示法**

## 7.2、存储过程 ##

### 7.2.1、创建过程 ###

在ORACLE server上建立存储过程，可以被多个应用程序调用，可以向存储过程传递参数，也可以向存储过程传回参数。

使用PLSQLDEVELOPER开发存储过程： FILE-->New-->Program Window-->Procedure

创建过程语法：

CREATE [OR REPLACE] PROCEDURE Procedure_name
    [ (argment [ { IN | IN OUT }] Type,
       argment [ { IN | OUT | IN OUT } ] Type ]
       [ AUTHID DEFINER | CURRENT_USER ]
       { IS | AS } <类型.变量的说明>
       BEGIN
       <执行部分>
       EXCEPTION
       <可选的异常错误处理程序>
       END;

例题：删除指定员工记录

create or replace procedure del_emp(
        v_empid in employees.employee_id%type
    )
           is
    
           no_result exception;
    begin
      delete from employees
      where employee_id = v_empid;
    
      if sql%notfound then
        raise no_result;
      end if;
    
      dbms_output.put_line('编号为：'||v_empid||'的员工已删除');
    exception
      when no_result then
        dbms_output.put_line('该员工不存在');
      when others then
        dbms_output.put_line(sqlcode || '---' || sqlerrm);
    end;

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_5_color_FFFFFF_t_70_g_se_x_16][]

### 7.2.2、调用存储过程 ###

例题：查询指定员工记录

create or replace procedure query_emp(
           v_empid employees.employee_id%type,
           v_name out employees.last_name%type,
           v_sal out employees.salary%type
           )
           is
           
    begin
      select last_name,salary into v_name,v_sal
      from employees
      where employee_id = v_empid;
      
      dbms_output.put_line('员id为：'||v_empid||','||'工资为：'||v_sal);
      
    exception
      when no_data_found then
         dbms_output.put_line('查无此人');
      when others then
         dbms_output.put_line(sqlcode || '---' || sqlerrm);
    end;
    
    --注意：要将v_name，v_sal作为select的into目标的话得使用out型参数

调用方法：

declare
      v_name employees.last_name%type;
      v_sal employees.salary%type;
      v_id employees.employee_id%type := &v_id;
    begin
      query_emp(v_id,v_name,v_sal);
      dbms_output.put_line('姓名：'||v_name||','||'工资：'||v_sal);
    end;

## 7.3、AUTHID ##

在创建存储过程时, 可使用 AUTHID CURRENT\_USER 或 AUTHID DEFINER 选项,以表明在执行该过程时

Oracle 使用的权限.

1.  如果使用 AUTHID CURRENT\_USER 选项创建一个过程, 则 Oracle 用调用该过程的用户权限执

行该过程. 为了成功执行该过程, 调用者必须具有访问该存储过程体中引用的所有数据库对象所必须的权限

1.  如果用默认的 AUTHID DEFINER 选项创建过程, 则 Oracle 使用过程所有者的特权执行该过程

为了成功执行该过程, 过程的所有者必须具有访问该存储过程体中引用的所有数据库对象所必

须的权限. 想要简化应用程序用户的特权管理, 在创建存储过程时, 一般选择 AUTHID DEFINER

选项 –-- 这样就不必授权给需要调用的此过程的所有用户了

## 7.4、查看错误信息 ##

我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。

在 SQLPLUS 下来调式主要用的方法是：

*  使用show error命令提示源码的错误位置
 *  使用user\_errors数据字典来查看各存储过程的错误位置

## 7.5、删除过程和函数 ##

1.  删除过程

使用drop procedure命令删除过程

语法格式：

DROP PROCEDURE [user.]Procudure_name;

1.  删除函数

使用drop function命令删除函数

语法格式：

DROP FUNCTION [user.]Procudure_name;

# 8、包的创建和应用 #

**包是一组相关过程、函数、变量、常量和游标等** **PL/SQL** **程序设计元素的组合**，它具有面向对象程序设

计语言的特点，是对这些 PL/SQL 程序设计元素的封装。包类似于 **JAVA** **语言中的类，其中变量相当**

**于类中的成员变量，过程和函数相当于类方法**。把相关的模块归类成为包，**可使开发人员利用面向对象的**

**方法进行存储过程的开发，从而提高系统性能**。

与类相同，**包中的程序元素也分为公用元素和私用元素两种**，这两种元素的区别是他们允许访问的程

序范围不同，即它们的作用域不同。**公用元素**不仅可以被包中的函数、过程所调用，也可以被包外的 PL/SQL

程序访问，而**私有元素**只能被包内的函数和过程序所访问。

在 PL/SQL 程序设计中，使用包不仅可以使程序设计模块化，对外隐藏包内所使用的信息（通过使用私

用变量），而且可以提高程序的执行效率。因为，当程序首次调用包内函数或过程时，ORACLE 将整个包调

入内存，当再次访问包内元素时，ORACLE 直接从内存中读取，而不需要进行磁盘 I/O 操作，从而使程序执

行效率得到提高

一个包由两个分开的部分组成：

*  包定义（package）：包定义部分**声明**包内数据类型、变量、常量、游标、子程序和异常错误处理等元
    
    素，这些元素为包的公有元素。
 *  包主体（package body）：包主体则是包定义部分的**具体实现**，它定义了包定义部分所声明的游标和子
    
    程序，在包主体中还可以声明包的私有元素。

注意：包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中，详见数据字典 user\_source,

all\_source, dba\_source

## 8.1、包的好处 ##

**Package好处：**

1.  模块化：一般把有相关性的函数和过程放到一个Package中；
2.  易设计：可以把包说明和包体分别编写和编译，先编写和编译包说明部分，在编写和说明包体部分；这有利
    
    于分工合作；
3.  信息隐藏：包体中函数可以部分出现在包说明中，只有出现在包说明中的函数和过程才是该Package的公有
    
    函数和过程，可以被其他包中的函数调用，否则对其他包中的函数是不可见的，未在包说明部分出现的函数
    
    和过程相当于私有的。
4.  加载性能提高：当Package中有一个函数或过程被调用时，整个Packege就被加载到内存中，这样当该
    
    Package中其他函数被调用时，就直接从内存读取了，可以减少磁盘IO,从而提高性能。 这个特性也提醒
    
    我们不要去搞巨无霸的Package,把你用到的任何 函数都写到一个Package中，这会导致严重的内存浪费。
5.  重载：一个package 中可以定义同名、不同参数的函数或过程。

## 8.2、包的定义 ##

**Package概念：按照业务逻辑、把相关的Func , Procedure组织到一起，形成一个函数或者过程集合，这就是一个Package，这是PLSQL中程序的一种组织形式。也是我们写PLSQL最主要的形式；**

语法格式：

--创建包定义
    CREATE [OR REPLACE] PACKAGE package_name
        [AUTHID {CURRENT_USER | DEFINER}]
        {IS | AS} [公有数据类型定义[公有数据类型定义]…]
        [公有游标声明[公有游标声明]…]
        [公有变量、常量声明[公有变量、常量声明]…]
        [公有子程序声明[公有子程序声明]…]
    END [package_name];

**注意：AUTHID CURRENT\_USER和AUTHID DEFINER选项说明应用程序在调用函数时所使用的权限模式，它们与CREATE FUNCTION语句中invoker\_right\_clause子句的作用相同。**

--创建包主体
    CREATE [OR REPLACE] PACKAGE BODY package_name
    - 41 - {IS | AS} [私有数据类型定义[私有数据类型定义]…]
    [私有变量、常量声明[私有变量、常量声明]…]
    [私有子程序声明和定义[私有子程序声明和定义]…]
    [公有游标定义[公有游标定义]…]
    [公有子程序定义[公有子程序定义]…]
    BEGIN
    PL/SQL 语句
    END [package_name];

**注意：在包主体定义公有程序时，它们必须与包定义中所声明子程序的格式完全一致**

## 8.3、包的开发步骤 ##

与存储过程类似，主要步骤如下：

1.  将每个存储过程调式正确
2.  用文本编辑软件将各个存储过程和函数集成在一起
3.  按照包的定义要求将集成的文本的前面加上包定义
4.  按照包的定义要求将集成的文本的前面加上包主体
5.  使用 SQLPLUS 或开发工具进行调式

## 8.4、包的定义说明 ##

**Package中的向前声明特性**：在Package body中，一个函数中调用另一个函数（也在该Package中），则**另**

**一个函数必须在前面先定义，否则就是非法引用**；如果你非要调用在程序代码中后定义的函数，可把这个函数设置成**公有函数**，在包说明部分说明；

例题：创建一个名为demo\_pack的包，该包中含有一个记录变量deptrec，两个函数和一个过程

--包的定义
    create or replace package demo_pack
           is
           deptrec departments%rowtype;
           function add_dept(
               dept_id number,dept_name varchar2,location_id number)
             return number;
           function remove_dept(dept_id number)
             return number;
           procedure query_dept(dept_id in number);
    end demo_pack;

包主体的创建：实现上面所声明的定义

create or replace package body demo_pack
           is
           function add_dept(dept_id number,dept_name varchar2,location_id number)
             return number
           is
           empno_remaining exception;
           pragma exception_init(empno_remaining,-1);-- -1表示该错误是违反了唯一约束条件的错误代码
           begin
             insert into departments values(dept_id,dept_name,location_id);
             if sql%found then
               return 1;
             end if;
           exception
             when empno_remaining then
               return 0;
             when others then
               return -1;
           end add_dept;
    
           function remove_dept(dept_id number)
             return number
             is
           begin
             delete from departments where department_id = dept_id;
             if sql%found then
               return 1;
             else
               return 0;
             end if;
           exception
             when others then
               return -1;
           end remove_dept;
    
           procedure query_dept(dept_id in number)
             is
           begin
             select * into deptrec from departments where department_id = dept_id;
           exception
             when no_data_found then
               dbms_output.put_line('数据库中没有编码为'||dept_id||'的部门');
             when too_many_rows then
               dbms_output.put_line('程序运行错误');
             when others then
               dbms_output.put_line(sqlcode||'---'||sqlerrm);
           end query_dept;
    
           begin
             null;
           end demo_pack;

调用 demo\_pack 包内函数对 dept 表进行插入、查询和修改操作，并通过 demo\_pack 包中的记录变量 DeptRec

显示所查询到的数据库信息：

DECLARE
    Var NUMBER;
    BEGIN
    Var := demo_pack.add_dept(90,’Administration’, ‘Beijing’);
    IF var =-1 THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE||’----‘||SQLERRM);
    ELSIF var =0 THEN
    DBMS_OUTPUT.PUT_LINE(‘该部门记录已经存在！’);
    ELSE
    DBMS_OUTPUT.PUT_LINE(‘添加记录成功！’);
    Demo_pack.query_dept(90);
    DBMS_OUTPUT.PUT_LINE(demo_pack.DeptRec.deptno||’---‘||
    demo_pack.DeptRec.dname||’---‘||demo_pack.DeptRec.loc);
    var := demo_pack.remove_dept(90);
    IF var =-1 THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE||’----‘||SQLERRM);
    ELSIF var=0 THEN
    DBMS_OUTPUT.PUT_LINE(‘该部门记录不存在！’);
    ELSE
    DBMS_OUTPUT.PUT_LINE(‘删除记录成功！’);
    END IF;
    END IF;
    END;

## 8.5、删除包 ##

可以使用 **DROP PACKAGE** 命令对不需要的包进行删除，语法如下：

DROP PACKAGE [BODY] [user.]package_name;

# 9、触发器 #

触发器类似过程和函数，都有**声明**、执行和**异常处理**过程的PLSQL块。

## 9.1、触发器类型 ##

**触发器在数据库里是以独立的对象存储**，它与存储过程不同的是，存储过程是通过其他程序来启动或直接启动运行的，而**触发器是由一个事件来启动运行**。**即触发器是当某个事件发生时自动地隐式运行**。**并且，触发器不能接受参数**。所以运行触发器也叫触发或点火。**oracle事件指的是对数据库的表进行的insert、update和delete操作或对视图进行类似的操作。**

触发器组成：

*  **触发事件**：即在任何情况下触发trigger；例如insert，update，delete
 *  **触发事件**：即该trigger是在触发事件发生之前（before）还是之后（after）触发，也就是触发事件和该trigger的操作顺序
 *  **触发器本身**：即该trigger被触发之后的目的和意图，正是触发器本身要做的事情。例如：PLSQL块
 *  **触发频率**：说明触发器内定义的动作被执行的次数。**即语句级（statement）触发器和行级（row）触发器。**
    
     *  语句级（statement）触发器：指当某触发事件发生时，该触发器只执行一次
     *  行级（row）触发器 ：指当某出发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次

### 9.1.1、DML触发器 ###

oracle可以在DML语句进行触发，**可以在DML操作前或操作后进行触发**，**并且可以对每个行或语句操作上进行触发。**

### 9.1.2、替代触发器 ###

由于在oracle里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。

### 9.1.3、系统触发器 ###

它可以在oracle数据库系统的事件中进行触发，如oracle系统的启动与关闭。

## 9.2、创建触发器 ##

语法格式：

CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER } {INSERT | DELETE | UPDATE [OF column [, column …]]}
    ON [schema.] table_name 
    [FOR EACH ROW ] [WHEN condition]
    trigger_body;

before和after指触发器的触发时序分别为前触发和后触发方式

*  前触发：是在执行触发事件之前触发当前所创建的触发器（例：数据备份）
 *  后触发：是在执行触发事件之后触发当前所创建的触发器

for each row：说明触发器为行触发器。

行触发器与语句触发器的区别：

行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，**只要符合触发条件，都激活一次触发器**；而语句触发器是将**整个语句操作**作为触发事件，当它符合约束条件时，**激活一次触发器。**

**注意：当省略for each row语句时，before和after触发器为语句触发器，而instead of 触发器则为行触发器。**

每张表最多可建立 **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

**重点注意：**

1.  **instead of：用trigger的内容替换事件本身的动作**
2.  **row级：SQL语句影响到的每一行都会引发trigger**
3.  **statement级：一句SQL语句引发一次，不管它影响多少行（甚至0行）**

### 9.2.1、创建DML触发器 ###

触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。

**触发器的限制：**

*  create trigger语句文本的字符长度不能超过32kb
 *  触发器体内的select语句只能为select...into...结构，或者定义为游标所使用的select语句
 *  触发器中不能使用数据库事务控制语句commit，rollback，savepoint语句
 *  由触发器所调用的过程或函数也不能使用数据库事务控制语句

例题：建立一个触发器，修改职工表时，将该职工表的记录写到日志表上

--第一步：创建一个表来装信息
    create table my_emp_update
    as
    select employee_id,salary from employees
    
    --第二步：创建触发器
    create or replace trigger update_emp_trigger
    after update on employees for each row
    begin
      insert into my_emp_update
      values(:new.employee_id,:new.salary);
    end;
    --注意：这里可能会遇到权限不足问题，解决办法就是赋权==>GRANT CREATE TRIGGER TO xxx
    
    --第三步：触发触发器
    declare
      cursor emp_cursor is
      select employee_id,last_name,salary
      from employees;
      
      --调整的基数
      v_temp number(9,2);
      
    begin
      for emp in emp_cursor loop
        if emp.salary < 5000 then v_temp := 0.05;
        elsif emp.salary < 10000 then v_temp := 0.03;
        elsif emp.salary < 15000 then v_temp := 0.02;
        else v_temp := 0.01;
        end if;
        
        dbms_output.put_line('调整前='||emp.last_name||':'|| emp.salary);
        update employees set salary = salary * (1 + v_temp)
        where employee_id = emp.employee_id;
      end loop;
    end;
    
    --第四步：查看信息
    SQL> select * from my_emp_update;
    EMPLOYEE_ID     SALARY
    ----------- ----------
            210    5000.00
            100   28000.00
            ...     ...
            209    5150.00
            211    6180.00
    220 rows selected

### 9.2.2、创建替代（instead）触发器 ###

语法格式：

CREATE [OR REPLACE] TRIGGER trigger_name
    INSTEAD OF
    {INSERT | DELETE | UPDATE [OF column [, column …]]}
    ON [schema.] view_name
    [FOR EACH ROW ] [WHEN condition]
    trigger_body;

INSTEAD OF 选项使 ORACLE 激活触发器，而不执行触发事件。**只能对视图和对象视图建立** **INSTEAD OF**

**触发器**，而不能对表、模式和数据库建立 INSTEAD OF 触发器。

INSTEAD\_OF 用于对视图的 DML 触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所

有的联结都是可更新的。但可以按照所需的方式执行更新。

### 9.2.3、创建系统事件触发器 ###

ORACLE 提供的系统事件触发器可以在 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_clause] 
     trigger_body;

*  ddl\_event\_list:一个或多个DDL事件，事件间用OR分开
 *  database\_event\_list：一个或多个数据库事件，事件间用OR分开

### 9.2.4、删除触发器 ###

drop trigger trigger_name;

注意：

当删除其他用户模式中的触发器名称，**需要具有 DROP ANY TRIGGER 系统权限**，当删除建立在数据库上

的触发器时，用户需要具有 ADMINISTER DATABASE TRIGGER 系统权限。

此外，**当删除表或视图时，建立在这些对象上的触发器也随之删除**。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_17_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/4415c7fcaa95411692d460efbbec594c.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA56eD5aS055qE5omT5bel5LuU,size_17,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_8_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/535374e27ce0437c998374e627ca1663.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA56eD5aS055qE5omT5bel5LuU,size_8,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_4_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/716ef599b1de40f58bb559f4dff59e44.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA56eD5aS055qE5omT5bel5LuU,size_4,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_19_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/4e47398bd26341ffac850b7754149710.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA56eD5aS055qE5omT5bel5LuU,size_19,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_19_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/f0bb371ab46f464f87c1236544325183.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA56eD5aS055qE5omT5bel5LuU,size_19,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_13_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/fc36682e3db646e68dc8c008fcec817a.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA56eD5aS055qE5omT5bel5LuU,size_13,color_FFFFFF,t_70,g_se,x_16
[d75ee4ee87734f0097fb19223c960a42.png]: https://img-blog.csdnimg.cn/d75ee4ee87734f0097fb19223c960a42.png
[6c71687f29744d86b0684a87bf929453.png]: https://img-blog.csdnimg.cn/6c71687f29744d86b0684a87bf929453.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56eD5aS055qE5omT5bel5LuU_size_5_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/2c7fbed973f6490fb0b83c2683d738d6.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA56eD5aS055qE5omT5bel5LuU,size_5,color_FFFFFF,t_70,g_se,x_16