[TOC]
PL/SQL概述
PL/SQL（Procedure Language/SQL）是 Oracle对 SQL 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。
- PL/SQL是一种完全可移植的高性能事务处理语言
- PL/SQL提供了内置的，解释的且与OS无关的编程环境
- 也可以从命令行SQL * Plus接口直接调用PL/SQL
- 也可以从外部编程语言调用直接调用数据库
- PL/SQL的通用语法基于ADA和Pascal编程语言
- 除了Oracle，PL/SQL还可以在TimesTen内存数据库和IBM DB2中使用
一、PL/SQL 程序结构
DECLARE
-- 声明部分 (可选)
-- 变量、常量、游标、异常等的声明
BEGIN
-- 执行部分 (必需)
-- PL/SQL语句和SQL语句
EXCEPTION
-- 异常处理部分 (可选)
-- 异常处理代码
END;案例：
declare
age number default 10; -- 定义一个age变量,默认值为10;
begin
age := 11; -- 将age变量的值变为11;
DBMS_OUTPUT.put_line('年龄:' || age); -- 打印括号里的值
end;二. 变量声明
变量是在程序中可以操纵的存储区域的名称。 PL/SQL中的每个变量都有一个指定的数据类型，它决定了变量内存的大小和布局。
PL/SQL变量的名称由可选的字母，数字，美元($)符号，下划线和数字符号组成，不能超过30个字符。 默认情况下，变量名不区分大小写。不能将保留的PL/SQL关键字用作变量名称。
基本语法格式:
-- 声明变量
变量名 类型(长度);
-- 变量赋值
变量名 := 变量值;
-- select into 变量赋值
select 列名 into 变量名 from 表名 where 条件;
select 列名1,列名2 into 变量1,变量2 from 表名 where 条件
-- 列名要和变量名一一对应2.1 变量数据类型
PL/SQL支持多种数据类型，包括：
- 标量类型：NUMBER，VARCHAR2，CHAR，DATE，BOOLEAN等
- 复合类型：RECORD，TABLE
- 引用类型：REF CURSOR
- LOB类型：BLOB，CLOB，BFILE
2.2 记录型变量
列变量：记录表中的一列存放在变量中
基本语法格式:
-- 定义列变量
列变量名 表名.列名%type;
-- 定义变量v_num0，其类型与T_ACCOUNT表的NUM0字段相同
v_num0 T_ACCOUNT.NUM0%type;行变量：记录表中的一行存放在变量中
基本语法格式:
-- 定义行变量
行变量名 表名%rowtype;
-- 引用行变量
行变量名.列名;三、控制结构
3.1 条件语句
IF-THEN-ELSE
- PL/SQL中使用elsif而不是elseif
if condition then
statements;
-- 条件成立时执行的语句
elsif condition then
statements; -- 可选的额外条件判断
else
statements; -- 所有条件都不成立时执行
end if; -- 必须以END IF结束3.2 循环语句
基本循环
- 基本循环 - 需要显式指定退出条件
LOOP
statements; -- 循环体语句
EXIT WHEN condition; -- 满足条件时退出循环
END LOOP; -- 循环结束标记WHILE循环
- WHILE循环 - 先判断条件再执行
while condition loop -- 当条件为真时执行循环
statements;
end loop;FOR循环
for counter in start..end loop -- counter从start到end自动递增
statements;
-- 可以使用counter变量
END LOOP;四、游标
游标是系统为用户开设的一个数据缓冲区，存放 SQL 语句的执行结果。
游标（Cursor）是一种用于查询结果集的指针，它允许你逐行处理查询结果。
显式游标
declare
-- 声明游标，定义查询语句
cursor emp_cursor is selsct * from employees;
-- 定义记录变量，用于存储游标返回的行数据
v_emp employees%ROWTYPE;
BEGIN
OPEN emp_cursor; -- 打开游标
LOOP
FETCH emp_cursor INTO v_emp;
-- 获取一行数据存入变量
exit when emp_cursor%NOTFOUND;
-- 当没有更多数据时退出循环
-- 在这里处理获取到的数据(v_emp)
END LOOP;
CLOSE emp_cursor; -- 必须显式关闭游标
END;隐式游标
隐式调用游标，实现自动管理，可以简化代码
BEGIN
-- 直接在FOR循环中使用SELECT语句
for emp_rec in (select * from employees) LOOP
-- 可以直接使用emp_rec访问当前行数据
-- 不需要显式打开/关闭游标
END LOOP;
END;五、异常处理
异常是指PL/SQL程序在执行时出现的错误，在PL/SQL中的一个警告或错误的情形都被称为异常
异常处理通常写在执行体的最下面，在所有执行语句之后以exception关键字开始异常处理
在运行程序时出现的错误叫做异常，发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
异常有两种类型：
- 预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
- 用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发
以下是Oracle数据库中常见的21个预定义异常及其说明的整理表格：
| 异常名称 | 错误代码 | 触发场景 |
|---|---|---|
NO_DATA_FOUND |
ORA-01403 | SELECT INTO 语句未找到任何数据时触发。 |
TOO_MANY_ROWS |
ORA-01422 | SELECT INTO 语句返回多行数据时触发。 |
DUP_VAL_ON_INDEX |
ORA-00001 | 尝试向唯一索引列插入重复值时触发（违反唯一性约束）。 |
INVALID_CURSOR |
ORA-01001 | 尝试操作未打开或已关闭的游标（如关闭未打开的游标）。 |
ZERO_DIVIDE |
ORA-01476 | 除数为零时触发（如 5/0）。 |
INVALID_NUMBER |
ORA-01722 | 字符串转换为数字失败时触发（如 TO_NUMBER('ABC')）。 |
VALUE_ERROR |
ORA-06502 | 变量赋值时数据类型或长度不匹配（如字符串超长）。 |
STORAGE_ERROR |
ORA-06500 | PL/SQL 运行时内存不足或栈溢出时触发。 |
PROGRAM_ERROR |
ORA-06501 | PL/SQL 内部错误（如程序逻辑问题）。 |
TIMEOUT_ON_RESOURCE |
ORA-00051 | 等待数据库资源超时（如锁等待）。 |
CURSOR_ALREADY_OPEN |
ORA-06511 | 尝试打开已打开的游标时触发。 |
LOGIN_DENIED |
ORA-01017 | 使用无效用户名或密码登录数据库时触发。 |
NOT_LOGGED_ON |
ORA-01012 | 尝试执行数据库操作但未建立有效连接时触发。 |
ACCESS_INTO_NULL |
ORA-06530 | 尝试为未初始化的对象属性赋值时触发。 |
CASE_NOT_FOUND |
ORA-06592 | CASE 语句无匹配条件且无 ELSE 子句时触发。 |
COLLECTION_IS_NULL |
ORA-06531 | 访问未初始化的集合（如嵌套表或数组）时触发。 |
SUBSCRIPT_BEYOND_COUNT |
ORA-06533 | 访问集合元素时下标超过集合实际元素数量。 |
SUBSCRIPT_OUTSIDE_LIMIT |
ORA-06532 | 使用非法下标（如负数或零）访问集合元素。 |
ROWTYPE_MISMATCH |
ORA-06504 | 游标返回的行类型与接收变量类型不匹配时触发。 |
SELF_IS_NULL |
ORA-30625 | 调用未初始化的对象类型的方法时触发。 |
SYS_INVALID_ROWID |
ORA-01410 | 转换无效字符串为 ROWID 时触发（如 ROWID 格式错误）。 |
异常基本语法结构:
EXCEPTION
when 异常类型 then -- 异常处理逻辑用户自定义异常语法结构:
declare
自定义异常名 EXCEPTION;
PRAGMA EXCEPTION_INIT (自定义异常名,错误码(-01403));
begin
exception
when 自定义异常名 then
subquery;
end;案例：
BEGIN
-- 可能抛出异常的代码块
-- 例如：SELECT INTO可能抛出NO_DATA_FOUND异常
EXCEPTION -- 异常处理部分
WHEN NO_DATA_FOUND THEN -- 处理特定异常
DBMS_OUTPUT.PUT_LINE('没有找到数据');
WHEN OTHERS THEN -- 处理所有其他未捕获的异常
DBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE); -- 系统错误代码
DBMS_OUTPUT.PUT_LINE('错误消息: ' || SQLERRM); -- 错误消息文本
END;六、存储过程和函数
6.1 存储过程
存储过程：是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中
主要特点：
- 不直接返回值，但可以通过OUT参数返回数据
- 可以作为独立的PL/SQL块调用
- 可以包含事务控制语句(COMMIT/ROLLBACK)
- 主要用于执行操作而非计算
存储过程创建语法：
CREATE [OR REPLACE] PROCEDURE procedure_name
-- OR REPLACE表示存在则替换
[(parameter1 [IN | OUT | IN OUT] datatype [, ...])] -- 参数定义部分
{IS | AS} -- IS或AS关键字开始声明部分
[声明部分] -- 定义局部变量、游标等
BEGIN
-- 执行部分(核心业务逻辑)
EXCEPTION
-- 异常处理部分
END [procedure_name];
-- 可选的结束标签| 模式 | 描述 | 示例 |
|---|---|---|
| IN | 默认模式，输入参数 | p_emp_id IN NUMBER |
| OUT | 输出参数，用于返回值 | p_result OUT VARCHAR2 |
| IN OUT | 既是输入也是输出参数 | p_counter IN OUT NUMBER |
案例：
CREATE OR REPLACE PROCEDURE update_salary(
p_emp_id IN NUMBER, -- 输入参数：员工ID
p_amount IN NUMBER -- 输入参数：薪资调整金额
) AS
BEGIN
-- 更新指定员工的薪资
UPDATE employees
SET salary = salary + p_amount
WHERE employee_id = p_emp_id;
COMMIT; -- 提交事务
EXCEPTION
WHEN OTHERS THEN -- 发生异常时回滚
ROLLBACK;
RAISE; -- 重新抛出异常
END update_salary;6.2 函数
主要特点：
- 必须返回一个值(通过RETURN语句)
- 不能包含事务控制语句(COMMIT/ROLLBACK)
- 主要用于计算和返回值
- 可以作为SQL表达式的一部分调用
PLSQL函数创建语法
CREATE [OR REPLACE] FUNCTION function_name -- OR REPLACE表示存在则替换
[(parameter1 [IN | OUT | IN OUT] datatype [, ...])] -- 参数定义部分
RETURN return_datatype -- 必须指定返回类型
{IS | AS} -- IS或AS关键字开始声明部分
[声明部分] -- 定义局部变量、游标等
BEGIN
-- 执行部分
RETURN expression; -- 必须包含RETURN语句
EXCEPTION
-- 异常处理部分
END [function_name];案例：
CREATE OR REPLACE FUNCTION get_employee_name(
p_emp_id IN NUMBER -- 输入参数：员工ID
) RETURN VARCHAR2 AS -- 指定返回值类型
v_name VARCHAR2(100); -- 局部变量
BEGIN
-- 查询并拼接员工姓名
SELECT first_name || ' ' || last_name
INTO v_name
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_name; -- 必须包含RETURN语句
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '员工不存在'; -- 异常时返回默认值
END get_employee_name;6.3 存储过程与函数对比
| 特性 | 存储过程 | 函数 |
|---|---|---|
| 返回值 | 通过OUT参数返回 | 必须通过RETURN返回 |
| 调用方式 | 独立调用 | 可作为表达式一部分 |
| 事务控制 | 可以包含COMMIT/ROLLBACK | 不能包含 |
| 主要用途 | 执行数据库操作 | 计算并返回值 |
| DML语句 | 可以包含 | 可以包含(在特定情况下) |
| 在SQL中调用 | 不能直接在SELECT中使用 | 可以直接在SELECT中使用 |
七、包 [Package]
PL/SQL包是Oracle数据库中的一个重要特性，它是一组相关的过程、函数、变量、常量和游标等程序对象的集合，被组织在一个逻辑单元中
PL/SQL包由两部分组成：
- 包规范 [Package Specification]
- 定义包的公共接口
- 声明可以被外部程序访问的类型、变量、常量、异常、游标、过程和函数
- 不包含任何实现代码
- 包体 [Package Body]
- 包含包规范中声明的所有过程和函数的实现代码
- 可以包含额外的私有变量、过程和函数（这些在包规范中未声明，只能在包内部使用）
包规范
-- 包规范 - 定义公共接口
CREATE OR REPLACE PACKAGE emp_pkg AS
-- 公共类型和常量声明
-- 公共过程声明
PROCEDURE update_salary(
p_emp_id IN NUMBER,
p_amount IN NUMBER
);
-- 公共函数声明
FUNCTION get_employee_name(
p_emp_id IN NUMBER
) RETURN VARCHAR2;
END emp_pkg;包体
-- 包体 - 实现包规范中声明的内容
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
-- 私有变量和类型声明 - 只能在包内访问
-- 实现update_salary过程
PROCEDURE update_salary(
p_emp_id IN NUMBER,
p_amount IN NUMBER
) AS
BEGIN
-- 实际实现代码
NULL; -- 示例中暂不实现
END update_salary;
-- 实现get_employee_name函数
FUNCTION get_employee_name(
p_emp_id IN NUMBER
) RETURN VARCHAR2 AS
BEGIN
-- 实际实现代码
RETURN NULL; -- 示例中暂不实现
END get_employee_name;
END emp_pkg;常用系统包，Oracle提供了许多内置包如：
- DBMS_OUTPUT：用于输出调试信息
- UTL_FILE：用于文件I/O操作
- DBMS_SCHEDULER：用于作业调度
- DBMS_LOB：用于大对象操作
- DBMS_SQL：用于动态SQL
八、触发器
PL/SQL触发器是Oracle数据库中一种特殊的存储过程，它在特定数据库事件发生时自动执行。触发器与表相关联，当指定的事件发生在表上时，触发器就会被"触发"执行
8.1 触发器的类型
DML触发器：响应DML操作(INSERT, UPDATE, DELETE)：
- 行级触发器：受影响的每一行都会触发一次
- 语句级触发器：每个DML语句触发一次，无论影响多少行
DDL触发器：响应DDL操作(CREATE, ALTER, DROP等)
系统/数据库事件触发器：响应系统事件(如数据库启动、关闭，用户登录、注销等)
INSTEAD OF触发器：用于视图上的复杂更新操作
8.2 触发器的时间点
- BEFORE：在触发事件之前执行
- AFTER：在触发事件之后执行
- INSTEAD OF：替代触发事件执行
创建触发器的基本语法：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE [OF column,...]}
ON {table_name | view_name}
[REFERENCING [OLD AS old] [NEW AS new]]
[FOR EACH ROW]
[WHEN (condition)]
DECLARE
-- 声明部分
BEGIN
-- 触发器主体
EXCEPTION
-- 异常处理
END;以上是PL/SQL的基本语法概述，PL/SQL还支持更高级的特性如批量绑定、动态SQL、集合操作等。
