【PL/SQL】OBJECT TYPEとメンバーメソッドの使い方｜MAP・継承・ネスト表まで

CREATE OR REPLACE TYPE customer_obj AS OBJECT (
  cust_id   NUMBER,
  cust_name VARCHAR2(100),
  email     VARCHAR2(200),

  MEMBER PROCEDURE show_info,
  MEMBER FUNCTION domain RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY customer_obj AS
  MEMBER PROCEDURE show_info IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(
      'ID=' || SELF.cust_id ||
      ', NAME=' || SELF.cust_name ||
      ', EMAIL=' || SELF.email
    );
  END show_info;

  MEMBER FUNCTION domain RETURN VARCHAR2 IS
  BEGIN
    IF SELF.email IS NULL OR INSTR(SELF.email, '@') = 0 THEN
      RETURN NULL;
    END IF;

    RETURN SUBSTR(SELF.email, INSTR(SELF.email, '@') + 1);
  END domain;
END;
/

DECLARE
  l_customer customer_obj;
BEGIN
  l_customer := customer_obj(
                  101,
                  'Sato Taro',
                  'taro@example.com'
                );

  l_customer.show_info();
  DBMS_OUTPUT.PUT_LINE('domain=' || l_customer.domain());
END;
/

CREATE OR REPLACE TYPE customer_obj AS OBJECT (
  cust_id   NUMBER,
  cust_name VARCHAR2(100),
  email     VARCHAR2(200),

  CONSTRUCTOR FUNCTION customer_obj(
    p_cust_id   IN NUMBER,
    p_cust_name IN VARCHAR2
  ) RETURN SELF AS RESULT,

  MEMBER FUNCTION domain RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY customer_obj AS
  CONSTRUCTOR FUNCTION customer_obj(
    p_cust_id   IN NUMBER,
    p_cust_name IN VARCHAR2
  ) RETURN SELF AS RESULT
  IS
  BEGIN
    SELF.cust_id := p_cust_id;
    SELF.cust_name := p_cust_name;
    SELF.email := NULL;
    RETURN;
  END;

  MEMBER FUNCTION domain RETURN VARCHAR2 IS
  BEGIN
    IF SELF.email IS NULL OR INSTR(SELF.email, '@') = 0 THEN
      RETURN NULL;
    END IF;
    RETURN SUBSTR(SELF.email, INSTR(SELF.email, '@') + 1);
  END;
END;
/

CREATE OR REPLACE TYPE customer_tab AS TABLE OF customer_obj;
/

DECLARE
  l_customers customer_tab := customer_tab();
BEGIN
  l_customers.EXTEND(2);
  l_customers(1) := customer_obj(1, 'Tanaka Hanako', 'hanako@example.com');
  l_customers(2) := customer_obj(2, 'Yamada Jiro', 'jiro@example.com');

  FOR i IN 1 .. l_customers.COUNT LOOP
    l_customers(i).show_info();
  END LOOP;
END;
/

CREATE TABLE order_master (
  order_id NUMBER PRIMARY KEY,
  customer customer_obj
);

INSERT INTO order_master(order_id, customer)
VALUES (
  1,
  customer_obj(100, 'Takahashi Ichiro', 'ichiro@example.com')
);

SELECT o.order_id,
       o.customer.cust_id,
       o.customer.cust_name,
       o.customer.domain() AS email_domain
FROM order_master o;

CREATE OR REPLACE TYPE amount_obj AS OBJECT (
  currency_code VARCHAR2(3),
  amount        NUMBER,

  MAP MEMBER FUNCTION sort_key RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY amount_obj AS
  MAP MEMBER FUNCTION sort_key RETURN NUMBER IS
  BEGIN
    RETURN NVL(SELF.amount, 0);
  END;
END;
/

CREATE OR REPLACE TYPE amount_tab AS TABLE OF amount_obj;
/

SELECT VALUE(t).currency_code,
       VALUE(t).amount
FROM TABLE(amount_tab(
       amount_obj('JPY', 1200),
       amount_obj('USD', 10),
       amount_obj('EUR', 8)
     )) t
ORDER BY VALUE(t);

CREATE OR REPLACE TYPE base_customer AS OBJECT (
  cust_id   NUMBER,
  cust_name VARCHAR2(100),

  MEMBER FUNCTION get_info RETURN VARCHAR2
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY base_customer AS
  MEMBER FUNCTION get_info RETURN VARCHAR2 IS
  BEGIN
    RETURN 'customer=' || SELF.cust_name;
  END;
END;
/

CREATE OR REPLACE TYPE corporate_customer UNDER base_customer (
  company_name VARCHAR2(200),

  OVERRIDING MEMBER FUNCTION get_info RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY corporate_customer AS
  OVERRIDING MEMBER FUNCTION get_info RETURN VARCHAR2 IS
  BEGIN
    RETURN 'company=' || SELF.company_name || ', contact=' || SELF.cust_name;
  END;
END;
/

CREATE OR REPLACE TYPE customer_json_obj AS OBJECT (
  cust_id   NUMBER,
  cust_name VARCHAR2(100),
  email     VARCHAR2(200),

  MEMBER FUNCTION to_json RETURN CLOB
);
/

CREATE OR REPLACE TYPE BODY customer_json_obj AS
  MEMBER FUNCTION to_json RETURN CLOB IS
    l_json CLOB;
  BEGIN
    SELECT JSON_OBJECT(
             'custId' VALUE SELF.cust_id,
             'name'   VALUE SELF.cust_name,
             'email'  VALUE SELF.email
             RETURNING CLOB
           )
    INTO l_json
    FROM dual;

    RETURN l_json;
  END;
END;
/

CREATE OR REPLACE TYPE sales_row_obj AS OBJECT (
  customer_id NUMBER,
  total_amt   NUMBER
);
/

CREATE OR REPLACE TYPE sales_row_tab AS TABLE OF sales_row_obj;
/

CREATE OR REPLACE FUNCTION get_sales_rows
RETURN sales_row_tab PIPELINED
IS
BEGIN
  FOR r IN (
    SELECT customer_id, SUM(amount) total_amt
    FROM sales
    GROUP BY customer_id
  ) LOOP
    PIPE ROW (sales_row_obj(r.customer_id, r.total_amt));
  END LOOP;

  RETURN;
END;
/

SELECT *
FROM TABLE(get_sales_rows());

SELECT type_name, typecode, attributes, methods, final, instantiable
FROM user_types
WHERE type_name IN ('CUSTOMER_OBJ', 'CUSTOMER_TAB');

SELECT type_name, attr_name, attr_type_name, length, precision, scale
FROM user_type_attrs
WHERE type_name = 'CUSTOMER_OBJ'
ORDER BY attr_no;

SELECT type_name, method_name, method_type, final, instantiable
FROM user_type_methods
WHERE type_name = 'CUSTOMER_OBJ'
ORDER BY method_no;

SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = 'CUSTOMER_OBJ'
ORDER BY type, name;

SELECT name, type, line, position, text
FROM user_errors
WHERE name = 'CUSTOMER_OBJ'
ORDER BY sequence;