Extending the Oracle Server with Object Types Mairie de Paris - 1

1. Extending the Oracle Server with Object Types(Mairie de Paris - #1/2)

2. Objectifs (Oracle9i) At the end of this training, you should be able to Create user-defined datatypes Call an object type constructor to create an instance of an object Use object types in table declarations Create REF pointers to row objects Use object types in SQL statements, including new SQL operators Discuss object type method specifications Multilevel Collections and Type evolution Implement Inheritance Optimize and tune Object storage within Oracle9i Develop Java-based classes dedicated to object Database application Support STRUTS framework and Object Database

3. Review: Identifying Type Hierarchy

4. Sample Scalar Type Usage : NCHAR

5. Some Precision CHAR(n) chaîne de car. De longueur fixe (n<2000) VARCHAR2(2) chaîne de long. Variable (<4000) NCHAR (n) chaîne de longueur fixe (complétée à droite) NVARCHAR2(n) chaîne de longueur variable. N<4000. Les chaînes sont codées suivant le jeu de caractères national actif. BLOB Données binaires non structurées (4 Go Max) CLOB Chaîne de caractères de longueur variable (4Go maximum). Ne peut contenir que des caractères codés sur 1 octet. NCLOB Chaîne de longueur variable (4Go max). BFILE Données binaires stockées dans des fichiers externes à la base de données (4Go max).

6. Abstract Data Type

7. Abstract Data Type Un type objet est un type de données composite défini par l’utilisateur (Développeur) Il encapsule une structure de données et des méthodes exposées pour manipuler l’objet

8. Structure d’un type objet

9. Applying User-Defined Datatypes Some applications of user-defined datatypes Create another user-defined datatypes Create as data type of relational tables Create a particular object tables Column can store a reference instances of user-defined datatypes

10. Attributes User-defined types must have one or more attributes Attributes are named and have a data type Four categories of user-defined types are simple, composite, cyclic, and collection If the attribute data type is a user-defined type in another schema, you must use the schema name as a qualifier of the data type.

11. Creating User-Defined Types: Syntax

12. Declaring a Simple Type

13. Trois cas de figure ADT, Agrégation et objet circulaire

14. Defining another Relational Type with User-Defined Datatypes

15. Creating a Relational Table with User-Defined Types SQL> CREATE TYPE address_type AS OBJECT( 2 address1 VARCHAR2(30), 3 address2 VARCHAR2(30), 4 city VARCHAR2(15), 5 state VARCHAR2(2), 6 zip VARCHAR2(16)); 7 / Type created. SQL> CREATE TABLE customer( 2 id INTEGER, 3 cust_address address_type); Table created.

16. Defining an Object Type

17. Creating a Composite User-Defined Type

18. Creating Cyclic Object Type SQL> CREATE TYPE employee_typ; /*incomplete*/ 2 / SQL> CREATE TYPE department_typ AS OBJECT( 2 deptno NUMBER(2), 3 manager REF employee_typ); 4 / SQL> CREATE OR REPLACE TYPE 2 employee_typ AS OBJECT( 3 empno NUMBER(4), 4 emp_name VARCHAR(32), 5 dept REF department_typ); 6 /

19. SQL Code

20. Resolving Names for Attributes

21. Resolving Names for Attributes : CREATE REM DROP TABLE person_tab CASCADE CONSTRAINTS; REM DROP TYPE contact_typ FORCE; CREATE OR REPLACE TYPE contact_typ AS OBJECT ( wired VARCHAR2(20), cellular VARCHAR2(20)); / CREATE TABLE person_tab ( name VARCHAR2(16), phone contact_typ) / ALTER TABLE person_tab ADD ( CONSTRAINT PEOPLE_TAB_PRIMARY_KEY PRIMARY KEY (name))

22. INSERT INSERT INTO person_tab VALUES ('Jean', contact_typ('33141129718', '33685910407') ) / INSERT INTO person_tab VALUES ('Pierre', NEW contact_typ('33144267278', '33685910407') ) / COMMIT;

23. Using a PL/SQL Bloc (sample 1) SET SERVEROUTPUT ON DECLARE /* example 1 */ P contact_typ := contact_typ('33141129718', '33685910407'); BEGIN DBMS_OUTPUT.PUT_LINE(p.cellular); END; /

24. Using a PL/SQL Bloc (alternate sample 2)

25. Resolving Names for Attributes : SELECT

26. Resolving Names for Attributes : Update UPDATE person_tab SET phone = contact_typ( '44141129718','44685910407') WHERE name = 'Daisy'; COMMIT;

27. Resolving Names for Attributes : Update UPDATE person_tab p SET phone = contact_typ('000111222', p.phone.cellular) WHERE name = 'Pierre'; /* Alternate update */ UPDATE person_tab p SET p.phone.cellular = '000111222' WHERE name = 'Pierre';

28. Object View

29. Object Views Just as a view is a virtual table, an object view is a virtual object table. An object view is a way to access relational data using object-relational features. It lets you develop object-oriented applications without changing the underlying relational schema. Each row in the view is an object: you can call its methods, access its attributes using the dot notation, and create a REF that points to it.

30. Sample Object View (#1/2)

31. Sample Object View (#2/2)

32. More: Sample of Object Table

33. Object Type Privileges

34. Object Type Privileges

35. Sample Schema Usage

36. Discussion: Object Type Privilege SQL> CONNECT system/manager SQL> GRANT CONNECT,RESOURCE TO elvis IDENTIFIED BY elvis; SQL> CONNECT scott/tiger SQL> GRANT INSERT ON person_tab TO elvis; SQL> CONNECT elvis/elvis SQL> INSERT INTO scott.person_tab VALUES ('Jean 23', scott.contact_typ('33141129718', '33685910407') ); ERROR at Line 2 ORA-00904: invalid column name

37. Discussion : Object Type Privilege SQL> CONNECT scott/tiger SQL> GRANT INSERT ON person_tab TO elvis; SQL> GRANT SELECT ON person_tab TO elvis; SQL> GRANT EXECUTE ON contact_typ TO elvis; SQL> CONNECT elvis/elvis SQL> INSERT INTO scott.person_tab VALUES ('Jean 23', scott.contact_typ('33141129718', '33685910407') ) / SQL> SELECT p.name,p.phone.CELLULAR,p.phone.wired FROM scott.person_tab p;

38. Comparing Object Tables and Relational Tables Object table Stores row objects Create by CREATE TABLE OF command Object identifier is generated for each row object by the system SQL> CREATE TABLE person OF person_typ; Column objects do not have the system-generated identifier, and therefore are not referred SQL> CREATE TABLE person_rel(p person_typ);

39. Example SQL> CREATE TYPE T AS OBJECT (c NUMBER(1)); Type created SQL> CREATE TABLE tab_1 (col T); Table created. SQL> DESC tab_1 Name Null? Type -------------------------- -------- ------- COL T

40. Defining Relationship Between Objects

41. Defining Relationship Between Objects A relationship is an object that defines how one object, or one set of objects, relates to another or set of objects Oracle8i/9i defines one kind of relationship, a one-to-one unidirectional association, REF. You can set the a condition on the REF that limits the REF values to a single table. This condition is the scope and will be part of SQL3 In practical terms, references allow you to create a pointer to a persistent row object, and use the pointer to query the object.

42. Using a REF CREATE TYPE tp1 AS OBJECT (a INTEGER, b VARCHAR2(10)); / CREATE TABLE tp1s OF tp1; INSERT INTO tp1s VALUES(tp1(99,'abcd')); SELECT REF(x) FROM tp1s x WHERE x.a = 99; REF(X) ------------------------------------ 000028020920476C419FB5465B8BFDD8D2…

43. Comment about the REF type You use the REF type modifier to construct a reference to an object The last statement in the example selects a reference to the row objects in the TP1S table and the result is displayed below Additional Operators are REF, to take the REF of a row in an object table REF, INTO to capture the REF to a new row in an object table while inserting DEREF, to return an object value identified by a reference

44. Sample Scoped REFs (#1/4) A REF can be scoped to an object table of the declared type (address_objtyp in the example) or of any subtype of the declared type. If scoped to an object table of a subtype, the REF column is effectively constrained to hold references only to instances of the subtype (and its subtypes, if any) in the table. A REF can be scoped to an object table of the declared type (address_objtyp in the example) or of any subtype of the declared type. If scoped to an object table of a subtype, the REF column is effectively constrained to hold references only to instances of the subtype (and its subtypes, if any) in the table.

45. Sample Scoped REFs (#2/4)

46. Sample Scoped REFs (#3/4)

47. Sample Scoped REFs (#4/4)

48. Dangling REFs If the target object of a REF is removed, the REF is said to be DANGLING SQL> SELECT COUNT(*) FROM tp1s x WHERE REF(x) IS DANGLING; COUNT(*) --------- 0

49. Dangling REFs It is possible for the object identified by a REF to become unavailable—through either deletion of the object or a change in privileges. Such a REF is called dangling. Oracle SQL provides a predicate (called IS DANGLING) to allow testing REFs for this condition.

50. Dangling REFs (1/2)

51. Dangling REFs (2/2)

52. Dangling REFs (extra sample)

53. Dereferencing REFs Accessing the object referred to by a REF is called dereferencing the REF. Oracle provides the DEREF operator to do this. Dereferencing a dangling REF returns a null object.

54. DEREF (*** cf 9i) CREATE OR REPLACE TYPE person_typ AS OBJECT ( name VARCHAR2(30), phone VARCHAR2(20)); / CREATE TABLE people_tab OF person_typ; INSERT INTO people_tab VALUES(NEW person_typ('Jean','1'). DECLARE t person_typ; r REF person_typ; BEGIN SELECT REF(p) INTO r FROM people_tab p WHERE p.name = 'Jean' AND ROWNUM = 1; SELECT DEREF (r) INTO t FROM DUAL; DBMS_OUTPUT.PUT_LINE(t.name ||' '||t.phone); END; /

55. New VALUE DML Operator SQL> SELECT VALUE(p) FROM dept_tab p; VALUE(P)(SERVICE, ADDRESS) ------------------------------------ DEPT_TYP('ACCOUNTING', 'NEW YORK') DEPT_TYP('OPERATION', 'KISTA') DEPT_TYP('SALES', 'CHICAGO') DEPT_TYP('RESEARCH', 'DALLAS')

56. Sample of Object Table

57. Methods

58. Methods Methods are functions or procedures that you can declare in an object type definition to implement behavior that you want objects of that type to perform. An application calls the methods to invoke the behavior. Methods can be written in PL/SQL or virtually any other programming language. Methods written in PL/SQL or Java are stored in the database. Methods written in other languages, such as C, are stored externally.

59. Methods Two general kinds of methods can be declared in a type definition: Member Static Constructor There is also a third kind of method, called a constructor method, that the system defines for every object type. You call a type’s constructor method to construct or create an object instance of the type.

60. Sample basic (1/3) CREATE OR REPLACE TYPE name_type AS OBJECT( f_name VARCHAR2(25), l_name VARCHAR2(25), initials VARCHAR2(7), MEMBER FUNCTION full_name RETURN VARCHAR2); / CREATE OR REPLACE TYPE BODY name_type AS MEMBER FUNCTION full_name RETURN VARCHAR2 IS BEGIN RETURN (f_name || ' ' || l_name); END full_name; END; /

61. Reflection (DESCRIBE) SQL> DESC name_type Name Null? Type -------------------------------- -------- ----- F_NAME VARCHAR2(25) L_NAME VARCHAR2(25) INITIALS VARCHAR2(7) METHOD ------ MEMBER FUNCTION FULL_NAME RETURNS VARCHAR2 SQL> DROP TYPE name_type FORCE; SQL> REM no type evolution using Oracle8i!

62. Reflection (USER_SOURCE) SQL> SELECT TEXT FROM USER_SOURCE WHERE NAME='NAME_TYPE'; TEXT --------------------------------------------------------- TYPE name_type AS OBJECT( f_name VARCHAR2(25), l_name VARCHAR2(25), initials VARCHAR2(7), MEMBER FUNCTION full_name RETURN VARCHAR2); TYPE BODY name_type AS MEMBER FUNCTION full_name RETURN VARCHAR2 IS BEGIN RETURN (f_name || ' ' || l_name); END full_name; END;

63. Sample Basic (2/3)

64. Sample basic 3/3 CREATE OR REPLACE TYPE people_typ AS OBJECT ( emp_id NUMBER(7), name name_type ); / CREATE TABLE employee_tab OF people_typ; INSERT INTO employee_tab VALUES(1,name_type('Daisy','Perez','DP')); SELECT p.emp_id, p.name.f_name, p.name.full_name() FROM employee_tab p;

65. Static Methods Static methods are invoked on the object type, not its instances. You use a static method for operations that are global to the type and do not need to reference the data of a particular object instance. A static method has no SELF parameter. You invoke a static method by using the "dot" notation to qualify the method call with the name of the object type: type_name.method().

66. Static Method (1/2) DROP TYPE Customer_typ FORCE; CREATE TYPE Customer_typ AS OBJECT ( id NUMBER, name VARCHAR2(20), addr VARCHAR2(30), STATIC PROCEDURE print(str IN VARCHAR2) ); / CREATE OR REPLACE TYPE BODY Customer_typ AS STATIC PROCEDURE print (str IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(str); END print; END; /

67. Static Method (2/2) DECLARE c1 Customer_typ := Customer_typ (1,'Jean','Paris'); BEGIN Customer_typ.print(c1.name); /* fine */ -- c1.print(c1.name); /* a static method cannot be invoked on an instance value! */ END; /

68. Built-in parameter named SELF Encapsulated Member Methods

69. SELF Member methods are the means by which an application gains access to an object instance’s data. Member methods have a built-in parameter named SELF that denotes the object instance on which the method is currently being invoked. Member methods can reference the attributes and methods of SELF without a qualifier. This makes it simpler to write member methods.

70. Sample 1/3

71. Sample 2/3

72. What About That Test? DECLARE c circle_typ := circle_typ (2); BEGIN c.print(c.area(3)); END; / 28.86

73. New Circle_Typ

74. TYPE BODY

75. Right Behavior

76. Rec_type Specification

77. Rec_type BODY

78. Sample: rec_type

79. Sample 3/3

80. Sample of Member Method (1/2) CREATE TYPE Rational AS OBJECT ( num INTEGER, den INTEGER, MEMBER PROCEDURE normalize, MEMBER FUNCTION gcd (x IN INTEGER, y IN INTEGER) RETURN INTEGER ); /

81. Sample of Member Method (2/2) CREATE OR REPLACE TYPE BODY Rational AS MEMBER FUNCTION gcd(x IN INTEGER,y IN INTEGER) RETURN INTEGER IS ans INTEGER; BEGIN IF (y <= x) AND (x MOD y = 0) THEN ans := y; ELSIF x < y THEN ans := gcd(y, x); ELSE ans := gcd(y, x MOD y); END IF; RETURN ans; END gcd; MEMBER PROCEDURE normalize IS BEGIN DBMS_OUTPUT.PUT_LINE ( gcd(SELF.num,SELF.den)); END normalize; END; /

82. Build-in Parameter in Member Method Member methods have a built-in parameter named SELF that denotes the object instance on which the method is currently being invoked. Member methods can reference the attributes and methods of SELF without a qualifier. SELF does not need to be explicitly declared, although it can be. It is always the first parameter passed to the method. In member functions, if SELF is not declared, its parameter mode defaults to IN. In member procedures, if SELF is not declared, its parameter mode defaults to IN OUT. You invoke a member method using the "dot" notation object_variable.method(). Any parameters occur inside the parentheses,which are required.

83. Sample : for fun (1/6) CREATE TYPE Yahoo AS OBJECT ( price NUMBER, Ticket VARCHAR2(4), MEMBER FUNCTION getFunQuote RETURN VARCHAR2 ); /

84. Sample : for fun (2/6) -- jean pierre perez (Oracle Wireless and Voice Division) CREATE OR REPLACE TYPE BODY Yahoo AS MEMBER FUNCTION getFunQuote RETURN VARCHAR2 IS Nombre NUMBER ; p NUMBER ; ho NUMBER; ao NUMBER; a NUMBER; Ni NUMBER; Di NUMBER; Nj NUMBER; Dj NUMBER; H NUMBER; Z NUMBER; Nk NUMBER; Dk NUMBER; Rapport NUMBER; temp NUMBER;

85. Sample : for fun (3/6)

86. Sample : for fun (4/6)

87. Sample : for fun (6/6)

88. Sample : for fun (6/6) SQL> DECLARE 2 y Yahoo := Yahoo(12.125,'ORCL'); 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(y.price); 5 DBMS_OUTPUT.PUT_LINE( 6 FLOOR(y.price)||' '||y.getFunQuote()); 7 END; 8 / 12.125 12 1/8

89. Methods for Comparing Objects

90. Methods for Comparing Objects The values of a scalar datatype such as CHAR or REAL have a predefined order, which allows them to be compared. But an object type, such as a customer_typ, which can have multiple attributes of various datatypes, has no predefined axis of comparison. To be able to compare and order variables of an object type, you must specify a basis for comparing them. Two special kinds of member methods can be defined for doing this: map methods and order methods. An object type can declare at most one map method (or one order method). A subtype can declare a map method only if its root supertype declares one. An ORDER method must be called for every two objects being compared, whereas a MAP method is called once per object.

91. MAP Method A map method is an optional kind of method that provides a basis for comparing objects by mapping object instances to one of the scalar types DATE, NUMBER, VARCHAR2 or to an ANSI SQL type such as CHARACTER or REAL. A map method is simply a parameterless member function that uses the MAP keyword and returns one of the datatypes just listed. A MAP method, acting like a hash function, maps object values into scalar values, which are then compared using operators If an object type defines one map method, this method is called automatically to evaluate such comparisons as obj_1 > obj_2, which is equivalent in a way to: obj_1.map() > obj_2.map()

92. Sample Map Method (1/2) CREATE OR REPLACE TYPE Parallelepiped_typ AS OBJECT ( name VARCHAR2(10), length NUMBER, width NUMBER, cote NUMBER, MAP MEMBER FUNCTION volume RETURN NUMBER, MEMBER PROCEDURE print ); / CREATE OR REPLACE TYPE BODY Parallelepiped_typ AS MAP MEMBER FUNCTION volume RETURN NUMBER IS BEGIN RETURN SELF.length * SELF.width * SELF.cote; END volume; MEMBER PROCEDURE print IS BEGIN DBMS_OUTPUT.PUT_LINE (SELF.name || ' is the largest.'); END print; END; /

93. V8i (syntax) DECLARE p1 Parallelepiped_typ := Parallelepiped_typ('P1',2,5,10); p2 Parallelepiped_typ := Parallelepiped_typ('P2',3,4,5); BEGIN IF p1>=p2 THEN p1.print; ELSE p2.print; END IF; END; /

94. Sample Map Method (2/2)

95. MAP Member Available from SELECT and ORDER BY Clauses SQL> CREATE TABLE Rectangle_Tab OF Rectangle_Typ; SQL> INSERT INTO rectangle_tab VALUES('R1',8,6); SQL> INSERT INTO rectangle_tab VALUES('R2',2,7); SQL> SELECT r.area() FROM rectangle_tab r WHERE r.name = 'R2' ORDER BY r.area(); R.AREA() ---------- 14

96. Order Methods Order methods make direct object-to-object comparisons in order to tell you that the current object is less than, equal to, or greater than the other object that it is being compared to, with respect to the criterion used by the method. An order method is a function (called automatically) with one declared parameter for another object of the same type. Order methods are useful where comparison semantics may be too complex to use a map method. For example, to compare binary objects such as images, you might create an order method to compare the images by their brightness or number of pixels. An ORDER method is less efficient because it must be called repeatedly (it can compare only two objects at a time) while MAP acts like a hash function.

97. Sample Order Methods (1/3) REM SQL> DROP TYPE Customer_typ FORCE; CREATE TYPE Customer_typ AS OBJECT ( id NUMBER, name VARCHAR2(20), addr VARCHAR2(30), ORDER MEMBER FUNCTION match (c Customer_Typ) RETURN INTEGER, MEMBER PROCEDURE print(str IN VARCHAR2) ); / SHOW ERRORS

98. Sample Order Methods (2/3) 9i syntax CREATE OR REPLACE TYPE BODY Customer_typ AS ORDER MEMBER FUNCTION match (c Customer_Typ) RETURN INTEGER IS BEGIN CASE WHEN SELF.id < c.id THEN RETURN -1; WHEN SELF.id > c.id THEN RETURN 1; ELSE RETURN 0; END CASE; END match; MEMBER PROCEDURE print (str IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(str); END print; END; /

99. Sample Order Methods (2/3) 8i syntax

100. Sample Order Methods (3/3) 9i syntax DECLARE c1 Customer_typ := Customer_typ(1,'Jean','Paris'); c2 Customer_typ := Customer_typ(2,'Pierre','Rouen'); c3 Customer_typ := Customer_typ(1,'Pierre','Lyon'); BEGIN CASE WHEN (c1.match(c2)=-1) THEN c1.print('Smaller!'); WHEN (c1.match(c2)= 1) THEN c1.print('Larger!'); ELSE c1.print('Oops!'); END CASE; CASE WHEN (c1.match(c3)=-1) THEN c1.print('Smaller!'); WHEN (c1.match(c3)= 1) THEN c1.print('Larger!'); ELSE c1.print('Oops!'); /* same ID ! */ END CASE; END; /

101. Constructor Methods Every object type has a constructor method implicitly defined for it by the system. A constructor method is a function that returns a new instance of the user-defined type and sets up the values of its attributes. You can also explicitly define your own constructors. A constructor method is a function; it returns the new object as its value. The name of the constructor method is just the name of the object type. Its parameters have the names and types of the object type’s attributes.

102. Comparison Methods in Type Hierarchies In a type hierarchy, where definitions of specialized types are derived from definitions of more general types, only the root type—the most basic type, from which all other types are derived—can define an order method. If the root type does not define one, its subtypes cannot define one either. If the root type specifies a map method, any of its subtypes can define a map method that overrides the map method of the root type. But if the root type does not specify a map method, no subtype can specify one either. So if the root type does not specify either a map or an order method, none of the subtypes can specify either a map or order method.

103. Constructor

104. Type Inheritance Open "Inheritance and Storage (2/3)" Power Point Document to move forward

105. Hierarchy A type hierarchy is a sort of family tree of object types. It consists of a parent base type, called a supertype, and one or more levels of child object types, called subtypes, derived from the parent. Subtypes in a hierarchy are connected to their supertypes by inheritance. This means that subtypes automatically acquire the attributes and methods of their parent type. It also means that subtypes automatically acquire any changes made to these attributes or methods in the parent: any attributes or methods updated in a supertype are updated in subtypes as well.

106. Inheritance SET SERVEROUTPUT ON CREATE OR REPLACE TYPE Person AS OBJECT ( name VARCHAR(20), MEMBER PROCEDURE print ) NOT FINAL; / CREATE OR REPLACE TYPE BODY Person AS MEMBER PROCEDURE print AS BEGIN DBMS_OUTPUT.PUT_LINE('Name: ' || name); END; END; /

107. Inheritance CREATE OR REPLACE PROCEDURE printName IS p Person; BEGIN p := Person('Jean Pierre'); p.print(); END; /

108. Inheritance CREATE OR REPLACE TYPE vip UNDER Person ( phone VARCHAR2(20), OVERRIDING MEMBER PROCEDURE print ); / CREATE OR REPLACE TYPE BODY Vip AS OVERRIDING MEMBER PROCEDURE print AS BEGIN DBMS_OUTPUT.PUT_LINE('Name: ' || name); DBMS_OUTPUT.PUT_LINE('Name: ' || phone); END; END; /

109. Inheritance CREATE OR REPLACE PROCEDURE printBusinessCard IS v Vip; BEGIN v := Vip('Jean Pierre','0685910407'); v.print(); END; /

110. Inheritance SQL> EXEC printName; Name: Jean Pierre PL/SQL procedure successfully completed. SQL> EXEC printBusinessCard; Name: Jean Pierre Name: 0685910407

111. Dealing with Collections

112. Collections For modeling one-to-many relationships, Oracle supports three collection datatypes: varrays and nested tables, & associative array Collection types can be used anywhere other datatypes can be used: you can have object attributes of a collection type, columns of a collection type, and so forth.

113. Aspects to Consider while Working with Collections What Is a Collection? Defining and Declaring Collections Initializing and Referencing Collections Assigning and Comparing Collections Manipulating Collections Using Collection Methods Avoiding Collection Exceptions Reducing Loop Overhead for Collections with Bulk Binds What Is a Record? Defining and Declaring Records Initializing Records Assigning Records Manipulating Records

114. Collections A collection is an ordered group of elements, all of the same type. Collections work like arrays. Collections can store instance of an object type can, conversely, can be attributes of an object type. Three kinds of collections in PL/SQL: VARRAY and NESTED TABLE, plus ASSOCIATIVE ARRAY Associative Arrays are classified as collections also known as index-by tables. A collection is an ordered group of elements, all of the same type (for example, the grades for a class of students). Each element has a unique subscript that determines its position in the collection. PL/SQL offers two collection types. Items of type TABLE are either index-by tables (Version 2 PL/SQL tables) or nested tables (which extend the functionality of index-by tables). Items of type VARRAY are varrays (short for variable-size arrays). Collections work like the arrays found in most third-generation programming languages. However, collections can have only one dimension and must be indexed by integers. (In some languages such as Ada and Pascal, arrays can have multiple dimensions and can be indexed by enumeration types.) You can define collection types in a package, then use them programmatically in your applications. Also, you can pass collections as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms. In addition, collections can store instances of an object type and (except for index-by tables) can be attributes of an object type. A collection is an ordered group of elements, all of the same type (for example, the grades for a class of students). Each element has a unique subscript that determines its position in the collection. PL/SQL offers two collection types. Items of type TABLE are either index-by tables (Version 2 PL/SQL tables) or nested tables (which extend the functionality of index-by tables). Items of type VARRAY are varrays (short for variable-size arrays). Collections work like the arrays found in most third-generation programming languages. However, collections can have only one dimension and must be indexed by integers. (In some languages such as Ada and Pascal, arrays can have multiple dimensions and can be indexed by enumeration types.) You can define collection types in a package, then use them programmatically in your applications. Also, you can pass collections as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms. In addition, collections can store instances of an object type and (except for index-by tables) can be attributes of an object type.

115. Les méthodes associées

116. Propriétés comparées

117. Collection Types

118. Nested Table Is a table that is a column of another table. In other words, it is a table within a table. Nested table are unbounded (size can increase dynamically) collections of homogeneous elements. Oracle server stores the rows of a nested table in a particular order. Initially dense but they can become sparse through deletions. Available both PL/SQL as well as the Database.

119. VARRAYS Variable-size arrays, or VARRAYs, are also collections of homogeneous elements. However, Varrays are always bounded and never sparse. Maximum size is specified in its type definition.

120. Nested Table Versus VARRAYS Nested Table Do not have maximum size Can be sparse Stored out-of-line. Not in the same segment as the containing one. In the database,do not retain ordering and subscripts. VARRAYS Have a maximum size Are always dense Stored in-line (if < 4Ko). In the same segment as the containing one. In the database, retain ordering and subscripts Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows. Within PL/SQL, nested tables are like one-dimensional arrays. However, nested tables differ from arrays in two important ways. First, arrays have a fixed upper bound, but nested tables are unbounded. So, the size of a nested table can increase dynamically. Second, arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array. Initially, nested tables are dense, but they can be sparse (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE. That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts. Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows. Within PL/SQL, nested tables are like one-dimensional arrays. However, nested tables differ from arrays in two important ways. First, arrays have a fixed upper bound, but nested tables are unbounded. So, the size of a nested table can increase dynamically. Second, arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array. Initially, nested tables are dense, but they can be sparse (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE. That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts.

121. Creating Collection Types Nested table in the database CREATE [OR REPLACE] TYPE type_name AS TABLE OF element datatype [NOT NULL]; VARRAY in the database CREATE [OR REPLACE] TYPE type_name AS {VARRAY | VARYING ARRAY} (max_elements) OF element datatype [NOT NULL];

122. Nested Table is any PL/SQL datatype: except BINARY_INTEGER, PLS_INTEGER BOOLEAN LONG, LONG RAW NATURAL, NATURALN POSITIVE, POSITIVEN REF CURSOR SIGNTYPE STRING

123. VARRAY is any PL/SQL datatype: except  BINARY_INTEGER, PLS_INTEGER BOOLEAN BLOB, CLOB (restriction applies only to varrays) LONG, LONG RAW NATURAL, NATURALN NCHAR, NCLOB, NVARCHAR2 object types with BLOB or CLOB attributes (restriction applies only to varrays) object types with TABLE or VARRAY attributes POSITIVE, POSITIVEN REF CURSOR SIGNTYPE STRING TABLE VARRAY

124. Sample 1/4 CREATE TYPE Project_Typ AS OBJECT ( project_no NUMBER(2), title VARCHAR2(35), cost NUMBER(7,2) ); / CREATE TYPE ProjectList AS VARRAY (50) OF Project_Typ; /

125. Sample 2/4

126. Sample 3/4

127. Sample4/4

128. Adding One More Row

129. SELECT…TABLE…

130. Using Collection Methods (extra) DECLARE i INTEGER; x ProjectList; BEGIN x := ProjectList(Project_Typ(1,'Design New Rpt',3250), Project_Typ(2,'Outsource',12350), Project_Typ(3,'Audit',1425) ); i := x.FIRST; WHILE i IS NOT NULL LOOP IF x(i).cost > 10000 THEN DBMS_OUTPUT.PUT_LINE(x(i).title); END IF; i := x.NEXT(i); END LOOP; END; /

131. Sample of Nested Table (#1/3)

132. Nested Table Storage (#2/3)

133. Sample of Nested Table (#3/3)

134. Referencing an Element in the Nested Table by Subscript DECLARE TYPE t IS TABLE OF VARCHAR2(15); names t := t('Jean', 'Daisy', 'Moa'); PROCEDURE ln (str IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(str); END ln; BEGIN FOR i IN names.FIRST .. names.LAST LOOP IF names(i) = 'Jean' THEN ln(names(i)); END IF; END LOOP; END; /

135. Inserting a Nested Table into a Database Table (#1/2) CREATE TYPE CourseList AS TABLE OF VARCHAR2(64); / CREATE TABLE department ( name VARCHAR2(20), director VARCHAR2(20), office VARCHAR2(20), courses CourseList) NESTED TABLE courses STORE AS courses_tab2;

136. Inserting a Nested Table into a Database Table (#2/2) BEGIN INSERT INTO department VALUES('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList('Expository Writing', 'Film and Literature', 'Modern Science Fiction', 'Discursive Writing', 'Modern English Grammar', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The American Novel')); END; /

137. Retrieving a PL/SQL Nested Table from a Database Table DECLARE container CourseList; BEGIN SELECT courses INTO container FROM department WHERE name = 'English'; FOR j IN container.FIRST..container.LAST LOOP IF container.EXISTS(j) THEN DBMS_OUTPUT.PUT_LINE(container(j)); END IF; END LOOP; END; /

138. Updating a Nested Table within a Database Table DECLARE new_courses CourseList := CourseList('Expository Writing', 'Film and Literature', 'Discursive Writing', 'Modern English Grammar', 'Realism and Naturalism', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The American Novel', 'Advanced Workshop in Poetry'); BEGIN UPDATE department SET courses = new_courses WHERE name = 'English'; END; /

139. Manipulating Individual Elements

140. More Sample About VARRAYs

141. Sample using VARRAYS (#1/2)

142. SET SERVEROUTPUT ON DECLARE CURSOR c1 IS SELECT e.ename AS nom,d.dname AS service, e.hiredate AS quand FROM emp e, dept d WHERE e.deptno = d.deptno AND e.deptno = 10; TYPE t1 IS TABLE OF c1%ROWTYPE NOT NULL INDEX BY BINARY_INTEGER; array1 t1; PROCEDURE ln (String IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(String); END; BEGIN FOR v1 IN c1 LOOP array1(array1.COUNT) := v1; END LOOP; FOR k IN array1.FIRST..array1.LAST LOOP ln(array1(k).nom || ' ' ||array1(k).service || ' ' || TO_CHAR(array1(k).quand,'Day DD Month') ); END LOOP; END; /

143. Sample using VARRAYS and Database (#2/2)

144. Working with Collections in PL/SQL You can declare collections as the formal parameters of functions and procedures. That way, you can pass collections to stored subprograms and from one subprogram to another. The following example declares a nested table as a parameter of a packaged procedure. You can also specify a collection type in the RETURN clause of a function specification Collections follow the usual scoping and instantiation rules.

145. DECLARE CURSOR c1 IS SELECT e.ename, d.dname, sal revenu, e.hiredate FROM emp e, dept d WHERE e.deptno = d.deptno AND d.deptno = 10; TYPE t1 IS TABLE OF c1%ROWTYPE NOT NULL INDEX BY VARCHAR2(64); array1 t1; BEGIN FOR v1 IN c1 LOOP array1(v1.ename) := v1; array1(v1.ename).revenu := array1(v1.ename).revenu * 12; INSERT INTO test_c1(nom,service,revenu,quand) VALUES( array1(v1.ename).ename, array1(v1.ename).dname, array1(v1.ename).revenu, array1(v1.ename).hiredate ); DBMS_OUTPUT.PUT_LINE( array1(v1.ename).ename ||' inserted.'); END LOOP; COMMIT; END; /

146. Associative Array Basic, from a package, and passing IN OUT parameters

147. Associative Array DECLARE TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64); country_population population_type; continent_population population_type; howmany NUMBER; BEGIN country_population('Greenland') := 100000; country_population('Iceland') := 750000; howmany := country_population('Greenland'); DBMS_OUTPUT.PUT_LINE(howmany); continent_population('Australia') := 30000000; END; /

148. Writing and Compiling PL/SQL Packages. A Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. CREATE PACKAGE name AS -- specification (visible part) -- public type and object declarations -- subprogram specifications END [name]; CREATE PACKAGE BODY name AS -- body (hidden part) -- private type and object declarations -- subprogram bodies [BEGIN -- initialization statements] END [name];

149. Two parts

150. Sample of Package (#1/3) 9i REM Package specification CREATE OR REPLACE PACKAGE coll_pkg AS TYPE pop_t IS TABLE OF NUMBER INDEX BY VARCHAR2(64); PROCEDURE print (str IN VARCHAR2); PROCEDURE howmany(city IN pop_t, arg IN VARCHAR2); END coll_pkg; /

151. Sample of Package (#2/3) 9i REM Package body CREATE OR REPLACE PACKAGE BODY coll_pkg AS PROCEDURE print (str IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(str); END print; PROCEDURE howmany(city IN pop_t, arg IN VARCHAR2) IS BEGIN print (UPPER(arg) || ': ' || city(arg)); END howmany; END coll_pkg; /

152. Sample of Package (#3/3) 9i REM Runtime DECLARE town coll_pkg.pop_t; BEGIN town('Paris') := 8000000; coll_pkg.howmany(town,'Paris'); END; / PARIS: 8000000

153. Using Collection Methods EXISTS COUNT LIMIT FIRST and LAST PRIOR and NEXT EXTEND TRIM DELETE Collection_name.METHOD_NAME[(parameters)]

154. PS: About IN OUT and NOCOPY Hint Formal parameters can have three modes – IN, OUT, and IN OUT. Oracle8i added a NOCOPY modifier. IN: the formal parameter acts like a PL/SQL constant. It is considered read-only and cannot be changed. OUT: the formal parameter acts like an unitialized PL/SQL variable, and thus has a value of NULL. It can be read from and written to. IN OUT mode: the value of the actual parameter is passed into the procedure when the procedure is invoked. Inside the procedure, the formal parameter acts like an initialized variable,and can be read from and written to. If OUT NOCOPY is present, the PL/SQL compiler will try to pass the parameter by reference, rather than by value. The primary advantage of NOCOPY is that it may increase performance. This is especially valuable when passing large PL/SQL tables.

155. Using Collection Methods (#1/4)

156. Using Collection Methods (#2/4)

157. Using Collection Methods (#3/4)

158. Using Collection Methods (#4/4)

159. Multilevel Collection Types

160. Multilevel Collection Types Multilevel collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multilevel collection types are: Nested table of nested table type Nested table of varray type Varray of nested table type Varray of varray type Nested table or varray of a user-defined type that has an attribute that is a nested table or varray type Like ordinary, single-level collection types, multilevel collection types can be used with columns in a relational table or with object attributes in an object table.

161. Using Multilevel Collections In addition to collections of scalar or object types, you can also create collections whose elements are collections. For example, you can create a nested table of varrays, a varray of varrays, a varray of nested tables, and so on. When creating a nested table of nested tables as a column in SQL, check the syntax of the CREATE TABLE statement to see how to define the storage table

162. Nested Table Storage Tables A nested table type column or object table attribute requires a storage table where rows for all nested tables in the column are stored. Similarly with a multilevel nested table collection of nested tables: the inner set of nested tables requires a storage table just as the outer set does. You specify one by appending a second nested-table storage clause.

163. Walking on the Moon Multilevel Collection Types

164. Create environment again Drop table stars; Drop type satellite_t force; Drop type nt_sat_t force; Drop type planet_t force; Drop type nt_pl_t force;

165. Sample: Walking on the Moon (1/4) CREATE OR REPLACE TYPE satellite_t AS OBJECT ( name VARCHAR2(20), diameter NUMBER); / CREATE OR REPLACE TYPE nt_sat_t AS TABLE OF satellite_t; / CREATE TYPE planet_t AS OBJECT ( name VARCHAR2(20), mass NUMBER, satellites nt_sat_t); / CREATE TYPE nt_pl_t AS TABLE OF planet_t; /

166. Nested Table Storage Tables (2/4) CREATE TABLE stars ( name VARCHAR2(20), age NUMBER, planets nt_pl_t) NESTED TABLE planets STORE AS planets_tab (NESTED TABLE satellites STORE AS satellites_tab);

167. Nested Table Storage Tables (2/4) Physical attributes for the storage tables can be specified in the nested table clause. For example: CREATE TABLE stars ( name VARCHAR2(20), age NUMBER, planets nt_pl_t) NESTED TABLE planets STORE AS planets_tab ( PRIMARY KEY (NESTED_TABLE_ID, name) ORGANIZATION INDEX COMPRESS NESTED TABLE satellites STORE AS satellites_tab );

168. Constructors for Multilevel Collections (3/4) INSERT INTO stars VALUES('Sun',23, nt_pl_t( planet_t( 'Neptune', 10, nt_sat_t( satellite_t('Proteus',67), satellite_t('Triton',82) ) ), planet_t( 'Hearth', 400, nt_sat_t( satellite_t('Moon',120) ) ) ) );

169. Constructors for Multilevel Collections The previous example calls the constructor for the multilevel collection type nt_pl_t. This type is a nested table of planets, each of which contains a nested table of satellites as an attribute. The constructor for the outer nested table calls the planet_t constructor for each planet to be created; each planet constructor calls the constructor for the satellites nested table type to create its nested table of satellites; and the satellites nested table type constructor calls the satellite_t constructor for each satellite instance to be created.

170. Query Collections There are two general ways to query such table : One way returns the collections nested in the result rows that contain them: SELECT s.name NAME,s.planets PLANETS FROM stars s; NAME PLANETS ---- ----------- Sun, NT_PL_T(PLANET_T('Neptune', 10, NT_SAT_T( SATELLITE_T( The other way distributes or unnests collections such that each collection element appears on a row by itself by using a TABLE expression with the collection to join the nested table with the row that contains the nested table. SQL> SELECT p.name 2 FROM stars s, TABLE(s.planets) p; NAME -------------------- Neptune Jupiter

171. Sample: Walking on the Moon (4/4) SQL> SELECT t.name 2 FROM stars s, 3 TABLE(s.planets) p, 4 TABLE(p.satellites) t 5 WHERE p.name = 'Hearth'; NAME -------------------- Moon

172. Performing DML Operations on Collections Oracle supports the following DML operations on nested table columns: Inserts and updates that provide a new value for the entire collection Piecewise Updates Inserting new elements into the collection Deleting elements from the collection Updating elements of the collection.

173. Piecewise Operations on Nested Tables INSERT INTO stars VALUES('Alpha',4, nt_pl_t( planet_t( 'Exoplanet', 3, nt_sat_t( satellite_t('Voyager',67) ) ) ) );

174. Piecewise Insert into an Inner Nested Table DECLARE null_table_value EXCEPTION; PRAGMA EXCEPTION_INIT (null_table_value,-22908); BEGIN INSERT INTO TABLE( SELECT p.satellites FROM TABLE( SELECT s.planets FROM stars s WHERE s.name = 'Sun') p WHERE p.name = 'Hearth') VALUES ('Miranda', 31); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); EXCEPTION WHEN null_table_value THEN NULL; /* Uranus */ END; /

175. Querying a Multilevel Collection Variable DECLARE v_planets nt_pl_t := nt_pl_t( planet_t( 'Hermes', 10, nt_sat_t(satellite_t('Apollo',67)) ) ); BEGIN DBMS_OUTPUT.PUT_LINE(v_planets(1).name); DBMS_OUTPUT.PUT_LINE(v_planets(1).mass); DBMS_OUTPUT.PUT_LINE(v_planets(1).satellites(1).name); DBMS_OUTPUT.PUT_LINE(v_planets(1).satellites(1).diameter; EXCEPTION WHEN SUBSCRIPT_BEYOND_COUNT THEN NULL; END; /

176. Multilevel Collections Updated Atomically DECLARE v_planets nt_pl_t := nt_pl_t( planet_t( 'Hermes', 10, nt_sat_t( satellite_t('Apollo',67), satellite_t('Spoutnik',57) ) ) ); BEGIN UPDATE stars s SET s.planets = v_planets WHERE s.name = 'Alpha'; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); END; /

177. More samples about Nested Collections

178. Sample: Cars with Options CREATE TYPE options_obj AS OBJECT ( name VARCHAR2(20), price NUMBER); / CREATE TYPE options_typ AS TABLE OF options_obj; / CREATE TABLE car_tab ( name VARCHAR2(20), age NUMBER, options options_typ) NESTED TABLE options STORE AS options_tab; INSERT INTO car_tab VALUES('Clio',5, options_typ( options_obj('Radio',200), options_obj('Air Conditioning',500), options_obj('Convertible',1230) ) );

179. Select… SQL> SELECT t.name 2 FROM TABLE(SELECT c.options FROM car_tab c) t; NAME -------------------- Radio Air Conditioning Convertible

180. Multilevel Nested Table Example (#1/2) DECLARE TYPE tb1 IS TABLE OF VARCHAR2(20); TYPE ntb1 IS TABLE OF tb1; -- table of table elements TYPE tv1 IS VARRAY(10) OF INTEGER; TYPE ntb2 IS TABLE OF tv1; -- table of varray elements vtb1 tb1 := tb1('one', 'three'); vntb1 ntb1 := ntb1(vtb1); vntb2 ntb2 := ntb2( tv1(3,5),tv1(5,7,3) ); -- table of varray elements PROCEDURE ln (str IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(str); END ln;

181. Multilevel Nested Table Example (#1/2) BEGIN ln ( vtb1(vtb1.LAST) ); vntb1.EXTEND; vntb1(2) := vntb1(1); -- delete the first element in vntb1 vntb1.DELETE(1); /* delete the first string from the second table in the nested table. */ vntb1(2).DELETE(1); END; /

182. Multilevel VARRAY Example (#1/2) DECLARE TYPE t1 IS VARRAY(10) OF INTEGER; -- multilevel varray type TYPE nt1 IS VARRAY(10) OF t1; va t1 := t1(2,3,5); -- initialize multilevel varray nva nt1 := nt1 (va, t1(55,6,73), t1(2,4), va); i INTEGER; va1 t1; PROCEDURE ln (str IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(str); END ln; BEGIN -- multilevel access i := nva(2)(1); --> return value 55 ln(i);

183. Multilevel VARRAY Example (#1/2) -- add a new varray element to nva nva.EXTEND; nva(5) := t1(56, 32); -- replace an inner varray element nva(4) := t1(45,43,67,43345); -- replace an inner integer element nva(4)(4) := 1; -- replaces 43345 with 1 -- add a new element to the 4th varray element -- and store integer 89 into it. nva(4).EXTEND; nva(4)(5) := 89; END; /

184. Extra Sample DECLARE TYPE t1 IS VARRAY(3) OF INTEGER; TYPE nt1 IS VARRAY(2) OF t1; -- multilevel varray type X t1 := t1(5,6,9); Y t1 := t1(0,4,8); -- initialize multilevel varray M nt1 := nt1 (X,Y); s INTEGER := 0; PROCEDURE ln (str IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(str); END ln; BEGIN -- multilevel access FOR i IN M.FIRST..M.COUNT LOOP FOR j IN Y.FIRST..Y.LAST LOOP s := s + M(i)(j); END LOOP; END LOOP; ln('Sum = ' || s); END; /

185. Trial Using Nested Table PSP

186. The Big Picture

187. Create Object, ADT & Nested Table CREATE TYPE options_obj AS OBJECT ( name VARCHAR2(28), choice VARCHAR2(1), price NUMBER); / CREATE TYPE options_typ AS TABLE OF options_obj; / CREATE TABLE car_tab ( name VARCHAR2(23), price NUMBER, options options_typ) NESTED TABLE options STORE AS options_tab;

188. Populating the CAR_TAB INSERT INTO car_tab VALUES('Clio',9499, options_typ( options_obj('Projecteurs',NULL,250), options_obj('Toit ouvrant',NULL,500), options_obj('Climatisation','1',900), options_obj('Radiosat','1',700), options_obj('Peinture',NULL,500) ) );

189. Query including Nested Table SQL> SELECT c.name AS GAMME, o.name AS OPTIONS, o.price AS PRICE FROM car_tab c, TABLE(c.options) o; GAMME OPTIONS PRICE ---------- --------------- ---------- Clio Projo 1000 Capote 1000 Climatisation 900 …/… Megane Radiosat CD 950 Radiosat 700 …/…

190. Update UPDATE TABLE(SELECT c.options FROM car_tab c WHERE c.name = 'Clio') o SET VALUE(o) = options_obj('Capote',1,1000) WHERE o.name = 'Toit ouvrant';

191. Handle the Nested Table Within the Table UPDATE TABLE(SELECT c.options FROM car_tab c WHERE c.name = 'Clio') o SET VALUE(o) = options_obj('Capote',1,1000) WHERE o.name = 'Toit ouvrant'; INSERT INTO TABLE(SELECT c.options FROM car_tab c WHERE c.name = 'Clio') VALUES ('Jantes', 1, 2000); DELETE FROM TABLE(SELECT c.options FROM car_tab c WHERE c.name = 'Clio') o WHERE o.name = 'Jantes'; COMMIT;

192. Type evolution Oracle10g

193. Oracle Object-Relational and Java Open the proper material (3/3)

194. Storage & Performances using Oracle Object Design See second materials