Working with RECORD Type

Example: FETCH Assigns Values to Record that Function Returns

DECLARE

  TYPE EmpRecTyp IS RECORD (

    emp_id  employees.employee_id%TYPE,

    salary  employees.salary%TYPE

  );

  CURSOR desc_salary RETURN EmpRecTyp IS

    SELECT employee_id, salary

    FROM employees

    ORDER BY salary DESC;

  highest_paid_emp       EmpRecTyp;

  next_highest_paid_emp  EmpRecTyp;

  FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS

    emp_rec  EmpRecTyp;

  BEGIN

    OPEN desc_salary;

    FOR i IN 1..n LOOP

      FETCH desc_salary INTO emp_rec;

    END LOOP;

    CLOSE desc_salary;

    RETURN emp_rec;

  END nth_highest_salary;

BEGIN

  highest_paid_emp := nth_highest_salary(1);

  next_highest_paid_emp := nth_highest_salary(2);

  DBMS_OUTPUT.PUT_LINE(

    'Highest Paid: #' ||

    highest_paid_emp.emp_id || ', $' ||

    highest_paid_emp.salary

  );

  DBMS_OUTPUT.PUT_LINE(

    'Next Highest Paid: #' ||

    next_highest_paid_emp.emp_id || ', $' ||

    next_highest_paid_emp.salary

  );

END;

/

Example: UPDATE Statement Assigns Values to Record Variable

DECLARE

  TYPE EmpRec IS RECORD (

    last_name  employees.last_name%TYPE,

    salary     employees.salary%TYPE

  );

  emp_info    EmpRec;

  old_salary  employees.salary%TYPE;

BEGIN

  SELECT salary INTO old_salary

   FROM employees

   WHERE employee_id = 100;

  UPDATE employees

    SET salary = salary * 1.1

    WHERE employee_id = 100

    RETURNING last_name, salary INTO emp_info;

  DBMS_OUTPUT.PUT_LINE (

    'Salary of ' || emp_info.last_name || ' raised from ' ||

    old_salary || ' to ' || emp_info.salary

  );

END;

/

Example: Assigning NULL to Record Variable

DECLARE

  TYPE age_rec IS RECORD (

    years  INTEGER DEFAULT 35,

    months INTEGER DEFAULT 6

  );

  TYPE name_rec IS RECORD (

    first  employees.first_name%TYPE DEFAULT 'John',

    last   employees.last_name%TYPE DEFAULT 'Doe',

    age    age_rec

  );

  name name_rec;

  PROCEDURE print_name AS

  BEGIN

    DBMS_OUTPUT.PUT(NVL(name.first, 'NULL') || ' ');

    DBMS_OUTPUT.PUT(NVL(name.last,  'NULL') || ', ');

    DBMS_OUTPUT.PUT(NVL(TO_CHAR(name.age.years), 'NULL') || ' yrs ');

    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(name.age.months), 'NULL') || ' mos');

  END;

BEGIN

  print_name;

  name := NULL;

  print_name;

END;

/

Record Comparisons

Records cannot be tested natively for nullity, equality, or inequality. These BOOLEAN expressions are illegal:

• My_Record IS NULL
• My_Record_1 = My_Record_2
• My_Record_1 > My_Record_2

You must write your own functions to implement such tests.

Example: Initializing Table by Inserting Record of Default Values

DROP TABLE schedule;

CREATE TABLE schedule (

  week  NUMBER,

  Mon   VARCHAR2(10),

  Tue   VARCHAR2(10),

  Wed   VARCHAR2(10),

  Thu   VARCHAR2(10),

  Fri   VARCHAR2(10),

  Sat   VARCHAR2(10),

  Sun   VARCHAR2(10)

);

DECLARE

  default_week  schedule%ROWTYPE;

  i             NUMBER;

BEGIN

  default_week.Mon := '0800-1700';

  default_week.Tue := '0800-1700';

  default_week.Wed := '0800-1700';

  default_week.Thu := '0800-1700';

  default_week.Fri := '0800-1700';

  default_week.Sat := 'Day Off';

  default_week.Sun := 'Day Off';

  FOR i IN 1..6 LOOP

    default_week.week    := i;

   

    INSERT INTO schedule VALUES default_week;

  END LOOP;

END;

/

Example: Updating Rows with Record

DECLARE

  default_week  schedule%ROWTYPE;

BEGIN

  default_week.Mon := 'Day Off';

  default_week.Tue := '0900-1800';

  default_week.Wed := '0900-1800';

  default_week.Thu := '0900-1800';

  default_week.Fri := '0900-1800';

  default_week.Sat := '0900-1800';

  default_week.Sun := 'Day Off';

  FOR i IN 1..3 LOOP

    default_week.week    := i;

 

    UPDATE schedule

    SET ROW = default_week

    WHERE week = i;

  END LOOP;

END;

/

Restrictions on Record Inserts and Updates

These restrictions apply to record inserts and updates:

• Record variables are allowed only in these places:

o    On the right side of the SET clause in an UPDATE statement

o    In the VALUES clause of an INSERT statement

o    In the INTO subclause of a RETURNING clause

Record variables are not allowed in a SELECT list, WHERE clause, GROUP BY clause, or ORDER BY clause.

• The keyword ROW is allowed only on the left side of a SET clause. Also, you cannot use ROW with a subquery.
• In an UPDATE statement, only one SET clause is allowed if ROW is used.
• If the VALUES clause of an INSERT statement contains a record variable, no other variable or value is allowed in the clause.
• If the INTO subclause of a RETURNING clause contains a record variable, no other variable or value is allowed in the subclause.
• These are not supported:

o    Nested RECORD types

o    Functions that return a RECORD type

o    Record inserts and updates using the EXECUTE IMMEDIATE statement.

I hope you all have enjoyed reading this article. Comments are welcome....