PL/SQL by Example / Object

Object-oriented programming is not exclusive to higher-level languages; a very similar principle can be achieved in PL/SQL using the special OBJECT type. A class, or rather an object, is defined similarly to a package, i.e., with a specification and a body.

Constructor

The way an object and its internal values are initialized is determined by the constructor. Every object always has one default constructor where the number of input parameters equals the number of variables specified in the public part. However, it is of course possible to create constructors with a different number of parameters and custom logic.

CREATE OR REPLACE TYPE Employee_T AS OBJECT(
    FullName VARCHAR2(100)
  , Salary NUMBER

  , CONSTRUCTOR FUNCTION Employee_T
      RETURN SELF AS RESULT

  , CONSTRUCTOR FUNCTION Employee_T(
        p_FirstName VARCHAR2
      , p_LastName VARCHAR2
      , p_Salary NUMBER
    ) RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE BODY Employee_T AS

  CONSTRUCTOR FUNCTION Employee_T
    RETURN SELF AS RESULT
  IS
  BEGIN
    FullName:='N/A';
    Salary:=0;
    RETURN;
  END;

  CONSTRUCTOR FUNCTION Employee_T(
      p_FirstName VARCHAR2
    , p_LastName VARCHAR2
    , p_Salary NUMBER
  ) RETURN SELF AS RESULT
  IS
  BEGIN
    FullName:=p_FirstName||' '||p_LastName;
    Salary:=p_Salary;
    RETURN;
  END;

END;
/

Using all three constructor types in practice. Note the absence of the NEW keyword, which introduces instantiation in other languages.

DECLARE
  l_Employee Employee_T;
BEGIN
  -- using the parameterless constructor
  l_Employee:=Employee_T();
  dbms_output.put_line(l_Employee.FullName||', '||l_Employee.Salary);
  -- N/A, 0

  -- using the implicit constructor
  l_Employee:=Employee_T('Francis Sauce', 20000);
  dbms_output.put_line(l_Employee.FullName||', '||l_Employee.Salary);
  -- Francis Sauce, 20000

  -- using the constructor with three parameters
  l_Employee:=Employee_T('Peter', 'Novak', 30000);
  dbms_output.put_line(l_Employee.FullName||', '||l_Employee.Salary);
  -- Peter Novak, 30000
END;

Functions and Procedures

In addition to constructors, it is also possible to define functions and procedures within an object. These can be primarily used for manipulating the object’s attributes, as demonstrated by the following simple example.

CREATE OR REPLACE TYPE Employee_T AS OBJECT(
    FullName VARCHAR2(100)
  , Salary NUMBER

  , MEMBER FUNCTION mf_Display(
        p_Separator CHAR:=', '
    ) RETURN VARCHAR2

  , MEMBER PROCEDURE mp_Delete
);
/

CREATE OR REPLACE TYPE BODY Employee_T AS

  MEMBER FUNCTION mf_Display(
      p_Separator CHAR:=', '
  ) RETURN VARCHAR2
  IS
  BEGIN
    IF (FullName IS NOT NULL) THEN
      RETURN FullName||p_Separator||Salary;
    ELSE
      RETURN 'N/A';
    END IF;
  END;

  MEMBER PROCEDURE mp_Delete
  IS
  BEGIN
    FullName:=NULL;
    Salary:=NULL;
  END;

END;
/

Both attributes and the object’s functions and procedures are accessed using dot notation.

DECLARE
  l_Employee Employee_T;
BEGIN
  l_Employee:=Employee_T('Francis Sauce', 20000);
  dbms_output.put_line(l_Employee.mf_Print); -- Francis Sauce, 20000

  l_Employee.mp_Clear;
  dbms_output.put_line(l_Employee.mf_Print); -- N/A
END;