oracle学习笔记(二十六)：PLSQL

---

一、概念：

SQL99 标准：

1、是操作所有 关系型数据库 的准则；

2、是第四代语言；

3、是一种结构化查询语言；

4、只需发出合法合理的命令，就有对应的结果显示；

SQL 的特点：

1、交互性强，非过程化；

2、数据库操纵能力强，只需发送命令，无需关注如何实现；

3、多表操作时，自动导航简单，例如：

        select emp.empno,emp.sal,dept.dname

        from emp,dept

        where emp.deptno = dept.deptno

4、容易调式，错误提示，直接了当；

5、SQL 强调结果；

PLSQL 是什么？

1、是专用于 oracle 服务器，在 SQL 基础之上，添加了一些过程化控制语句；

2、过程化包括有：类型定义、判断、循环、游标、异常（或例外）处理等...

3、PLSQL 强调过程；

为什么要用 PLSQL？

因为 SQL 是第四代命令式语言，无法显示处理过程化的业务；

所以需要用一个过程化程序设计语言来弥补 SQL 的不足之处；

SQL 和 PLSQL 不是替代关系，是弥补关系；

PLSQL 程序的完整组成结构如下：

[declare]
                说明部分;（变量说明、光标说明、例外说明）
        begin
                语句系列;（DML语句、TCL事务控制语句）
        [exception]
                例外处理语句;
        end;
        /

注意：在 PLSQL 中，分号（;）表示每条语句的结束，斜杠（/）表示整个 PLSQL 程序结束；

[declare]、 [exception] 如果没有的话可以省略，begin 和 end 必须要有；

书写 PLSQL 的工具有：

1、SQLPLUS 工具；

2、SQLDeveloper 工具；

3、第三方工具（PLSQL & 其它）；

PLSQL 与 SQL 执行有什么不同？

1、SQL 是单条执行的；

2、PLSQL 是整体执行的，不能单条执行；

---

二、一个简单的 PLSQL 语句：

1、写一个 PLSQL，输出 “hello world” 字符串，语法：dbms_output.put_line("需要输出的字符串")

SQL> begin
  2      -- 向 SQLPLUS 客户端工具输出字符串
  3      dbms_output.put_line('hello world');
  4  end;
  5  /

PL/SQL 过程已成功完成。

发现并没有输出 "hello world"，这是由于客户端工具导致的，默认情况下，客户端不显示 PLSQL 的执行结果；

可以通过下面语句 开启或关闭 显示 PLSQL 的执行结果：set serveroutput on/off;

SQL> set serveroutput on;
SQL> begin
  2      -- 向 SQLPLUS 客户端工具输出字符串
  3      dbms_output.put_line('hello world');
  4  end;
  5  /
hello world

PL/SQL 过程已成功完成。

dbms_output 是 oracle 中的一个输出对象（dbms = database manager system，数据库管理系统）；

put_line 是上述对象的一个方法，用于输出一行数据（自动换行）；

---

三、PLSQL 的类型：

1、使用基本类型变量，常量和注释，求 10+100 的和：

SQL> declare
  2      -- 声明一个数字型变量 mysum，最大存储 3 位数值；
  3      -- := 是赋值语句，表示 mysum 的初始赋值为 0；
  4      mysum number(3) := 0;
  5
  6      -- 声明一个字符串类型变量 tip，最大存储 10 个字节，初始赋值为 '结果是 ';
  7      tip varchar2(10) := '结果是 ';
  8  begin
  9      /* 计算 10+100 的值，并赋值给 mysum 变量 */
 10      mysum := (10 + 100);
 11
 12      /* 输出 mysum 变量的值；|| 是连接符； */
 13      dbms_output.put_line(tip || mysum);
 14  end;
 15  /
结果是 110

PL/SQL 过程已成功完成。

2、输出 7369 号员工姓名和工资，格式如下：7369号员工的姓名是 SMITH，薪水是 800，语法：使用表名.字段%type

SQL> declare
  2      -- 声明一个变量 pename，类型和 emp 表中的 ename 类型一样
  3      pename emp.ename%type;
  4
  5      -- 声明一个变量 psal，类型和 emp 表中的 sal 类型一样
  6      psal   emp.sal%type;
  7  begin
  8      -- sql 语句：
  9      -- select ename,sal from emp where empno = 7369;
 10      -- PLSQL 语句：将 ename 的值放入 pename 变量中，sal 的值放入 psal 变量中
 11      select ename,sal into pename,psal from emp where empno = 7369;
 12      -- 输出
 13      dbms_output.put_line('7369号员工的姓名是 ' || pename || ', 薪水是 ' || psal);
 14  end;
 15  /
7369号员工的姓名是 SMITH, 薪水是 800

PL/SQL 过程已成功完成。

3、输出 7369 号员工姓名和工资，格式如下：7369号员工的姓名是 SMITH，薪水是 800，语法：使用表名%rowtype

SQL> declare
  2      -- 声明一个变量，emp_record，类型和 emp 表的结构一致；
  3      -- 即 emp 表有 8 个字段，emp_record 变量中也有 8 个字段；
  4      emp_record emp%rowtype;
  5  begin
  6      -- 查询数据放入 emp_record 变量中：
  7      -- emp_record 变量中有 8 个字段，那么 select 语句就必须要查询 8 个字段，否则报错；
  8      select * into emp_record from emp where empno = 7369;
  9
 10      -- 输出结果：
 11      dbms_output.put_line('7369号员工的姓名是 ' || emp_record.ename || ', 薪水是 ' || emp_record.sal);
 12  end;
 13  /
7369号员工的姓名是 SMITH, 薪水是 800

PL/SQL 过程已成功完成。

当定义变量时，该变量的类型与表中某字段的类型相同时，可以使用 表名.字段名%type；

当定义变量时，该变量与整个表结构完全相同时，可以使用 表名%rowtype，此时通过 变量名.字段名，可以取变量的值；

---

四、PLSQL 的判断：

1、使用 if-else-end if 显示今天星期几，是 "工作日" 还是 "休息日"：

SQL> declare
  2      -- 声明一个变量 pday，存储当前是 星期几
  3      pday varchar2(10);
  4  begin
  5      -- 查询今天是星期几，并放入 pday 变量中
  6      select to_char(sysdate,'day') into pday from dual;
  7
  8      -- 输出：
  9      dbms_output.put_line('今天是 ' || pday);
 10
 11      -- 判断今天是 '休息日' 还是 '工作日'
 12      if (pday in ('星期六','星期日')) then
 13          dbms_output.put_line('休息日');
 14      else
 15          dbms_output.put_line('工作日');
 16      end if;
 17  end;
 18  /
今天是 星期四
工作日

PL/SQL 过程已成功完成。

2、从键盘接收值，使用 if-elsif-else-end if 显示输入的分数（score）所处的等级：

SQL> declare
  2      -- 声明一个变量 pscore，存储输入的 分数
  3      pscore number(3) := &pscore;
  4  begin
  5      -- 判断分数所处的等级
  6      if (pscore < 60) then
  7          dbms_output.put_line('未及格');
  8      elsif (pscore < 70) then
  9          dbms_output.put_line('及格');
 10      elsif (pscore < 80) then
 11          dbms_output.put_line('一般');
 12      elsif (pscore < 90) then
 13          dbms_output.put_line('良好');
 14      elsif (pscore <= 100) then
 15          dbms_output.put_line('优秀');
 16      else
 17          dbms_output.put_line('超神');
 18      end if;
 19  end;
 20  /
输入 pscore 的值:  88
原值    3: pscore number(3) := &pscore;
新值    3: pscore number(3) := 88;
良好

PL/SQL 过程已成功完成。

---

五、PLSQL 的循环：

1、使用 loop 循环显示 1-5：

SQL> declare
  2      i number(2) := 1;
  3  begin
  4      loop
  5          -- 当 i>5 时，退出循环
  6          exit when i > 5;
  7          -- 输出 i 的值
  8          dbms_output.put_line(i);
  9          -- 变量自加
 10          i := i + 1;
 11      end loop;
 12  end;
 13  /
1
2
3
4
5

PL/SQL 过程已成功完成。

2、使用 while 循环显示 1-5：

SQL> declare
  2      i number(2) := 1;
  3  begin
  4      -- 当 i<6 时进入循环（即 i>=6 时退出循环）
  5      while (i < 6)
  6      loop
  7          -- 输出 i
  8          dbms_output.put_line(i);
  9          -- 变量值增加
 10          i := i + 1;
 11      end loop;
 12  end;
 13  /
1
2
3
4
5

PL/SQL 过程已成功完成。

3、使用 while 循环，向 emp 表中插入 999 条记录：

SQL> declare
  2      i number(4) := 1;
  3  begin
  4      -- 当 i<1000 时进入循环（即 i>=1000 时退出循环）
  5      while( i < 1000 )
  6      loop
  7          -- 插入数据
  8          insert into emp(empno,ename) values(i,'Jack');
  9          i := i + 1;
 10      end loop;
 11  end;
 12  /

PL/SQL 过程已成功完成。

4、使用 for 循环显示 20-25：

SQL> declare
  2      i number(2) := 20;
  3  begin
  4      -- 表示 i 的值从 20 开始，到 25 结束，中间两个点(..) 是固定写法；
  5      -- for 循环的步长是固定的，每次只能增加 1；
  6      for i in 20 .. 25
  7      loop
  8          dbms_output.put_line(i);
  9      end loop;
 10  end;
 11  /
20
21
22
23
24
25

PL/SQL 过程已成功完成。

---

六、PLSQL 的游标：

1、什么是游标（Cursor）？

类似于 jdbc 中 ResultSet 对象的功能，从上到下依次获取每一条记录的功能；

2、使用无参游标，查询所有员工的姓名和工资：

SQL> declare
  2      -- 定义游标（cemp），并指向 select 语句查询的结果集；
  3      cursor cemp is select ename,sal from emp;
  4
  5      -- 声明两个变量，用来存储游标指向记录的值
  6      vename emp.ename%type;
  7      vsal   emp.sal%type;
  8  begin
  9      -- 打开游标，这时游标位于第一条记录之前
 10      open cemp;
 11
 12      -- 循环移动游标，获取数据
 13      loop
 14         -- 向下移动一次游标，并将游标指向记录的值放入变量中
 15         fetch cemp into vename,vsal;
 16
 17         -- 当游标找不到记录时，退出循环
 18         exit when cemp%notfound;
 19
 20         -- 输出结果
 21         dbms_output.put_line(vename || ' -------- ' || vsal);
 22      end loop;
 23
 24      -- 关闭游标
 25      close cemp;
 26  end;
 27  /
SMITH -------- 800
ALLEN -------- 1600
WARD -------- 1250
JONES -------- 2975
MARTIN -------- 1250
BLAKE -------- 2850
CLARK -------- 2450
SCOTT -------- 3000
KING -------- 5000
TURNER -------- 1500
ADAMS -------- 1100
JAMES -------- 950
FORD -------- 3000
MILLER -------- 1300

PL/SQL 过程已成功完成。

3、使用带参游标，查询 10 号部门的员工姓名和工资：

SQL> declare
  2      -- 定义一个带参游标，声明一个形参（参数名 参数类型），多个参数之间用逗号分隔；
  3      -- 游标指向 select 语句查询的结果集；
  4      cursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;
  5
  6      -- 声明两个变量，用来存储游标指向记录的值
  7      pename emp.ename%type;
  8      psal emp.sal%type;
  9  begin
 10      -- 开启游标，传入实参
 11      open cemp(10);
 12
 13      -- 循环移动游标，获取数据
 14      loop
 15          -- 移动一次游标，并将游标指向当前记录的值 放入变量中
 16          fetch cemp into pename,psal;
 17
 18          -- 当游标没有指向记录时，退出循环
 19          exit when cemp%notfound;
 20
 21          -- 输出结果
 22          dbms_output.put_line(pename || ' 的薪水是 ' || psal);
 23      end loop;
 24
 25      -- 关闭游标
 26      close cemp;
 27  end;
 28  /
CLARK 的薪水是 3250
KING 的薪水是 5400
MILLER 的薪水是 1700

PL/SQL 过程已成功完成。

开启游标时，传入的实参，也可以使用 & 通过控制台输入：

SQL> declare
  2      -- 定义一个带参游标，声明一个形参（参数名 参数类型），多个参数之间用逗号分隔；
  3      -- 游标指向 select 语句查询的结果集；
  4      cursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;
  5
  6      -- 声明两个变量，用来存储游标指向记录的值
  7      pename emp.ename%type;
  8      psal emp.sal%type;
  9  begin
 10      -- 开启游标，传入实参
 11      open cemp(&pdeptno);
 12
 13      -- 循环移动游标，获取数据
 14      loop
 15          -- 移动一次游标，并将游标指向当前记录的值 放入变量中
 16          fetch cemp into pename,psal;
 17
 18          -- 当游标没有指向记录时，退出循环
 19          exit when cemp%notfound;
 20
 21          -- 输出结果
 22          dbms_output.put_line(pename || ' 的薪水是 ' || psal);
 23      end loop;
 24
 25      -- 关闭游标
 26      close cemp;
 27  end;
 28  /
输入 pdeptno 的值:  20
原值   11:     open cemp(&pdeptno);
新值   11:     open cemp(20);
SMITH 的薪水是 1200
JONES 的薪水是 3775
SCOTT 的薪水是 4000
ADAMS 的薪水是 1500
FORD 的薪水是 4000

PL/SQL 过程已成功完成。

4、使用无参游标，真正给员工涨工资，ANALYST涨1000，MANAGER涨800，其它涨400；要求显示编号，姓名，职位，薪水

SQL> declare
  2      -- 定义游标
  3      cursor cemp is select empno,ename,job,sal from emp;
  4
  5      -- 定义变量
  6      pempno emp.empno%type;
  7      pename emp.ename%type;
  8      pjob   emp.job%type;
  9      psal   emp.sal%type;
 10  begin
 11      -- 开启游标
 12      open cemp;
 13
 14      -- 循环移动游标
 15      loop
 16          -- 移动一次游标，并将游标指向的当前记录的值放入变量中
 17          fetch cemp into pempno,pename,pjob,psal;
 18
 19          -- 当游标找不到数据时，退出循环
 20          exit when cemp%notfound;
 21
 22          -- 判断语句
 23          if (pjob = 'ANALYST') then
 24              update emp set sal = sal + 1000 where empno = pempno;
 25              psal := psal + 1000;
 26          elsif (pjob = 'MANAGER') then
 27              update emp set sal = sal + 800 where empno = pempno;
 28              psal := psal + 800;
 29          else
 30              update emp set sal = sal + 400 where empno = pempno;
 31              psal := psal + 400;
 32          end if;
 33
 34          -- 输出结果
 35          dbms_output.put_line(pempno || ', ' || pename || ', ' ||  pjob || ', ' || psal);
 36      end loop;
 37
 38      commit; -- 提交事务
 39
 40      -- 关闭游标
 41      close cemp;
 42  end;
 43  /
7369, SMITH, CLERK, 1200
7499, ALLEN, SALESMAN, 2000
7521, WARD, SALESMAN, 1650
7566, JONES, MANAGER, 3775
7654, MARTIN, SALESMAN, 1650
7698, BLAKE, MANAGER, 3650
7782, CLARK, MANAGER, 3250
7788, SCOTT, ANALYST, 4000
7839, KING, PRESIDENT, 5400
7844, TURNER, SALESMAN, 1900
7876, ADAMS, CLERK, 1500
7900, JAMES, CLERK, 1350
7902, FORD, ANALYST, 4000
7934, MILLER, CLERK, 1700

PL/SQL 过程已成功完成。

---

七、PLSQL 的例外：例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性；（例外也叫异常）

1、使用 oracle 系统内置异常，演示 除0 异常【zero_divide】

在没有写异常的情况下，oracle 服务器也会报出相应的错误；

SQL> declare
  2      myresult number(2);
  3  begin
  4      myresult := 1/0;
  5      dbms_output.put_line(myresult);
  6  end;
  7  /
declare
*
第 1 行出现错误:
ORA-01476: 除数为 0
ORA-06512: 在 line 4

也可以通过 oracle 的异常机制，自定义输出一些错误提示：

SQL> declare
  2      myresult number(2);
  3  begin
  4      myresult := 1/0;
  5      dbms_output.put_line(myresult);
  6  exception
  7      -- 如果异常为 zero_divide，可以自定义输出一句错误提示
  8      when zero_divide then
  9          dbms_output.put_line('除数不能为0');
 10  end;
 11  /
除数不能为0

PL/SQL 过程已成功完成。

2、使用 oracle 系统内置异常，查询 100 号部门的员工姓名，演示没有找到数据【no_data_found】:

SQL> declare
  2      pename varchar2(20);
  3  begin
  4      -- 查询 100 号部门的员工姓名，存入 pename 变量中
  5      select ename into pename from emp where deptno = 100;
  6      dbms_output.put_line(pename);
  7  exception
  8      -- 如果异常为 NO_DATA_FOUND，表示查不到数据
  9      when NO_DATA_FOUND then
 10          -- 自定义输出一句错误信息
 11          dbms_output.put_line('查无该部门员工');
 12          -- 也可以在异常中执行其他 DML 操作
 13          insert into emp(empno,ename) values(1111,'ERROR');
 14  end;
 15  /
查无该部门员工

PL/SQL 过程已成功完成。

3、使用用户自定义异常，使用光标cursor，查询 10/20/30/100 号部门的员工姓名，演示没有找到数据【nohave_emp_found】

查询 20 号部分员工姓名：

SQL> declare
  2      -- 声明一个游标，带一个参数（参数名 参数类型），游标指向 select 查询的结果集
  3      cursor cemp(pdeptno emp.deptno%type) is select ename from emp where deptno = pdeptno;
  4
  5      -- 声明一个异常，异常的名称为 nohave_emp_found
  6      nohave_emp_found exception;
  7
  8      -- 声明一个变量，类型和 emp 表中的 ename 类型一致
  9      pename emp.ename%type;
 10  begin
 11      -- 打开游标，这时游标位于第一条记录之前
 12      open cemp(&dno);
 13
 14      -- 向下移动一次游标，指向第一条记录；并将记录的值存入变量 pename 中；
 15      fetch cemp into pename;
 16
 17      -- 判断：如果游标指向的记录为空，就抛出异常
 18      if (cemp%notfound) then
 19          -- 抛出异常
 20          raise nohave_emp_found;
 21      else
 22          -- 输出变量 pename 的值
 23          dbms_output.put_line(pename);
 24
 25          -- 循环
 26          loop
 27              -- 向下移动一次游标，指向第二条记录
 28              fetch cemp into pename;
 29              -- 如果找不到记录的话，退出循环
 30              exit when cemp%notfound;
 31              -- 输出变量 pename 的值
 32              dbms_output.put_line(pename);
 33          end loop;
 34
 35      end if;
 36
 37      -- 关闭游标
 38      close cemp;
 39  exception
 40      -- 如果异常为 nohave_emp_found，输出 '查无此部门员工'；
 41      when nohave_emp_found then
 42          dbms_output.put_line('查无此部门员工');
 43  end;
 44  /
输入 dno 的值:  20
原值   12: open cemp(&dno);
新值   12: open cemp(20);
SMITH
JONES
SCOTT
ADAMS
FORD

PL/SQL 过程已成功完成。

查询 100 号部门员工姓名：

SQL> declare
  2      -- 声明一个游标，带一个参数（参数名 参数类型），游标指向 select 查询的结果集
  3      cursor cemp(pdeptno emp.deptno%type) is select ename from emp where deptno = pdeptno;
  4
  5      -- 声明一个异常，异常的名称为 nohave_emp_found
  6      nohave_emp_found exception;
  7
  8      -- 声明一个变量，类型和 emp 表中的 ename 类型一致
  9      pename emp.ename%type;
 10  begin
 11      -- 打开游标，这时游标位于第一条记录之前
 12      open cemp(&dno);
 13
 14      -- 向下移动一次游标，指向第一条记录；并将记录的值存入变量 pename 中；
 15      fetch cemp into pename;
 16
 17      -- 判断：如果游标指向的记录为空，就抛出异常
 18      if (cemp%notfound) then
 19          -- 抛出异常
 20          raise nohave_emp_found;
 21      else
 22          -- 输出变量 pename 的值
 23          dbms_output.put_line(pename);
 24
 25          -- 循环
 26          loop
 27              -- 向下移动一次游标，指向第二条记录
 28              fetch cemp into pename;
 29              -- 如果找不到记录的话，退出循环
 30              exit when cemp%notfound;
 31              -- 输出变量 pename 的值
 32              dbms_output.put_line(pename);
 33          end loop;
 34
 35      end if;
 36
 37      -- 关闭游标
 38      close cemp;
 39  exception
 40      -- 如果异常为 nohave_emp_found，输出 '查无此部门员工'；
 41      when nohave_emp_found then
 42          dbms_output.put_line('查无此部门员工');
 43  end;
 44  /
输入 dno 的值:  100
原值   12: open cemp(&dno);
新值   12: open cemp(100);
查无此部门员工

PL/SQL 过程已成功完成。