November 20, 2002
PL/SQL vs Transact-SQL, Part 1
Converting the tables and associated objects was a breeze, not so the little snippets of code I wrote.
I'm slowly going through my PL/SQL code and converting it to Transact-SQL. I'll make notes here as I go. The first thing to note is that in Oracle there is a real difference between plain SQL and PL/SQL, in SQL Server there isn't really any separation. This means that in T-SQL you end up with a series of statements, but in Oracle you have to structure your PL/SQL and keep it seperate from any pure SQL (DML or DDL) operations.
First, declaring variables. PL/SQL forces you to declare variables in the scope of the block in which they are used, because the structure of the atomic unit of code is;
DECLARE
-- local variable declarations
BEGIN
-- code statements
END;
In T-SQL the atomic unit of code is the individual statement so you can declare local variables wherever you like. An easy conversion is just to replace the declare section of your PL/SQL block with a series of DECLARE statements in T-SQL. But, in T-SQL you cannot set a value in a local variable when you define it, you must do this in a seperate statement. So something like;
l_date DATE := to_date('31-JUL-02');
becomes;
DECLARE @l_date DATETIME;
SET @l_date = '31-JUL-02';
More will be forthcoming, as and when I find it. If I have made any mistakes, please feel free to correct me via the comments. As a side note, I would love to write this in Python but that is ruled out for the same reason as Oracle, the client doesn't have it.
Posted by Andy Todd at November 20, 2002 04:27 PM