All geek questions in one place

How to bind member functions in a PL / SQL object

This may be a bad use case for what I am trying to accomplish, but I have read dozens of pages and I cannot figure out if this is possible in Oracle or not.

I would like to do something similar to another stackoverflow question: How to bind calls in an object of type pl / sql returning CAM

In my code, I have a type called parseName with a constructor function and 3-member functions: getFirstName, getMiddleName, getLastName. I would like to have a fourth member function: cleanString, which removes all characters not between AZ or az.

Call examples:

SELECT parseName('John123 Doe').getFirstName().cleanString()
FROM dual;

SELECT parseName('John123 Doe').getFirstName()
FROM dual;

Here is the type I wrote so far.

CREATE OR REPLACE TYPE parseName AS OBJECT
(
    g_name         VARCHAR2(255),
    g_parts        NUMBER,
    g_first_name   VARCHAR2(255),
    g_middle_name  VARCHAR2(1),
    g_last_name    VARCHAR2(255),
    -- constructor function
    CONSTRUCTOR FUNCTION parseName
      (p_name IN VARCHAR2)
      RETURN self AS result,
    -- member functions
    MEMBER FUNCTION getFirstName  RETURN VARCHAR2,
    MEMBER FUNCTION getMiddleName RETURN VARCHAR2,
    MEMBER FUNCTION getLastName   RETURN VARCHAR2
);
/
SHOW ERRORS
/

CREATE OR REPLACE TYPE BODY parseName IS

  -- populateValues
  CONSTRUCTOR FUNCTION parseName
    (p_name IN VARCHAR2)
  RETURN self AS result
  IS
    -- other variables
    v_name    VARCHAR2(255);
    v_length  NUMBER;
    v_parts   NUMBER;
    v_instr   NUMBER;
  BEGIN

    -- save off input
    v_name := TRIM(p_name);

    -- check
    IF v_name IS NULL THEN
      self.g_first_name  := 'Unknown';
      self.g_middle_name := ' ';
      self.g_last_name   := 'Unknown';
      RETURN;
    END IF;

    -- otherwise, fill our global
    self.g_name := v_name;

    -- exit
    RETURN;

  END;

  /* getFirstName */
  /* --------------------------------------- */
  MEMBER FUNCTION getFirstName
    RETURN VARCHAR2
  IS
    v_parts NUMBER;
  BEGIN

    -- did we get a null on construct?
    IF self.g_first_name IS NOT NULL THEN
      RETURN self.g_first_name;
    END IF;

    -- how many spaces do we have?
    v_parts := LENGTH(self.g_name) - LENGTH(REPLACE(self.g_name,' ',''));

    -- if 0 spaces, return the name
    IF v_parts = 0 THEN
      RETURN self.g_name;
    -- else, return everything up to the space
    ELSE
      RETURN TRIM(SUBSTR(self.g_name,1, INSTR(self.g_name,' ',1) ));
    END IF;

  END getFirstName;

  /* getMiddleName */
  /* --------------------------------------- */
  MEMBER FUNCTION getMiddleName
    RETURN VARCHAR2
  IS
    v_parts  NUMBER;
    v_instr2 NUMBER;
    v_instr1 NUMBER;
  BEGIN

    -- did we get a null on construct?
    IF self.g_middle_name IS NOT NULL THEN
      RETURN NULL;
    END IF;

    -- how many spaces do we have?
    v_parts := LENGTH(self.g_name) - LENGTH(REPLACE(self.g_name,' ',''));

    -- if we have zero spaces, we only have a first name, return null
    IF v_parts = 0 THEN
      RETURN NULL;
    -- don't do middle if we only have 1 space
    ELSIF v_parts = 1 THEN
      RETURN NULL;
    -- else, we've got more than one, so grab between space 1 and 2
    ELSE
      v_instr2 := INSTR(self.g_name,' ',1,2);
      v_instr1 := INSTR(self.g_name,' ',1,1);
      RETURN TRIM( SUBSTR(self.g_name, v_instr1, (v_instr2-v_instr1) ));
    END IF;

  END getMiddleName;

  /* getLastName */
  /* --------------------------------------- */
  MEMBER FUNCTION getLastName
    RETURN VARCHAR2
  IS
    v_parts  NUMBER;
  BEGIN

    -- did we get a null on construct?
    IF self.g_last_name IS NOT NULL THEN
      RETURN self.g_last_name;
    END IF;

    -- how many spaces do we have?
    v_parts := LENGTH(self.g_name) - LENGTH(REPLACE(self.g_name,' ',''));

    -- if we have zero spaces, we only have a first name, return 'Unknown'
    IF v_parts = 0 THEN
      RETURN 'Unknown';
    -- if have 1 space, the space on is the last name
    ELSIF v_parts = 1 THEN
      RETURN TRIM( SUBSTR(self.g_name, INSTR(self.g_name,' ',1,1), LENGTH(self.g_name)) );
    -- else, we've got more than one, go from 2 to end
    ELSE
      RETURN TRIM( SUBSTR(self.g_name, INSTR(self.g_name,' ',1,2), LENGTH(self.g_name)) );
    END IF;

  END getLastName;


END;
/
SHOW ERRORS
/

.

Thanks for any advice you can provide.

+4
source share
1 answer

-, : cleanString, A-Z a-z.

:

SELECT parseName('John123 Doe').getFirstName().cleanString()
FROM dual;

.getFirstName(), , ( , -), - VARCHAR2. VARCHAR2 , (- ) , .cleanString().

cleanString():

CREATE FUNCTION cleanString( value VARCHAR2 ) RETURN VARCHAR2
IS
BEGIN
  RETURN REGEXP_REPLACE( value, '[^[:alpha:]]+' );
END cleanString;
/

:

 SELECT cleanString( parseName('John123 Doe').getFirstName() )
 FROM   DUAL;

-, :

SQL Fiddle

Oracle 11g R2:

CREATE OR REPLACE TYPE parseName AS OBJECT
(
    g_name         VARCHAR2(255),
    g_first_name   VARCHAR2(255),
    g_middle_name  VARCHAR2(1),
    g_last_name    VARCHAR2(255),
    -- constructor function
    CONSTRUCTOR FUNCTION parseName(p_name IN VARCHAR2) RETURN self AS result,
    -- member functions
    MEMBER FUNCTION cleanNames    RETURN parseName,
    MEMBER FUNCTION getFirstName  RETURN VARCHAR2,
    MEMBER FUNCTION getMiddleName RETURN VARCHAR2,
    MEMBER FUNCTION getLastName   RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY parseName IS
  -- populateValues
  CONSTRUCTOR FUNCTION parseName(p_name IN VARCHAR2) RETURN self AS result
  IS
  BEGIN
    g_name := TRIM(p_name);
    g_first_name  := REGEXP_SUBSTR( g_name, '^(\S+)\s+((\S*?)\s+)?(.*)$', 1, 1, NULL, 1 );
    g_middle_name := REGEXP_SUBSTR( g_name, '^(\S+)\s+((\S*?)\s+)?(.*)$', 1, 1, NULL, 3 );
    g_last_name   := REGEXP_SUBSTR( g_name, '^(\S+)\s+((\S*?)\s+)?(.*)$', 1, 1, NULL, 4 );
    RETURN;
  END;

  MEMBER FUNCTION cleanNames RETURN parseName
  IS
    v_name parseName := SELF;
  BEGIN
    v_name.g_first_name  := cleanString( SELF.g_first_name );
    v_name.g_middle_name := cleanString( SELF.g_middle_name );
    v_name.g_last_name   := cleanString( SELF.g_last_name );
    RETURN v_name;
  END;

  MEMBER FUNCTION getFirstName RETURN VARCHAR2
  IS
  BEGIN
    RETURN g_first_name;
  END getFirstName;

  MEMBER FUNCTION getMiddleName RETURN VARCHAR2
  IS
  BEGIN
    RETURN g_middle_name;
  END getMiddleName;

  MEMBER FUNCTION getLastName RETURN VARCHAR2
  IS
  BEGIN
    RETURN g_last_name;
  END getLastName;
END;
/

1:

SELECT parseName('John123 Doe').getFirstName(),
       parseName('John123 Doe').cleanNames().getFirstName()
FROM DUAL

:

| PARSENAME('JOHN123DOE').GETFIRSTNAME() | PARSENAME('JOHN123DOE').CLEANNAMES().GETFIRSTNAME() |
|----------------------------------------|-----------------------------------------------------|
|                                John123 |                                                John |
+4

Source: https://habr.com/ru/post/1695818/

More articles:


All Articles