中山市网站建设_网站建设公司_支付系统_seo优化-宁波市网站建设公司

PL/SQL 编程详解：语法、使用方法与综合案例

PL/SQL（Procedural Language/SQL）是 Oracle 数据库专有的过程化编程语言，它在 SQL 的基础上增加了变量、控制结构、异常处理等程序设计能力，适用于编写存储过程、函数、触发器等数据库逻辑。

一、PL/SQL 简介

1. 什么是 PL/SQL？

过程化扩展：在标准 SQL 基础上加入条件判断、循环、变量等。
块结构：程序由一个或多个“块”（Block）组成。
高效执行：可在数据库服务器端运行，减少网络传输。
强类型语言：变量需声明数据类型。

2. PL/SQL 优势

支持事务控制（COMMIT/ROLLBACK）
异常处理机制完善
可调用 SQL 函数和系统包（如 DBMS_OUTPUT）
与 Oracle 深度集成

二、安装与运行环境准备

前提：已安装 Oracle Database（如 19c/21c）或 Oracle Express Edition (XE)

1. 启用 SCOTT 用户（用于练习）

-- 以 sysdba 登录CONNECT/ASSYSDBA-- 解锁并设置密码ALTERUSERscott ACCOUNTUNLOCKIDENTIFIEDBYtiger;-- 连接 scottCONNECTscott/tiger

2. 启用 DBMS_OUTPUT（用于调试输出）

-- 在 SQL*Plus 或 SQL Developer 中执行SETSERVEROUTPUTON;

✅ 推荐工具：
SQL*Plus（命令行）
Oracle SQL Developer（图形界面，免费）
VS Code + Oracle 插件

三、PL/SQL 块结构

PL/SQL 程序由匿名块或命名块（过程、函数）组成。

1. 匿名块语法

[DECLARE -- 声明部分：变量、常量、游标等 ] BEGIN -- 执行部分：SQL 和 PL/SQL 语句 [EXCEPTION -- 异常处理部分 ] END; /

⚠️ 注意：匿名块必须以/结尾（在 SQL*Plus 中）才能执行。

2. 示例：最简单的 PL/SQL 块

-- 输出 "Hello, PL/SQL!" BEGIN DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!'); END; /

DBMS_OUTPUT.PUT_LINE是 Oracle 内置过程，用于向控制台输出信息。

四、代码注释和标识符

1. 注释方式

-- 单行注释 /* 多行注释 可跨越多行 */

2. 标识符规则

以字母开头
可包含字母、数字、下划线_、美元符$、井号#
最大长度 30 字符（Oracle 12c 之前），128 字符（12c+）
不区分大小写（但建议统一风格）

✅ 推荐命名规范：

变量：v_employee_id
常量：c_max_salary
游标：cur_emp

五、文本（字符串字面量）

用单引号'包围
单引号本身用两个单引号表示：'It''s OK'

DECLARE v_msg VARCHAR2(100) := 'He said: ''Hello!'''; BEGIN DBMS_OUTPUT.PUT_LINE(v_msg); -- 输出：He said: 'Hello!' END; /

六、数据类型、变量和常量

1. 基本数据类型

类型	说明	示例
`NUMBER(p,s)`	数值	`NUMBER(10,2)`
`VARCHAR2(n)`	可变长字符串	`VARCHAR2(50)`
`CHAR(n)`	定长字符串（空格填充）	`CHAR(10)`
`DATE`	日期时间	`SYSDATE`
`BOOLEAN`	布尔值（仅 PL/SQL 内部使用）	`TRUE/FALSE`

2. 特殊数据类型

类型	说明
`%TYPE`	引用表列的数据类型
`%ROWTYPE`	引用整行记录的结构
`RECORD`	自定义复合类型
`TABLE`	PL/SQL 表（类似数组）

3. 定义变量和常量

DECLARE -- 基本变量 v_emp_id NUMBER(4) := 7369; v_name VARCHAR2(20); v_hire_date DATE := SYSDATE; -- 使用 %TYPE（推荐！避免硬编码类型） v_salary emp.sal%TYPE; -- 自动匹配 emp 表 sal 列类型 -- 常量（必须初始化，且不能修改） c_pi CONSTANT NUMBER := 3.14159; c_company CONSTANT VARCHAR2(20) := 'Oracle Corp'; -- 布尔变量 v_is_manager BOOLEAN := FALSE; BEGIN -- 从表中查询赋值 SELECT ename, sal INTO v_name, v_salary FROM emp WHERE empno = v_emp_id; DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ', Salary: ' || v_salary); END; /

✅ 优势：使用%TYPE可使代码随表结构自动适应，提高可维护性。

七、PL/SQL 表达式

支持算术、比较、逻辑、字符串连接等操作。

DECLARE v_a NUMBER := 10; v_b NUMBER := 3; v_result NUMBER; BEGIN v_result := v_a + v_b * 2; -- 16 v_result := MOD(v_a, v_b); -- 1 v_result := POWER(v_a, 2); -- 100 IF v_a > v_b AND v_b != 0 THEN DBMS_OUTPUT.PUT_LINE('Valid calculation'); END IF; END; /

八、流程控制语句

1. 选择语句

(1) IF-THEN

IF condition THEN statements; END IF;

(2) IF-THEN-ELSE

IF salary > 5000 THEN bonus := 1000; ELSE bonus := 500; END IF;

(3) IF-THEN-ELSIF-ELSE

DECLARE v_grade CHAR(1) := 'B'; BEGIN IF v_grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); ELSIF v_grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Good'); ELSIF v_grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Fair'); ELSE DBMS_OUTPUT.PUT_LINE('Unknown grade'); END IF; END; /

(4) CASE 语句（Oracle 9i+）

CASE v_deptno WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('Accounting'); WHEN 20 THEN DBMS_OUTPUT.PUT_LINE('Research'); WHEN 30 THEN DBMS_OUTPUT.PUT_LINE('Sales'); ELSE DBMS_OUTPUT.PUT_LINE('Other Dept'); END CASE;

2. 循环语句

(1) LOOP … EXIT

DECLARE i NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('Count: ' || i); i := i + 1; EXIT WHEN i > 5; END LOOP; END; /

(2) WHILE LOOP

DECLARE i NUMBER := 1; BEGIN WHILE i <= 5 LOOP DBMS_OUTPUT.PUT_LINE('While: ' || i); i := i + 1; END LOOP; END; /

(3) FOR LOOP（最常用）

BEGIN FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE('For loop: ' || i); END LOOP; -- 反向循环 FOR i IN REVERSE 1..3 LOOP DBMS_OUTPUT.PUT_LINE('Reverse: ' || i); END LOOP; END; /

九、PL/SQL 游标（Cursor）

游标用于处理多行查询结果。

1. 基本原理

隐式游标：Oracle 自动为 DML 语句（INSERT/UPDATE/DELETE）创建。
显式游标：程序员手动声明，用于 SELECT 多行。

2. 显式游标（四步法：声明 → 打开 → 获取 → 关闭）

DECLARE -- 1. 声明游标 CURSOR cur_emp IS SELECT empno, ename, sal FROM emp WHERE deptno = 20; -- 声明记录变量 v_emp_rec cur_emp%ROWTYPE; BEGIN -- 2. 打开游标 OPEN cur_emp; -- 3. 循环获取数据 LOOP FETCH cur_emp INTO v_emp_rec; EXIT WHEN cur_emp%NOTFOUND; -- 无更多数据时退出 DBMS_OUTPUT.PUT_LINE( 'ID: ' || v_emp_rec.empno || ', Name: ' || v_emp_rec.ename || ', Salary: ' || v_emp_rec.sal ); END LOOP; -- 4. 关闭游标 CLOSE cur_emp; END; /

3. 隐式游标（SQL 游标）

Oracle 自动维护，可通过属性访问：

属性	说明
`SQL%FOUND`	DML 影响至少一行？
`SQL%NOTFOUND`	未影响任何行？
`SQL%ROWCOUNT`	影响的行数
`SQL%ISOPEN`	总为 FALSE（隐式游标自动关闭）

BEGIN UPDATE emp SET sal = sal * 1.1 WHERE deptno = 50; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows.'); ELSE DBMS_OUTPUT.PUT_LINE('No employees in dept 50.'); END IF; END; /

4. 游标属性总结

游标类型	`%FOUND`	`%NOTFOUND`	`%ROWCOUNT`	`%ISOPEN`
显式	是	是	是	是
隐式	是	是	是	否（始终 FALSE）

5. 游标变量（REF CURSOR）

用于动态 SQL 或跨过程传递结果集（高级用法，略）。

6. 通过 FOR 语句循环游标（推荐！自动管理打开/关闭）

DECLARE CURSOR cur_high_sal IS SELECT ename, sal FROM emp WHERE sal > 2500; BEGIN -- 自动 OPEN, FETCH, CLOSE FOR rec IN cur_high_sal LOOP DBMS_OUTPUT.PUT_LINE(rec.ename || ' earns $' || rec.sal); END LOOP; END; /

✅ 优点：代码简洁，不易出错。

十、PL/SQL 异常处理

1. 异常处理方法

捕获运行时错误（如除零、无数据、重复主键等）
防止程序崩溃，提供友好提示

2. 异常处理语法

BEGIN -- 可能出错的代码 EXCEPTION WHEN exception_name THEN -- 处理特定异常 WHEN OTHERS THEN -- 处理所有其他异常 END; /

3. 预定义异常（常用）

异常名	触发条件
`NO_DATA_FOUND`	SELECT INTO 未返回行
`TOO_MANY_ROWS`	SELECT INTO 返回多行
`ZERO_DIVIDE`	除零错误
`DUP_VAL_ON_INDEX`	唯一约束冲突
`INVALID_NUMBER`	字符串转数字失败

DECLARE v_ename emp.ename%TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE empno = 9999; -- 不存在的员工 EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Error: Employee not found!'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Error: Multiple employees found!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM); END; /

SQLERRM返回错误消息文本。

4. 自定义异常

DECLARE -- 声明自定义异常 e_invalid_salary EXCEPTION; v_sal emp.sal%TYPE := 100; BEGIN IF v_sal < 500 THEN -- 抛出自定义异常 RAISE e_invalid_salary; END IF; EXCEPTION WHEN e_invalid_salary THEN DBMS_OUTPUT.PUT_LINE('Error: Salary too low!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('System error: ' || SQLERRM); END; /

也可使用RAISE_APPLICATION_ERROR(-20001, '自定义消息')抛出带编号的错误。

十一、综合性实战案例

案例：批量调整员工工资并记录日志

需求：

为每个部门工资最低的员工加薪 10%
记录调整前后的工资到日志表
处理可能的异常（如无员工、加薪后超上限）
使用游标遍历部门

步骤 1：创建日志表

CREATETABLEemp_salary_log(log_id NUMBER GENERATED ALWAYSASIDENTITY,empno NUMBER(4),old_sal NUMBER(7,2),new_sal NUMBER(7,2),update_timeDATEDEFAULTSYSDATE);

步骤 2：PL/SQL 程序

DECLARE -- 自定义异常 e_salary_too_high EXCEPTION; PRAGMA EXCEPTION_INIT(e_salary_too_high, -20001); -- 游标：每个部门最低工资员工 CURSOR cur_min_sal_dept IS SELECT e.empno, e.ename, e.sal, e.deptno, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.sal = ( SELECT MIN(sal) FROM emp e2 WHERE e2.deptno = e.deptno ); v_new_sal emp.sal%TYPE; v_max_allowed CONSTANT NUMBER := 10000; BEGIN DBMS_OUTPUT.PUT_LINE('Starting salary adjustment...'); FOR rec IN cur_min_sal_dept LOOP BEGIN v_new_sal := rec.sal * 1.1; -- 检查是否超过上限 IF v_new_sal > v_max_allowed THEN RAISE e_salary_too_high; END IF; -- 更新工资 UPDATE emp SET sal = v_new_sal WHERE empno = rec.empno; -- 记录日志 INSERT INTO emp_salary_log (empno, old_sal, new_sal) VALUES (rec.empno, rec.sal, v_new_sal); DBMS_OUTPUT.PUT_LINE( 'Adjusted: ' || rec.ename || ' (Dept: ' || rec.dname || ') from ' || rec.sal || ' to ' || v_new_sal ); EXCEPTION WHEN e_salary_too_high THEN DBMS_OUTPUT.PUT_LINE( 'Warning: ' || rec.ename || '''s new salary exceeds limit. Skipped.' ); WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Log entry already exists for ' || rec.empno); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error for ' || rec.ename || ': ' || SQLERRM); END; END LOOP; COMMIT; -- 提交事务 DBMS_OUTPUT.PUT_LINE('Adjustment completed.'); EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Transaction rolled back due to error: ' || SQLERRM); END; /

输出示例：

Starting salary adjustment... Adjusted: SMITH (Dept: RESEARCH) from 800 to 880 Adjusted: JAMES (Dept: SALES) from 950 to 1045 Adjusted: MILLER (Dept: ACCOUNTING) from 1300 to 1430 Adjustment completed.

十二、总结

模块	核心知识点
块结构	DECLARE/BEGIN/EXCEPTION/END
变量/常量	`%TYPE`,`%ROWTYPE`, CONSTANT
控制结构	IF, CASE, LOOP, FOR
游标	显式/隐式，FOR 循环游标
异常处理	预定义异常、自定义异常、SQLERRM
最佳实践	使用绑定变量、避免硬编码、合理使用 COMMIT/ROLLBACK

💡学习建议：
从简单匿名块开始练习
多使用DBMS_OUTPUT.PUT_LINE调试
优先使用FOR循环游标
异常处理要覆盖OTHERS

掌握 PL/SQL 是成为 Oracle 开发者或 DBA 的关键一步。后续可深入学习存储过程、函数、包、触发器等高级特性。

中山市网站建设_网站建设公司_支付系统_seo优化