"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.
- 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.