Chapter 4 ObjectRelational SQL

1
Chapter 4 Object-Relational SQL

• ??????
• caili_at_ynu.edu.cn

2
Chapter4 Object-Relational SQL
4.1 Introduction 4.2 Objects and Tables 4.3
Collection Types 4.4 Procedural SQL, User-Defined
Functions (UDFs), and Methods 4.5 External
Functions and Packaged User-Defined Types (UDTs)
3
4.1 Introduction

• ORSQL Capabilities
• The object-relational model of ORSQL supports a
  composite structured type the user can declare to
  define what are sometimes referred to as
  objects-what we have been used to thinking of as
  rows-that is, a grouping of typed data values
  that can be stored in a table.

4
4.1 Introduction

• ORSQL Capabilities
• Collection Types????
• The object-relational model allows a column value
  (on a single row) to contain a set (or some other
  type of collection, such as array) of row-like
  values.
• In some systems a single column value can itself
  hold a table, a feature known as table nesting.
• The object-relational model breaks the first
  normal form rule by permitting column values that
  contain multi-valued, structured data values.

5
4.1 Introduction

• ORSQL Capabilities
• Collection Types????

6
4.1 Introduction

• ORSQL Capabilities
• Methods and UDFs
• In object-oriented languages such as Java, any
  private data in an object can be accessed only
  via object methods such methods are functions
  that can be called to operate on one particular
  object.
• In ORSQL all data is considered to be public
  rather than private.
• UDFs are not so attached but can deal with
  multiple different objects as parameters of equal
  importance.

7
4.2 Objects and Tables

• In the ORACLE object-relational model we have a
  new kind of user-defined type, known as an object
  type.
• 4.2.1 Object Types in ORACLE
• How to create an object type
• Example 4.2.1

8
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• No object of this type has yet created, for that,
  we need to create a table whose rows or columns
  can hold objects.
• Example 4.2.2

This is an empty table, so we have still not
create any objects of type name_t.
9
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• We use a form of dot notation to access
  attributes within the tname object column of the
  teachers table defined in Example 4.2.2.

??????,??
10
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Example 4.2.3

Note that name_t is a type that must already be
defined in order for the definition of person_t
to execute properly, and this is what is called a
dependency person_t is dependent on name_t.
11
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• A table in ORACLE is called an object table if
  its rows are of object type, that is, each of its
  rows consists of an object of that type.
• Example 4.2.4

equally
12
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Example 4.2.4

13
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Example 4.2.4 simple queries
• select p.age from people p where p.ssno123
• select p.pname from people p where p.agegt25
• The name_t objects in the pname column will be
  printed out in a format such as
  name_t(Sanchez,Jose,F)

14
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Example 4.2.4 simple queries
• select from people p where p.agegt25

The non-object columns are displayed as expected.
The column pname has a heading showing its
attribute names, and its values are contained in
the object constructor name_t().
15
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Example 4.2.4 we can retrieve all columns of an
  object table such as people as a single column
  value.
• select value(p) from people p where p.agegt25

16
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Example 4.2.5
• select value(p) from people p
• where p.pnamename_t(Sanchez,Jose,F)
• Note that it is the name_t() that is the object
  constructor here, not the value() form, which can
  only be used to retrieve the current row of a
  table the value() form cannot be used to
  constructor a new object.

17
4.2 Objects and Tables
ORACLE requires all attribute-accessing
expressions to be fully qualified by an alias

• 4.2.1 Object Types in ORACLE
• Example 4.2.6 uses nested dot notation to
  access an attribute of a column object.
• select p.pname, p.age from people p
• where p.pname.fname like Pat and p.agegt50
• select p.pname from people p
• select pname from people
• select pname.fname from people
• select people.pname.fname from people

Doesnt Work!
18
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Insert and Update statement can also use object
  constructors to specify values for new rows but
  with rather strict rules.
• Example 4.2.7
• create table scientists of name_t
• insert into scientists values (Einstein,Albert
  ,E)
• consider
• insert into scientists name_t (Einstein,Albert
  ,E)

name_t lname fname mi
Doesn't Work! Insert to be followed by either a
VALUES keyword or a Subquery.
19
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• consider
• update scientists s
• set sname_t (Einstein,Andrew,F)
• where values(s)name_t (Einstein,Albert,
  E)

construct row object
Works! Replacing an entire row object with an
object constructed value.
20
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• consider
• insert into people
• values (1234, name_t (Einstein,Andrew,F
  ), 10)
• insert into scientists
• select p.pname from people p

ALL Works!
21
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• P185 Figure4.6???

22
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Definition of the REF Object Reference
• Objects that appear in object tables are called
  row objects, whereas objects that appear as table
  columns (or attributes within other objects) are
  called column objects.

Column objects
Row objects
23
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Definition of the REF Object Reference
• ORACLE provides all row objects with a unique
  means of identification, known as an object
  identifier.
• And a column of a table can be declared to have a
  built-in data type called a REF to allow it to
  point to a row object of a object table.
• Example 4.2.9
• We can use REFs to avoid what may be inefficient
  joins between orders and the other tables.

24
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Definition of the REF Object Reference
• P185Example4.2.9

25
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE
• Definition of the REF Object Reference
• Example 4.2.9
• select o.orderno, o.ordcust.cname
• from orders o
• where o.dollarsgt200.00
• We have replace a join query with a much simpler
  REF syntax, and the result is not only simpler
  syntax but the cname value is also more efficient
  to access.

26
4.2 Objects and Tables
REF more efficiently

• 4.2.1 Object Types in ORACLE
• Definition of the REF Object Reference
• Example 4.2.10
• select distinct o.ordcust.cname, o.ordagent.anme
• from orders o
• 3.3.4
• select distinct customers.cname, agents.anme
• from customers, orders, agnets
• where customers.cidorders.cid and
• orders.aidagnets.aid

27
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE-Definition of the
  REF Object Reference
• Example 4.2.11 A query that requires a
  self-join of orders with itself is not shortened.
• select distinct x1.pid
• from orders x1, orders x2
• where x1.pidx2.pid and x1.ordcustltx2.ordcust
• Retrieving a single orders row together with
  related information from customers, agents, and
  products will be more efficiently handled through
  a REF form than through a join of foreign key to
  primary key.

28
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE-Definition of the
  REF Object Reference
• ORACLE provide a function REF() that can be used
  to derive the REF value of an object appearing in
  an SQL statement in some other connection.
• Example 4.2.12
• select c.cname from customers c
• where not exists (select from orders x
• where x.ordcustref(c) and x.aida05)

It should be clear how the Subquery is dependent
on the outer Select statement this is a
correlated Subquery.
29
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE-Definition of the
  REF Object Reference
• In FOR ALL queries, we see some improvement in
  complexity from using REFs.
• Example4.2.13
• select c.cid from customers c where
• not exists (select from agents a
• where a.cityNew York and
• not exists (select from orders x
• where x.ordcustref(c) and
• 
  x.ordagentref(a)))

30
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE-Definition of the
  REF Object Reference
• An object type cannot recursively contain a
  component of the same type, but it can contain a
  REF to another object of the same type.
• Example4.2.15

create type police_officer_t as object
pol_person person_t, badge_number
integer, partner ref
police_officer_t
create table police_officers of police
officer_t primary key(badage_number), scope
for (partner) is
police_officers
31
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE-Loading Tables with
  REFs
• In the case of the orders table, the REF columns
  are determined completely by the foreign key cid,
  aid, or pid column values in orders, since these
  columns are primary key in their own tables.
• We can start by using the same load procedure
  with the same data as in the relational case.
• Since the REF columns in orders are the last ones
  defined, this will result in null values for
  REFs. Then we can set all the references by one
  Update as follows.

32
4.2 Objects and Tables

• 4.2.1 Object Types in ORACLE-Loading Tables with
  REFs
• Example4.2.17
• update orders o set
• ordcust(select ref(c) from customers c where
  c.cido.cid),
• ordagent(select ref(a) from agents a where
  a.aido.aid),
• ordprod(select ref(p) from products p where
  p.pido.pid)

33
4.2 Objects and Tables

• 4.2.3 Objects and Tables Summary

34
4.3 Collection Types

• Collection types allow us to put multiple values
  in a column of an individual row.

35
4.3 Collection Types

• 4.3.1 Collection Types in ORACLE
• There are two collection types in ORACLE table
  types and array types.
• Table Types and Nested Tables
• Example 4.3.1
• create type dependents_t as table of person_t

????person_t??????
36
4.3 Collection Types

• 4.3.1 Collection Types in ORACLE
• Table Types and Nested Tables
• Example 4.3.1
• create table employees
• ( eid int,
• eperson person_t,
• dependents dependent_t,
• primary key(eid)
• ) nested table dependents store as
  dependents_tab

dependents??????,???dependents_tab
37
4.3 Collection Types

• 4.3.1 Collection Types in ORACLE
• Table Types and Nested Tables
• Example 4.3.2 accessing nested tables.
• select dependents from employees
• where eid101
• Example 4.3.4
• select eid from employees e
• where 6lt( select count()
• from table(e.dependents)
• )

38
4.3 Collection Types

• 4.3.1 Collection Types in ORACLE
• Array Types for VARRAY
• The second collection type for ORACLE is the
  array type, declared as VARRAY, standing for
  varraying-length array.
• Example 4.3.11
• create type extensions_t as varray(4) of int

extensions_t????4???????
39
4.3 Collection Types

• 4.3.1 Collection Types in ORACLE
• Array Types for VARRAY
• Example 4.3.11
• create table phonebook
• (
• phperson person_t,
• extensions extensions_t
• )
• p210 Figure4.19

40
4.3 Collection Types

• 4.3.1 Collection Types in ORACLE
• Array Types for VARRAY
• Example 4.3.12
• select pb.phperson.fname, pb.extensions
• from phonebook pb
• where pb.phperson.ssno1234
• Example 4.3.13
• select (select count() from table(pb.extensions))
  
• from phonebook pb
• where pb.phperson.sson1234

41
4.3 Collection Types

• 4.3.1 Collection Types in ORACLE
• Inserts and Updates in ORACLE
• Example 4.3.15
• insert into employees values
• (101, person_t(123,name_t(Smith,John,p),45)
  , dependents_t(
• person_t(322,name_t(Smith,Michael,J),8),
  person_t(138, name_t(Smith,Susan,R),12)
• )
• )
• p201 Figure4.12

42
4.3 Collection Types

• 4.3.1 Collection Types in ORACLE
• Inserts and Updates in ORACLE
• Example 4.3.15
• update phonebook set
• extensionsextensions_t(345,999)
• where eid101