SQL> CREATE OR REPLACE FUNCTION find_gcd (
  2      p_n1    IN  POSITIVE
  3  ,   p_n2    IN  POSITIVE
  4  )
  5  RETURN POSITIVE
  6  IS
  7      l_n1    POSITIVE := p_n1;
  8      l_n2    POSITIVE := p_n2;
  9  BEGIN
 10      WHILE NOT (l_n1 = l_n2)
 11      LOOP
 12          CASE SIGN(l_n1 - l_n2)
 13          WHEN +1
 14          THEN l_n1 := l_n1 - l_n2;
 15          ELSE l_n2 := l_n2 - l_n1;
 16          END CASE;
 17      END LOOP;
 18      RETURN (l_n1);
 19  END find_gcd;
 20  /
 
Function created.
 
SQL> SELECT find_gcd(find_gcd(10,15),25) gcd
  2  FROM   DUAL
  3  /
 
       GCD
----------
         5
 
SQL>

SQL> CREATE OR REPLACE TYPE gcd_agg AS OBJECT (
  2      factor  NUMBER
  3  ,   STATIC FUNCTION odciaggregateinitialize (
  4                          sctx IN OUT gcd_agg)
  5      RETURN NUMBER
  6  ,   MEMBER FUNCTION odciaggregateiterate (
  7                          SELF    IN OUT  gcd_agg
  8                      ,   value   IN      NUMBER)
  9      RETURN NUMBER
 10  ,   MEMBER FUNCTION odciaggregateterminate (
 11                          SELF        IN  gcd_agg
 12                      ,   returnvalue OUT NUMBER
 13                      ,   flags       IN  NUMBER)
 14      RETURN NUMBER
 15  ,   MEMBER FUNCTION odciaggregatemerge (
 16                          SELF    IN OUT  gcd_agg
 17                      ,   ctx2    IN      gcd_agg)
 18      RETURN NUMBER
 19  );
 20  /
 
Type created.
 
SQL> CREATE OR REPLACE TYPE BODY gcd_agg AS
  2      STATIC FUNCTION odciaggregateinitialize (
  3                              sctx IN OUT gcd_agg)
  4      RETURN NUMBER
  5      IS
  6      BEGIN
  7          sctx := gcd_agg(TO_NUMBER(NULL));
  8          RETURN (odciconst.success);
  9      END odciaggregateinitialize;
 10
 11      MEMBER FUNCTION odciaggregateiterate (
 12                          SELF    IN OUT  gcd_agg
 13                      ,   value   IN      NUMBER)
 14      RETURN NUMBER
 15      IS
 16          l_value         POSITIVE;
 17          l_return_value  NUMBER;
 18      BEGIN
 19          BEGIN
 20              l_value := ABS(TRUNC(value));
 21              IF (SELF.factor IS NULL
 22              OR SELF.factor <= 0) THEN
 23                  SELF.factor := l_value;
 24              ELSE
 25                  WHILE NOT (SELF.factor = l_value)
 26                  LOOP
 27                      CASE SIGN(SELF.factor - l_value)
 28                      WHEN +1
 29                      THEN SELF.factor := SELF.factor - l_value;
 30                      ELSE l_value := l_value - SELF.factor;
 31                      END CASE;
 32                  END LOOP;
 33              END IF;
 34              l_return_value := odciconst.success;
 35          EXCEPTION
 36              WHEN VALUE_ERROR THEN
 37                  l_return_value := odciconst.error;
 38          END;
 39          RETURN (l_return_value);
 40      END odciaggregateiterate;
 41
 42      MEMBER FUNCTION odciaggregateterminate (
 43                          SELF        IN  gcd_agg
 44                      ,   returnvalue OUT NUMBER
 45                      ,   flags       IN  NUMBER)
 46      RETURN NUMBER
 47      IS
 48      BEGIN
 49          returnvalue := SELF.factor;
 50          RETURN (odciconst.success);
 51      END odciaggregateterminate;
 52
 53      MEMBER FUNCTION odciaggregatemerge (
 54                          SELF    IN OUT  gcd_agg
 55                      ,   ctx2    IN      gcd_agg)
 56      RETURN NUMBER
 57      IS
 58          l_value_1   POSITIVE := SELF.factor;
 59          l_value_2   POSITIVE := ctx2.factor;
 60      BEGIN
 61          IF (l_value_1 IS NULL
 62          OR l_value_1 <= 0) THEN
 63              l_value_1 := l_value_2;
 64          ELSE
 65              WHILE NOT (l_value_1 = l_value_2)
 66              LOOP
 67                  CASE SIGN(l_value_1 - l_value_2)
 68                  WHEN +1
 69                  THEN l_value_1 := l_value_1 - l_value_2;
 70                  ELSE l_value_2 := l_value_2 - l_value_1;
 71                  END CASE;
 72              END LOOP;
 73          END IF;
 74          RETURN (odciconst.success);
 75      END odciaggregatemerge;
 76  END;
 77  /
 
Type body created.
 
SQL> CREATE OR REPLACE FUNCTION gcd (input NUMBER)
  2  RETURN NUMBER
  3  PARALLEL_ENABLE AGGREGATE USING gcd_agg;
  4  /
 
Function created.
 
SQL> CREATE TABLE t (id VARCHAR2(2), n INT)
  2  /
 
Table created.
 
SQL> INSERT INTO t VALUES ('A',25);
SQL> INSERT INTO t VALUES ('A',30);
SQL> INSERT INTO t VALUES ('A',55);
SQL> INSERT INTO t VALUES ('B',77);
SQL> INSERT INTO t VALUES ('C',7176);
SQL> INSERT INTO t VALUES ('C',5428);
SQL> INSERT INTO t VALUES ('C',7820);
 
SQL> SELECT id, gcd(t.n) FROM t GROUP BY id
  2  /
 
ID   GCD(T.N)
-- ----------
A           5
B          77
C          92
 
SQL>