Records and Collections

Last night I finished up my study guide book on Oracle SQL and PL/SQL. There was a lot to take in. The second to last chapter was on records and collections.

Records are structures that are composed of fields. Those fields can be scalar types (like VARCHAR2), or they can be record types themselves.

Explicit records are defined when you list out each field and its type. An implicit record is one which takes its type from another object using %ROWTYPE.

I already kind of knew about implicit and explicit records. However I discovered that other things I knew were also implicit records. This includes the record in a FOR LOOP when you loop through a cursor. It also includes the :OLD and :NEW modifiers in a trigger.

You can assign one record variable to another if they are of the same exact type. You cannot do this if they are similar types, even if those two different types match field by field. Also you cannot use records as VALUES in an INSERT statement.

The second to last chapter went over the different collections available in PL/SQL. I will just mention the first one here. They are called Index-by Tables. Previously they were called PL/SQL tables. You can put any database types in these collections, as well as PL/SQL specific types. Maybe I will write another post on the other types of collections in the future.