Oracle学习记录——6.Oracle的过程、函数、包、触发器、控制结构、游标、例外

素颜马尾好姑娘i 2023-06-15 09:43 29阅读 0赞

### 文章目录 ###

*  1. PL/SQL 块的结构和实例
 *   *  PL/SQL介绍
     *  PL/SQL Developer
 *  2.PL/SQL基础
 *   *  PL/SQL介绍
     *  PL/SQL块介绍
 *  3.PL/SQL分类——过程、函数、包、触发器
 *   *  过程
     *  函数
     *  包
     *  触发器
 *  4.定义并使用常量，复合类型
 *   *  标量类型
     *  复合类型
     *  参照类型
 *  5.PL/SQL的进阶——控制结构（分支，循环，控制）
 *   *  条件分支语句
     *  循环语句
     *   *  循环语句——loop
         *  循环语句——while
         *  循环语句——for
     *  顺序控制语句
     *   *  goto
         *  null
 *  6.游标
 *   *  显式游标
     *  隐式游标
     *  动态游标
 *  7.PL/SQL存储过程
 *   *  无返回值的存储过程
     *  有返回值的存储过程
     *   *  有返回值的存储过程（非列表）
         *  有返回值的存储过程（列表\[结果集\]）
     *  编写分页过程
 *  8.触发器
 *  9.例外处理
 *   *  例外介绍
     *  处理预定义例外
     *  非预定义例外
     *  处理自定义例外

# 1. PL/SQL 块的结构和实例 #

## PL/SQL介绍 ##

**PL/SQL** **是什么？**

PL/SQL (procedural language/sql)是 oracle 在标准的 sql 语言上的扩展。

PL/SQL 不仅允许嵌入 sql 语言，还可以定义变量和常量，允许使用条件语句和循环语句，

允许使用例外处理各种错误，这样使得它的功能变得更加强大。

**为什么学** PL/SQL

1.  提高应用程序的运行性能
2.  模块化的设计思想【分页的过程，订单的过程，转账的过程。。】
3.  减少网络传输量
4.  提高安全性（sql 会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL 就 不会）

**PL/SQL的缺点**

移植性不好（换数据库就用不了），

**用什么编写** **PL/SQL**

sqlplus 开发工具

sqlplus 是 oracle 公司提供的一个工具，之前已经使用过了，这里就不再阐述

## PL/SQL Developer ##

PL/SQL Developer开发工具是用于开发 PL/SQL块的集成开发环境(ide)，它是一个独立的产 品，而不是 oracle 的一个附带品。

PL/SQL Developer是一个集成开发环境，专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。MySQL 不支持 PL/SQL ，但支持Navicat Premium。

示例：

编写一个存储过程，该过程可以向某表中添加记录。

1.  创建一个简单的表

create table mytest(name varchar2(30)，passwd varchar2(30));

1.  创建过程 Sql 代码

create or replace procedure sp_pro1 is 
    begin--执行部分 
    insert into mytest values(‘小明'，‘m1234’）;
    end;

1.  常用命令

*  replace：表示如果有 sp\_pro1，就替换
 *  show error：如何查看错误信息：
 *  如何调用该过程：
    
     *  exec 过程名（参数值 1，参数值 2…); // 只能用在CMD平台上
     *  call 过程名（参数值 1，参数值 2…); // 只能用在SQL环境中

1.  编写一个存储过程，该过程可以删除某表记录。

create or replace procedure sp_pro2 is 
    begin--执行部分 
    delete from mytest where name=’小明’;
    end;

# 2.PL/SQL基础 #

## PL/SQL介绍 ##

开发人员使用 pl/sql 编写应用模块时，不仅需要掌握 sql 语句的编写方法，还要掌 握 pl/sql 语句及语法规则。

pl/sql 编程可以使用变量和逻辑控制语句，从而可以编写非 常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用 pl/sql 编程，我们可以轻松地完成非常复杂的查询要求。

**PL/SQL可以做什么**

*  块（编程）
 *  过程（存储过程）
 *  函数
 *  触发器
 *  包

**编写规范**

1.  注释
    
     *  单行注释：-- Sql 代码
     *  多行注释/\*. . . \*/来划分
2.  标志符号的命名规范
    
     *  当定义变量时，建议用 v\_作为前缀，v\_sal
     *  当定义常量时，建议用 c\_作为前缀，c\_rate
     *  当定义游标时，建议用\_cursor作为后缀，emp\_cursor
     *  当定义例外（异常）时，建议用 e\_作为前缀，e\_error

## PL/SQL块介绍 ##

块(block)是 PL/SQL 的基本程序单元，编写 PL/SQL 程序实际上就是编写 PL/SQL 块， 要完成相对简单的应用功能，可能只需要编写一个 PL/SQL 块，但是如果想要实现复杂的功能，可能需要在一个 PL/SQL块中嵌套其它的 PL/SQL 块。

PL/SQL 块由三个部分构成：定义部分，执行部分，例外处理部分。如下所示：

*  declare

定义部分：定义常量、变量、游标、例外、复杂数据类型

*  begin

执行部分：要执行的 PL/SQL 语句和 sql 语句

*  exception

例外处理部分：处理运行的各种错误

*  end;

块的部分构成：

*  定义部分是从 declare 开始的，该部分是可选的；
 *  执行部分是从 begin 开始的，该部分是必须的；
 *  例外处理部分是从 exception 开始的，该部分是可选的。

**实例**如下：

实例1：只包括执行部分的 pl/sql 块

set serveroutput on —-打开输出选项
    begin
    dbms_output.put_line (‘hello’);
    end;

相关说明：

oracle 事先预定义了一些例外，no\_data\_found就是找不到数据的例外。

declare
    --定义变量
    v_ename varchar2(5);
    v_sal number(7，2);
    begin
    --执行部分
    select ename，sal into v_ename，v_sal from emp where empno=&aa;
    --在控制台显示用户名
    dbms_output.put_line('用户名是:'|| v_ename || '工资是:'||v_sal);
    --异常处理
    exception
    when no_data_found then
    dbms_output.put_line('朋友，你的编号输入有误!'');
    end;

# 3.PL/SQL分类——过程、函数、包、触发器 #

## 过程 ##

**过程用于执行特定的操作**，当建立过程时，既可以指定输入参数（in)，也可以指 定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在 sqlplus 中可以使用 create procedure 命令来建立过程。

实例如下：

1.  请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资
2.  调用过程有两种方法：exec，call

Create or replace procedure sp_pro3(spName varchar2，newSal number) is
    --不要写成 number(3，2)，表明类型就可以了，不需要大小。
    begin
    --执行部分，根据用户名去修改工资
    update emp set sal=newSal where ename=spName; 
    end;

## 函数 ##

**函数用于返回特定的数据**，当建立函数时，在函数头部必须包含 return 子句。而在函数体内必须包含 return 语句返回的数据。我们可以使用 create function 来建立函数，实例如下：

一输入雇员的姓名，返回该雇员的年薪
    create function annual_incomec(name varchar2)
    return number is
    annual_salazy number(7，2);
    begin
    --执行部分
    select sal*12 + nvl(comm，0) into annual_salazy from emp 
    where ename=name;
    return annual_salazy;
    end;

如果函数创建过程有编译错误，可以使用 show error 命令显示错误

在sqlplus中调用函数 Sql 代码：

SQL> var income number
    SQL> call annual_incomec(‘scott’）into: income;
    SQL> print income

## 包 ##

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。

1.  我们可以使用create package命令来创建包

**实例：**

--创建一个包 sp_package 
    --声明该包有一个过程 update_sal 
    --声明该包有一个函数 annual_income 
    create package sp_package is 
    procedure update_sal(sp_name varchar2，sp_newSal number); 
    function annual_income(sp_name varchar2) return number; 
    end;

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数

1.  建立包体可以使用create package body命令

**实例：**

--给包 sp_package 实现包体
    Sql 代码--实现包体
    CREATE or replace PACKAGE body sp_package is
    PROCEDURE update_sal(sp_name varchar2,sp_newSal number) is
    begin
     --执行部分
    update emp set sal=sp_newSal where ename=sp_name;
     dbms_output.put_line('员工：'||sp_name||' 工资是'||sp_newSal);
    end;
    FUNCTION annual_income(sp_name varchar2) return number is
    v_annual_salary number(7,2);
    begin
    执行部分
    select (sal+nvl(comm,0))*12 into v_annual_salary from emp where 
    ename=sp_name;
    exception
    when no_data_found then
     dbms_output.put_line('没有此用户');
    return v_annual_salary;
    end;
    end;

1.  如何调用包的过程或是函数

当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。

**实例**：

SQL> call sp_package.update_sal(’SCOTT’，1500);

1.  总结

包是 pl/sql 中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力

## 触发器 ##

触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和 触发的操作，常用的触发事件包括 insert，update，delete 语句，而触发操作实际就是一个 pl/sql 块。可以使用 create trigger 来建立触发器。

特别说明：

在后面会介绍触发器的使用，因为触发器是非常有用的，可维护数据库的安全和一致性。

# 4.定义并使用常量，复合类型 #

在编写 pl/sql 程序时，可以定义变量和常量；在 pl/sql 程序中包括有：

1.  标量类型(scalar)
2.  复合类型(composite)
3.  参照类型(reference)

## 标量类型 ##

**标量——常用类型**

在编写 pl/sql 块时，如果要使用变量，需在定义部分定义变量。pl/sql 中定义变量和常量的语法如下：

identifier [const] datatype [not null] [:=| default expr]

*  identifier:名称
 *  const：指定常量。需要指定它的初始值，且其值是不能改变的
 *  datatype：数据类型
 *  not null：指定变量值不能为 null
 *  :=：给变量或是常量指定初始值
 *  default：用于指定初始值
 *  expr ：指定初始值的 pl/sql 表达式，可以是文本值、其它变量、函数等。

标量定义案例

1.  定义一个变长字符串

v_ename varchar2(10);

1.  定义一个小数,范围 -9999.99~9999.99

v_sal number(6,2);

1.  定义一个小数并给一个初始值为 5.4 :=是 pl/sql 的赋值号

v_sal2 number(6,2):=5.4;

1.  定义一个日期类型的数据

v_hiredate date;

1.  定义一个布尔变量，不能为空，初始值为 false

v_valid boolean not null default false;

**标量——使用标量**

在定义好变量后，就可以使用这些变量。这里需要说明的是 pl/sql 块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)

下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为 0.03)为例。说明变量的使用，看看如何编写。

declare
     c_tax_rate number(3,2):=0.03;
     --用户名
     v_ename varchar2(5);
     v_sal number(7,2);
     v_tax_sal number(7,2);
     begin
     --执行
     select ename,sal into v_ename,v_sal from emp where empno=&no;
     --计算所得税
     v_tax_sal := v_sal*c_tax_rate;
     --输出
     dbms_output.put_line('姓名是：'||v_ename||'工资：'||v_sal||'交税： '||v_tax_sal);
     end;

**标量——使用%type类型**

对于上面的 pl/sql 块有一个问题：

就是如果员工的姓名超过了 5 个字符的话，就会有错误，为了降低 pl/sql 程序的维护工作量，可以使用%type 属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。

我们看看这个怎么使用：

标识符名用户名.表名.列名%type;

比如上例的 v\_ename，这样定义：

v_ename scott.emp.ename%type;

## 复合类型 ##

用于存放多个值的变量。主要包括这几种：

1.  pl/sql 记录
2.  pl/sql 表（了解）

**复合类型**——**pl/sql**

类似于高级语言中的结构体，需要注意的是，当引用 pl/sql 记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

--输入一个员工的编号，显示员工的姓名，工资，职位 
     declare 
     /*定义一个 pl/sql 记录类型 emp_record_type,类型包含 3 个数据 name,salary,job。说白了，就是一个类型可以存放 3 个数据，主要是为了好管理*/ 
     type emp_record_type is record( 
     name emp.ename%type, 
     salary emp.sal%type, 
     title emp.job%type); 
    --定义了一个 sp_record 变量，这个变量的类型是 emp_record_type 
     sp_record emp_record_type; 
     begin 
     select ename, sal, job into sp_record from emp where empno =7788; 
     dbms_output.put_line ('员工名:' || sp_record.name); 
    end; 
    --记录实例

**使用%ROWTYPE**

%**ROWTYPE** 用来获取表中的**行数据类型**。即，记录类型。

例如：

DECLARE 
    v_product_record products%rowtype; /*表明 v_product_record 变量可以存储产品表中的一行记录*/ 
    …… 
    BEGIN 
    /*可以使用如下语句将一行记录直接赋值*/ 
    SELECT * INTO v_product_record FROM products WHERE  product_id = 3; 
    …… 
    END;

使用% ROWTYPE 定义的变量中的成员名称、类型与表中的列名称和类型完全一致。

## 参照类型 ##

参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写 pl/sql 程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj\_type)两种参照变量类型。

**参照变量——ref cursor 游标变量**

使用游标时，当定义游标时不需要指定相应的 select 语句，但是当使用游标时 （open 时）需要指定 select 语句，这样一个游标就与一个 select 语句结合了。

实例如下：

1.  请使用 pl/sql 编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
2.  在 1 的基础上，如果某个员工的工资低于 200 元，就添加 100 元。

declare 
    --定义游标 sp_emp_cursor 
    type sp_emp_cursor is ref cursor; 
    --定义一个游标变量 
    test_cursor sp_emp_cursor; 
    --定义变量 
    v_ename emp.ename%type; 
    v_sal emp.sal%type; 
    begin 
    --执行 
    --把 test_cursor 和一个 select 结合 
    open test_cursor for select ename,sal from emp where deptno=&no; 
    --循环取出 
    loop 
    fetch test_cursor into v_ename,v_sal; 
    --判断是否 test_cursor 为空 
    exit when test_cursor%notfound; 
    dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal); 
    end loop; 
    end;

# 5.PL/SQL的进阶——控制结构（分支，循环，控制） #

pl/sql 的进阶——控制结构

在任何计算机语言(c，java，pascal)都有各种控制语句（条件语句，循环结构， 顺序控制结构…）在 pl/sql 中也存在这样的控制结构。

本节介绍一下知识点：

1.  使用各种 if 语句
2.  使用循环语句
3.  使用控制语句——goto 和 null；

## 条件分支语句 ##

pl/sql 中提供了三种条件分支语句

if -then，… end if 
    if – then – else，…end if 
    if – then– elsif – then… end if

1.  **简单的条件判断 if** **–** **then**
    
    问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于 2000，就给该员工工资增加 10%

create or replace procedure sp_pro6(spName varchar2) is
     --定义
     v_sal emp.sal%type;
    begin
     --执行
     select sal into v_sal from emp where ename=spName;
     --判断
     if v_sal<2000 then
     update emp set sal=sal+sal*10% where ename=spName;
     end if;
    end;

1.  **二重条件分支 if - then -else**
    
    问题：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是 0 就在原来的基础上增加 100；如果补助为 0 就把补助设为 200；

create or replace procedure sp_pro6(spName varchar2) is
     --定义
     v_comm emp.comm%type;
     begin
     --执行
     select comm into v_comm from emp where ename=spName;
     --判断
     if v_comm<>0 then
     update emp set comm=comm+100 where ename=spName;
     else
     update emp set comm=comm+200 where ename=spName;
     end if;
     end;

解法二：

CREATE OR REPLACE PROCEDURE sp_pro2(sp_name varchar2) is
     v_comm emp.comm%type;
    begin
    select comm into v_comm from emp where ename=sp_name;
    if v_comm <>0 then
    update emp set comm=comm+100 where ename=sp_name;
    else
    update emp set comm=200 where ename=sp_name;
    end if;
    select comm into v_comm from emp where ename=sp_name;
     dbms_output.put_line('名字：'||sp_name||' 奖金：'||v_comm);
    end;

\*\*3. \*\*多重条件分支 **if** – **then** – **elsif** –**then**

问题：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是 PRESIDENT就给他的工资增加 1000，如果该雇员的职位是 MANAGER 就给他的工资增加 500，其它职位的雇员工资增加 200

create or replace procedure sp_pro6(spNo number) is
     --定义
     v_job emp.job%type;
    begin
     --执行
     select job into v_job from emp where empno=spNo;
     if v_job='PRESIDENT' then
     update emp set sal=sal+1000 where empno=spNo;
     elsif v_job='MANAGER' then
     update emp set sal=sal+500 where empno=spNo;
     else
     update emp set sal=sal+200 where empno=spNo;
     end if;
    end;

## 循环语句 ##

*  **基本循环**

LOOP 
    执行语句； 
    EXIT WHEN 条件； 
    END LOOP;

*  **WHILE** **循环**

WHILE 条件 LOOP 
    执行语句； 
    END LOOP;

*  **FOR** **循环**

FOR 计数器 IN 低界..高界 LOOP 
    执行语句； 
    END LOOP;

### 循环语句——loop ###

loop 循环是 pl/sql 中最简单的循环语句，这种循环语句以 loop 开头，以 end loop 结尾，这种循环至少会被执行一次。

\*\*案例：\*\*现有一张表 users，表结构如下：

用户 id | 用户名

请编写一个过程，可以输入用户名，并循环添加 10 个用户到 users 表中，用户编号从 1 开始增加

create or replace procedure sp_pro6(spName varchar2) is
     --定义 :=表示赋值
     v_num number:=1;
    begin
    --执行部分
    loop
     insert into users values(v_num,spName);
     --判断是否要退出循环
     exit when v_num=10;
     --自增
     v_num:=v_num+1;
     end loop;
    end;

### 循环语句——while ###

基本循环至少要执行循环体一次，而对于 while 循环来说，只有条件为 true 时，才会执行循环体语句，while 循环以 while…loop 开始，以 end loop 结束。

案例：现有一张表 users，表结构如下：

用户 id| 用户名

问题：请编写一个过程，可以输入用户名，并循环添加 10 个用户到 users 表中， 用户编号从 11 开始增加。

create or replace procedure sp_pro6(spName varchar2) is
     --定义 :=表示赋值
     v_num number:=11;
    begin
     while v_num<=20 loop
     --执行
     insert into users values(v_num,spName);
     v_num:=v_num+1;
     end loop;
    end;

### 循环语句——for ###

FOR 循环也是经常使用的一种循环语句，使用 FOR 循环，循环将会执行指定的次数。基本 for 循环的语法结构如下：

FOR index in [REVERSE]
     lower_bound..upper_bound LOOP
     statement1;
     statement2;
     ...
     END LOOP

其中 index 是 FOR 循环的计数器变量，计数器从最小值到最大值按步进一递增。

REVERSE 是反转的意思，正常的循环计数器从小到大递增，使用 REVERSE 将使计数器从大到小递减。

begin 
     for i in 1..10 loop 
     insert into users values (i, 'xiaoming'); 
     end loop; 
    end;

我们可以看到控制变量 i，在隐含中就在不停地增加。

## 顺序控制语句 ##

### goto ###

goto 语句用于跳转到特定符号去执行语句。注意由于使用 goto 语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用 goto 语句（多层循环可能会有用处）。

基本语法如下 goto lable，其中 lable 是已经定义好的标号名，示例如下：

declare
     i int := 1;
    begin
     loop
     dbms_output.put_line('输出 i=' || i);
    if i = 10then
     goto end_loop;
     end if;
     i := i + 1;
     end loop;
    <<end_loop>>
     dbms_output.put_line('循环结束');
    end;

### null ###

null 语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用 null 语句的主要好处是可以提高 pl/sql 的可读性。

declare
     v_sal emp.sal%type;
     v_ename emp.ename%type;
    begin
     select ename, sal into v_ename, v_sal from emp where empno =&no;
     if v_sal < 3000 then
     update emp set comm = sal * 0.1 where ename = v_ename;
     else
    null;
    end if;
    end;

# 6.游标 #

当运行 DML 语句时，PL/SQL 打开一个内建游标并处理结果，游标是维护查询结果 的内存中的一个区域，本质而言，游标实际上是一种能从包括多条数据记录的结果集 中每次提取一条记录的机制。游标在运行 DML 语句时打开，完成后关闭。

游标的种类分为：

*  显式游标
 *  隐式游标
 *  动态游标

## 显式游标 ##

*  声明游标：CURSOR cursor\_name(游标名称，自己给的) IS （游标是给那个表或者结果集定义） select\_statement;
 *  为查询打开游标：OPEN cursor\_name;
 *  取得结果放入 PL/SQL 变量中：FETCH cursor\_name INTO list\_of\_variables;
 *  关闭游标：CLOSE cursor\_name;

**游标的相关属性**

*  **cursor\_name%notfound 用于判断游标是否循环结束，true 表示结束**
 *  %found 和%notfound 相反
 *  %rowcount 用于显示已经访问过的记录数
 *  %isopen 判断游标是否打开

例1：一从键盘输入一个部门号，将部门的员工信息输出来（姓名，工资）

declare
     cursor cursor_emp is select * from emp where deptno=&dept;
     rr scott.emp%rowtype;--(或者 rr cursor_emp%rowtype 更好用)
    begin 
     open cursor_emp;
     loop
     fetch cursor_emp into rr;
     exit when cursor_emp%notfound;
     dbms_output.put_line(rr.ename||'员工的工资是'||rr.sal);
     end loop;
     close cursor_emp;
    end;

例2：对一些员工加工资，800 以下 1.2 倍，800 到 2000 是原工资的 1.1 倍，大于 2000， 不增加。

declare
     cursor cursor_emp is select * from emp for update; --声明游标,注意后面要
    使用 current of 的时候，查询上需要添加 for update
     rr scott.emp%rowtype;
    begin 
     open cursor_emp;--打开游标
     loop
     fetch cursor_emp into rr; --提取数据
     exit when cursor_emp%notfound; --退出
    if rr.sal <=800 then
     update emp set sal=sal*1.2 where current of cursor_emp;
     elsif rr.sal<2000 then
     update emp set sal=sal*1.1 where current of cursor_emp;
     else
     dbms_output.put_line(rr.ename||'没有涨工资');
     end if;
     end loop;
     close cursor_emp; --关闭游标
    end;

**带参数的显式游标**

与存储过程和函数相似，可以将参数传递给游标并在查询中使用，这对于处理在某种条件下打开游标的情况非常有用。语法如下：

*  CURSOR <cursor\_name>(<param\_name>\[,<param\_type>\]…) IS select\_statement;

例：对上面的例子操作，对某个部门按照以上规则加工资

declare
     cursor cursor_emp(dno scott.emp.DEPTNO%type) is select * from emp 
    where deptno=dno for update ;
     rr scott.emp%rowtype;
    begin 
     open cursor_emp(20);
     loop
     fetch cursor_emp into rr;
     exit when cursor_emp%notfound;
     if rr.sal <=800 then
     update emp set sal=sal*1.2 where current of cc;
     elsif rr.sal<2000 then
     update emp set sal=sal*1.1 where current of cc;
     else
     dbms_output.put_line(rr.ename||'没有涨工资');
     end if;
     end loop;
     close cursor_emp;
    end;

**游标的for循环**

使用 FOR 循环来访问游标，可以简化游标的多个控制过程。在 FOR 循环中的游标，被隐式的打开、循环取操作和关闭游标，而存放游标数据的记录类型变量也不用声明，由 FOR 循环隐式的声明。

*  循环游标用于简化游标处理代码
 *  当用户需要从游标中提取所有记录时使用
 *  循环游标的语法如下：
    
        FOR <record_index> IN <cursor_name> 
        LOOP 
        <executable statements> 
        END LOOP;

范例

declare
    --游标结构声明定义
    cursor ca isselect*from emp where deptno=10;
    carc ca%rowtype;--游标变量
    begin
    for carc in ca loop
    dbms_output.put_line(carc.ename);
    endloop;
    end;

declare
    cursor ca(dno emp.deptno%type)isselect*from emp where
    deptno=dno;
    carec ca%rowtype;
    begin
    for carec in ca(10)loop
    dbms_output.put_line(carec.ename);
    endloop;
    end;

## 隐式游标 ##

Pl/sql 为所有 sql 数据操纵语句隐式声明游标,作为隐式游标，用户不能直接命名和控制隐式游标。

–oracle 数据库中游标的属性:

*  SQL%FOUND:dml 语句返回一行或者多行时，%found 属性返回 true.
 *  SQL%notfound:没有返回影响行数，返回 true
 *  SQL%rowcount:返回受影响的行数,如果没有返回行，返回 0。

在使用隐式游标属性要注意，只能取出最后一条执行的 SQL 语句的隐式游标属性。

范例

declare
    begin
    update emp set sal=sal+10where empNO=73619;
    ifSQL%foundthen
    dbms_output.put_line('表被更新');
    endif;
    ifSQL%isopenthen
    dbms_output.put_line('test');
    else
    dbms_output.put_line('隐式游标始终为false');
    endif;
    dbms_output.put_line(SQL%rowcount);
    end;

## 动态游标 ##

游标变量也叫动态游标，通过 REF CORSOR 方式定义，它仍然是指向一段 SQL语句的内存地址的指针。和动态游标相比，之前在声明时就定义好 SELECT 语句的游标称作静态游标，**而动态游标在打开时才指定其对应的** **SELECT** **语句**。

范例：请使用 pl/sql 编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。

declare
     --定义游标 sp_emp_cursor
     type sp_emp_cursor is ref cursor;
     --定义一个游标变量
     test_cursor sp_emp_cursor;
     --定义变量
     v_ename emp.ename%type;
     v_sal emp.sal%type;
    begin
     --执行
     --把 test_cursor 和一个 select 结合
     open test_cursor for select ename,sal from emp where deptno=&no;
     --循环取出
     loop
     fetch test_cursor into v_ename,v_sal;
     --判断是否 test_cursor 为空
     exit when test_cursor%notfound;
     dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);
     end loop;
     close test_cursor；
    end;

# 7.PL/SQL存储过程 #

分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习 pl/sql编程开发就一定要掌握该技术。

## 无返回值的存储过程 ##

为了让大家伙比较容易接受分页过程编写，还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程。

案例：现有一张表 book，表结构如下：

书号 |书名 | 出版社

请写一个过程，可以向 book 表添加书。

*  in：表示这是一个输入参数，默认为 in
 *  out：表示一个输出参数

create table book(
    bookId number primary key,
    bookName varchar2(50),
    publishHouse varchar2(50)
    );

create or replace procedure sp_pro7(spBookId in number,spbookName in 
    varchar2,sppublishHouse in varchar2) is
    begin
    insert into book values(spBookId,spbookName,sppublishHouse);
    end;

## 有返回值的存储过程 ##

### 有返回值的存储过程（非列表） ###

如何处理有返回值的存储过程：

例1：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

--有输入和输出的存储过程
    create or replace procedure sp_pro8
    (spno in number, spName out varchar2)is
    begin
    select ename into spName from emp where empno=spno;
    end;

例1扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

create or replace procedure sp_pro8(spno in number, spName out varchar2,spSal out number,spJob outvarchar2) is 
    begin 
    select ename,sal,job into spName,spSal,spJob from emp where empno = spno; 
    end;

### 有返回值的存储过程（列表\[结果集\]） ###

例1：编写一个过程，输入部门号，返回该部门所有雇员信息。

对该题分析如下：

由于 oracle 存储过程没有返回值，它的所有返回值都是通过 out 参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用 pagkage 了。所以要分两部分：

返回结果集的过程

1.  建立一个包，在该包中，定义类型 test\_cursor,是个游标。如下：

create or replace package testpackage as 
    TYPE test_cursor is ref cursor; 
    end testpackage;

1.  建立存储过程。如下：

create or replace procedure sp_pro9(spNo in number,p_cursor out 
    testpackage.test_cursor) is 
    begin 
    open p_cursor for select * from emp where deptno = spNo;end;

测试案例

declare
     v_deptno emp.deptno%type;
     v_test_cursor testpackage.test_cursor;
    row_emp emp%rowtype;
    begin
     sp_pro3(10,v_test_cursor);
    loop
    fetch v_test_cursor into row_emp;
    exit when v_test_cursor%notfound;
     dbms_output.put_line(' 用 户 名 ： '||row_emp.ename||' 工 资 '||row_emp.sal);
    end loop;
    end;

## 编写分页过程 ##

有了上面的基础，相信大家可以完成分页存储过程了。 要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。

--oracle 的分页
    select rownum,t1.* from (select * from emp) t1 
    where rownum<=10;
    --在分页时，大家可以把下面的 sql 语句当做一个模板使用
    select * from(select rownum rn,t1.* from (select * from emp) t1 where 
    rownum<=10)
    where rn>=6;
    --显示行号和所有的字段信息
    selectrownum,t1.*from(select*from emp) t1;
    --显示前10条记录
    selectrownum,t1.*from(select*from emp) t1 whererownum<=10;
    --显示6-10条记录
    Select * from(select rownum rn,t1.*from(select * from emp)temp where
    rownum<=10)where temp.rn>=6;
    --开发一个包
    --建立一个包，在该包中，定义类型 test_cursor,是个游标。如下：
    Sql 代码
    create or replace package testpackage as
    TYPE test_cursor is ref cursor;
    end testpackage;
    --开始编写分页的过程
    create or replace procedure fenye
    (tableName in varchar2,
    Pagesize in number,--每页显示记录数
    pageNow in number, --当前页码
    myrows out number,--总记录数
    myPageCount out number,--总页数
    p_cursor out testpackage.test_cursor--返回的记录集
    ) is
    --定义部分
    --定义 sql 语句字符串 v_sql varchar2(1000);
    --定义两个整数 v_begin number:=(pageNow-1)*Pagesize+1;
    v_end number:=pageNow*Pagesize;
    begin
    --执行部分
    v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') Where rn>='||v_begin;
    --把游标和 sql 关联
    open p_cursor for v_sql;
    --计算 myrows 和 myPageCount
    --组织一个 sql 语句
    v_sql:='select count(*) from '||tableName;
    --执行 sql,并把返回的值，赋给 myrows； execute immediate v_sql into myrows;
    --计算 myPageCount
    if mod(myrows,Pagesize)=0 then
    myPageCount:=myrows/Pagesize;
    else
    myPageCount:=myrows/Pagesize+1
    end if;
    --关闭游标
    --close p_cursor;
    end;

# 8.触发器 #

触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括 insert,update,delete 语句，而触发操作实际就是一个 pl/sql 块。可以使用 create trigger 来建立触发器。触发器是特殊的 pl/sql 块，该 pl/sql 块是在特定的事件出现时（增删改数据时，创建删除用户时，对数据库进行启动关闭时等）自动调用的。触发器与子程序的区别在于；子程序由用户或应用程序显示调用，而触发器不能被用户或应用程序直接调用，oracle 会在事件发生时自动插入触发器执行的代码.。

触发器语法：

create [or replace] trigger 触发器名称
    { before|after|instead of}
    {
       insert|delete|update [of 列 1,列 2....]}
    on 表名或者视图名
    trigger_body

常用触发器结构：

declare
     -- local variables here
    begin

触发器主体：

end mytrigger;

例1：创建触发器

Create or replace trigger hello_dept
    Before
    Insert or update or delete on dept;
    begin 
    dbms_output.put_line('hello,触发器 hello_emp 自动被调用');
    end hello_dept;

例2：创建触发器：当修改表的时候将操作信息放入到历史记录中

//创建触发器的依附表 (日志表) 
    create table emp2_log 
     ( 
     ename varchar2(30) , --谁操作的
     eaction varchar2(20), --什么操作
     etime date --操作时间
     ); 
     create or replace trigger trig 
     after 
    insert or delete or update on emp2 
    for each row
    begin
     if inserting then 
     insert into emp2_log values(USER, 'insert', 
    sysdate); 
     elsif updating then 
     insert into emp2_log values(USER, 'update', 
    sysdate); 
     elsif deleting then 
     insert into emp2_log values(USER, 'delete', 
    sysdate); 
     end if; 
     end;

**行级触发器**

行级出发是在每行数据操作时都会出发执行，语法格式如下：

create [or replace] trigger 触发器名称 
    {before|after|instead of} 
    {
       insert|delete|update [of 列 1,列 2....]} 
    on 表名或者视图名 
    FOR EACH ROW 
    trigger_body

# 9.例外处理 #

## 例外介绍 ##

oracle 将例外分为预定义例外，非预定义例外和自定义例外三种。

*  预定义例外：用于处理常见的 oracle 错误
 *  非预定义例外：用于处理预定义例外不能处理的例外
 *  自定义例外：用于处理与 oracle 错误无关的其它情况

## 处理预定义例外 ##

预定义例外是由 pl/sql 所提供的系统例外。当 pl/sql 应用程序违反了 oracle 规定的限制时，则会隐含的触发一个内部例外。pl/sql 为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外。

1.  **预定义例外case\_not\_found**

在开发 pl/sql 块中编写 case 语句时，如果在 when 子句中没有包含必须的条件分支，就会触发 case\_not\_found 的例外：

create or replace procedure sp_pro6(spno number) is
    v_sal emp.sal%type;
    begin
    select sal into v_sal from emp where empno = spno;
    case
    when v_sal <1000 then
    update emp set sal = sal + 100 where empno = spno;
    when v_sal <2000 then
    update emp set sal = sal + 200 where empno = spno;
    end case;
    exception
    when case_not_found then
    dbms_output.put_line('case 语句没有与' || v_sal || '相匹配的条件 ');
    end;

1.  **预定义例外cursor\_already\_open**

当重新打开已经打开的游标时，会隐含的触发例外cursor\_already\_open

declare
    cursor emp_cursor is select ename, sal from emp;
    begin
    open emp_cursor;
    for emp_record1 in emp_cursor loop
    dbms_output.put_line(emp_record1.ename);
    end loop;
    exception
    when cursor_already_open then
    dbms_output.put_line('游标已经打开');
    end;

1.  **预定义例外**

在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup\_val\_on\_index 例外

begin
    insert into dept values (10, '公关部', '西安');
    exception
    when dup_val_on_index then
    dbms_output.put_line('在 deptno 列上不能出现重复值');
    end;

1.  **预定义例外 invalid\_cursor**

当试图在不合法的游标上执行操作时，会触发该例外

例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外

declare
    cursor emp_cursor is select ename, sal from emp;
    emp_record emp_cursor%rowtype;
     begin
    --open emp_cursor; --打开游标
    fetch emp_cursor into emp_record;
    dbms_output.put_line(emp_record.ename);
    close emp_cursor;
    exception
    when invalid_cursor then
    dbms_output.put_line('请检测游标是否打开');
    end;

1.  **预定义例外** **invalid\_number**

当输入的数据有误时，会触发该例外

比如：数字 1100 写成了 loo 就会触发该例外

begin
    update emp set sal= sal + 'loo';
    exception
    when invalid_number then
    dbms_output.put_line('输入的数字不正确');
    end;

1.  **预定义例外 no\_data\_found**

下面是一个 pl/sql 块，当执行 select into 没有返回行，就会触发该例外

declare
    v_sal emp.sal%type;
    begin
    select sal into v_sal from empwhere ename='&name';
    exception
    when no_data_found then
    dbms_output.put_line('不存在该员工')；
    end;

1.  **预定义例外** **too\_many\_rows**

当执行 select into 语句时，如果返回超过了一行，则会触发该例外.

declare
    v_ename emp.ename%type;
    begin
    select ename into v_ename from emp ; exception
    when too_many_rows then
    dbms_output.put_line('返回了多行');
    end;

1.  **预定义例外 zero\_divide**

当执行 2/0 语句时，则会触发该例外。

1.  **预定义例外 value\_error**

当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value\_error，比如：

declare
    v_ename varchar2(5);
    begin
    select ename into v_ename from emp where empno = &no1;
    dbms_output.put_line(v_ename);
    exception
    when value_error then
    dbms_output.put_line('变量尺寸不足');
    end;

其它预定义例外(这些例外不是在 pl/sql 里触发的，而是在用 oracle 时触发的， 所以取名叫其它预定义例外)

1.  login\_denied

当用户非法登录时，会触发该例外

1.  not\_logged\_on

如果用户没有登录就执行 dml 操作，就会触发该例外

1.  storage\_error

如果超过了内存空间或是内存被损坏，就触发该例外

1.  timeout\_on\_resource

如果 oracle 在等待资源时，出现了超时就触发该例外

## 非预定义例外 ##

非预定义例外用于处理与预定义例外无关的 oracle 错误。使用预定义例外只能处理 21 个 oracle 错误，而当使用 pl/sql 开发应用程序时，可能会遇到其它的一些 oracle 错误。比如在 pl/sql 块中执行 dml 语句时，违反了约束规定等等。在这样的情况下，也可以处理 oracle 的各种例外，因为非预定义例外用的不多，这里就不举例了。

## 处理自定义例外 ##

预定义例外和自定义例外都是与 oracle 错误相关的，并且出现的 oracle 错误会隐含的触发相应的例外；而自定义例外与 oracle 错误没有任何关联，它是由开发人员为特定情况所定义的例外。

问题：请编写一个 pl/sql 块，接收一个雇员的编号，并给该雇员工资增加 1000元，如果该雇员不存在，请提示。

--自定义例外
    create or replace procedure ex_test(spNo number)is
    begin
    --更新用户 sal
    update emp set sal=sal+1000 where empno=spNo;
    end;

运行，该过程被成功创建。

SQL> exec ex_test(56);

PL/SQL 过程被成功完成

这里，编号为 56 是不存在的，刚才的报异常了，为什么现在不报异常呢？

因为刚才的是 select 语句

怎么解决这个问题呢？修改代码，如下：

--自定义例外
    create or replace procedure ex_test(spNo number)
    is
    --定义一个例外
    myex exception;
    begin
    --更新用户 sal
    update emp set sal=sal+1000 where empno=spNo;
    --sql%notfound 这是表示没有 update
    --raise myex;触发 myex
    if sql%notfound then
    raise myex;
    end if;
    exception
    when myex then
    dbms_output.put_line('没有更新任何用户')；
    end;

现在再测试一次：

SQL> exec ex_test(56);

成功