PL/SQL User Defined Record and Table

Speaker Notes

Slide 1:

RECORD and TABLE are user-defined composite types that can be defined by the programmer/developer. The RECORD type allows the developer to define a type consisting of specific fields/columns. The TABLE type is similiar to array but there are very significant differences in implementation.

Please be sure to check the notes and other sources for additional information.

We are no getting more deeply into the kind of code that will frequently be done in Oracle anonymous blocks. By the way, remember that we are working with anonymous block and that in fact we will be looking at other coding structures soon and we need to be able to draw the distinction.

Slide 2:

One reason that what is traditionally thought of as records in a table are called rows is that there is another data structure in Oracle called records that is very similar in both concept and structure to the rows in the table.

NOTE the vocabulary - in Oracle the data is a table and a table is made up of rows which is made up of columns.

The table record is created within Oracle and it consists of fields.

There is a definite difference between row and record and column and field in these definitions.

Note we will not deal with cursor based in this presentation.

Slide 3:

In the next presentation, we will discuss explicit cursors. It is important to understand the difference.

Slide 4:

The code in this anonymous block simply takes a record based on the identification number entered by the user and inserts it into another table.

The programmer has defined rec_donor as being comprised of three fields that will be filled by the SELECT.

The implicit cursor SELECT INTO moves the data into the individual fields or elements in the record defined in the declare section.

This example does not really take advantage of the type definition. The code could have been done in other ways. The next slide will use the type definition more advantageously.

Slide 5:

In this example the SELECT ... INTO simply referes to the variable of the defined type, a_rec_donor.

In this example we are using the implicit cursor SELECT INTO to fill the value in the defined record. In this example we selected into the whole record. In the previous example we selected into the individual fields.

Note that the INTO clause of the implicit query is the only clause that can refer to the whole rather than the component elements or fields.

Slide 6:

An error occured in the VALUE clause.

"PLS-00382 expression is of wrong type

Cause: An expression has the wrong datatype for the context in which it was found.

Action: Change the datatype of the expression. You might want to use datatype conversion functions."

Copyright(C) 1995 Oracle Corporation.

Again Note that the INTO clause is the only one that can refer to the whole, the other clauses must refer to the individual elements.

Slide 7:

Note that new_donor has the same layout as the donor table that has been used repeatedly in these examples.

%ROWTYPE is ued to create a table record. %ROWTYPE is similiar to %TYPE except that it declares a composite structure while %TYPE declares a simple (scalar) variable.

Note that the structure is:

record_name table_name%ROWTYPE

In the example above, v_donor is the record name and some_donor is the table name. Essentially this is saying that v_donor has the same layout as seen when you look at the description of some_donor.

Slide 8:

This shows the execution of the anonymous block from the previous slide.

Slide 9:

As with records, when you work with tables you first define the table structure using the TYPE...TABLE statement and then declare the actual table using that table type.

Note that table types have some similarities to arrays but there are significant differences.

Think of the table as having two columns - the primary key column and the value column.

Syntax:

TYPE table_type_name IS TABLE OF datatype INDEX BY BINARY_INDEX.

Okay so now we have another use of "table"!

Please be very sure to see the notes on tables - some of the basic things and definitions of attributes are not included in this presentation.

Slide 10:

This uses the count attribute to count the number of records in the table.

v_table_var.COUNT

Note that v_table_var is of the type t_table1

Slide 11:

The first four lines of the output are produced in the FOR loop which is set up to show each element as execution happens.

The last line is the result of doing a count using v_table_var.COUNT.

Slide 12:

This uses .EXIST to test for existence of the number that is keyed in. The test for exists is the condition on an IF statement so that if different messages are displayed depending on whether the record exists.

Slide 13:

Note that I can make v_index a number or a binary_integer.

Notice that the table is gone through starting with the lowest number - 401 - even though that is not the order in which the elements are added.

Slide 14:

Note again that v_index can be number or binary_integer.

The first five commands under begin are filling the array with data. Essential v_area(508) is assigned 'Southeastern MA' while v_area(401) is assigned 'Rhode Island'. Notice the order of these assignments does not matter.

Slide 15:

.LAST looks at the last row

.NEXT returns the next row after the specified

Note that NEXT has to have the index defined in parenthesis.

.FIRST returns the first row