Plsql 简明教程

PL/SQL - Quick Guide

PL/SQL - Overview

PL/SQL 编程语言是由 Oracle 公司在 20 世纪 80 年代末作为 SQL 和 Oracle 关系数据库的程序扩展语言开发的。以下是关于 PL/SQL 的一些重要事实 -

  1. PL/SQL 是一种完全可移植、高性能的事务处理语言。

  2. PL/SQL 提供了内置、解释的和与操作系统无关的编程环境。

  3. PL/SQL 还可以直接从命令行 SQL*Plus interface 调用。

  4. 也可以从外部编程语言调用数据库来进行直接调用。

  5. PL/SQL 一般语法基于 ADA 和 Pascal 编程语言。

  6. 除了 Oracle 之外，PL/SQL 还可在 TimesTen in-memory database 和 IBM DB2 中使用。

Features of PL/SQL

PL/SQL 具有以下特性 -

  1. PL/SQL 与 SQL 紧密集成。

  2. 它提供广泛的错误检查。

  3. 它提供多种数据类型。

  4. 它提供各种编程结构。

  5. 它通过函数和过程支持结构化编程。

  6. It supports object-oriented programming.

  7. 它支持 Web 应用程序和服务器页面的开发。

Advantages of PL/SQL

PL/SQL 具有以下优点 -

  1. SQL 是标准数据库语言，PL/SQL 与 SQL 紧密集成。PL/SQL 支持静态和动态 SQL。静态 SQL 支持 DML 操作和从 PL/SQL 块进行事务控制。在动态 SQL 中，SQL 允许在 PL/SQL 块中嵌入 DDL 语句。

  2. PL/SQL 允许一次向数据库发送整个语句块。这可以减少网络流量，并为应用程序提供高性能。

  3. 由于 PL/SQL 可以在数据库中查询、转换和更新数据，因此可以极大提高程序员的工作效率。

  4. PL/SQL 通过异常处理、封装、数据隐藏和面向对象数据类型等强大功能节省了设计和调试时间。

  5. 编写在 PL/SQL 中的应用程序完全可移植。

  6. PL/SQL 提供了高安全级别。

  7. PL/SQL 提供了对预定义 SQL 包的访问。

  8. PL/SQL 提供了对面向对象编程的支持。

  9. PL/SQL 提供了对开发 Web 应用程序和服务器页面的支持。

PL/SQL - Environment Setup

在本章中，我们将讨论 PL/SQL 的环境设置。PL/SQL 不是一个独立的编程语言;它是 Oracle 编程环境中的一个工具。 SQL Plus* 是一个交互式工具，它允许你在命令提示符处键入 SQL 和 PL/SQL 语句。然后将这些命令发送到数据库进行处理。一旦这些语句 được xử lý，结果将被发送回并在屏幕上显示。

要运行 PL/SQL 程序，你应该在你的机器上安装了 Oracle RDBMS Server。这将负责执行 SQL 命令。Oracle RDBMS 的最新版本是 11g。你可以从以下链接下载 Oracle 11g 的试用版 -

Download Oracle 11g Express Edition

你将不得不根据你的操作系统下载 32 位或 64 位版本的安装程序。通常有两个文件。我们下载了 64 位版本。你还可以对你的操作系统使用类似的步骤，无论它是 Linux 还是 Solaris。

  1. win64_11gR2_database_1of2.zip

  2. win64_11gR2_database_2of2.zip

在下载以上两个文件后，你需要将它们解压到一个目录 database 中，并在该目录下你会找到以下子目录 -

oracle sub directories

Step 1

现在让我们使用安装文件启动 Oracle Database Installer。以下是第一个屏幕。你可以提供你的电子邮件 ID，并选中复选框，如以下屏幕截图所示。单击 Next 按钮。

oracle install step1

Step 2

系统将把你定向到以下屏幕;取消选中复选框，然后单击 Continue 按钮继续。

oracle install error1

Step 3

使用单选按钮仅选择第一个选项 Create and Configure Database ，然后单击 Next 按钮继续。

oracle install step2

Step 4

我们假设你正在安装 Oracle 以用于基本的学习目的，并且你正在你的 PC 或笔记本电脑上安装它。因此，选择 Desktop Class 选项，然后单击 Next 按钮继续。

oracle install step3

Step 5

提供一个安装 Oracle 服务器的位置。只需修改 Oracle Base ，其他位置将自动设置。你還需要提供一个密码;此密码将由系统 DBA 使用。一旦你提供了所需的信息，单击 Next 按钮继续。

oracle install step4

Step 6

再次单击 Next 按钮继续。

oracle install step5

Step 7

单击 Finish 按钮继续;这将启动实际的服务器安装。

oracle install step6

Step 8

这将会花些片刻时间，直到 Oracle 开始执行必需的配置。

oracle install step7

Step 9

在此，Oracle 安装将复制必需的配置文件。这应该需要片刻时间−

oracle install configuration1

Step 10

复制数据库文件后，您将看到以下对话框。只需单击 OK 按钮并退出。

oracle install configuration2

Step 11

安装后，您将看到以下最终窗口。

oracle install step8

Final Step

现在是验证您的安装的时候了。在命令提示符处，如果您使用的是 Windows，请使用以下命令：−

sqlplus "/ as sysdba"

您应该拥有用于编写 PL/SQL 命令和脚本的 SQL 提示符 −

plsql command prompt

Text Editor

从命令提示符运行大型程序可能会导致无意中丢失部分工作。始终建议使用命令文件。要使用命令文件−

  1. 在文本编辑器中键入代码，例如 Notepad, Notepad+, 或 EditPlus 等。

  2. 使用 .sql 扩展名在主目录中保存该文件。

  3. 从创建 PL/SQL 文件的目录启动 SQL*Plus command prompt 。

  4. 在 SQL*Plus 命令提示符下键入 @file_name 以执行程序。

如果您不使用文件执行 PL/SQL 脚本，则只需复制 PL/SQL 代码并右键单击显示 SQL 提示符的黑色窗口；使用 paste 选项将完整代码粘贴到命令提示符中。最后，如果尚未执行代码，只需按 Enter 即可执行代码。

PL/SQL - Basic Syntax

在本章中，我们将讨论 PL/SQL 的基本语法，这是一种 block-structured 语言；这意味着 PL/SQL 程序被划分为逻辑代码块并编写在这些逻辑代码块中。每个块包含三个子部分：−

S.No

Sections & Description

1

Declarations 此部分以关键字 DECLARE 开头。这是一个可选部分，它定义了程序中要使用的所有变量、游标、子程序和其他元素。

2

Executable Commands 此部分包含在关键字 BEGIN 和 END 之间，并且是一个强制部分。它包含程序的可执行 PL/SQL 语句。它应该至少包含一行可执行代码，这可能只是一个 NULL command ，表示什么都不应该执行。

3

Exception Handling 此部分以关键字 EXCEPTION 开头。此可选部分包含 exception(s) 来处理程序中的错误。

每个 PL/SQL 语句都以分号 (;) 结尾。PL/SQL 块可以使用 BEGIN 和 END 嵌套在其他 PL/SQL 块内。以下是 PL/SQL 块的基本结构：−

DECLARE
   <declarations section>
BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling>
END;

The 'Hello World' Example

DECLARE
   message  varchar2(20):= 'Hello, World!';
BEGIN
   dbms_output.put_line(message);
END;
/

end; 行表示 PL/SQL 块的结束。要在 SQL 命令行中运行代码，您可能需要在代码最后一行之后的第一个空白行的开头键入 /。在 SQL 提示符处执行以上代码时，它产生以下结果：−

Hello World

PL/SQL procedure successfully completed.

The PL/SQL Identifiers

PL/SQL 标识符是常量、变量、异常、过程、游标和保留字。标识符由一个字母（后面可以跟更多字母、数字、美元符号、下划线和数字符号）组成，并且不应超过 30 个字符。

默认情况下， identifiers are not case-sensitive 。因此，你可以使用 integer 或者 INTEGER 来表示数值。你不能将保留关键字用作标识符。

The PL/SQL Delimiters

分隔符是具有特殊含义的符号。以下是 PL/SQL 中的分隔符列表：

Delimiter

Description

+, -, *, /

Addition, subtraction/negation, multiplication, division

%

Attribute indicator

'

Character string delimiter

.

Component selector

(,)

Expression or list delimiter

:

Host variable indicator

,

Item separator

"

Quoted identifier delimiter

=

Relational operator

@

Remote access indicator

;

Statement terminator

:=

Assignment operator

⇒

Association operator

*

*

Concatenation operator

**

Exponentiation operator

<<, >>

标签分隔符（开始和结束）

/, /

多行注释分隔符（开始和结束）

--

Single-line comment indicator

..

Range operator

<, >, ⇐, >=

Relational operators

<>, '=, ~=, ^=

不同版本的 NOT EQUAL

The PL/SQL Comments

程序注释是可以包括在你所编写的 PL/SQL 代码中的解释性语句，并且有助于其他人阅读其源代码。所有编程语言都允许某种形式的注释。

PL/SQL 支持单行和多行注释。PL/SQL 编译器会忽略出现在任何注释中的所有字符。PL/SQL 单行注释以分隔符 (双连字符) 开始，多行注释则由 /* 和 */ 括住。

DECLARE
   -- variable declaration
   message  varchar2(20):= 'Hello, World!';
BEGIN
   /*
   *  PL/SQL executable statement(s)
   */
   dbms_output.put_line(message);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Hello World

PL/SQL procedure successfully completed.

PL/SQL Program Units

PL/SQL 单元是以下任一项：

  1. PL/SQL block

  2. Function

  3. Package

  4. Package body

  5. Procedure

  6. Trigger

  7. Type

  8. Type body

下文章节将讨论这些单元中的每个单元。

PL/SQL - Data Types

本章中，我们将讨论 PL/SQL 中的数据类型。PL/SQL 变量、常量和参数必须具有有效的数据类型，该数据类型指定了存储格式、约束和有效范围。本章中，我们将重点介绍 SCALAR 和 LOB 数据类型。其他两个数据类型将在其他章节中介绍。

S.No

Category & Description

1

Scalar 没有内部组件的单一值，如 NUMBER, DATE, 或 BOOLEAN 。

2

Large Object (LOB) 指向与其他数据项分开存储的大对象的指针，例如文本、图形图像、视频剪辑和声波形式。

3

Composite 具有可单独访问的内部组件的数据项。例如，集合和记录。

4

Reference 指向其他数据项的指针。

PL/SQL Scalar Data Types and Subtypes

PL/SQL 标量数据类型和子类型属于以下类别 −

S.No

Date Type & Description

1

执行算术运算的 Numeric 数值。

2

表示单个字符或字符字符串的 Character 字母数字值。

3

执行逻辑运算的 Boolean 逻辑值。

4

Datetime Dates and times.

PL/SQL 提供数据类型的子类型。例如，数据类型 NUMBER 具有名为 INTEGER 的子类型。在将 PL/SQL 代码嵌入到其他程序（例如 Java 程序）时，可以在 PL/SQL 程序中使用子类型来使数据类型与其他程序中的数据类型兼容。

PL/SQL Numeric Data Types and Subtypes

下表列出了 PL/SQL 预定义的数值数据类型及其子类型 −

S.No

Data Type & Description

1

用 32 位表示的，范围在 -2,147,483,648 到 2,147,483,647 之间的带符号整数

2

用 32 位表示的，范围在 -2,147,483,648 到 2,147,483,647 之间的带符号整数

3

IEEE 754 格式的单精度浮点数

4

IEEE 754 格式的双精度浮点数

5

NUMBER(prec, scale) 绝对值为 1E-130 至 (但不包括) 1.0E126 的定点数或浮点数。NUMBER 变量还可以表示 0

6

DEC(prec, scale) 最大精度为 38 位十进制数字的 ANSI 特定定点类型

7

DECIMAL(prec, scale) 最大精度为 38 位十进制数字的 IBM 特定定点类型

8

NUMERIC(pre, secale) 最大精度为 38 位十进制数字的浮点类型

9

DOUBLE PRECISION 最大精度为 126 位二进制数字（约 38 位十进制数字）的 ANSI 特定浮点类型

10

FLOAT 最大精度为 126 位二进制数字（约 38 位十进制数字）的 ANSI 和 IBM 特定浮点类型

11

INT 最大精度为 38 位十进制数字的 ANSI 特定整数类型

12

INTEGER 最大精度为 38 位十进制数字的 ANSI 和 IBM 特定整数类型

13

SMALLINT 最大精度为 38 位十进制数字的 ANSI 和 IBM 特定整数类型

14

REAL 最大精度为 63 位二进制数字（约 18 位十进制数字）的浮点类型

以下是一个有效的声明 −

DECLARE
   num1 INTEGER;
   num2 REAL;
   num3 DOUBLE PRECISION;
BEGIN
   null;
END;
/

编译并执行上述代码后，将产生以下结果 −

PL/SQL procedure successfully completed

PL/SQL Character Data Types and Subtypes

以下是 PL/SQL 预定义字符数据类型及其子类型 −

S.No

Data Type & Description

1

CHAR 最大长度为 32,767 字节的定长字符字符串

2

VARCHAR2 最大长度为 32,767 字节的可变长度字符字符串

3

RAW 最大长度为 32,767 字节的可变长度二进制字符串或字节字符串，不受 PL/SQL 解释

4

NCHAR 最大长度为 32,767 字节的定长国家/地区代码字符字符串

5

NVARCHAR2 最大长度为 32,767 字节的可变长度国家/地区代码字符字符串

6

LONG 最大长度为 32,760 字节的可变长度字符字符串

7

LONG RAW 最大大小为 32,760 字节的可变长度二进制或字节字符串，不经 PL/SQL 解析

8

ROWID 物理行标识符，普通表中行的地址

9

UROWID 通用行标识符（物理、逻辑或外部行标识符）

PL/SQL Boolean Data Types

BOOLEAN 数据类型存储在逻辑运算中使用的逻辑值。逻辑值为布尔值 TRUE 和 FALSE 以及值 NULL 。

然而，SQL 没有等同于 BOOLEAN 的数据类型。因此，布尔值不能在 − 中使用

  1. SQL statements

  2. 内置 SQL 函数（如 TO_CHAR ）

  3. 从 SQL 语句调用的 PL/SQL 函数

PL/SQL Datetime and Interval Types

DATE 数据类型用于存储固定长度日期时间，其中包括自午夜以来的时间（以秒为单位）。有效的日期范围是从公元前 4712 年 1 月 1 日到公元 9999 年 12 月 31 日。

默认日期格式由 Oracle 初始化参数 NLS_DATE_FORMAT 设置。例如，默认可能为“DD-MON-YY”，其中包括一个用于表示当月日期的两位数字、月份名称的缩写以及该年的最后两位数字。例如，01-OCT-12。

每个 DATE 包含世纪、年、月、日、小时、分钟和秒。下表显示了每个字段的有效值 −

Field Name

Valid Datetime Values

Valid Interval Values

YEAR

-4712 到 9999（不包括公元 0 年）

Any nonzero integer

MONTH

01 to 12

0 to 11

DAY

01 到 31（受 MONTH 和 YEAR 的值限制，根据该国家/地区的日历规则）

Any nonzero integer

HOUR

00 to 23

0 to 23

MINUTE

00 to 59

0 to 59

SECOND

00 到 59.9(n)，其中 9(n) 是时间分数秒的精度

0 到 59.9(n)，其中 9(n) 是间隔分数秒的精度

TIMEZONE_HOUR

-12 到 14（此范围适应夏令时变更）

Not applicable

TIMEZONE_MINUTE

00 to 59

Not applicable

TIMEZONE_REGION

在动态性能视图 V$TIMEZONE_NAMES 中找到

Not applicable

TIMEZONE_ABBR

在动态性能视图 V$TIMEZONE_NAMES 中找到

Not applicable

PL/SQL Large Object (LOB) Data Types

大型对象 (LOB) 数据类型是指大型数据项，例如文本、图形图像、视频剪辑和声音波形。LOB 数据类型允许高效、随机、分块访问这些数据。以下是预定义的 PL/SQL LOB 数据类型 −

Data Type

Description

Size

BFILE

用于将大型二进制对象存储在数据库外部的操作系统文件中。

系统相关。不能超过 4 千兆字节 (GB)。

BLOB

用于在数据库中存储较大的二进制对象。

8 到 128 太字节 (TB)

CLOB

用于在数据库中存储较大的字符数据块。

8 to 128 TB

NCLOB

用于在数据库中存储较大的 NCHAR 数据块。

8 to 128 TB

PL/SQL User-Defined Subtypes

子类型是其他数据类型的子集，后者称为其基础类型。子类型具有与其基础类型相同有效的操作，但其有效值仅为其子集。

PL/SQL 在包 STANDARD 中预定义了几个子类型。例如，PL/SQL 预定义了子类型 CHARACTER 和 INTEGER ，如下所示：

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);

您可以定义和使用您自己的子类型。下述程序说明了如何定义和使用用户定义的子类型：

DECLARE
   SUBTYPE name IS char(20);
   SUBTYPE message IS varchar2(100);
   salutation name;
   greetings message;
BEGIN
   salutation := 'Reader ';
   greetings := 'Welcome to the World of PL/SQL';
   dbms_output.put_line('Hello ' || salutation || greetings);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Hello Reader Welcome to the World of PL/SQL

PL/SQL procedure successfully completed.

NULLs in PL/SQL

PL/SQL NULL 值表示 missing 或 unknown data ，它们不是整数、字符或任何其他特定数据类型。请注意， NULL 与空数据字符串或空字符值 '\0' 不同。可以赋值 NULL，但它不能与任何内容相等，包括它自身。

PL/SQL - Variables

在本章中，我们将讨论 Pl/SQL 中的变量。变量不过是赋予存储区域的一个名称，存储区域可以被我们的程序操作。PL/SQL 中的每个变量都有一个特定的数据类型，此数据类型确定变量内存的大小和布局；可存储在此内存中的值的范围；以及可应用于变量的操作集。

PL/SQL 变量的名称由一个字母组成（可以后跟更多的字母、数字、美元符号、下划线和数字符号），且不应超过 30 个字符。默认情况下，变量名称不区分大小写。你不能使用保留的 PL/SQL 关键字作为变量名称。

PL/SQL 编程语言允许定义各种类型的变量，例如日期时间数据类型、记录、集合等，我们将在后续的章节中介绍这些内容。对于本章，我们只研究基本变量类型。

Variable Declaration in PL/SQL

PL/SQL 变量必须在声明部分中声明，或在包中声明为全局变量。当你声明变量时，PL/SQL 将为变量的值分配内存，并且存储位置由变量名称识别。

声明变量的语法为：

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

其中，variable_name 是 PL/SQL 中的有效标识符，datatype 必须是有效的 PL/SQL 数据类型或任何用户定义的数据类型，我们已经在上一章中讨论过了。下面显示了一些有效的变量声明及其定义：

sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);

当你在数据类型中提供大小、范围或精度限制时，这被称为 constrained declaration 。受限声明需要的内存比不受限声明少。例如：

sales number(10, 2);
name varchar2(25);
address varchar2(100);

Initializing Variables in PL/SQL

每当你声明一个变量时，PL/SQL 都会为其分配默认值 NULL。如果你想使用非 NULL 值初始化变量，你可以在声明期间使用以下任意一种方法来执行此操作：

  1. The DEFAULT keyword

  2. The assignment operator

例如 -

counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';

你还可以使用 NOT NULL 约束指定变量不应该具有 NULL 值。如果你使用 NOT NULL 约束，你必须明确针对该变量指定一个初始值。

正确初始化变量是一种良好的编程实践，否则，程序有时会产生意外结果。试用以下示例，它使用了各种类型的变量：

DECLARE
   a integer := 10;
   b integer := 20;
   c integer;
   f real;
BEGIN
   c := a + b;
   dbms_output.put_line('Value of c: ' || c);
   f := 70.0/3.0;
   dbms_output.put_line('Value of f: ' || f);
END;
/

执行上述代码后，将生成以下结果 −

Value of c: 30
Value of f: 23.333333333333333333

PL/SQL procedure successfully completed.

Variable Scope in PL/SQL

PL/SQL 允许块的嵌套，即每个程序块都可以包含另一个内部块。如果一个变量在内部块中声明，则外部块无法访问该变量。但是，如果一个变量在外部块中声明且可访问，则所有嵌套内部块也可以访问该变量。有两种类型的变量作用域 -

  1. Local variables − 在内部块中声明且外部块无法访问的变量。

  2. Global variables − 在最外部块或包中声明的变量。

下面示例以简单形式说明了 Local 和 Global 变量的用法 −

DECLARE
   -- Global variables
   num1 number := 95;
   num2 number := 85;
BEGIN
   dbms_output.put_line('Outer Variable num1: ' || num1);
   dbms_output.put_line('Outer Variable num2: ' || num2);
   DECLARE
      -- Local variables
      num1 number := 195;
      num2 number := 185;
   BEGIN
      dbms_output.put_line('Inner Variable num1: ' || num1);
      dbms_output.put_line('Inner Variable num2: ' || num2);
   END;
END;
/

执行上述代码后，将生成以下结果 −

Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

PL/SQL procedure successfully completed.

Assigning SQL Query Results to PL/SQL Variables

可以使用 SQL 的 SELECT INTO 语句为 PL/SQL 变量赋值。 SELECT list 中的每个项目都必须在 INTO list 中有一个相应的类型兼容变量。以下示例说明了这个概念。让我们创建一个名为 CUSTOMERS 的表 −

( For SQL statements, please refer to the SQL tutorial )

CREATE TABLE CUSTOMERS(
   ID   INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Table Created

现在让我们在表中插入一些值 −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

以下程序使用 SQL 的 SELECT INTO clause 将上述表中的值分配给 PL/SQL 变量 −

DECLARE
   c_id customers.id%type := 1;
   c_name  customers.name%type;
   c_addr customers.address%type;
   c_sal  customers.salary%type;
BEGIN
   SELECT name, address, salary INTO c_name, c_addr, c_sal
   FROM customers
   WHERE id = c_id;
   dbms_output.put_line
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/

执行上述代码后，将生成以下结果 −

Customer Ramesh from Ahmedabad earns 2000

PL/SQL procedure completed successfully

PL/SQL - Constants and Literals

在本章中，我们将讨论 PL/SQL 中的 constants 和 literals 。常量保存的值一旦声明，就不会在程序中更改。常量声明指定其名称、数据类型和值，并为其分配存储。声明还可以施加 NOT NULL constraint 。

Declaring a Constant

常量使用 CONSTANT 关键字声明。它需要一个初始值，并且不允许更改该值。例如 −

PI CONSTANT NUMBER := 3.141592654;
DECLARE
   -- constant declaration
   pi constant number := 3.141592654;
   -- other declarations
   radius number(5,2);
   dia number(5,2);
   circumference number(7, 2);
   area number (10, 2);
BEGIN
   -- processing
   radius := 9.5;
   dia := radius * 2;
   circumference := 2.0 * pi * radius;
   area := pi * radius * radius;
   -- output
   dbms_output.put_line('Radius: ' || radius);
   dbms_output.put_line('Diameter: ' || dia);
   dbms_output.put_line('Circumference: ' || circumference);
   dbms_output.put_line('Area: ' || area);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Radius: 9.5
Diameter: 19
Circumference: 59.69
Area: 283.53

Pl/SQL procedure successfully completed.

The PL/SQL Literals

文字是一个明确的数字、字符、字符串或布尔值，而不是由标识符表示。例如，TRUE、786、NULL、'tutorialspoint' 都是布尔、数字或字符串类型的文字。在 PL/SQL 中，文字区分大小写。PL/SQL 支持以下类型的文字 −

  1. Numeric Literals

  2. Character Literals

  3. String Literals

  4. BOOLEAN Literals

  5. Date and Time Literals

下表提供以上所有类文字值的示例。

S.No

Literal Type & Example

1

Numeric Literals 050 78 -14 0 +327676.6667 0.0 -12.0 3.14159 +7800.006E5 1.0E-8 3.14159e0 -1E38 -9.5e-3

2

Character Literals 'A' '%' '9' ' ' 'z' '('

3

String Literals 'Hello, world!' 'Tutorials Point' '19-NOV-12'

4

BOOLEAN Literals TRUE、FALSE 和 NULL。

5

Date and Time Literals DATE '1978-12-25';TIMESTAMP '2012-10-29 12:01:01';

要在字符串文字中嵌入单引号，请像以下程序所示将两个单引号并排放置 −

DECLARE
   message  varchar2(30):= 'That''s tutorialspoint.com!';
BEGIN
   dbms_output.put_line(message);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

That's tutorialspoint.com!

PL/SQL procedure successfully completed.

PL/SQL - Operators

在本章中，我们将讨论 PL/SQL 中的操作符。操作符是一个符号，告诉编译器执行特定的数学或逻辑操作。PL/SQL 语言中内置了丰富的操作符，并提供了以下类型的操作符 −

  1. Arithmetic operators

  2. Relational operators

  3. Comparison operators

  4. Logical operators

  5. String operators

在这里，我们将逐个了解算术、关系、比较和逻辑操作符。字符串操作符将在本章节后面讨论 − PL/SQL - Strings 。

Arithmetic Operators

以下表格显示了 PL/SQL 支持的所有算术运算符。我们假设 variable A 含 10， variable B 含 5，则 −

Show Examples

Operator

Description

Example

+

Adds two operands

A + B 将产生 15

-

从第一个操作数中减去第二个操作数

A - B 将产生 5

*

Multiplies both operands

A * B 将产生 50

/

Divides numerator by de-numerator

A / B 将产生 2

**

指数运算符，将一个操作数提升到另一个操作数的幂

A ** B 将产生 100000

Relational Operators

关系运算符比较两个表达式或值并返回布尔结果。以下表格显示了 PL/SQL 支持的所有关系运算符。我们假设 variable A 含 10， variable B 含 20，则 −

Show Examples

Operator

Description

Example

=

检查两个操作数的值是否相等，如果相等，则条件变为真。

(A = B) 为假。

!= <> ~=

检查两个操作数的值是否相等，如果值不相等，则条件变为真。

(A != B) 为真。

>

检查左操作数的值是否大于右操作数的值，如果大于，则条件变为真。

(A > B) 不为真。

<

检查左操作数的值是否小于右操作数的值，如果小于，则条件变为真。

(A < B) 为真。

>=

检查左操作数的值是否大于或等于右操作数的值，如果大于或等于，则条件变为真。

(A >= B) 不为真。

⇐

检查左操作数的值是否小于或等于右操作数的值，如果小于或等于，则条件变为真。

(A ⇐ B) 为真

Comparison Operators

比较运算符用于将一个表达式与另一个表达式进行比较。结果始终为 TRUE, FALSE 或 NULL 。

Show Examples

Operator

Description

Example

LIKE

LIKE 运算符将一个字符、字符串或 CLOB 值与一个模式进行比较，如果该值与该模式匹配，则返回 TRUE，如果不匹配，则返回 FALSE。

如果 'Zara Ali' like 'Z% A_i' 返回布尔 true，则 'Nuha Ali' like 'Z% A_i' 返回布尔 false。

BETWEEN

BETWEEN 运算符测试一个值是否位于指定范围内。x BETWEEN a AND b 意味着 x >= a 且 x ⇐ b。

如果 x = 10，则 x between 5 and 20 返回 true，x between 5 and 10 返回 true，但 x between 11 and 20 返回 false。

IN

IN 运算符测试集合成员资格。x IN (set) 意味着 x 等于 set 的任何成员。

如果 x = 'm'，则 x in ('a', 'b', 'c') 返回布尔 false，但 x in ('m', 'n', 'o') 返回布尔 true。

IS NULL

IS NULL 运算符在其操作数为空值时返回 BOOLEAN 值 TRUE，否则返回 FALSE。涉及空值比较时总是生成空值。

如果 x = 'm'，则 'x is null' 返回布尔值 false。

Logical Operators

下表显示 PL/SQL 支持的逻辑运算符。所有这些运算符都针对布尔操作数工作，并产生布尔结果。让我们假设 variable A 为真， variable B 为假，那么 −

Show Examples

Operator

Description

Examples

and

称为逻辑 AND 运算符。如果两个操作数都为真，则条件变为真。

(A and B) 为假。

or

称为逻辑 OR 运算符。如果两个操作数中的任何一个为真，则条件变为真。

(A or B) 为真。

not

称为逻辑 NOT 运算符。用于反转其操作数的逻辑状态。如果一个条件为真，则逻辑 NOT 运算符会使其变为假。

not (A and B) 为真。

PL/SQL Operator Precedence

运算符优先级确定表达式中项的分组。这会影响表达式的求值方式。某些运算符比其他运算符具有更高的优先级；例如，乘法运算符的优先级高于加法运算符。

例如， x = 7 + 3 * 2 ；在这里， x 被赋值为 13 ，而不是 20，因为运算符 * 的优先级高于 +，因此它首先与 3*2 相乘，然后再加到 7 中。

此处，优先级最高的运算符显示在表顶部，优先级最低的运算符显示在表底部。在表达式中，将首先评估优先级较高的运算符。

运算符的优先级如下：=、<、>、⇐、>=、<>、!=、~=、^=、IS NULL、LIKE、BETWEEN、IN。

Show Examples

Operator

Operation

**

exponentiation

+, -

identity, negation

*, /

multiplication, division

+, -,

addition, subtraction, concatenation

comparison

NOT

logical negation

AND

conjunction

OR

inclusion

PL/SQL - Conditions

在本章中，我们将讨论 PL/SQL 中的条件。决策结构要求程序员指定一个或多个条件，以便由程序对其进行求值或测试，如果确定条件为真，则执行一条或多条语句，并且如果确定条件为假，则可以选择执行其他语句。

以下是大多数编程语言中常见的条件（即决策制定）结构的通用形式 −

decision making

PL/SQL 编程语言提供了以下类型的决策语句。单击以下链接查看其详细信息。

S.No

Statement & Description

1

IF - THEN statement IF statement 将条件与由关键词 THEN 和 END IF 括起来的语句序列关联起来。如果条件为真，则会执行语句，如果条件为假或空，则 IF 语句不起作用。

2

IF-THEN-ELSE statement IF statement 添加了关键词 ELSE ，后面紧跟另一条语句序列。如果条件为假或空，则只会执行另一条语句序列。它确保执行语句序列的任何一个。

3

IF-THEN-ELSIF statement 它允许您从几个备选方案中进行选择。

4

Case statement 和 IF 语句一样， CASE statement 选择一组要执行的语句。但是，为了选择序列，CASE 语句使用选择器，而不是多个布尔表达式。选择器是一个表达式，其值用于从几个备选方案中选择一个。

5

Searched CASE statement 被搜索的 CASE 语句 has no selector ，还有它 WHEN 子句中搜索条件生成布尔值。

6

nested IF-THEN-ELSE 您可以在另一个 IF-THEN 或 IF-THEN-ELSIF 语句中使用一个 IF-THEN 或 IF-THEN-ELSIF 语句。

PL/SQL - Loops

在本章中，我们将讨论 PL/SQL 中的循环。在很多情况下，需要多次执行代码块。通常，语句按顺序执行：函数中的第一个语句最先执行，然后是第二个，依此类推。

编程语言提供了各种控制结构，允许执行更复杂的路径。

循环语句允许我们多次执行一个语句或一组语句，以下是大多数编程语言中循环语句的一般形式 −

loop architecture

PL/SQL 提供以下类型的循环来处理循环要求。点击以下链接查看其详细信息。

S.No

Loop Type & Description

1

PL/SQL Basic LOOP 在此循环结构中，语句序列包含在 LOOP 和 END LOOP 语句之间。在每次迭代中，执行语句序列，然后在循环顶部继续控制。

2

PL/SQL WHILE LOOP 在一个给定的条件为真时重复一个语句或一组语句。在执行循环体之前，它会测试该条件。

3

PL/SQL FOR LOOP 多次执行一组语句，并缩短管理循环变量的代码。

4

Nested loops in PL/SQL 您可以在任何另一个基本循环、while 循环或 for 循环中使用一个或多个循环。

Labeling a PL/SQL Loop

可以标记 PL/SQL 循环。该标签应由双尖括号（<< 和 >>）括起来，并出现在 LOOP 语句的开头。标签名称也可以出现在 LOOP 语句的末尾。您可以在 EXIT 语句中使用该标签退出循环。

以下程序说明了这个概念 −

DECLARE
   i number(1);
   j number(1);
BEGIN
   << outer_loop >>
   FOR i IN 1..3 LOOP
      << inner_loop >>
      FOR j IN 1..3 LOOP
         dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
      END loop inner_loop;
   END loop outer_loop;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

i is: 1 and j is: 1
i is: 1 and j is: 2
i is: 1 and j is: 3
i is: 2 and j is: 1
i is: 2 and j is: 2
i is: 2 and j is: 3
i is: 3 and j is: 1
i is: 3 and j is: 2
i is: 3 and j is: 3

PL/SQL procedure successfully completed.

The Loop Control Statements

循环控制语句改变了它在正常序列中的执行。当执行退出一个作用域时，在该作用域中创建的所有自动对象会被销毁。

PL/SQL 支持以下控制语句。标记循环还有助于控制循环外部。点击以下链接查看其详细信息。

S.No

Control Statement & Description

1

EXIT statement Exit 语句完成循环，控制权将传递到 END LOOP 之后的语句。

2

CONTINUE statement 导致循环跳过其主体的其余部分，然后在再次迭代之前立即重新测试其条件。

3

GOTO statement 将控制权传输到标记语句。虽然不建议在程序中使用 GOTO 语句。

PL/SQL - Strings

PL/SQL 中的字符串实际上是一系列字符，可选择指定大小。这些字符可以是数字、字母、空格、特殊字符或它们的组合。PL/SQL 提供了三种字符串——

  1. Fixed-length strings ——在这种字符串中，程序员在声明字符串时指定长度。该字符串将右填充空格到指定的长度。

  2. Variable-length strings ——在这种字符串中，指定字符串的最大长度（最长为 32,767），不会进行填充。

  3. Character large objects (CLOBs) ——这些是可变长度字符串，最大可达 128 TB。

PL/SQL 字符串可以是变量或文本。字符串文本用引号括起来。例如，

'This is a string literal.' Or 'hello world'

要在一串文字中包含一个单引号，您需要将两个单引号放在一起。例如，

'this isn''t what it looks like'

Declaring String Variables

Oracle 数据库提供了多种字符串数据类型，例如 CHAR、NCHAR、VARCHAR2、NVARCHAR2、CLOB 和 NCLOB。前缀为 'N' 的数据类型是 'national character set' 数据类型，用于存储 Unicode 字符数据。

如果您需要声明一个可变长度字符串，您必须提供该字符串的最大长度。例如，VARCHAR2 数据类型。以下示例说明如何声明并使用一些字符串变量——

DECLARE
   name varchar2(20);
   company varchar2(30);
   introduction clob;
   choice char(1);
BEGIN
   name := 'John Smith';
   company := 'Infotech';
   introduction := ' Hello! I''m John Smith from Infotech.';
   choice := 'y';
   IF choice = 'y' THEN
      dbms_output.put_line(name);
      dbms_output.put_line(company);
      dbms_output.put_line(introduction);
   END IF;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

John Smith
Infotech
Hello! I'm John Smith from Infotech.

PL/SQL procedure successfully completed

若要声明定长字符串，请使用 CHAR 数据类型。在此处，您不必为定长变量指定最大长度。如果您省略长度约束，Oracle Database 会自动使用所需的最小长度。下列两个声明是相同的 −

red_flag CHAR(1) := 'Y';
 red_flag CHAR   := 'Y';

PL/SQL String Functions and Operators

PL/SQL 提供连接符运算符 (||) ，用于连接两个字符串。下表提供了 PL/SQL 提供的字符串函数 −

S.No

Function & Purpose

1

ASCII(x); 返回字符 x 的 ASCII 值。

2

CHR(x); 返回 ASCII 值为 x 的字符。

3

CONCAT(x, y); 连接字符串 x 和 y 并返回附加的字符串。

4

INITCAP(x); 将 x 中每个单词的第一个字母转换为大写并返回该字符串。

5

INSTR(x, find_string [, start] [, occurrence]); 在 x 中搜索 find_string 并返回其出现的位置。

6

INSTRB(x); 返回字符串中另一个字符串的位置，但以字节方式返回该值。

7

LENGTH(x); 返回 x 中的字符数。

8

LENGTHB(x); 以字节为单位返回单字节字符集中字符字符串的长度。

9

LOWER(x); 将 x 中的字母转换为小写并返回该字符串。

10

LPAD(x, width [, pad_string]) ; 使用空格填充 x ，以使字符串的总长度达到 width 个字符。

11

LTRIM(x [, trim_string]); 从 x 的左侧修剪字符。

12

NANVL(x, value); 如果 x 匹配 NaN 特殊值（非数字），则返回 value，否则返回 x 。

13

NLS_INITCAP(x); 与 INITCAP 函数相同，除了它可以使用 NLSSORT 指定的不同排序方法。

14

NLS_LOWER(x) ; 与 LOWER 函数相同，除了它可以使用 NLSSORT 指定的不同排序方法。

15

NLS_UPPER(x); 与 UPPER 函数相同，除了它可以使用 NLSSORT 指定的不同排序方法。

16

NLSSORT(x); 更改排序字符的方法。必须在任何 NLS 函数之前指定；否则，将使用默认排序。

17

NVL(x, value); 如果 x 为 null，则返回 value；否则，返回 x。

18

NVL2(x, value1, value2); 如果 x 不为 null，则返回 value1；如果 x 为 null，则返回 value2。

19

REPLACE(x, search_string, replace_string); 在 x 中搜索 search_string 并用 replace_string 替换它。

20

RPAD(x, width [, pad_string]); 填充 x 。

21

RTRIM(x [, trim_string]); 从右边 x 修剪。

22

SOUNDEX(x) ; 返回包含 x 的语音表示的字符串。

23

SUBSTR(x, start [, length]); 返回从开始指定的位置开始的 x 的子串。可以提供子串的可选长度。

24

SUBSTRB(x); 与 SUBSTR 相同，不同之处在于对于单字节字符系统，参数以字节而不是字符表示。

25

TRIM([trim_char FROM) x); 从 x 的左边和右边修剪字符。

26

UPPER(x); 将 x 中的字母转换为大写并返回该字符串。

现在让我们通过几个示例来理解这个概念 -

Example 1

DECLARE
   greetings varchar2(11) := 'hello world';
BEGIN
   dbms_output.put_line(UPPER(greetings));

   dbms_output.put_line(LOWER(greetings));

   dbms_output.put_line(INITCAP(greetings));

   /* retrieve the first character in the string */
   dbms_output.put_line ( SUBSTR (greetings, 1, 1));

   /* retrieve the last character in the string */
   dbms_output.put_line ( SUBSTR (greetings, -1, 1));

   /* retrieve five characters,
      starting from the seventh position. */
   dbms_output.put_line ( SUBSTR (greetings, 7, 5));

   /* retrieve the remainder of the string,
      starting from the second position. */
   dbms_output.put_line ( SUBSTR (greetings, 2));

   /* find the location of the first "e" */
   dbms_output.put_line ( INSTR (greetings, 'e'));
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

HELLO WORLD
hello world
Hello World
h
d
World
ello World
2

PL/SQL procedure successfully completed.

Example 2

DECLARE
   greetings varchar2(30) := '......Hello World.....';
BEGIN
   dbms_output.put_line(RTRIM(greetings,'.'));
   dbms_output.put_line(LTRIM(greetings, '.'));
   dbms_output.put_line(TRIM( '.' from greetings));
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

......Hello World
Hello World.....
Hello World

PL/SQL procedure successfully completed.

PL/SQL - Arrays

在本章中，我们将讨论 PL/SQL 中的数组。PL/SQL 编程语言提供了一个称为 VARRAY 的数据结构，它可以存储相同类型元素的固定大小顺序集合。varray 用于存储有序数据集合，但是往往最好将数组视为相同类型变量的集合。

所有 varray 均由连续的内存位置组成。最低地址对应于第一个元素，最高地址对应于最后一个元素。

varrays

数组是集合类型数据的一部分，它代表可变大小的数组。我们将在后面一章中学习其他集合类型 'PL/SQL Collections' 。

varray 中的每个元素都有一个与之关联的索引。它还具有可以动态更改的最大大小。

Creating a Varray Type

Varray 类型由 CREATE TYPE 语句创建。你必须指定 varray 中存储的最大大小和元素类型。

在模式级别创建 VARRAY 类型的基本句法是 −

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

其中，

  1. varray_type_name 是一个有效的属性名，

  2. n 是 varray 中的元素数（最大值），

  3. element_type 是数组元素的数据类型。

可以使用 ALTER TYPE 语句更改 varray 的最大大小。

例如，

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);
/

Type created.

在 PL/SQL 块中创建 VARRAY 类型的基本语法是 −

TYPE varray_type_name IS VARRAY(n) of <element_type>

例如 -

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;

现在让我们通过几个示例来理解这个概念 -

Example 1

以下程序说明了 varray 的使用 −

DECLARE
   type namesarray IS VARRAY(5) OF VARCHAR2(10);
   type grades IS VARRAY(5) OF INTEGER;
   names namesarray;
   marks grades;
   total integer;
BEGIN
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i in 1 .. total LOOP
      dbms_output.put_line('Student: ' || names(i) || '
      Marks: ' || marks(i));
   END LOOP;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Total 5 Students
Student: Kavita  Marks: 98
Student: Pritam  Marks: 97
Student: Ayan  Marks: 78
Student: Rishav  Marks: 87
Student: Aziz  Marks: 92

PL/SQL procedure successfully completed.

Please note −

  1. 在 Oracle 环境中，varray 的起始索引始终为 1。

  2. 你可以使用与 varray 同名的 varray 类型的构造方法来初始化 varray 元素。

  3. Varrays are one-dimensional arrays.

  4. varray 在声明时会自动变为 NULL，并且必须在引用其元素之前对其进行初始化。

Example 2

varray 的元素也可以是数据库表的任何 %ROWTYPE 或数据库表字段的任何 %TYPE。以下示例对该概念进行了说明。

我们将使用存储在数据库中的 CUSTOMERS 表，如下所示 −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

下面的示例使用 cursor ，你将在另一章中详细学习它。

DECLARE
   CURSOR c_customers is
   SELECT  name FROM customers;
   type c_list is varray (6) of customers.name%type;
   name_list c_list := c_list();
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter + 1;
      name_list.extend;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter));
   END LOOP;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed.

PL/SQL - Procedures

在本章中，我们将讨论 PL/SQL 中的过程。 subprogram 是执行特定任务的程序单元/模块。这些子程序组合在一起形成更大的程序。这基本上被称为“模块化设计”。可以通过称为 calling program 的另一个子程序或程序来调用子程序。

可以创建子程序 −

  1. At the schema level

  2. Inside a package

  3. Inside a PL/SQL block

在模式级别，子程序是 standalone subprogram 。它使用 CREATE PROCEDURE 或 CREATE FUNCTION 语句创建。它存储在数据库中，可以使用 DROP PROCEDURE 或 DROP FUNCTION 语句进行删除。

在包内创建的子程序是 packaged subprogram 。它存储在数据库中，并且仅当包使用 DROP PACKAGE 语句删除时才能删除。我们将在 'PL/SQL - Packages' 章中讨论包。

PL/SQL 子程序是带有一组参数的命名的 PL/SQL 块，可以使用这些参数对其进行调用。PL/SQL 提供两种子程序 −

  1. Functions − 这些子程序返回单个值；主要用于计算并返回一个值。

  2. Procedures − 这些子程序不直接返回值；主要用于执行一项操作。

本章将介绍 PL/SQL procedure 的重要方面。我们将在下一章讨论 PL/SQL function 。

Parts of a PL/SQL Subprogram

每个 PL/SQL 子程序都有一个名称，还可以有一个参数列表。与匿名 PL/SQL 块一样，命名的块还将具有以下三部分 −

S.No

Parts & Description

1

Declarative Part 是可选部分。但是，子程序的声明部分不以 DECLARE 关键字开头。其中包含类型、游标、常量、变量、异常和嵌套子程序的声明。这些项对于子程序来说是本地的，并且在子程序完成执行时停止存在。

2

Executable Part 是强制部分，并且包含执行指定操作的语句。

3

Exception-handling 又是一个可选部分。其中包含处理运行时错误的代码。

Creating a Procedure

使用 CREATE OR REPLACE PROCEDURE 语句来创建过程。CREATE OR REPLACE PROCEDURE 语句的简化语法如下 −

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  < procedure_body >
END procedure_name;

其中，

  1. procedure-name 指定过程的名称。

  2. [OR REPLACE] 选项允许修改现有过程。

  3. 可选参数列表包含名称、模式和参数的类型。 IN 表示将从外部传递的值，OUT 表示用于在过程外部返回值的参数。

  4. procedure-body 包含可执行部分。

  5. 使用 AS 关键字而不是 IS 关键字来创建独立过程。

Example

以下示例创建了一个简单过程，在执行时，会在屏幕上显示字符串“Hello World!”。

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;
/

当使用 SQL 提示执行以上代码时，它将会生成以下结果 −

Procedure created.

Executing a Standalone Procedure

独立过程可以通过两种方式来调用 −

  1. Using the EXECUTE keyword

  2. 从 PL/SQL 块调用过程的名称

通过 EXECUTE 关键字可以调用名为 'greetings' 的上述过程，如下所示 −

EXECUTE greetings;

上述调用将显示 −

Hello World

PL/SQL procedure successfully completed.

这个过程也可从另一个 PL/SQL 块调用 -

BEGIN
   greetings;
END;
/

上述调用将显示 −

Hello World

PL/SQL procedure successfully completed.

Deleting a Standalone Procedure

使用 DROP PROCEDURE 语句删除独立过程。语法用于删除过程是 -

DROP PROCEDURE procedure-name;

你可以使用以下语句来删除 greetings 过程 -

DROP PROCEDURE greetings;

Parameter Modes in PL/SQL Subprograms

在 PL/SQL 子程序中输出参数模式列表 -

S.No

Parameter Mode & Description

1

IN IN 参数让你可以将值传递给子程序。 It is a read-only parameter 。在子程序中，IN 参数用作常量。不能为其分配值。你可以将常量、文本、已初始化变量或表达式作为 IN 参数传递。你也可以为其初始化一个默认值，但如果其在子程序调用里，它将省略。 It is the default mode of parameter passing. Parameters are passed by reference 。

2

OUT OUT 参数向调用程序返回一个值。在子程序中，OUT 参数用作变量。你能改变它的值并在分配后参考该值。 The actual parameter must be variable and it is passed by value 。

3

IN OUT IN OUT 参数向子程序传递一个初始值并向调用者返回一个更新的值。可以为其分配一个值并可以读取该值。对应于 IN OUT 形式参数的实际参数必须是变量，而不是常量或表达式。必须为形式参数分配值。 Actual parameter is passed by value. 。

IN & OUT Mode Example 1

此程序查找两个值的最小值。此处，过程使用 IN 模式采用两个数字，并使用 OUT 参数返回它们的最小值。

DECLARE
   a number;
   b number;
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END;
BEGIN
   a:= 23;
   b:= 45;
   findMin(a, b, c);
   dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Minimum of (23, 45) : 23

PL/SQL procedure successfully completed.

IN & OUT Mode Example 2

此过程计算传递值值的平方。这个例子显示了我们如何可以使用相同参数来接受一个值，然后返回另一个结果。

DECLARE
   a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
  x := x * x;
END;
BEGIN
   a:= 23;
   squareNum(a);
   dbms_output.put_line(' Square of (23): ' || a);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Square of (23): 529

PL/SQL procedure successfully completed.

Methods for Passing Parameters

实际参数可以通过三种方式传递 -

  1. Positional notation

  2. Named notation

  3. Mixed notation

Positional Notation

在位置符号中，你可以调用过程为 -

findMin(a, b, c, d);

在位置符号中，第一个实际参数替换了第一个形式参数；第二个实际参数替换了第二个形式参数，以此类推。因此， a 替换为 x, b 替换为 y, c 替换为 z 替换 d 替换为 m 。

Named Notation

在已命名符号中，实际参数与形式参数使用 arrow symbol ( ⇒ ) 关联。过程调用将像以下示例 -

findMin(x => a, y => b, z => c, m => d);

Mixed Notation

在混合符号中，你可以在过程调用中混合这两种符号；但位置符号应先于已命名符号。

以下调用是合法的 -

findMin(a, b, c, m => d);

然而，这是不合法的：

findMin(x => a, b, c, d);

PL/SQL - Functions

在本章中，我们将讨论 PL/SQL 中的函数。函数与过程相同，只是函数会返回值。因此，前一章的所有讨论也适用于函数。

Creating a Function

使用 CREATE FUNCTION 语句创建独立函数。 CREATE OR REPLACE PROCEDURE 语句的简化语法如下：

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
   < function_body >
END [function_name];

其中，

  1. function-name 指定函数的名称。

  2. [OR REPLACE] 选项允许修改现有函数。

  3. 可选参数列表包含参数的名称、模式和类型。IN 表示将从外部传递的值，而 OUT 表示将用于在过程外部返回值的参数。

  4. 该函数必须包含 return 语句。

  5. RETURN 子句指定要从函数返回的数据类型。

  6. function-body 包含可执行部分。

  7. 创建独立函数时，使用 AS 关键字代替 IS 关键字。

Example

以下示例说明如何创建和调用独立函数。此函数返回 customers 表中 CUSTOMER 的总数。

我们将使用我们创建在 PL/SQL Variables 一章中的 CUSTOMERS 表 −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
   total number(2) := 0;
BEGIN
   SELECT count(*) into total
   FROM customers;

   RETURN total;
END;
/

当使用 SQL 提示执行以上代码时，它将会生成以下结果 −

Function created.

Calling a Function

在创建函数时，你需要提供函数将要完成的功能的定义。要使用函数，你必须调用此函数执行已定义的任务。当程序调用函数时，对此被调用函数实施程序控制。

一个被调用的函数执行定义的任务，当它的返回语句执行或 last end statement 达到时，它将程序控制权返回给主程序。

要调用函数，你只需传回所需参数和函数名字，如果函数返回一个值，那么你便可以储存返回的值。下列程序从匿名代码组调用了函数 totalCustomers −

DECLARE
   c number(2);
BEGIN
   c := totalCustomers();
   dbms_output.put_line('Total no. of Customers: ' || c);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Total no. of Customers: 6

PL/SQL procedure successfully completed.

Example

以下的例子显示如何用 PL/SQL 函数宣布、定义和启动一个简单的 PL/SQL 函数，它能计算并返回两个值中的最大值。

DECLARE
   a number;
   b number;
   c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
    z number;
BEGIN
   IF x > y THEN
      z:= x;
   ELSE
      Z:= y;
   END IF;
   RETURN z;
END;
BEGIN
   a:= 23;
   b:= 45;
   c := findMax(a, b);
   dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Maximum of (23,45): 45

PL/SQL procedure successfully completed.

PL/SQL Recursive Functions

我们已经看到，一个程序或子程序可能会调用另一个子程序。当一个子程序调用它自己，它称之为递归调用并且此过程称为 recursion 。

为了说明这个概念，让我们计算一个数字的阶乘。一个数字 n 的阶乘被定义为 −

n! = n*(n-1)!
   = n*(n-1)*(n-2)!
      ...
   = n*(n-1)*(n-2)*(n-3)... 1

以下的程序通过递归地调用它自己来计算一个给定数字的阶乘 −

DECLARE
   num number;
   factorial number;

FUNCTION fact(x number)
RETURN number
IS
   f number;
BEGIN
   IF x=0 THEN
      f := 1;
   ELSE
      f := x * fact(x-1);
   END IF;
RETURN f;
END;

BEGIN
   num:= 6;
   factorial := fact(num);
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Factorial 6 is 720

PL/SQL procedure successfully completed.

PL/SQL - Cursors

在本章中，我们将讨论 PL/SQL 中的游标。Oracle 创建一个名为上下文的内存区域以处理 SQL 语句，该上下文中包含处理该语句所需的所有信息；例如，已处理的行数等。

cursor 是此上下文区域的一个指针。PL/SQL 通过游标控制上下文区域。游标保存 SQL 语句返回的行（一个或多个）。游标保存的行集称为 active set 。

您可以为游标命名，以便在程序中引用它来获取和处理 SQL 语句返回的行，每次一行。有两种类型的游标：

  1. Implicit cursors

  2. Explicit cursors

Implicit Cursors

当为语句没有显式游标时，在执行 SQL 语句时，Oracle 始终自动创建隐式游标。程序员无法控制隐式游标及其信息。

每当发出 DML 语句（INSERT、UPDATE 和 DELETE）时，都会有一个隐式游标与该语句相关联。对于 INSERT 操作，游标保存需要插入的数据。对于 UPDATE 和 DELETE 操作，游标标识将受到影响的行。

在 PL/SQL 中，您可以将最近的隐式游标称为 SQL cursor ，它始终具有 %FOUND, %ISOPEN, %NOTFOUND 和 %ROWCOUNT 等属性。SQL 游标有 %BULK_ROWCOUNT 和 %BULK_EXCEPTIONS 等附加属性，设计用于与 FORALL 语句一起使用。下表提供了最常用属性的说明−

S.No

Attribute & Description

1

如果 INSERT、UPDATE 或 DELETE 语句影响了一行或多行，或 SELECT INTO 语句返回了一行或多行，则返回 TRUE。否则，返回 FALSE。

2

%FOUND 的逻辑相反。如果 INSERT、UPDATE 或 DELETE 语句未影响任何行，或 SELECT INTO 语句未返回任何行，则返回 TRUE。否则，返回 FALSE。

3

对于隐式游标，始终返回 FALSE，因为 Oracle 在执行其关联的 SQL 语句后会自动关闭 SQL 游标。

4

返回受 INSERT、UPDATE 或 DELETE 语句影响的行数，或 SELECT INTO 语句返回的行数。

任何 SQL 游标属性将被访问为 sql%attribute_name ，如下例所示。

Example

我们将使用我们在前几章创建和使用的 CUSTOMERS 表。

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

以下程序将更新该表，并将每个客户的工资增加 500，并使用 SQL%ROWCOUNT 属性来确定受影响的行数−

DECLARE
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

6 customers selected

PL/SQL procedure successfully completed.

如果您查看 customers 表中的记录，您会发现这些行已更新−

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 |
|  2 | Khilan   |  25 | Delhi     |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2500.00 |
|  4 | Chaitali |  25 | Mumbai    |  7000.00 |
|  5 | Hardik   |  27 | Bhopal    |  9000.00 |
|  6 | Komal    |  22 | MP        |  5000.00 |
+----+----------+-----+-----------+----------+

Explicit Cursors

显式游标是程序员定义的游标，用于对 context area 获得更多控制。应在 PL/SQL Block 的声明部分中定义显式游标。它创建在返回多行的 SELECT 语句上。

创建显式游标的语法如下−

CURSOR cursor_name IS select_statement;

使用显式游标包括以下步骤−

  1. 声明游标以初始化内存

  2. 打开游标以分配内存

  3. 获取游标以检索数据

  4. 关闭游标以释放分配的内存

Declaring the Cursor

声明游标使用名称和关联的 SELECT 语句定义游标。例如−

CURSOR c_customers IS
   SELECT id, name, address FROM customers;

Opening the Cursor

打开游标为游标分配内存，并使其准备好从 SQL 语句中检索返回的行。例如，我们将打开上面定义的游标，如下所示−

OPEN c_customers;

Fetching the Cursor

获取游标包括一次访问一行。例如，我们将从已打开的游标获取行，如下所示 -

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

关闭游标意味着释放分配的内存。例如，我们将关闭已打开的游标，如下所示 -

CLOSE c_customers;

Example

以下是说明显式游标概念的完整示例；

DECLARE
   c_id customers.id%type;
   c_name customer.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   OPEN c_customers;
   LOOP
   FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

PL/SQL - Records

在本章中，我们将讨论 PL/SQL 中的记录。 record 是可以容纳不同种类数据项目的数据结构。记录由不同的字段组成，类似于数据库表中的行。

例如，您希望跟踪库中的书籍。您可能想要跟踪每本书的以下属性，例如书名、作者、主题、书本编号。包含各个项目字段的记录可以将书本作为一个逻辑单元进行处理，并允许您更好地组织和呈现其信息。

PL/SQL 可以处理以下类型的记录：

  1. Table-based

  2. Cursor-based records

  3. User-defined records

Table-Based Records

%ROWTYPE 属性使编程人员可以创建 table-based 和 cursorbased 记录。

以下示例说明了 table-based 记录的概念。我们将使用上几章中创建和使用的 CUSTOMERS 表：

DECLARE
   customer_rec customers%rowtype;
BEGIN
   SELECT * into customer_rec
   FROM customers
   WHERE id = 5;
   dbms_output.put_line('Customer ID: ' || customer_rec.id);
   dbms_output.put_line('Customer Name: ' || customer_rec.name);
   dbms_output.put_line('Customer Address: ' || customer_rec.address);
   dbms_output.put_line('Customer Salary: ' || customer_rec.salary);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Customer ID: 5
Customer Name: Hardik
Customer Address: Bhopal
Customer Salary: 9000

PL/SQL procedure successfully completed.

Cursor-Based Records

以下示例说明了 cursor-based 记录的概念。我们将使用上几章中创建和使用的 CUSTOMERS 表：

DECLARE
   CURSOR customer_cur is
      SELECT id, name, address
      FROM customers;
   customer_rec customer_cur%rowtype;
BEGIN
   OPEN customer_cur;
   LOOP
      FETCH customer_cur into customer_rec;
      EXIT WHEN customer_cur%notfound;
      DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);
   END LOOP;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

1 Ramesh
2 Khilan
3 kaushik
4 Chaitali
5 Hardik
6 Komal

PL/SQL procedure successfully completed.

User-Defined Records

PL/SQL 提供了一个用户定义的记录类型，允许您定义不同的记录结构。这些记录由不同的字段组成。假设您希望跟踪库中的书籍。您可能想要跟踪每本书的以下属性：

  1. Title

  2. Author

  3. Subject

  4. Book ID

Defining a Record

记录类型定义为：

TYPE
type_name IS RECORD
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION],
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION],
   ...
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION);
record-name  type_name;

Book 记录以以下方式声明：

DECLARE
TYPE books IS RECORD
(title  varchar(50),
   author  varchar(50),
   subject varchar(100),
   book_id   number);
book1 books;
book2 books;

Accessing Fields

要访问记录的任意字段，我们使用点 (.) 运算符。成员访问运算符被编码为记录变量名和我们希望访问的字段之间的句点。下面是一个解释记录用法的示例：

DECLARE
   type books is record
      (title varchar(50),
      author varchar(50),
      subject varchar(100),
      book_id number);
   book1 books;
   book2 books;
BEGIN
   -- Book 1 specification
   book1.title  := 'C Programming';
   book1.author := 'Nuha Ali ';
   book1.subject := 'C Programming Tutorial';
   book1.book_id := 6495407;
   -- Book 2 specification
   book2.title := 'Telecom Billing';
   book2.author := 'Zara Ali';
   book2.subject := 'Telecom Billing Tutorial';
   book2.book_id := 6495700;

  -- Print book 1 record
   dbms_output.put_line('Book 1 title : '|| book1.title);
   dbms_output.put_line('Book 1 author : '|| book1.author);
   dbms_output.put_line('Book 1 subject : '|| book1.subject);
   dbms_output.put_line('Book 1 book_id : ' || book1.book_id);

   -- Print book 2 record
   dbms_output.put_line('Book 2 title : '|| book2.title);
   dbms_output.put_line('Book 2 author : '|| book2.author);
   dbms_output.put_line('Book 2 subject : '|| book2.subject);
   dbms_output.put_line('Book 2 book_id : '|| book2.book_id);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Book 1 title : C Programming
Book 1 author : Nuha Ali
Book 1 subject : C Programming Tutorial
Book 1 book_id : 6495407
Book 2 title : Telecom Billing
Book 2 author : Zara Ali
Book 2 subject : Telecom Billing Tutorial
Book 2 book_id : 6495700

PL/SQL procedure successfully completed.

Records as Subprogram Parameters

您可以像传递任何其他变量一样将记录作为子程序参数进行传递。您还可以像在上述示例中访问记录字段一样访问记录字段：

DECLARE
   type books is record
      (title  varchar(50),
      author  varchar(50),
      subject varchar(100),
      book_id   number);
   book1 books;
   book2 books;
PROCEDURE printbook (book books) IS
BEGIN
   dbms_output.put_line ('Book  title :  ' || book.title);
   dbms_output.put_line('Book  author : ' || book.author);
   dbms_output.put_line( 'Book  subject : ' || book.subject);
   dbms_output.put_line( 'Book book_id : ' || book.book_id);
END;

BEGIN
   -- Book 1 specification
   book1.title  := 'C Programming';
   book1.author := 'Nuha Ali ';
   book1.subject := 'C Programming Tutorial';
   book1.book_id := 6495407;

   -- Book 2 specification
   book2.title := 'Telecom Billing';
   book2.author := 'Zara Ali';
   book2.subject := 'Telecom Billing Tutorial';
   book2.book_id := 6495700;

   -- Use procedure to print book info
   printbook(book1);
   printbook(book2);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Book  title : C Programming
Book  author : Nuha Ali
Book subject : C Programming Tutorial
Book  book_id : 6495407
Book title : Telecom Billing
Book author : Zara Ali
Book subject : Telecom Billing Tutorial
Book book_id : 6495700

PL/SQL procedure successfully completed.

PL/SQL - Exceptions

在本教程中，我们将讨论 PL/SQL 中的 Exceptions。Exception 是程序执行过程中的错误条件。PL/SQL 支持程序员使用程序中的 EXCEPTION 块捕获此类条件，并针对错误条件采取适当措施。Exceptions 有两种：

  1. System-defined exceptions

  2. User-defined exceptions

Syntax for Exception Handling

Exception 处理的常规语法如下。在这里，你可以列出尽可能多的异常进行处理。默认异常将使用 WHEN others THEN 进行处理：

DECLARE
   <declarations section>
BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling goes here >
   WHEN exception1 THEN
      exception1-handling-statements
   WHEN exception2  THEN
      exception2-handling-statements
   WHEN exception3 THEN
      exception3-handling-statements
   ........
   WHEN others THEN
      exception3-handling-statements
END;

Example

我们编写一个程序来说明概念。我们将使用在前面教程中创建和使用的 CUSTOMERS 表：

DECLARE
   c_id customers.id%type := 8;
   c_name customerS.Name%type;
   c_addr customers.address%type;
BEGIN
   SELECT  name, address INTO  c_name, c_addr
   FROM customers
   WHERE id = c_id;
   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

No such customer!

PL/SQL procedure successfully completed.

上述程序显示了给定 ID 的客户的姓名和地址。由于数据库中没有 ID 值为 8 的客户，因此程序引发了在 EXCEPTION block 中捕获的运行时异常 NO_DATA_FOUND 。

Raising Exceptions

当数据库服务器有任何内部错误时，它将自动引发异常，但是程序员也可以通过使用 RAISE 命令显式引发异常。以下是在引发异常的简单语法 -

DECLARE
   exception_name EXCEPTION;
BEGIN
   IF condition THEN
      RAISE exception_name;
   END IF;
EXCEPTION
   WHEN exception_name THEN
   statement;
END;

您可以在引发 Oracle 标准异常或任何用户定义的异常时使用上述语法。在下一部分中，我们将为您提供一个引发用户定义异常的示例。您可以以类似方式引发 Oracle 标准异常。

User-defined Exceptions

PL/SQL 允许您根据程序的需要定义自己的异常。必须声明用户定义的异常，然后使用 RAISE 语句或 DBMS_STANDARD.RAISE_APPLICATION_ERROR 过程显式引发异常。

声明异常的语法是 -

DECLARE
   my-exception EXCEPTION;

Example

以下示例说明了该概念。此程序询问客户 ID，当用户输入无效 ID 时，将引发异常 invalid_id 。

DECLARE
   c_id customers.id%type := &cc_id;
   c_name customerS.Name%type;
   c_addr customers.address%type;
   -- user defined exception
   ex_invalid_id  EXCEPTION;
BEGIN
   IF c_id <= 0 THEN
      RAISE ex_invalid_id;
   ELSE
      SELECT  name, address INTO  c_name, c_addr
      FROM customers
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
   END IF;

EXCEPTION
   WHEN ex_invalid_id THEN
      dbms_output.put_line('ID must be greater than zero!');
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Enter value for cc_id: -6 (let's enter a value -6)
old  2: c_id customers.id%type := &cc_id;
new  2: c_id customers.id%type := -6;
ID must be greater than zero!

PL/SQL procedure successfully completed.

Pre-defined Exceptions

PL/SQL 提供了许多预定义的异常，当程序违反任何数据库规则时，这些异常将被执行。例如，当 SELECT INTO 语句不返回任何行时，将引发预定义的异常 NO_DATA_FOUND。下表列出了几个重要的预定义异常 -

Exception

Oracle Error

SQLCODE

Description

ACCESS_INTO_NULL

06530

-6530

当将空对象自动分配了一个值时，将引发该异常。

CASE_NOT_FOUND

06592

-6592

当 CASE 语句的 WHEN 子句中没有选择任何选项，并且没有 ELSE 子句时，将引发该异常。

COLLECTION_IS_NULL

06531

-6531

当程序尝试将 EXISTS 以外の集合方法应用于未初始化的嵌套表或变长数组，或者程序尝试将值分配给未初始化的嵌套表或变长数组的元素时，将引发该异常。

DUP_VAL_ON_INDEX

00001

-1

当尝试在具有唯一索引的列中存储重复值时，将引发该异常。

INVALID_CURSOR

01001

-1001

当尝试执行不允许的游标操作（如关闭未打开的游标）时，将引发该异常。

INVALID_NUMBER

01722

-1722

当字符字符串转换为数字失败（因为该字符串并未表示有效数字）时，将引发该异常。

LOGIN_DENIED

01017

-1017

当程序尝试使用无效的用户名或密码登录到数据库时，将引发该异常。

NO_DATA_FOUND

01403

+100

当 SELECT INTO 语句不返回任何行时，将引发该异常。

NOT_LOGGED_ON

01012

-1012

当在未连接到数据库的情况下发出数据库调用时，将引发该异常。

PROGRAM_ERROR

06501

-6501

当 PL/SQL 出现内部问题时，将引发该异常。

ROWTYPE_MISMATCH

06504

-6504

当游标在具有不兼容数据类型的变量中提取值时，将引发该异常。

SELF_IS_NULL

30625

-30625

当调用成员方法时，但未初始化对象类型的实例时，将引发该异常。

STORAGE_ERROR

06500

-6500

PL/SQL 内存用尽或内存损坏时引发。

TOO_MANY_ROWS

01422

-1422

SELECT INTO 语句返回多于一行数据时引发。

VALUE_ERROR

06502

-6502

发生算术、转换、截断或大小约束错误时引发。

ZERO_DIVIDE

01476

1476

尝试将数字除以零时引发。

PL/SQL - Triggers

在本章中，我们将讨论 PL/SQL 中的触发器。触发器是存储程序，当某些事件发生时会自动执行或触发。触发器事实上是为了响应以下任何事件而编写的 −

  1. database manipulation (DML) 语句（DELETE、INSERT 或 UPDATE）

  2. 一个 database definition (DDL) 语句 (CREATE、ALTER 或 DROP)。

  3. 一个 database operation (SERVERERROR、LOGON、LOGOFF、STARTUP 或 SHUTDOWN)。

可以在相关联的表、视图、模式或数据库上定义触发器。

Benefits of Triggers

可以针对以下目的编写触发器：

  1. * 自动生成一些派生列的值

  2. Enforcing referential integrity

  3. * 事件日志和存储有关表访问的信息

  4. Auditing

  5. Synchronous replication of tables

  6. Imposing security authorizations

  7. Preventing invalid transactions

Creating Triggers

  • 创建触发器的语法为：

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
   Declaration-statements
BEGIN
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

其中，

  1. CREATE [OR REPLACE] TRIGGER trigger_name - 创建或替换具有 trigger_name 的现有触发器。

  2. {BEFORE | AFTER | INSTEAD OF} - 这指定触发器将何时执行。INSTEAD OF 子句用于在视图上创建触发器。

  3. {INSERT [OR] | UPDATE [OR] | DELETE} - 这指定 DML 操作。

  4. [OF col_name] - 这指定将更新的列名。

  5. [ON table_name] - 这指定与触发器关联的表的名称。

  6. [REFERENCING OLD AS o NEW AS n] - 这允许你引用各种 DML 语句的新值和旧值，例如 INSERT、UPDATE 和 DELETE。

  7. [FOR EACH ROW] - 这指定一个行级触发器，即触发器将针对受影响的每一行执行。否则，触发器只会在执行 SQL 语句时执行一次，这称为表级触发器。

  8. WHEN (condition) - 这为触发器将触发的行提供了一个条件。此子句仅对行级触发器有效。

Example

首先，将使用在之前的章节中创建并使用的 CUSTOMERS 表：

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

以下程序为 customers 表创建了一个 row-level 触发器，该触发器将在 CUSTOMERS 表上执行的 INSERT、UPDATE 或 DELETE 操作触发。此触发器将显示新旧值之间的薪水差异：

CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
   sal_diff number;
BEGIN
   sal_diff := :NEW.salary  - :OLD.salary;
   dbms_output.put_line('Old salary: ' || :OLD.salary);
   dbms_output.put_line('New salary: ' || :NEW.salary);
   dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Trigger created.

这里需要考虑以下几点：

  1. * 旧值和新值引用不可用于表级触发器，但可以将其用于记录级触发器。

  2. 如果您想在同一个触发器中查询表，那么您应该使用 AFTER 关键字，因为只有在应用了最初的更改且表已恢复到一致状态之后，触发器才可以查询表或再次对其进行更改。

  3. 上述触发器以这样一种方式进行编写：它将在表上的任何 DELETE 或 INSERT 或 UPDATE 操作之前触发，但你可以针对单个或多个操作编写触发器，例如“BEFORE DELETE”（在使用 DELETE 操作在表中删除记录时触发）。

Triggering a Trigger

让我们对 CUSTOMERS 表执行一些 DML 操作。这里有一个 INSERT 语句，它将在表中创建一个新记录：

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

当在 CUSTOMERS 表中创建记录时，将触发上述创建触发器 display_salary_changes ，它将显示以下结果：

Old salary:
New salary: 7500
Salary difference:

由于这是一个新记录，旧工资不可用，以上结果显示为 null。现在让我们对 CUSTOMERS 表执行另一个 DML 操作。UPDATE 语句将更新表中一个现有的记录：

UPDATE customers
SET salary = salary + 500
WHERE id = 2;

当在 CUSTOMERS 表中更新记录时，将触发上述创建触发器 display_salary_changes ，它将显示以下结果：

Old salary: 1500
New salary: 2000
Salary difference: 500

PL/SQL - Packages

在本章中，我们将讨论 PL/SQL 中的包。包是将具有逻辑关连性的 PL/SQL 类型、变量和子程序进行分组的模式对象。

一个包会有两个强制性部分：

  1. Package specification

  2. Package body or definition

Package Specification

规范是对包的界面。它只是 DECLARES 可以从包外部进行引用的类型、变量、常量、异常、游标和子程序。换句话说，它包含有关包内容的所有信息，但不包括子程序的代码。

放在规范中的所有对象被称为 public 对象。未在包规范中但已在包正文中编码的任何子程序都被称为 private 对象。

以下代码段显示了一个具有单个过程的包规范。你可以在包中定义许多全局变量，以及多个过程或函数。

CREATE PACKAGE cust_sal AS
   PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Package created.

Package Body

包正文包含对包规范中声明的各种方法的代码，以及其他对包外部代码隐藏的私有声明。

CREATE PACKAGE BODY 语句用于创建包正文。以下代码段显示了为上述所创建的 cust_sal 包的包正文声明。我假设我们已在数据库中创建 CUSTOMERS 表，如 PL/SQL - Variables 章所述。

CREATE OR REPLACE PACKAGE BODY cust_sal AS

   PROCEDURE find_sal(c_id customers.id%TYPE) IS
   c_sal customers.salary%TYPE;
   BEGIN
      SELECT salary INTO c_sal
      FROM customers
      WHERE id = c_id;
      dbms_output.put_line('Salary: '|| c_sal);
   END find_sal;
END cust_sal;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Package body created.

Using the Package Elements

包元素（变量、过程或函数）可使用以下语法进行访问：

package_name.element_name;

考虑一下这一点，我们已在数据库架构中创建了上述包，以下程序使用 cust_sal 包的 find_sal 方法：

DECLARE
   code customers.id%type := &cc_id;
BEGIN
   cust_sal.find_sal(code);
END;
/

当在 SQL 提示符下执行上述代码时，它会提示输入客户 ID，并在输入 ID 时，它会显示相应的工资，如下所示：

Enter value for cc_id: 1
Salary: 3000

PL/SQL procedure successfully completed.

Example

以下程序提供了一个更完整的包。我们将使用存储在我们的数据库中的，具有以下记录的 CUSTOMERS 表：

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 |
|  2 | Khilan   |  25 | Delhi     |  3000.00 |
|  3 | kaushik  |  23 | Kota      |  3000.00 |
|  4 | Chaitali |  25 | Mumbai    |  7500.00 |
|  5 | Hardik   |  27 | Bhopal    |  9500.00 |
|  6 | Komal    |  22 | MP        |  5500.00 |
+----+----------+-----+-----------+----------+

The Package Specification

CREATE OR REPLACE PACKAGE c_package AS
   -- Adds a customer
   PROCEDURE addCustomer(c_id   customers.id%type,
   c_name  customers.Name%type,
   c_age  customers.age%type,
   c_addr customers.address%type,
   c_sal  customers.salary%type);

   -- Removes a customer
   PROCEDURE delCustomer(c_id  customers.id%TYPE);
   --Lists all customers
   PROCEDURE listCustomer;

END c_package;
/

当在 SQL 提示符下执行上述代码时，它将创建上述包并显示以下结果：

Package created.

Creating the Package Body

CREATE OR REPLACE PACKAGE BODY c_package AS
   PROCEDURE addCustomer(c_id  customers.id%type,
      c_name customers.Name%type,
      c_age  customers.age%type,
      c_addr  customers.address%type,
      c_sal   customers.salary%type)
   IS
   BEGIN
      INSERT INTO customers (id,name,age,address,salary)
         VALUES(c_id, c_name, c_age, c_addr, c_sal);
   END addCustomer;

   PROCEDURE delCustomer(c_id   customers.id%type) IS
   BEGIN
      DELETE FROM customers
      WHERE id = c_id;
   END delCustomer;

   PROCEDURE listCustomer IS
   CURSOR c_customers is
      SELECT  name FROM customers;
   TYPE c_list is TABLE OF customers.Name%type;
   name_list c_list := c_list();
   counter integer :=0;
   BEGIN
      FOR n IN c_customers LOOP
      counter := counter +1;
      name_list.extend;
      name_list(counter) := n.name;
      dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
      END LOOP;
   END listCustomer;

END c_package;
/

上述示例使用了 nested table 。我们将在下一章中讨论嵌套表的概念。

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Package body created.

Using The Package

以下程序使用在软件包 c_package 中定义的 declared 和 defined 方法。

DECLARE
   code customers.id%type:= 8;
BEGIN
   c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
   c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
   c_package.listcustomer;
   c_package.delcustomer(code);
   c_package.listcustomer;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish

PL/SQL procedure successfully completed

PL/SQL - Collections

在本章中，我们将讨论 PL/SQL 的 Collection。Collection 是拥有相同数据类型的一组有序元素。每个元素都被一个代表其在 Collection 中位置的唯一下标标识。

PL/SQL 提供了三种 Collection 类型 −

  1. 索引表或关联数组

  2. Nested table

  3. Variable-size array or Varray

Oracle 文档为每种 Collection 类型提供了以下特性 −

Collection Type

Number of Elements

Subscript Type

Dense or Sparse

Where Created

可以成为对象类型属性

关联数组（或索引表）

Unbounded

String or integer

Either

Only in PL/SQL block

No

Nested table

Unbounded

Integer

以稠密形态开始，可变为稀疏形态

在 PL/SQL 代码组或模式层

Yes

Variablesize array (Varray)

Bounded

Integer

Always dense

在 PL/SQL 代码组或模式层

Yes

我们已经在第 'PL/SQL arrays' 章讨论过了 varray。在本章中，我们将讨论 PL/SQL 表。

两种类型的 PL/SQL 表，例如索引表和嵌套表有相同的结构，并且它们的行的访问是使用下标符号。不过，这两种类型的表在一点上存在差异；嵌套表可以被储存在数据库列中，而索引表不能。

Index-By Table

index-by 表（也称为 associative array ）是一组 key-value 对。每个键是唯一的，用于查找相应的值。键可以是整数或字符串。

使用以下语法创建索引表。在此，我们创建一个名为 table_name 的 index-by 表，其键将为 subscript_type，关联值将为 element_type

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

table_name type_name;

Example

以下示例演示如何创建一个表来存储整数值以及姓名，稍后它会打印相同的姓名列表。

DECLARE
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
   salary_list salary;
   name   VARCHAR2(20);
BEGIN
   -- adding elements to the table
   salary_list('Rajnish') := 62000;
   salary_list('Minakshi') := 75000;
   salary_list('Martin') := 100000;
   salary_list('James') := 78000;

   -- printing the table
   name := salary_list.FIRST;
   WHILE name IS NOT null LOOP
      dbms_output.put_line
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
      name := salary_list.NEXT(name);
   END LOOP;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Salary of James is 78000
Salary of Martin is 100000
Salary of Minakshi is 75000
Salary of Rajnish is 62000

PL/SQL procedure successfully completed.

Example

索引表中的元素也可以是任何数据库表或任何数据库表字段的 %ROWTYPE 。以下示例说明了该概念。我们将使用存储在我们的数据库中的 CUSTOMERS 表为 −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
DECLARE
   CURSOR c_customers is
      select name from customers;

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer;
   name_list c_list;
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter +1;
      name_list(counter) := n.name;
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter));
   END LOOP;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed

Nested Tables

nested table 很像一个具有任意数量元素的一维数组。但是，嵌套表在以下方面与数组不同 −

  1. 数组有声明的元素数量，但嵌套表没有。嵌套表的大小可以动态增加。

  2. 数组总是密集的，即总是具有连续的脚标。嵌套数组最初是密集的，但当从中删除元素时，它可能会变得稀疏。

嵌套表使用以下语法创建 −

TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;

此声明类似于 index-by 表的声明，但没有 INDEX BY 子句。

嵌套表可以存储在数据库列中。它还可以进一步用于简化 SQL 操作，在该操作中，将单列表与较大表联接。关联数组无法存储在数据库中。

Example

以下示例说明了嵌套表的用法 −

DECLARE
   TYPE names_table IS TABLE OF VARCHAR2(10);
   TYPE grades IS TABLE OF INTEGER;
   names names_table;
   marks grades;
   total integer;
BEGIN
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i IN 1 .. total LOOP
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
   end loop;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92

PL/SQL procedure successfully completed.

Example

nested table 的元素也可以是任何数据库表或任何数据库表字段 %TYPE 的 %ROWTYPE 。以下示例说明了该概念。我们将使用存储在我们的数据库中的 CUSTOMERS 表为 −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
DECLARE
   CURSOR c_customers is
      SELECT  name FROM customers;
   TYPE c_list IS TABLE of customerS.No.ame%type;
   name_list c_list := c_list();
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter +1;
      name_list.extend;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter));
   END LOOP;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed.

Collection Methods

PL/SQL 提供了内置收集方法，使收集更容易使用。下表列出了方法及其用途 −

S.No

Method Name & Purpose

1

EXISTS(n) 如果集合中的第 n 个元素存在，则返回 TRUE；否则返回 FALSE。

2

COUNT 返回集合当前包含的元素数。

3

LIMIT 检查集合的最大大小。

4

FIRST 返回集合中使用整数脚标的第一个（最小的）索引号。

5

LAST 返回集合中使用整数脚标的最后（最大的）索引号。

6

PRIOR(n) 返回集合中位于索引 n 之前的索引号。

7

NEXT(n) 返回下一个索引号 n。

8

EXTEND 向集合中添加一个空元素。

9

EXTEND(n) 向集合中添加 n 个空元素。

10

EXTEND(n,i) 向集合中添加 n 份第 i 个元素的副本。

11

TRIM 从集合的末尾移除一个元素。

12

TRIM(n) 从集合的末尾移除 n 个元素。

13

DELETE 从集合中移除所有元素，COUNT 设置为 0。

14

DELETE(n) 从具有数字键或嵌套表的关联数组中移除 nth 元素。如果关联数组具有字符串键，则删除对应于键值的元素。如果 n 为空，则 DELETE(n) 不执行任何操作。

15

DELETE(m,n) 从关联数组或嵌套表中移除范围 m..n 内的所有元素。如果 m 大于 n ，或者 m 或 n 为空，则 DELETE(m,n) 不执行任何操作。

Collection Exceptions

下表提供了集合异常及其引发的时间 −

Collection Exception

Raised in Situations

COLLECTION_IS_NULL

您尝试操作原子空集合。

NO_DATA_FOUND

下标指定已被删除的元素或者是不存在的关联数组元素。

SUBSCRIPT_BEYOND_COUNT

下标超过集合中的元素数量。

SUBSCRIPT_OUTSIDE_LIMIT

下标超出允许的范围。

VALUE_ERROR

下标为 null 或不能转换为键类型。如果键被定义为 PLS_INTEGER 范围并且下标超出该范围，则可能会发生该异常。

PL/SQL - Transactions

在本章中，我们将讨论 PL/SQL 中的事务。数据库 transaction 是一个工作原子单元，可能由一个或多个相关的 SQL 语句构成。称为原子单元是因为构成事务的 SQL 语句所产生的数据库修改可以共同提交，即永久保存在数据库中或从数据库回滚（撤销）。

成功执行的 SQL 语句和提交的事务不同。即使 SQL 语句成功执行，但除非提交包含该语句的事务，否则可以回滚该语句并撤销该语句所做的所有更改。

Starting and Ending a Transaction

事务有 beginning 和 end 。当发生以下事件之一时，事务开始：

  1. 连接到数据库后执行第一条 SQL 语句。

  2. 事务完成后发出的每条新 SQL 语句。

当发生以下事件之一时，事务结束：

  1. 发出 COMMIT 或 ROLLBACK 语句。

  2. 发出 DDL 语句，例如 CREATE TABLE 语句，因为在这种情况下会自动执行一条 COMMIT 语句。

  3. 发出 DCL 语句，例如 GRANT 语句，因为在这种情况下会自动执行一条 COMMIT 语句。

  4. 用户断开与数据库的连接。

  5. 用户通过发出 EXIT 命令退出 SQL*PLUS ，会自动执行一条 COMMIT 语句。

  6. SQL*Plus 异常终止，会自动执行 ROLLBACK 语句。

  7. DML 语句失败，在这种情况下会自动执行一条 ROLLBACK 语句以撤销该 DML 语句。

Committing a Transaction

通过发出 SQL 命令 COMMIT 使事务永久生效。COMMIT 命令的常规语法为：

COMMIT;

例如，

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

COMMIT;

Rolling Back Transactions

在不执行 COMMIT 的情况下对数据库所做的更改可以使用 ROLLBACK 命令撤销。

ROLLBACK 命令的常规语法为：

ROLLBACK [TO SAVEPOINT < savepoint_name>];

当事务由于系统故障等前所未有的情况而中止时，从上次提交后整个事务都会自动回滚。如果不使用 savepoint ，则只需使用以下语句回滚所有更改：

ROLLBACK;

Savepoints

保存点是一种标记，有助于通过设置一些检查点将一个较长的事务拆分为较小的单元。通过在较长的事务中设置保存点，可以在需要时回滚到检查点。这是通过发出 SAVEPOINT 命令来完成的。

SAVEPOINT 命令的一般语法是：

SAVEPOINT < savepoint_name >;

例如

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 );
SAVEPOINT sav1;

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000;
ROLLBACK TO sav1;

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 7;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 8;

COMMIT;

ROLLBACK TO sav1 − 此语句回滚至标记了保存点 sav1 的那一点的所有更改。

之后，您所做的更改将开始。

Automatic Transaction Control

要设置只要执行 COMMIT 或 INSERT, UPDATE 命令时，自动执行 DELETE ，您可以设置 AUTOCOMMIT 环境变量，如下所示：

SET AUTOCOMMIT ON;

您可以使用以下命令关闭自动提交模式：

SET AUTOCOMMIT OFF;

PL/SQL - Date & Time

在本章里，我们将讨论 PL/SQL 中的日期和时间。在 PL/SQL 中有两类日期和时间相关数据类型 -

  1. Datetime data types

  2. Interval data types

Datetime 数据类型是 -

  1. DATE

  2. TIMESTAMP

  3. TIMESTAMP WITH TIME ZONE

  4. 带本地时区的 TIMESTAMP

区间数据类型包括 −

  1. INTERVAL YEAR TO MONTH

  2. INTERVAL DAY TO SECOND

Field Values for Datetime and Interval Data Types

datetime 和 interval 数据类型均由 fields 组成。这些字段的值决定了数据类型的值。下表列出了日期时间和区间的字段及其可能的值。

Field Name

Valid Datetime Values

Valid Interval Values

YEAR

-4712 到 9999（不包括公元 0 年）

Any nonzero integer

MONTH

01 to 12

0 to 11

DAY

01 到 31（受 MONTH 和 YEAR 的值限制，根据该国家/地区的日历规则）

Any nonzero integer

HOUR

00 to 23

0 to 23

MINUTE

00 to 59

0 to 59

SECOND

00 至 59.9(n)，其中 9(n) 是时间的小数秒精度。9(n) 部分不适用于 DATE。

0 到 59.9(n)，其中 9(n) 是间隔分数秒的精度

TIMEZONE_HOUR

-12 至 14（范围适用于夏令时变化）。不适用于 DATE 或 TIMESTAMP。

Not applicable

TIMEZONE_MINUTE

00 至 59。不适用于 DATE 或 TIMESTAMP。

Not applicable

TIMEZONE_REGION

不适用于 DATE 或 TIMESTAMP。

Not applicable

TIMEZONE_ABBR

不适用于 DATE 或 TIMESTAMP。

Not applicable

The Datetime Data Types and Functions

以下是日期时间数据类型 −

DATE

它在字符和数字数据类型中都存储日期和时间信息。它由世纪、年份、月份、日期、小时、分钟和秒的信息组成。它指定为 −

TIMESTAMP

它是 DATE 数据类型的扩展。它存储 DATE 数据类型的年、月和日，以及小时、分钟和秒值。它适用于存储精确的时间值。

TIMESTAMP WITH TIME ZONE

它是 TIMESTAMP 的变体，在其值中包含时区区域名称或时区偏移。时区偏移是以小时和分钟表示的本地时间与 UTC 时间之间的差异。这种数据类型适用于跨地理区域收集和评估日期信息。

TIMESTAMP WITH LOCAL TIME ZONE

它是 TIMESTAMP 的另一个变体，在其值中包含一个时区偏移。

下表提供了日期时间函数（其中 x 具有日期时间值）−

S.No

Function Name & Description

1

ADD_MONTHS(x, y); 将 y 个月添加到 x 。

2

LAST_DAY(x); 返回该月的最后一天。

3

MONTHS_BETWEEN(x, y); 返回 x 和 y 之间的月份数。

4

NEXT_DAY(x, day); 返回 x 之后的下一天的日期时间。

5

NEW_TIME; 从用户指定时区返回时间/日期值。

6

ROUND(x [, unit]); Rounds x.

7

SYSDATE(); Returns the current datetime.

8

TRUNC(x [, unit]); Truncates x.

时间戳函数（其中 x 具有时间戳值）−

S.No

Function Name & Description

1

CURRENT_TIMESTAMP(); 返回一个 TIMESTAMP WITH TIME ZONE，其中包含当前会话时间以及会话时区。

2

*EXTRACT({ YEAR

MONTH

DAY

HOUR

MINUTE

SECOND }

{ TIMEZONE_HOUR

TIMEZONE_MINUTE }

{ TIMEZONE_REGION

} TIMESTAMP_ABBR ) 中提取并返回年、月、日、时、分、秒或时区。

3

FROM_TZ(x, time_zone); 将 TIMESTAMP x 和时区 time_zone 指定的时间转换为 TIMESTAMP WITH TIMEZONE。

4

LOCALTIMESTAMP(); 返回一个包含会话时区中本地时间戳的 TIMESTAMP。

5

SYSTIMESTAMP(); 返回一个包含当前数据库时间及数据库时区的 TIMESTAMP WITH TIME ZONE。

6

SYS_EXTRACT_UTC(x); 将 TIMESTAMP WITH TIMEZONE x 转换为一个包含 UTC 时间和日期戳的 TIMESTAMP。

7

TO_TIMESTAMP(x, [format]); 将字符串 x 转换为一个 TIMESTAMP。

8

Examples

以下代码片段展示了上述函数的使用方法：

Example 1

SELECT SYSDATE FROM DUAL;

Output −

08/31/2012 5:25:34 PM

Example 2

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;

Output −

31-08-2012 05:26:14

Example 3

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

Output −

01/31/2013 5:26:31 PM

Example 4

SELECT LOCALTIMESTAMP FROM DUAL;

Output −

8/31/2012 5:26:55.347000 PM

The Interval Data Types and Functions

以下是 Interval 数据类型：

  1. IINTERVAL YEAR TO MONTH - 使用 YEAR 和 MONTH datetime 字段存储一段时间。

  2. INTERVAL DAY TO SECOND - 使用天、小时、分钟和秒存储一段时间。

Interval Functions

S.No

Function Name & Description

1

NUMTODSINTERVAL(x, interval_unit); 将数字 x 转换为 INTERVAL DAY TO SECOND。

2

NUMTOYMINTERVAL(x, interval_unit); 将数字 x 转换为 INTERVAL YEAR TO MONTH。

3

TO_DSINTERVAL(x); 将字符串 x 转换为 INTERVAL DAY TO SECOND。

4

TO_YMINTERVAL(x); 将字符串 x 转换为 INTERVAL YEAR TO MONTH。

PL/SQL - DBMS Output

在本章中，我们将讨论 PL/SQL 中的 DBMS 输出。 DBMS_OUTPUT 是一个内置包，用于显示输出、调试信息，以及从 PL/SQL 块、子程序、包和触发器发送消息。我们在整个教程中都已使用过此包。

我们一起来看一下一个小代码段，它将显示数据库中的所有用户表。尝试在数据库中运行它，以列出所有表名：

BEGIN
   dbms_output.put_line  (user || ' Tables in the database:');
   FOR t IN (SELECT table_name FROM user_tables)
   LOOP
      dbms_output.put_line(t.table_name);
   END LOOP;
END;
/

DBMS_OUTPUT Subprograms

DBMS_OUTPUT 包具有以下子程序：

S.No

Subprogram & Purpose

1

DBMS_OUTPUT.DISABLE; Disables message output.

2

DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); 启用消息输出。 buffer_size 的 Null 值表示无限制缓冲区大小。

3

DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER); 检索缓冲的一行信息。

4

DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER); 从缓冲区中检索一系列行。

5

DBMS_OUTPUT.NEW_LINE; Puts an end-of-line marker.

6

DBMS_OUTPUT.PUT(item IN VARCHAR2); 将部分行放置在缓冲区中。

7

Example

DECLARE
   lines dbms_output.chararr;
   num_lines number;
BEGIN
   -- enable the buffer with default size 20000
   dbms_output.enable;

   dbms_output.put_line('Hello Reader!');
   dbms_output.put_line('Hope you have enjoyed the tutorials!');
   dbms_output.put_line('Have a great time exploring pl/sql!');

   num_lines := 3;

   dbms_output.get_lines(lines, num_lines);

   FOR i IN 1..num_lines LOOP
      dbms_output.put_line(lines(i));
   END LOOP;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Hello Reader!
Hope you have enjoyed the tutorials!
Have a great time exploring pl/sql!

PL/SQL procedure successfully completed.

PL/SQL - Object Oriented

在本章中，我们将讨论面向对象的 PL/SQL。PL/SQL 允许定义对象类型，这有助于在 Oracle 中设计面向对象数据库。对象类型允许创建复合类型。使用对象允许您实现具有特定数据结构和对其操作方法的真实世界对象。对象具有属性和方法。属性是对象的性质，用于存储对象的状态；方法用于对行为建模。

使用 CREATE [OR REPLACE] TYPE 语句创建对象。以下是一个创建包含几个属性的简单 address 对象的示例：

CREATE OR REPLACE TYPE address AS OBJECT
(house_no varchar2(10),
 street varchar2(30),
 city varchar2(20),
 state varchar2(10),
 pincode varchar2(10)
);
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type created.

让我们再创建一个对象 customer ，其中我们把 attributes 和 methods 结合在一起，以获得面向对象的体验：

CREATE OR REPLACE TYPE customer AS OBJECT
(code number(5),
 name varchar2(30),
 contact_no varchar2(12),
 addr address,
 member procedure display
);
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type created.

Instantiating an Object

定义对象类型为对象提供蓝图。为了使用此对象，需要创建此对象的实例。您可以使用实例名称和 the access operator (.) 访问对象的属性和方法，如下所示：

DECLARE
   residence address;
BEGIN
   residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');
   dbms_output.put_line('House No: '|| residence.house_no);
   dbms_output.put_line('Street: '|| residence.street);
   dbms_output.put_line('City: '|| residence.city);
   dbms_output.put_line('State: '|| residence.state);
   dbms_output.put_line('Pincode: '|| residence.pincode);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

House No: 103A
Street: M.G.Road
City: Jaipur
State: Rajasthan
Pincode: 201301

PL/SQL procedure successfully completed.

Member Methods

Member methods 用于操作对象的 attributes 。在声明对象类型时，您提供了成员方法的声明。对象主体定义了成员方法的代码。对象主体是使用 CREATE TYPE BODY 语句创建的。

Constructors 是函数，它们返回一个新对象作为其值。每个对象都有一个系统定义的构造函数方法。构造函数的名称与对象类型相同。例如：

residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');

comparison methods 用于比较对象。有两种方法可以比较对象：

Map method

Map method 是一种通过这种方式实现的函数，其值取决于属性的值。例如，对于一个客户对象，如果两个客户的客户代码相同，那么这两个客户可能是一样的。因此，这两个对象之间的关系将取决于代码的值。

Order method

Order method 实现了用于比较两个对象的一些内部逻辑。例如，对于一个矩形对象，如果它的两边都更大，那么一个矩形将大于另一个矩形。

Using Map method

让我们尝试使用以下矩形对象来理解上述概念－

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 member procedure display,
 map member function measure return number
);
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type created.

创建类型主体－

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
   MAP MEMBER FUNCTION measure return number IS
   BEGIN
      return (sqrt(length*length + width*width));
   END measure;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type body created.

现在使用矩形对象及其成员函数－

DECLARE
   r1 rectangle;
   r2 rectangle;
   r3 rectangle;
   inc_factor number := 5;
BEGIN
   r1 := rectangle(3, 4);
   r2 := rectangle(5, 7);
   r3 := r1.enlarge(inc_factor);
   r3.display;
   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Length: 8
Width: 9
Length: 5
Width: 7

PL/SQL procedure successfully completed.

Using Order method

现在， same effect could be achieved using an order method 。让我们使用一个 order 方法来重新创建矩形对象－

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member procedure display,
 order member function measure(r rectangle) return number
);
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type created.

创建类型主体－

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
   ORDER MEMBER FUNCTION measure(r rectangle) return number IS
   BEGIN
      IF(sqrt(self.length*self.length + self.width*self.width)>
         sqrt(r.length*r.length + r.width*r.width)) then
         return(1);
      ELSE
         return(-1);
      END IF;
   END measure;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type body created.

使用矩形对象及其成员函数－

DECLARE
   r1 rectangle;
   r2 rectangle;
BEGIN
   r1 := rectangle(23, 44);
   r2 := rectangle(15, 17);
   r1.display;
   r2.display;
   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Length: 23
Width: 44
Length: 15
Width: 17
Length: 23
Width: 44

PL/SQL procedure successfully completed.

Inheritance for PL/SQL Objects

PL/SQL允许从现有的基础对象创建对象。要实施继承，基础对象应被声明为 NOT FINAL 。默认值为 FINAL 。

以下程序展示了PL/SQL对象中的继承。让我们创建另一个名为 TableTop 的对象，它从Rectangle对象中继承而来。为此，我们需要创建基础矩形对象－

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 NOT FINAL member procedure display) NOT FINAL
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type created.

创建基础类型主体－

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type body created.

创建子对象桌台－

CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
   material varchar2(20),
   OVERRIDING member procedure display
)
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type created.

创建子对象桌台的类型主体－

CREATE OR REPLACE TYPE BODY tabletop AS
OVERRIDING MEMBER PROCEDURE display IS
BEGIN
   dbms_output.put_line('Length: '|| length);
   dbms_output.put_line('Width: '|| width);
   dbms_output.put_line('Material: '|| material);
END display;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type body created.

使用桌台对象及其成员函数－

DECLARE
   t1 tabletop;
   t2 tabletop;
BEGIN
   t1:= tabletop(20, 10, 'Wood');
   t2 := tabletop(50, 30, 'Steel');
   t1.display;
   t2.display;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Length: 20
Width: 10
Material: Wood
Length: 50
Width: 30
Material: Steel

PL/SQL procedure successfully completed.

Abstract Objects in PL/SQL

NOT INSTANTIABLE 子句允许您声明一个抽象对象。您不能按原样使用抽象对象；您将需要创建此类对象的子类型或子类型来使用它的功能。

例如，

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
 NOT INSTANTIABLE NOT FINAL
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Type created.