PL/SQL – Member Function: Link directly to object without parameters

oracle-12cplsql

I want a table generated from a table function in oracle.

Like this:

+--------------------------------------------------------------------+
| CUSTOMERS                                                          |
+----+-------+------------+-------------+---------------+------------+
| ID | FIRST |    LAST    | LOCATION_ID | CREATION_DATE | FULL_YEARS |
+----+-------+------------+-------------+---------------+------------+
|  1 | Max   | Mustermann |          54 | 2017-10-05    |          0 |
|  2 | Hans  | Dimitry    |          87 | 2016-12-26    |          1 |
|  3 | Olga  | Trausch    |          71 | 2015-06-02    |          3 |
+----+-------+------------+-------------+---------------+------------+

But I want that the column FULL_YEARS is a member function of my object customer.

So I implement my customer object as

CREATE TYPE "CUSTOMER" AS OBJECT (
    customer_id           NUMBER,
    customer_firstname    VARCHAR2,
    customer_lastname     VARCHAR2,
    customer_location_id  NUMBER,
    creation_date         DATE,
    MEMBER FUNCTION "FULL_YEARS"(in_date DATE) RETURN NUMBER
);
/

My customer object body

CREATE OR REPLACE
TYPE BODY CUSTOMER AS

  MEMBER FUNCTION "FULL_YEARS"(in_date DATE) RETURN NUMBER AS
    v_years NUMBER;
  BEGIN
    SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, in_date)/12) INTO v_years
    FROM dual;
    RETURN v_years;
  END "FULL_YEARS";
END;
/

As example I declared an anonymous plsql block for testing.

DECLARE
  v_customer CUSTOMER;
BEGIN
  v_customer := CUSTOMER(2001, 'Hugo', 'McKinnock', 16, SYSDATE - INTERVAL '18' YEAR);
  DBMS_OUTPUT.PUT_LINE(v_customer.FULL_YEARS(v_customer.CREATION_DATE));
END;
/

But, how can I directly access in my member function of the column creation_date from my customer object. I don't want the parameter in_date of my member function.

I want only a call like this for example

 DBMS_OUTPUT.PUT_LINE(v_customer.FULL_YEARS());

Volker

Best Answer

Forget my comment. I was thinking of something else.

Your question is really about "How do I access the MEMBER attributes from within a MEMBER function?".

In this case, you use the keyword SELF as the current instance's name.

Spec

CREATE TYPE "CUSTOMER" AS OBJECT (
    customer_id           NUMBER,
    customer_firstname    VARCHAR2(30),
    customer_lastname     VARCHAR2(30),
    customer_location_id  NUMBER,
    creation_date         DATE,
    MEMBER FUNCTION "FULL_YEARS" RETURN NUMBER
);
/

Body

CREATE OR REPLACE
TYPE BODY CUSTOMER AS

  MEMBER FUNCTION "FULL_YEARS" RETURN NUMBER
  as
     v_years  NUMBER;
  BEGIN
    -- don't do the SELECT INTO FROM DUAL thing.  that is just ugly code.
    v_years := FLOOR(MONTHS_BETWEEN( sysdate, self.creation_date)/12);

    RETURN v_years;
  END "FULL_YEARS";
END;
/
Related Question