4. Oracle学习笔记-PL/SQL编程

PL/SQL编程

  graph LR
A[PL/SQL编程]-->B[PL/SQL简介]
A-->C[数据类型、变量和常量]
A-->D[流程控制语句]
A-->E[PL/SQL游标]
A-->F[PL/SQL异常处理]

1 PL/SQL简介

PL/SQL(Procedural Language/SQL)是 Oracle 在数据库中引入的一种过程化编程语言。PL/SQL构建于SQL之上，可以用来编写包含SQL语句的程序。

PL/SQL中可以通过IF语句或LOOP语句控制程序的执行流程，甚至可以定义变量，在语句之间传递数据信息，从而控制程序处理的细节过程。因此能将SQL语句的数据操纵能力、数据查询能力和PL/SQL的过程处理能力结合在一起，达到取长补短的目的。

1.1 特点

PL/SQL 与 SQL 紧密集成。
提供了广泛的错误检查。
提供了多种数据类型。
提供了多种编程结构。
支持通过函数和过程进行结构化编程。
支持面向对象的编程。
支持Web应用程序和服务器页面的开发。

1.2 优点

SQL 是标准的数据库语言，PL/SQL 与 SQL 紧密集成。 PL/SQL 支持静态和动态 SQL。静态 SQL 支持来自 PL/SQL 块的 DML 操作和事务控制。在动态 SQL 中，SQL 允许在 PL/SQL 块中嵌入 DDL 语句。
PL/SQL 允许一次将整个语句块发送到数据库。这可以减少网络流量并为应用程序提供高性能。
PL/SQL 可以提高程序员的工作效率，因为它可以查询、转换和更新数据库中的数据。
PL/SQL 凭借强大的功能（例如异常处理、封装、数据隐藏和面向对象的数据类型）节省了设计和调试时间。
用 PL/SQL 编写的应用程序是完全可移植的。
PL/SQL 提供高安全级别。

1.3 基本语法

1.3.1 PL/SQL 程序的基本划分

PL/SQL允许你在数据库中编写存储过程、函数和触发器等。一个完整的PL/SQL程序通常由以下三个部分组成：

声明部分（可选）：在这个部分可以声明变量、常量、类型和游标。
执行部分（必须）：这是PL/SQL块的主要部分，包含所有要执行的语句，如条件判断、循环、赋值操作等。
异常处理部分（可选）：用于捕获并处理可能出现的异常。

DECLARE
    -- 声明部分
    v_employee_id employees.employee_id%TYPE;
BEGIN
    -- 执行部分
    SELECT employee_id INTO v_employee_id FROM employees WHERE first_name = 'John';

    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
EXCEPTION
    -- 异常处理部分
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with the given name');
END;

1.3.2 标识符

在PL/SQL中，标识符是用于命名变量、常量、游标、过程、函数等的对象。它们必须遵循以下规则：

标识符名不能超过30字符；
第一个字符必须为字母；
不分大小写；
不能用’-‘(减号);
不能是SQL保留字。

1.3.3 分隔符

分隔符是在PL/SQL中有特殊含义的符号。这些符号用于分隔代码的不同部分或表示特定操作。常见的分隔符如下表.

分隔符	描述
*+, -, , /**	加法、减法/否定、乘法、除法
%	属性指示符
‘	字符串分隔符
.	组件选择器
(,)	表达式或列表分隔符
:	主变量指示符
,	项目分隔符
“	带引号的标识符分隔符
=	关系运算符
@	远程访问指示器
;	语句终止符
:=	赋值运算符
=>	关联运算符
**\	\	**	连接运算符
\	指数运算符
<<, >>	标签分隔符（开始和结束）
*/, /*	多行注释分隔符（开始和结束）
—	单行注释指示器
..	范围运算符
<, >, <=, >=	关系运算符
<>, ‘=, ~=, ^=	不等于的不同版本

1.4 PL/SQL的注释

在PL/SQL中，有两种注释方式：

单行注释：使用双破折号（--）开始，直到行尾都是注释内容。
1
-- This is a single-line comment
多行注释：使用/*开始，*/结束，中间的内容为注释。
1
2
3
4
/*
* This is a
* multi-line comment
*/

2 数据类型、变量和常量

2.1 数据类型

PL/SQL 变量、常量和参数必须具有有效的数据类型，它指定存储格式、约束和有效值范围。

PL/SQL 数据类型可以分为以下几类：

标量数据类型 (Scalar)：没有内部组件的单个值，例如 NUMBER、DATE 或 BOOLEAN。标量数据类型可以是预定义的，也可以是用户自定义的子类型。
大对象 (LOB) ( Large Object)：指向与其他数据项（例如文本、图形图像、视频剪辑和声音波形）分开存储的大型对象的指针。LOB 数据类型包括 BFILE、BLOB、CLOB 和 NCLOB。
复合数据类型 (Composite)：具有可单独访问的内部组件的数据项。复合数据类型包括记录和集合。
引用数据类型 (Reference)：指向其他数据项的指针。

2.1.1 标量数据类型

Numeric : 对其执行算术运算的数值。
Character : 表示单个字符或字符串的字母数字值。
Boolean : 对其执行逻辑操作的逻辑值。
Datetime : 日期和时间。

2.1.2 数值数据类型

PLS_INTEGER

-2147483648 到 2147483647 范围内的有符号整数，以 32 位表示
BINARY_INTEGER

-2147483648 到 214783647 范围内的有符号整数，以 32 位表示
BINARY_FLOAT

单精度 IEEE 754 格式浮点数
BINARY_DOUBLE

双精度 IEEE 754 格式浮点数
NUMBER(prec, scale)

绝对值在 1E-130 到（但不包括）1.0E126 范围内的定点或浮点数。一个 NUMBER 变量也可以表示 0
DEC(prec, scale)

ANSI 特定定点类型，最大精度为 38 位十进制数字
DECIMAL(prec, scale)

IBM 特定定点类型，最大精度为 38 位十进制数字
NUMERIC(pre, secale)

浮点型，最大精度为 38 位十进制数字

prec: 总位数, scale: 小数位数
DOUBLE PRECISION

ANSI 特定浮点类型，最大精度为 126 位二进制位（约 38 位十进制位）
FLOAT

ANSI 和 IBM 特定的浮点类型，最大精度为 126 位二进制位（大约 38 位十进制位）
INT

ANSI 特定整数类型，最大精度为 38 位十进制数字
INTEGER

ANSI 和 IBM 特定整数类型，最大精度为 38 位十进制数字
SMALLINT

ANSI 和 IBM 特定整数类型，最大精度为 38 位十进制数字
REAL

浮点型，最大精度为 63 位二进制（约 18 位十进制）

2.1.3 字符数据类型

CHAR(m)

最大长度为 m(m<32767) 字节的定长字符串, 默认值为1
VARCHAR2(m)

最大长度为 m(m<32767) 字节的可变长度字符串
RAW

最大大小为 32767 字节的可变长度二进制或字节字符串，不由 PL/SQL 解释
NCHAR

最大长度为 32767 字节的定长国家字符串
NVARCHAR2

最大长度为 32767 字节的可变长度国家字符串
LONG

最大长度为 32760 字节的可变长度字符串
LONG RAW

最大大小为 32760 字节的可变长度二进制或字节字符串，不由 PL/SQL 解释
ROWID

物理行标识，普通表中的行地址
UROWID

通用行标识符（物理、逻辑或外部行标识符）

2.1.4 布尔数据类型

布尔类型只有BOOLEAN，用于程序的流程控制和业务逻辑判断，其变量值可以是TRUE、FALSE和NULL中的一种。

2.1.5 日期时间和间隔类型

日期类型只有DATE类型。存储日期和时间。DATE使用一个字节存储世纪、年、月、天、小时、
分钟和秒

2.1.6 大对象(LOB) 数据类型

大型对象 (LOB) 数据类型是指大型数据项，例如文本、图形图像、视频剪辑和声音波形。 LOB 数据类型允许对这些数据进行高效、随机、分段的访问。以下是预定义的 PL/SQL LOB 数据类型.

数据类型	描述	大小
BFILE	用于将大型二进制对象存储在数据库外的操作系统文件中。	取决于系统。不能超过 4 GB。
BLOB	用于在数据库中存储大型二进制对象。	8 到 128 太字节 (TB)
CLOB	用于在数据库中存储大块字符数据。	8 到 128 TB
NCLOB	用于在数据库中存储大块 NCHAR 数据。	8 到 128 TB

2.1.7 用户定义的子类型

子类型是另一种数据类型的子集，称为其基类型。子类型具有与其基类型相同的有效操作，但只有其有效值的一个子集。

PL/SQL 在包STANDARD 中预定义了几个子类型。例如，PL/SQL 预定义子类型 CHARACTER 和 INTEGER 如下 −

1 2	SUBTYPE CHARACTER IS CHAR; SUBTYPE INTEGER IS NUMBER(38,0);

用户定义的子类型的定义和使用:

DECLARE 
   SUBTYPE name IS char(20); 
   SUBTYPE message IS varchar2(100); 
   salutation name; 
   greetings message; 
BEGIN 
   salutation := 'Reader '; 
   greetings := 'Welcome to the World of PL/SQL'; 
   dbms_output.put_line('Hello ' || salutation || greetings); 
END; 
/

2.2 常量

常量保存一个值，一旦声明，在程序中就不会改变。常量声明指定其名称、数据类型和值，并为其分配存储空间。该声明还可以施加 NOT NULL 约束。

常量的声明

使用 CONSTANT 关键字声明常量。它需要一个初始值，并且不允许更改该值。

DECLARE
  -- 声明一个常量
  PI CONSTANT NUMBER := 3.14159;

  -- 声明一个常量并施加 NOT NULL 约束
  MAX_VALUE CONSTANT INTEGER NOT NULL := 100;

  -- 声明一个常量字符串
  GREETING CONSTANT VARCHAR2(50) := 'Hello, World!';

  -- 声明一个日期常量
  CURRENT_DATE_CONSTANT CONSTANT DATE := SYSDATE;

BEGIN
  -- 使用常量
  DBMS_OUTPUT.PUT_LINE('圆周率是: ' || TO_CHAR(PI));
  DBMS_OUTPUT.PUT_LINE('最大值是: ' || TO_CHAR(MAX_VALUE));
  DBMS_OUTPUT.PUT_LINE('问候语: ' || GREETING);
  DBMS_OUTPUT.PUT_LINE('当前日期: ' || TO_CHAR(CURRENT_DATE_CONSTANT, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

在 SQL 提示符下执行上述代码时，结果如下:

圆周率是: 3.14159
最大值是: 100
问候语: Hello, World!
当前日期: 2023-12-14 02:25:04

PL/SQL procedure successfully completed.

2.3 变量

在PL/SQL编程中，变量是一种用于存储和操作数据的命名存储位置。变量可以包含各种数据类型，如数字、字符、日期等。本节将介绍变量的声明、初始化、作用域以及如何将SQL查询结果分配给PL/SQL变量。

2.3.1 变量声明

在PL/SQL中，变量用于存储数据值，提供了一种在程序执行过程中存储和操作数据的机制。使用 DECLARE 关键字来声明变量，语法如下：

DECLARE
  -- 声明变量
  variable_name datatype;
BEGIN
  -- 在此处使用变量
  -- ...
END;
/

示例代码：

DECLARE
  -- 声明整数变量
  emp_id NUMBER;

  -- 声明字符串变量
  emp_name VARCHAR2(50);

  -- 声明日期变量
  hire_date DATE;
BEGIN
  -- 在此处使用变量
  emp_id := 101;
  emp_name := 'John Doe';
  hire_date := SYSDATE;

  -- 打印变量值
  DBMS_OUTPUT.PUT_LINE('雇员ID: ' || TO_CHAR(emp_id));
  DBMS_OUTPUT.PUT_LINE('雇员姓名: ' || emp_name);
  DBMS_OUTPUT.PUT_LINE('雇佣日期: ' || TO_CHAR(hire_date, 'YYYY-MM-DD'));
END;
/

2.3.2 初始化变量

在声明变量时，可以选择为其赋予初始值。这可以通过使用 := 操作符来完成。示例代码：

DECLARE
  -- 初始化整数变量
  num_of_employees NUMBER := 10;

  -- 初始化字符串变量
  company_name VARCHAR2(100) := 'ABC Corporation';

  -- 初始化日期变量
  start_date DATE := TO_DATE('2023-01-01', 'YYYY-MM-DD');
BEGIN
  -- 在此处使用带有初始值的变量
  -- ...
END;
/

2.3.3 变量作用域

PL/SQL中的变量作用域分为局部变量和全局变量。局部变量只在声明它们的块中可见，而全局变量在整个程序中可见。示例代码：

DECLARE
  -- 全局变量
  global_var NUMBER := 100;

  PROCEDURE my_procedure IS
    -- 局部变量
    local_var NUMBER := 50;
  BEGIN
    -- 可以访问全局变量
    DBMS_OUTPUT.PUT_LINE('全局变量值: ' || TO_CHAR(global_var));

    -- 可以访问局部变量
    DBMS_OUTPUT.PUT_LINE('局部变量值: ' || TO_CHAR(local_var));
  END my_procedure;
BEGIN
  -- 在此处访问全局变量
  DBMS_OUTPUT.PUT_LINE('全局变量值（在 BEGIN 块中）: ' || TO_CHAR(global_var));

  -- 调用过程
  my_procedure;
END;
/

2.3.4 将 SQL 查询结果分配给 PL/SQL 变量

可以使用 SELECT INTO 语句将 SQL 查询的结果分配给 PL/SQL 变量。示例代码：

DECLARE
  -- 声明变量
  employee_name VARCHAR2(50);

BEGIN
  -- 使用 SELECT INTO 将查询结果赋值给变量
  SELECT emp_name INTO employee_name
  FROM employees
  WHERE emp_id = 101;

  -- 打印变量值
  DBMS_OUTPUT.PUT_LINE('雇员姓名: ' || employee_name);
END;
/

在上述示例中，SELECT emp_name INTO employee_name 语句从名为 “employees” 的表中选择雇员ID为101的雇员姓名，并将结果赋给变量 employee_name。

2.4 特殊的数据类型

2.4.1 `%TYPE` 类型

使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。

DECLARE
  var_name  student.sname%TYPE;
  var_sdept student.sdept%TYPE;
BEGIN
  SELECT
    sname,
    sdept INTO var_name,
    var_dept
  FROM
    student
  WHERE sno = '201215123';
  DBMS_OUTPUT.PUTLINE('The result is:' || var_name || ', sdept:' || var_sdept);
END;
/

2.4.2 `RECORD` 类型

记录类型就好像是一个封装了多个属性的类，也就是一个变量里包含其他变量的结构体。在记录类型的定义结构中包含成员变量及其数据类型：

DECLARE
  TYPE stu_type IS RECORD(
    var_name student.sname%TYPE,
    var_sdept student.sdept%TYPE
  );
  stuinfo stu_type;
BEGIN
  SELECT sname, sdept INTO stuinfo.var_name, stuinfo.var_sdept FROM student WHERE sno = '201215123';
  DBMS_OUTPUT.PUT_LINE('The result is:' || stuinfo.var_name || ', sdept:' || stuinfo.var_sdept);
END;
/

2.4.3 `%ROWTYPE` 类型

%ROWTYPE类型的变量结合了%TYPE类型和记录类型变量的优点，根据数据表中行的结构定义一
种特殊的数据类型，来存储从数据表中检索到的一行数据，语法形式如下：
rowVar_name table_name%rowtype;

DECLARE
  rowvar_stu student%ROWTYPE;
BEGIN
  SELECT * INTO rowvar_stu FROM student WHERE sno = '201215123';
  DBMS_OUTPUT.PUT_LINE('The result is:' || rowvar_stu.sname || ', sage:' || rowvar_stu.sage);
END;

2.4.4 字符串

PL/SQL 中的字符串实际上是一个带有可选大小规范的字符序列。字符可以是数字、字母、空白、特殊字符或所有字符的组合。 PL/SQL 提供三种字符串:

固定长度字符串 − 在这样的字符串中，程序员在声明字符串时指定长度。字符串右填充空格至指定的长度。
可变长度字符串 − 在此类字符串中，指定字符串的最大长度为 32,767，并且不进行填充。
字符大对象 (CLOB) − 这些是可变长度的字符串，最大可达 128 TB。

PL/SQL 字符串可以是变量或文字。字符串文字用引号括起来。例如，

1	'This is a string literal.' Or 'hello world'

要在字符串文字中包含单引号，您需要键入两个并排的单引号。例如，

1	'this isn''t what it looks like'

2.4.4.1 声明字符串变量

Oracle 数据库提供了多种字符串数据类型，例如 CHAR、NCHAR、VARCHAR2、NVARCHAR2、CLOB 和 NCLOB。以 ‘N’ 为前缀的数据类型是 ‘国家字符集’ 数据类型，用于存储 Unicode 字符数据。

如果需要声明可变长度字符串，则必须提供该字符串的最大长度。例如，VARCHAR2 数据类型。

-- 示例：声明字符串变量
DECLARE
  -- CHAR 类型，固定长度字符串
  v_char_string CHAR(10) := 'Oracle';

  -- VARCHAR2 类型，可变长度字符串
  v_varchar_string VARCHAR2(20) := 'Database';

  -- NCHAR 类型，固定长度 Unicode 字符串
  v_nchar_string NCHAR(10) := N'中文';

  -- NVARCHAR2 类型，可变长度 Unicode 字符串
  v_nvarchar_string NVARCHAR2(20) := N'数据库';

BEGIN
  -- 输出字符串变量的值
  DBMS_OUTPUT.PUT_LINE('CHAR 类型：' || v_char_string);
  DBMS_OUTPUT.PUT_LINE('VARCHAR2 类型：' || v_varchar_string);
  DBMS_OUTPUT.PUT_LINE('NCHAR 类型：' || v_nchar_string);
  DBMS_OUTPUT.PUT_LINE('NVARCHAR2 类型：' || v_nvarchar_string);
END;
/

2.4.4.2 字符串函数

PL/SQL 提供连接运算符 (||) 用于连接两个字符串。以下是常见的字符串函数:

函数	描述
CONCAT(x, y);	连接字符串 x 和 y 并返回附加的字符串。例：`CONCAT('Hello', ' World')`
LENGTH(x);	返回字符串的长度。例：`LENGTH('Hello')`
SUBSTR(x, start [, length]);	返回从 start 指定的位置开始的 x 子字符串。可以提供子字符串的可选长度。例：`SUBSTR('Hello', 2, 3)`
INSTR(x, find_string [, start] [, occurrence]);	在 x 中搜索 find_string 并返回它出现的位置。例：`INSTR('Hello', 'lo')`
UPPER(x);	将字符串转换为大写。例：`UPPER('hello')`
LOWER(x)	将字符串转换为小写。例：`LOWER('Hello')`
TRIM([trim_char FROM) x);	去除字符串x两侧的空格或指定字符。例：`TRIM(' Hello ')`
LTRIM(x [, trim_string]);	去除字符串左侧的空格或指定字符。例：`LTRIM(' Hello ')`
RTRIM	去除字符串右侧的空格或指定字符。例：`RTRIM(' Hello ')`
REPLACE	替换字符串中的指定子串。例：`REPLACE('Hello', 'l', 'p')`
INITCAP	将字符串中的每个单词的首字母大写。例：`INITCAP('hello world')`
LPAD	在字符串左侧填充指定字符。例：`LPAD('123', 5, '0')`
RPAD	在字符串右侧填充指定字符。例：`RPAD('123', 5, '0')`
LTRIM	去除字符串左侧的空格或指定字符。例：`LTRIM(' Hello ')`
RTRIM	去除字符串右侧的空格或指定字符。例：`RTRIM(' Hello ')`
SOUNDEX	返回字符串的语音表示形式。例：`SOUNDEX('Hello')`
ASCII	返回字符串的第一个字符的 ASCII 值。例：`ASCII('A')`
CHR	返回具有指定 ASCII 值的字符。例：`CHR(65)`
INSTR(x, find_string [, start] [, occurrence])	返回字符串中指定字节的位置。例：`INSTRB('Hello', 'lo', 1, 1)`
SUBSTRB	返回字符串的字节子串。例：`SUBSTRB('Hello', 2, 3)`
CONVERT	将字符串从一种字符集转换为另一种字符集。例：`CONVERT('Hello', 'UTF8', 'AL32UTF8')`
NLS_INITCAP	返回字符串的每个单词的首字母大写，根据指定的 NLS 参数。例：`NLS_INITCAP('hello world', 'NLS_SORT = BINARY')`
NLS_LOWER	将字符串转换为小写，根据指定的 NLS 参数。例：`NLS_LOWER('Hello', 'NLS_SORT = BINARY')`
NLS_UPPER	将字符串转换为大写，根据指定的 NLS 参数。例：`NLS_UPPER('hello', 'NLS_SORT = BINARY')`
NLS_SORT	返回字符串的排序值，根据指定的 NLS 参数。例：`NLS_SORT('Hello', 'NLS_SORT = BINARY')`
REGEXP_INSTR	返回字符串中与正则表达式模式匹配的位置。例：`REGEXP_INSTR('Hello World', 'Worl.d')`
REGEXP_SUBSTR(source_string, pattern [, start_position [, match_occurrence [, match_return_option [, match_parameter ]]]])	返回字符串中与正则表达式模式匹配的子串。例：`REGEXP_SUBSTR('Hello World', 'Worl.d')`
REGEXP_LIKE (source_string, pattern [, match_parameter ])	判断字符串是否与正则表达式模式匹配。例：`REGEXP_LIKE('Hello World', 'Worl.d')`

3 流程控制语句

3.1 条件语句

3.1.1 IF - THEN 语句

IF - THEN 语句用于根据条件执行一个代码块。

DECLARE
  score NUMBER := 75;
BEGIN
  -- 判断分数是否合格
  IF score >= 60 THEN
    DBMS_OUTPUT.PUT_LINE('考试合格!');
  END IF;
END;
/

3.1.2 IF-THEN-ELSE 语句

IF-THEN-ELSE 语句允许在条件为真时执行一个代码块，否则执行另一个。

DECLARE
  temperature NUMBER := 28;
BEGIN
  -- 判断温度是否适宜
  IF temperature > 30 THEN
    DBMS_OUTPUT.PUT_LINE('太热了!');
  ELSE
    DBMS_OUTPUT.PUT_LINE('天气不错。');
  END IF;
END;
/

3.1.3 IF-THEN-ELSIF 语句

IF-THEN-ELSIF 语句可以用于检查多个条件，根据不同条件执行相应的代码块。

DECLARE
  num NUMBER := 0;
BEGIN
  -- 判断数值的正负
  IF num > 0 THEN
    DBMS_OUTPUT.PUT_LINE('正数');
  ELSIF num < 0 THEN
    DBMS_OUTPUT.PUT_LINE('负数');
  ELSE
    DBMS_OUTPUT.PUT_LINE('零');
  END IF;
END;
/

3.1.4 CASE 语句

CASE 语句用于基于表达式的值选择不同的执行路径。

DECLARE
  day_of_week NUMBER := 3;
  day_name VARCHAR2(20);
BEGIN
  -- 根据星期几设置对应的名称
  CASE day_of_week
    WHEN 1 THEN day_name := '星期一';
    WHEN 2 THEN day_name := '星期二';
    WHEN 3 THEN day_name := '星期三';
    WHEN 4 THEN day_name := '星期四';
    WHEN 5 THEN day_name := '星期五';
    ELSE day_name := '周末';
  END CASE;

  DBMS_OUTPUT.PUT_LINE('今天是：' || day_name);
END;
/

CASE 语句还可以用于在查询中进行条件检索。

DECLARE
  department_id NUMBER := 2;
  department_name VARCHAR2(50);

BEGIN
  -- 查询部门名称
  SELECT
    CASE 
      WHEN department_id = 1 THEN '人事部'
      WHEN department_id = 2 THEN '财务部'
      WHEN department_id = 3 THEN '销售部'
      ELSE '未知部门'
    END
  INTO department_name
  FROM departments
  WHERE department_id = department_id;

  DBMS_OUTPUT.PUT_LINE('部门名称：' || department_name);
END;
/

3.1.5 嵌套的 IF-THEN-ELSE

IF-THEN-ELSE 语句可以嵌套，用于处理更复杂的条件逻辑。

DECLARE
  num1 NUMBER := 10;
  num2 NUMBER := 5;
  result VARCHAR2(50);

BEGIN
  -- 嵌套的 IF-THEN-ELSE 语句
  IF num1 > num2 THEN
    IF num1 - num2 = 5 THEN
      result := '满足条件 A';
    ELSE
      result := '满足条件 B';
    END IF;
  ELSE
    result := '未满足条件';
  END IF;

  DBMS_OUTPUT.PUT_LINE('结果：' || result);
END;
/

3.2 循环语句

3.2.1 PL/SQL 基本循环

LOOP 语句用于创建基本循环。

DECLARE
  counter NUMBER := 1;

BEGIN
  -- 使用基本循环输出数字
  LOOP
    EXIT WHEN counter > 5;
    DBMS_OUTPUT.PUT_LINE('计数器：' || counter);
    counter := counter + 1;
  END LOOP;
END;
/

3.2.2 PL/SQL WHILE 循环

WHILE 循环在条件为真时执行代码块。

DECLARE
  counter NUMBER := 1;

BEGIN
  -- 使用 WHILE 循环输出数字
  WHILE counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('计数器：' || counter);
    counter := counter + 1;
  END LOOP;
END;
/

3.2.3 PL/SQL FOR 循环

FOR 循环用于迭代一个数值范围。

DECLARE
  total NUMBER := 0;

BEGIN
  -- 使用 FOR 循环计算总和
  FOR i IN 1..5 LOOP
    total := total + i;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('1到5的总和为：' || total);
END;
/

3.2.4 PL/SQL 中的嵌套循环

在PL/SQL中，可以嵌套循环以处理更复杂的场景。

DECLARE
  rows_count NUMBER := 3;
  cols_count NUMBER := 4;

BEGIN
  -- 嵌套循环用于输出矩阵
  FOR i IN 1..rows_count LOOP
    FOR j IN 1..cols_count LOOP
      DBMS_OUTPUT.PUT(i || '-' || j || ' ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
  END LOOP;
END;
/

3.3 跳转语句 GOTO

GOTO 语句用于无条件地将控制转移到代码中的标签位置。

DECLARE
  counter NUMBER := 1;

BEGIN
  -- 使用 GOTO 语句跳转到指定标签
  <<loop_start>>
  LOOP
    EXIT WHEN counter > 5;
    DBMS_OUTPUT.PUT_LINE('计数器：' || counter);
    counter := counter + 1;
    
    -- 跳转到指定标签
    GOTO loop_start;
  END LOOP;
END;
/

4 游标

Oracle 创建一个内存区域，称为上下文区域，用于处理 SQL 语句，其中包含处理语句所需的所有信息；例如，处理的行数等。

cursor 是指向该上下文区域的指针。 PL/SQL 通过游标控制上下文区域。游标保存 SQL 语句返回的行（一个或多个）。游标保存的行集称为活动集(active set)。有两种类型的游标, 分别为隐式游标和显式游标 .

4.1 隐式游标

隐式游标是Oracle在执行没有定义显式游标的SQL语句时自动创建的。程序员不能控制隐式游标和其中的信息。

每当执行DML语句（INSERT，UPDATE和DELETE）时，就会与该语句关联一个隐式游标。对于INSERT操作，游标保存了需要插入的数据。对于UPDATE和DELETE操作，游标标识了将受影响的行。

在PL/SQL中，你可以使用SQL游标来引用最近的隐式游标，它总是具有一些属性，如%FOUND，%ISOPEN，%NOTFOUND和%ROWCOUNT 。SQL游标还有额外的属性: %BULK_ROWCOUNT和%BULK_EXCEPTIONS，它们是为了与FORALL语句一起使用而设计的。

属性	描述
%FOUND	如果 INSERT、UPDATE 或 DELETE 语句影响了一行或多行，或者 SELECT INTO 语句返回了一行或多行，则返回 TRUE。否则，它返回 FALSE。
%NOTFOUND	%FOUND 的逻辑相反。如果 INSERT、UPDATE 或 DELETE 语句不影响任何行，或者 SELECT INTO 语句不返回任何行，则返回 TRUE。否则，它返回 FALSE。
%ISOPEN	对于隐式游标，总是返回 FALSE，因为 Oracle 在执行关联的 SQL 语句后会自动关闭 SQL 游标。
%ROWCOUNT	返回受 INSERT、UPDATE 或 DELETE 语句影响或由 SELECT INTO 语句返回的行数。

在使用隐式游标的属性时，需要在前面加上隐式游标的默认名称SQL

begin
	update student set sage=sage+1 where sdept='CS';
	if sql%notfound then
		dbms_output.put_line('not fount any student');
	else
		dbms_output.put_line(sql%rowcount||' students has changed');
	end if;
end;

4.2 显式游标

显式游标是由用户声明和操作的一种游标，通常用于操作查询select语句返回的结果集。应在 PL/SQL 块的声明部分中定义显式游标。它是在返回多行的 SELECT 语句上创建的。

使用游标的步骤如下图:

  flowchart LR
声明游标-->B[打开游标]
B --> C[读取游标]
C --> A{数据为空}
A --> |是|关闭游标
A --> |否|C

4.2.1 声明显式游标

声明游标定义游标名称和相关的 SELECT 语句。

1	CURSOR cursor_name IS select_statement;

4.2.2 打开游标

打开游标会为游标分配内存，并准备好将 SQL 语句返回的行提取到其中。

1	OPEN cursor_name;

4.2.3 获取游标

一次访问一行数据。

1	FETCH cur_stu INTO stu_row;

4.2.4 关闭游标

关闭游标, 释放分配的内存。

1	CLOSE cursor_name;

4.2.5 显式游标案例

DECLARE
  CURSOR cur_stu IS 
    SELECT sno, sname, sage FROM student WHERE sdept = 'CS';
  TYPE record_stu IS RECORD (
    var_sno student.sno%TYPE,
    var_sname student.sname%TYPE,
    var_sage student.sage%TYPE
  );
  stu_row record_stu;
BEGIN
  OPEN cur_stu;
  FETCH cur_stu INTO stu_row;
  WHILE cur_stu%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(stu_row.var_sname || ' id:' || stu_row.var_sno || ' age: ' || stu_row.var_sage);
    FETCH cur_stu INTO stu_row;
  END LOOP;
  CLOSE cur_stu;
END;
/

5 异常

5.1 异常处理语法

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

示例:

DECLARE
  a INT;
  b INT;
  c NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('please input a value for a:');
  a := &a;
  DBMS_OUTPUT.PUT_LINE('please input a value for b:');
  b := &b;
  c := (a + b) / (a - b);
  DBMS_OUTPUT.PUT_LINE(c);
EXCEPTION
  WHEN zero_divide THEN
    DBMS_OUTPUT.PUT_LINE('Divisor can not be zero');
END;

5.2 引发异常

每当出现任何内部数据库错误时，数据库服务器都会自动引发异常，但程序员可以使用命令 RAISE 显式引发异常。以下是引发异常的简单语法 −

DECLARE 
   exception_name EXCEPTION; 
BEGIN 
   IF condition THEN 
      RAISE exception_name; 
   END IF; 
EXCEPTION 
   WHEN exception_name THEN 
   statement; 
END;

您可以使用上述语法引发 Oracle 标准异常或任何用户定义的异常。

5.3 用户定义的异常

PL/SQL 允许您根据程序的需要定义自己的异常。必须声明用户定义的异常，然后使用 RAISE 语句或过程 DBMS_STANDARD.RAISE_APPLICATION_ERROR 显式引发。

1 2	DECLARE my-exception EXCEPTION;

示例:

DECLARE
  v_data NUMBER;
  v_myexp EXCEPTION;
BEGIN
  v_data := &v_data;
  IF v_data > 100 THEN
    RAISE v_myexp;
  END IF;
EXCEPTION
  WHEN v_myexp THEN
    DBMS_OUTPUT.PUT_LINE('MY EXCEPTION');
END;

5.4 预定义的异常

异常	Oracle 错误	SQLCODE	描述
ACCESS_INTO_NULL	06530	-6530	当一个空对象被自动赋值时引发。
CASE_NOT_FOUND	06592	-6592	当没有选择 CASE 语句的 WHEN 子句中的任何选项，并且没有 ELSE 子句时引发。
COLLECTION_IS_NULL	06531	-6531	当程序尝试将 EXISTS 以外的集合方法应用于未初始化的嵌套表或 varray，或者程序尝试将值分配给未初始化的嵌套表或 varray 的元素时引发。
DUP_VAL_ON_INDEX	00001	-1	当尝试将重复值存储在具有唯一索引的列中时会引发此问题。
INVALID_CURSOR	01001	-1001	当试图进行不允许的游标操作时引发，例如关闭未打开的游标。
INVALID_NUMBER	01722	-1722	当字符串转换为数字失败时引发，因为字符串不代表有效数字。
LOGIN_DENIED	01017	-1017	当程序尝试使用无效的用户名或密码登录数据库时引发。
NO_DATA_FOUND	01403	+100	当 SELECT INTO 语句没有返回行时引发。
NOT_LOGGED_ON	01012	-1012	在未连接到数据库的情况下发出数据库调用时引发。
PROGRAM_ERROR	06501	-6501	当 PL/SQL 出现内部问题时引发。
ROWTYPE_MISMATCH	06504	-6504	当游标在具有不兼容数据类型的变量中获取值时引发。
SELF_IS_NULL	30625	-30625	在调用成员方法时引发，但对象类型的实例未初始化。
STORAGE_ERROR	06500	-6500	当 PL/SQL 内存不足或内存损坏时引发。
TOO_MANY_ROWS	01422	-1422	当 SELECT INTO 语句返回多行时引发。
VALUE_ERROR	06502	-6502	当发生算术、转换、截断或大小约束错误时引发。
ZERO_DIVIDE	01476	1476	当试图将一个数字除以零时引发。