3 ms·

What makes pl/sql an abomination of a language?

by ako 5y ago

hnlmorg 5y ago
It's an attempt at bringing procedural workflows to SQL but the two paradigms aren't really compatible. It's much easier working with a scripting language like Perl or Python and embedding SQL queries. Use a relational language for the relational logic and a procedural language for the procedural logic.
- noisy_boy 5y ago
  Granted PL/SQL isn't as flexible as Perl or Python - it is not a fully-fledged application/system language (don't think it was meant to be that). Having said that, it does have ability to define types/reference column/row types, typical data structures like arrays/hashes/records(which are like structs), control-flow etc but the main point is that all of that runs within the database engine - this is useful when you must deal with complicated conditional logic but don't want to make those round-trips and handle all of it in one go. Generally traditional relational SQL is the preferred approach and it can cater to conditional scenarios as well but sometimes SQL statements combined with conditional logic written procedurally in one place is simpler to understand/more performant. To be clear, I'm not advocating this over the traditional approach of programs running discrete queries and handling the logic themselves - just saying that the approach taken by PL/SQL has its merits.
  - hnlmorg 5y ago
    I get the reason for PL/SQL and completely understand the advantages of stored procedures (I used to write code for Oracle Middleware so have had several years of experience in specifically this domain). I'm just answering the question of why the language sucks to write code for. I'm not suggesting PL/SQL should be a fully fledged scripting language either (neither Perl nor Python are systems languages by the way). I'm just saying the two paradigms PL/SQL aims to leverage don't combine well so the end result is always going to be ugly.
  - masklinn 5y ago
    > Granted PL/SQL isn't as flexible as Perl or Python Which, incidentally, can be used as procedural languages in Postgres (as well as TCL) as part of the standard distribution. And there are third-party extensions for javascript, ruby, scheme, r, java, lua, … (though some of them may not be maintained anymore, the pg13 documentation doesn't list scheme and ruby anymore).
slotrans 5y ago
It's not. I wrote a lot of PL/SQL at my first real software job and I miss it dearly. I treasure every opportunity I get to write a little PL/pgSQL, which is very similar. Use it for the right purposes and it's a joy to write. There are certainly things it can't do, and things it can do but only poorly, but that's true of anything.
- hnlmorg 5y ago
  > Use it for the right purposes and it's a joy to write. I did use it for the right purposes. I was writing applications for Oracle Middleware. You don't get any more "right purposes" than that. And no, it really isn't a joy. Almost everyone I've ever met or spoken to hated PL/SQL (you being literally the one exception). I get that enjoyment is subjective but in this case it really feels like you're the anomalous data point. I've not use PL/pgSQL so can't comment on how similar it is to Oracle PL/SQL but PostgreSQL is a much nice RDBMS to manage and write SQL for than Oracle is. So it wouldn't surprise me if PL/pgSQL had some quality of life improvements.
  - ako 5y ago
    I've done a fair amount of plsql programming as well, did not see it as a big problem. When processing a lot of data i definitely prefer it to doing the same in java/python/... Executing queries with jdbc, mapping resultsets to java objects, writing back changes is cumbersome and bad performance. Working with plsql packages is like programming in pascal/modula 2 but with first class SQL support built in. A bit like linq in c#. For example, I don't see how for cursor loops in plsql like documented here: https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/ https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-f... are less elegant than doing something similar with java or python.

What makes pl/sql an abomination of a language?

by ako 5y ago

What makes pl/sql an abomination of a language?

It's an attempt at bringing procedural workflows to SQL but the two paradigms aren't really compatible. It's much easier working with a scripting language like Perl or Python and embedding SQL queries. Use a relational language for the relational logic and a procedural language for the procedural logic.

noisy_boy 5y ago
Granted PL/SQL isn't as flexible as Perl or Python - it is not a fully-fledged application/system language (don't think it was meant to be that). Having said that, it does have ability to define types/reference column/row types, typical data structures like arrays/hashes/records(which are like structs), control-flow etc but the main point is that all of that runs within the database engine - this is useful when you must deal with complicated conditional logic but don't want to make those round-trips and handle all of it in one go. Generally traditional relational SQL is the preferred approach and it can cater to conditional scenarios as well but sometimes SQL statements combined with conditional logic written procedurally in one place is simpler to understand/more performant. To be clear, I'm not advocating this over the traditional approach of programs running discrete queries and handling the logic themselves - just saying that the approach taken by PL/SQL has its merits.
- hnlmorg 5y ago
  I get the reason for PL/SQL and completely understand the advantages of stored procedures (I used to write code for Oracle Middleware so have had several years of experience in specifically this domain). I'm just answering the question of why the language sucks to write code for. I'm not suggesting PL/SQL should be a fully fledged scripting language either (neither Perl nor Python are systems languages by the way). I'm just saying the two paradigms PL/SQL aims to leverage don't combine well so the end result is always going to be ugly.
- masklinn 5y ago
  > Granted PL/SQL isn't as flexible as Perl or Python Which, incidentally, can be used as procedural languages in Postgres (as well as TCL) as part of the standard distribution. And there are third-party extensions for javascript, ruby, scheme, r, java, lua, … (though some of them may not be maintained anymore, the pg13 documentation doesn't list scheme and ruby anymore).

slotrans 5y ago

It's not. I wrote a lot of PL/SQL at my first real software job and I miss it dearly. I treasure every opportunity I get to write a little PL/pgSQL, which is very similar. Use it for the right purposes and it's a joy to write. There are certainly things it can't do, and things it can do but only poorly, but that's true of anything.

hnlmorg 5y ago
> Use it for the right purposes and it's a joy to write. I did use it for the right purposes. I was writing applications for Oracle Middleware. You don't get any more "right purposes" than that. And no, it really isn't a joy. Almost everyone I've ever met or spoken to hated PL/SQL (you being literally the one exception). I get that enjoyment is subjective but in this case it really feels like you're the anomalous data point. I've not use PL/pgSQL so can't comment on how similar it is to Oracle PL/SQL but PostgreSQL is a much nice RDBMS to manage and write SQL for than Oracle is. So it wouldn't surprise me if PL/pgSQL had some quality of life improvements.
- ako 5y ago
  I've done a fair amount of plsql programming as well, did not see it as a big problem. When processing a lot of data i definitely prefer it to doing the same in java/python/... Executing queries with jdbc, mapping resultsets to java objects, writing back changes is cumbersome and bad performance. Working with plsql packages is like programming in pascal/modula 2 but with first class SQL support built in. A bit like linq in c#. For example, I don't see how for cursor loops in plsql like documented here: https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/ https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-f... are less elegant than doing something similar with java or python.