 |
|
Object
Types in PL/SQL
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
Oracle implements Objects through the use of
TYPEs, defined in a similar way to packages. Unlike packages in
which the instance of the package is limited to the current session,
an instance of an object type can be stored in the database for later
use.
The definition of the type contains a comma
separated list of attributes/properties, defined in the same way as
package variables, and member functions/procedures. If a
type contains member functions/procedures, the procedural work for
these elements is defined in the TYPE BODY.
To see how objects can be used, suppose an
object is created to represent a person. In this case, a person
is defined by three attributes: first_name, last_name and
date_of_birth. Returning the age of the person is also desired,
so this is included as a member function, get_age.
CREATE OR
REPLACE TYPE t_person AS OBJECT (
first_name VARCHAR2(30),
last_name VARCHAR2(30),
date_of_birth DATE,
MEMBER FUNCTION get_age RETURN NUMBER
);
/
Type
created.
Next the type body is created to implement the
get_age member function.
CREATE OR
REPLACE TYPE BODY t_person AS
MEMBER FUNCTION get_age RETURN NUMBER AS
BEGIN
RETURN tRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12);
END get_age;
END;
/
Type body
created.
Once the object is defined, it can be used to
define a column in a database table.
CREATE TABLE
people (
id NUMBER(10) NOT NULL,
person t_person
);
Table
created.
To insert data into the PEOPLE table, the
t_person() constructor must be used. This can be done as part of a
regular DML statement or using PL/SQL.
INSERT INTO
people (id, person)
VALUES (1, t_person('John', 'Doe', TO_DATE('01/01/2000','DD/MM/YYYY')));
1 row
created.
COMMIT;
Commit
complete.
DECLARE
l_person t_person;
BEGIN
l_person := t_person('Jane','Doe', TO_DATE('01/01/2001','DD/MM/YYYY'));
INSERT INTO people (id, person)
VALUES (2, l_person);
COMMIT;
END;
/
PL/SQL
procedure successfully completed.
Once the data is loaded, it can be queried
using the dot notation:
-
alias.column.attibute
-
alias.column.function()
The query below shows this in action.
SELECT p.id,
p.person.first_name,
p.person.get_age() AS age
FROM people p;
ID PERSON.FIRST_NAME
AGE
---------- ------------------------------ ----------
1 John
5
2 Jane
4
2 rows
selected.
Collections in PL/SQL
Oracle uses collections in PL/SQL the same way
other languages use arrays. There are three basic collections types,
each using their own methods.
Associative Arrays in PL/SQL (Index-By
Tables)
Associative Arrays have no upper bounds
allowing them to constantly extend. Originally the collection
could only be indexed by a BINARY_INTEGER, although VARCHAR2 indexes
were introduced in Oracle 9.2. Regardless of the index type, the index
values do not need to be consecutive. The collection is extended
by assigning values to an element using an index value that does not
currently exist. The code below gives a brief example of how an
associative array might be manipulated.
SET
SERVEROUTPUT ON
DECLARE
TYPE t_collection IS TABLE OF NUMBER(10)
INDEX BY BINARY_INTEGER;
l_coll t_collection;
l_idx NUMBER;
BEGIN
-- Initialise the collection.
<< load_loop >>
FOR i IN 1 .. 5 LOOP
l_coll(i) := i;
END LOOP load_loop;
--
Delete the third item of the collection.
l_coll.DELETE(3);
--
Traverse sparse collection
l_idx := l_coll.FIRST;
<< display_loop >>
WHILE l_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number ' || l_coll(l_idx));
l_idx := l_coll.NEXT(l_idx);
END LOOP display_loop;
END;
/
The number 1
The number 2
The number 4
The number 5
PL/SQL
procedure successfully completed.
SQL>
Nested Table Collections in PL/SQL
Unlike associative arrays, nested table
collections do not have an index value and can be stored in a database
column. In addition, some DML operations are possible on nested
tables when they are stored in the database.
During creation the collection must be dense,
having consecutive subscripts for the elements. Once created,
elements can be deleted using the DELETE method to make the collection
sparse. The NEXT method overcomes the problems of traversing
sparse collections. The following code is a repeat of the
previous example but coded to use a table collection.
SET
SERVEROUTPUT ON SIZE 1000000
DECLARE
TYPE t_collection IS TABLE OF NUMBER(10);
l_coll t_collection;
l_idx NUMBER;
BEGIN
--
Initialise the collection with two values.
l_coll := t_collection(1, 2);
--
Extend the collection with extra values.
<< load_loop >>
FOR i IN 3 .. 5 LOOP
l_coll.extend;
l_coll(l_coll.last) := i;
END LOOP load_loop;
--
Delete the third item of the collection.
l_coll.DELETE(3);
--
Traverse sparse collection
l_idx := l_coll.FIRST;
<< display_loop >>
WHILE l_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number ' || l_coll(l_idx));
l_idx := l_coll.NEXT(l_idx);
END LOOP display_loop;
END;
/
The number 1
The number 2
The number 4
The number 5
PL/SQL
procedure successfully completed.
SQL>
|
