全部產品
Search

搜尋本產品

    PolarDB：記錄變數

    文件中心

    PolarDB：記錄變數

    更新時間：Jul 06, 2024

    本文介紹了記錄變數的文法和使用方法等相關內容。

    簡介

    記錄變數（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)