PL/SQL User Defined types - Record and Table

RECORD and TABLE are user-defined composite types that can be defined by the programmer. The record type allows the defination of a type that consists of specified fields. Once the type has been defined, you can then define a specific name for the type as it is being used, a variable name that references the type. The following PL/SQL coding examples show some uses of TYPE IS RECORD. In the examples, a_rec_donor is the variable dame for the type rec_donor which is defined as having three fields: rec_id, rec_name and rec_yrgoal. Notice in the first I name the fields in the INTO of the SELECT and in the second example, I use the variable name for the record type in the INTO.

SQL CODE:

SQL> edit atsd1

PL/SQL CODE:

SET VERIFY OFF
ACCEPT in_idno PROMPT 'Please enter the donor idno: '
DECLARE
   TYPE rec_donor IS RECORD
     (rec_id VARCHAR2(5), rec_name VARCHAR2(15), rec_yrgoal NUMBER(7,2));
   a_rec_donor rec_donor; -- Note: this declares a variable of the defined type
BEGIN
   SELECT idno, name, yrgoal INTO a_rec_donor.rec_id, a_rec_donor.rec_name, a_rec_donor.rec_yrgoal
   FROM new_donor WHERE idno = &in_idno;
   INSERT INTO some_donor
   VALUES(a_rec_donor.rec_id, a_rec_donor.rec_name, a_rec_donor.rec_yrgoal);
END;
/
SET VERIFY ON

SQL CODE:

SQL> SELECT * FROM new_donor;

IDNO  NAME            STADR           CITY       ST ZIP   DATEFST      YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St      Seekonk    MA 02345 03-JUL-98       500 John Smith
12121 Jennifer Ames   24 Benefit St   Providence RI 02045 24-MAY-97       400 Susan Jones
22222 Carl Hersey     24 Benefit St   Providence RI 02045 03-JAN-98           Susan Jones
23456 Susan Ash       21 Main St      Fall River MA 02720 04-MAR-92       100 Amy Costa
33333 Nancy Taylor    26 Oak St       Fall River MA 02720 04-MAR-92        50 John Adams
34567 Robert Brooks   36 Pine St      Fall River MA 02720 04-APR-98        50 Amy Costa

6 rows selected.

SQL> SELECT * FROM some_donor;

no rows selected

SQL> @ atsd1
Please enter the donor idno: 23456

PL/SQL procedure successfully completed.

Input truncated to 13 characters
SQL> SELECT * FROM some_donor;

IDNO  NAME               YRGOAL
----- --------------- ---------
23456 Susan Ash             100

SQL> edit atsd2

PL/SQL CODE:

SET VERIFY OFF
ACCEPT in_idno PROMPT 'Please enter the donor idno: '
DECLARE
   TYPE rec_donor IS RECORD
     (rec_id VARCHAR2(5), rec_name VARCHAR2(15), rec_yrgoal NUMBER(7,2));
   a_rec_donor rec_donor; -- Note: this declares a variable of the defined type
BEGIN
   SELECT idno, name, yrgoal INTO a_rec_donor
   FROM new_donor WHERE idno = &in_idno;
   INSERT INTO some_donor
   VALUES(a_rec_donor.rec_id, a_rec_donor.rec_name, a_rec_donor.rec_yrgoal);
END;
/
SET VERIFY ON

SQL> @ atsd2
Please enter the donor idno: 11111

PL/SQL procedure successfully completed.

Input truncated to 13 characters
SQL> SELECT * FROM some_donor;

IDNO  NAME               YRGOAL
----- --------------- ---------
23456 Susan Ash             100
11111 Stephen Daniels       500

The following code uses %ROWTYPE in a similar way that we have used %TYPE. In this case we are simply declaring the variable v_donor as having the same layout and types as the row in the some_donor table. ROWTYPE is useful because it allows the underlying database to be modified without having to go in and change the PL/SQL code.

Note that in the select I am taking certain columns from new_donor and inserting them in the variable v_donor which is of the row type some_donor. The fields that I have selected are fields that correspond correctly with the layout of some_donor. When I do the insert, I need to name the columns and since v_donor has the same layout as some_donor, the columns have the same name and I can refer to the as v_donor.column name. Note that in the values, I cannot use v_donor successfully.

SQL CODE:

SQL> edit atsd4

PL/SQL CODE:

SET VERIFY OFF
ACCEPT in_idno PROMPT 'Please enter the donor idno: '
DECLARE
   v_donor some_donor%ROWTYPE;
BEGIN
   SELECT idno, name, yrgoal INTO v_donor
   FROM new_donor WHERE idno = &in_idno;
   INSERT INTO some_donor
   VALUES(v_donor.idno, v_donor.name, v_donor.yrgoal);
END;
/
SET VERIFY ON

SQL CODE:

SQL> @ atsd4
Please enter the donor idno: 22222

PL/SQL procedure successfully completed.

SQL> SELECT * FROM some_donor;

IDNO  NAME               YRGOAL
----- --------------- ---------
23456 Susan Ash             100
11111 Stephen Daniels       500
22222 Carl Hersey

SQL> @ atsd4
Please enter the donor idno: 12121

PL/SQL procedure successfully completed.

Input truncated to 13 characters
SQL> SELECT * FROM some_donor;

IDNO  NAME               YRGOAL
----- --------------- ---------
12121 Jennifer Ames         400
23456 Susan Ash             100
11111 Stephen Daniels       500
22222 Carl Hersey

In the example above, please note that 12121 was the last record entered and it shows up as the first once again verifying that if you leave ordering up to Oracle you get an interesting order.

Tables

PL/SQL tables defined as TYPE IS TABLE are similar to arrays but significantly different in their implementation. Think of a PL/SQL table as similar to a database table that contains two columns. The first column would be the KEY or primary key column and would be of type binary_integer and the second column would be the VALUE column that contains the data we are putting into the table. Tables in Oracle have no limit on the number of rows, the elements in the table are not ordered in any specified way, specifically, they do not have to be sequential.

A table is defined as a TYPE field in the Declare section using the syntax:

TYPE table type name IS TABLE OF data type INDEX BY BINARY_INTEGER

Note that the data type has to be a scalar or record datatype and the index has to be binary_integer, at least in the current version.

Examples of declaring table:

TYPE t_first_table IS TABLE of whatever.first%TYPE
     INDEX BY BINARY_INTEGER;
TYPE t_second_table is TABLE of whatever.datesnd%TYPE
     INDEX BY BINARY_INTEGER;
v_first    t_first_table;
v_second   t_second_table;

In the examples above, the first table and the second table are defined as having similar types to the field first and the field datesnd on the table whatever. Then, variables are set up from each of the defined table types. The index to both of them is the required binary integer. You could also define a table using a record layout. In this case, you can also use the column names when you are putting data into the tables if you want to put data into a specific column in the table.

TYPE t_third_table IS TABLE OF whatever%ROWTYPE
     INDEX BY BINARY_INTEGER;
v_third    t_third_table

To put elements into the tables, the following assignment statements could be used.

v_first(1) := 'Snow shovel';
v_first(8) := 'Trowel';
v_first(4) := 'Sprinkler';
v_second(2) := SYSDATE;
v_third(12).city := 'Rhode Island'

The first table would now contain the following:

KEY	VALUE
1 Snow Shovel
8 Trowel
4 Sprinkler

The second table would contain the following:

KEY	VALUE
2	21-JUL-99

The third table would contain the following:

KEY	VALUE
12 RHODE ISLAND

When dealing with tables there are several attributes available that will help in the effective handling of the table data:

COUNT will return the number of rows in the table
DELETE will delete rows from a table
EXISTS will return a boolean true if the entry requested exists in the table and a boolean false if it does not exist
FIRST will return the binary_integer index of the first row
LAST will return the binary_integer index of the last row
NEXT will return the binary_integer index of the next row in the table - the one after the specified row
PRIOR will return the binary_integer index of the previous row in the table - the one before the specified row

In the example below, I have created a table called t_table_1 and then added 4 variables to that table. Notice that the numbers are not in order or in sequence. For my own sense of order and control, I would prefer to plan for more orderly numbering when possible. The next thing I did was use COUNT to tell me how many rows are in the table. COUNT returned the count of 4.

SQL CODE:

SQL> edit table1

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
  TYPE t_table1 IS TABLE OF VARCHAR2(12)
    INDEX BY BINARY_INTEGER;
  v_table_var   t_table1;
  v_count_result NUMBER;
BEGIN
  v_table_var(5) := 'Fifth week';
  v_table_var(2) := 'Second week';
  v_table_var(12) := 'Twelfth week';
  v_table_var(6) := 'Sixth week';
  v_count_result:= v_table_var.COUNT;
  dbms_output.put_line('The count is: '||v_count_result);
END;
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> @ table1
The count is: 4

PL/SQL procedure successfully completed.

In the code below, I have added an IF statement to test to see if a particular record exists. The user will be prompted to enter a number (the index number) which will come in as in_num and will be stored as v_num. The IF statement asks if there is an element in the table with the index number that was keyed in. This is asked in the code IF v_table_var.EXISTS(v_num) where v_table_var is the variable name and v_num is the keyed in index. If it is, the message the row exists will display otherwise the row does not exist will display.

SQL CODE:

SQL> edit table2

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
  TYPE t_table1 IS TABLE OF VARCHAR2(12)
    INDEX BY BINARY_INTEGER;
  v_table_var   t_table1;
  v_count_result NUMBER;
  v_num NUMBER :=&in_num;
BEGIN
   v_table_var(5) := 'Fifth week';
   v_table_var(2) := 'Second week';
   v_table_var(12) := 'Twelfth week';
   v_table_var(6) := 'Sixth week';
   v_count_result:= v_table_var.COUNT;
   dbms_output.put_line('The count is: '||v_count_result);
   IF v_table_var.EXISTS(v_num) THEN
      dbms_output.put_line('The row exists');
   ELSE
      dbms_output.put_line('The row does not exist');
   END IF;
END;
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> @ table2
Enter value for in_num: 6
old   6:   v_num NUMBER :=&in_num;
new   6:   v_num NUMBER :=6;
The count is: 4
The row exists
PL/SQL procedure successfully completed.

Input truncated to 20 characters
SQL> @ table2
Enter value for in_num: 3
old   6:   v_num NUMBER :=&in_num;
new   6:   v_num NUMBER :=3;
The count is: 4
The row does not exist

PL/SQL procedure successfully completed.

In this example, I am using the first, next, prior and last. Notice first the syntax. FIRST and LAST stand alone while NEXT and PRIOR need to have an index defined in parenthesis.

SQL CODE:

SQL> edit table3

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
  TYPE t_table1 IS TABLE OF VARCHAR2(12)
    INDEX BY BINARY_INTEGER;
  v_table_var   t_table1;
  v_count_result NUMBER;
  v_index_first BINARY_INTEGER;
  v_index_next BINARY_INTEGER;
  v_index_prior BINARY_INTEGER;
  v_index_last BINARY_INTEGER;
BEGIN
   v_table_var(5) := 'Fifth week';
   v_table_var(2) := 'Second week';
   v_table_var(12) := 'Twelfth week';
   v_table_var(6) := 'Sixth week';
   v_count_result:= v_table_var.COUNT;
   dbms_output.put_line('The count is: '||v_count_result);
   v_index_first := v_table_var.FIRST;
   dbms_output.put_line('The first record is: '||v_index_first);
   v_index_next := v_table_var.NEXT(v_index_first);
   dbms_output.put_line('The next record is: '||v_index_next);
   v_index_prior := v_table_var.PRIOR(v_index_next);
   dbms_output.put_line('The prior record is: '||v_index_prior);
   v_index_last := v_table_var.last;
   dbms_output.put_line('The last record is: '||v_index_last);
   v_index_prior := v_table_var.PRIOR(v_index_last);
   dbms_output.put_line('The prior record is: '||v_index_prior);
   v_index_first := v_table_var.FIRST;
   dbms_output.put_line('The first record is: '||v_index_first);
END;
/
SET SERVEROUTPUT OFF

The execution is shown below. Compare the order of the table as I created and the results. The first and last return the first and last rows, however, you need to note that the first row is defined by Oracle as the row that has the lowest index while the last row is defined by Oracle as the row that has the highest index. When I worked with next and prior, I had to provide an index. After I displayed first, I then used the index on first to come up with next. Next is the next row in order by index. I then used the next index (which is what I was currently looking at) to display prior - it went back to the first. I then showed the last and used the last index to show prior. This showed the row with the index that put it sequentially above last. Finally I showed the first again.

SQL CODE:

SQL> @ table3
The count is: 4
The first record is: 2
The next record is: 5
The prior record is: 2
The last record is: 12
The prior record is: 6
The first record is: 2

PL/SQL procedure successfully completed.

The following example shows the use of the delete and then demonstrates the results using first and next. The user inputs the index of the row in the table to be deleted. That number is assigned to an index field of binary integer and then it is used to delete the row from the table. In my example, I deleted the row with index 5. Note that when I display first I get 2, then next gets me 6, next again gets me 12 and the request for next again returns nothing since 5 was deleted and there are now only 3 rows in the table. This is confirmed by the display of the record count both before and after the delete. Before the delete it reads 4, after the delete it reads 3.

SQL CODE:

SQL> edit table4

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
  TYPE t_table1 IS TABLE OF VARCHAR2(12)
    INDEX BY BINARY_INTEGER;
  v_table_var   t_table1;
  v_count_result NUMBER;
  v_num NUMBER :=&in_num;
  v_index_first BINARY_INTEGER;
  v_index_next BINARY_INTEGER;
  v_index_in BINARY_INTEGER;
BEGIN
   v_table_var(5) := 'Fifth week';
   v_table_var(2) := 'Second week';
   v_table_var(12) := 'Twelfth week';
   v_table_var(6) := 'Sixth week';
   v_count_result:= v_table_var.COUNT;
   dbms_output.put_line('The count is: '||v_count_result);
   v_index_in := v_num;
   v_table_var.DELETE(v_index_in);
   v_index_first := v_table_var.FIRST;
   dbms_output.put_line('The first record is: '||v_index_first);
   v_index_next := v_table_var.NEXT(v_index_first);
   dbms_output.put_line('The next record is: '||v_index_next);
   v_index_next := v_table_var.NEXT(v_index_next);
   dbms_output.put_line('The next record is: '||v_index_next);   
   v_index_next := v_table_var.NEXT(v_index_next);
   dbms_output.put_line('The next record is: '||v_index_next); 
   v_count_result:= v_table_var.COUNT;
   dbms_output.put_line('The count is: '||v_count_result);  
END;
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> @ table4
Enter value for in_num: 5
old   6:   v_num NUMBER :=&in_num;
new   6:   v_num NUMBER :=5;
The count is: 4
The first record is: 2
The next record is: 6
The next record is: 12
The next record is:
The count is: 3

PL/SQL procedure successfully completed.

When using tables in Oracle, it is a good idea to use count as I did simply because the size of the table is unconstrained. You could also choose to keep your own row count that you increment everytime you add a row to the table. If it is possible using indexes of 1 and then 2, 3, 4 etc. will allow you a little more control in processing. Sometimes it isn't possible and as we have seen in the examples above, Oracle accommodates the indexes you choose. If you want to delete a whole table for some reason, you can assign null to it. This would be done with the statement: v_table_var := NULL;.