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.)
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.
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