|
|
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
含 Oracle PL/SQL基本结构、变量和常量、条件和循环控制、存储过程和函数、游标、触发器、PL/SQL包、动态SQL、异常处理、最佳实践
🏆 文章目标：关于之前发布的 PostgreSQL 存储过程 相关的文章，网友反馈还是不错的，特此编写这篇文章来帮助那些需要了解Oracle 存储过程，用于快速整改或者上手，也是对大家的回馈。
🍀 一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
✅ 创作者：Jay…
🎉 个人主页： Jay的个人主页
🍁 展望：若本篇讲解内容帮助到您，请帮忙点个赞吧，再点点您的小手关注下，您的支持是我继续写作的最大动力，谢谢🙏 作为回馈，对我博客内容感兴趣的小伙伴可以私聊我，我免费分享 Oracle PL/SQL 的编码规范，大家一起共同进步。Oracle PL/SQL 入门教程
Oracle PL/SQL（Procedural Language/Structured Query Language）是Oracle数据库中的过程式扩展，用于构建复杂的数据库应用程序。本教程将带领您了解PL/SQL的基础知识和一些简单的案例。
PL/SQL是一种结构化的编程语言，允许开发者在Oracle数据库中编写过程、函数、触发器和包。这使得在数据库中执行复杂的逻辑操作成为可能。PL/SQL语言具有高性能、易于调试和维护的优点。
1. PL/SQL 基本结构
PL/SQL 的基本结构包括声明部分、执行部分和异常处理部分。一个典型的 PL/SQL 块如下所示：
基本结构：
DECLARE -- 声明部分 BEGIN -- 执行部分 EXCEPTION -- 异常处理部分 END;DECLARE -- 声明部分 v_name VARCHAR2(100); BEGIN -- 执行部分 SELECT first_name INTO v_name FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name); EXCEPTION -- 异常处理部分 WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data found for employee ID 100.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred.'); END;1.1 声明部分
在声明部分，可以声明变量、常量、游标、类型等。声明的语法如下：
variable_name data_type [NOT NULL] [:= default_value]; constant_name CONSTANT data_type := value;1.2 执行部分
执行部分是 PL/SQL 块的主体，包括 SQL 语句、控制结构、循环等。
1.3 异常处理部分
异常处理部分用于捕获并处理运行时出现的错误。PL/SQL 支持自定义异常，也支持预定义异常。
2. 变量和常量
在PL/SQL中，可以使用
DECLARE部分声明变量和常量。变量用于存储中间值，常量用于存储不可更改的值。2.1 变量
声明变量的语法：
variable_name datatype [NOT NULL] [:= initial_value];DECLARE v_age NUMBER := 30; v_salary NUMBER; BEGIN v_salary := v_age * 1000; DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary); END;2.2 常量
声明常量的语法：
constant_name CONSTANT datatype := value;DECLARE c_tax_rate CONSTANT NUMBER := 0.15; v_income NUMBER := 10000; v_tax NUMBER; BEGIN v_tax := v_income * c_tax_rate; DBMS_OUTPUT.PUT_LINE('Tax: ' || v_tax); END;3. 条件和循环控制
PL/SQL支持多种条件和循环控制结构，例如IF语句、CASE语句、FOR循环和WHILE循环。
3.1 IF语句
IF语句语法：
IF condition THEN -- statements ELSIF condition THEN -- statements -- statements END IF;DECLARE v_age NUMBER := 25; BEGIN IF v_age < 20 THEN DBMS_OUTPUT.PUT_LINE('Teenager'); ELSIF v_age >= 20 AND v_age < 30 THEN DBMS_OUTPUT.PUT_LINE('Young adult'); DBMS_OUTPUT.PUT_LINE('Adult'); END IF; END;3.2 CASE语句
CASE语句语法：
CASE [expression] WHEN value1 THEN -- statements WHEN value2 THEN -- statements [ELSE -- statements] END CASE;DECLARE v_score NUMBER := 85; v_grade CHAR(1); BEGIN WHEN v_score >= 90 THEN v_grade := 'A'; WHEN v_score >= 80 THEN v_grade := 'B'; WHEN v_score >= 70 THEN v_grade := 'C'; v_grade := 'F'; END CASE; DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade); END;3.3 FOR循环
FOR循环语法：
FOR counter IN [REVERSE] lower_bound..upper_bound LOOP -- statements END LOOPDECLARE BEGIN FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE('Iteration: ' || i); END LOOP; END;3.4 WHILE循环
WHILE循环语法：
WHILE condition LOOP -- statements END LOOP;DECLARE v_counter NUMBER := 1; BEGIN WHILE v_counter <= 5 LOOP DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter); v_counter := v_counter + 1; END LOOP; END;3.5 示例
以下示例演示了如何使用PL/SQL计算一个数字的阶乘：
DECLARE v_number NUMBER := 5; v_factorial NUMBER := 1; BEGIN FOR i IN 1..v_number LOOP v_factorial := v_factorial * i; END LOOP; DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_number || ' is ' || v_factorial); END;以下示例演示了如何使用PL/SQL检查一个数字是否为素数：
DECLARE v_number NUMBER := 29; v_is_prime BOOLEAN := TRUE; BEGIN FOR i IN 2..FLOOR(SQRT(v_number)) LOOP IF MOD(v_number, i) = 0 THEN v_is_prime := FALSE; EXIT; END IF; END LOOP; IF v_is_prime THEN DBMS_OUTPUT.PUT_LINE(v_number || ' is a prime number.'); DBMS_OUTPUT.PUT_LINE(v_number || ' is not a prime number.'); END IF; END;计算平均工资
假设我们有一个
employees表，现在要计算所有员工的平均工资，并将结果存储在一个变量中。sqlCopy code DECLARE v_total_salary NUMBER := 0; v_avg_salary NUMBER; v_employee_count NUMBER := 0; BEGIN -- 计算员工总工资 SELECT SUM(salary) INTO v_total_salary FROM employees; -- 计算员工总数 SELECT COUNT(*) INTO v_employee_count FROM employees; -- 计算平均工资 IF v_employee_count > 0 THEN v_avg_salary := v_total_salary / v_employee_count; DBMS_OUTPUT.PUT_LINE('平均工资为：' || TO_CHAR(v_avg_salary)); DBMS_OUTPUT.PUT_LINE('无员工数据'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生错误：' || SQLERRM); END;这个例子展示了 PL/SQL 基本结构和控制结构的用法。首先，我们声明了三个变量：
v_total_salary、v_avg_salary和v_employee_count。然后，在执行部分，我们分别查询员工总工资和员工总数，并将结果存储在相应的变量中。接下来，我们使用 IF-THEN-ELSE 控制结构计算平均工资，并通过DBMS_OUTPUT.PUT_LINE输出结果。最后，我们使用异常处理部分捕获可能出现的错误。4. 存储过程和函数
PL/SQL 允许创建存储过程和函数，它们是可重用的 PL/SQL 代码块，可以从其他PL/SQL代码块、SQL语句或应用程序中调用。存储过程和函数的主要区别在于，函数必须返回一个值，而存储过程不需要。
4.1 存储过程
存储过程是一组SQL和PL/SQL语句的集合，用于执行特定任务。存储过程可以有参数，允许传递输入值和返回输出值。
存储过程的语法：
sqlCopy code CREATE [OR REPLACE] PROCEDURE procedure_name (parameter_list) -- 声明部分 BEGIN -- 执行部分 EXCEPTION -- 异常处理部分 END;创建存储过程的示例：
CREATE OR REPLACE PROCEDURE raise_salary (p_employee_id NUMBER, p_amount NUMBER) AS BEGIN UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_employee_id; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END raise_salary;调用存储过程的示例：
DECLARE v_employee_id NUMBER := 100; v_amount NUMBER := 500; BEGIN raise_salary(v_employee_id, v_amount); DBMS_OUTPUT.PUT_LINE('Salary raised for employee ID: ' || v_employee_id); END;4.2 函数
函数类似于存储过程，但它返回一个值。函数可以在SQL语句、PL/SQL代码块或其他函数中使用。
函数的语法：
sqlCopy code CREATE [OR REPLACE] FUNCTION function_name (parameter_list) RETURN return_type -- 声明部分 BEGIN -- 执行部分 RETURN return_value; EXCEPTION -- 异常处理部分 END;创建函数的示例：
CREATE OR REPLACE FUNCTION get_employee_name (p_employee_id NUMBER) RETURN VARCHAR2 AS v_name VARCHAR2(100); BEGIN SELECT first_name || ' ' || last_name INTO v_name FROM employees WHERE employee_id = p_employee_id; RETURN v_name; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Not found'; END get_employee_name;调用函数的示例：
DECLARE v_employee_id NUMBER := 100; v_employee_name VARCHAR2(100); BEGIN v_employee_name := get_employee_name(v_employee_id); DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END;通过掌握存储过程和函数的使用，您可以将复杂的逻辑封装成模块化的组件，提高代码的可重用性和可维护性。此外，您还可以在SQL语句中直接调用函数，使得查询更加灵活和高效。
4.3. 计算员工的年薪
假设我们要创建一个存储过程，用于增加员工工资，以及一个函数，用于计算员工的年薪。
4.3.1 创建存储过程
sqlCopy code CREATE OR REPLACE PROCEDURE increase_salary ( p_employee_id NUMBER, p_percentage NUMBER BEGIN UPDATE employees SET salary = salary * (1 + p_percentage / 100) WHERE employee_id = p_employee_id; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;4.3.2 创建函数
sqlCopy code CREATE OR REPLACE FUNCTION calculate_annual_salary (p_employee_id NUMBER) RETURN NUMBER v_monthly_salary NUMBER; BEGIN SELECT salary INTO v_monthly_salary FROM employees WHERE employee_id = p_employee_id; RETURN v_monthly_salary * 12; EXCEPTION WHEN OTHERS THEN RETURN NULL; END;在这个例子中，我们创建了一个存储过程
increase_salary和一个函数calculate_annual_salary。存储过程increase_salary接受两个参数：员工 ID 和涨薪百分比，用于更新员工的工资。函数calculate_annual_salary接受一个参数：员工 ID，用于计算员工的年薪。5. 游标
在 PL/SQL 中，游标是用于检索和操作查询结果集的数据结构。游标分为显式游标和隐式游标。隐式游标是系统自动创建和管理的，当使用 SELECT INTO 语句时，系统会自动创建隐式游标。显式游标需要手动定义、打开、检索数据以及关闭。
5.1 创建显式游标
sqlCopy code DECLARE CURSOR cursor_name IS SELECT_statement;5.2 打开游标
sqlCopy code OPEN cursor_name;5.3 获取游标数据
sqlCopy code FETCH cursor_name INTO variable_list;5.4 关闭游标
sqlCopy code CLOSE cursor_name;5.5. 示例：使用游标
假设我们要查询
employees表中的所有员工，并逐行输出员工姓名和工资。sqlCopy code DECLARE CURSOR employee_cursor IS SELECT first_name, last_name, salary FROM employees; v_first_name employees.first_name%TYPE; v_last_name employees.last_name%TYPE; v_salary employees.salary%TYPE; BEGIN OPEN employee_cursor; FETCH employee_cursor INTO v_first_name, v_last_name, v_salary; EXIT WHEN employee_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' - ' || v_salary); END LOOP; CLOSE employee_cursor; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生错误：' || SQLERRM); END;这个例子展示了如何使用显式游标处理查询结果。我们首先声明一个名为
employee_cursor的游标，用于查询员工的姓名和工资。然后，我们逐行获取游标中的数据，并输出结果。最后，我们关闭游标并处理可能出现的异常。6. 触发器
触发器是一种特殊的存储过程，它在特定的事件（如 INSERT、UPDATE、DELETE）发生时自动执行。触发器可以用于维护数据完整性、审计跟踪或其他自动处理任务。
6.1 创建触发器
sqlCopy code CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE [OF column_list] | DELETE} ON table_name [FOR EACH ROW] [WHEN condition] DECLARE -- 声明部分 BEGIN -- 执行部分 EXCEPTION -- 异常处理部分 END;6.2 示例：使用触发器
假设我们要创建一个触发器，用于在更新员工工资时自动记录工资变更历史。
首先，创建一个
salary_changes表，用于存储工资变更记录：CREATE TABLE salary_changes ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, employee_id NUMBER, old_salary NUMBER, new_salary NUMBER, changed_date DATE接下来，创建一个触发器，用于在更新员工工资时自动插入一条记录到
salary_changes表：CREATE OR REPLACE TRIGGER record_salary_change AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_changes (employee_id, old_salary, new_salary, changed_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END;在这个例子中，我们创建了一个名为
record_salary_change的触发器。当employees表的salary字段发生更新时，触发器会自动插入一条工资变更记录到salary_changes表。通过使用触发器，我们可以自动记录工资变更历史，而不需要在每次更新工资时手动插入记录。7. PL/SQL 包
PL/SQL 包是一种用于封装相关的存储过程、函数、类型、变量和常量等的模块。PL/SQL 包有助于代码的模块化、可维护性和重用性。
PL/SQL 包由两部分组成：包规范和包体。包规范声明了包中的对象和子程序，包体则实现了包规范中声明的子程序。
7.1 创建包规范
包规范定义了包中的对象和子程序，用于声明公共变量、常量、类型、异常和子程序签名。包规范不包含子程序的实现。
包规范的语法：
sqlCopy code CREATE [OR REPLACE] PACKAGE package_name -- 声明部分 END;7.2 创建包体
包体包含包规范中声明的子程序的实现以及私有变量、常量和类型的声明。
包体的语法：
sqlCopy code CREATE [OR REPLACE] PACKAGE BODY package_name -- 实现部分 END;7.3 示例
创建包规范：
CREATE OR REPLACE PACKAGE hr_package AS PROCEDURE hire_employee(p_employee_id NUMBER, p_salary NUMBER); FUNCTION get_employee_name(p_employee_id NUMBER) RETURN VARCHAR2; END hr_package;创建包体：
CREATE OR REPLACE PACKAGE BODY hr_package AS PROCEDURE hire_employee(p_employee_id NUMBER, p_salary NUMBER) AS BEGIN INSERT INTO employees (employee_id, salary) VALUES (p_employee_id, p_salary); COMMIT; END hire_employee; FUNCTION get_employee_name(p_employee_id NUMBER) RETURN VARCHAR2 AS v_name VARCHAR2(100); BEGIN SELECT first_name || ' ' || last_name INTO v_name FROM employees WHERE employee_id = p_employee_id; RETURN v_name; END get_employee_name; END hr_package;调用包中的子程序：
DECLARE v_employee_id NUMBER := 101; v_employee_name VARCHAR2(100); BEGIN hr_package.hire_employee(v_employee_id, 5000); v_employee_name := hr_package.get_employee_name(v_employee_id); DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END;通过使用PL/SQL包，您可以将相关的过程、函数和变量组织在一起，提高代码的可读性和可维护性。
8. 动态SQL
在某些情况下，您可能需要在运行时构建和执行SQL语句。PL/SQL支持动态SQL，允许您在代码中创建和执行SQL语句和PL/SQL代码块。
要执行动态SQL，您可以使用
EXECUTE IMMEDIATE语句或DBMS_SQL包。8.1 EXECUTE IMMEDIATE
EXECUTE IMMEDIATE语句用于执行简单的动态SQL语句。它不支持引用游标或绑定多个变量。
EXECUTE IMMEDIATE语法：EXECUTE IMMEDIATE dynamic_string [INTO {variable[, variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, bind_argument]...];DECLARE v_table_name VARCHAR2(30) := 'employees'; v_count NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name INTO v_count; DBMS_OUTPUT.PUT_LINE('Number of rows in ' || v_table_name || ': ' || v_count); END;8.2 DBMS_SQL包
DBMS_SQL包提供了更强大的动态SQL处理功能，允许您处理复杂的动态SQL语句、引用游标和绑定多个变量。DECLARE v_table_name VARCHAR2(30) := 'employees'; v_sql VARCHAR2(200); v_cursor NUMBER; v_count NUMBER; BEGIN v_sql := 'SELECT COUNT(*) FROM ' || v_table_name; v_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_count); DBMS_SQL.EXECUTE(v_cursor); DBMS_SQL.FETCH_ROWS(v_cursor); DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_count); DBMS_SQL.CLOSE_CURSOR(v_cursor); DBMS_OUTPUT.PUT_LINE('Number of rows in ' || v_table_name || ': ' || v_count); END;通过使用动态SQL，您可以在运行时构建和执行SQL语句，提高应用程序的灵活性和可扩展性。
9. 错误处理
PL/SQL提供了一套完善的错误处理机制，允许您捕获异常、处理错误并向用户提供有用的信息。通过使用异常处理器，您可以确保应用程序在遇到错误时仍能正常运行。
异常处理器的语法：
BEGIN -- executable statements EXCEPTION WHEN exception_name THEN -- statements WHEN OTHERS THEN -- statements END;预定义异常示例：
DECLARE v_employee_id NUMBER := 999; v_employee_name VARCHAR2(100); BEGIN SELECT first_name || ' ' || last_name INTO v_employee_name FROM employees WHERE employee_id = v_employee_id; DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ID ' || v_employee_id || ' not found.'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee with ID ' || v_employee_id); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM); END;自定义异常示例：
DECLARE v_employee_id NUMBER := 999; v_employee_name VARCHAR2(100); e_employee_not_found EXCEPTION; BEGIN SELECT first_name || ' ' || last_name INTO v_employee_name FROM employees WHERE employee_id = v_employee_id; IF v_employee_name IS NULL THEN RAISE e_employee_not_found; END IF; DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); EXCEPTION WHEN e_employee_not_found THEN DBMS_OUTPUT.PUT_LINE('Employee ID ' || v_employee_id || ' not found.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' ||SQLERRM); END;通过使用预定义异常和自定义异常，您可以优雅地处理程序中的错误情况，确保应用程序的健壮性和稳定性。
10. PL/SQL 编程最佳实践和技巧
10.1 注释和文档
编写清晰的注释和文档对于编写可维护的代码至关重要。在编写 PL/SQL 代码时，请确保为关键部分编写注释，以解释代码的功能和实现细节。同时，为存储过程、函数和触发器编写文档，描述它们的输入参数、输出结果和作用。
10.2 使用变量和常量
避免在代码中直接使用字面值（如数字或字符串），而是将它们存储在变量或常量中。这样可以提高代码的可读性和可维护性。
10.3 错误处理
确保对所有可能发生的错误进行适当的处理。使用 PL/SQL 的异常处理机制来捕获和处理运行时错误。可以自定义异常，以便在特定情况下引发和处理。
10.4 代码重用
尽量将重复的代码片段封装为存储过程、函数或包。这样可以提高代码的可读性和可维护性，同时减少代码冗余和错误的可能性。
10.5 优化性能
在编写 PL/SQL 代码时，关注性能优化。优化查询语句，避免在循环中执行相同的查询。使用批量操作（如 BULK COLLECT 和 FORALL）来提高数据操作性能。注意避免使用慢速的游标操作，尽量使用基于集合的操作。
11. 结束语
通过学习本教程，你已经掌握了 Oracle PL/SQL 的基本知识和进阶技巧。在实际应用中，你可以根据需求编写更复杂的 PL/SQL 代码来完成各种任务。同时，遵循编程最佳实践，确保编写出高质量、可维护的代码。
Oracle PL/SQL 是一个强大且灵活的工具，可以帮助你更高效地处理数据库操作。不断学习和实践，你将在这个领域取得更多的成果。祝你学习愉快！
