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: