User Defined Record

📖 Theory

The Scattered Student Profile

Imagine your university library needs a custom verification receipt printed at the gate. The security guard needs to see three distinct things: the student's name from the members table, the book title from the books table, and the calculated late fee. If you use standard scalar variables, you have to declare three separate, loose variables, track them individually, and pass all three through your program loops. Why carry three loose sheets of paper when you could staple them into a single neat report?

📖 Theory

The Custom Travel Organizer Pouch

Think of basic scalar variables like putting your loose coins, paper cash, and passport into different pockets of your jeans. It is easy to lose track of one. Now, think of a %ROWTYPE variable like buying a pre-made box that explicitly matches one specific table structure. A User-Defined Record is like designing your own custom travel organizer pouch from scratch! You decide exactly how many pockets it has: one small slot for a numeric fine, a long transparent pocket for a text name, and a square slot for a date stamp. You create the blueprint, name the pouch, and use it to hold a custom bundle of data.

📖 Theory

What is a User-Defined Record?

A User-Defined Record is a composite data type in PL/SQL that allows you to group multiple related fields, each with its own name and data type, into a single structured unit. Unlike %ROWTYPE which automatically mirrors an entire existing table or view row, a user-defined record gives you absolute control. You use the TYPE ... IS RECORD statement in the DECLARE block to define the custom blueprint, and then instantiate variables based on that new type.

📊 At a glance

Structural behavioral differences between table row anchors and custom records

⌨️ Practical

Defining and Populating a Custom Library Record

-- Defining a custom type and loading data using dot notation
DECLARE
  -- Step 1: Create the blueprint/type structure
  TYPE GatePassRecord IS RECORD (
    student_name members.name%TYPE,
    borrowed_isbn books.book_id%TYPE,
    fine_balance  NUMBER(7,2) := 0.00
  );
  
  -- Step 2: Declare an actual variable of our custom type
  v_pass GatePassRecord;
BEGIN
  -- Accessing fields and assigning data dynamically via dot notation
  v_pass.borrowed_isbn := 9005;
  v_pass.fine_balance := 45.50;
  
  -- Streaming direct query outputs straight into individual record components
  SELECT name 
  INTO v_pass.student_name
  FROM members
  WHERE member_id = 101;
  
  DBMS_OUTPUT.PUT_LINE('Gate Verification Successful for: ' || v_pass.student_name);
  DBMS_OUTPUT.PUT_LINE('Outstanding Clearances: Rs. ' || v_pass.fine_balance);
END;
/

🪜 Follow along

The Lifecycle of a Record Lifecycle

1. 1. Compile Blueprint Oracle reads the TYPE definition block and registers your custom composite layout in local session memory.
2. 2. Memory Allocation The variable declaration statement reserves separate continuous memory slots for each internal field element defined.
3. 3. Component Targeting Using the dot operator (variable.field), you target explicit compartments within the structure for local assignments.
4. 4. Monitored Selection A SELECT INTO query returns exactly one row, filling only the mapped fields without breaking adjacent data nodes.

🧩 Quiz

Which of the following syntax patterns correctly defines a user-defined record type inside a PL/SQL DECLARE block?

1. CREATE TYPE record_name AS RECORD (field1 NUMBER);
2. TYPE type_name IS RECORD (field1 data_type, field2 data_type);
3. DECLARE RECORD type_name (field1 NUMBER, field2 VARCHAR2);
4. RECORD type_name IS TYPE (field1 data_type);

⚠️ Watch out

The Complete Equality Comparison Blindspot

Here is an absolute trap that external university examiners love using to cut marks! In PL/SQL, you cannot directly compare two record variables using standard equality operators. Writing IF v_pass1 = v_pass2 THEN will trigger an immediate compilation failure. Oracle does not know how to evaluate a global comparison across composite data structures. You must evaluate them field-by-field, like this: IF v_pass1.student_name = v_pass2.student_name THEN.

💭 Think first

The Record-to-Record Assignment Test

Mental Challenge: Can you assign one record variable directly to another record variable (e.g., v_pass1 := v_pass2;) without writing out all the dot notations field-by-field? Think about type mapping mentally before tapping.

📖 Theory

Semester 4 Lab Project Architecture

When designing complex library management or banking applications for your end-of-semester lab presentations, use records to clean up your code. If a PL/SQL Function needs to return multiple values (like a student's eligibility status AND their total books checked out), you cannot use scalar types. Return a single custom record type instead to keep your modular data pipeline clean and elegant!

✅ Summary

Key takeaways

• User-defined records allow developers to construct unique, multi-field composite data structures.
• Creating records requires a two-step dance: define the structure type first, then declare the variable.
• Individual internal attributes are read and updated using dot notation syntax (variable_name.field_name).
• Records provide localized architectural flexibility that table %ROWTYPE anchors cannot match.
• Direct boolean comparisons (=, !=) on full record blocks are completely illegal in PL/SQL.
• Memory hook: Map the type layout first, name your custom box next, and pick fields using a dot!