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

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

Lab 18.3 Exercise Answers

This section gives you some suggested answers to the questions in Lab 18.3, 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.3.1 Answers

A1:

Answer: Your output should look similar to the following:


table_tab2(1)(1): 1 
table_tab2(1)(2): 2 
table_tab2(1)(3): 3 
table_tab2(2)(1): 3 
table_tab2(2)(2): 2 
table_tab2(2)(3): 1 
PL/SQL procedure successfully completed.

The script ch18_4a.sql uses multilevel index-by tables or an index-by table of index-by tables. The declaration portion of the script defines a multilevel index-by table table_tab2

. Each row of this table is an index-by table consisting of three rows. The executable portion of the script populates the multilevel table via nested numeric FOR loops. In the first iteration of the outer loop, the inner loop populates the index-by table table_tab1

with values 1, 2, 3, and the first row of the multilevel table table_tab2

. In the second iteration of the outer loop, the inner loop populates the index-by table table_tab1

with values 3, 2, 1, and the second row of the multilevel table table_tab2.

A2:

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


-- ch18_4b.sql, version 2.0
SET SERVEROUTPUT ON
DECLARE
TYPE table_type1 IS TABLE OF integer INDEX BY BINARY_INTEGER;
TYPE table_type2 IS TABLE OF table_type1; 
table_tab1 table_type1;
table_tab2 table_type2 := table_type2(); 
BEGIN
FOR i IN 1..2 LOOP
table_tab2.EXTEND; 
FOR j IN 1..3 LOOP
IF i = 1 THEN
table_tab1(j) := j;
ELSE
table_tab1(j) := 4 - j;
END IF;
table_tab2(i)(j) := table_tab1(j);
DBMS_OUTPUT.PUT_LINE ('table_tab2('||
i||')('||j||'): '||table_tab2(i)(j));
END LOOP;
END LOOP;
END;

In this version of the script, the table_type2 is declared as a nested table of index-by tables. Next, table_tab2 is initialized prior to its use, and its size is extended before a new element is assigned a value.

A3:

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


-- ch18_4c.sql, version 3.0
SET SERVEROUTPUT ON
DECLARE
TYPE table_type1 IS VARRAY(3) OF integer; 
TYPE table_type2 IS TABLE OF table_type1; 
table_tab1 table_type1 := table_type1(); 
table_tab2 table_type2 := table_type2(table_tab1); 
BEGIN
FOR i IN 1..2 LOOP
table_tab2.EXTEND; 
table_tab2(i) := table_type1(); 
FOR j IN 1..3 LOOP
IF i = 1 THEN
table_tab1.EXTEND; 
table_tab1(j) := j;
ELSE
table_tab1(j) := 4 - j;
END IF;
table_tab2(i).EXTEND; 
table_tab2(i)(j):= table_tab1(j);
DBMS_OUTPUT.PUT_LINE ('table_tab2('||i||')('||j||'): '||
table_tab2(i)(j));
END LOOP;
END LOOP;
END;

In this declaration section of the script, the table_type1 is defined as a varray with three integer elements, and the table_type2 is declared as a nested table of varrays. Next, table_tab1 and table_tab2 are initialized prior to their uses.

In the executable portion of the script, the size of the table_tab2 is incremented via the EXTEND method and its individual elements are initialized as follows:


table_tab2(i) := table_type1();

Notice that that each element is initialized via the constructor associated with the varray type table_type1. Furthermore, in order to populate a nested table, a new varray element must be added to the each nested table element as shown:


table_tab2(i).EXTEND;

In other words, for the first iteration of the outer loop, there are three varray elements added to the first element of the nested table. Without this statement, the script causes the following error:

DECLARE

ERROR at line 1:

ORA-06533: Subscript beyond count

ORA-06512: at line 20

When run, this script produces output identical to the original example:


table_tab2(1)(1): 1 
table_tab2(1)(2): 2 
table_tab2(1)(3): 3 
table_tab2(2)(1): 3 
table_tab2(2)(2): 2 
table_tab2(2)(3): 1 
PL/SQL procedure successfully completed.

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

فارسی

کردی

العربیه

اردو

Türkçe

Русский

English

Français

کانال فیلم من

تبیان من

فایلهای من

کتابخانه من

پنل پیامکی

وبلاگ من

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

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

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

Benjamin Rosenzweig

آدرس پست الکترونیک گیرنده :

آدرس پست الکترونیک فرستنده :

نام و نام خانوارگی فرستنده :

پیغام برای گیرنده ( حداکثر 250 حرف ) :

کد امنیتی را وارد نمایید

فونت

اندازه قلم

حالت نمایش

Lab 18.3 Exercise Answers

18.3.1 Answers