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

什么是PL/SQL子程序？

在本教程中，您将看到如何创建和执行命名块、过程和函数的详细说明。

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

我们还会介绍这两个子程序之间的主要区别，并进行讨论。 Oracle 内置函数。

PL/SQL 子程序中的术语

在学习 PL/SQL 子程序之前，我们将讨论这些子程序中包含的各种术语。

参数

参数是任何有效值的变量或占位符。 PL/SQL 数据类型 PL/SQL 子程序通过此参数与主程序交换值。此参数允许向子程序输入和输出值。trac从中汲取价值。

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

根据用途，参数可分为：

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

IN 参数

• 用于向子程序提供输入。
• 它是子程序内部的只读变量；其值不能在子程序内部更改。
• 在调用语句中，它可以是变量、字面值或表达式，例如“5*8”或“a/b”。
• 默认情况下，参数类型为 IN。

OUT 参数

• 用于获取子程序的输出。
• 它是子程序内部的读写变量；它的值可以在子程序内部更改。
• 在调用语句中，应该始终使用一个变量来保存来自子程序的值。

IN OUT 参数

• 用于向子程序提供输入和获取输出。
• 它是子程序内部的读写变量；它的值可以在子程序内部更改。
• 在调用语句中，应该始终使用一个变量来保存来自子程序的值。

创建子程序时应指定参数类型。

返回

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

通常情况下，父代码块或主代码块会调用子程序，控制权从父代码块转移到被调用的子程序。子程序中的 RETURN 语句会将控制权返回给父代码块。对于函数而言，RETURN 语句还会返回一个值，该值的数据类型在函数声明时指定。

PL/SQL 中的存储过程是什么？

A 程序 在 PL/SQL 中，子程序单元由一组 PL/SQL 语句组成，这些语句可以通过名称调用。每个过程都有其唯一的名称，并存储在 PL/SQL 中。 Oracle 数据库作为数据库对象。

注意： 子程序本质上就是一个过程，需要根据需求手动创建。创建完成后，它会作为数据库对象存储。

PL/SQL 中过程子程序单元的特点是：

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

句法

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE PROCEDURE 指示编译器创建一个新过程。关键字“OR REPLACE”指示编译器用当前过程替换现有过程（如果有）。
• 过程名称必须唯一。
• 当存储过程嵌套在其他代码块中时，使用关键字“IS”。如果存储过程是独立的，则使用“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');

Code 说明：

• Code 第1行： 创建名为“welcome_msg”的过程，该过程有一个类型为“IN”的参数“p_name”。
• Code 第4行： 通过连接输入的姓名来打印欢迎信息。
• 程序编译成功。
• Code 第7行： 使用参数“EXEC”调用该过程Guru99'。该程序执行并打印“欢迎”。 Guru99“。

什么是函数？

函数是独立的 PL/SQL 子程序。与过程类似，函数具有唯一的名称，并以 PL/SQL 数据库对象的形式存储。其特点如下：

• 函数是独立的模块，主要用于计算。
• 函数使用 RETURN 关键字返回一个值，该值的数据类型在创建时定义。
• 函数要么应该返回一个值，要么应该抛出一个异常；函数必须有返回值。
• 不包含 DML 语句的函数可以直接在 SELECT 查询中调用，而包含 DML 语句的函数只能从其他 PL/SQL 块中调用。
• 它可以包含嵌套块，也可以嵌套在其他块或包中。
• 它包含声明部分（可选）、执行部分和异常处理部分（可选）。
• 可以通过参数将值传递给函数或从函数中获取值。
• 这些参数应该包含在调用语句中。
• 除了使用 RETURN 之外，函数还可以通过 OUT 参数返回值。
• 由于它总是返回一个值，因此调用语句总是使用赋值运算符来填充变量。

句法

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 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 语句来调用它。

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
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;

Code 说明：

• Code 第1行： 创建名为“welcome_msg_func”的函数，该函数有一个类型为“IN”的参数“p_name”。
• Code 第2行： 将返回类型声明为 VARCHAR2。
• Code 第5行： 返回连接后的值“Welcome”和参数值。
• Code 第8行： 匿名块调用上述函数。
• Code 第9行： 声明变量时，使其数据类型与函数的返回类型相同。
• Code 第11行： 调用该函数并将返回值填充到变量'lv_msg'中。
• Code 第12行： 打印变量值。输出结果为“欢迎”。 Guru99“。
• Code 第14行： 通过 SELECT 语句调用同一个函数。返回值被输出到标准输出。

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

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

流程与功能：主要区别

PL/SQL 中的内置函数

PL / SQL 它包含多种内置函数，用于处理字符串和日期数据类型。这里我们介绍一些常用函数及其用法。

转换函数

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

字符串函数

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

日期函数

这些函数用于处理日期。

常见问题