Oracle基本修練: PL/SQL Record

ChunJen Wang

Published in

jimmy-wang

8 min readSep 12, 2021

本篇將筆記如何使用Record來管理program data。

Content

Overview of PL/SQL Record
Declaring records: Table-based, Cursor-based, Programmer-defined
Referencing a record’s field
Assigning records
Records and INSERT / UPDATE statements
Nested record

Let’s Start!

1. Overview of PL/SQL Record

record代表的是一種組合的資料結構(composite data structure)，其組成會包含多個欄位資料，例如下方由first name, last name, email, and phone number組成，就可以視為record。

而使用record最大的好處是，讓我們的程式執行可以從 field-level to record-level operations。

2. Declaring records: Table-based, Cursor-based, Programmer-defined

a) Table-based
%ROWTYPE attribute就是可以一次將跨欄位的一row資料存入一個變量中。

DECLARE
   record_name table_name%ROWTYPE;

b) Cursor-based

DECLARE
    record_name cursor_name%ROWTYPE;

c) Programmer-defined
若要建立的record結構與原資料相同，則使用table-based, cursor-based就夠用了；但若我們需要重構record，那我們就會需要採用programmer-defined record。其結構就如

[define]首先，根據要重構的structure來定義 record type

TYPE record_type IS RECORD (
    field_name1 data_type1 [[NOT NULL] := | DEFAULT default_value],
    field_name2 data_type2 [[NOT NULL] := | DEFAULT default_value],
    ...
);

[declare]再宣告record (based on the record type)

record_name record_type;

以一個範例來看：

DECLARE
TYPE r_customer_contact_t     　　　　　　     -- define a record type
IS
  RECORD
  (
    customer_name customers.name%TYPE,
    first_name    contacts.first_name%TYPE,
    last_name     contacts.last_name%TYPE );
  r_customer_contacts r_customer_contact_t;　-- declare a record
BEGIN
  NULL;
END;

3. Referencing a record’s field

如何從record中取出其中一個欄位資料? 使用record.[欄位名稱]

record_name.field_name

4. Assigning records

如何將record進行賦值? 有兩種情境，
第一種是針對整個record進行賦值，例如

r_contact1 := r_contact2;

第二是針對record中的某幾個欄位進行賦值。

r_contact.first_name := 'John';
r_contact.last_name := 'Doe';
r_contact.phone := '(408-654-2865)';

其他如 SELECT INTO , FETCH INTO 的做法也是可以達到相同的效果。

-- 1) SELECT INTO
SELECT
  first_name, last_name, phone
INTO
  r_contact
FROM
  contacts
WHERE
  contact_id = 100;-- 2) fetch a whole record
FETCH cur_contacts INTO r_contact;-- 3) fetch individual fields
FETCH
  cur_contacts
INTO
  r_contact.first_name, 
  r_contact.last_name, 
  r_contact.phone;

5. Records and INSERT / UPDATE statements

而record也可以透過insert/update來新增或更新資料。例如我們先建立一個表 persons 進行實作。

CREATE TABLE persons  (
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2( 50 ) NOT NULL, 
    last_name VARCHAR2( 50 ) NOT NULL,
    primary key (person_id)
);

透過insert來把資料放入record(r_person):

DECLARE
  r_person persons%ROWTYPE;BEGIN
  -- assign values to person record
  r_person.person_id  := 1;
  r_person.first_name := 'John';
  r_person.last_name  := 'Doe';  -- insert a new person
  INSERT INTO persons VALUES r_person;
END;

或者，透過update來更新record(r_person)，需要搭配 SET ROW來執行:

DECLARE
  r_person persons%ROWTYPE;BEGIN
  -- get person data of person id 1
  SELECT * INTO r_person 
  FROM persons 
  WHERE person_id = 1;  -- change the person's last name
  r_person.last_name  := 'Smith';  -- update the person
  UPDATE persons
  SET ROW = r_person
  WHERE person_id = r_person.person_id;
END;

6. Nested record

當然我們也可以建立nested record，以節省繁複的code。
例如建立一個customer這個record，但底下再包一個address，那麼ship_to 與bill_to 就會包含了address所定義的資料。

DECLARE
  TYPE address IS RECORD (
    street_name VARCHAR2(255),
    city VARCHAR2(100),
    state VARCHAR2(100),
    postal_code VARCHAR(10),
    country VARCHAR2(100)
  );
  TYPE customer IS RECORD(
      customer_name VARCHAR2(100),
      ship_to address,
      bill_to address
  );
  r_one_time_customer customer;
BEGIN  r_one_time_customer.customer_name := 'John Doe';
  -- assign address
  r_one_time_customer.ship_to.street_name := '4000 North 1st street';
  r_one_time_customer.ship_to.city := 'San Jose';
  r_one_time_customer.ship_to.state := 'CA';
  r_one_time_customer.ship_to.postal_code := '95134';
  r_one_time_customer.ship_to.country := 'USA';
  -- bill-to address is same as ship-to address
  r_one_time_customer.bill_to := one_time_customer.ship_to;
END;

以上為PL/SQL會使用到RECORD使用方式。

資料來源:
https://www.oracletutorial.com/plsql-tutorial/plsql-record/