一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（下）-阿里云开发者社区

一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（上）+https://developer.aliyun.com/article/1492303

4.1 存储过程

存储过程是一组SQL和PL/SQL语句的集合，用于执行特定任务。存储过程可以有参数，允许传递输入值和返回输出值。

存储过程的语法：

sqlCopy code
CREATE [OR REPLACE] PROCEDURE procedure_name (parameter_list)
IS
  -- 声明部分
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
IS
  -- 声明部分
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
)
IS
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
IS
  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;
  LOOP
    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
AS
  -- 声明部分
END;
/

7.2 创建包体

包体包含包规范中声明的子程序的实现以及私有变量、常量和类型的声明。

包体的语法：

sqlCopy code
CREATE [OR REPLACE] PACKAGE BODY package_name
AS
  -- 实现部分
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 是一个强大且灵活的工具，可以帮助你更高效地处理数据库操作。不断学习和实践，你将在这个领域取得更多的成果。祝你学习愉快！

关注公众号：熊猫Jay字节之旅，了解更多 AI 技巧 ~

一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（下）

4.1 存储过程

4.2 函数

4.3. 计算员工的年薪

4.3.1 创建存储过程

4.3.2 创建函数

5. 游标

5.1 创建显式游标

5.2 打开游标

5.3 获取游标数据

5.4 关闭游标

5.5. 示例：使用游标

6. 触发器

6.1 创建触发器

6.2 示例：使用触发器

7. PL/SQL 包

7.1 创建包规范

7.2 创建包体

7.3 示例

8. 动态SQL

8.1 EXECUTE IMMEDIATE

8.2 DBMS_SQL包

9. 错误处理

10. PL/SQL 编程最佳实践和技巧

10.1 注释和文档

10.2 使用变量和常量

10.3 错误处理

10.4 代码重用

10.5 优化性能

11. 结束语

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（下）

4.1 存储过程

4.2 函数

4.3. 计算员工的年薪

4.3.1 创建存储过程

4.3.2 创建函数

5. 游标

5.1 创建显式游标

5.2 打开游标

5.3 获取游标数据

5.4 关闭游标

5.5. 示例：使用游标

6. 触发器

6.1 创建触发器

6.2 示例：使用触发器

7. PL/SQL 包

7.1 创建包规范

7.2 创建包体

7.3 示例

8. 动态SQL

8.1 EXECUTE IMMEDIATE

8.2 DBMS_SQL包

9. 错误处理

10. PL/SQL 编程最佳实践和技巧

10.1 注释和文档

10.2 使用变量和常量

10.3 错误处理

10.4 代码重用

10.5 优化性能

11. 结束语

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像