Oracle PL/SQL by Example- P12

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
61
lượt xem
20
download

Oracle PL/SQL by Example- P12

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle pl/sql by example- p12', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Oracle PL/SQL by Example- P12

  1. LAB 23.1 Lab 23.1 Exercises 522 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. Lab 23.1 Exercises LAB 23.1 523 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. LAB 23.1 Lab 23.1 Exercises 524 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. Lab 23.1 Exercises LAB 23.1 525 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); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. LAB 23.1 Lab 23.1 Exercises 526 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)) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. Lab 23.1 Exercises LAB 23.1 527 LOOP v_counter := v_counter + 1; v_enrollment_tab(v_counter) := 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_tab(v_counter).student_id); DBMS_OUTPUT.PUT_LINE ('first_name: '|| v_enrollment_tab(v_counter).first_name); DBMS_OUTPUT.PUT_LINE ('last_name: '|| v_enrollment_tab(v_counter).last_name); DBMS_OUTPUT.PUT_LINE ('course_no: '|| v_enrollment_tab(v_counter).course_no); DBMS_OUTPUT.PUT_LINE ('section_no: '|| v_enrollment_tab(v_counter).section_no); DBMS_OUTPUT.PUT_LINE ('enroll_date: '|| v_enrollment_tab(v_counter).enroll_date); DBMS_OUTPUT.PUT_LINE ('final_grade: '|| v_enrollment_tab(v_counter).final_grade); DBMS_OUTPUT.PUT_LINE ('------------------'); END LOOP; END; The preceding script defines an associative array of objects that is populated with the help of the cursor FOR loop. After a single row of the associative array has been initialized, it is displayed on the screen. Take a closer look at how each row of the associative array is initialized: v_enrollment_tab(v_counter) := enrollment_obj_type(rec.student_id, rec.first_name, rec.last_name, rec.course_no, rec.section_no, rec.enroll_date, rec.final_grade); A row is referenced by a subscript. In this case it is a variable, v_counter. Because each row represents an object instance, it is initialized by referencing the default constructor method asso- ciated with the corresponding object type. When run, 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: ------------------ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. LAB 23.1 Lab 23.1 Exercises 528 student_id: 102 first_name: Fred last_name: Crocitto course_no: 25 section_no: 5 enroll_date: 30-JAN-07 final_grade: 92 ------------------ student_id: 103 first_name: J. last_name: Landry course_no: 20 section_no: 2 enroll_date: 30-JAN-07 final_grade: ------------------ student_id: 104 first_name: Laetia last_name: Enison course_no: 20 section_no: 2 enroll_date: 30-JAN-07 final_grade: ------------------ PL/SQL procedure successfully completed. B) Modify the script so that the table of objects is populated using the BULK SELECT INTO statement. ANSWER: The script should look similar to the following. Changes are shown in bold. -- ch23_3b.sql, version 2.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; 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) BULK COLLECT INTO v_enrollment_tab 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); FOR i IN 1..v_enrollment_tab.COUNT LOOP Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. Lab 23.1 Exercises LAB 23.1 529 DBMS_OUTPUT.PUT_LINE ('student_id: '|| v_enrollment_tab(i).student_id); DBMS_OUTPUT.PUT_LINE ('first_name: '|| v_enrollment_tab(i).first_name); DBMS_OUTPUT.PUT_LINE ('last_name: '|| v_enrollment_tab(i).last_name); DBMS_OUTPUT.PUT_LINE ('course_no: '|| v_enrollment_tab(i).course_no); DBMS_OUTPUT.PUT_LINE ('section_no: '|| v_enrollment_tab(i).section_no); DBMS_OUTPUT.PUT_LINE ('enroll_date: '|| v_enrollment_tab(i).enroll_date); DBMS_OUTPUT.PUT_LINE ('final_grade: '|| v_enrollment_tab(i).final_grade); DBMS_OUTPUT.PUT_LINE ('------------------'); END LOOP; END; In this version of the script, the cursor FOR loop has been replaced by the BULK SELECT INTO statement. As a result, the cursor FOR loop is replaced by the numeric FOR loop to display data on the screen. These changes eliminate the need for the variable v_counter, which was used to reference individual rows of the associative array. When run, this version of the script produces output that is identical to the previous version. C) Modify the script so that data stored in the table of objects can be retrieved using the SELECT INTO statement as well. ANSWER: As mentioned previously, for you to select data from a table of objects, the underlying table type must be either a nested table or a varray that is created and stored in the database schema. This is accomplished by the following statement: CREATE OR REPLACE TYPE enroll_tab_type AS TABLE OF enrollment_obj_type; / After the nested table type is created, the script is modified as follows. Changes are shown in bold. -- ch23_3c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE v_enrollment_tab enroll_tab_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) BULK COLLECT INTO v_enrollment_tab 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); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. LAB 23.1 Lab 23.1 Exercises 530 FOR rec IN (SELECT * FROM TABLE(CAST(v_enrollment_tab AS enroll_tab_type))) LOOP DBMS_OUTPUT.PUT_LINE ('student_id: '||rec.student_id); DBMS_OUTPUT.PUT_LINE ('first_name: '||rec.first_name); DBMS_OUTPUT.PUT_LINE ('last_name: '||rec.last_name); DBMS_OUTPUT.PUT_LINE ('course_no: '||rec.course_no); DBMS_OUTPUT.PUT_LINE ('section_no: '||rec.section_no); DBMS_OUTPUT.PUT_LINE ('enroll_date: '||rec.enroll_date); DBMS_OUTPUT.PUT_LINE ('final_grade: '||rec.final_grade); DBMS_OUTPUT.PUT_LINE ('------------------'); END LOOP; END; Note that in this version of the script, the numeric FOR loop is replaced by the cursor FOR loop against the nested table of objects. Note that the DBMS_OUTPUT.PUT_LINE statements are also changed so that they reference records returned by the cursor. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. LAB 23.2 531 LAB 23.2 Object Type Methods LAB OBJECTIVE After completing this lab, you will be able to . Use object type methods In Lab 23.1 you learned that object type methods are functions and procedures that specify actions that may be performed on the object type attributes and that they are defined in the object type specification. You also have seen how to use default system-defined constructor methods. The constructor is only one of the method types that PL/SQL supports. Some other method types are member, static, map, and order. The method type typically is determined by the actions that a particular method performs. For example, constructor methods are used to initialize object instances, and map and order methods are used to compare and sort object instances. Often object type methods use a built-in parameter called SELF This parameter represents a . particular instance of the object type. As such, it is available to the methods that are invoked on that object type instance. You will see various examples of the SELF parameter in the following discussions. CONSTRUCTOR METHODS As discussed previously, a constructor method is a default method that is implicitly created by the system whenever a new object type is created. It is a function that has the same name as its object type. Its input parameters have the same names and datatypes as the object type attrib- utes and are listed in the same order as the object type attributes. The constructor method returns a new instance of the object type. In other words, it initializes a new object instance and assigns values to the object attributes. Consider the following code fragments, which illustrate calls to the default constructor method for the zipcode_obj_type created earlier: FOR EXAMPLE zip_obj1 := ZIPCODE_OBJ_TYPE('00914', 'Santurce', 'PR', USER, SYSDATE, USER, SYSDATE); or zip_obj2 := ZIPCODE_OBJ_TYPE(NULL, NULL, NULL, NULL, NULL, NULL NULL); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. LAB 23.2 Object Type Methods 532 The first call to the constructor method returns a new instance, zip_obj1, of zipcode_obj_ type with attributes initialized to non-null values. The second call creates a new instance, zip_obj2, with NULL attribute values. Note that both calls produce non-null instances of the zipcode_obj_type. The difference is in the values assigned to the individual attributes. In the preceding examples, calls to the default constructor method use positional notation. Recall that positional notation associates values with corresponding parameters by their position in the header of the function, procedure, or, in this case, constructor. Next, consider the call to the default constructor method that uses named notation. Note that in this case, the order of parameters does not correspond to the order of the attributes in zipcode_obj_type. Instead, they are referenced by their names: FOR EXAMPLE zip_obj3 := ZIPCODE_OBJ_TYPE(created_by => USER, created_date => SYSDATE, modified_by => USER, modified_date => SYSDATE, zip =>'00914', city => 'Santurce', state => 'PR'); PL/SQL lets you create your own (user-defined) constructors. User-defined constructors offer flexibility that default constructors lack. For example, you may want to define a constructor on the zipcode_obj_type that initializes only some of the attributes of the newly created object instance. In this case, the system initializes to NULL any attributes for which you do not specify values. In addition, you can control the number and types of parameters that your constructor may require. Consider the following example of the user-defined constructors for the zipcode_obj_type. FOR EXAMPLE Note that before recreating zipcode_obj_type you must drop the nested table type v_zip_tab_type, created in Lab 23.1, to prevent the following error message: CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT * ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents The nested table type can be dropped as follows: DROP TYPE v_zip_tab_type; The object type can be recreated as shown here: CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT (zip VARCHAR2(5), Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. Object Type Methods LAB 23.2 533 city VARCHAR2(25), state VARCHAR2(2), created_by VARCHAR2(30), created_date DATE, modified_by VARCHAR2(30), modified_date DATE, CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2) RETURN SELF AS RESULT, CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2, city VARCHAR2, state VARCHAR2) RETURN SELF AS RESULT); / CREATE OR REPLACE TYPE BODY zipcode_obj_type AS CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type, zip VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.zip := zip; SELECT city, state INTO SELF.city, SELF.state FROM zipcode WHERE zip = SELF.zip; RETURN; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2, city VARCHAR2, state VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.zip := zip; SELF.city := city; SELF.state := state; RETURN; END; END; / Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. LAB 23.2 Object Type Methods 534 This script overloads two constructor methods for zip_code_obj_type. Overloading allows two methods or subprograms to use the same name as long as their parameters differ in either datatypes or their number. In the preceding example, the first constructor method expects two parameters, and the second constructor method expects four parameters. Both constructors use the default parameter SELF as an IN OUT parameter and as a return datatype in the RETURN clause. As stated previously, SELF references a particular object type instance. Note the use of the NOCOPY compiler hint. This hint typically is used with OUT and IN OUT parameters. By default, OUT and IN OUT parameters are passed by value. This means that the values of the parameters are copied before the subprogram or method is executed. Then, during execution, temporary variables are used to hold values of the OUT parameters. For the parameters that represent complex datatypes such as collections, records, and object type instances, the copying step can add significant processing overhead. By adding a NOCOPY hint, you instruct the PL/SQL compiler to pass OUT and IN OUT parameters by reference and elim- inate the copying step. Next, both constructor methods populate the city, state, and zip attributes. Note how these attributes are referenced using the SELF parameter. MEMBER METHODS Member methods provide access to the object instance data. As such, a member method should be defined for each action that object type must perform. For example, you may need to return city, state, and zip code values associated with an object instance to the calling application, as shown in the following example: FOR EXAMPLE CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT (zip VARCHAR2(5), city VARCHAR2(25), state VARCHAR2(2), created_by VARCHAR2(30), created_date DATE, modified_by VARCHAR2(30), modified_date DATE, CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2) RETURN SELF AS RESULT, CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2, city VARCHAR2, state VARCHAR2) RETURN SELF AS RESULT, MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2, out_city OUT VARCHAR2, out_state OUT VARCHAR2) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. Object Type Methods LAB 23.2 535 ); / CREATE OR REPLACE TYPE BODY zipcode_obj_type AS CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type, zip VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.zip := zip; SELECT city, state INTO SELF.city, SELF.state FROM zipcode WHERE zip = SELF.zip; RETURN; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2, city VARCHAR2, state VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.zip := zip; SELF.city := city; SELF.state := state; RETURN; END; MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2, out_city OUT VARCHAR2, out_state OUT VARCHAR2) IS BEGIN out_zip := SELF.zip; out_city := SELF.city; out_state := SELF.state; END; END; / Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. LAB 23.2 Object Type Methods 536 In this version of the script, you add a member procedure that returns values of zip code, city, and state associated with a particular instance of the zip_code_obj_type object type. Note that the reference to the SELF parameter in this procedure is optional, and that the preceding assignment statements can be modified as follows: out_zip := zip; out_city := city; out_state := state; These statements initialize OUT parameters associated with individual attributes of a particular object instance, just like the statements that include the reference to the SELF parameter. STATIC METHODS Static methods are created for actions that do not need to access data associated with a particu- lar object instance. As such, these methods are created for the object type itself and describe actions that are global to that object type. Because static methods do not have access to the data associated with a particular object type instance, they may not reference the default parameter SELF Consider the following example of the static method that displays zip code information: . FOR EXAMPLE CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT (zip VARCHAR2(5), city VARCHAR2(25), state VARCHAR2(2), created_by VARCHAR2(30), created_date DATE, modified_by VARCHAR2(30), modified_date DATE, CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2) RETURN SELF AS RESULT, CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2, city VARCHAR2, state VARCHAR2) RETURN SELF AS RESULT, MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2, out_city OUT VARCHAR2, out_state OUT VARCHAR2), STATIC PROCEDURE display_zipcode_info (in_zip_obj IN ZIPCODE_OBJ_TYPE) ); / CREATE OR REPLACE TYPE BODY zipcode_obj_type AS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. Object Type Methods LAB 23.2 537 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type, zip VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.zip := zip; SELECT city, state INTO SELF.city, SELF.state FROM zipcode WHERE zip = SELF.zip; RETURN; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2, city VARCHAR2, state VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.zip := zip; SELF.city := city; SELF.state := state; RETURN; END; MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2, out_city OUT VARCHAR2, out_state OUT VARCHAR2) IS BEGIN out_zip := SELF.zip; out_city := SELF.city; out_state := SELF.state; END; STATIC PROCEDURE display_zipcode_info (in_zip_obj IN ZIPCODE_OBJ_TYPE) IS BEGIN DBMS_OUTPUT.PUT_LINE ('Zip: ' ||in_zip_obj.zip); DBMS_OUTPUT.PUT_LINE ('City: ' ||in_zip_obj.city); DBMS_OUTPUT.PUT_LINE ('State: '||in_zip_obj.state); END; END; / Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. LAB 23.2 Object Type Methods 538 In this version of the script, the static method displays zip code information on the screen. It is important to note that even though this method references data associated with some object instance, this object instance is created elsewhere (such as in another PL/SQL script, function, or procedure) and then passed into this method. COMPARING OBJECTS In PL/SQL, element datatypes such as VARCH AR2, NUMBER, and DATE have a predefined order that enables them to be compared to each other or sorted. For example, the comparison operator > determines which variable contains a greater value, and the IF-THEN-ELSE state- ment evaluates to TRUE, FALSE, or NULL accordingly: IF v_num1 > v_num2 THEN -- Do something ELSE -- Do something else END IF; However, an object type may contain multiple attributes of different datatypes and therefore does not have a predefined order. Then, to be able to compare and sort object instances of the same object type, you must specify how these object instances should be compared and ordered. You can do this using two types of optional member methods—map and order. MAP METHODS Map methods compare and order object instances, essentially by mapping an object instance to an element (scalar) datatype such as DATE, NUMBER, or VARCHAR2. This mapping is used to position an object instance on the axis (DATE, NUMBER, or VARCHAR2) used for the comparison. A map method is a member function that does not accept any parameters and returns an element datatype, as demonstrated in the following example: FOR EXAMPLE CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT (zip VARCHAR2(5), city VARCHAR2(25), state VARCHAR2(2), created_by VARCHAR2(30), created_date DATE, modified_by VARCHAR2(30), modified_date DATE, CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2) RETURN SELF AS RESULT, CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. Object Type Methods LAB 23.2 539 city VARCHAR2, state VARCHAR2) RETURN SELF AS RESULT, MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2, out_city OUT VARCHAR2, out_state OUT VARCHAR2), STATIC PROCEDURE display_zipcode_info (in_zip_obj IN ZIPCODE_OBJ_TYPE), MAP MEMBER FUNCTION zipcode RETURN VARCHAR2 ); / CREATE OR REPLACE TYPE BODY zipcode_obj_type AS CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type, zip VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.zip := zip; SELECT city, state INTO SELF.city, SELF.state FROM zipcode WHERE zip = SELF.zip; RETURN; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY ZIPCODE_OBJ_TYPE, zip VARCHAR2, city VARCHAR2, state VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.zip := zip; SELF.city := city; SELF.state := state; RETURN; END; MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2, out_city OUT VARCHAR2, out_state OUT VARCHAR2) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. LAB 23.2 Object Type Methods 540 IS BEGIN out_zip := SELF.zip; out_city := SELF.city; out_state := SELF.state; END; STATIC PROCEDURE display_zipcode_info (in_zip_obj IN ZIPCODE_OBJ_TYPE) IS BEGIN DBMS_OUTPUT.PUT_LINE ('Zip: ' ||in_zip_obj.zip); DBMS_OUTPUT.PUT_LINE ('City: ' ||in_zip_obj.city); DBMS_OUTPUT.PUT_LINE ('State: '||in_zip_obj.state); END; MAP MEMBER FUNCTION zipcode RETURN VARCHAR2 IS BEGIN RETURN (zip); END; END; / In this version of the script, the map member function returns the value of the zip attribute that has been defined as VARCHAR2. After the map method is added to the object type, the object type instances may be compared or ordered similar to the element datatypes. For example, if V_ZIP_OBJ1 and V_ZIP_OBJ2 are two instances of the ZIPCODE_OBJ_TYPE, they can be compared like this: v_zip_obj1 > v_zip_obj2 or v_zip_obj1.zipcode() > v_zip_obj2.zipcode() Note that the second statement uses dot notation to reference the map function. Consider the following example, which demonstrates how the various object type methods created so far may be used: FOR EXAMPLE DECLARE v_zip_obj1 zipcode_obj_type; v_zip_obj2 zipcode_obj_type; BEGIN -- Initialize object instances with user-defined constructor -- methods Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. Object Type Methods LAB 23.2 541 v_zip_obj1 := zipcode_obj_type (zip => '12345', city => 'Some City', state => 'AB'); v_zip_obj2 := zipcode_obj_type (zip => '48104'); -- Compare object instances via map methods IF v_zip_obj1 > v_zip_obj2 THEN DBMS_OUTPUT.PUT_LINE ('v_zip_obj1 is greater than v_zip_obj2'); ELSE DBMS_OUTPUT.PUT_LINE ('v_zip_obj1 is not greater than v_zip_obj2'); END IF; END; Note that when user-defined constructors are invoked, the call statements have no reference to the SELF default parameter. When run, the script produces the following output: v_zip_obj1 is not greater than v_zip_obj2 PL/SQL procedure successfully completed. ORDER METHODS Order methods use a different technique when comparing and ordering object instances. They do not map object instances to an external axis such as NUMBER or DATE. Instead, an order method compares the current object instance with another object instance of the same object type based on some criterion specified in the method. An order method is a member function with a single IN parameter of the same object type that returns INTEGER as its return type. Furthermore, the method must return a negative number, 0, or a positive number. This number indicates that the object instance referenced by the SELF parameter is less than, equal to, or greater than the object instance referenced by the IN parameter. DID YOU KNOW? The map and order methods have the following restrictions: . An object type may contain either an order or map method. . An object type derived from another object type may not define an order method. Consider the following example of the order method for the zipcode_obj_type: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản