备案控制台
文档
产品文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版（兼容Oracle） 开发指南（Oracle语法兼容2.0） PL/SQL 记录变量

记录变量

更新时间：
一键部署
产品详情
相关技术圈
我的收藏

本文介绍了记录变量的语法和使用方法等相关内容。

简介

记录变量（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子句包含记录变量，则不允许在该子句中使用任何其他变量或值。

记录变量的创建

您可以通过以下三种方式来创建一个记录变量：

  1. 定义一个记录类型，然后声明该类型的变量。

  2. 使用%ROWTYPE声明一个记录变量，该变量表示数据库表或视图的完整行或行的一部分，在%ROWTYPE之前可以是表、视图、显式游标或强游标变量。

  3. 使用%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)
文档推荐
  • 本页导读 （0）
文档反馈