Oracle Collections - PowerPoint PPT Presentation

1 / 45

About This Presentation

Title:

Oracle Collections

Description:

... to a column or expression in the cursor SELECT statement. Nested Records ... Create corresponding cursors and records :FETCH into a record, rather than into ... – PowerPoint PPT presentation

Number of Views:716

Avg rating:3.0/5.0

Slides: 46

Provided by: Comp66

Category:

more less

Transcript and Presenter's Notes

Title: Oracle Collections

1
Oracle Collections

VARRAYs (varying arrays)
Nested Tables (PL/SQL Tables)
PL/SQL Record

2
VARRAYs

A VARRAY (varying array) is a variable-length
ordered list of values of either a standard or
user-defined type
A VARRAY has a maximum number values which is
specified at array type creation
The elements inside a VARRAY cannot serve as an
index into a table
VARRAYs cannot be used when the number of
elements is unknown or is very large

3
VARRAY syntax

VARRAYs in Oracle are treated as an array of
elements of a supported or abstract data type
Use this syntax when creating a VARRAY type
CREATE OR REPLACE TYPE xxx_va
AS VARRAY(n) OF (type)

4
VARRAY syntax

For example, if we wanted to create a VARRAY of
18 numbers representing the scores on 18 holes in
a golf game, we could use an array of numbers
CREATE OR REPLACE TYPE scores_va
AS VARRAY(18) of NUMBER(2,0)

5
VARRAY syntax

Lets create an array of ship-to addresses for a
customer. First, we could create the address
type as
CREATE OR REPLACE TYPE address_ty AS OBJECT
(
name VARCHAR2(30),
street VARCHAR2(30),
city VARCHAR2(20),
county VARCHAR2(30),
postcode VARCHAR2(9)
)

6
VARRAY syntax

We could then create a VARRAY of (up to) 10
ship-to locations with the following command
CREATE OR REPLACE TYPE shiptos_va AS
VARRAY(10) of address_ty

7
VARRAY syntax

Finally, we could incorporate these 10 ship-to
locations in our customer table as it is created
CREATE TABLE customer (
name VARCHAR2(30),
billto address_ty,
shiptos shiptos_va,
...
)
So for each customer, we have one bill-to
address and (up to 10) ship-to addresses!

8
Inserting data into VARRAYs

You must use the VARRAY type name when
inserting data. For example, lets define a golf
game table
CREATE TABLE golfgame (
courseid VARCHAR2(10),
dateplayed DATE,
playerid VARCHAR2(10),
scores scores_va
)

9
Inserting data into VARRAYs

Now we can use the INSERT command to create a
row in our new table
INSERT INTO golfgame VALUES (
'Aviara', '05-FEB-2004, 'GPlayer',
scores_va(4,3,1,4,3,2,4,3,5,2,4,3,
2,4,3,4,2,3)
)

10
Inserting data into VARRAYs

What if we want to insert an array of ADTs?
(You know the answer)
INSERT INTO customer VALUES (...
shiptos_va(
address_ty('123 Main St.',...),
address_ty('444 First Ave',...),
address_ty('23 Skidoo Rd',...)
)
)

11
Inserting data into VARRAYs

Notice that we use the VARRAY type name, not the
column name, in the insert statement
We can specify less than the number of elements
in the VARRAY, optionally setting the remaining
elements to NULL
We can NOT specify more than the number of
elements in the VARRAY

12
Selecting data from VARRAYs

Individual entries in a VARRAY are accessible
within PL/SQL.
However, using only SQL commands, we are limited
to what we can do using using VARRAYs

13
Selecting data from VARRAYs

One very useful capability is using the TABLE
function within SQL to flatten a 3-dimensional
table into two dimensions
SELECT courseid, dateplayed,
playerid, n.
FROM golfgame,
TABLE(golfgame.scores) n

14
Selecting data from VARRAYs

Notice that we dont have a lot of flexibility in
what elements in the VARRAY are listed
However, we can still control which columns are
listed using the n. notation.

15
Data Analysis

VARRAYs can be very useful when using PL/SQL to
analyse and display aggregate data within an
array
For example, in our golf game, we could easily
use PL/SQL to analyse the best and worst 9 holes
of golf for each player much more easily than if
each score were held in a separate row

16
VARRAYS PL/SQL Example 1
DECLARE TYPE table_type IS VARRAY(5) OF
NUMBER(10) v_tab table_type v_idx
NUMBER BEGIN v_tab table_type(1, 2) FOR i
IN 3 .. 5 LOOP v_tab.extend v_tab(v_tab.last)
i END LOOP v_idx v_tab.FIRST ltlt
display_loop gtgt --you may name a loop WHILE
v_idx IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE('The
number ' v_tab(v_idx)) v_idx
v_tab.NEXT(v_idx) END LOOP display_loop END
17
VARRAYS Example 1 (Delete method)
DECLARE . . ltlt load_loop gtgt FOR i IN 1 .. 5
LOOP v_tab(i) i END LOOP load_loop --
Delete the third item of the collection.
v_tab.DELETE(3) . . END
18
VARRAYS Popular Methods
EXISTS(n) - Returns TRUE if the specified element
exists. COUNT - Returns the number of elements
in the collection. LIMIT - Returns the maximum
number of elements for a VARRAY FIRST - Returns
the index of the first element in the collection.
LAST - Returns the index of the last element in
the collection. PRIOR(n) - Returns the index of
the element prior to the specified element.
NEXT(n) - Returns the index of the next element
after the specified element. EXTEND - Appends a
single null element to the collection. EXTEND(n)
- Appends n null elements to the collection.
EXTEND(n1,n2) - Appends n1 copies of the n2th
element to the collection. TRIM - Removes a
single element from the end of the collection.
TRIM(n) - Removes n elements from the end of the
collection. DELETE - Removes all elements from
the collection. DELETE(n) - Removes element n
from the collection. DELETE(n1,n2) - Removes all
elements from n1 to n2 from the collection.
19
Nested Tables Review

A nested table is a PL/SQL table which is nested
as the value of a column in a row of a primary
table
There are no limits on the size of a nested
table, thereby supporting an infinite number of
repeating groups or values

20
Nested Table Syntax

The syntax for creating a nested table type is
CREATE OR REPLACE TYPE xxx_nt
AS TABLE OF (type)
where xxx_nt is the nested table type name, and
(type) can be either a supported or an abstract
data type.

21
Nested table example

Lets create an abstract data type to describe
patients of doctors in a hospital
CREATE OR REPLACE TYPE patient_ty AS OBJECT (
firstname VARCHAR2(20),
lastName VARCHAR2(20),
roomid NUMBER(4,0),
admitdate DATE,
diagnosis VARCHAR2(40)
)

22
Nested table example

We then create a type of nested table named
patient_nt consisting of any number of rows of
patients.
CREATE OR REPLACE TYPE patients_nt AS TABLE OF
patient_ty

23
Nested table example

We could then create a table of doctors, where
each doctors row contains his own set of
patients.
CREATE TABLE doctor (
doctorid VARCHAR2(5),
drlastname VARCHAR2(30),
specialty VARCHAR2(10),
patients patients_nt
)
NESTED TABLE patients STORE AS
patients_nt_table

24
Nested Table example

Notice that we have specified a new table name
for the nested table at the end of the CREATE
TABLE command
This is because the nested table is stored as
its own table, internally separate from the
original table.

25
Inserting data in Nested Tables

Just like other ADTs, inserting data into a
table containing nested tables is done using the
type name
INSERT INTO doctor VALUES (
'00001', 'Kildare', 'Cancer',
patients_nt(
patient_ty('Joe','Smith','433',
'02-Feb-04','Tumor'),
patient_ty(...),
)
)

26
Updating data in Nested Tables

You can update data in a nested table using the
TABLE command (using the column name, not type
name)
UPDATE TABLE(
SELECT patients
FROM doctor
WHERE doctorid '00001') d
SET d.admitdate '24-Feb-2004'
WHERE d.lastname 'Smith'
AND d.firstname 'Joe'

27
Inserting data in Nested Tables

You can insert data into an existing nested
table using the same technique
INSERT INTO TABLE(
SELECT patients
FROM doctor
WHERE doctorid '00001')
VALUES (
patient_ty('Jack',Jones',591',
'05-Feb-04',Sick Humor')
)

28
Deleting data in Nested Tables

Finally, you can delete data in a nested table
using the same structured command
DELETE TABLE(
SELECT patients
FROM doctor
WHERE doctorid '00001') d
WHERE d.lastname 'Smith'
AND d.firstname 'Joe'

29
Nested Tables

Unlike VARRAYs, nested table data is not stored
with the rest of the rows data, so scanning a
table with nested tables is more efficient than
scanning a table using with VARRAYs
The use of nested tables is very controversial
within the Oracle community
Can you think of how we could have used separate
tables (rather than nested tables) in our
example? What (dis)advantages would we have
doing this?

30
Nested Tables

Nested Tables could be used where aggregate
reporting is important, but the maximum number of
elements is not known or varies wildly
Good use for a Nested Table is where a separate
table doesnt make much sense, for example,
names, types, or selections that wont be
searched
dependents names for an employee
names of boats for an owner
colleges or universities attended for a student

31
Nested Table Example

DECLARE
TYPE table_type IS TABLE OF NUMBER(10)
v_tab table_type
v_idx NUMBER
BEGIN
v_tab table_type(1, 2)
ltlt load_loop gtgt
FOR i IN 3 .. 5 LOOP
v_tab.extend
v_tab(v_tab.last) i
END LOOP load_loop
v_tab.DELETE(3)
v_idx v_tab.FIRST
ltlt display_loop gtgt
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number '
v_tab(v_idx))
v_idx v_tab.NEXT(v_idx)
END LOOP display_loop
END

32
User Defined PL/SQL Records

Contains one or more components of any scalar,
record, or collection data types
Similar structure to records in 3GL
Not the same as roes in database table
Convenient for fetching a row of data from a
table for processing

33
Creating a PL/SQL Record

Type type_name is RECORD
(field1decleration, field 2 declaration ,..)
Where field declaration is
Field_name (field type tablerowtype)
not null defalut exp
Example
.
Type emp_record_type is record
(ename varchar2(10),
Job emp.jobtype,
Salary number(7,2) not null 1500)
emp_rec emp_record_type
.

34
Referencing, Initialising And Assigning Values TO
Records

Record_name.field_name
Ex emp_rec.job PROGRAMMER
An entire record can be assigned to another
record of the same type
Records cannot be compared, rather their field
can be
Selecting into a record
SELECT emp_id,dept,title,hire_date,college_recruit
INTO new_emp_rec FROM emp WHERE surname 'LI'

35
Cursor based Records

A record based on the cursor's SELECT statement.
Each field corresponds to a column or expression
in the cursor SELECT statement

36
Nested Records

Nesting records is a powerful way to normalize
data structures and hide complexity within PL/SQL
programs
DECLARE
-- Define a record.
TYPE phone_rectype IS RECORD (
area_code VARCHAR2(3),
exchange VARCHAR2(3),
phn_number VARCHAR2(4),
extension VARCHAR2(4))
-- Define a record composed of records.
TYPE contact_rectype IS RECORD (
day_phone phone_rectype,
eve_phone phone_rectype,
cell_phone phone_rectype)
-- Declare a variable for the nested record.
auth_rep_info_rec contact_rectype
..

37
Guidelines for using PL/SQL Records

Create corresponding cursors and records FETCH
into a record, rather than into individual
variables
Create table-based records create a new
table-based record (ROWTYPE) to store that data.
Whenever appropriate, pass records as parameters
instead of variables

38
Notes

Collections can not be directly compared
Collections cannot appear in a DISTINCT, GROUP
BY, or ORDER BY list.
Individual elements of collections can be
compared

39
Comparisons between Collection Elements

DECLARETYPE Clientele IS TABLE OF
VARCHAR2(64)group1 Clientele
Clientele('Customer 1', 'Customer 2') group2
Clientele Clientele('Customer 1', 'Customer
3') BEGIN -- Equality test causes
compilation error. IF group1(1) group2(1)
THEN dbms_output.put_line('It Is Equal')
ELSE dbms_output.put_line('It Not Is
Equal') END IFEND/

40
Global Example on Collection

CREATE OR REPLACE TYPE strings_nt IS TABLE OF
VARCHAR2(100) /
CREATE OR REPLACE PACKAGE favorites_pkg IS
my_favorites strings_nt strings_nt
('CHOCOLATE' , 'BRUSSEL SPROUTS' , 'SPIDER ROLL'
)
dad_favorites strings_nt strings_nt ('PICKLED
HERRING , 'POTATOES' , 'PASTRAMI' , 'CHOCOLATE'
) PROCEDURE show_favorites ( title_in IN
VARCHAR2 , favs_in IN strings_nt )
END /

41
Joining the two collection into One