Design Pattern are great! Not just in Java or C#

The Template Method Pattern can also be implementet with Oracle’s PL/SQL language (using clean Object Types). 🔗

The Template Method Design Patterns is probalby one of the most widely used and useful design pattern. common example of this is when writing a program that will handle data using various detail algorithms. The abstract Class would have a method called the “Template” Method which contains the main program logic. There also exists “helper method"s in the class, which are specified by any class that inherits from it. So the abstract class defines the main logic. The subclasses itself implement the details logic. So every developer can implement his specific details without changing the main logic.

|

|

In PL/SQL this design pattern can be implementet the procedural way as been done by Lucas Jellema (Amis.nl) Here I created a PL/SQL sample with Object Types:

Template Method Pattern implemented in PL/SQL 🔗

Der Template Method Pattern ist ein Designmuster, das auch in der Oracle PL/SQL-Sprache implementiert werden kann. Es handelt sich um eines der am weitesten verbreiteten und nützlichsten Designmuster. Ein häufiges Beispiel hierfür ist die Erstellung eines Programms, das Daten mit verschiedenen Detailalgorithmen verarbeitet. Die abstrakte Klasse enthält eine Methode namens Template Method, die die Hauptprogrammlogik enthält. Es gibt auch Hilfsmethoden, die von allen spezifiziert sind, die von ihr erben. Die abstrakte Klasse definiert also die Hauptlogik, während die Unterklassen die Detaillogik implementieren.

In PL/SQL kann dieses Designmuster auf prozedurale Weise implementiert werden, wie es Lucas Jellema auf Amis.nl getan hat.

drop type adress_o;
drop type adress_abstract_o;
more adress_abstract_o;

create or replace type adress_abstract_o as object (
    PersId number,
    member procedure setPersId(pPersId in number),
    member function getPersId return number,
    member function getMainResult return number,
    member function doSomethingA return number,
    member function doSomethingB return number
) not final not instantiable;

create or replace type body adress_abstract_o as
    -- Setters & Getters
    member procedure setPersId(pPersId in number) is
    begin
        self.PersId := pPersId;
    end;
    
    member function getPersId return number is
    begin
        return self.PersId;
    end;
    
    -- Main Template Method
    member function getMainResult return number is
    begin
        return doSomethingA + doSomethingB;
    end;
    
    -- Private methods supporting the template method
    -- can be overridden by subclass methods
    member function doSomethingA return number is
    begin
        return 0;
    end;
    
    member function doSomethingB return number is
    begin
        return 0;
    end;
end;
/

create or replace type adress_o under adress_abstract_o
(OVERRIDING member function doSomethingA return number,
OVERRIDING member function doSomethingB return number
) final instantiable;

create or replace type body adress_o as
    -- Private methods overriding the adress_abstract_o
    OVERRIDING member function doSomethingA return number is
    begin
        return 0;
    end;
    
    OVERRIDING member function doSomethingB return number is
    begin
        return 0;
    end;
end;

-- Testcase on adress_o
declare
    a adress_o;
    p number;
begin
    a := adress_o();
    dbms_output.put_line('PersId: ' || a.getPersId);
    dbms_output.put_line('Adresse: ' || a.getMainResult);
end;

Im Jahr 2024 ist das Template Method Pattern immer noch ein grundlegendes Designmuster, das in der Oracle PL/SQL-Programmierung verwendet werden kann. Es ermöglicht eine klare Trennung zwischen Hauptprogrammlogik und Detailimplementierungen in Unterklassen. Durch die Verwendung von Objekttypen können Entwickler ihr spezifisches Detailverhalten implementieren, ohne die Hauptlogik ändern zu müssen.

Die Implementierung des Template Method Patterns in PL/SQL bietet nach wie vor eine effektive Möglichkeit, die Wartbarkeit und Erweiterbarkeit von Programmen zu verbessern. Es ermöglicht eine einfachere Handhabung von komplexen Datenverarbeitungsalgorithmen und trägt zur Schaffung gut strukturierter und klarer Codebasen bei.

Template Method Pattern implemented in PLSQL 🔗

Design patterns are great! They are not limited to Java or C. The Template Method Pattern can also be implemented in Oracles PLSQL language using clean Object Types.

The Template Method Design Pattern is probably one of the most widely used and useful design patterns. A common example of this is when writing a program that will handle data using various detail algorithms. The abstract Class would have a method called the Template Method which contains the main program logic. There also exist helper methods in the class, which are specified by any class that inherits from it. So the abstract class defines the main logic. The subclasses themselves implement the detailed logic. This allows every developer to implement their specific details without changing the main logic.

Implementation in PLSQL 🔗

In PLSQL, this design pattern can be implemented in a procedural way, as done by Lucas Jellema at Amis.nl. Here is a PLSQL sample using Object Types:

drop type addresso;
drop type addressabstracto;

...

create or replace type addressabstracto as object
    PersId number,
    member procedure setPersId(pPersId in number),
    member function getPersId return number,
    member function getMainResult return number,
    member function doSomethingA return number,
    member function doSomethingB return number
    not final not instantiable;

create or replace type body addressabstracto as
    Setters & Getters
    member procedure setPersId(pPersId in number) is
    begin
        self.PersId := pPersId;
    end;

    member function getPersId return number is
    begin
        return self.PersId;
    end;

    Main Template Method
    member function getMainResult return number is
    begin
        return doSomethingA + doSomethingB;
    end;

    Private methods supporting the template method
    can be overridden by subclass methods
    member function doSomethingA return number is
    begin
        return 0;
    end;

    member function doSomethingB return number is
    begin
        return 0;
    end;
end;

...

In this implementation, the main logic is defined in the abstract class, while the subclasses override specific methods to implement detailed logic.

Testcase 🔗

Here is a simple testcase using the addresso type:

declare
    a addresso;
    p number := 12;
begin
    a := addresso;
    dbms_output.put_line('PersId: ' || a.getPersId);
    dbms_output.put_line('Address: ' || a.getMainResult);
end;

Fazit 2025 🔗

In 2025, the Template Method Pattern remains a fundamental design pattern that can be used in Oracle PLSQL programming. It allows for a clear separation between main program logic and detailed implementations in subclasses. By using Object Types, developers can implement specific detailed behaviors without having to modify the main logic.

The implementation of the Template Method Pattern in PLSQL still provides an effective way to improve the maintainability and extensibility of programs. It enables easier handling of complex data processing algorithms and contributes to creating well-structured and clear code bases.

By following this pattern, developers can create more robust and maintainable code that is adaptable to changing requirements and promotes code reuse and consistency.