|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
METHODS
in general can be defined with the objects to perform operations on
the object attributes. METHODS can be functions or procedures but can
only be DETERMINISTIC meaning they need to be Write No Database State
(WNDS). By default oracle provides the MAP and ORDER methods for
comparisons and ordering.
Using MAP methods :
MAP methods defined with
the objects is used to return scalar value to enable comparisons
between objects and for ordering (order by and distinct clauses). The
function is invoked implicitly by oracle whenever it encounters an
order by or distinct clauses.
Test Case :
create or replace type test_type as object(owner
varchar2(30),object_id number,
MEMBER function get_ow_objid return varchar2,
--
Used to perform operation on object attributes
pragma restrict_references(get_ow_objid,WNDS),
--
Used for DISTINCT
clause in this test case
MAP
member function distinct_owner return varchar2,
pragma restrict_references(distinct_owner,WNDS,RNDS,WNPS,RNPS)
);
/
create or replace type body test_type as
member function get_ow_objid return varchar2 is
--
Action is performed on the attributes
begin
return owner||' '||object_id;
end;
MAP
member function distinct_owner return varchar2 is
--
Test case is on
returning DISTINCT owner
begin
return owner;
end;
end;
/
create or replace type test_objtab as table of test_type;
/
An Example PL/SQL code
that uses these methods :
declare
local_array test_objtab;
begin
--
The DISTINCT clause automatically invokes the MAP method to order on
owner
select
distinct
test_type(owner,object_id) bulk collect into local_array from
dba_objects;
for i
in local_array.first.. local_array.last
loop
--
local_array(i).get_ow_objid invokes the method to perform a certain
operation on the attributes.
insert into test_table values (local_array(i).owner,local_array(i).object_id,local_array(i).get_ow_objid);
end
loop;
commit;
end;
/ |
