"Oracle PL-SQL is the same as Microsoft T-SQL. If you know one, you know the other"
Not really.
Here are a few differences, some of them can have a serious impact of the way you write sproc code. Good:
- T-SQL: stored procedures return result sets very easily: all you have to do is write a select statement without any 'INTO'. PL-SQL: you have to select into a ref cursor and define this ref cursor as output parameter.
- T-SQL: stored procedures do not rollback automatically if something fails. Until SQL Server 2005 you had to test @@ERROR after each and every statement and goto a handler to rollback. Ugly, tedious and error-prone. In 2005 you can use a TRY CATCH, which is much more elegant. However you still have to rollback explicitly
PL-SQL: sprocs are atomic. Any error inside a sproc rolls everything back up to the point where the sproc was called. - T-SQL: no %TYPE! You can't refer to the type of a column without repeating it.
- T-SQL: RAISERROR does not break the flow. It simply returns an error string or message but the sproc still returns normally. Unless you use it within a TRY block, in which case the flow is diverted to the CATCH block (for SQL 2005 and beyond). Depending on the severity level you specify, RAISERROR within a TRY block either
- returns an error number without breaking the flow
- jumps to the CATCH block
- breaks the current database connection (wow!) (provided you have sysadmin role)
- PL-SQL: raise_application_error throws an exception, exits the current sproc, rolls back till implicit savepoint at the beginning of the sproc...
- Oracle does not have a BOOLEAN column type, although you can define a BOOLEAN variable in PL/SQL. SQL Server has a BIT column type where values can be only 0 or 1.
2 comments:
Erm... converting a string to a date - yes you can explicitly set the format in T-SQL, look at the third parameter for CONVERT.
Erm... RAISERROR does not break the flow - yes it does, if you specify an error which would break the flow. Different error levels are either informational or not.
Erm... Might be worth reading documentation sometimes.
string to a date: ok, fixed.
RAISERROR:
http://msdn.microsoft.com/en-us/library/ms178592.aspx
Apparently the only way you can break the flow without a TRY block is to use severity levels of 20 or higher. You have to be sysadmin and it breaks the current database connection (which is a bit violent, I just wanted to raise an exception)
I got into the habit of using TRY blocks for any sproc containing more than 2 statements. I use standard CATCH handlers that rollback to the beginning of the TRY, retrieve the last error message and re-raise it with RAISERROR so that the calling app gets a clue about what happened.
Post a Comment