PL/SQL、存储过程、自定义函数、包、游标

内容来自通义千问搜索，用于随记解答个人心中疑问。

PL/SQL（Procedural Language/SQL）是 Oracle 数据库的一种过程化扩展语言，它结合了 SQL 语句和过程化编程结构，使得开发者可以在数据库中编写复杂的业务逻辑。PL/SQL 支持变量声明、控制结构（如条件语句和循环）、异常处理、游标操作等功能，使其成为一种强大的数据库编程工具。

一、PL/SQL 的主要特点

过程化编程：
支持变量声明、赋值和操作。
支持条件语句（如 IF、CASE）和循环语句（如 LOOP、WHILE、FOR）。
支持子程序（如过程 PROCEDURE 和函数 FUNCTION）。
SQL 集成：
可以在 PL/SQL 代码中嵌入 SQL 语句，如 SELECT、INSERT、UPDATE 和 DELETE。
支持游标（Cursor）来处理查询结果集。
异常处理：
支持异常处理机制，可以捕获和处理运行时错误。
模块化：
支持包（Package），可以将相关的子程序、变量和类型组织在一起，提高代码的可重用性和可维护性。

二、基本语法

1. 声明部分
在 PL/SQL 块中，可以声明变量、常量和游标。

DECLARE
    v_employee_id NUMBER := 100;
    v_employee_name VARCHAR2(100);
    v_salary NUMBER;
    CURSOR c_employees IS
        SELECT id, name, salary FROM employees;
BEGIN
    -- 业务逻辑
END;
/

2. 执行部分
这是 PL/SQL 块的主要部分，包含业务逻辑。

BEGIN
    -- 业务逻辑
    SELECT name, salary INTO v_employee_name, v_salary
    FROM employees
    WHERE id = v_employee_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

3. 异常处理部分
可以捕获和处理运行时错误。

BEGIN
    -- 业务逻辑
    SELECT name, salary INTO v_employee_name, v_salary
    FROM employees
    WHERE id = v_employee_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
/

三、 常见的 PL/SQL 结构

1. 条件语句

BEGIN
    IF v_salary > 50000 THEN
        DBMS_OUTPUT.PUT_LINE('High Salary');
    ELSIF v_salary > 30000 THEN
        DBMS_OUTPUT.PUT_LINE('Medium Salary');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Low Salary');
    END IF;
END;
/

2. 循环语句

BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Number: ' || i);
    END LOOP;
END;
/

3. 游标

DECLARE
    CURSOR c_employees IS
        SELECT id, name, salary FROM employees;
    v_id NUMBER;
    v_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    OPEN c_employees;
    LOOP
        FETCH c_employees INTO v_id, v_name, v_salary;
        EXIT WHEN c_employees%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name || ', Salary: ' || v_salary);
    END LOOP;
    CLOSE c_employees;
END;
/

四、子程序

1. 过程（Procedure）

CREATE OR REPLACE PROCEDURE GetEmployeeById(p_id IN NUMBER) IS
    v_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    SELECT name, salary INTO v_name, v_salary
    FROM employees
    WHERE id = p_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

2. 函数（Function）

CREATE OR REPLACE FUNCTION GetEmployeeNameById(p_id IN NUMBER) RETURN VARCHAR2 IS
    v_name VARCHAR2(100);
BEGIN
    SELECT name INTO v_name
    FROM employees
    WHERE id = p_id;
    
    RETURN v_name;
END;
/

3、包（Package）
包用于组织相关的子程序、变量和类型，提高代码的可重用性和可维护性。

CREATE OR REPLACE PACKAGE Employee_Package IS
    PROCEDURE GetEmployeeById(p_id IN NUMBER);
    FUNCTION GetEmployeeNameById(p_id IN NUMBER) RETURN VARCHAR2;
END Employee_Package;
/

CREATE OR REPLACE PACKAGE BODY Employee_Package IS
    PROCEDURE GetEmployeeById(p_id IN NUMBER) IS
        v_name VARCHAR2(100);
        v_salary NUMBER;
    BEGIN
        SELECT name, salary INTO v_name, v_salary
        FROM employees
        WHERE id = p_id;
        
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
        DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
    END GetEmployeeById;
    
    FUNCTION GetEmployeeNameById(p_id IN NUMBER) RETURN VARCHAR2 IS
        v_name VARCHAR2(100);
    BEGIN
        SELECT name INTO v_name
        FROM employees
        WHERE id = p_id;
        
        RETURN v_name;
    END GetEmployeeNameById;
END Employee_Package;
/

总结

PL/SQL 是 Oracle 数据库中的一种强大工具，它结合了 SQL 语句和过程化编程结构，使得开发者可以在数据库中编写复杂的业务逻辑。通过使用 PL/SQL，可以提高应用程序的性能和可维护性，减少网络通信开销，并确保数据的一致性和完整性。

PL/SQL对象

在 PL/SQL（Procedural Language/SQL）中，对象是一种高级的数据结构，可以用来封装数据和操作这些数据的方法。PL/SQL 对象类型（Object Types）允许你定义复杂的、面向对象的数据结构，这些结构可以包含属性（属性类似于类的成员变量）和方法（方法类似于类的成员函数）。这些对象类型可以在 PL/SQL 程序中使用，也可以在数据库表中作为列的数据类型。

PL/SQL 对象类型的定义

1. 定义对象类型

使用 CREATE TYPE 语句来定义一个对象类型。对象类型可以包含属性和方法。

CREATE TYPE EmployeeType AS OBJECT (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER,
    MEMBER PROCEDURE display_details
);
/

在这个例子中，EmployeeType 是一个对象类型，包含三个属性：id、name 和 salary，以及一个成员方法 display_details。

2. 定义方法

方法是在对象类型中定义的函数或过程。可以使用 MEMBER 关键字来定义成员方法，使用 STATIC 关键字来定义静态方法。

CREATE TYPE EmployeeType AS OBJECT (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER,
    MEMBER PROCEDURE display_details
)
/

CREATE TYPE BODY EmployeeType AS
    MEMBER PROCEDURE display_details IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('ID: ' || SELF.id);
        DBMS_OUTPUT.PUT_LINE('Name: ' || SELF.name);
        DBMS_OUTPUT.PUT_LINE('Salary: ' || SELF.salary);
    END display_details;
END;
/

在这个例子中，display_details 方法用于显示员工的详细信息。SELF 是一个隐式的参数，代表当前对象实例。

使用对象类型

1. 在 PL/SQL 程序中使用对象类型

可以在 PL/SQL 程序中创建对象类型的实例，并调用其方法。

DECLARE
    emp EmployeeType;
BEGIN
    -- 创建对象实例
    emp := EmployeeType(1, 'John Doe', 50000);
    
    -- 调用方法
    emp.display_details();
END;
/

2. 在数据库表中使用对象类型

可以在数据库表中使用对象类型作为列的数据类型。

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_details EmployeeType
);
/

在这个例子中，employees 表有一个 emp_details 列，其数据类型是 EmployeeType。

插入和查询对象类型数据

1. 插入数据

可以使用 INSERT 语句将对象类型的实例插入到表中。

INSERT INTO employees (emp_id, emp_details)
VALUES (1, EmployeeType(1, 'John Doe', 50000));
/

2. 查询数据

可以使用 SELECT 语句查询表中的对象类型数据。

SELECT emp_id, emp_details.id, emp_details.name, emp_details.salary
FROM employees;
/

高级特性

1. 继承

对象类型支持继承，可以使用 UNDER 关键字来定义子类型。

CREATE TYPE ManagerType UNDER EmployeeType (
    department VARCHAR2(100),
    MEMBER PROCEDURE display_department
)
/

CREATE TYPE BODY ManagerType AS
    MEMBER PROCEDURE display_department IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Department: ' || SELF.department);
    END display_department;
END;
/

在这个例子中，ManagerType 是 EmployeeType 的子类型，新增了一个 department 属性和一个 display_department 方法。

2. 集合类型

可以定义集合类型（如表类型和嵌套表类型），用于存储多个对象类型的实例。

CREATE TYPE EmployeeTableType AS TABLE OF EmployeeType;
/

DECLARE
    emp_list EmployeeTableType := EmployeeTableType();
BEGIN
    -- 向集合中添加对象实例
    emp_list.EXTEND;
    emp_list(1) := EmployeeType(1, 'John Doe', 50000);
    
    emp_list.EXTEND;
    emp_list(2) := EmployeeType(2, 'Jane Smith', 60000);
    
    -- 遍历集合并调用方法
    FOR i IN 1..emp_list.COUNT LOOP
        emp_list(i).display_details;
    END LOOP;
END;
/

总结

PL/SQL 对象类型提供了一种强大的机制，用于在 Oracle 数据库中定义和操作复杂的数据结构。通过使用对象类型，可以封装数据和操作数据的方法，提高代码的可重用性和可维护性。对象类型支持继承和集合类型，使得数据建模更加灵活和强大。