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

在本教程中，您将了解创建和执行命名块（过程和函数）的详细说明。

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

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

PL/SQL 子程序中的术语

在学习 PL/SQL 子程序之前，我们将讨论将构成这些子程序的各种术语。下面是我们将要讨论的术语。

参数

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

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

根据其目的，参数可分为：

IN 参数
OUT 参数
IN OUT 参数

IN 参数

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

OUT 参数

此参数用于从子程序获取输出。
它是子程序中的读写变量。其值可以在子程序中更改。
在调用语句中，这些参数必须始终是变量，以保存当前子程序的值。

IN OUT 参数

此参数可同时用于向子程序提供输入和获取输出。
它是子程序中的读写变量。其值可以在子程序中更改。
在调用语句中，这些参数必须始终是变量，以保存子程序的值。

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

RETURN

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

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

PL/SQL 中的过程是什么？

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

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

以下是 PL/SQL 中的过程子程序单元的特征：

过程是独立的程序块，可以存储在数据库中。
可以通过引用其名称来调用这些 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”指示编译器替换现有过程（如果有）。
过程名称应唯一。
当存储过程嵌套在其他块中时，将使用关键字“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’);

代码解释

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

函数是什么？

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

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

语法

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”指示编译器替换现有函数（如果有）。
函数名称应唯一。
应指定返回数据类型。
当过程嵌套在其他块中时，将使用关键字“IS”。如果过程是独立的，则使用“AS”。除了编码约定外，两者具有相同的含义。

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

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

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：创建名为“welcome_msg_func”且带有一个“IN”类型参数“p_name”的 Oracle 函数。
代码行 2：将返回类型声明为 VARCHAR2。
代码行 5：返回“Welcome”和参数值的连接值。
代码行 8：用于调用上述函数的匿名块。
代码行 9：声明数据类型与函数返回数据类型相同的变量。
代码行 11：调用函数并将返回值填充到变量“lv_msg”中。
代码行 12：打印变量值。您在此处获得的输出是“Welcome Guru99”。
代码行 14：通过 SELECT 语句调用同一个函数。返回值直接定向到标准输出。

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

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

过程与函数的区别：关键差异

过程	函数
主要用于执行特定进程	主要用于执行某些计算
不能在 SELECT 语句中调用	不包含 DML 语句的函数可以在 SELECT 语句中调用
使用 OUT 参数返回值	使用 RETURN 返回值
不强制要求返回值	必须返回值
RETURN 仅退出子程序的控制。	RETURN 退出子程序的控制，并返回值
创建时不会指定返回数据类型	创建时必须指定返回数据类型

PL/SQL 中的内置函数

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

转换函数

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

函数名	用途	示例
TO_CHAR	将其他数据类型转换为字符数据类型	TO_CHAR(123);
TO_DATE ( string, format )	将给定的字符串转换为日期。字符串必须与格式匹配。	TO_DATE(‘2015-JAN-15’, ‘YYYY-MON-DD’); 输出: 1/15/2015
TO_NUMBER (text, format)	将文本转换为给定格式的数字类型。格式“9”表示数字的位数	Select TO_NUMBER(‘1234′,’9999’) from dual; 输出: 1234 Select TO_NUMBER(‘1,234.45′,’9,999.99’) from dual; 输出: 1234

字符串函数

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

函数名	用途	示例
INSTR(text, string, start, occurance)	给出给定字符串中特定文本的位置。 text – 主字符串 string – 需要搜索的文本 start – 搜索的起始位置（可选） occurance – 搜索字符串的出现次数（可选）	Select INSTR(‘AEROPLANE’,’E’,2,1) from dual 输出: 2 Select INSTR(‘AEROPLANE’,’E’,2,2) from dual 输出：9（E 的第 2 次出现）
SUBSTR ( text, start, length)	给出主字符串的子字符串值。 text – 主字符串 start – 起始位置 length – 要截取的长度	select substr(‘aeroplane’,1,7) from dual 输出：aeropla
UPPER ( text )	返回提供的文本的大写形式	Select upper(‘guru99’) from dual; 输出：GURU99
LOWER ( text )	返回提供的文本的小写形式	Select lower (‘AerOpLane’) from dual; 输出：aeroplane
INITCAP ( text)	返回给定文本的首字母大写形式。	Select (‘guru99’) from dual 输出：Guru99 Select (‘my story’) from dual 输出：My Story
LENGTH ( text )	返回给定字符串的长度	Select LENGTH (‘guru99’) from dual; 输出: 6
LPAD ( text, length, pad_char)	使用给定字符，在左侧用给定长度（总字符串）填充字符串。	Select LPAD(‘guru99’, 10, ‘$’) from dual; 输出：$$$$guru99
RPAD (text, length, pad_char)	使用给定字符，在右侧用给定长度（总字符串）填充字符串。	Select RPAD(‘guru99′,10,’-‘) from dual 输出：guru99—-
LTRIM ( text )	删除文本开头的空格	Select LTRIM(‘ Guru99’) from dual; 输出：Guru99
RTRIM ( text )	删除文本末尾的空格	Select RTRIM(‘Guru99 ‘) from dual; 输出；Guru99

日期函数

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

函数名	用途	示例
ADD_MONTHS (date, no.of months)	向日期添加给定的月数	ADD_MONTH(‘2015-01-01’,5); 输出: 05/01/2015
SYSDATE	返回服务器的当前日期和时间	Select SYSDATE from dual; 输出：2015年10月4日下午2:11:43
TRUNC	将日期变量四舍五入到较低的可能值	select sysdate, TRUNC(sysdate) from dual; 输出：2015年10月4日下午2:12:39 2015年10月4日
ROUND	将日期四舍五入到最近的整数，向上或向下	Select sysdate, ROUND(sysdate) from dual 输出：2015年10月4日下午2:14:34 2015年10月5日
MONTHS_BETWEEN	返回两个日期之间的月数	Select MONTHS_BETWEEN (sysdate+60, sysdate) from dual 输出: 2

摘要

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

如何创建过程及其调用方式
如何创建函数及其调用方式
过程和函数之间的相似之处和区别
PL/SQL 子程序中的参数和 RETURN 公共术语
Oracle PL/SQL 中的常用内置函数