Lab 18.1 Exercise Answers


This section gives you some suggested answers to the questions in Lab 18.1, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

18.1.1 Answers

a)

Explain the script ch18_1a.sql.

A1:

Answer: The declaration section of the script contains definition of the index-by table type, course_type . This type is based on the column DESCRIPTION of the table COURSE. Next, the actual index-by table is declared as course_tab .

The executable section of the script populates the course_tab table in the cursor FOR loop. Each element of the index-by table is referenced by its subscript, v_counter . For each iteration of the loop, the value of v_counter is incremented by 1 so that each new description value is stored in the new row of the index-by table.

b)

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

A2:

Answer: Your script should look similar to the following script. Changes are shown in bold letters .

 -- ch18_1b.sql, version 2.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;  DBMS_OUTPUT.PUT_LINE('course('v_counter'): 'course_tab(v_counter));  END LOOP; END; 

Consider another version of the same script.

 
 -- ch18_1c.sql, version 3.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;  FOR i IN 1..v_counter LOOP   DBMS_OUTPUT.PUT_LINE('course('i'): 'course_tab(i));   END LOOP;  END; 

When run, both versions produce the same output:

 
  course(1): DP Overview   course(2): Intro to Computers   course(3): Intro to Programming   course(4): Structured Programming Techniques   course(5): Hands-On Windows   course(6): Intro to Java Programming   course(7): Intermediate Java Programming   course(8): Advanced Java Programming   course(9): JDeveloper   course(10): Intro to Unix   course(11): Basics of Unix Admin   course(12): Advanced Unix Admin   course(13): Unix Tips and Techniques   course(14): Structured Analysis   course(15): Project Management   course(16): Database Design   course(17): Internet Protocols   course(18): Java for C/C++ Programmers   course(19): GUI Programming   course(20): Intro to SQL   course(21): Oracle Tools   course(22): PL/SQL Programming   course(23): Intro to Internet   course(24): Intro to the Basic Language   course(25): Operating Systems   course(26): Network Administration   course(27): JDeveloper Lab   course(28): Database System Principles   course(29): JDeveloper Techniques   course(30): DB Programming in Java   PL/SQL procedure successfully completed.  
c)

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

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_1d.sql, version 4.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;  DBMS_OUTPUT.PUT_LINE('course('course_tab.FIRST'): '   course_tab(course_tab.FIRST));   DBMS_OUTPUT.PUT_LINE('course('course_tab.LAST'): '   course_tab(course_tab.LAST));  END; 

Consider the statements

 
  course_tab(course_tab.FIRST)  and  course_tab(course_tab.LAST)  

used in this example. While these statements look somewhat different from the statements that you have seen so far, they produce the same effect as

 
  course_tab(1)  and  course_tab(30)  

statements because, as mentioned earlier, the FIRST and LAST methods return the subscripts of the first and last elements of a collection, respectively. In this example, the index-by table contains 30 elements, where the first element has subscript of 1, and the last element has subscript of 30.

This version of the script produces the following output:

 
  course(1): DP Overview   course(30): DB Programming in Java   PL/SQL procedure successfully completed.  
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.

A4:

Answer: Your script should look similar to the following script. All changes are shown in bold letters.

 -- ch18_1e.sql, version 5.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;  -- Display the total number of elements in the index-by   -- table   DBMS_OUTPUT.PUT_LINE ('1. Total number of elements: 'course_tab.COUNT);   -- Delete the last element of the index-by table   -- Display the total number of elements in the index-by   -- table   course_tab.DELETE(course_tab.LAST);   DBMS_OUTPUT.PUT_LINE ('2. Total number of elements: 'course_tab.COUNT);   -- Delete the fifth element of the index-by table   -- Display the total number of elements in the index-by   -- table   -- Display the subscript of the last element of the   -- index-by table   course_tab.DELETE(5);   DBMS_OUTPUT.PUT_LINE ('3. Total number of elements: 'course_tab.COUNT);   DBMS_OUTPUT.PUT_LINE ('3. The subscript of the last ''element: 'course_tab.LAST);  END; 

When run, this example produces the following output:

 
  1. Total number of elements: 30   2. Total number of elements: 29   3. Total number of elements: 28   3. The subscript of the last element: 29   PL/SQL procedure successfully completed.  

First, the total number of the elements in the index-by table is calculated via the COUNT method and displayed on the screen. Second, the last element is deleted via DELETE and LAST methods, and the total number of the elements in the index-by table is displayed on the screen again. Third, the fifth element is deleted, and the total number of the elements in the index-by table and the subscript of the last element are displayed on the screen.

Consider the last two lines on the output. After the fifth element of the index-by table is deleted, the COUNT method returns value 28, and the LAST method returns the value 29. Usually, the values returned by the COUNT and LAST methods are equal. However, when an element is deleted from the middle of the index-by table, the value returned by the LAST method is greater than the value returned by the COUNT method because the COUNT method ignores deleted elements.

18.1.2 Answers

a)

Modify the script 18_1a.sql used in Exercise 18.1.1. Instead of using an index-by table, use a nested table.

A1:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_2a.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;   course_tab course_type := course_type();  v_counter INTEGER := 0; BEGIN FOR course_rec IN course_cur LOOP v_counter := v_counter + 1;  course_tab.EXTEND;  course_tab(v_counter) := course_rec.description; END LOOP; END; 
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.

A2:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_2b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE CURSOR course_cur IS SELECT description FROM course; TYPE course_type IS TABLE OF course.description%TYPE; course_tab course_type := course_type(); v_counter INTEGER := 0; BEGIN FOR course_rec IN course_cur LOOP v_counter := v_counter + 1; course_tab.EXTEND; course_tab(v_counter) := course_rec.description; END LOOP;  course_tab.DELETE(30);   course_tab(30) := 'New Course';  END; 
A2:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_2c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE CURSOR course_cur IS SELECT description FROM course; TYPE course_type IS TABLE OF course.description%TYPE; course_tab course_type := course_type(); v_counter INTEGER := 0; BEGIN FOR course_rec IN course_cur LOOP v_counter := v_counter + 1; course_tab.EXTEND; course_tab(v_counter) := course_rec.description; END LOOP;  course_tab.TRIM;   course_tab(30) := 'New Course';  END; 

When run, this version of the script produces the following error:

 
  DECLARE   *   ERROR at line 1:   ORA-06533: Subscript beyond count   ORA-06512: at line 18  

In the previous version of the script, the last element of the nested table is removed via the DELETE method. As mentioned earlier, when the DELETE method is used, the PL/SQL keeps a placeholder of the deleted element. Therefore, the statement

 
  course_tab(30) := 'New Course';  

does not cause any errors.

In the current version of the script, the last element of the nested table is removed via the TRIM method. In this case, the PL/SQL does not keep placeholder of the trimmed element because the TRIM method manipulates the internal size of a collection. As a result, the reference to the trimmed elements causes 'Subscript beyond count' error.

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?

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_2d.sql, version 4.0 SET SERVEROUTPUT ON DECLARE CURSOR course_cur IS SELECT description FROM course; TYPE course_type IS TABLE OF course.description%TYPE; course_tab course_type := course_type(); v_counter INTEGER := 0; BEGIN FOR course_rec IN course_cur LOOP v_counter := v_counter + 1; course_tab.EXTEND; course_tab(v_counter) := course_rec.description; END LOOP; course_tab.TRIM;  course_tab.EXTEND;  course_tab(30) := 'New Course'; END; 

In order to reference the trimmed element, the EXTEND method is use to increase the size on the collection. As a result, the assignment statement

 
  course_tab(30) := 'New Course';  

does not cause any errors.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net