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

简介: 一篇文章带你学会 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 技巧 ~

相关文章
|
1月前
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(上)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
12天前
|
存储 关系型数据库 MySQL
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
19 0
|
14天前
|
存储 SQL Oracle
oracle 存储过程导出sql语句 导出为文件
oracle 存储过程导出sql语句 导出为文件
|
1月前
|
存储 SQL 数据库
MSSQL 存储过程:功能和用法详解
MSSQL 存储过程:功能和用法详解
25 0
|
1月前
|
存储 SQL 数据库
MSSQL存储过程的功能和用法
MSSQL存储过程的功能和用法
28 1
|
1月前
|
存储 SQL Oracle
Oracle存储过程与自定义函数的调用:异同与实战场景
【4月更文挑战第19天】Oracle的存储过程与自定义函数各有特色,存储过程用于封装复杂SQL操作,常在批量处理和数据维护中使用,通过CALL或EXECUTE调用;而自定义函数则用于简单计算和查询,返回单一值,可直接在SQL语句中调用。了解两者异同,如返回值方式、调用方式和应用场景,能提升数据库管理效率。实战场景包括:使用存储过程定期清理过期数据,用自定义函数在查询中动态计算字段值。
|
8天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
8天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
12天前
|
存储 关系型数据库 MySQL
MySql创建带事务操作的存储过程
MySql创建带事务操作的存储过程
|
1天前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)