Can procedures, cursors and triggers be used within user-defined types with object-relational databases?

Question

I am new to object-relational databases, currently teaching myself from a textbook.

When creating user-defined types in Oracle, with tables of UDT objects, is it possible to use procedures, cursors, triggers etc. or are these functions only usable with relational databases?

I have spent time searching online to find this answer myself but I'm at a point where I have read so much on the topic it has me confused, so I thought it could be answered simply here (hopefully).

Answer 1

When creating user-defined types in Oracle, with tables of UDT objects, is it possible to use procedures, cursors, triggers etc.

Yes.

or are these functions only usable with relational databases?

No.

---

CREATE TYPE person IS OBJECT(
  id NUMBER,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  mother REF PERSON,
  MEMBER FUNCTION full_name( self IN PERSON )
    RETURN VARCHAR2,
  MEMBER FUNCTION children (self IN PERSON )
    RETURN SYS_REFCURSOR
);

CREATE TABLE people OF person (
  ID PRIMARY KEY
);

CREATE TYPE BODY person IS
  MEMBER FUNCTION full_name( self IN PERSON )
    RETURN VARCHAR2
  IS
  BEGIN
    RETURN self.first_name || ' ' || self.last_name;
  END;

  MEMBER FUNCTION children( self IN PERSON )
    RETURN SYS_REFCURSOR
  IS
    p_cursor SYS_REFCURSOR;
  BEGIN
    OPEN p_cursor FOR
    SELECT VALUE(p) AS child
    FROM   people p
    WHERE  p.mother.id = self.id;

    RETURN p_cursor;
  END;
END;
/

CREATE SEQUENCE people__id__seq;

CREATE TRIGGER people__new__trg
BEFORE INSERT ON people
FOR EACH ROW
  WHEN (new.ID IS NULL)
BEGIN
  :new.ID := PEOPLE__ID__SEQ.NEXTVAL;
END;
/

INSERT INTO people ( first_name, last_name )
SELECT 'Alice', 'Abbot' FROM DUAL UNION ALL
SELECT 'Belle', 'Burns' FROM DUAL UNION ALL
SELECT 'Carol', 'Charles' FROM DUAL;

UPDATE people
SET mother = ( SELECT REF(p) FROM people p WHERE id=2 )
WHERE id = 3;

Then:

SELECT id,
       first_name,
       last_name, 
       p.mother.id
FROM   people p;

Outputs:

ID | FIRST_NAME | LAST_NAME | MOTHER.ID
-: | :--------- | :-------- | --------:
 1 | Alice      | Abbot     |      null
 2 | Belle      | Burns     |      null
 3 | Carol      | Charles   |         2

Showing that the trigger has worked, generating ID values for the rows.

and

DECLARE
  p_person PERSON;
  p_child  PERSON;
  p_cursor SYS_REFCURSOR;
BEGIN
  SELECT VALUE( p )
  INTO   p_person
  FROM   people p
  WHERE  id = 2;

  p_cursor := p_person.children();
  DBMS_OUTPUT.PUT_LINE( 'Children of ' || p_person.full_name );

  LOOP
    FETCH p_cursor INTO p_child;
    EXIT WHEN p_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( p_child.full_name );
  END LOOP;

  CLOSE p_cursor;
END;
/

Outputs:

Children of Belle Burns
Carol Charles

Showing an example of a cursor from a function. Procedures are similarly possible and are left as an example for the OP.

db<>fiddle here