Oracle PL/SQL 编程入门：第二十章 函数 Functions

最新推荐文章于 2025-07-03 16:55:53 发布
原创 最新推荐文章于 2025-07-03 16:55:53 发布 · 244 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#Functions #oracle #数据库 #pl sql #存储过程 #函数

欢迎来到 Oracle PL/SQL 编程入门 的第二十章！在这一章中，我们将深入探讨 函数（Functions）。通过学习什么是函数、如何建立和使用函数以及如何优化函数执行，你将能够编写更加高效和可重用的数据库操作代码。此外，我们还会介绍一些注意事项，并通过实际例子展示它们的用法。准备好迎接新的挑战了吗？让我们开始吧！

第一节：函数

1.1 什么是函数

函数（Function） 是一种特殊的存储过程，它返回一个值。函数可以接受输入参数（IN 参数），并且必须有一个返回值。函数通常用于计算或转换数据，并且可以在 SQL 语句中直接调用。

小贴士：函数就像是一个“魔法计算器”

函数就像是给你的程序提供了一个“魔法计算器”，你可以把一系列复杂的计算封装在这个计算器里，然后只需要调用这个计算器的名字就能得到结果。合理使用可以让程序更加简洁和高效。

第二节：语法与建立函数

2.1 语法

CREATE [OR REPLACE] FUNCTION function_name (
  parameter1 IN datatype,
  parameter2 IN datatype,
  ...
) RETURN return_datatype
IS|AS
BEGIN
  -- 函数体
  RETURN result;
END;
/

小贴士：RETURN 关键字让函数有输出

RETURN 关键字就像是给你的函数提供了一个“出口通道”，让它可以把计算结果返回给你。合理使用可以让函数有明确的输出结果。

2.2 建立存储函数

示例代码：

CREATE OR REPLACE FUNCTION get_employee_count (
  p_department_id IN departments.department_id%TYPE
) RETURN NUMBER
IS
  v_count NUMBER;
BEGIN
  -- 查询部门中的员工数量
  SELECT COUNT(*) INTO v_count
  FROM employees
  WHERE department_id = p_department_id;

  -- 返回查询结果
  RETURN v_count;
END;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 创建一个名为 get_employee_count 的函数，该函数返回指定部门中的员工数量。

小贴士：命名清晰让维护更容易

命名清晰就像是给你的程序提供了一个“标签系统”，让你可以更容易地找到和理解每个部分的功能。合理使用可以让维护更加简单和高效。

2.3 应用实践：创建一个简单的函数

示例代码：

-- 创建函数
CREATE OR REPLACE FUNCTION calculate_bonus (
  p_salary IN employees.salary%TYPE,
  p_bonus_rate IN NUMBER
) RETURN NUMBER
IS
BEGIN
  -- 计算奖金
  RETURN p_salary * p_bonus_rate;
END;
/

-- 调用函数
DECLARE
  v_bonus NUMBER;

BEGIN
  -- 调用函数计算奖金
  v_bonus := calculate_bonus(5000, 0.1);

  -- 输出奖金信息
  DBMS_OUTPUT.PUT_LINE('计算出的奖金: ' || v_bonus);
END;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 创建一个名为 calculate_bonus 的函数，并调用它计算奖金。
  3. 输出应为“计算出的奖金: 500”。

第三节：在函数中使用SQL语句

3.1 在SQL中调用函数

函数可以在 SQL 语句中直接调用，这使得它们非常适合用于计算和转换数据。

示例代码：

-- 查询并显示各部门的员工数量
SELECT department_id, get_employee_count(department_id) AS employee_count
FROM departments;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 查询各部门的员工数量，并显示结果。

小贴士：在SQL中调用函数让数据处理更灵活

在SQL中调用函数就像是给你的查询提供了一个“插件”，让它可以根据需要进行各种计算和转换。合理使用可以让数据处理更加灵活和强大。

3.2 写复杂的函数

复杂函数可以包含多个步骤和条件逻辑，适用于更复杂的业务需求。

示例代码：

CREATE OR REPLACE FUNCTION calculate_total_compensation (
  p_employee_id IN employees.employee_id%TYPE
) RETURN NUMBER
IS
  v_salary employees.salary%TYPE;
  v_commission_pct employees.commission_pct%TYPE;
  v_total_compensation NUMBER;
BEGIN
  -- 查询员工的基本薪资和佣金比例
  SELECT salary, commission_pct
  INTO v_salary, v_commission_pct
  FROM employees
  WHERE employee_id = p_employee_id;

  -- 计算总薪酬
  IF v_commission_pct IS NOT NULL THEN
    v_total_compensation := v_salary + (v_salary * v_commission_pct);
  ELSE
    v_total_compensation := v_salary;
  END IF;

  -- 返回总薪酬
  RETURN v_total_compensation;
END;
/

-- 调用函数
DECLARE
  v_total_compensation NUMBER;

BEGIN
  -- 调用函数计算总薪酬
  v_total_compensation := calculate_total_compensation(100);

  -- 输出总薪酬信息
  DBMS_OUTPUT.PUT_LINE('员工ID 100 的总薪酬: ' || v_total_compensation);
END;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 创建一个名为 calculate_total_compensation 的函数，并调用它计算总薪酬。
  3. 输出应为“员工ID 100 的总薪酬: X”。

小贴士：复杂函数让业务逻辑更清晰

复杂函数就像是给你的程序提供了一个“逻辑引擎”，让你可以把复杂的业务逻辑封装在里面。合理使用可以让业务逻辑更加清晰和易于维护。

第四节：优化SQL中的函数执行

4.1 使用 WITH 子句

使用 WITH 子句（Common Table Expressions, CTE）可以提高查询性能，特别是在嵌套查询较多的情况下。

示例代码：

WITH emp_data AS (
  SELECT employee_id, salary, commission_pct
  FROM employees
  WHERE department_id = 10
)
SELECT employee_id, 
       calculate_total_compensation(employee_id) AS total_compensation
FROM emp_data;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 使用 WITH 子句优化查询性能，并调用函数计算总薪酬。
  3. 输出应为各部门员工的总薪酬。

小贴士：WITH 子句让查询结构更清晰

WITH 子句就像是给你的查询提供了一个“临时视图”，让你可以把复杂的查询分解成更小的部分。合理使用可以让查询结构更加清晰和易于维护。

4.2 使用 UDF（用户定义函数）

用户定义函数（UDF）可以显著提高 SQL 查询的性能，尤其是在频繁调用函数的情况下。

示例代码：

-- 创建一个简单的UDF
CREATE OR REPLACE FUNCTION calculate_bonus_udf (
  p_salary IN employees.salary%TYPE,
  p_bonus_rate IN NUMBER
) RETURN NUMBER
DETERMINISTIC
IS
BEGIN
  RETURN p_salary * p_bonus_rate;
END;
/

-- 使用UDF优化查询性能
SELECT employee_id, calculate_bonus_udf(salary, 0.1) AS bonus
FROM employees;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 创建一个名为 calculate_bonus_udf 的用户定义函数，并使用它优化查询性能。
  3. 输出应为每位员工的奖金。

小贴士：UDF让查询性能更高效

UDF就像是给你的查询提供了一个“加速器”，让它可以在频繁调用函数的情况下仍然保持高效的性能。合理使用可以让查询性能更加高效。

第五节：注意事项

在编写 PL/SQL 函数时，有一些常见问题需要注意，以避免潜在的错误和性能问题。

5.1 错误处理

在函数中，错误处理尤为重要。使用异常处理机制来捕获和处理可能的错误。

示例：

CREATE OR REPLACE FUNCTION get_employee_salary (
  p_employee_id IN employees.employee_id%TYPE
) RETURN NUMBER
IS
  v_salary employees.salary%TYPE;
BEGIN
  -- 查询员工薪资
  SELECT salary INTO v_salary
  FROM employees
  WHERE employee_id = p_employee_id;

  -- 返回薪资
  RETURN v_salary;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, '未找到指定的员工ID');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002, '发生其他错误: ' || SQLERRM);
END;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 如果查询成功，返回员工的薪资；如果失败，输出相应的错误信息。

小贴士：异常处理让程序更健壮

异常处理就像是给你的程序提供了一个“救生圈”，让它可以在遇到错误时及时采取措施，确保程序的稳定性。合理使用可以让程序更加健壮和可靠。

第六节：代码挑战

现在是时候来挑战一下自己了！我们将通过几个简单的练习来巩固所学的知识。

挑战一：创建一个带 IN 参数的函数

编写一个 PL/SQL 块，创建一个带 IN 参数的函数，用于计算员工的奖金，并调用该函数进行测试。

示例代码：

-- 创建函数
CREATE OR REPLACE FUNCTION calculate_bonus (
  p_salary IN employees.salary%TYPE,
  p_bonus_rate IN NUMBER
) RETURN NUMBER
IS
BEGIN
  -- 计算奖金
  RETURN p_salary * p_bonus_rate;
END;
/

-- 调用函数
DECLARE
  v_bonus NUMBER;

BEGIN
  -- 调用函数计算奖金
  v_bonus := calculate_bonus(5000, 0.1);

  -- 输出奖金信息
  DBMS_OUTPUT.PUT_LINE('计算出的奖金: ' || v_bonus);
END;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 创建一个名为 calculate_bonus 的函数，并调用它计算奖金。
  3. 输出应为“计算出的奖金: 500”。

挑战二：创建一个在 SQL 中调用的函数

编写一个 PL/SQL 块，创建一个函数，用于查询员工的薪资，并在 SQL 语句中调用该函数进行测试。

示例代码：

-- 创建函数
CREATE OR REPLACE FUNCTION get_employee_salary (
  p_employee_id IN employees.employee_id%TYPE
) RETURN NUMBER
IS
  v_salary employees.salary%TYPE;
BEGIN
  -- 查询员工薪资
  SELECT salary INTO v_salary
  FROM employees
  WHERE employee_id = p_employee_id;

  -- 返回薪资
  RETURN v_salary;
END;
/

-- 在SQL中调用函数
SELECT employee_id, get_employee_salary(employee_id) AS salary
FROM employees
WHERE department_id = 10;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 创建一个名为 get_employee_salary 的函数，并在 SQL 语句中调用它查询员工的薪资。
  3. 输出应为部门ID为10的所有员工的薪资。

挑战三：创建一个复杂的函数

编写一个 PL/SQL 块，创建一个复杂的函数，用于计算员工的总薪酬（包括基本薪资和佣金），并调用该函数进行测试。

示例代码：

-- 创建函数
CREATE OR REPLACE FUNCTION calculate_total_compensation (
  p_employee_id IN employees.employee_id%TYPE
) RETURN NUMBER
IS
  v_salary employees.salary%TYPE;
  v_commission_pct employees.commission_pct%TYPE;
  v_total_compensation NUMBER;
BEGIN
  -- 查询员工的基本薪资和佣金比例
  SELECT salary, commission_pct
  INTO v_salary, v_commission_pct
  FROM employees
  WHERE employee_id = p_employee_id;

  -- 计算总薪酬
  IF v_commission_pct IS NOT NULL THEN
    v_total_compensation := v_salary + (v_salary * v_commission_pct);
  ELSE
    v_total_compensation := v_salary;
  END IF;

  -- 返回总薪酬
  RETURN v_total_compensation;
END;
/

-- 调用函数
DECLARE
  v_total_compensation NUMBER;

BEGIN
  -- 调用函数计算总薪酬
  v_total_compensation := calculate_total_compensation(100);

  -- 输出总薪酬信息
  DBMS_OUTPUT.PUT_LINE('员工ID 100 的总薪酬: ' || v_total_compensation);
END;
/

执行说明：

  1. 在 SQL*Plus 或 SQL Developer 中运行上述代码块。
  2. 创建一个名为 calculate_total_compensation 的函数，并调用它计算总薪酬。
  3. 输出应为“员工ID 100 的总薪酬: X”。

第七节：本章总结

在这一章中，我们深入探讨了 PL/SQL 中的函数（Functions），包括什么是函数、如何建立和使用函数以及如何优化函数执行。以下是本章的主要内容总结：

总结要点：

  1. 函数 是一种特殊的存储过程，它返回一个值。函数可以接受输入参数，并且必须有一个返回值。
  2. CREATE OR REPLACE FUNCTION 语句用于创建或替换函数。
  3. 在SQL中调用函数 可以使函数在查询中直接使用，适用于计算和转换数据。
  4. 优化SQL中的函数执行 可以通过使用 WITH 子句和用户定义函数（UDF）来提高查询性能。

恭喜你完成了第二十章的学习！通过这节课，你已经掌握了 函数 的使用方法，并为后续更高级的内容打下了坚实的基础。未来的学习中，我们将继续探索 PL/SQL 的更多功能，如异常处理、包等。希望你能继续保持对 PL/SQL 的兴趣，勇敢探索，成为一名熟练的 PL/SQL 用户！

 

plsql function
aoyanbai9311的博客
11-09 206
do language plpgsql$$declaredz driver_zone%rowtype;leadtime integer;begin leadtime = 0; for dz in select * from driver_zone loop leadtime = leadtime + 1; update driver_zo...
Oracle/PLSQL: Creating Functions
冯立彬的博客
10-30 7145
 Oracle/PLSQL: Creating FunctionsIn Oracle, you can create your own functions.译：在ORACLE中，你可以创建你自己的方法。The syntax for a function is:CREATE [OR REPLACE] FUNCTION function_name    [ (parameter
【Oracle学习笔记】8.函数（Function）
最新发布
鑫仔的记忆
07-03 399
【代码】【Oracle学习笔记】8.函数（Function）
ORACLE function方法学习
sophylyzz的博客
03-18 279
获取组织机构代码，以'a','b'...形式返回 比如：若为211241003 返回： '211241003','211241','211200','210000' 211241 返回：'211241','211200','210000' 211200 返回：'211200','210000' 210000...
plsql函数（用户定义函数）
hjdhxy的博客
09-19 899
函数（Function）是一种子程序，用于封装一段逻辑，并返回一个结果值。
Oracle函数学习总结
a2311772099的博客
09-13 181
Oracle函数学习总结
Oracle PL/SQL编程入门：第二版精华解读
" Beginning Oracle PL-SQL 2nd Edition 是一本针对初学者的Oracle PL/SQL编程教程，书中通过丰富的实例来讲解相关概念和技术。" 本书旨在帮助读者掌握编写Oracle数据库存储过程的基础知识，存储过程是PL/SQL的核心...
Oracle PL/SQL编程基础：从入门到理解块结构
"Oracle PL/SQL语言入门" Oracle PL/SQL是Oracle数据库系统中的过程化编程语言，它扩展了SQL的功能，使得开发人员能够编写更复杂、更具控制力的数据库应用程序。PL/SQL结合了SQL的非过程化特性与传统的编程语言如C...
Oracle PL/SQL编程指南：20100416版
本文档是一份详细的Oracle PL/SQL编程指南，旨在帮助开发者理解和掌握PL/SQL在Oracle数据库中的应用。PL/SQL是Oracle数据库支持的一种过程化编程语言，它融合了SQL的功能和一种高级编程语言的特性。 PL/SQL的特点和...
Oracle PL/SQL编程指南：程序开发与数据处理
"Oracle PL/SQL编程应用程序开发指南" 这篇PDF文档是关于Oracle PL/SQL程序开发的详细指南，适用于Oracle 10g环境。PL/SQL是Oracle数据库中的一种过程化编程语言，它结合了SQL的功能，使得在数据库中进行复杂逻辑...
Oracle PL/SQL编程基础：结构与匿名块
"这篇文档是关于在Oracle数据库中使用PL/SQL进行编程的教程，它是一种将过程化语言扩展到SQL的语言，提供了更多的灵活性和控制力，相较于JDBC更为轻量级。通常，PL/SQL代码会被编写在单独的文件中，如PL.sql，然后...
oracle functions
06-14
oracle functions introduction and example
Oracle的几个Function实例
03-21
NULL 博文链接：https://shihuan830619.iteye.com/blog/952088
plsql中的procedure和function编程
写自己的代码，让别人去看吧
05-15 4445
procedure:存储过程 eg: create or replace procedure p   --这儿是和plsql的区别         is         cursor c         is              select * from emp2 for  update;         --v_temp c%rowtype; begin
oracle function详解,Oracle 函数 Function 解析
weixin_35705382的博客
04-02 517
定义函数用于计算和返回一个结果值，把经常需要进行的计算写成函数,函数的调用是表达式的一部分。函数与过程在创建的形式上有些相似，也是编译后放在内存中供用户使用。函数必须有一个返回值，而过程没有做强制的规定。RETURN在声明部分需要定义一个返回参数的类型，而在函数体中必须有一个RETURN语句。如果函数结束时还没有遇到返回语句，就会发生错误。创建create or replace function ...
Oracle Functions快速入门
In-Memory Computing Technology
01-13 1176
简介 Oracle Function是Oracle Cloud Infrastructure对Fn的实现。 学习Oracle Function，如果要实践的话，Oracle官方提供的Oracle Functions QuickStart是最佳途径。这个QuickStart有网页版，文字版和PDF版。 Oracle Blogs也有一篇对应的文章：Oracle Functions: Serverles...
oracle function详解,Oracle函数用法详解
weixin_35949264的博客
04-02 9700
本文概述函数是用于返回单个值的子程序。你必须在调用函数之前声明并定义一个函数。它可以在同一时间声明和定义, 也可以在同一块中先声明然后定义。在Oracle中创建函数句法CREATE [OR REPLACE] FUNCTION function_name[ (parameter [, parameter]) ]RETURN return_datatypeIS | AS[declaration_sec...
【Oracle】函数
奥利文儿的博客
07-08 1317
函数又称为存储函数、存储结构，用户定义的函数可以被SQL语句或PL/SQL程序直接调用。函数与存储过程的不同之处在于函数有一个显式返回值，而存储过程只能依靠OUT或IN OUT返回数据。语法： RETURN语句用于指明函数返回值的类型，但不能约束返回值的长度、精度等。 在BEGIN函数体中，必须至少包含一个RETURN语句指明函数返回值。 函数可以在SQL语句的以下部分调用：–创建无参函数 –创建有参函数 –无参函数的调用 – 在pl/sql语句块中的调用 –有参函数的调用 –在PL/SQL语句块中的调
Oracle 分析函数（Analytic Functions） 说明
David Dai -- Focus on Oracle
11-10 8377
一.  Analytic Functions 说明 分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式. 官方对分析函数的说明如下： Analytic functions compute an aggregate value based on a group of rows. They differ fromaggregate functions in that
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

caifox菜狐狸

你的鼓励将是我创作的最大动力！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值