本文介绍了记录变量的语法和使用方法等相关内容。
简介
记录变量(record variable)是PL/SQL中的一种特殊变量,其通常代表了一行
的概念。它可以拥有多个字段,并与类似于访问表的列(即 a.b
)的形式来访问它的字段。由于这种性质,使得它与表具有一些特殊的交互方式。
语法
记录类型定义:
TYPE record_type IS RECORD ( {field_definition [, ...]} ) ;
其中,field_definition如下所示:
field datatype [ [NOT NULL] {:= | DEFAULT} expression ]
记录变量声明:
record1 {record_type | {table | view | cursor} % ROWTYPE | record2 % TYPE} ;
使用限制
记录变量仅允许出现在以下位置:
在UPDATE语句中SET子句的右侧。
在INSERT语句的VALUES子句中。
在RETURNING子句的INTO子句中。
不允许在SELECT列表、WHERE子句、GROUP BY子句或ORDER BY子句中使用记录变量。
关键字ROW仅允许在SET子句的左侧使用。此外,您不能将ROW与子查询一起使用。
在使用ROW的UPDATE语句中,只允许一个SET子句。
如果INSERT语句的VALUES子句包含记录变量,则不允许在该子句中使用任何其他变量或值。
如果INTO子句包含记录变量,则不允许在该子句中使用任何其他变量或值。
记录变量的创建
您可以通过以下三种方式来创建一个记录变量:
定义一个记录类型,然后声明该类型的变量。
使用
%ROWTYPE
声明一个记录变量,该变量表示数据库表或视图的完整行或行的一部分,在%ROWTYPE
之前可以是表、视图、显式游标或强游标变量。使用
%TYPE
声明与先前声明的记录变量相同类型的记录变量。
CREATE TABLE test(id INT, name VARCHAR(10));
DECLARE
TYPE r_type IS RECORD(id INT, name VARCHAR(10));
v1 r_type; -- 方式1
v2 test%ROWTYPE; -- 方式2
v3 v2%TYPE; -- 方式3
BEGIN
...
END;
在PL/SQL块中定义的记录类型是局部类型。它仅在块中可用,对外部不可见。当记录类型在包中被声明时,它才存储在数据库中。此时,您可以通过以下语法使用:
CREATE PACKAGE pkg AS
TYPE r_type IS RECORD(id INT, name VARCHAR(10));
END;
CREATE PACKAGE BODY pkg AS
END;
DECLARE
r pkg.r_type := pkg.r_type(1, 'a');
BEGIN
RAISE NOTICE 'id: %, name: %', r.id, r.name;
END;
结果显示如下:
NOTICE: id: 1, name: a
使用 %ROWTYPE
创建的记录变量表示表的完整行或是行的一部分。您可以通过table%ROWTYPE
来获取表的完整行,或是通过预先定义的视图或是游标来获取表行的一部分。
CREATE TABLE test(id INT, name VARCHAR(10));
DECLARE
r1 test%ROWTYPE; -- 获取表的完整行定义,包括id和name
CURSOR cur IS SELECT id FROM test;
r2 cur%ROWTYPE; -- 获取表的行定义的id列
BEGIN
r1.id = 1;
r1.name = 'a';
r2.id = 2;
RAISE NOTICE 'r1.id: %, r1.name: %', r1.id, r1.name;
RAISE NOTICE 'r2.id: %', r2.id;
END;
结果显示如下:
NOTICE: r1.id: 1, r1.name: a
NOTICE: r2.id: 2
记录变量的初始化
在定义记录类型时,您可以为字段设定非空约束,或是指定默认值。
DECLARE
TYPE r_type IS RECORD(id INT NOT NULL := 1,
name VARCHAR(10) DEFAULT 'name');
BEGIN
...
END;
使用后两种方式创建记录变量时,变量的默认值均为NULL。这两种方式不会继承表的约束,仅获取其字段名称以及类型。不会获取表中的隐藏列和rowid列。
此外,在初始化记录变量时,和寻常的变量一样,您可以通过constant来将其指定为一个常量,这使得记录变量在初始化后无法被修改。
DECLARE
TYPE r_type IS RECORD(id INT, name VARCHAR(10));
r CONSTANT r_type := r_type(1, 'a');
BEGIN
r := My_Rec(2, 'b'); -- 错误
END;
结果显示如下:
ERROR: variable "r" is declared CONSTANT
记录变量的赋值
您可以逐个分配值给记录变量的每个字段。
DECLARE
TYPE r_type IS RECORD(id INT, name VARCHAR(10));
r r_type;
BEGIN
r.id := 1;
r.name := 'name';
RAISE NOTICE 'r.id: %, r.name: %', r.id, r.name;
END;
结果显示如下:
NOTICE: r.id: 1, r.name: name
您也可以直接将一个记录变量赋值给另一个记录变量,只要它们的对应字段的属性允许隐式类型转换。
DECLARE
TYPE r_type1 IS RECORD(id INT, name VARCHAR(10));
TYPE r_type2 IS RECORD(id VARCHAR(10), name INT);
r1 r_type1;
r2 r_type2 := r_type2('1', 2);
BEGIN
r1 := r2;
RAISE NOTICE 'r.id: %, r.name: %', r1.id, r1.name;
END;
结果显示如下:
NOTICE: r.id: 1, r.name: 2
如果类型转换错误,则会报错。
DECLARE
TYPE r_type1 IS RECORD(id INT, name VARCHAR(10));
TYPE r_type2 IS RECORD(id VARCHAR(10), name INT);
r1 r_type1;
r2 r_type2 := r_type2('1', 2);
BEGIN
r1 := r2;
RAISE NOTICE 'r.id: %, r.name: %', r1.id, r1.name;
END;
结果显示如下:
ERROR: invalid input syntax for type integer: "a"
您还可以从表中取出行,将其传入记录变量中。同样地,需要返回行的列与记录变量的字段能够对应地进行隐式类型转换。
CREATE TABLE test(id INT, name VARCHAR(10))
INSERT INTO test VALUES(1, 'a');
INSERT INTO test VALUES(2, 'b');
DECLARE
TYPE r_type IS RECORD(id int, name text);
r r_type;
BEGIN
SELECT * INTO r FROM test LIMIT 1; -- 这里限制了返回的行数
RAISE NOTICE '%', r;
END;
结果显示如下:
NOTICE: (1,a)
您必须限制返回行的数量,否则将抛出异常。
CREATE TABLE test(id INT, name VARCHAR(10))
INSERT INTO test VALUES(1, 'a');
INSERT INTO test VALUES(2, 'b');
DECLARE
TYPE r_type IS RECORD(id int, name text);
r r_type;
BEGIN
SELECT * INTO r FROM test LIMIT 1; -- 这里限制了返回的行数
RAISE NOTICE '%', r;
END;
结果显示如下:
ERROR: query returned more than one row
HINT: Make sure the query returns a single row, or use LIMIT 1.
同理,您还可以使用 FETCH INTO
和 UPDATE/INSERT/DELETE RETURNING INTO
这样带有INTO
的语句来将返回的结果传入记录变量中。用法与 SELECT INTO
类似。
当记录变量不存在非空约束时,您可以使用NULL对其进行赋值。但不允许对含有非空约束的记录变量整体赋值为NULL,或是对非空字段赋值为NULL。
记录变量的测试
记录变量可以进行非空测试、相等性测试和不等性测试。在进行相等性或不等性测试时,会从前往后逐一比较对应字段,根据其类型的比较函数进行判断。
DECLARE
TYPE r_type IS RECORD(id INT, name VARCHAR(10))
r1 r_type := r_type(1, 2);
r2 r_type := r_type(1, 2);
BEGIN
RAISE NOTICE '%', r1 IS NULL; -- false
RAISE NOTICE '%', r1 = r2; -- true
RAISE NOTICE '%', r1 > r2; -- false
r2.id = 10;
RAISE NOTICE '%', r1 < r2; -- true
END;
结果显示如下:
NOTICE: f
NOTICE: t
NOTICE: f
NOTICE: t
记录变量与表的交互
如上所述,表中的数据可以通过 SELECT INTO
、FETCH INTO
、RETURNING INTO
传入记录变量中。而记录变量也可以通过特殊语法来将其内容插入表中。
CREATE TABLE test(id INT, name VARCHAR(10));
DECLARE
TYPE r_type IS RECORD(id INT, name VARCHAR(10));
r r_type := r_type(1, 'a');
BEGIN
INSERT INTO test VALUES r; -- 使用记录变量插入表
END;
SELECT id, name FROM test;
结果显示如下:
id | name
----+------
1 | a
(1 row)
此外,您还可以使用记录变量更新表。
DECLARE
TYPE r_type IS RECORD(id INT, name VARCHAR(10));
r r_type := r_type(2, 'b');
BEGIN
UPDATE test SET ROW = r WHERE id = 1; -- 使用记录变量更新表
END;
SELECT id, name FROM test;
结果显示如下:
id | name
----+------
2 | b
(1 row)
- 本页导读 (1)
- 简介
- 语法
- 使用限制
- 记录变量的创建
- 记录变量的初始化
- 记录变量的赋值
- 记录变量的测试
- 记录变量与表的交互