Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 20.1 Exercise Answers

This section gives you some suggested answers to the questions in Lab 20.1, 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.1.1 Answers

In the script above, you declare four new variables, v_street, v_city, v_state, and v_zip. Next, you modify the dynamic SQL statement so that it can return the student's address. As a result, you modify the INTO clause by adding the new variables to it. Next, you add DBMS_OUTPUT.PUT_LINE statements to display the student's address on the screen.

When run, the script produces the output shown below:

Enter value for sv_student_id: 105

old 3: v_student_id NUMBER := &sv_student_id;

new 3: v_student_id NUMBER := 105;

First Name: Angel

Last Name: Moskowitz

Street: 320 John St.

City: Ft. Lee

State: NJ

Zip Code: 07024

PL/SQL procedure successfully completed.

It is important to remember that the order of variables listed in the INTO clause must follow the order of columns listed in the SELECT statement. In other words, if the INTO clause listed variables so that v_zip and v_state were misplaced while the SELECT statement remains unchanged, the scripts would generate an error as demonstrated below.

SET SERVEROUTPUT ON
DECLARE
sql_stmt VARCHAR2(200);
v_student_id NUMBER := &sv_student_id;
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
v_street VARCHAR2(50);
v_city VARCHAR2(25);
v_state VARCHAR2(2);
v_zip VARCHAR2(5);
BEGIN
sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||
' ,b.city, b.state, b.zip' ||
' FROM student a, zipcode b' ||
' WHERE a.zip = b.zip' ||
' AND student_id = :1';
EXECUTE IMMEDIATE sql_stmt

-- variables v_state and v_zip are misplaced

INTO v_first_name, v_last_name, v_street, v_city, v_zip, v_state
USING v_student_id;
DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
DBMS_OUTPUT.PUT_LINE ('Street: '||v_street);
DBMS_OUTPUT.PUT_LINE ('City: '||v_city);
DBMS_OUTPUT.PUT_LINE ('State: '||v_state);
DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip);
END;

Enter value for sv_student_id: 105

old 3: v_student_id NUMBER := &sv_student_id;

new 3: v_student_id NUMBER := 105;

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at line 16

This error is generated because variable v_state can hold up to two characters. However, you are trying to store in it a value of zip code that contains 5 characters.

The declaration portion of the script contains a new variable, v_table_name that holds the name of a table provided at run-time by a user. In addition, the variable v_student_id has been replaced by the variable v_id since it is not known in advance what table, STUDENT or INSTRCTOR, will be accessed at run-time.

The executable portion of the script contains a modified dynamic SQL statement. Notice that the statement does not contain any information specific to the STUDENT or INSTRCUTOR tables. In other words, the dynamic SQL statement used by the previous version (ch20_1b.sql)

sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||

' ,b.city, b.state, b.zip' ||

' FROM student a, zipcode b' ||

' WHERE a.zip = b.zip' ||

' AND student_id = :1';

has been replaced by

sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||

' ,b.city, b.state, b.zip' ||

' FROM '||v_table_name||' a, zipcode b' ||

' WHERE a.zip = b.zip' ||

' AND '||v_table_name||'_id = :1';

The table name (student) has been replaced by the variable v_table_name in the FROM and the WHERE clauses.

Note that for the last two versions of the script you have used generic table aliases, 'a' and 'b', instead of 's' and 'z' or 'i' and 'z', that are more descriptive. This technique allows you to create generic SQL statements that are not based on a specific table since you do not always know it in advance.

This version of the script produces output shown below. First run is against the STUDENT table, and second run is against the INSTRUCTOR table:

Enter value for sv_table_name: student

old 3: v_table_name VARCHAR2(20) := '&sv_table_name';

new 3: v_table_name VARCHAR2(20) := 'student';

Enter value for sv_id: 105

old 4: v_id NUMBER := &sv_id;

new 4: v_id NUMBER := 105;

First Name: Angel

Last Name: Moskowitz

Street: 320 John St.

City: Ft. Lee

State: NJ

Zip Code: 07024

PL/SQL procedure successfully completed.

Enter value for sv_table_name: instructor

old 3: v_table_name VARCHAR2(20) := '&sv_table_name';

new 3: v_table_name VARCHAR2(20) := 'instructor';

Enter value for sv_id: 105

old 4: v_id NUMBER := &sv_id;

new 4: v_id NUMBER := 105;

First Name: Anita

Last Name: Morris

Street: 34 Maiden Lane

City: New York

State: NY

Zip Code: 10015

PL/SQL procedure successfully completed.