2026 年最常见的 20 道 PL/SQL 面试题及答案

Q: PL/SQL 中的触发器是什么？

在 INSERT 、 UPDATE 、 DELETE 操作之前或之后自动执行的动作。

Q: 什么是 PL/SQL 的批量处理？

批量处理（ BULK COLLECT 、 FORALL ）在处理大数据集时通过减少 PL/SQL 与 SQL 引擎之间的上下文切换来提升性能。

Q: PL/SQL 中 BULK COLLECT 与 FORALL 有何区别？

BULK COLLECT 将多行数据从 SQL 一次性取入 PL/SQL 集合，替代逐行的 SELECT INTO 。 FORALL 通过一次性将一组值发送给 SQL 引擎来执行批量 DML（插入、更新、删除）。 BULK COLLECT 用于读取数据， FORALL 用于写入数据。两者都能减少 SQL 与 PL/SQL 引擎间的上下文切换，这是数据密集型 PL/SQL 代码的主要性能开销来源。

Q: 如何在 PL/SQL 中防止 SQL 注入？

PL/SQL 中的主要风险是通过字符串拼接用户输入并交给 EXECUTE IMMEDIATE 执行的动态 SQL。请改用 绑定变量 ： EXECUTE IMMEDIATE 'SELECT ... WHERE id = :id' USING p_id 。绑定变量不会被当作 SQL 语法解析，因此注入代码无法执行。若由于语句结构必须动态变化（表名、列名）而需要拼接，请使用白名单验证允许的取值，并采用最小权限的数据库授权，即使发生注入也能最大程度降低影响。

PL/SQL 是 Oracle 面向 SQL 的过程化语言扩展。如果您要使用 Oracle 数据库，就必须掌握它。这对于数据库管理员、开发人员和数据分析师等关键岗位都同样适用，因为他们都需要在数据库中处理复杂的业务逻辑。

我认为面试官通常从三个关键方面评估候选人：

概念理解：核心原理、数据类型、控制结构与异常处理。
实战编码能力：编写高效的存储过程、函数、触发器与包。
性能考量：优化 PL/SQL 代码以提升执行时间、降低资源消耗，并避免如 SQL 与 PL/SQL 之间过度上下文切换等错误。

为此，我整理了一份 PL/SQL 面试题指南。我将从基础概念开始，逐步扩展到批量处理、动态 SQL 与性能调优等更高级的话题。

如果您想系统理解 Oracle 数据库的基础概念，推荐学习我们的 Introduction to Oracle SQL 课程，涵盖 SQL 与 PL/SQL 在 Oracle 执行引擎中的交互方式。

要点速览（TL;DR）

PL/SQL 是 Oracle 的 SQL 过程化扩展，新增变量、循环、条件、异常处理，以及通过过程、函数和包实现模块化代码
初级问题涵盖代码块结构（DECLARE、BEGIN、EXCEPTION、END）、标量/复合/引用数据类型与控制结构
中级问题涵盖存储过程 vs. 函数、包、触发器、异常处理，以及 %NOTFOUND 等游标属性
高级问题涵盖批量处理（BULK COLLECT、FORALL）、EXECUTE IMMEDIATE 动态 SQL、复合触发器与 PRAGMA 指令
情景题考察实战能力：高并发触发器设计、批处理事务、包的架构设计，以及使用 NOWAIT/SKIP LOCKED 预防死锁

PL/SQL 初级面试题

在面试的初始阶段，面试官会通过基础性问题评估您对数据库与 PL/SQL 概念的掌握。请学习下列问题与答案，为面试首轮做好准备。

1. 什么是 PL/SQL？

PL/SQL 是 Oracle 对 SQL 的过程化扩展。SQL 是声明式语言，聚焦于数据检索与操作；而 PL/SQL 支持实现过程化逻辑，是在数据库中编写复杂业务规则的强大工具。它支持变量、循环、条件、异常处理，并通过过程、函数与包实现模块化编程。

如需直观比较两种语言在实践中的差异，请参阅我们的教程 Difference Between SQL and PL/SQL。

2. PL/SQL 代码块的基本结构是什么？

PL/SQL 代码块是 PL/SQL 的基本执行单元，由四个主要部分组成：

DECLARE（可选）：用于定义变量、常量、游标与用户自定义类型。
BEGIN：可执行部分，用于编写 SQL 查询与过程化语句。
EXCEPTION（可选）：处理运行时错误与异常，实现平滑的错误恢复。
END;：标记代码块结束。

3. 常用的 PL/SQL 数据类型有哪些？

PL/SQL 支持多种数据类型，主要分为以下几类：

标量类型：单值类型，如 NUMBER、VARCHAR2、DATE、BOOLEAN。
复合类型：集合，如 RECORD（自定义结构）与 TABLE/VARRAY（数组）。
引用类型：指向数据库对象的指针，如用于动态查询处理的 REF CURSOR。

4. PL/SQL 的基本控制结构有哪些？

PL/SQL 提供多种控制结构来管理程序流程：

循环：包括 LOOP、FOR LOOP 与 WHILE LOOP，用于重复执行语句。
条件语句：包括 IF 与 CASE 语句，根据条件执行不同的代码块。另一个值得学习的条件函数是 DECODE() 函数。

PL/SQL 中级面试题

在掌握基础问题后，接下来来看一些中级 PL/SQL 面试题。在这一阶段，面试官会考察您将 PL/SQL 概念落地实现的技术熟练度。

5. 存储过程与函数有什么区别？

存储过程与函数都是可复用的 PL/SQL 代码块，但用途有所不同。

存储过程用于执行不返回值的操作，如插入、更新或删除数据。它们适合修改数据或执行不需要返回结果的复杂操作。

例如，下例过程通过对给定 employee_id 的 salary 增加指定的 p_increment 来更新工资，使工资可根据输入参数动态更新，

CREATE PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS  
BEGIN  
    -- Update the salary of the employee with the given ID  
    UPDATE employees  
    SET salary = salary + p_increment  
    WHERE employee_id = p_emp_id;  
END;技术支持

另一方面，函数在执行后会返回一个值。它们适用于需要返回结果的计算或数据检索。

下例函数简化了获取员工工资的过程，使其可在 SQL 查询或其他过程内复用。

CREATE FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER AS  
    v_salary NUMBER;  
BEGIN  
    -- Retrieve the salary for the given employee ID  
    SELECT salary INTO v_salary  
    FROM employees  
    WHERE employee_id = p_emp_id;  
    
    -- Return the retrieved salary  
    RETURN v_salary;  
END;技术支持

属性	存储过程	函数
是否返回值	否（使用 OUT 参数）	是（单一返回值）
可用于 SQL 查询	否	是
主要目的	执行动作（插入、更新、删除）	计算并返回结果
调用方式	`EXEC procedure_name(...)`	`SELECT function_name(...) FROM DUAL`
是否可能有副作用	是	应避免（破坏确定性）

我们的教程 User Defined Stored Procedures in SQL 更深入地介绍了命名规范、参数模式与真实场景模式。

6. 什么是 PL/SQL 包？

PL/SQL 包是相关过程、函数与变量的集合，通过封装实现更好的组织与复用。它包含两部分：

包规范：声明公共元素（过程、函数、变量）。
包体：包含规范中声明的过程和函数的实现细节。

例如，下述语句创建了包 employee_pkg，在规范中定义了一个用于上调员工工资的过程和一个返回员工总数的函数，其实现放在包体中提供。

-- Create a package named 'employee_pkg'
CREATE PACKAGE employee_pkg AS  

    -- Procedure to increase an employee's salary by a percentage  
    PROCEDURE raise_salary(p_emp_id NUMBER, p_percent NUMBER);  

    -- Function to return the total number of employees  
    FUNCTION get_total_employees RETURN NUMBER;  

END employee_pkg;技术支持

7. 什么是 PL/SQL 触发器？

触发器是在特定数据库事件（如插入、更新或删除）发生时自动执行的 PL/SQL 代码块。触发器用于强制执行业务规则，也常用于审计。类型包括：

行级触发器：对每一条受影响的记录执行一次。
语句级触发器：对每条 SQL 语句执行一次，无论影响的记录数多少。

下述语句创建了 employees 表上的一个 AFTER UPDATE 触发器 trg_salary_audit，将工资变更记录写入 salary_audit 表，包含员工 ID、旧/新工资与更新时间戳。

-- Create or replace a trigger 'trg_salary_audit'
CREATE OR REPLACE TRIGGER trg_salary_audit  
AFTER UPDATE OF salary ON employees  -- Fires after salary updates in 'employees' table
FOR EACH ROW  -- Executes for each updated row
BEGIN  
    -- Inserts old and new salary details into 'salary_audit' table
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)  
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);  
END;技术支持

学习我们的 SQL Triggers 教程，以便在面试中遇到触发器相关问题时胸有成竹。

还有一种尚未提到的触发器类型是INSTEAD OF 触发器。与行级和语句级触发器不同，INSTEAD OF 触发器在视图上的 DML 语句被触发时“替代”执行，让您编写逻辑去处理底层基表上的操作。它是让不可更新视图接受 INSERT、UPDATE 与 DELETE 操作的标准做法。

-- Allow inserts into a non-updatable view of employees and departments
CREATE OR REPLACE TRIGGER trg_instead_of_insert
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (employee_id, employee_name, department_id)
    VALUES (:NEW.employee_id, :NEW.employee_name, :NEW.department_id);
END;技术支持

8. PL/SQL 中有哪些异常处理方法？

PL/SQL 提供了错误处理机制以提升稳定性并防止崩溃。异常类型包括：

预定义异常：内置异常，如 NO_DATA_FOUND、TOO_MANY_ROWS 和 ZERO_DIVIDE。
用户自定义异常：使用 EXCEPTION 声明并通过 RAISE 抛出。

例如，下面的 PL/SQL 代码块获取 ID 为 100 的员工工资，若工资低于 1000 则抛出自定义异常，并处理可能的错误，包括员工记录缺失与意外异常。

DECLARE  
    v_salary NUMBER;  -- Variable to store the employee's salary  
    e_low_salary EXCEPTION;  -- Custom exception for low salary  

BEGIN  
    -- Retrieve salary of employee with ID 100  
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;  

    -- Raise exception if salary is below 1000  
    IF v_salary < 1000 THEN  
        RAISE e_low_salary;  
    END IF;  

EXCEPTION  
    WHEN NO_DATA_FOUND THEN  -- Handle case where employee ID is not found  
        DBMS_OUTPUT.PUT_LINE('Employee not found.');  
    WHEN e_low_salary THEN  -- Handle custom low salary exception  
        DBMS_OUTPUT.PUT_LINE('Salary is below the allowed threshold.');  
    WHEN OTHERS THEN  -- Handle any other unexpected errors  
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);  

END;技术支持

异常处理的最佳实践包括：

始终预判潜在异常。
使用有意义的错误信息。
记录异常以便审计。
保持异常处理代码简洁，并聚焦于恢复。

9. 如何验证 UPDATE 语句是否执行成功？

可使用 SQL 的 %NOTFOUND 属性判断 UPDATE 是否更改了任何记录。如果最近一次执行的 SQL 语句未影响任何行，该变量返回 TRUE。

例如，下面的语句将 10 号部门员工工资上调 10%，随后使用 %NOTFOUND 属性检查 UPDATE 是否影响到任何行。若未更新，则输出“No rows were updated.”；若有更新，则使用 SQL%ROWCOUNT 输出受影响的行数。

DECLARE
    -- Declare a variable to store the number of rows updated
    rows_updated INTEGER;
BEGIN
    -- Perform an UPDATE statement on the 'employees' table
    UPDATE employees
    SET salary = salary * 1.1
    WHERE department_id = 10;

    -- Check if any rows were updated by using %NOTFOUND
    IF SQL%NOTFOUND THEN
        -- If no rows were updated, print a message
        DBMS_OUTPUT.PUT_LINE('No rows were updated.');
    ELSE
        -- If rows were updated, print how many rows were affected
        rows_updated := SQL%ROWCOUNT;  -- Store the number of rows updated
        DBMS_OUTPUT.PUT_LINE(rows_updated || ' rows were updated.');
    END IF;
END;
/技术支持

PL/SQL 高级面试题

现在我们来探讨一些高级面试题，适用于需要更多经验的高级岗位。

10. 如何优化 PL/SQL 的性能？

PL/SQL 性能优化主要聚焦于五种技术。

批量处理（BULK COLLECT / FORALL）： 批量获取或修改多行，避免逐行处理。第 11 题有实战示例。
在 BULK COLLECT 中使用 LIMIT： 使用 BULK COLLECT INTO v_coll LIMIT 500 限制每批的内存占用。否则 Oracle 会一次性将全部结果集加载到内存，遇到大表将导致问题。
NOCOPY 提示： 将大的 IN OUT 参数按引用传递而非按值传递。对 50 MB 集合的深拷贝在每次调用都会很昂贵；NOCOPY 可跳过拷贝。
本地编译（native compilation）： 在会话或系统级设置 PLSQL_CODE_TYPE = NATIVE。Oracle 将 PL/SQL 编译为机器码而非字节码，可降低计算密集型例程的 CPU 时间。
RESULT_CACHE： 将该 pragma 添加到具有确定性的、重复以相同输入被调用的函数。Oracle 会直接返回缓存结果，而无需再次执行函数体。

如需更广泛了解与 PL/SQL 调优互补的 SQL 性能技术，请参阅我们的 SQL Query Optimization 指南，涵盖索引设计与查询改写在内的 15 种技术。

11. 如何使用批量操作减少上下文切换？

PL/SQL 提供批量处理技术，通过一次性获取或修改多行数据来优化 SQL 与 PL/SQL 的交互。

例如，下例通过 BULK COLLECT 将 10 号部门的所有员工批量取入集合，并遍历输出每位员工姓名，从而减少 SQL 与 PL/SQL 之间的上下文切换、提升性能。

DECLARE  
    -- Define a table-type collection based on the 'employees' table structure  
    TYPE emp_table IS TABLE OF employees%ROWTYPE;  
    v_emps emp_table;  -- Declare a variable of this type  

BEGIN  
    -- Bulk fetch employees from department 10 into the collection  
    SELECT * BULK COLLECT INTO v_emps FROM employees WHERE department_id = 10;  

    -- Loop through the collection and print employee names  
    FOR i IN 1..v_emps.COUNT LOOP  
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emps(i).employee_name);  
    END LOOP;  

END;技术支持

同时，下例使用 FORALL 执行批量更新，将 ID 为 101、102、103 的员工工资上调 10%。

DECLARE  
    -- Define a table-type collection for employee IDs  
    TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;  
    -- Initialize collection with specific employee IDs  
    v_emp_ids t_emp_ids := t_emp_ids(101, 102, 103);  

BEGIN  
    -- Bulk update salaries by 10% for specified employee IDs  
    FORALL i IN 1..v_emp_ids.COUNT  
        UPDATE employees SET salary = salary * 1.10 WHERE employee_id = v_emp_ids(i);  

END;技术支持

12. 什么是动态 SQL 与 Ref Cursor？

动态 SQL 允许在运行时动态执行 SQL 语句，适用于表名、列名或查询结构可变的场景。

下面的 PL/SQL 代码块使用动态 SQL 统计 employees 表的行数并打印结果。我喜欢这种方式，因为它具备更好的灵活性。

DECLARE  
    v_table_name VARCHAR2(50) := 'employees';  -- Store table name  
    v_count NUMBER;  -- Variable to hold row count  

BEGIN  
    -- Dynamically count rows in the specified table  
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name INTO v_count;  

    -- Print the total count  
    DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);  

END;  技术支持

Ref Cursor（引用游标）是可在运行时打开、提取与关闭的动态游标，支持在程序单元之间传递查询结果。

下例使用 REF CURSOR 获取并打印 20 号部门的员工姓名。游标以动态方式打开，通过循环提取并在处理后关闭。

DECLARE  
    -- Define a REF CURSOR type  
    TYPE emp_ref_cursor IS REF CURSOR;  
    v_cursor emp_ref_cursor;  -- Declare a cursor variable  
    v_name employees.employee_name%TYPE;  -- Variable to store employee name  

BEGIN  
    -- Open the cursor for employees in department 20  
    OPEN v_cursor FOR SELECT employee_name FROM employees WHERE department_id = 20;  

    -- Fetch and print employee names in a loop  
    LOOP  
        FETCH v_cursor INTO v_name;  
        EXIT WHEN v_cursor%NOTFOUND;  
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);  
    END LOOP;  

    -- Close the cursor  
    CLOSE v_cursor;  

END;技术支持

若需深入学习（含参数化查询与 DBMS_SQL 包），请参阅我们的 Dynamic SQL tutorial。

13. 如何处理“变异表”（mutating table）错误？

当触发器试图修改其所在的同一张表时，会发生变异表错误。复合触发器通过将触发器执行拆分为 BEFORE、AFTER 与 FOR EACH ROW 等多个阶段，避免直接修改带来的问题。

下面的复合触发器通过在每行更新前收集数据、在语句结束后批量插入的方式，将工资变更高效记录到 salary_audit 表，减少上下文切换并提升性能。

CREATE OR REPLACE TRIGGER trg_salary_audit  
FOR UPDATE OF salary ON employees  -- Trigger fires on salary updates  
COMPOUND TRIGGER  

    -- Declare an associative array to store audit records  
    TYPE t_salary_audit IS TABLE OF salary_audit%ROWTYPE INDEX BY PLS_INTEGER;  
    v_audit_data t_salary_audit;  
    v_idx PLS_INTEGER := 0;  

    -- Before updating each row, store old and new salary details  
    BEFORE EACH ROW IS  
    BEGIN  
        v_idx := v_idx + 1;  
        v_audit_data(v_idx).employee_id := :OLD.employee_id;  
        v_audit_data(v_idx).old_salary := :OLD.salary;  
        v_audit_data(v_idx).new_salary := :NEW.salary;  
        v_audit_data(v_idx).change_date := SYSDATE;  
    END BEFORE EACH ROW;  

    -- After the statement, insert all audit records in bulk  
    AFTER STATEMENT IS  
    BEGIN  
        FORALL i IN 1..v_idx  
            INSERT INTO salary_audit VALUES v_audit_data(i);  
    END AFTER STATEMENT;  

END trg_salary_audit;技术支持

14. 什么是 PL/SQL 中的重载（overloading）？

重载允许在同一个包内定义多个同名但参数不同的过程或函数。它通过为相似操作提供多种方式来增强代码的可读性与可维护性。

如下所示，包体实现了两个名为 update_salary 的重载过程：一个按指定金额增加工资，另一个按生效日期设置新工资，并相应更新 employees 表。

-- Create the package specification
CREATE OR REPLACE PACKAGE emp_pkg AS  
    -- Procedure to increment salary by a specified amount  
    PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER);

    -- Overloaded procedure to set a new salary with an effective date  
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER, p_effective_date DATE);
END emp_pkg;
/技术支持

-- Create the package body
CREATE OR REPLACE PACKAGE BODY emp_pkg AS  

    -- Procedure to increment salary by a specified amount  
    PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS  
    BEGIN  
        UPDATE employees  
        SET salary = salary + p_increment  
        WHERE employee_id = p_emp_id;  
    END update_salary;  

    -- Overloaded procedure to set a new salary with an effective date  
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER, p_effective_date DATE) AS  
    BEGIN  
        UPDATE employees  
        SET salary = p_new_salary, last_update = p_effective_date  
        WHERE employee_id = p_emp_id;  
    END update_salary;  

END emp_pkg;
/技术支持

15. PL/SQL 中的编译指示与 pragma 是什么？

PL/SQL 提供编译指示（PRAGMA）用于优化代码与处理异常。常见的 pragma 包括：

PRAGMA EXCEPTION_INIT：将用户自定义异常与 Oracle 错误码关联。
PRAGMA SERIALLY_REUSABLE：优化包的内存使用以提升可扩展性。

下面的 PL/SQL 代码块通过自定义异常 e_invalid_salary（映射到错误码 -20001）来处理无效工资的插入。如果异常被抛出，则打印错误信息。

DECLARE  
    e_invalid_salary EXCEPTION;  -- Declare custom exception for invalid salary  
    PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);  -- Associate exception with error code -20001  
BEGIN  
    -- Attempt to insert an employee with invalid salary  
    INSERT INTO employees (employee_id, salary) VALUES (999, -1000);  
EXCEPTION  
    -- Handle the custom exception and print a message  
    WHEN e_invalid_salary THEN  
        DBMS_OUTPUT.PUT_LINE('Invalid salary detected!');  
END;  技术支持

16. 跟踪与调试 PL/SQL 代码的方法有哪些？

PL/SQL 提供多个内置包用于跟踪与调试代码性能。常见方法包括使用 DBMS_TRACE 跟踪执行流程，使用 DBMS_APPLICATION_INFO 监控会话活动，以及使用 DBMS_SESSION 收集会话级诊断信息。

下述语句首先通过 DBMS_SESSION.set_sql_trace(TRUE) 启用 SQL 跟踪，然后使用 DBMS_APPLICATION_INFO.set_client_info() 设置客户端会话信息以便监控。接着执行一个模拟 UPDATE 的 PL/SQL 代码块。最后使用 DBMS_SESSION.set_sql_trace(FALSE) 关闭跟踪。

-- Enable tracing for the current session using DBMS_SESSION
BEGIN
   -- Start session-level tracing
   DBMS_SESSION.set_sql_trace(TRUE);
END;
/

-- Set application information using DBMS_APPLICATION_INFO
BEGIN
   -- Set the application name and action for session monitoring
   DBMS_APPLICATION_INFO.set_client_info('Trace Debug Session');
   DBMS_APPLICATION_INFO.set_action('Debugging PL/SQL Code');
END;
/

-- Example PL/SQL block that simulates a process for debugging
DECLARE
   v_employee_id NUMBER := 100;
BEGIN
   -- Example query to fetch employee details
   FOR rec IN (SELECT first_name, last_name FROM employees WHERE employee_id = v_employee_id) LOOP
      DBMS_OUTPUT.put_line('Employee: ' || rec.first_name || ' ' || rec.last_name);
   END LOOP;
   
   -- Simulate some logic that could be traced
   IF v_employee_id = 100 THEN
      DBMS_OUTPUT.put_line('Employee ID is 100');
   END IF;
END;
/

-- Disable tracing after the session is complete
BEGIN
   -- Stop session-level tracing
   DBMS_SESSION.set_sql_trace(FALSE);
END;
/技术支持

情景式 PL/SQL 面试题

在真实的企业级应用中，PL/SQL 常用于处理复杂的业务逻辑。面试官通常会考察候选人将 PL/SQL 应用于实际场景的能力。以下是一些关键挑战与应对策略。

在最后这个部分，如果您正在面试，我建议您思考如何按照下述模板组织答案，但当然也要结合您所在行业与自身经验进行调整。如果您在面试候选人、并希望获得如何提问与评估答案深度的灵感，也希望本节内容能为您的问题设计与评估标准提供启发。

17. 在高并发环境中如何设计触发器？

设想一个场景：需要强制执行一条业务规则，即同一名员工的工资每天最多只能被更新一次。然而数据库事务并发很高，简单的触发器可能导致争用或性能问题。

与其使用每次更新都触发的行级触发器、从而拖慢性能，不如通过语句级触发器配合日志表，来阻止同一天内的重复工资更新。

例如，下述触发器会在允许更新前检查 salary_update_log 表，阻止同一天内对同一员工的多次工资更新；若当天已更新则抛出错误，否则记录更新日期。

-- Create a table to log the last salary update for each employee
CREATE TABLE salary_update_log (
    employee_id NUMBER PRIMARY KEY,  -- Employee ID as primary key
    last_update DATE  -- Date of the last salary update
);

-- Create or replace a compound trigger to prevent multiple salary updates on the same day
CREATE OR REPLACE TRIGGER trg_prevent_multiple_salary_update  
FOR UPDATE OF salary ON employees  
COMPOUND TRIGGER  

    -- Declare a variable to store last salary update date per row
    TYPE emp_log_type IS TABLE OF DATE INDEX BY PLS_INTEGER;
    emp_log emp_log_type;

    BEFORE STATEMENT IS  
    BEGIN  
        -- Load existing salary update logs into memory for reference
        FOR rec IN (SELECT employee_id, last_update FROM salary_update_log) LOOP  
            emp_log(rec.employee_id) := rec.last_update;  
        END LOOP;  
    END BEFORE STATEMENT;  

    BEFORE EACH ROW IS  
    BEGIN  
        -- Check if an entry exists for this employee
        IF emp_log.EXISTS(:NEW.employee_id) THEN  
            -- Validate if salary was updated today
            IF emp_log(:NEW.employee_id) = TRUNC(SYSDATE) THEN  
                RAISE_APPLICATION_ERROR(-20010, 'Salary can only be updated once per day');  
            ELSE  
                -- Update log in memory
                emp_log(:NEW.employee_id) := TRUNC(SYSDATE);  
            END IF;  
        ELSE  
            -- Insert a new log entry into memory
            emp_log(:NEW.employee_id) := TRUNC(SYSDATE);  
        END IF;  
    END BEFORE EACH ROW;  

    AFTER STATEMENT IS  
    BEGIN  
        -- Apply changes to the log table after all row updates
        FOR i IN emp_log.FIRST .. emp_log.LAST LOOP  
            MERGE INTO salary_update_log l  
            USING (SELECT i AS employee_id, emp_log(i) AS last_update FROM DUAL) s  
            ON (l.employee_id = s.employee_id)  
            WHEN MATCHED THEN  
                UPDATE SET l.last_update = s.last_update  
            WHEN NOT MATCHED THEN  
                INSERT (employee_id, last_update) VALUES (s.employee_id, s.last_update);  
        END LOOP;  
    END AFTER STATEMENT;  

END trg_prevent_multiple_salary_update;技术支持

上述方案可降低行级锁定并在批量更新时尽量减少事务冲突。

18. 如何将大型事务拆分为更小的批次以提高可靠性？

设想一个银行系统需要对数百万客户账户批量上调利率。如果通过单个大型事务执行，可能会长时间锁表或导致回滚失败。

在这种情况下，使用带批次 COMMIT 的批量处理，增量地处理数据并减少争用。

下面的 PL/SQL 代码块通过 BULK COLLECT 获取账户 ID，并逐个迭代将利率上调 5%。每更新 1000 条记录提交一次，以提升性能并降低资源占用。最后一次提交确保剩余更新被保存。

DECLARE  
    -- Define a collection type for account IDs  
    TYPE t_accounts IS TABLE OF NUMBER;  -- Use NUMBER instead of referencing accounts.account_id
    v_account_ids t_accounts;  -- Variable to store account IDs  
    v_batch_size CONSTANT NUMBER := 1000;  -- Batch size for commits  

BEGIN  
    -- Bulk collect all account IDs into the collection  
    SELECT account_id BULK COLLECT INTO v_account_ids FROM accounts;  

    -- Loop through each account ID to update the interest rate  
    FOR i IN 1 .. v_account_ids.COUNT LOOP  
        UPDATE accounts  
        SET interest_rate = interest_rate * 1.05  -- Increase interest rate by 5%  
        WHERE account_id = v_account_ids(i);  

        -- Commit after every 1000 updates  
        IF MOD(i, v_batch_size) = 0 THEN  
            COMMIT;  
        END IF;  
    END LOOP;  

    COMMIT; -- Final commit for any remaining updates  
END;
/技术支持

该方案可在薪酬发放等金融系统中提高可靠性，避免事务失败。

我们的 SQL Transactions guide 深入讲解了 COMMIT、ROLLBACK、SAVEPOINT 以及隔离级别——这些都是情景题中常见的概念。

19. 如何将复杂逻辑组织进包以提升可维护性？

某零售公司需要实现一个订单处理系统，涉及多个步骤：检查库存可用性、预留库存、计算折扣与记录交易历史。

与其编写分散的独立过程，不如通过 PL/SQL 包对相关过程与函数进行结构化组织，从而提升代码复用性与可维护性。

下面的包规范定义了用于订单处理的函数与过程，包括检查库存、预留库存、计算折扣与记录交易状态。它为处理订单相关任务提供了模块化方案。

-- Create a package specification for order processing functions and procedures
CREATE PACKAGE order_processing_pkg AS  

    -- Function to check if enough inventory is available for the product
    FUNCTION check_inventory(p_product_id NUMBER, p_quantity NUMBER) RETURN BOOLEAN;  

    -- Procedure to reserve stock for a specific order
    PROCEDURE reserve_stock(p_order_id NUMBER, p_product_id NUMBER, p_quantity NUMBER);  

    -- Function to calculate discount based on customer and total amount
    FUNCTION calculate_discount(p_customer_id NUMBER, p_total_amount NUMBER) RETURN NUMBER;  

    -- Procedure to log the status of a transaction
    PROCEDURE log_transaction(p_order_id NUMBER, p_status VARCHAR2);  

END order_processing_pkg;技术支持

随后使用如下包体实现包规范中定义的函数与过程，处理检查库存可用性、预留库存、计算折扣与记录交易状态等关键任务，确保订单处理顺畅。

-- Create the package body implementing the functions and procedures for order processing
CREATE PACKAGE BODY order_processing_pkg AS  

    -- Function to check if sufficient inventory is available for the product
    FUNCTION check_inventory(p_product_id NUMBER, p_quantity NUMBER) RETURN BOOLEAN AS  
        v_available_qty NUMBER;  -- Variable to store available quantity  
    BEGIN  
        -- Retrieve the available quantity from inventory
        SELECT stock_quantity INTO v_available_qty FROM inventory WHERE product_id = p_product_id;  
        -- Return true if enough stock is available, otherwise false
        RETURN v_available_qty >= p_quantity;  
    END check_inventory;  

    -- Procedure to reserve stock for a specific order
    PROCEDURE reserve_stock(p_order_id NUMBER, p_product_id NUMBER, p_quantity NUMBER) AS  
    BEGIN  
        -- Deduct the ordered quantity from the inventory
        UPDATE inventory SET stock_quantity = stock_quantity - p_quantity WHERE product_id = p_product_id;  
    END reserve_stock;  

    -- Function to calculate a discount based on the total order amount
    FUNCTION calculate_discount(p_customer_id NUMBER, p_total_amount NUMBER) RETURN NUMBER AS  
        v_discount NUMBER := 0;  -- Initialize discount to 0  
    BEGIN  
        -- Apply 10% discount if the total amount is greater than 500
        IF p_total_amount > 500 THEN  
            v_discount := p_total_amount * 0.10;  
        END IF;  
        -- Return the calculated discount
        RETURN v_discount;  
    END calculate_discount;  

    -- Procedure to log the transaction status
    PROCEDURE log_transaction(p_order_id NUMBER, p_status VARCHAR2) AS  
    BEGIN  
        -- Insert a log entry for the order status
        INSERT INTO order_log (order_id, status, log_date) VALUES (p_order_id, p_status, SYSDATE);  
    END log_transaction;  

END order_processing_pkg;技术支持

20. 在高事务系统中如何处理死锁？

设想一个金融系统会频繁同时更新多张相关表。当两笔事务互相等待对方已加锁的资源时，就会产生死锁，从而导致性能瓶颈。

为解决此问题，应始终在各事务间以一致的顺序加锁行。同时使用 NOWAIT 或 SKIP LOCKED，避免无限等待。

例如，下面的 PL/SQL 代码块尝试使用 FOR UPDATE NOWAIT 锁定 accounts 表中的特定行，若该行已被其他会话锁定，则立即失败。成功加锁后，更新相关交易状态并提交。若发生错误，则捕获异常并打印错误信息。

DECLARE  
    v_balance NUMBER(15,2);  -- Declare variable to store the account balance  
BEGIN  
    -- Lock the account row for update to prevent other sessions from modifying it  
    SELECT balance INTO v_balance FROM accounts  
    WHERE account_id = 101 FOR UPDATE NOWAIT;  

    -- Update the transaction status to 'Processed' for all transactions related to this account  
    UPDATE transactions  
    SET status = 'Processed'  
    WHERE account_id = 101 AND status = 'Pending';  -- Update only pending transactions  

    COMMIT;  -- Commit the changes  

EXCEPTION  
    -- Handle errors, such as locking issues or unexpected exceptions  
    WHEN OTHERS THEN  
        DBMS_OUTPUT.PUT_LINE('Transaction failed: ' || SQLERRM);  
        ROLLBACK;  -- Ensure rollback in case of failure  
END;
/技术支持

类似地，下面的 PL/SQL 代码块通过 FOR UPDATE SKIP LOCKED 锁定待处理交易并跳过已被其他会话锁定的行。它将每条交易状态更新为“Processing”，并在末尾提交变更。

DECLARE  
    -- Declare a cursor to select pending transactions and lock rows for update
    CURSOR c_pending_txns IS  
        SELECT transaction_id FROM transactions WHERE status = 'Pending' FOR UPDATE SKIP LOCKED;  -- Skip locked rows  

BEGIN  
    -- Loop through the pending transactions
    FOR txn IN c_pending_txns LOOP  
        -- Update the status of each transaction to 'Processing'
        UPDATE transactions SET status = 'Processing' WHERE transaction_id = txn.transaction_id;  
    END LOOP;  
    COMMIT;  -- Commit the changes to finalize the transaction updates  
END;技术支持

结语

对于以 Oracle 为核心的岗位，雇主希望候选人既能编写正确的 PL/SQL 代码，又能应对真实挑战并优化数据库性能。建议查阅 Oracle 官方文档 PL/SQL Language Reference，以及我们的 SQL Injection tutorial——通过动态 PL/SQL 进行 SQL 注入是高级面试中常见的话题，文中提供了深入指南与最佳实践。您也可以在 interactive Oracle FreeSQL playground 上，使用 Oracle 的云平台在线运行 PL/SQL 查询。另建议加入 Oracle Community 与 Stack Overflow 论坛，与专家与开发者交流、查找 PL/SQL 相关问题的解决方案。

不过，没有什么比系统化的学习路径更有效。因此，我推荐从我们的 Introduction to Oracle SQL 课程开始，作为从 SQL 基础到 Oracle 高阶编程的最系统路径。如果您想进一步提升数据库相关能力，也推荐学习 Database Design 课程，掌握数据库的创建与管理，并学会为自身需求选择合适的 DBMS。最后，试试我们的 Associate Data Engineer in SQL 职业路径，学习数据工程与数据仓库的基础。

最后，如果您正在面试候选人，同时也希望为团队赋能，请联系 DataCamp for Business 团队。DataCamp 可通过实践项目与定制学习路径为整个团队提升技能。团队可在同一平台获得结构化课程、实操项目与学习进度跟踪。与我们的团队建立联系，提升团队的信心与专业度，就从今天开始。

PL SQL 常见问答

什么是 PL/SQL？它与 SQL 有何不同？

PL/SQL 是 Oracle 开发的过程化语言，支持控制结构与错误处理；而 SQL 是声明式语言，负责数据的检索与操作。

什么是 PL/SQL 包？

为模块化编程与复用而设计的相关过程、函数、变量与游标的集合。

过程与函数有何区别？

过程执行不返回值的动作，而函数返回可在查询中使用的值。

PL/SQL 中的触发器是什么？

在 INSERT、UPDATE、DELETE 操作之前或之后自动执行的动作。

什么是 PL/SQL 的批量处理？

批量处理（BULK COLLECT、FORALL）在处理大数据集时通过减少 PL/SQL 与 SQL 引擎之间的上下文切换来提升性能。

PL/SQL 中 BULK COLLECT 与 FORALL 有何区别？

BULK COLLECT 将多行数据从 SQL 一次性取入 PL/SQL 集合，替代逐行的 SELECT INTO。FORALL 通过一次性将一组值发送给 SQL 引擎来执行批量 DML（插入、更新、删除）。BULK COLLECT 用于读取数据，FORALL 用于写入数据。两者都能减少 SQL 与 PL/SQL 引擎间的上下文切换，这是数据密集型 PL/SQL 代码的主要性能开销来源。

如何在 PL/SQL 中防止 SQL 注入？

PL/SQL 中的主要风险是通过字符串拼接用户输入并交给 EXECUTE IMMEDIATE 执行的动态 SQL。请改用绑定变量：EXECUTE IMMEDIATE 'SELECT ... WHERE id = :id' USING p_id。绑定变量不会被当作 SQL 语法解析，因此注入代码无法执行。若由于语句结构必须动态变化（表名、列名）而需要拼接，请使用白名单验证允许的取值，并采用最小权限的数据库授权，即使发生注入也能最大程度降低影响。

主题

职业服务 SQL