PL/SQL resourse for folks mostly familiar with C-based OOP languages

May 16, 2013 1:10 PM

I feel like the best way for me to improve my very basic understanding of PL/SQL would be a book or website designed for an audience of programmers who know Java or C# or VB.NET. Is there such a thing?

Yeah, PL/SQL is procedural, it should be simple (I imagine my hecklers saying), but when I see REFCURSORs and RECORDS and TABLES declared and manipulated, I just don't get it.

If you can't suggest something quite like what I am looking for, please do tell me what you think the best PL/SQL book is if you have an opinion on that. I appreciate it.

posted by kitcat to Education (6 answers total)

Man, honestly, there isn't such a thing. And if there is, you'll end up learning SQL the wrong way.

I can't tell you how many times I've come across some SQL someone else has written (T-SQL, Microsoft's variant, for what its worth, but this still holds true) - and it is clearly written by someone with a C-like programming background. They do things in logical blocks and steps and procedures that feel very much like an A then B then C to get D result, when the SQL way of doing it is completely different.

Perfect example is row-by-row operations (a CURSOR in T-SQL, or an incremental WHILE loop). I'm sure there are reasonable uses for them, but I have yet (10+ years now of heavy database work) to come across a CURSOR that couldn't be re-written as a set of true SQL operations. The whole point of the database and the *power* of the language is to avoid things like row-by-row operations.

Sorry I can't point to specific examples. My general advice would be

* lean on the DBAs and SQL experts at your company. After you've written something, even if it works perfectly for you, go and ask them if it is done right
* don't be afraid to rewrite your code
* expiriment

I very much feel that really "knowing" SQL is just absorbed through experience, not something you can specifically learn like a functional language.
posted by ish__ at 1:40 PM on May 16, 2013 [4 favorites]

Best answer: ish__is right on. SQL is more like relational algebra than most other kinds of coding, so thinking about learning it in that way might be useful.
posted by Aizkolari at 2:24 PM on May 16, 2013 [1 favorite]

Best answer: To learn PL/SQL, I would start with this book.
posted by thelonius at 2:36 PM on May 16, 2013

ish - he (or she) is talking about PL/SQL, a procedural language that oracle supports. Not SQL proper.
posted by RustyBrooks at 3:40 PM on May 16, 2013 [1 favorite]

Or maybe that was clear. I can't tell because I don't know anything about T-sql. But if you have chosen to use PL/SQL to do somehting... using cursors and loops and what not is literally how you do it.
posted by RustyBrooks at 3:41 PM on May 16, 2013

I have to echo ish's sentiments. Just because it offers a procedural way of doing things, doesn't mean you should.

It's spooky really, their comment hits a bit too close to home. Just this week I have managed to deprecate a 3000+ line stored proc written by our database 'experts', who I believe comes from an old school imperative (C) programming background. My new version is 6 lines! For one particular 'real world' input I have tried, the old query upwards of an hour; my new one, 10secs. No joke :(

Now I really don't claim to be an SQL wizz, and I've just fallen into doing this work by accident it seems. Whilst I did a very small amount of relational algebra and calculus at university, the biggest help in getting the right mindset for constructing good SQL has been from Functional Programming. In my day job I am primarily a C# programmer, but I write very FP-influenced code (I was reflecting the other day that I'm more of a LINQ programmer than a C# one).

So, if you are indeed a .Net programmer, stop writing those foreach loops in favour of LINQ methods, and you'll start to get yourself into the SQL mindset.

... gah, I've written all of that, just to notice that ish also mentions FP in their last sentence. Just take this all as a very longwinded and very strong seconding of their entire post.
posted by Shelduck at 8:48 AM on May 17, 2013 [1 favorite]