Oracle PL/SQL编程

## 一、PL/SQL 简介

PL/SQL（Procedural Language/Structured Query Language）是Oracle数据库的过程化扩展语言，结合了SQL的数据操作能力和过程化逻辑控制（如条件判断、循环等）。其主要特点包括：

- **块结构**：代码以块（Block）为单位组织。
- **高效性**：减少网络传输（可一次性执行多个SQL语句）。
- **错误处理**：支持异常处理机制（EXCEPTION）。
- **可移植性**：依赖于Oracle环境，但语法标准统一。

## 二、基本语法

PL/SQL程序的基本单位是**块**，分为以下部分：

```sql
[DECLARE]
    -- 声明部分（可选）：定义变量、常量、游标等。
BEGIN
    -- 执行部分（必需）：主逻辑代码，包含SQL或流程控制语句。
[EXCEPTION]
    -- 异常处理部分（可选）：处理运行时错误。
END;
/
```

**可选参数说明**：

- `DECLARE`：仅用于匿名块或子程序（函数/存储过程）的变量声明。在命名块（如存储过程）中省略，直接以`IS`或`AS`开始声明。
- `EXCEPTION`：非必需，但建议添加以提高健壮性。

**注意**：

- 使用`DBMS_OUTPUT.PUT_LINE`输出前需开启输出开关（在SQL*Plus中执行`SET SERVEROUTPUT ON`）。
- 块末尾的`/`表示执行（在SQL*Plus等工具中需单独一行）。
- 命名块（如存储过程）创建后需调用才能执行：

```sql
BEGIN
  get_emp_salary(100);  -- 调用存储过程示例
END;
/
```

## 三、块类型

#### 1、匿名块

**示例**：计算两个数的和

```sql
DECLARE
    a NUMBER := 10;     -- 声明变量a并赋值
    b NUMBER := 20;
    result NUMBER;
BEGIN
    result := a + b;
    DBMS_OUTPUT.PUT_LINE('结果：' || result);  -- 输出结果
END;
/
```

#### 2、命名块

存储过程、函数、触发器等

**示例**：创建一个查询员工工资的过程

```sql
CREATE OR REPLACE PROCEDURE get_emp_salary (p_emp_id IN NUMBER) 
IS
    v_salary employees.salary%TYPE;  -- 使用%TYPE继承字段类型
BEGIN
    SELECT salary INTO v_salary 
    FROM employees 
    WHERE employee_id = p_emp_id;
    DBMS_OUTPUT.PUT_LINE('工资：' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('员工不存在');
END;
/
```

## 四、变量声明

#### 1、语法

```sql
变量名 [CONSTANT] 数据类型 [NOT NULL] [:= 初始值];
```

#### 2、示例

```sql
DECLARE
    name VARCHAR2(50) := '张三';  -- 字符串变量
    age CONSTANT NUMBER := 30;   -- 常量
    salary NUMBER NOT NULL := 5000; -- 非空变量
```

## 五、流程控制

#### 1、条件判断

```sql
IF 条件 THEN
   语句;
ELSIF 条件 THEN  -- 注意是ELSIF，不是ELSEIF！
   语句;
ELSE
   语句;
END IF;
```

#### 2、LOOP循环

```sql
LOOP
    EXIT WHEN 条件;  -- 满足条件时退出
    -- 循环体
END LOOP;
```

#### 3、FOR循环

```sql
FOR i IN 1..10 LOOP  -- 循环1到10
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
```

## 六、异常处理

#### 1、语法

```sql
EXCEPTION
    WHEN 异常1 THEN 处理语句;
    WHEN OTHERS THEN 处理语句;  -- 捕获其他未处理的异常
```

#### 2、示例

处理除零错误

```sql
DECLARE
    x NUMBER := 10;
    y NUMBER := 0;
    result NUMBER;
BEGIN
    result := x / y;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('错误：除数不能为0');
END;
/
```

## 七、游标

用于逐行处理查询结果集，类似于迭代器。

#### 1、语法

一般在`DECLARE`块中

```sql
CURSOR 游标名 [参数列表] IS SELECT语句;
```

#### 2、显式游标

##### 语法

```sql
DECLARE
    CURSOR 游标名 IS SELECT语句;  -- 声明游标
    变量1 数据类型;
    变量2 数据类型;
BEGIN
    OPEN 游标名;                  -- 打开游标
    LOOP
        FETCH 游标名 INTO 变量列表; -- 提取数据
        EXIT WHEN 游标名%NOTFOUND; -- 退出条件
        -- 处理数据的逻辑
    END LOOP;
    CLOSE 游标名;                 -- 关闭游标
END;
/
```

##### 属性

- `游标名%FOUND`：FETCH成功提取到数据时返回TRUE
- `游标名%NOTFOUND`：FETCH未提取到数据时返回TRUE
- `游标名%ROWCOUNT`：返回已提取的行数
- `游标名%ISOPEN`：游标打开时返回TRUE

##### 示例

**带参数的显式游标**

```sql
DECLARE
    CURSOR dept_emp_cursor (p_dept_id NUMBER, p_min_salary NUMBER) IS 
        SELECT employee_id, first_name, salary 
        FROM employees 
        WHERE department_id = p_dept_id 
          AND salary > p_min_salary;
  
    v_emp_id employees.employee_id%TYPE;
    v_name employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN dept_emp_cursor(50, 3000);  -- 传递参数：部门ID=50，最低工资3000
  
    LOOP
        FETCH dept_emp_cursor INTO v_emp_id, v_name, v_salary;
        EXIT WHEN dept_emp_cursor%NOTFOUND; -- 没有更多数据时退出
      
        DBMS_OUTPUT.PUT_LINE('员工: ' || v_name || ', 工资: ' || v_salary);
    END LOOP;
  
    CLOSE dept_emp_cursor;
END;
/
```

**使用FOR循环简化显式游标**

```sql
DECLARE
    CURSOR emp_cursor IS 
        SELECT employee_id, first_name, salary 
        FROM employees 
        WHERE department_id = 50;
BEGIN
    FOR emp_record IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('员工ID: ' || emp_record.employee_id || 
                           ', 姓名: ' || emp_record.first_name || 
                           ', 工资: ' || emp_record.salary);
    END LOOP;
END;
/
```

#### 3、隐式游标

##### 特点

- Oracle自动创建和管理
- 用于单行查询（SELECT INTO）或DML操作（INSERT/UPDATE/DELETE）
- 不需要显式声明、打开、关闭

##### 属性

- `SQL%FOUND`：如果操作影响至少一行，返回TRUE
- `SQL%NOTFOUND`：如果操作没有影响任何行，返回TRUE
- `SQL%ROWCOUNT`：返回受影响的行数
- `SQL%ISOPEN`：总是返回FALSE（隐式游标自动关闭）

##### 示例

**SELECT INTO语句**

```sql
DECLARE
    v_emp_name employees.first_name%TYPE;
    v_emp_salary employees.salary%TYPE;
BEGIN
    -- 隐式游标：单行查询
    SELECT first_name, salary INTO v_emp_name, v_emp_salary
    FROM employees 
    WHERE employee_id = 100;
  
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_emp_name || ', 工资: ' || v_emp_salary);
  
    -- 检查隐式游标属性
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('查询成功，找到1条记录');
    END IF;
END;
/
```

**DML操作中的隐式游标**

```sql
DECLARE
    v_dept_id NUMBER := 60;
    v_dept_name VARCHAR2(30) := '技术部';
BEGIN
    -- 插入操作，使用隐式游标
    INSERT INTO departments (department_id, department_name)
    VALUES (v_dept_id, v_dept_name);
  
    -- 检查插入结果
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('插入成功，影响行数: ' || SQL%ROWCOUNT);
    END IF;
  
    -- 更新操作
    UPDATE employees 
    SET salary = salary * 1.1 
    WHERE department_id = 50;
  
    DBMS_OUTPUT.PUT_LINE('更新了 ' || SQL%ROWCOUNT || ' 条记录');
END;
/
```

#### 4、异常处理

```sql
DECLARE
    CURSOR emp_cursor IS SELECT * FROM employees;
BEGIN
    FOR emp IN emp_cursor LOOP
        -- 处理逻辑
        NULL;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        IF emp_cursor%ISOPEN THEN
            CLOSE emp_cursor;
        END IF;
        RAISE;
END;
/
```

## 八、包与包体

#### 1、概念

##### 包（Package）

- **定义**：包的**规范（Specification）**，类似于Java中的接口或C++的头文件
- **作用**：声明公共的变量、常量、异常、游标、函数和存储过程的接口
- **特点**：只包含声明，不包含具体实现

##### 包体（Package Body）

- **定义**：包的**实现部分**，包含包规范中声明的所有子程序的具体代码
- **作用**：实现包规范中定义的函数、存储过程等
- **特点**：可以包含私有变量和子程序（仅在包体内可见）

#### 2、创建语法

##### 包（Package）

```sql
CREATE [OR REPLACE] PACKAGE 包名 
IS | AS
    -- 公共变量声明（可选）
    公共变量名 数据类型 [:= 初始值];
  
    -- 公共常量声明（可选）
    常量名 CONSTANT 数据类型 := 值;
  
    -- 异常声明（可选）
    异常名 EXCEPTION;
  
    -- 游标声明（可选）
    CURSOR 游标名 [(参数列表)] RETURN 返回类型;
  
    -- 函数声明（必需有对应的包体实现）
    FUNCTION 函数名 [(参数列表)] RETURN 数据类型;
  
    -- 存储过程声明（必需有对应的包体实现）
    PROCEDURE 过程名 [(参数列表)];
END [包名];
/
```

##### 包体（Package Body）

```sql
CREATE [OR REPLACE] PACKAGE BODY 包名 
IS | AS
    -- 私有变量声明（可选，仅在包体内可见）
    私有变量名 数据类型;
  
    -- 私有子程序声明（可选）
    FUNCTION 私有函数名 RETURN 数据类型;
  
    -- 包规范中声明的子程序的具体实现
    FUNCTION 函数名 [(参数列表)] RETURN 数据类型 
    IS
        -- 局部变量
    BEGIN
        -- 实现代码
        RETURN 值;
    END 函数名;
  
    PROCEDURE 过程名 [(参数列表)] 
    IS
        -- 局部变量
    BEGIN
        -- 实现代码
    END 过程名;
  
    -- 初始化部分（可选）
BEGIN
    -- 包初始化代码，在第一次调用包时执行
END [包名];
/
```

#### 3、示例

##### 包（Package）

```sql
-- 示例：创建员工管理包规范
CREATE OR REPLACE PACKAGE emp_pkg 
IS
    -- 公共常量
    g_max_salary CONSTANT NUMBER := 100000;
  
    -- 公共异常
    emp_not_found EXCEPTION;
  
    -- 函数声明
    FUNCTION get_emp_name (p_emp_id NUMBER) RETURN VARCHAR2;
    FUNCTION calculate_bonus (p_emp_id NUMBER, p_percent NUMBER) RETURN NUMBER;
  
    -- 存储过程声明
    PROCEDURE update_salary (p_emp_id NUMBER, p_new_salary NUMBER);
    PROCEDURE get_emp_details (p_emp_id NUMBER);
  
    -- 游标声明
    CURSOR emp_cursor RETURN employees%ROWTYPE;
END emp_pkg;
/
```

##### 包体（Package Body）

```sql
-- 示例：创建员工管理包体
CREATE OR REPLACE PACKAGE BODY emp_pkg 
IS
    -- 私有变量（仅在包体内可见）
    v_company_name VARCHAR2(100) := 'Oracle公司';
  
    -- 私有函数（仅在包体内可见）
    FUNCTION validate_emp_id (p_emp_id NUMBER) RETURN BOOLEAN 
    IS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count 
        FROM employees 
        WHERE employee_id = p_emp_id;
        RETURN (v_count > 0);
    END validate_emp_id;
  
    -- 实现包规范中的函数
    FUNCTION get_emp_name (p_emp_id NUMBER) RETURN VARCHAR2
    IS
        v_emp_name employees.first_name%TYPE;
    BEGIN
        IF NOT validate_emp_id(p_emp_id) THEN
            RAISE emp_not_found;
        END IF;
      
        SELECT first_name INTO v_emp_name 
        FROM employees 
        WHERE employee_id = p_emp_id;
      
        RETURN v_emp_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE emp_not_found;
    END get_emp_name;
  
    FUNCTION calculate_bonus (p_emp_id NUMBER, p_percent NUMBER) RETURN NUMBER
    IS
        v_salary employees.salary%TYPE;
        v_bonus NUMBER;
    BEGIN
        SELECT salary INTO v_salary 
        FROM employees 
        WHERE employee_id = p_emp_id;
      
        v_bonus := v_salary * p_percent / 100;
        RETURN v_bonus;
    END calculate_bonus;
  
    -- 实现包规范中的存储过程
    PROCEDURE update_salary (p_emp_id NUMBER, p_new_salary NUMBER)
    IS
    BEGIN
        IF p_new_salary > g_max_salary THEN
            RAISE_APPLICATION_ERROR(-20001, '工资超过最大值: ' || g_max_salary);
        END IF;
      
        UPDATE employees 
        SET salary = p_new_salary 
        WHERE employee_id = p_emp_id;
      
        IF SQL%ROWCOUNT = 0 THEN
            RAISE emp_not_found;
        END IF;
      
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('工资更新成功');
    END update_salary;
  
    PROCEDURE get_emp_details (p_emp_id NUMBER)
    IS
        v_emp employees%ROWTYPE;
    BEGIN
        SELECT * INTO v_emp 
        FROM employees 
        WHERE employee_id = p_emp_id;
      
        DBMS_OUTPUT.PUT_LINE('员工ID: ' || v_emp.employee_id);
        DBMS_OUTPUT.PUT_LINE('姓名: ' || v_emp.first_name || ' ' || v_emp.last_name);
        DBMS_OUTPUT.PUT_LINE('工资: ' || v_emp.salary);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('员工不存在');
    END get_emp_details;
  
    -- 实现包规范中的游标
    CURSOR emp_cursor RETURN employees%ROWTYPE 
    IS
        SELECT * FROM employees WHERE department_id = 50;
  
    -- 初始化部分（可选）
BEGIN
    DBMS_OUTPUT.PUT_LINE('员工管理包已初始化 - ' || v_company_name);
END emp_pkg;
/
```

##### 调用包中的子程序

```sql
-- 调用包中的函数
DECLARE
    v_name VARCHAR2(100);
    v_bonus NUMBER;
BEGIN
    v_name := emp_pkg.get_emp_name(100);
    v_bonus := emp_pkg.calculate_bonus(100, 10);
  
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_name || ', 奖金: ' || v_bonus);
END;
/

-- 调用包中的存储过程
BEGIN
    emp_pkg.update_salary(100, 25000);
    emp_pkg.get_emp_details(100);
END;
/
```

##### 包的重载（Overloading）

```sql
-- 包规范：重载函数
CREATE OR REPLACE PACKAGE overload_pkg 
IS
    -- 同名函数，参数不同
    FUNCTION calculate_area (p_radius NUMBER) RETURN NUMBER;
    FUNCTION calculate_area (p_length NUMBER, p_width NUMBER) RETURN NUMBER;
END overload_pkg;
/

-- 包体：实现重载
CREATE OR REPLACE PACKAGE BODY overload_pkg 
IS
    FUNCTION calculate_area (p_radius NUMBER) RETURN NUMBER
    IS
    BEGIN
        RETURN 3.14159 * p_radius * p_radius;  -- 圆面积
    END;
  
    FUNCTION calculate_area (p_length NUMBER, p_width NUMBER) RETURN NUMBER
    IS
    BEGIN
        RETURN p_length * p_width;  -- 矩形面积
    END;
END overload_pkg;
/

-- 调用重载函数
BEGIN
    DBMS_OUTPUT.PUT_LINE('圆面积: ' || overload_pkg.calculate_area(5));
    DBMS_OUTPUT.PUT_LINE('矩形面积: ' || overload_pkg.calculate_area(10, 20));
END;
/
```

#### 4、包的管理操作

```sql
-- 查看包信息
DESC emp_pkg;  -- 查看包规范

-- 查看包源代码
SELECT text FROM user_source WHERE name = 'EMP_PKG' AND type = 'PACKAGE' ORDER BY line;
SELECT text FROM user_source WHERE name = 'EMP_PKG' AND type = 'PACKAGE BODY' ORDER BY line;

-- 删除包（同时删除规范和包体）
DROP PACKAGE emp_pkg;

-- 仅删除包体
DROP PACKAGE BODY emp_pkg;

-- 重新编译包
ALTER PACKAGE emp_pkg COMPILE;
ALTER PACKAGE emp_pkg COMPILE BODY;
```

#### 5、注意事项

**包规范与包体必须匹配**：包体中必须实现规范中声明的所有子程序

**初始化部分**：在包第一次被调用时执行，且只执行一次

**包状态**：包变量在数据库会话期间保持其值

**依赖关系**：修改包规范会使依赖的对象失效，需要重新编译

## 九、存储过程/函数补充

#### 1、存储过程参数

##### 模式

存储过程支持三种参数模式：

| 模式             | 关键字   | 作用               | 说明               |
| ---------------- | -------- | ------------------ | ------------------ |
| **输入参数**     | `IN`     | 将值传递给存储过程 | 默认模式，只读     |
| **输出参数**     | `OUT`    | 从存储过程返回值   | 可写，调用后获取值 |
| **输入输出参数** | `IN OUT` | 既传递值又返回值   | 可读写             |

##### 定义语法

```sql
CREATE OR REPLACE PROCEDURE 过程名 (
    参数1 [IN | OUT | IN OUT] 数据类型 [:= 默认值],
    参数2 [IN | OUT | IN OUT] 数据类型 [:= 默认值],
    ...
)
IS
    -- 局部变量声明
BEGIN
    -- 过程体
END 过程名;
/
```

##### 纯输入参数示例

```sql
CREATE OR REPLACE PROCEDURE add_employee (
    p_emp_id    IN NUMBER,           -- 输入参数：员工ID
    p_first_name IN VARCHAR2,        -- 输入参数：名
    p_last_name  IN VARCHAR2,        -- 输入参数：姓
    p_salary     IN NUMBER := 5000,  -- 输入参数：工资（带默认值）
    p_dept_id    IN NUMBER           -- 输入参数：部门ID
)
IS
BEGIN
    INSERT INTO employees (
        employee_id, first_name, last_name, salary, department_id, hire_date
    ) VALUES (
        p_emp_id, p_first_name, p_last_name, p_salary, p_dept_id, SYSDATE
    );
  
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('员工添加成功: ' || p_first_name || ' ' || p_last_name);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('错误：员工ID已存在');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误：' || SQLERRM);
END add_employee;
/
```

##### 纯输出参数示例

```sql
CREATE OR REPLACE PROCEDURE get_employee_stats (
    p_total_count    OUT NUMBER,     -- 输出参数：员工总数
    p_avg_salary     OUT NUMBER,     -- 输出参数：平均工资
    p_max_salary     OUT NUMBER,     -- 输出参数：最高工资
    p_min_salary     OUT NUMBER      -- 输出参数：最低工资
)
IS
BEGIN
    SELECT 
        COUNT(*),
        AVG(salary),
        MAX(salary),
        MIN(salary)
    INTO 
        p_total_count,
        p_avg_salary,
        p_max_salary,
        p_min_salary
    FROM employees;
  
    DBMS_OUTPUT.PUT_LINE('统计完成');
END get_employee_stats;
/
```

##### 输入输出参数混合

```sql
CREATE OR REPLACE PROCEDURE calculate_salary_adjustment (
    p_emp_id         IN  NUMBER,     -- 输入参数：员工ID
    p_adjust_percent IN  NUMBER,     -- 输入参数：调整百分比
    p_old_salary     OUT NUMBER,     -- 输出参数：原工资
    p_new_salary     OUT NUMBER,     -- 输出参数：新工资
    p_salary_change  OUT NUMBER,     -- 输出参数：工资变化额
    p_emp_name       IN OUT VARCHAR2 -- 输入输出参数：员工姓名
)
IS
    v_original_name VARCHAR2(100);
BEGIN
    -- 获取原工资和姓名
    SELECT salary, first_name || ' ' || last_name 
    INTO p_old_salary, v_original_name
    FROM employees 
    WHERE employee_id = p_emp_id;
  
    -- 如果输入姓名为空，使用数据库中的姓名
    IF p_emp_name IS NULL THEN
        p_emp_name := v_original_name;
    END IF;
  
    -- 计算新工资
    p_new_salary := p_old_salary * (1 + p_adjust_percent / 100);
    p_salary_change := p_new_salary - p_old_salary;
  
    -- 更新数据库
    UPDATE employees 
    SET salary = p_new_salary 
    WHERE employee_id = p_emp_id;
  
    COMMIT;
  
    DBMS_OUTPUT.PUT_LINE('工资调整完成: ' || p_emp_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_old_salary := 0;
        p_new_salary := 0;
        p_salary_change := 0;
        DBMS_OUTPUT.PUT_LINE('错误：员工不存在');
END calculate_salary_adjustment;
/
```

#### 2、存储过程调用

##### 匿名块

```sql
-- 调用纯输入参数的过程
BEGIN
    add_employee(1001, '张', '三', 8000, 50);
END;
/

-- 调用带输出参数的过程
DECLARE
    v_total NUMBER;
    v_avg   NUMBER;
    v_max   NUMBER;
    v_min   NUMBER;
BEGIN
    get_employee_stats(v_total, v_avg, v_max, v_min);
    DBMS_OUTPUT.PUT_LINE('总数: ' || v_total || ', 平均: ' || v_avg);
END;
/

-- 调用混合参数的过程
DECLARE
    v_old_salary NUMBER;
    v_new_salary NUMBER;
    v_change     NUMBER;
    v_emp_name   VARCHAR2(100) := '张三';
BEGIN
    calculate_salary_adjustment(100, 10, v_old_salary, v_new_salary, v_change, v_emp_name);
  
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_emp_name);
    DBMS_OUTPUT.PUT_LINE('原工资: ' || v_old_salary);
    DBMS_OUTPUT.PUT_LINE('新工资: ' || v_new_salary);
    DBMS_OUTPUT.PUT_LINE('调整额: ' || v_change);
END;
/
```

##### EXECUTE命令（SQL\*Plus环境）

```sql
-- 只适用于无输出参数的过程
EXECUTE add_employee(1002, '李', '四', 7000, 60);

-- 或者使用缩写
EXEC add_employee(1003, '王', '五', 6000, 70);
```

##### CALL语句

```sql
-- 调用无输出参数的过程
CALL add_employee(1004, '赵', '六', 5500, 80);

-- 调用有输出参数的过程（需要绑定变量）
VARIABLE v_total NUMBER;
VARIABLE v_avg NUMBER;
CALL get_employee_stats(:v_total, :v_avg, :v_max, :v_min);
PRINT v_total v_avg;
```

#### 3、函数声明

##### 定义语法

```sql
CREATE OR REPLACE FUNCTION 函数名 (
    参数1 [IN] 数据类型 [:= 默认值],
    参数2 [IN] 数据类型 [:= 默认值],
    ...
) RETURN 数据类型
IS
    -- 局部变量声明
    变量名 数据类型;
BEGIN
    -- 函数体
    RETURN 返回值;  -- 必须包含RETURN语句
END 函数名;
/
```

##### 示例

```sql
-- 示例1：基本函数
CREATE OR REPLACE FUNCTION get_employee_name (
    p_emp_id IN NUMBER
) RETURN VARCHAR2
IS
    v_full_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name 
    INTO v_full_name
    FROM employees 
    WHERE employee_id = p_emp_id;
  
    RETURN v_full_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN '员工不存在';
END get_employee_name;
/

-- 示例2：计算函数
CREATE OR REPLACE FUNCTION calculate_bonus (
    p_salary    IN NUMBER,
    p_percentage IN NUMBER DEFAULT 10  -- 默认参数
) RETURN NUMBER
IS
    v_bonus NUMBER;
BEGIN
    v_bonus := p_salary * p_percentage / 100;
    RETURN ROUND(v_bonus, 2);  -- 四舍五入保留2位小数
END calculate_bonus;
/

-- 示例3：复杂逻辑函数
CREATE OR REPLACE FUNCTION get_salary_grade (
    p_salary IN NUMBER
) RETURN VARCHAR2
IS
    v_grade VARCHAR2(20);
BEGIN
    CASE 
        WHEN p_salary < 3000 THEN v_grade := '初级';
        WHEN p_salary BETWEEN 3000 AND 8000 THEN v_grade := '中级';
        WHEN p_salary BETWEEN 8001 AND 15000 THEN v_grade := '高级';
        ELSE v_grade := '资深';
    END CASE;
  
    RETURN v_grade;
END get_salary_grade;
/
```

#### 4、函数调用

##### 匿名块

```sql
-- 基本调用
DECLARE
    v_name  VARCHAR2(100);
    v_bonus NUMBER;
    v_grade VARCHAR2(20);
BEGIN
    -- 调用函数并将返回值赋给变量
    v_name := get_employee_name(100);
    v_bonus := calculate_bonus(5000, 15);
    v_grade := get_salary_grade(7500);
  
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('奖金: ' || v_bonus);
    DBMS_OUTPUT.PUT_LINE('等级: ' || v_grade);
END;
/

-- 直接在输出中使用函数返回值
BEGIN
    DBMS_OUTPUT.PUT_LINE('总经理: ' || get_employee_name(100));
    DBMS_OUTPUT.PUT_LINE('标准奖金: ' || calculate_bonus(8000));
END;
/

-- 在条件判断中使用函数返回值
DECLARE
    v_emp_id NUMBER := 100;
BEGIN
    IF get_salary_grade(
        (SELECT salary FROM employees WHERE employee_id = v_emp_id)
    ) = '高级' THEN
        DBMS_OUTPUT.PUT_LINE('该员工为高级职称');
    ELSE
        DBMS_OUTPUT.PUT_LINE('该员工为其他职称');
    END IF;
END;
/
```

##### SQL语句中调用

```sql
-- 在SELECT语句中调用
SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    get_employee_name(employee_id) as full_name,          -- 调用自定义函数
    calculate_bonus(salary, 12) as annual_bonus,          -- 带参数调用
    get_salary_grade(salary) as salary_level              -- 嵌套调用
FROM employees 
WHERE department_id = 50
ORDER BY salary DESC;

-- 在WHERE条件中使用函数
SELECT 
    employee_id,
    first_name,
    salary
FROM employees 
WHERE get_salary_grade(salary) IN ('高级', '资深')        -- 函数作为条件
AND salary > 5000;

-- 在GROUP BY中使用函数结果
SELECT 
    get_salary_grade(salary) as grade_level,              -- 工资等级
    COUNT(*) as emp_count,                                -- 人数统计
    AVG(salary) as avg_salary                             -- 平均工资
FROM employees 
GROUP BY get_salary_grade(salary)                         -- 按函数结果分组
ORDER BY avg_salary DESC;

-- 在UPDATE语句中使用函数
UPDATE employees 
SET salary = salary + calculate_bonus(salary, 5)          -- 使用函数计算结果
WHERE department_id = 60;

-- 在INSERT语句中使用函数
INSERT INTO salary_log (emp_id, log_date, bonus_amount)
VALUES (100, SYSDATE, calculate_bonus(8000, 8));          -- 函数作为插入值
```

##### DML操作中调用

```sql
-- 在INSERT中使用函数返回值
INSERT INTO employee_audit (
    audit_id, 
    emp_id, 
    action, 
    action_date, 
    details
) VALUES (
    audit_seq.NEXTVAL,
    100,
    '薪资调整',
    SYSDATE,
    '新奖金: ' || calculate_bonus(12000, 10)              -- 函数在字符串拼接中
);

-- 在UPDATE的SET子句中使用函数
UPDATE employees 
SET 
    salary = salary * 1.1,
    last_bonus = calculate_bonus(salary * 1.1, 12)        -- 使用新工资计算奖金
WHERE employee_id = 100;

-- 在DELETE的条件中使用函数
DELETE FROM temp_employees 
WHERE get_salary_grade(salary) = '初级';                   -- 删除初级员工
```

##### 包中调用

```sql
-- 创建包含函数的包
CREATE OR REPLACE PACKAGE hr_utils 
IS
    FUNCTION get_department_name(p_dept_id NUMBER) RETURN VARCHAR2;
    FUNCTION get_manager_name(p_dept_id NUMBER) RETURN VARCHAR2;
END hr_utils;
/

CREATE OR REPLACE PACKAGE BODY hr_utils 
IS
    FUNCTION get_department_name(p_dept_id NUMBER) RETURN VARCHAR2
    IS
        v_dept_name departments.department_name%TYPE;
    BEGIN
        SELECT department_name INTO v_dept_name
        FROM departments WHERE department_id = p_dept_id;
        RETURN v_dept_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN '部门不存在';
    END;
  
    FUNCTION get_manager_name(p_dept_id NUMBER) RETURN VARCHAR2
    IS
        v_mgr_name VARCHAR2(100);
    BEGIN
        SELECT get_employee_name(manager_id) 
        INTO v_mgr_name
        FROM departments 
        WHERE department_id = p_dept_id;
      
        RETURN NVL(v_mgr_name, '无经理');
    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN '部门不存在';
    END;
END hr_utils;
/

-- 调用包中的函数
BEGIN
    DBMS_OUTPUT.PUT_LINE('部门: ' || hr_utils.get_department_name(50));
    DBMS_OUTPUT.PUT_LINE('经理: ' || hr_utils.get_manager_name(50));
END;
/
```

##### 游标中使用

```sql
-- 游标中使用函数返回值
DECLARE
    CURSOR emp_cursor IS
        SELECT 
            employee_id,
            salary,
            calculate_bonus(salary, 20) as potential_bonus,  -- 函数在游标中
            get_salary_grade(salary) as current_grade
        FROM employees 
        WHERE department_id = 50;
  
    v_emp_id employees.employee_id%TYPE;
    v_salary employees.salary%TYPE;
    v_bonus NUMBER;
    v_grade VARCHAR2(20);
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_salary, v_bonus, v_grade;
        EXIT WHEN emp_cursor%NOTFOUND;
      
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || v_emp_id || 
            ' | 工资: ' || v_salary || 
            ' | 潜在奖金: ' || v_bonus ||
            ' | 等级: ' || v_grade
        );
    END LOOP;
    CLOSE emp_cursor;
END;
/
```

##### 函数返回值作为默认值

```sql
-- 使用函数返回值作为默认值
CREATE OR REPLACE FUNCTION get_next_emp_id RETURN NUMBER
IS
    v_max_id NUMBER;
BEGIN
    SELECT NVL(MAX(employee_id), 100) + 1 
    INTO v_max_id 
    FROM employees;
  
    RETURN v_max_id;
END get_next_emp_id;
/

-- 在存储过程中使用函数默认值
CREATE OR REPLACE PROCEDURE add_employee_default (
    p_first_name IN VARCHAR2,
    p_last_name  IN VARCHAR2,
    p_salary     IN NUMBER DEFAULT 5000,
    p_emp_id     IN NUMBER DEFAULT get_next_emp_id()  -- 函数作为默认值
)
IS
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date)
    VALUES (p_emp_id, p_first_name, p_last_name, p_salary, SYSDATE);
  
    DBMS_OUTPUT.PUT_LINE('添加员工成功，ID: ' || p_emp_id);
END add_employee_default;
/

-- 调用时使用函数生成的默认ID
BEGIN
    add_employee_default('张', '三', 6000);  -- ID由函数自动生成
END;
/
```

## 一、PL/SQL 简介

## 二、基本语法

PL/SQL程序的基本单位是**块**，分为以下部分：

**可选参数说明**：

**注意**：

```sql
BEGIN
  get_emp_salary(100);  -- 调用存储过程示例
END;
/
```

## 三、块类型

#### 1、匿名块

**示例**：计算两个数的和

#### 2、命名块

存储过程、函数、触发器等

**示例**：创建一个查询员工工资的过程

## 四、变量声明

#### 1、语法

```sql
变量名 [CONSTANT] 数据类型 [NOT NULL] [:= 初始值];
```

#### 2、示例

```sql
DECLARE
    name VARCHAR2(50) := '张三';  -- 字符串变量
    age CONSTANT NUMBER := 30;   -- 常量
    salary NUMBER NOT NULL := 5000; -- 非空变量
```

## 五、流程控制

#### 1、条件判断

```sql
IF 条件 THEN
   语句;
ELSIF 条件 THEN  -- 注意是ELSIF，不是ELSEIF！
   语句;
ELSE
   语句;
END IF;
```

#### 2、LOOP循环

```sql
LOOP
    EXIT WHEN 条件;  -- 满足条件时退出
    -- 循环体
END LOOP;
```

#### 3、FOR循环

```sql
FOR i IN 1..10 LOOP  -- 循环1到10
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
```

## 六、异常处理

#### 1、语法

```sql
EXCEPTION
    WHEN 异常1 THEN 处理语句;
    WHEN OTHERS THEN 处理语句;  -- 捕获其他未处理的异常
```

#### 2、示例

处理除零错误

## 七、游标

用于逐行处理查询结果集，类似于迭代器。

#### 1、语法

一般在`DECLARE`块中

```sql
CURSOR 游标名 [参数列表] IS SELECT语句;
```

#### 2、显式游标

##### 语法

##### 属性

##### 示例

**带参数的显式游标**

**使用FOR循环简化显式游标**

#### 3、隐式游标

##### 特点

- Oracle自动创建和管理
- 用于单行查询（SELECT INTO）或DML操作（INSERT/UPDATE/DELETE）
- 不需要显式声明、打开、关闭

##### 属性

##### 示例

**SELECT INTO语句**

**DML操作中的隐式游标**

#### 4、异常处理

## 八、包与包体

#### 1、概念

##### 包（Package）

##### 包体（Package Body）

#### 2、创建语法

##### 包（Package）

##### 包体（Package Body）

#### 3、示例

##### 包（Package）

##### 包体（Package Body）

##### 调用包中的子程序

-- 调用包中的存储过程
BEGIN
    emp_pkg.update_salary(100, 25000);
    emp_pkg.get_emp_details(100);
END;
/
```

##### 包的重载（Overloading）

-- 调用重载函数
BEGIN
    DBMS_OUTPUT.PUT_LINE('圆面积: ' || overload_pkg.calculate_area(5));
    DBMS_OUTPUT.PUT_LINE('矩形面积: ' || overload_pkg.calculate_area(10, 20));
END;
/
```

#### 4、包的管理操作

```sql
-- 查看包信息
DESC emp_pkg;  -- 查看包规范

-- 删除包（同时删除规范和包体）
DROP PACKAGE emp_pkg;

-- 仅删除包体
DROP PACKAGE BODY emp_pkg;

-- 重新编译包
ALTER PACKAGE emp_pkg COMPILE;
ALTER PACKAGE emp_pkg COMPILE BODY;
```

#### 5、注意事项

**包规范与包体必须匹配**：包体中必须实现规范中声明的所有子程序

**初始化部分**：在包第一次被调用时执行，且只执行一次

**包状态**：包变量在数据库会话期间保持其值

**依赖关系**：修改包规范会使依赖的对象失效，需要重新编译

## 九、存储过程/函数补充

#### 1、存储过程参数

##### 模式

存储过程支持三种参数模式：

##### 定义语法

##### 纯输入参数示例

##### 纯输出参数示例

##### 输入输出参数混合

#### 2、存储过程调用

##### 匿名块

```sql
-- 调用纯输入参数的过程
BEGIN
    add_employee(1001, '张', '三', 8000, 50);
END;
/

##### EXECUTE命令（SQL\*Plus环境）

```sql
-- 只适用于无输出参数的过程
EXECUTE add_employee(1002, '李', '四', 7000, 60);

-- 或者使用缩写
EXEC add_employee(1003, '王', '五', 6000, 70);
```

##### CALL语句

```sql
-- 调用无输出参数的过程
CALL add_employee(1004, '赵', '六', 5500, 80);

-- 调用有输出参数的过程（需要绑定变量）
VARIABLE v_total NUMBER;
VARIABLE v_avg NUMBER;
CALL get_employee_stats(:v_total, :v_avg, :v_max, :v_min);
PRINT v_total v_avg;
```

#### 3、函数声明

##### 定义语法

##### 示例

#### 4、函数调用

##### 匿名块

-- 直接在输出中使用函数返回值
BEGIN
    DBMS_OUTPUT.PUT_LINE('总经理: ' || get_employee_name(100));
    DBMS_OUTPUT.PUT_LINE('标准奖金: ' || calculate_bonus(8000));
END;
/

##### SQL语句中调用

-- 在UPDATE语句中使用函数
UPDATE employees 
SET salary = salary + calculate_bonus(salary, 5)          -- 使用函数计算结果
WHERE department_id = 60;

-- 在INSERT语句中使用函数
INSERT INTO salary_log (emp_id, log_date, bonus_amount)
VALUES (100, SYSDATE, calculate_bonus(8000, 8));          -- 函数作为插入值
```

##### DML操作中调用

-- 在DELETE的条件中使用函数
DELETE FROM temp_employees 
WHERE get_salary_grade(salary) = '初级';                   -- 删除初级员工
```

##### 包中调用

-- 调用包中的函数
BEGIN
    DBMS_OUTPUT.PUT_LINE('部门: ' || hr_utils.get_department_name(50));
    DBMS_OUTPUT.PUT_LINE('经理: ' || hr_utils.get_manager_name(50));
END;
/
```

##### 游标中使用

##### 函数返回值作为默认值

-- 调用时使用函数生成的默认ID
BEGIN
    add_employee_default('张', '三', 6000);  -- ID由函数自动生成
END;
/
```

最后修改：2025 年 12 月 04 日

👆请简单粗暴地爱我。

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

AlibabaCloud配置中心与限流

DeepSeek R1/V3 使用本地知识库

Day14 - elm后端JDBC版商家部分

Linux常见命令

SpringCloud负载均衡与Feign调用

Oracle PL/SQL编程

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Oracle PL/SQL编程

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款