Transcript No Slide Title

Object methods
• PL/SQL functions/procedures that can operate on the attributes of an
object type: member functions defined inside the type definition
• constructor method: create automatically
• used in insertion statements
• insert into slsrep_o values (3, name_ty(..)…etc)
• has the same name as the object type
• has a parameter for each attribute of the object type
• User-defined methods
• two step method of creation
• create object type definition including method specification
• create object type body which includes the method code
Example
• Method type definition
CREATE or replace TYPE paycheck_type1 AS OBJECT (
check_number NUMBER(20),
payer VARCHAR2(40),
payee VARCHAR2(40),
date_issued DATE,
payment_authorization VARCHAR2(20),
payer_account_number VARCHAR2(20),
routing_number VARCHAR2(20),
payment_amount NUMBER(10),
MEMBER FUNCTION adjust_amount (check_number IN NUMBER,
deduct_amount IN NUMBER) RETURN NUMBER,
PRAGMA RESTRICT_REFERENCES (adjust_amount, RNDS,WNDS,RNPS,WNPS));
• Pragma: compiler directive that serve as instructions to the PL/SQL
compiler
• Restrict-references pragma: restricts the types of references depending
on where it is used
• WNDS (writes no database state): cannot modify database tables-applies to any function called in a SQL statement
• WNPS (writes no package state): does not modify any packaged variables
applies to any function called from SELECT, VALUES, SET clauses
• RNPS (reads no package state): does not examine any package variable-applies to remote and parallel functions
• RNDS (reads no database states): does not read any database table
• Method body
Object methods
CREATE or replace TYPE BODY paycheck_type1 AS MEMBER FUNCTION adjust_amount
( check_number in NUMBER,
deduct_amount in NUMBER
) RETURN NUMBER IS
BEGIN
RETURN payment_amount - deduct_amount;
END; /* first end for member function end */
END;
• Calling a method
1* select p.payment_amount from paycheck_o p
SQL> /
PAYMENT_AMOUNT
-------------2016
UPDATE paycheck_o p
SET payment_amount = p.adjust_amount(4596854,200)
WHERE check_number = 4596854
SQL> select p.payment_amount from paycheck_o p;
PAYMENT_AMOUNT
-------------1816
• Altering a type
• recompiling: alter type <type-name> compile [spec|body]
• adding new methods
• alter type <type-name> replace as object (<object-spec>)
• give an exact specification of the data type as original
• add new methods
Example
STEP 1: alter the type by adding method spec.
ALTER TYPE paycheck_type REPLACE AS OBJECT (
check_number NUMBER(20),
payer VARCHAR2(40),
payee VARCHAR2(40),
date_issued DATE,
payment_authorization VARCHAR2(20),
payer_account_number VARCHAR2(20),
routing_number VARCHAR2(20),
payment_amount NUMBER(10),
MEMBER FUNCTION adjust_amount (check_number IN NUMBER,
deduct_amount IN NUMBER) RETURN NUMBER,
PRAGMA RESTRICT_REFERENCES (adjust_amount, RNDS,WNDS,RNPS,WNPS))
/
STEP 2: create method body
. 1 CREATE or replace TYPE BODY paycheck_type AS MEMBER FUNCTION adjust_amount
2 ( check_number in NUMBER,
3 deduct_amount in NUMBER
4 ) RETURN NUMBER IS
5 BEGIN
6 RETURN payment_amount - deduct_amount;
7 END; /* first end for member function end */
8* END;
SQL> /
Type body created.
STEP 3: compile body
SQL> alter type paycheck_type compile body;
Type body altered.