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

a)

Explain the script ch20_2a.sql shown above.

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.  
b)

Modify the script created in the previous exercise (ch20_2a.sql) so that the SELECT statement can be run against either STUDENT or INSTRUCTOR table. In other words, a user can specify table name used in the SELECT statement at the run time.

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.  


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