PL/SQL - 成員函式：直接連結到不帶參數的對象

我想要一個從 oracle 中的表函式生成的表。

像這樣：

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

但我希望該列FULL_YEARS是我的對象客戶的成員函式。

所以我將我的客戶對象實現為

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
);
/

我的客戶對象主體

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;
/

例如，我聲明了一個匿名 plsql 塊進行測試。

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;
/

creation_date但是，我怎樣才能從我的客戶對象直接訪問列的成員函式。我不想要in_date我的成員函式的參數。

例如，我只想要這樣的電話

DBMS_OUTPUT.PUT_LINE(v_customer.FULL_YEARS());

人們

忘記我的評論。我在想別的東西。

您的問題實際上是關於“如何MEMBER從MEMBER函式中訪問屬性？”。

在這種情況下，您使用關鍵字SELF作為目前實例的名稱。

規格

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
);
/

身體

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;
/

引用自：https://dba.stackexchange.com/questions/216719