Lesson Eight A: PLSQL Records

1
Lesson Eight A PL/SQL Records

• Eric Langager
• Beihang College of Software

2
What is a Record?

• Similar in concept to a row in a database table.
• Composite data structure, i.e. it contains a
  group of elements, basically the fields from a
  row.
• In PL/SQL, it functions basically as a variable
  for a row.
• Comes in one of three types
• Table-based
• Cursor-based
• Programmer-defined

3
Benefits of Using Records

• Data Abstraction
• Data is generalized. Records allow you to get
  away from the details and see the big picture.
• Aggregate Operations.
• Records allow you to perform operations on whole
  blocks of data.
• Leaner, Cleaner Code.
• Records allow you to write cleaner code, and less
  code, because you will need fewer comments and
  fewer variables.
• (see DataMove.sql)

4
Table-Based Records

• Contain the same fields as a row in a table
• CREATE TABLE students (
• id NUMBER(5) PRIMARY KEY,
• first_name VARCHAR2(20),
• last_name VARCHAR2(20),
• major VARCHAR2(30),
• current_credits NUMBER(3)
• )
• ------------------------------------------------
  ----
• studentsrec.id
• studentsrec.first_name
• studentsrec.last_name
• studentsrec.major
• studentsrec.current_credits
• The select statement must match the declared
  variables.
• (see BadFetch.sql)

5
Table-Based Records

• Declared with the ROWTYPE attribute.
• DECLARE
• studentsrec studentsROWTYPE
• cursor c_move is
• select from students2
• BEGIN
• open c_move
• loop
• FETCH c_move into studentsrec

6
Cursor-Based Records

• Structure is drawn from the SELECT list of a
  cursor.
• DECLARE
• cursor c_move is
• SELECT from students2
• studentsrec c_moveROWTYPE

7
Programmer-Defined

• Declare a record type
• DECLARE
• TYPE empdept_rectype IS RECORD
• (ename VARCHAR2(10),
• job VARCHAR2(9),
• dname VARCHAR2(14),
• loc VARCHAR2(13))
• Then declare a record based on the type
• empdeptdata empdept_rectype