1 / 4

Understanding PL/SQL Object Methods and Procedures for Object Types

This guide explores the functionalities of object methods and user-defined procedures in PL/SQL, emphasizing how to define and manipulate object types with member functions. It covers constructing methods, such as constructor methods invoked during insertion, and the significance of pragma directives for managing database state references. Learn to create, alter, and compile object types with practical examples, specifically focusing on implementation details for adjusting payment amounts. Enhance your PL/SQL skills with clear steps for method definition and usage in SQL statements.

dick
Télécharger la présentation

Understanding PL/SQL Object Methods and Procedures for Object Types

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related