Plsql 简明教程
PL/SQL - Overview
PL/SQL 编程语言是由 Oracle 公司在 20 世纪 80 年代末作为 SQL 和 Oracle 关系数据库的程序扩展语言开发的。以下是关于 PL/SQL 的一些重要事实 -
-
PL/SQL 是一种完全可移植、高性能的事务处理语言。
-
PL/SQL 提供了内置、解释的和与操作系统无关的编程环境。
-
PL/SQL 还可以直接从命令行 SQL*Plus interface 调用。
-
也可以从外部编程语言调用数据库来进行直接调用。
-
PL/SQL 一般语法基于 ADA 和 Pascal 编程语言。
-
除了 Oracle 之外，PL/SQL 还可在 TimesTen in-memory database 和 IBM DB2 中使用。
Features of PL/SQL
PL/SQL 具有以下特性 -
-
PL/SQL 与 SQL 紧密集成。
-
它提供广泛的错误检查。
-
它提供多种数据类型。
-
它提供各种编程结构。
-
它通过函数和过程支持结构化编程。
-
It supports object-oriented programming.
-
它支持 Web 应用程序和服务器页面的开发。
Advantages of PL/SQL
PL/SQL 具有以下优点 -
-
SQL 是标准数据库语言，PL/SQL 与 SQL 紧密集成。PL/SQL 支持静态和动态 SQL。静态 SQL 支持 DML 操作和从 PL/SQL 块进行事务控制。在动态 SQL 中，SQL 允许在 PL/SQL 块中嵌入 DDL 语句。
-
PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.
-
PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.
-
PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.
-
编写在 PL/SQL 中的应用程序完全可移植。
-
PL/SQL 提供了高安全级别。
-
PL/SQL 提供了对预定义 SQL 包的访问。
-
PL/SQL 提供了对面向对象编程的支持。
-
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 的试用版 -
你将不得不根据你的操作系统下载 32 位或 64 位版本的安装程序。通常有两个文件。我们下载了 64 位版本。你还可以对你的操作系统使用类似的步骤，无论它是 Linux 还是 Solaris。
-
win64_11gR2_database_1of2.zip
-
win64_11gR2_database_2of2.zip
在下载以上两个文件后，你需要将它们解压到一个目录 database 中，并在该目录下你会找到以下子目录 -
Step 5
提供一个安装 Oracle 服务器的位置。只需修改 Oracle Base ，其他位置将自动设置。你還需要提供一个密码;此密码将由系统 DBA 使用。一旦你提供了所需的信息，单击 Next 按钮继续。
Final Step
现在是验证您的安装的时候了。在命令提示符处，如果您使用的是 Windows，请使用以下命令：−
sqlplus "/ as sysdba"您应该拥有用于编写 PL/SQL 命令和脚本的 SQL 提示符 −
Text Editor
从命令提示符运行大型程序可能会导致无意中丢失部分工作。始终建议使用命令文件。要使用命令文件−
-
在文本编辑器中键入代码，例如 Notepad, Notepad+, 或 EditPlus 等。
-
使用 .sql 扩展名在主目录中保存该文件。
-
从创建 PL/SQL 文件的目录启动 SQL*Plus command prompt 。
-
在 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 - 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 completedPL/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 的数据类型。因此，布尔值不能在 − 中使用
-
SQL statements
-
内置 SQL 函数（如 TO_CHAR ）
-
从 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.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 值初始化变量，你可以在声明期间使用以下任意一种方法来执行此操作：
-
The DEFAULT keyword
-
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 允许块的嵌套，即每个程序块都可以包含另一个内部块。如果一个变量在内部块中声明，则外部块无法访问该变量。但是，如果一个变量在外部块中声明且可访问，则所有嵌套内部块也可以访问该变量。有两种类型的变量作用域 -
-
Local variables − 在内部块中声明且外部块无法访问的变量。
-
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 successfullyPL/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 支持以下类型的文字 −
-
Numeric Literals
-
Character Literals
-
String Literals
-
BOOLEAN Literals
-
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 语言中内置了丰富的操作符，并提供了以下类型的操作符 −
-
Arithmetic operators
-
Relational operators
-
Comparison operators
-
Logical operators
-
String operators
在这里，我们将逐个了解算术、关系、比较和逻辑操作符。字符串操作符将在本章节后面讨论 − PL/SQL - Strings 。
Arithmetic Operators
以下表格显示了 PL/SQL 支持的所有算术运算符。我们假设 variable A 含 10， variable B 含 5，则 −
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，则 −
Operator |
Description |
Example |
= |
检查两个操作数的值是否相等，如果相等，则条件变为真。 |
(A = B) 为假。 |
!= <> ~= |
检查两个操作数的值是否相等，如果值不相等，则条件变为真。 |
(A != B) 为真。 |
> |
检查左操作数的值是否大于右操作数的值，如果大于，则条件变为真。 |
(A > B) 不为真。 |
< |
检查左操作数的值是否小于右操作数的值，如果小于，则条件变为真。 |
(A < B) 为真。 |
>= |
检查左操作数的值是否大于或等于右操作数的值，如果大于或等于，则条件变为真。 |
(A >= B) 不为真。 |
⇐ |
检查左操作数的值是否小于或等于右操作数的值，如果小于或等于，则条件变为真。 |
(A ⇐ B) 为真 |
Comparison Operators
比较运算符用于将一个表达式与另一个表达式进行比较。结果始终为 TRUE, FALSE 或 NULL 。
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 为假，那么 −
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。
Operator |
Operation |
** |
exponentiation |
+, - |
identity, negation |
*, / |
multiplication, division |
+, -, |
|
addition, subtraction, concatenation |
|
comparison |
|
NOT |
logical negation |
AND |
conjunction |
OR |
inclusion |
PL/SQL - Conditions
在本章中，我们将讨论 PL/SQL 中的条件。决策结构要求程序员指定一个或多个条件，以便由程序对其进行求值或测试，如果确定条件为真，则执行一条或多条语句，并且如果确定条件为假，则可以选择执行其他语句。
以下是大多数编程语言中常见的条件（即决策制定）结构的通用形式 −
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 中的循环。在很多情况下，需要多次执行代码块。通常，语句按顺序执行：函数中的第一个语句最先执行，然后是第二个，依此类推。
编程语言提供了各种控制结构，允许执行更复杂的路径。
循环语句允许我们多次执行一个语句或一组语句，以下是大多数编程语言中循环语句的一般形式 −
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 提供了三种字符串——
-
Fixed-length strings ——在这种字符串中，程序员在声明字符串时指定长度。该字符串将右填充空格到指定的长度。
-
Variable-length strings ——在这种字符串中，指定字符串的最大长度（最长为 32,767），不会进行填充。
-
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 completedTo declare a fixed-length string, use the CHAR datatype. Here you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length required. The following two declarations are identical −
red_flag CHAR(1) := 'Y';
red_flag CHAR := 'Y';PL/SQL String Functions and Operators
PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides the string functions provided by PL/SQL −
S.No |
Function & Purpose |
1 |
ASCII(x); Returns the ASCII value of the character x. |
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 均由连续的内存位置组成。最低地址对应于第一个元素，最高地址对应于最后一个元素。
数组是集合类型数据的一部分，它代表可变大小的数组。我们将在后面一章中学习其他集合类型 '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>其中，
-
varray_type_name 是一个有效的属性名，
-
n 是 varray 中的元素数（最大值），
-
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 −
-
在 Oracle 环境中，varray 的起始索引始终为 1。
-
你可以使用与 varray 同名的 varray 类型的构造方法来初始化 varray 元素。
-
Varrays are one-dimensional arrays.
-
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 的另一个子程序或程序来调用子程序。
可以创建子程序 −
-
At the schema level
-
Inside a package
-
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 提供两种子程序 −
-
Functions − 这些子程序返回单个值；主要用于计算并返回一个值。
-
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;其中，
-
procedure-name 指定过程的名称。
-
[OR REPLACE] 选项允许修改现有过程。
-
可选参数列表包含名称、模式和参数的类型。 IN 表示将从外部传递的值，OUT 表示用于在过程外部返回值的参数。
-
procedure-body 包含可执行部分。
-
使用 AS 关键字而不是 IS 关键字来创建独立过程。
Executing a Standalone Procedure
独立过程可以通过两种方式来调用 −
-
Using the EXECUTE keyword
-
从 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
实际参数可以通过三种方式传递 -
-
Positional notation
-
Named notation
-
Mixed notation
Positional Notation
在位置符号中，你可以调用过程为 -
findMin(a, b, c, d);在位置符号中，第一个实际参数替换了第一个形式参数；第二个实际参数替换了第二个形式参数，以此类推。因此， a 替换为 x, b 替换为 y, c 替换为 z 替换 d 替换为 m 。
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];其中，
-
function-name 指定函数的名称。
-
[OR REPLACE] 选项允许修改现有函数。
-
可选参数列表包含参数的名称、模式和类型。IN 表示将从外部传递的值，而 OUT 表示将用于在过程外部返回值的参数。
-
该函数必须包含 return 语句。
-
RETURN 子句指定要从函数返回的数据类型。
-
function-body 包含可执行部分。
-
创建独立函数时，使用 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 语句返回的行，每次一行。有两种类型的游标：
-
Implicit cursors
-
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;使用显式游标包括以下步骤−
-
声明游标以初始化内存
-
打开游标以分配内存
-
获取游标以检索数据
-
关闭游标以释放分配的内存
Declaring the Cursor
声明游标使用名称和关联的 SELECT 语句定义游标。例如−
CURSOR c_customers IS
SELECT id, name, address FROM 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 可以处理以下类型的记录：
-
Table-based
-
Cursor-based records
-
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 提供了一个用户定义的记录类型，允许您定义不同的记录结构。这些记录由不同的字段组成。假设您希望跟踪库中的书籍。您可能想要跟踪每本书的以下属性：
-
Title
-
Author
-
Subject
-
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 有两种：
-
System-defined exceptions
-
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 中的触发器。触发器是存储程序，当某些事件发生时会自动执行或触发。触发器事实上是为了响应以下任何事件而编写的 −
-
database manipulation (DML) 语句（DELETE、INSERT 或 UPDATE）
-
一个 database definition (DDL) 语句 (CREATE、ALTER 或 DROP)。
-
一个 database operation (SERVERERROR、LOGON、LOGOFF、STARTUP 或 SHUTDOWN)。
可以在相关联的表、视图、模式或数据库上定义触发器。
Benefits of Triggers
可以针对以下目的编写触发器：
-
* 自动生成一些派生列的值
-
Enforcing referential integrity
-
* 事件日志和存储有关表访问的信息
-
Auditing
-
Synchronous replication of tables
-
Imposing security authorizations
-
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;其中，
-
CREATE [OR REPLACE] TRIGGER trigger_name - 创建或替换具有 trigger_name 的现有触发器。
-
{BEFORE | AFTER | INSTEAD OF} - 这指定触发器将何时执行。INSTEAD OF 子句用于在视图上创建触发器。
-
{INSERT [OR] | UPDATE [OR] | DELETE} - 这指定 DML 操作。
-
[OF col_name] - 这指定将更新的列名。
-
[ON table_name] - 这指定与触发器关联的表的名称。
-
[REFERENCING OLD AS o NEW AS n] - 这允许你引用各种 DML 语句的新值和旧值，例如 INSERT、UPDATE 和 DELETE。
-
[FOR EACH ROW] - 这指定一个行级触发器，即触发器将针对受影响的每一行执行。否则，触发器只会在执行 SQL 语句时执行一次，这称为表级触发器。
-
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.这里需要考虑以下几点：
-
* 旧值和新值引用不可用于表级触发器，但可以将其用于记录级触发器。
-
If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.
-
The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table.
Triggering a Trigger
Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result −
Old salary:
New salary: 7500
Salary difference:Because this is a new record, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table −
UPDATE customers
SET salary = salary + 500
WHERE id = 2;When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result −
Old salary: 1500
New salary: 2000
Salary difference: 500PL/SQL - Packages
In this chapter, we will discuss the Packages in PL/SQL. Packages are schema objects that groups logically related PL/SQL types, variables, and subprograms.
A package will have two mandatory parts −
-
Package specification
-
Package body or definition
Package Specification
The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.
All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called a private object.
The following code snippet shows a package specification having a single procedure. You can have many global variables defined and multiple procedures or functions inside a package.
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/当以上代码在 SQL 提示符下执行时，它会生成以下结果：
Package created.Package Body
The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from the code outside the package.
The CREATE PACKAGE BODY Statement is used for creating the package body. The following code snippet shows the package body declaration for the cust_sal package created above. I assumed that we already have CUSTOMERS table created in our database as mentioned in the PL/SQL - Variables chapter.
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
The package elements (variables, procedures or functions) are accessed with the following syntax −
package_name.element_name;Consider, we already have created the above package in our database schema, the following program uses the find_sal method of the cust_sal package −
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/When the above code is executed at the SQL prompt, it prompts to enter the customer ID and when you enter an ID, it displays the corresponding salary as follows −
Enter value for cc_id: 1
Salary: 3000
PL/SQL procedure successfully completed.Example
The following program provides a more complete package. We will use the CUSTOMERS table stored in our database with the following records −
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;
/When the above code is executed at the SQL prompt, it creates the above package and displays the following result −
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;
/The above example makes use of the nested table. We will discuss the concept of nested table in the next chapter.
当以上代码在 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 completedPL/SQL - Collections
在本章中，我们将讨论 PL/SQL 的 Collection。Collection 是拥有相同数据类型的一组有序元素。每个元素都被一个代表其在 Collection 中位置的唯一下标标识。
PL/SQL 提供了三种 Collection 类型 −
-
索引表或关联数组
-
Nested table
-
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 completedNested Tables
nested table 很像一个具有任意数量元素的一维数组。但是，嵌套表在以下方面与数组不同 −
-
数组有声明的元素数量，但嵌套表没有。嵌套表的大小可以动态增加。
-
数组总是密集的，即总是具有连续的脚标。嵌套数组最初是密集的，但当从中删除元素时，它可能会变得稀疏。
嵌套表使用以下语法创建 −
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) 不执行任何操作。 |
PL/SQL - Transactions
在本章中，我们将讨论 PL/SQL 中的事务。数据库 transaction 是一个工作原子单元，可能由一个或多个相关的 SQL 语句构成。称为原子单元是因为构成事务的 SQL 语句所产生的数据库修改可以共同提交，即永久保存在数据库中或从数据库回滚（撤销）。
成功执行的 SQL 语句和提交的事务不同。即使 SQL 语句成功执行，但除非提交包含该语句的事务，否则可以回滚该语句并撤销该语句所做的所有更改。
Starting and Ending a Transaction
事务有 beginning 和 end 。当发生以下事件之一时，事务开始：
-
连接到数据库后执行第一条 SQL 语句。
-
事务完成后发出的每条新 SQL 语句。
当发生以下事件之一时，事务结束：
-
发出 COMMIT 或 ROLLBACK 语句。
-
发出 DDL 语句，例如 CREATE TABLE 语句，因为在这种情况下会自动执行一条 COMMIT 语句。
-
发出 DCL 语句，例如 GRANT 语句，因为在这种情况下会自动执行一条 COMMIT 语句。
-
用户断开与数据库的连接。
-
用户通过发出 EXIT 命令退出 SQL*PLUS ，会自动执行一条 COMMIT 语句。
-
SQL*Plus 异常终止，会自动执行 ROLLBACK 语句。
-
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 的那一点的所有更改。
之后，您所做的更改将开始。
PL/SQL - Date & Time
在本章里，我们将讨论 PL/SQL 中的日期和时间。在 PL/SQL 中有两类日期和时间相关数据类型 -
-
Datetime data types
-
Interval data types
Datetime 数据类型是 -
-
DATE
-
TIMESTAMP
-
TIMESTAMP WITH TIME ZONE
-
带本地时区的 TIMESTAMP
区间数据类型包括 −
-
INTERVAL YEAR TO MONTH
-
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
以下是日期时间数据类型 −
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 PMExample 2
SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;Output −
31-08-2012 05:26:14Example 3
SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;Output −
01/31/2013 5:26:31 PMExample 4
SELECT LOCALTIMESTAMP FROM DUAL;Output −
8/31/2012 5:26:55.347000 PMThe Interval Data Types and Functions
以下是 Interval 数据类型：
-
IINTERVAL YEAR TO MONTH - 使用 YEAR 和 MONTH datetime 字段存储一段时间。
-
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 用于比较对象。有两种方法可以比较对象：
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.