Oracle_PL/SQL

转载已于 2025-04-29 10:38:30 修改 · 140 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://www.doubao.com/

文章标签：

#oracle #sql #数据库

于 2025-04-26 09:46:31 首次发布

数据开发工具专栏收录该内容

14 篇文章

订阅专栏

PL/SQL是 Oracle 对 SQL 的过程化扩展,它将 SQL 的强大数据操作能力与过程化编程结构相结合.用PLSQL Developer 开发.

1 分类

1.1 基础结构

1.1.1 块结构

PL/SQL 程序由块组成,每个块包含声明部分、执行部分和异常处理三个部分.

DECLARE 
    -- 声明变量、游标等
    /* 多行注释 */
    v_variable_name data_type; 
BEGIN 
    -- 执行 SQL 语句和过程化语句
    SQL_statement; 
EXCEPTION 
    -- 处理异常
    WHEN exception_type THEN 
        exception_handler; 
END;

1.2 数据类型

①标量类型.

②记录类型:用于将相关数据组合在一起,类似其他编程语言中的结构体.

DECLARE 
    TYPE emp_record_type IS RECORD (
        emp_id NUMBER, 
        emp_name VARCHAR2(50), 
        salary NUMBER
    ); 
    v_emp_record emp_record_type; 
BEGIN 
    -- 使用记录
    v_emp_record.emp_id := 1; 
    v_emp_record.emp_name := 'John'; 
    v_emp_record.salary := 5000; 
END;

③关联数组:表类型或嵌套表,用于存储一组相同类型的数据.

DECLARE 
    -- 定义一个名为 num_table_type 的自定义数据类型
    -- 该类型是一个以 PLS_INTEGER 为索引的关联数组，数组元素为 NUMBER 类型
    TYPE num_table_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; 
    -- 声明一个 num_table_type 类型的变量 v_num_table
    v_num_table num_table_type; 
BEGIN 
    -- 给关联数组 v_num_table 中索引为 1 的元素赋值为 10
    v_num_table(1) := 10; 
    v_num_table(2) := 20; 
-- 结束 PL/SQL 块
END;

1.2 流程控制

1.2.1 条件判断

DECLARE 
    v_num NUMBER := 10; 
BEGIN 
    IF v_num > 5 THEN 
        ...; 
    ELSE 
        ...; 
    END IF; 
END;

1.2.2 循环结构

(1) 数值型 FOR 循环

用于按指定的数值范围进行迭代,循环计数器会自动递增或递减.

BEGIN
    -- 正向循环
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('正向循环，当前值: ' || i);
    END LOOP;

    -- 逆向循环
    FOR j IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('逆向循环，当前值: ' || j);
    END LOOP;
END;

(2) 游标型 FOR 循环

用于遍历查询结果集,它会自动处理游标的打开、获取数据、关闭等操作.

FOR record_name IN (SELECT_statement) LOOP
    -- 循环体代码，可通过 record_name 访问查询结果集中的列
END LOOP;

-- 示例
BEGIN
    FOR emp_record IN (SELECT employee_id, first_name, last_name FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE('员工 ID: ' || emp_record.employee_id ||
                             ', 姓名: ' || emp_record.first_name ||' '|| emp_record.last_name);
    END LOOP;
END;

(3) 嵌套表型 FOR 循环

-- 创建嵌套表类型
CREATE OR REPLACE TYPE num_table_type AS TABLE OF NUMBER;


DECLARE
    v_num_table num_table_type := num_table_type(10, 20, 30);
BEGIN
    FOR i IN v_num_table.FIRST..v_num_table.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('元素值: ' || v_num_table(i));
    END LOOP;
END;

(4) WHILE 循环

在每次执行循环体之前会先检查条件,只有当条件为真时才会执行循环体.

WHILE conditions LOOP
    -- 循环体代码
END LOOP;

(5) LOOP 循环

持续执行循环体中的代码,直到遇到 EXIT 语句或满足特定条件后才退出循环.

LOOP
    -- 循环体代码
    EXIT WHEN conditions; -- 退出循环的条件
END LOOP;

1.3 函数与存储过程

1.3.1 函数

用于执行计算并返回一个值.

-- 创建函数
CREATE OR REPLACE FUNCTION function_name (
    param1 IN data_type
) RETURN data_type 
IS 
    -- 声明部分
BEGIN 
    -- 执行部分并返回结果
    RETURN result; 
EXCEPTION 
    -- 异常处理部分
END;

1.3.2 存储过程

用于处理多个表的操作,执行批量任务,或者实现特定的业务流程.可接收参数并执行操作,也可以返回数据.

-- 创建存储过程
CREATE OR REPLACE PROCEDURE procedure_name (
    param1 IN data_type, 
    param2 OUT data_type
) 
IS 
    -- 声明部分
BEGIN 
    -- 执行部分
    SQL_statement; 
    param2 := result; 
EXCEPTION 
    -- 异常处理部分
END; 

-- 示例
CREATE OR REPLACE PROCEDURE get_emp_name (
    p_emp_id IN NUMBER, 
    p_emp_name OUT VARCHAR2
) 
IS 
BEGIN 
    SELECT employee_name INTO p_emp_name 
    FROM employees 
    WHERE employee_id = p_emp_id; 
EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        p_emp_name := 'Employee not found'; 
END;

1.4 触发器

触发器是一种特殊的存储过程,它会在特定数据库事件(如insert、update、delete)发生时自动执行,也可以基于数据库系统事件(数据库启动、关闭)触发.主要用于强制实施业务规则、数据完整性约束,以及自动化一些与数据库操作紧密相关的任务.

1.4.1 触发器构成

①触发事件:导致触发器执行的数据库操作或系统事件,如insert、update、delete数据操作,或者 LOGON、LOGOFF 等数据库会话事件.

②触发时机:分为 BEFORE 触发器和 AFTER 触发器.BEFORE 触发器在事件发生前触发,用于数据验证和预处理;AFTER 触发器在事件发生后触发,用于在数据操作完成后进行一些后续处理.

③触发条件:可以通过 WHEN 子句指定条件,满足条件时触发器才会执.

④触发动作:触发器实际执行的 PL/SQL 代码块.

1.4.2 触发器类型

(1) 语句级触发器

触发时机:在指定操作语句执行前或执行后触发一次.

CREATE OR REPLACE TRIGGER trigger_name 
BEFORE INSERT ON table_name 
BEGIN 
    -- 执行操作
END; 

-- 示例
-- 当对 employees 表执行任何 DELETE 操作时,都会记录一条日志信息
CREATE OR REPLACE TRIGGER log_delete_employees
BEFORE DELETE ON employees
BEGIN
    INSERT INTO operation_logs (log_message, operation_time)
    VALUES ('Delete operation on employees table', SYSDATE);

    DBMS_OUTPUT.PUT_LINE('Insert operation is about to happen'); 
END;

(2) 行级触发器

触发时机:对于受操作影响的每一行数据,都会触发一次.

CREATE OR REPLACE TRIGGER trigger_name 
BEFORE UPDATE ON table_name 
FOR EACH ROW 
WHEN conditions
BEGIN 
    -- 执行操作
END; 

-- 示例
-- 要在员工薪资更新时,记录旧薪资和新薪资
CREATE OR REPLACE TRIGGER log_salary_update
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 1.2)
BEGIN
    INSERT INTO salary_change_logs (employee_id, old_salary, new_salary, change_time)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

2 使用

2.1 处理SQL数据集

(1) 使用游标

逐行获取数据,可灵活执行不同处理逻辑,适配复杂业务场景;但频繁交互导致性能欠佳,处理大数据时资源消耗大.

DECLARE
    -- 定义游标，使用 SELECT 语句查询员工信息
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees;
BEGIN
    -- 使用游标型 FOR 循环遍历游标结果集
    FOR emp_record IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('员工 ID: ' || emp_record.employee_id ||
                             ', 姓名: ' || emp_record.first_name ||' '|| emp_record.last_name);
    END LOOP;
END;

(2) 使用 BULK COLLECT

批量加载数据至内存,减少数据库交互,处理效率高;不过需预先定义对象及嵌套表类型,且难以针对每行定制差异化逻辑.

-- 创建自定义对象类型
CREATE OR REPLACE TYPE employee_obj AS OBJECT (
    employee_id NUMBER,
    first_name VARCHAR2(100),
    last_name VARCHAR2(100)
);
/

-- 创建嵌套表类型
CREATE OR REPLACE TYPE employee_nested_table AS TABLE OF employee_obj;
/

DECLARE
    -- 声明嵌套表变量
    v_employees employee_nested_table;
BEGIN
    -- 使用 SELECT 语句结合 BULK COLLECT 将查询结果存储到嵌套表中
    SELECT employee_obj(employee_id, first_name, last_name)
    BULK COLLECT INTO v_employees
    FROM employees;

    -- 使用游标型 FOR 循环遍历嵌套表
    FOR emp_obj IN TABLE(v_employees) LOOP
        DBMS_OUTPUT.PUT_LINE('员工 ID: ' || emp_obj.employee_id ||
                             ', 姓名: ' || emp_obj.first_name ||' '|| emp_obj.last_name);
    END LOOP;
END;