Jeff Moden (9/4/2010)
Lynn Pettis (8/31/2010)
Guess what, Oracle actually has CTE's, they just don't call it that in the reference manual.True enough. And search for WITH won't do it either. They call it "Subquery ReFactoring".
I believe they called it this because this was what it was commonly known as in the SQL:1999 standard... which Microsoft only ran with in SQL Server 2005...
Jeff Moden (9/4/2010)
Lynn Pettis (8/31/2010)
Having access to the reference manual is showing me that I can actually use some of the things I have learned here on SSC.
There will be so much more that you won't be able to apply. Try overwriting a variable using a pseudo-cursor. Try writing a set based trigger. And wait until you discover the joys of the UPDATE statement in Oracle... you need to get really, really good at using correlated subqueries to do any joined UPDATEs in Oracle. If you have a late enough version, forget all about using UPDATE and just jump straight to MERGE for doing updates even if you don't need to do an "upsert".
If I may interject... 🙂
A few things:
1. If by "set based trigger" you mean that you can interate over every row that is updated, then you can iuse the "for each row" clause and use the :new or :old row tables to do inserts. Not sure what the performance is like compared to SQL Server's do it in one shot solution, but if anything it's more flexible than SQL Server's approach.
2. If you mean by a "joined" update you are missing the FROM clause, have a look at the following link: http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx
In other words, you can do the same thing in Oracle, only with different syntax. In fact, you can do many, many more things in the UPDATE and DELETE statements along these lines - check the UPDATE documentation and the DELETE documentation.
Jeff Moden (9/4/2010)
Lynn Pettis (8/31/2010)
Still looking forward to seeing if I can push for moving toward SQL Server, but we'll have to wait and see.
Heh... ask their Oracle DBA's about that. Of course, that's if you can get their attention on the subject. Most of the Oracle DBA's I've run across still think SQL Server is a toy to be ignored especially for "enterprise class applications". There are, of course, exceptions...
That is indeed somewhat infuriating, but sometimes I get their point, because:
1. Oracle runs on more platforms,
2. Oracle has implemented more of the SQL standard that Microsoft. Case in point: try doing a lag, lead, or top analytic function in SQL Server... you can't! You can only use rank, denserank, and row_number. If you were able to use lag(column) over () then I'm almost certain we wouldn't need to be using non-supported clustered index approaches to running total aggregates... though we wouldn't have had one of the coolest articles around on this approach, of course 🙂
3. Oracle RAC - enough said
4. Temporary tablespaces can be assigned to users - try THAT in SQL Server (TempDB anyone?)
5. Oracle has a sophisticated CBO - sadly, hard to get your head around, unlike SQL Server.
But then I look at the way you need to get execution plans, and I always think to myself - "I'd rather do that in SQL Server!".