Lab 20.2 Exercises

20.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements

Create the following PL/SQL script:

 -- ch20_2a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    TYPE zip_cur_type IS REF CURSOR;    zip_cur zip_cur_type;    sql_stmt VARCHAR2(500);    v_zip VARCHAR2(5);    v_total NUMBER;    v_count NUMBER; BEGIN    sql_stmt := 'SELECT zip, COUNT(*) total'                '  FROM student '                           '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; 

Consider the use of spaces in the SQL statements generated dynamically. In the script above, the string that holds the dynamic SQL statement consists of three strings concatenated together where each string is written on a separate line.

  sql_stmt := 'SELECT zip, COUNT(*) total'   '  FROM student '   'GROUP BY zip';  

This format of the dynamic SELECT statement is very similar to the format of any static SELECT statement that you have seen throughout this book. However, there is a settled difference. In one instance, extra spaces have been added for formatting reasons. For example, the FROM keyword is prefixed by two spaces so that it is aligned with the SELECT keyword. Yet, in another instance, a space has been added to separate out a reserved phrase. In this case, a space has been added after the STUDENT table to separate out GROUP BY clause. This step is necessary because once the strings are concatenated the resulting SELECT statement looks as follows :

  SELECT zip, COUNT(*) total  FROM student GROUP BY zip  

If no space is added after the STUDENT table, the resulting SELECT statement

  SELECT zip, COUNT(*) total  FROM studentGROUP BY zip  

causes error shown below

  ERROR: ORA-00933: SQL command not properly ended   PL/SQL procedure successfully completed.  

Execute the script, then complete the following exercises:

a)	Explain the script ch20_2a.sql shown above.
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.