records.sql

-- PL/SQL has three records -> table-based, cursor-based, programmer-defined

-- table-based, from contacts table
DECLARE 
    r_contact contacts%ROWTYPE;
BEGIN
    SELECT first_name, last_name, phone
    INTO r_contact
    FROM contacts WHERE contact_id = 100;
END;
    
-- Cursor-based
DECLARE
    CURSOR c_contacts IS
        SELECT first_name, last_name, phone FROM contacts;
    r_contact c_contacts%ROWTYPE;
BEGIN
    NULL;
END;
    
-- Programmer-defined
DECLARE
    -- Define record type
    TYPE r_customer_contact_t IS RECORD(
        customer_name customers.name%TYPE,
        first_name contacts.first_name%TYPE,
        last_name contacts.last_name%TYPE
    );
    -- Declare record
    r_customer_contacts r_customer_contact_t;
BEGIN
    NULL;
END;

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

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

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