Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

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

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.

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.

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.

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

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;
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.

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.

Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

فارسی

کردی

العربیه

اردو

Türkçe

Русский

English

Français

کانال فیلم من

تبیان من

فایلهای من

کتابخانه من

پنل پیامکی

وبلاگ من

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی