Oracle PL/SQL 存储过程和函数及示例

由： 菲奥娜·布朗 菲奥娜·布朗
Hours 更新

在本教程中，您将看到有关如何创建和执行命名块（过程和函数）的详细描述。

过程和函数是可以创建并作为数据库对象保存在数据库中的子程序。它们也可以在其他块中调用或引用。

除此之外，我们将介绍这两个子程序之间的主要区别。此外，我们将讨论 Oracle 内置函数。

PL/SQL 子程序中的术语

在了解 PL/SQL 子程序之前，我们将讨论这些子程序的各种术语。以下是我们将要讨论的术语。

产品型号

参数是变量或任何有效的占位符 PL/SQL 数据类型 PL/SQL 子程序通过它与主代码交换值。此参数允许向子程序提供输入并从这些子程序中提取。

  • 这些参数应该在创建时与子程序一起定义。
  • 这些参数包含在这些子程序的调用语句中，以便与子程序进行值交互。
  • 子程序中参数的数据类型和调用语句中的参数的数据类型应该相同。
  • 在参数声明时不应提及数据类型的大小，因为此类型的大小是动态的。

根据其用途，参数分为

  1. IN 参数
  2. OUT 参数
  3. IN OUT 参数

IN 参数

  • 该参数用于给子程序提供输入。
  • 在子程序中是只读变量，其值在子程序内部不可改变。
  • 在调用语句中，这些参数可以是变量、文字值或表达式，例如，它可以是像“5*8”或“a/b”这样的算术表达式，其中“a”和“b”是变量。
  • 默认情况下，参数为IN类型。

OUT 参数

  • 此参数用于获取子程序的输出。
  • 在子程序中是一个可读写的变量，其值可以在子程序内部改变。
  • 在调用语句中，这些参数应该始终是一个变量，以保存来自当前子程序的值。

IN OUT 参数

  • 此参数用于提供输入以及从子程序获取输出。
  • 在子程序中是一个可读写的变量，其值可以在子程序内部改变。
  • 在调用语句中，这些参数应该始终是一个变量，以保存来自子程序的值。

这些参数类型应该在创建子程序时提及。

返回

RETURN 是指示编译器将控制权从子程序切换到调用语句的关键字。在子程序中，RETURN 只是意味着控制权需要从子程序中退出。一旦控制器在子程序中找到 RETURN 关键字，就会跳过此关键字之后的代码。

通常，父块或主块将调用子程序，然后控制权将从这些父块转移到被调用的子程序。子程序中的 RETURN 将控制权返回到其父块。对于函数，RETURN 语句也会返回值。在函数声明时始终会提到此值的数据类型。数据类型可以是任何有效的 PL/SQL 数据类型。

PL/SQL 中的过程是什么？

A 程序 PL/SQL 中的子程序单元是由一组可以通过名称调用的 PL/SQL 语句组成的子程序单元。PL/SQL 中的每个过程都有自己独特的名称，可以通过该名称引用和调用。 Oracle 数据库存储为数据库对象。

请注意： 子程序只是一个过程，需要根据需要手动创建。创建后，它们将作为数据库对象存储。

PL/SQL中Procedure子程序单元的特点如下：

  • 程序是程序的独立块，可以存储在 数据库.
  • 可以通过引用其名称来调用这些 PLSQL 过程，以执行 PL/SQL 语句。
  • 它主要用于执行PL/SQL中的一个过程。
  • 它可以有嵌套的块，也可以在其他块或包内定义和嵌套。
  • 它包含声明部分（可选）、执行部分、异常处理部分（可选）。
  • 这些值可以传递到 Oracle 过程或通过参数从过程获取。
  • 这些参数应该包含在调用语句中。
  • SQL 中的过程可以使用 RETURN 语句将控制权返回给调用块，但不能通过 RETURN 语句返回任何值。
  • 不能直接从 SELECT 语句调用过程。它们可以从另一个块或通过 EXEC 关键字调用。

句法

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE 指示编译器在 Oracle关键字“OR REPLACE”指示编译器用当前过程替换现有过程（如果有）。
  • 过程名称应该是唯一的。
  • 当存储过程位于 Oracle 嵌套到其他一些块中。如果过程是独立的，则将使用“AS”。除了此编码标准外，两者具有相同的含义。

示例 1：创建过程并使用 EXEC 调用它

在此示例中，我们将创建一个 Oracle 程序以名称作为输入，并打印欢迎消息作为输出。我们将使用 EXEC 命令来调用该程序。

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) 
IS
BEGIN
dbms_output.put_line (‘Welcome '|| p_name);
END;
/
EXEC welcome_msg (‘Guru99’);

代码说明：

  • 代码行 1：创建名称为“welcome_msg”且带有一个“IN”类型的参数“p_name”的过程。
  • 代码行 4：通过连接输入的名称来打印欢迎信息。
  • 过程编译成功。
  • 代码行 7：使用带有参数“Guru99”的EXEC 命令调用该过程。过程被执行，并且消息被打印为“Welcome Guru99”。

相关文章

什么是函数？

函数是一个独立的 PL/SQL 子程序。与 PL/SQL 过程一样，函数有一个唯一的名称，可以通过该名称引用。这些函数存储为 PL/SQL 数据库对象。以下是函数的一些特征。

  • 函数是一个独立的块，主要用于计算目的。
  • 函数使用 RETURN 关键字来返回值，并且其数据类型在创建时定义。
  • 函数应该返回一个值或引发异常，即函数中返回是强制性的。
  • 没有 DML 语句的函数可以在 SELECT 查询中直接调用，而带有 DML 操作的函数只能从其他 PL/SQL 块中调用。
  • 它可以有嵌套的块，也可以在其他块或包内定义和嵌套。
  • 它包含声明部分（可选）、执行部分、异常处理部分（可选）。
  • 可以将值传递到函数中或者通过参数从过程中获取。
  • 这些参数应该包含在调用语句中。
  • PLSQL 函数除了使用 RETURN 之外，还可以通过 OUT 参数返回值。
  • 由于它总是返回值，在调用语句中它总是伴随赋值运算符来填充变量。

PL/SQL 中的函数

句法

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END;
  • CREATE FUNCTION 指示编译器创建一个新函数。关键字“OR REPLACE”指示编译器用当前函数替换现有函数（如果有）。
  • 函数名称应该是唯一的。
  • 应该提到 RETURN 数据类型。
  • 当过程嵌套在其他块中时，将使用关键字“IS”。如果过程是独立的，则将使用“AS”。除了此编码标准外，两者具有相同的含义。

示例 1：创建函数并使用匿名块调用它

在这个程序中，我们将创建一个函数，该函数以名称作为输入并返回欢迎消息作为输出。我们将使用匿名块和 select 语句来调用该函数。

PL/SQL 中的函数

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
IS
BEGIN
RETURN (‘Welcome ‘|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func (‘Guru99’);
dbms_output.put_line(lv_msg);
END;
SELECT welcome_msg_func(‘Guru99:) FROM DUAL;

代码说明：

  • 代码行 1：创建 Oracle 函数名称为“welcome_msg_func”，其参数“p_name”为“IN”类型。
  • 代码行 2：声明返回类型为 VARCHAR2
  • 代码行 5：返回连接值“Welcome”和参数值。
  • 代码行 8：匿名块调用上述函数。
  • 代码行 9：声明变量的数据类型与函数的返回数据类型相同。
  • 代码行 11：调用函数并将返回值填充到变量“lv_msg”。
  • 代码行 12：打印变量值。您将在此处获得的输出是“Welcome Guru99”
  • 代码行 14：通过SELECT语句调用相同函数。返回值直接指向标准输出。

过程和函数之间的相似之处

  • 两者都可以从其他 PL/SQL 块调用。
  • 如果子程序中引发的异常未在子程序中处理 异常处理 部分，那么它将传播到调用块。
  • 两者都可以根据需要具有任意数量的参数。
  • 两者在 PL/SQL 中都被视为数据库对象。

程序与功能：主要区别

程序 功能
主要用于执行某个进程 主要用于进行一些计算
无法在 SELECT 语句中调用 可以在 SELECT 语句中调用不包含 DML 语句的函数
使用 OUT 参数返回值 使用 RETURN 返回值
不强制返回值 必须返回值
RETURN 将简单地退出子程序控制。 RETURN 将退出子程序控制并返回值
创建时不会指定返回数据类型 返回数据类型在创建时是强制性的

PL/SQL 中的内置函数

PL / SQL 包含各种用于处理字符串和日期数据类型的内置函数。在这里我们将了解常用函数及其用法。

转换函数

这些内置函数用于将一种数据类型转换为另一种数据类型。

功能名称 用法 例如：
字符 将其他数据类型转换为字符数据类型 到字符（123）；
TO_DATE（字符串，格式） 将给定的字符串转换为日期。字符串应与格式匹配。

TO_DATE('2015-JAN-15', 'YYYY-MON-DD');

输出：1 / 15 / 2015
TO_NUMBER（文本，格式）

将文本转换为给定格式的数字类型。

信息‘9’表示数字的数量

 从双重中选择TO_NUMBER('1234′,'9999')；

输出：1234

从双重中选择TO_NUMBER('1,234.45′,'9,999.99')；

输出：1234

字符串函数

这些是用于字符数据类型的函数。

功能名称 用法 例如：
INSTR(文本，字符串，开始，发生) 给出给定字符串中特定文本的位置。

  • text – 主字符串
  • string – 需要搜索的文本
  • start – 搜索的起始位置（可选）
  • 一致性 – 搜索字符串的出现（可选）
 从双精度中选择 INSTR(‘AEROPLANE’,‘E’,2,1)

输出：2

从双精度中选择 INSTR(‘AEROPLANE’,‘E’,2,2)

输出：9（2nd E 的出现
SUBSTR（文本，开始，长度） 给出主字符串的子字符串值。

  • 文本 – 主字符串
  • 开始 – 起始位置
  • length – 要子串的长度
 从双精度中选择 substr('飞机',1,7)

输出: 航空飞机
上部（文本） 返回所提供文本的大写字母 从对偶中选择上部（‘guru99’）；

输出：GURU99
下方 ( 文字 ) 返回所提供文本的小写形式 从双重中选择较低（'AerOpLane'）；

输出：飞机
INITCAP（文本） 返回以大写字母开头的给定文本。 从双中选择（‘guru99’）

输出：Guru99

从双重中选择（“我的故事”）

输出： 我的故事
长度（文本） 返回给定字符串的长度 从对偶中选择 LENGTH (‘guru99’)；

输出：6
LPAD（文本，长度，pad_char） 使用给定的字符在左侧填充给定长度（总字符串）的字符串 从 dual 中选择 LPAD('guru99', 10, '$')；

输出：$$$$guru99
RPAD（文本，长度，pad_char） 使用给定的字符在右侧填充给定长度（总字符串）的字符串 从双中选择 RPAD('guru99′,10,'-')

输出：guru99——
LTRIM（文本） 修剪文本中的前导空白 从双重中选择 LTRIM(' Guru99')；

输出：Guru99
RTRIM（文本） 修剪文本尾部的空白 从双重中选择 RTRIM(‘Guru99’)；

输出; Guru99

日期函数

这些是用于处理日期的函数。

功能名称 用法 例如：
ADD_MONTHS（日期，月份数） 将给定的月份添加到日期 添加月份（'2015-01-01'，5）；

输出：05 / 01 / 2015
系统日期 返回服务器的当前日期和时间 从双重中选择SYSDATE；

输出：10 年 4 月 2015 日下午 2:11:43
TRUNC 将日期变量四舍五入为较低的可能值 从 dual 中选择 sysdate、TRUNC(sysdate)；

输出：10 年 4 月 2015 日下午 2:12:39
圆型行李箱 将日期四舍五入到最接近的上限或下限 从双精度中选择系统日期、ROUND(系统日期)

输出：10 年 4 月 2015 日下午 2:14:34
MONTHS_BETWEEN 返回两个日期之间的月份数 从双表中选择 MONTHS_BETWEEN (sysdate+60, sysdate)

输出：2

总结

在本章中，我们学习了以下内容。

  • 如何创建过程以及调用它的不同方法
  • 如何创建函数以及调用它的不同方法
  • 过程和函数之间的相同点和不同点
  • PL/SQL 子程序中的参数和 RETURN 常用术语
  • 常见的内置函数 Oracle PL / SQL

你可能会喜欢：