The following is an example in which the nested table is declared and used in an expression. You can use the nested table in all PL/SQL expressions. In this example, the nested table is named coworkers:

DECLARE
TYPE emp_table IS TABLE OF emp;
coworkers emp_table := emp_table (`Loretta','Robert','Stanton','Delores','Mary');
INDEX BY BINARY_INTEGER;
BEGIN
IF coworkers(i) = `Timothy' THEN...

The following example illustrates the nesting of tables within PL/SQL. In this example, the department table will have a column named courses that contains all the courses offered by that department. The first part of the example creates an object type named courses:

CREATE TYPE courses AS OBJECT
(course_no   NUMBER(4),
title    VARCHAR2(100));

After this object type is created, you now create the table type called courselist:

CREATE TYPE courselist AS TABLE OF courses;

Finally, you create the table named department with a nested table column named courses:

CREATE TABLE department
(name     VARCHAR2(100),
professor    VARCHAR2(55),
courses   courselist)
NESTED TABLE courses STORE AS courses_table;

The following is an example of inserting data into the nested table named department. Note how the multiple courses are entered into the course column:

INSERT into department
values (`ENGLISH','Kathy Johns',
courselist (course(100,'Modern English'),
course(200,'Poetry'),
course(300,'Advanced Modern English'));

Likewise, you can perform a SELECT statement on the department table by executing the command shown in the following example:

SELECT course_no, title into var_course_no, var_title
from (SELECT courses from department
WHERE name = `ENGLISH')
WHERE course_no = 200;

In this example, you select the English course with the course number 200 in the department table.

Summary

Today's lesson presents you with a quick overview of using SQL statements within a PL/SQL block. You learned how to insert and retrieve data from tables that you created and how to prepare for runtime errors. You also learned about PL/SQL variables—how to declare them; how to use them; and how to base them on other objects such as database tables, columns, constants, and even other variables. The %type attribute enables you to keep the variable declaration in synch with the datatypes in the database. PL/SQL tables are objects of type TABLE and look similar to database tables but differ slightly. These tables have one column and a binary primary index.

Q&A

Q What happens if my SQL statement fails inside the PL/SQL block?

A The processing of your PL/SQL block will terminate unless you have code in place to trap and handle the exception. PL/SQL allows for numerous predefined exceptions that make your coding easier.

Q What can a PL/SQL table do for me that a database table can't?

A A PL/SQL table provides several opportunities to the developer. The PL/SQL table is not bound to any size constraints, so as the rows are added, so grows the table. You don't have to ponder initial and incremental sizing issues. Many times a table is used as a working variable, so you do not want it to become an integral part of the schema.

Q What is a PL/SQL record?

A A PL/SQL record is a variable with the type record. It is a composite structure containing fields with its own datatypes. Records can help the developer by reducing the volume of code necessary. Records allow similar fields to be grouped and treated as one logical entity.

Workshop

The following workshop will test your comprehension of this chapter and give you an opportunity to practice what you have just learned. The answers to the quiz and exercises are provided in Appendix A, "Answers."

Quiz

1. Name some of the database objects that you can base a variable declaration on.
2. What is the variable attribute you use to base a variable on a table column?

3. Name several of the PL/SQL table built-in functions.
4. What are the four SQL DML statements permitted in a PL/SQL block?

Exercises

Evaluate each of the following three declarations and determine which ones are legal and which ones are not legal. Explain your answer for those that are not legal.

1. Legal or not legal:

   DECLARE
   emp_rec        emp_rec_type;
   

2. Legal or not legal:

   DECLARE
   emp_last_name         %type;
   

3. Legal or not legal:

   DECLARE
   TYPE   emp_table_type is table of VARCHAR2(55);
   emp_dept_table   emp_table_type;