Object methods

1
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

2
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_ac
count_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

3
Object methods

• Method body

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 SQLgt / PAYMENT_AMOUNT --------------
2016

SQLgt select p.payment_amount from paycheck_o
p PAYMENT_AMOUNT -------------- 1816
UPDATE paycheck_o p SET payment_amount
p.adjust_amount(4596854,200) WHERE check_number
4596854

• Altering a type
• recompiling alter type lttype-namegt compile
  specbody
• adding new methods
• alter type lttype-namegt replace as object
  (ltobject-specgt)
• give an exact specification of the data type as
  original
• add new methods

4
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_ac
count_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 SQLgt / Type body created.
STEP 3 compile body SQLgt alter type
paycheck_type compile body Type body altered.