Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources]

Benjamin Rosenzweig

نسخه متنی -صفحه : 289/ 200
نمايش فراداده

Lab 18.1 Exercises

18.1.1 Use Index-By Tables

In this exercise, you will learn more about index-by tables discussed earlier in the chapter.

Create the following PL/SQL script:

-- ch18_1a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
CURSOR course_cur IS
SELECT description
FROM course;
TYPE course_type IS TABLE OF course.description%TYPE
INDEX BY BINARY_INTEGER;
course_tab course_type;
v_counter INTEGER := 0;
BEGIN
FOR course_rec IN course_cur LOOP
v_counter := v_counter + 1;
course_tab(v_counter) := course_rec.description;
END LOOP;
END;



Answer the following questions:
a)

Explain the script ch18_1a.sql.
b)

Modify the script so that rows of the index-by table are displayed on the screen.
c)

Modify the script so that only first and last rows of the index-by table are displayed on the screen.
d)

Modify the script by adding the following statements and explain the output produced:
  1. Display the total number of elements in the index-by table after it has been populated on the screen.
  2. Delete the last element, and display the total number of elements of the index-by table again.
  3. Delete the fifth element, and display the total number of elements and the subscript of the last element of the index-by table again.
 
 18.1.2 Use Nested Tables
 

In this exercise, you will learn more about nested tables discussed earlier in this chapter.
Answer the following questions:
a)

Modify the script 18_1a.sql used in 


b)

Modify the script by adding the following statements and explain the output produced:
  1. Delete the last element of the nested table, and then reassign a new value to it. Execute the script.
  2. Trim the last element of the nested table, and then reassign a new value to it. Execute the script.
 
c)

How would you modify the script created, so that there is no error generated when a new value is assigned to the trimmed element?