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

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

Lab 20.2 Exercise Answers

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

20.2.1 Answers

A1:

Answer: In the declaration portion of the script, you define a weak cursor type, zip_cur_type

, and a cursor variable zip_cur

of the zip_cur_type

type. Next, you define string variable to hold dynamic SQL statement, and two variables v_zip

and v_total

to hold data returned by the cursor. Finally, you define a counter variable so that only the first ten rows returned by the cursor are displayed on the screen.

In the executable portion of the script, you generate a dynamic SQL statement, associate it with the cursor variable, zip_cur

, and open the cursor. Next, for each row returned by the cursor, you fetch values of zip code and total number of students into variables v_zip

and v_total

respectively. Then, you check if there are more rows to fetch from the cursor. If there are more rows to process, you increment the value of the counter variable by one. As long as the value of the counter is less than or equal to ten, you display the row returned by the cursor on the screen. If there are no more rows to fetch, you close the cursor.

In the exception-handling section of the script, you check if the cursor is open. If it is, you then close the cursor and display an error message on the screen before terminating the script.

When run, the script should produce output similar to the output shown below:

Zip code: 01247 Total: 1

Zip code: 02124 Total: 1

Zip code: 02155 Total: 1

Zip code: 02189 Total: 1

Zip code: 02563 Total: 1

Zip code: 06483 Total: 1

Zip code: 06605 Total: 1

Zip code: 06798 Total: 1

Zip code: 06820 Total: 3

Zip code: 06830 Total: 3

PL/SQL procedure successfully completed.

A2:

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

-- ch20_2b.sql, version 2.0
SET SERVEROUTPUT ON
DECLARE
TYPE zip_cur_type IS REF CURSOR;
zip_cur zip_cur_type;

v_table_name VARCHAR2(20) := '&sv_table_name';
sql_stmt VARCHAR2(500);
v_zip VARCHAR2(5);
v_total NUMBER;
v_count NUMBER;
BEGIN

DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||

' table');
sql_stmt := 'SELECT zip, COUNT(*) total'||

' FROM '||v_table_name||' '||
'GROUP BY zip';
v_count := 0;
OPEN zip_cur FOR sql_stmt;
LOOP
FETCH zip_cur INTO v_zip, v_total;
EXIT WHEN zip_cur%NOTFOUND;
-- Limit the number of lines printed on the
-- screen to 10
v_count := v_count + 1;
IF v_count <= 10 THEN
DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||
' Total: '||v_total);
END IF;
END LOOP;
CLOSE zip_cur;
EXCEPTION
WHEN OTHERS THEN
IF zip_cur%ISOPEN THEN
CLOSE zip_cur;
END IF;
DBMS_OUTPUT.PUT_LINE ('ERROR: '||
SUBSTR(SQLERRM, 1, 200));
END;

In this version of the script, you have added a variable, v_table_name, to hold the name of a table provided at the run-time. You also added a DBMS_OUTPUT.PUT_LINE table to display a message stating what table the total numbers are coming from. Next, you modified the dynamic SQL statement as follows

sql_stmt := 'SELECT zip, COUNT(*) total'||

' FROM '||v_table_name||' '||

'GROUP BY zip';

The variable v_table_name has been inserted in place of the actual table name (STUDENT). Note that you concatenated a space to the variable v_table_name, so that the SELECT statement does not cause any errors.

When run, this script produces the output shown below. The first run is based on the STUDENT table, and the second run is based on the INSTRUCTOR table.

Enter value for sv_table_name: student

old 5: v_table_name VARCHAR2(20) := '&sv_table_name';

new 5: v_table_name VARCHAR2(20) := 'student';

Totals from student table

Zip code: 01247 Total: 1

Zip code: 02124 Total: 1

Zip code: 02155 Total: 1

Zip code: 02189 Total: 1

Zip code: 02563 Total: 1

Zip code: 06483 Total: 1

Zip code: 06605 Total: 1

Zip code: 06798 Total: 1

Zip code: 06820 Total: 3

Zip code: 06830 Total: 3

PL/SQL procedure successfully completed.

Enter value for sv_table_name: instructor

old 5: v_table_name VARCHAR2(20) := '&sv_table_name';

new 5: v_table_name VARCHAR2(20) := 'instructor';

Totals from instructor table

Zip code: 10005 Total: 1

Zip code: 10015 Total: 3

Zip code: 10025 Total: 4

Zip code: 10035 Total: 1

PL/SQL procedure successfully completed.

So far you have seen that values returned by the dynamic SQL statements are stored in individual variables such as v_last_name or v_first_name. In such cases, you list variables in the order of the corresponding columns returned by the SELECT statement. This approach becomes somewhat cumbersome when a dynamic SQL statement returns more than a few columns. As a result, PL/SQL allows you to store values returned by the dynamic SELECT statements in the variables of the record type.

Consider the modified version of the script used in this lab. In this version, instead of creating separate variables, you create a user-defined record. This record is then used to fetch data from the cursor and display it on the screen. Changes are shown in bold letters.

SET SERVEROUTPUT ON
DECLARE
TYPE zip_cur_type IS REF CURSOR;
zip_cur zip_cur_type;

TYPE zip_rec_type IS RECORD

(zip VARCHAR2(5),

total NUMBER);

zip_rec zip_rec_type;
v_table_name VARCHAR2(20) := '&sv_table_name';
sql_stmt VARCHAR2(500);
v_count NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||
' table');
sql_stmt := 'SELECT zip, COUNT(*) total'||

' FROM '||v_table_name||' '||
'GROUP BY zip';
v_count := 0;
OPEN zip_cur FOR sql_stmt;
LOOP

FETCH zip_cur INTO zip_rec;
EXIT WHEN zip_cur%NOTFOUND;
-- Limit the number of lines printed on the
-- screen to 10
v_count := v_count + 1;
IF v_count <= 10 THEN

DBMS_OUTPUT.PUT_LINE ('Zip code: '||zip_rec.zip||

' Total: '||zip_rec.total);
END IF;
END LOOP;
CLOSE zip_cur;
EXCEPTION
WHEN OTHERS THEN
IF zip_cur%ISOPEN THEN
CLOSE zip_cur;
END IF;
DBMS_OUTPUT.PUT_LINE ('ERROR: '||
SUBSTR(SQLERRM, 1, 200));
END;

Enter value for sv_table_name: student

old 10: v_table_name VARCHAR2(20) := '&sv_table_name';

new 10: v_table_name VARCHAR2(20) := 'student';

Totals from student table

Zip code: 01247 Total: 1

Zip code: 02124 Total: 1

Zip code: 02155 Total: 1

Zip code: 02189 Total: 1

Zip code: 02563 Total: 1

Zip code: 06483 Total: 1

Zip code: 06605 Total: 1

Zip code: 06798 Total: 1

Zip code: 06820 Total: 3

Zip code: 06830 Total: 3

PL/SQL procedure successfully completed.

Enter value for sv_table_name: instructor

old 10: v_table_name VARCHAR2(20) := '&sv_table_name';

new 10: v_table_name VARCHAR2(20) := 'instructor';

Totals from instructor table

Zip code: 10005 Total: 1

Zip code: 10015 Total: 3

Zip code: 10025 Total: 4

Zip code: 10035 Total: 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 20.2 Exercise Answers

20.2.1 Answers