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. |
| 
No comments:
Post a Comment