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_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

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

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_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.