Oracle PL/SQL开发

PL/SQL（Procedural Language/Structured Query Language）是Oracle数据库的过程化扩展语言，它结合了SQL的强大查询能力和过程化语言的编程能力。PL/SQL允许开发人员编写存储过程、函数、触发器和包，实现复杂的业务逻辑和数据处理。

PL/SQL概述

什么是PL/SQL？

PL/SQL是Oracle数据库的专有语言，它扩展了标准SQL，添加了过程化编程结构，如变量、常量、控制结构、异常处理等。PL/SQL代码可以存储在数据库中，供应用程序调用。

PL/SQL的优势

• 高性能：PL/SQL代码在数据库服务器上执行，减少了网络传输开销
• 安全性：PL/SQL代码存储在数据库中，可以通过权限控制访问
• 可维护性：PL/SQL代码集中存储，便于修改和维护
• 可靠性：PL/SQL提供了强大的异常处理机制
• 功能强大：支持复杂的数据处理和业务逻辑
• 可移植性：PL/SQL代码可以在不同的Oracle数据库版本之间移植

PL/SQL应用场景

• 实现复杂的业务逻辑
• 批量数据处理
• 数据验证和转换
• 数据库触发器
• 存储过程和函数
• 数据库包

PL/SQL基础语法

PL/SQL块结构

PL/SQL程序由一个或多个块组成，每个块包含以下部分：

DECLARE
  -- 声明部分：变量、常量、游标、类型定义等
  variable_name datatype [NOT NULL] [:= default_value];
BEGIN
  -- 执行部分：PL/SQL语句和SQL语句
  statement;
EXCEPTION
  -- 异常处理部分：处理执行过程中的异常
  WHEN exception_name THEN
    statement;
  WHEN OTHERS THEN
    statement;
END;
/

简单示例

-- 输出Hello, World!
DECLARE
  v_message VARCHAR2(50) := 'Hello, World!';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_message);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

数据类型

标量数据类型

数据类型	描述	示例
VARCHAR2(size)	可变长度字符	v_name VARCHAR2(50);
CHAR(size)	固定长度字符	v_gender CHAR(1);
NUMBER(p,s)	数值类型	v_salary NUMBER(10,2);
INTEGER	整数类型	v_age INTEGER;
DATE	日期类型	v_hire_date DATE;
TIMESTAMP	时间戳类型	v_created_at TIMESTAMP;
BOOLEAN	布尔类型	v_active BOOLEAN := TRUE;
BINARY_INTEGER	二进制整数	v_counter BINARY_INTEGER := 0;

复合数据类型

1. RECORD类型

-- 定义RECORD类型
type employee_record is record (
  employee_id number(6),
  first_name varchar2(50),
  last_name varchar2(50),
  salary number(10,2),
  hire_date date
);

-- 声明RECORD变量
v_employee employee_record;

2. TABLE类型

-- 定义TABLE类型
type employee_table is table of employee_record index by binary_integer;

-- 声明TABLE变量
v_employee_table employee_table;

3. VARRAY类型

-- 定义VARRAY类型
type phone_varray is varray(5) of varchar2(20);

-- 声明VARRAY变量
v_phone_list phone_varray := phone_varray();

引用数据类型

1. REF CURSOR

-- 定义REF CURSOR类型
type employee_cursor is ref cursor return employees%rowtype;

-- 声明REF CURSOR变量
v_employee_cursor employee_cursor;

2. %TYPE和%ROWTYPE

-- 使用%TYPE获取列的数据类型
v_employee_id employees.employee_id%type;
v_salary employees.salary%type;

-- 使用%ROWTYPE获取表的行类型
v_employee employees%rowtype;

变量和常量

变量声明

DECLARE
  v_name VARCHAR2(50);
  v_age INTEGER := 30;
  v_salary NUMBER(10,2) NOT NULL := 5000.00;
  v_hire_date DATE := SYSDATE;
  v_active BOOLEAN := TRUE;
BEGIN
  -- 变量赋值
  v_name := 'John Doe';
  v_age := v_age + 1;
  v_salary := v_salary * 1.1;
  
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
  DBMS_OUTPUT.PUT_LINE('Age: ' || v_age);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

常量声明

DECLARE
  c_pi CONSTANT NUMBER := 3.14159;
  c_max_salary CONSTANT NUMBER(10,2) := 100000.00;
BEGIN
  DBMS_OUTPUT.PUT_LINE('PI: ' || c_pi);
  DBMS_OUTPUT.PUT_LINE('Max Salary: ' || c_max_salary);
END;
/

控制结构

条件语句

1. IF-THEN-ELSE语句

DECLARE
  v_salary NUMBER(10,2) := 6000.00;
BEGIN
  IF v_salary < 5000 THEN
    DBMS_OUTPUT.PUT_LINE('Low Salary');
  ELSIF v_salary BETWEEN 5000 AND 10000 THEN
    DBMS_OUTPUT.PUT_LINE('Medium Salary');
  ELSE
    DBMS_OUTPUT.PUT_LINE('High Salary');
  END IF;
END;
/

2. CASE语句

DECLARE
  v_dept_id NUMBER(4) := 60;
BEGIN
  CASE v_dept_id
    WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('Administration');
    WHEN 20 THEN DBMS_OUTPUT.PUT_LINE('Marketing');
    WHEN 30 THEN DBMS_OUTPUT.PUT_LINE('Purchasing');
    WHEN 40 THEN DBMS_OUTPUT.PUT_LINE('Human Resources');
    WHEN 50 THEN DBMS_OUTPUT.PUT_LINE('Shipping');
    WHEN 60 THEN DBMS_OUTPUT.PUT_LINE('IT');
    ELSE DBMS_OUTPUT.PUT_LINE('Other Department');
  END CASE;
END;
/

循环语句

1. LOOP循环

DECLARE
  v_counter INTEGER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 5;
  END LOOP;
END;
/

2. WHILE循环

DECLARE
  v_counter INTEGER := 1;
BEGIN
  WHILE v_counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;
END;
/

3. FOR循环

DECLARE
  v_sum INTEGER := 0;
BEGIN
  FOR i IN 1..10 LOOP
    v_sum := v_sum + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Sum: ' || v_sum);
  
  -- 反向循环
  FOR i IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Reverse Counter: ' || i);
  END LOOP;
END;
/

游标

游标是用于处理查询结果集的PL/SQL结构，它允许逐行处理查询结果。

隐式游标

Oracle为每个SQL语句自动创建隐式游标，用于处理单行结果。

DECLARE
  v_employee employees%rowtype;
BEGIN
  -- 隐式游标
  SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_employee.first_name || ' ' || v_employee.last_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Too many employees found.');
END;
/

显式游标

显式游标由开发人员显式声明和管理，用于处理多行结果。

DECLARE
  -- 声明游标
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = 60
    ORDER BY salary DESC;
  
  -- 声明变量
  v_employee_id employees.employee_id%type;
  v_first_name employees.first_name%type;
  v_last_name employees.last_name%type;
  v_salary employees.salary%type;
BEGIN
  -- 打开游标
  OPEN emp_cursor;
  
  -- 循环获取游标数据
  LOOP
    FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
  END LOOP;
  
  -- 关闭游标
  CLOSE emp_cursor;
END;
/

FOR循环游标

FOR循环游标是一种简化的显式游标，它自动处理游标的打开、获取和关闭。

DECLARE
  -- 声明游标
  CURSOR emp_cursor (p_dept_id IN NUMBER) IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id
    ORDER BY salary DESC;
BEGIN
  -- FOR循环游标
  FOR emp_rec IN emp_cursor(60) LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name || ', Salary: ' || emp_rec.salary);
  END LOOP;
END;
/

存储过程

存储过程是存储在数据库中的PL/SQL块，它可以接受参数，执行特定的业务逻辑，并返回结果。

创建存储过程

-- 创建存储过程
CREATE OR REPLACE PROCEDURE get_employee_info(
  p_employee_id IN NUMBER,
  p_first_name OUT VARCHAR2,
  p_last_name OUT VARCHAR2,
  p_salary OUT NUMBER
) AS
BEGIN
  SELECT first_name, last_name, salary
  INTO p_first_name, p_last_name, p_salary
  FROM employees
  WHERE employee_id = p_employee_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_first_name := NULL;
    p_last_name := NULL;
    p_salary := NULL;
  WHEN OTHERS THEN
    RAISE;
END get_employee_info;
/

调用存储过程

DECLARE
  v_first_name VARCHAR2(50);
  v_last_name VARCHAR2(50);
  v_salary NUMBER(10,2);
BEGIN
  -- 调用存储过程
  get_employee_info(100, v_first_name, v_last_name, v_salary);
  
  DBMS_OUTPUT.PUT_LINE('Employee Info: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
END;
/

带默认值的存储过程

-- 创建带默认值的存储过程
CREATE OR REPLACE PROCEDURE update_employee_salary(
  p_employee_id IN NUMBER,
  p_percentage IN NUMBER DEFAULT 10
) AS
BEGIN
  UPDATE employees
  SET salary = salary * (1 + p_percentage / 100)
  WHERE employee_id = p_employee_id;
  
  DBMS_OUTPUT.PUT_LINE('Salary updated successfully for employee ' || p_employee_id);
END update_employee_salary;
/

-- 调用带默认值的存储过程
EXEC update_employee_salary(100); -- 使用默认值10%
EXEC update_employee_salary(100, 15); -- 使用指定值15%

函数

函数是存储在数据库中的PL/SQL块，它可以接受参数，执行特定的业务逻辑，并返回一个值。

创建函数

-- 创建函数
CREATE OR REPLACE FUNCTION calculate_bonus(
  p_salary IN NUMBER,
  p_percentage IN NUMBER DEFAULT 10
) RETURN NUMBER AS
  v_bonus NUMBER;
BEGIN
  v_bonus := p_salary * p_percentage / 100;
  RETURN v_bonus;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END calculate_bonus;
/

调用函数

DECLARE
  v_salary NUMBER := 6000.00;
  v_bonus NUMBER;
BEGIN
  -- 调用函数
  v_bonus := calculate_bonus(v_salary);
  DBMS_OUTPUT.PUT_LINE('Bonus (10%): ' || v_bonus);
  
  v_bonus := calculate_bonus(v_salary, 15);
  DBMS_OUTPUT.PUT_LINE('Bonus (15%): ' || v_bonus);
  
  -- 在SQL语句中调用函数
  SELECT employee_id, first_name, last_name, calculate_bonus(salary) AS bonus
  INTO :emp_id, :first_name, :last_name, :bonus
  FROM employees
  WHERE employee_id = 100;
END;
/

触发器

触发器是一种特殊的存储过程，它在特定的事件发生时自动执行。

触发器类型

触发器类型	触发时机	触发事件
BEFORE	事件发生前	INSERT, UPDATE, DELETE
AFTER	事件发生后	INSERT, UPDATE, DELETE
INSTEAD OF	替代事件	INSERT, UPDATE, DELETE（仅用于视图）

创建触发器

-- 创建表级触发器
CREATE OR REPLACE TRIGGER trg_emp_salary_check
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  -- 检查薪资是否为正数
  IF :NEW.salary <= 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary must be positive.');
  END IF;
  
  -- 检查薪资增长是否超过50%
  IF UPDATING('salary') THEN
    IF :NEW.salary > :OLD.salary * 1.5 THEN
      RAISE_APPLICATION_ERROR(-20002, 'Salary increase cannot exceed 50%.');
    END IF;
  END IF;
END trg_emp_salary_check;
/

语句级触发器

-- 创建语句级触发器
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
DECLARE
  v_operation VARCHAR2(10);
  v_count INTEGER;
BEGIN
  IF INSERTING THEN
    v_operation := 'INSERT';
    SELECT COUNT(*) INTO v_count FROM employees;
  ELSIF UPDATING THEN
    v_operation := 'UPDATE';
    v_count := SQL%ROWCOUNT;
  ELSIF DELETING THEN
    v_operation := 'DELETE';
    v_count := SQL%ROWCOUNT;
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('Operation: ' || v_operation || ', Rows affected: ' || v_count);
END trg_emp_audit;
/

异常处理

异常处理是PL/SQL中处理运行时错误的机制，它允许开发人员捕获和处理异常，防止程序崩溃。

预定义异常

Oracle提供了一些预定义的异常，如：

• NO_DATA_FOUND：查询没有返回数据
• TOO_MANY_ROWS：查询返回多行数据
• ZERO_DIVIDE：除以零
• INVALID_CURSOR：无效的游标操作
• DUP_VAL_ON_INDEX：违反唯一约束

自定义异常

开发人员可以定义自己的异常，用于处理特定的业务逻辑错误。

DECLARE
  -- 定义自定义异常
  e_invalid_salary EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);
  
  v_salary NUMBER := -1000;
BEGIN
  IF v_salary <= 0 THEN
    RAISE e_invalid_salary;
  END IF;
EXCEPTION
  WHEN e_invalid_salary THEN
    DBMS_OUTPUT.PUT_LINE('Error: Salary must be positive.');
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Error: Division by zero.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM || ' (Code: ' || SQLCODE || ')');
END;
/

包

包是一种PL/SQL结构，它将相关的存储过程、函数、变量、常量和游标组织在一起，便于管理和重用。

创建包规范

-- 创建包规范
CREATE OR REPLACE PACKAGE employee_pkg AS
  -- 常量定义
  c_max_salary CONSTANT NUMBER := 100000;
  
  -- 类型定义
  TYPE employee_record IS RECORD (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER,
    hire_date DATE
  );
  
  TYPE employee_table IS TABLE OF employee_record;
  
  -- 存储过程声明
  PROCEDURE get_employee_list(
    p_dept_id IN NUMBER,
    p_employee_list OUT employee_table
  );
  
  PROCEDURE update_employee_salary(
    p_employee_id IN NUMBER,
    p_percentage IN NUMBER DEFAULT 10
  );
  
  -- 函数声明
  FUNCTION calculate_bonus(
    p_salary IN NUMBER,
    p_percentage IN NUMBER DEFAULT 10
  ) RETURN NUMBER;
  
  FUNCTION get_employee_count(
    p_dept_id IN NUMBER
  ) RETURN NUMBER;
  
END employee_pkg;
/

创建包体

-- 创建包体
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
  -- 存储过程实现
  PROCEDURE get_employee_list(
    p_dept_id IN NUMBER,
    p_employee_list OUT employee_table
  ) AS
    v_index INTEGER := 0;
  BEGIN
    p_employee_list := employee_table();
    
    FOR emp_rec IN (
      SELECT employee_id, first_name, last_name, salary, hire_date
      FROM employees
      WHERE department_id = p_dept_id
      ORDER BY last_name
    ) LOOP
      v_index := v_index + 1;
      p_employee_list.EXTEND;
      p_employee_list(v_index).employee_id := emp_rec.employee_id;
      p_employee_list(v_index).first_name := emp_rec.first_name;
      p_employee_list(v_index).last_name := emp_rec.last_name;
      p_employee_list(v_index).salary := emp_rec.salary;
      p_employee_list(v_index).hire_date := emp_rec.hire_date;
    END LOOP;
  END get_employee_list;
  
  PROCEDURE update_employee_salary(
    p_employee_id IN NUMBER,
    p_percentage IN NUMBER DEFAULT 10
  ) AS
  BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percentage / 100)
    WHERE employee_id = p_employee_id;
    
    IF SQL%ROWCOUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Employee not found.');
    END IF;
  END update_employee_salary;
  
  -- 函数实现
  FUNCTION calculate_bonus(
    p_salary IN NUMBER,
    p_percentage IN NUMBER DEFAULT 10
  ) RETURN NUMBER AS
  BEGIN
    RETURN p_salary * p_percentage / 100;
  END calculate_bonus;
  
  FUNCTION get_employee_count(
    p_dept_id IN NUMBER
  ) RETURN NUMBER AS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees
    WHERE department_id = p_dept_id;
    
    RETURN v_count;
  END get_employee_count;
  
END employee_pkg;
/

调用包中的存储过程和函数

DECLARE
  v_employee_list employee_pkg.employee_table;
  v_employee_count NUMBER;
  v_bonus NUMBER;
BEGIN
  -- 调用包中的存储过程
  employee_pkg.get_employee_list(60, v_employee_list);
  
  -- 输出结果
  FOR i IN 1..v_employee_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_list(i).first_name || ' ' || v_employee_list(i).last_name);
  END LOOP;
  
  -- 调用包中的函数
  v_employee_count := employee_pkg.get_employee_count(60);
  DBMS_OUTPUT.PUT_LINE('Employee count in department 60: ' || v_employee_count);
  
  v_bonus := employee_pkg.calculate_bonus(6000, 15);
  DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
/

PL/SQL开发最佳实践

1. 命名规范

• 使用有意义的名称，避免缩写
• 变量名以v_开头（如v_employee_id）
• 常量名以c_开头（如c_max_salary）
• 游标名以cur_开头（如cur_employee）
• 存储过程和函数名使用动词开头（如get_employee_list）
• 包名以_pkg结尾（如employee_pkg）
• 触发器名以trg_开头（如trg_emp_salary_check）

2. 代码结构

• 使用缩进提高代码可读性
• 每个PL/SQL块使用BEGIN和END标记
• 异常处理部分放在块的末尾
• 注释代码的功能和复杂逻辑

3. 性能优化

• 使用BULK COLLECT批量获取数据
• 使用FORALL批量执行DML操作
• 避免在循环中执行SQL语句
• 使用绑定变量，避免硬解析
• 合理使用索引

4. 错误处理

• 捕获所有异常，避免未处理的异常
• 使用有意义的错误消息
• 记录错误日志
• 避免使用WHEN OTHERS THEN NULL;

5. 安全性

• 限制PL/SQL代码的权限
• 避免使用动态SQL，或使用绑定变量
• 验证所有输入参数
• 避免在PL/SQL代码中存储敏感信息

版本差异

Oracle 12c

• 支持PL/SQL函数结果缓存
• 支持PL/SQL本机编译
• 增强了PL/SQL调试功能

Oracle 18c

• 支持PL/SQL条件编译
• 增强了PL/SQL包的性能
• 支持PL/SQL代码的自动优化

Oracle 19c

• 长期支持版本
• 增强了PL/SQL的安全性
• 支持PL/SQL代码的并行执行

Oracle 21c

• 支持PL/SQL代码的JSON输出
• 增强了PL/SQL的异常处理
• 支持PL/SQL代码的云原生部署

常见问题（FAQ）

Q: 存储过程和函数有什么区别？

A: 存储过程可以返回多个值（通过OUT参数），不返回函数值；函数只能返回一个值，且可以在SQL语句中调用。

Q: 触发器和存储过程有什么区别？

A: 触发器在特定事件发生时自动执行，不需要显式调用；存储过程需要显式调用。

Q: 包和存储过程有什么区别？

A: 包是一组相关的存储过程、函数、变量和常量的集合，便于管理和重用；存储过程是独立的PL/SQL块。

Q: 如何调试PL/SQL代码？

A: 可以使用Oracle SQL Developer或PL/SQL Developer等工具进行调试，也可以使用DBMS_OUTPUT.PUT_LINE输出调试信息，或使用DBMS_DEBUG包进行调试。

Q: 如何优化PL/SQL代码的性能？

A: 可以通过以下方式优化：

• 使用BULK COLLECT和FORALL批量操作
• 避免在循环中执行SQL语句
• 使用绑定变量
• 合理使用索引
• 优化SQL语句

Q: 如何处理PL/SQL中的异常？

A: 使用EXCEPTION块捕获和处理异常，可以使用预定义异常或自定义异常，避免未处理的异常导致程序崩溃。

总结

PL/SQL是Oracle数据库的强大编程语言，它结合了SQL的查询能力和过程化语言的编程能力。PL/SQL允许开发人员编写存储过程、函数、触发器和包，实现复杂的业务逻辑和数据处理。

在实际生产环境中，PL/SQL广泛应用于：

• 实现复杂的业务逻辑
• 批量数据处理
• 数据验证和转换
• 数据库触发器
• 数据库包

开发人员应该遵循PL/SQL最佳实践，包括命名规范、代码结构、性能优化、错误处理和安全性等方面，提高PL/SQL代码的质量和可维护性。

随着Oracle版本的更新，PL/SQL功能不断增强，如函数结果缓存、本机编译、条件编译等，开发人员应关注新版本的特性，提高开发效率和性能。