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