T-SQL vs PL\SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension language for SQL and the Oracle relational database

Sample PL SQL:
DECLARE
number1 NUMBER(2);
number2 NUMBER(2) := 17; — value default
text1 VARCHAR2(12) := ‘Hello world’;
text2 DATE := SYSDATE; — current date and time
BEGIN
SELECT street_number
INTO number1
FROM address
WHERE name = ‘INU’;
END;

Transact-SQL (T-SQL) is Microsoft’s and Sybase’s proprietary extension to SQL.

Sample T-SQL:

DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
PRINT ‘The count is ‘ + CONVERT(VARCHAR(10), @Counter)
SET @Counter = @Counter – 1
END

1) In PL/SQL, Oracle has implemented true structured programming and encapsulation functionality that is completely missing from Transact-SQL.

2) In PL/SQL, you can nest in-line procedures and functions within another procedure/function, allowing you to hide internal details and easily implement repetitive tasks via the nested components.
In T-SQL, each function/procedure needs to be compiled separately.

3) In PL/SQL, if you need to do something twice, you just write a nested function once and call it twice.
In T-SQL, you need to decide if the tradeoff of creating yet another separate component is worth the trouble, or do you just copy and paste the same code. This can have signficant impacts on maintainability.

4) In PL/SQL, you can create Packages to contain entire suites of data structures, procedures, and functions, and only expose those public interfaces that you want external callers to see. Plus, Packages can maintain internal variable states between calls, even including complex temporary table structures. Neither of these capabilities is available in T-SQL.