#100CodeExamples – PL/SQL Objects: Basics of force-sensitive beings
Tags: 100codeexamples, plsql, oracle
Published: 2019-01-23 06:50:22

---
title: #100CodeExamples – PL/SQL Objects: Basics of force-sensitive beings
published: true
tags: #100CodeExamples,PL/SQL,Oracle
canonical_url: https://cleandatabase.wordpress.com/2019/01/22/100codeexamples-pl-sql-objects-basics-of-force-sensitive-beings/
---

I dived into object-oriented programming in PL/SQL a while ago due to utPLSQL and also used it several times for my normal work meanwhile.

This powerful feature of the PL/SQL language is highly underused and therefore I want to create some examples around it in hopes to show some of the advantages objects in PL/SQL have to offer.

Let’s assume we want to implement some behavior for force-sensitive beings, whose most prominent representatives are, of course, Jedi and Sith:

```sql
-- First create a base type
create or replace type force_sensitive force as object
(
  -- First we define object-level attributes
  c_name varchar2(100),
  -- notice the comma instead of semicolon:
  -- this is a definition, similar to table definition - not
  -- an implemenntation.
 
  -- define a function we are going to implement
  member function name
    return varchar2,
 
  -- and one we are only going to implement
  -- inside the children
  not instantiable member function alignment
    return varchar2
)
-- For this is our base type we dont want it to be
-- instantiable, e.g. no "new force_sensitive()" possible
not final not instantiable;
/
 
-- Now implement the body of the base type
create or replace type body force_sensitive as
  member function name
    return varchar2
  as
    begin
      return c_name;
    end;
end;
/
 
-- We want to have a real type now
create or replace type jedi under force_sensitive (
  overriding member function alignment
    return varchar2
);
/
 
create or replace type body jedi as
  overriding member function alignment
    return varchar2
  as
    begin
      return 'light';
    end;
end;
/
 
-- And another real type with a specialty
create or replace type sith under force_sensitive (
  -- Default constructor has all member-attributes
  -- as parameters and assignes them. We can change
  -- this with our own constructor
  constructor function sith( c_name in varchar2 )
    return self as result,
 
  overriding member function alignment
    return varchar2
);
/
 
create or replace type body sith as
  constructor function sith( c_name in varchar2 )
    return self as result
  is
    begin
      -- self is a special built-in parameter
      -- which is passed to each member function as first
      -- parameter, no matter if explicitly defined or not.
      -- It holds an instance to the object
      self.c_name := 'Darth ' || c_name;
 
      -- however, in a constructor we dont return self
      -- but just return.
      return;
    end;
 
  overriding member function alignment
    return varchar2 is
    begin
      return 'dark';
    end;
end;
/
 
declare
  l_luke force_sensitive;
  l_vader force_sensitive;
 
  -- We can here see one of the first gifts of plsql-
  -- objects: We can build generalized methods which
  -- work for any subtype
  procedure output_alignment(
    i_force_user in out nocopy force_sensitive )
  as
    begin
      dbms_output.put_line(i_force_user.name() ||
        ' is aligned to ' || i_force_user.alignment());
    end;
begin
  -- Create a new instance of a concrete type
  l_luke := new jedi('Luke Skywalker');
  output_alignment(l_luke);
 
  l_vader := new sith('Vader');
  output_alignment(l_vader);
end;
/
```

Output:

```
Luke Skywalker is aligned to light
Darth Vader is aligned to dark
```

You can run this example on [LiveSQL](https://livesql.oracle.com/apex/livesql/s/hu8xkvvnavq9yzgk10qupu798).

This is only the first of several examples about PL/SQL objects in that specific theme to come, so stay tuned.