1

Good evening,

Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]?

(for MySQL, PostgreSQL, Oracle or etc.)

4
  • Can you be specific about why you want an alternative, and why you are interested in multiple platforms? Oct 25, 2011 at 9:06
  • Doesn't need to be for multiple platforms, I should've used xor instead of or. An OO alternative would be nice, since procedural is less commonly used nowadays.
    – A T
    Oct 25, 2011 at 9:22
  • 5
    I've used many OO languages but most of the supposed benefits are found in databases under different guises. I cannot urge you strongly enough to forget any OO religion you might have and use the correct tool for the job - in the case of Oracle that tool is PL/SQL 99.99% of the time. Oct 25, 2011 at 9:45
  • 3
    What do you mean by OO? Because PL/SQL perfectly well does what I would say are the two main OO characteristics, dynamic dispatch and data encapsulation.
    – Gaius
    Oct 25, 2011 at 9:46

2 Answers 2

3

Embedded Sprocs on a DBMS

I think for the purposes of your question the answer is probably somewhere between 'no' and 'it depends'.

Most relational DBMS platforms support stored procedures in a SQL derived dialect or a procedural language with embedded SQL capabilities. PL/SQL is an example of the latter class of languages.

Writing queries in arbitrary languages is not really an option as they still have to interact with the query optimiser, so they can only express semantics that are compatible with its architecture. Even 'procedural' SQL dialects like T-SQL actually issue multiple queries to the optimiser. SQL is always going to be limited in this way because of the tight coupling to the architecture of the query optimiser.

Many DBMS platforms support alternative languages for stored procedures, such as a JVM in Oracle, CLR integration in SQL Server and various embedded langauges in PostgreSQL. These allow you to write procedural code, support fast local connections to the DBMS, and (in some cases) integrate with APIs in the database manager. These APIs let you write custom aggregate functions and other extensions.

However, there good reasons not to use this for Non-SQL application code unless it is really necessary, especially in the case of Oracle. Three major reasons are:

  • It is often quite hard to debug code when it is embedded in the DBMS in this way.

  • There are no standard APIs for this type of code. It is highly non-portable and tightly coupled to the DBMS - you don't even have the same options for language across platforms. You would have to write and maintain a separate subsystem for every DBMS platform you intended to support.

  • You are paying DBMS licencing for the CPU capacity you are using to run the code. In the case of Oracle, the DBMS licence could be an order of magnitude more expensive than the hardware it is running on.

Generally, it is only sensible to use embedded code in the DBMS for applications where it is strictly necessary.

An alternative: OODBMS platforms

Some OODBMS platforms such as Gemstone/S, are designed to support an closely coupled O-O language and the system is intended to run like this. However, in the case of Gemstone/S the system is tightly coupled to a limited set of languages, prinicpally Smalltalk.

However, the Gemstone/S VM is good enough that somebody even ported ruby to run on it with a project called maglev.. This might get you closer to what you want, but it requires you to move to a specific platform.

6

Oracle has Java server-side, but I'd caution that it is not a replacement for PL/SQL. There is no better language than PL/SQL for manipulating data stored in the database - Java may be appropriate for computationally intensive business logic.

For postgres,

There are currently four procedural languages available in the standard PostgreSQL distribution: PL/pgSQL (Chapter 39), PL/Tcl (Chapter 40), PL/Perl (Chapter 41), and PL/Python (Chapter 42). There are additional procedural languages available that are not included in the core distribution

But I think it is fair to say that PL/pgSQL is the most mature and feature complete (and, you may be sorry to hear, very similar to PL/SQL).

I don't know much about MySQL stored procedures but here is a link to the docs

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.