Tài liệu Oracle PL/SQL by Example- P12 pptx

▼

Next, take a closer look at the second SELECT INTO statement. This statement uses CAST and

TABLE functions, which essentially enable you to query a nested table of objects as if it were a

regular table.

When run, this example produces the following output:

Zip: 00914

City: Santurce

State: PR

PL/SQL procedure successfully completed.

LAB 23.1 EXERCISES

This section provides exercises and suggested answers, 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 and what the effects are of any different answers you may come up with.

23.1.1 Use Object Types

In this exercise, you continue exploring object types.

Complete the following tasks:

A) Create object type ENROLLMENT_OBJ_TYPE, which has the following attributes:

ATTRIBUTE NAME DATA TYPE PRECISION

-------------- --------- ---------

student_id NUMBER 8

first_name VARCHAR2 25

last_name VARCHAR2 25

course_no NUMBER 8

section_no NUMBER 3

enroll_date DATE

final_grade NUMBER 3

ANSWER: The creation script should look similar to the following:

-- ch23_1a.sql, version 1.0

CREATE OR REPLACE TYPE ENROLLMENT_OBJ_TYPE AS OBJECT

(student_id NUMBER(8),

first_name VARCHAR2(25),

last_name VARCHAR2(25),

course_no NUMBER(8),

section_no NUMBER(3),

enroll_date DATE,

final_grade NUMBER(3));

B) The following script uses the newly created object type. Execute it and explain the output

produced.

-- ch23_2a.sql, version 1.0

SET SERVEROUTPUT ON

DECLARE

v_enrollment_obj enrollment_obj_type;

LAB 23.1

522

Lab 23.1 Exercises

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

BEGIN

v_enrollment_obj.student_id := 102;

v_enrollment_obj.first_name := 'Fred';

v_enrollment_obj.last_name := 'Crocitto';

v_enrollment_obj.course_no := 25;

END;

ANSWER: The output of the script should look similar to the following:

DECLARE

*

ERROR at line 1:

ORA-06530: Reference to uninitialized composite

ORA-06512: at line 6

This version of the script causes an ORA-06530 error because it references individual attributes of

the uninitialized object type instance. Before the object attribute can be referenced, the object

must be initialized with the help of the constructor method.

C) Modify the script created in the preceding exercise (ch23_2a.sql) so that it does not produce an

ORA-06530 error.

ANSWER: The script should look similar to the following. Changes are shown in bold.

-- ch23_2b.sql, version 2.0

SET SERVEROUTPUT ON

DECLARE

v_enrollment_obj enrollment_obj_type;

BEGIN

v_enrollment_obj :=

enrollment_obj_type(102, 'Fred', 'Crocitto', 25, null, null,

null);

END;

D) Modify this script (ch23_2b.sql) so that all object attributes are populated with corresponding

values selected from the appropriate tables.

ANSWER: The script should look similar to one of the following scripts. Changes are shown

in bold.

The first version of the script employs the SELECT INTO statement along with the constructor to

initialize other attributes as well. Note that the SELECT INTO statement specifies WHERE criteria for

the SECTION_NO in addition to the criteria for the STUDENT_ID and COURSE_NO. This ensures

that the SELECT INTO statement does not cause an ORA-01422: exact fetch returns

more than requested number of rows error.

-- ch23_2c.sql, version 3.0

SET SERVEROUTPUT ON

DECLARE

v_enrollment_obj enrollment_obj_type;

BEGIN

SELECT

enrollment_obj_type(st.student_id, st.first_name, st.last_name,

c.course_no, se.section_no, e.enroll_date,

e.final_grade)

INTO v_enrollment_obj

Lab 23.1 Exercises LAB 23.1

523

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

FROM student st, course c, section se, enrollment e

WHERE st.student_id = e.student_id

AND c.course_no = se.course_no

AND se.section_id = e.section_id

AND st.student_id = 102

AND c.course_no = 25

AND se.section_no = 2;

END;

The SELECT statement in the preceding script can be modified according to the ANSI 1999 SQL

standard:

SELECT enrollment_obj_type(st.student_id, st.first_name,

st.last_name, c.course_no, se.section_no,

e.enroll_date, e.final_grade)

INTO v_enrollment_obj

FROM enrollment e

JOIN student st

ON e.student_id = st.student_id

JOIN section se

ON e.section_id = se.section_id

JOIN course c

ON se.course_no = c.course_no

WHERE st.student_id = 102

AND c.course_no = 25

AND se.section_no = 2;

The preceding SELECT statement uses the ON syntax to specify the join condition between four

tables. This type of join becomes especially useful when the columns participating in the join do

not have the same name.

BY THE WAY

You will find detailed explanations and examples of the statements using the new ANSI 1999 SQL

standard in Appendix C and in the Oracle help. Throughout this book we have tried to provide you

with examples illustrating both standards; however, our main focus has remained on PL/SQL features

rather than SQL.

The second version of the script uses a cursor FOR loop. This approach eliminates the need for

additional criteria against the SECTION_NO.

-- ch23_2d.sql, version 4.0

SET SERVEROUTPUT ON

DECLARE

v_enrollment_obj enrollment_obj_type;

BEGIN

FOR REC IN (SELECT st.student_id, st.first_name, st.last_name,

c.course_no, se.section_no, e.enroll_date,

e.final_grade

FROM student st, course c, section se, enrollment e

WHERE st.student_id = e.student_id

AND c.course_no = se.course_no

LAB 23.1

524

Lab 23.1 Exercises

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

AND se.section_id = e.section_id

AND st.student_id = 102

AND c.course_no = 25)

LOOP

v_enrollment_obj :=

enrollment_obj_type(rec.student_id, rec.first_name,

rec.last_name, rec.course_no,

rec.section_no, rec.enroll_date,

rec.final_grade);

END LOOP;

END;

E) Modify one of the scripts created in the previous exercises (use either ch23_2c.sql or ch23_2d.sql)

so that attribute values are displayed on the screen.

ANSWER: The script should look similar to the following. All changes are shown in bold.

-- ch23_2e.sql, version 5.0

SET SERVEROUTPUT ON

DECLARE

v_enrollment_obj enrollment_obj_type;

BEGIN

FOR REC IN (SELECT st.student_id, st.first_name, st.last_name,

c.course_no, se.section_no, e.enroll_date,

e.final_grade

FROM student st, course c, section se, enrollment e

WHERE st.student_id = e.student_id

AND c.course_no = se.course_no

AND se.section_id = e.section_id

AND st.student_id = 102

AND c.course_no = 25)

LOOP

v_enrollment_obj :=

enrollment_obj_type(rec.student_id, rec.first_name,

rec.last_name, rec.course_no,

rec.section_no, rec.enroll_date,

rec.final_grade);

DBMS_OUTPUT.PUT_LINE ('student_id: '||

v_enrollment_obj.student_id);

DBMS_OUTPUT.PUT_LINE ('first_name: '||

v_enrollment_obj.first_name);

DBMS_OUTPUT.PUT_LINE ('last_name: '||

v_enrollment_obj.last_name);

DBMS_OUTPUT.PUT_LINE ('course_no: '||

v_enrollment_obj.course_no);

DBMS_OUTPUT.PUT_LINE ('section_no: '||

v_enrollment_obj.section_no);

DBMS_OUTPUT.PUT_LINE ('enroll_date: '||

v_enrollment_obj.enroll_date);

Lab 23.1 Exercises LAB 23.1

525

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

DBMS_OUTPUT.PUT_LINE ('final_grade: '||

v_enrollment_obj.final_grade);

END LOOP;

END;

This version of the script produces the following output:

student_id: 102

first_name: Fred

last_name: Crocitto

course_no: 25

section_no: 2

enroll_date: 30-JAN-07

final_grade:

student_id: 102

first_name: Fred

last_name: Crocitto

course_no: 25

section_no: 5

enroll_date: 30-JAN-07

final_grade: 92

PL/SQL procedure successfully completed.

23.1.2 Use Object Types with Collections

In this exercise, you continue exploring how object types may be used with collections.

Complete the following tasks:

A) Modify script ch23_2e.sql, created in the preceding exercise. In the new version of the script,

populate an associative array of objects. Use multiple student IDs for this exercise—102, 103,

and 104.

ANSWER: The script should look similar to the following:

-- ch23_3a.sql, version 1.0

SET SERVEROUTPUT ON

DECLARE

TYPE enroll_tab_type IS TABLE OF enrollment_obj_type

INDEX BY BINARY_INTEGER;

v_enrollment_tab enroll_tab_type;

v_counter integer := 0;

BEGIN

FOR REC IN (SELECT st.student_id, st.first_name, st.last_name,

c.course_no, se.section_no, e.enroll_date,

e.final_grade

FROM student st, course c, section se, enrollment e

WHERE st.student_id = e.student_id

AND c.course_no = se.course_no

AND se.section_id = e.section_id

AND st.student_id in (102, 103, 104))

LAB 23.1

526

Lab 23.1 Exercises

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.