Below is a side-by-side comparison of Oracle PL/SQL and SQL Server T-SQL for someone coming from a PL/SQL background. This will help you quickly understand the syntax and behavior differences.
🔄 PL/SQL vs T-SQL Syntax Comparison
| Concept / Operation | Oracle PL/SQL Syntax | SQL Server T-SQL Syntax |
|---|---|---|
| Anonymous Block | BEGIN ... END; | BEGIN ... END (no semicolon required) |
| Variable Declaration | DECLARE v_name VARCHAR2(50); | DECLARE @v_name VARCHAR(50); |
| Assignment | v_name := 'Hello'; | SET @v_name = 'Hello'; or SELECT @v_name = 'Hello'; |
| Output Message | DBMS_OUTPUT.PUT_LINE('Hello'); | PRINT 'Hello'; |
| IF Statement | IF condition THEN ... ELSIF ... ELSE ... END IF; | IF condition BEGIN ... END ELSE BEGIN ... END |
| LOOP (Simple Loop) | LOOP ... EXIT WHEN condition; END LOOP; | WHILE 1 = 1 BEGIN ... IF condition BREAK; END |
| FOR Loop (Numeric) | FOR i IN 1..5 LOOP ... END LOOP; | DECLARE @i INT = 1; WHILE @i <= 5 BEGIN ... SET @i += 1; END |
| WHILE Loop | WHILE condition LOOP ... END LOOP; | WHILE condition BEGIN ... END |
| Procedure Declaration | CREATE OR REPLACE PROCEDURE proc_name IS BEGIN ... END; | CREATE PROCEDURE proc_name AS BEGIN ... END |
| Function Declaration | CREATE OR REPLACE FUNCTION func_name RETURN TYPE IS BEGIN ... END; | CREATE FUNCTION func_name() RETURNS TYPE AS BEGIN ... RETURN ... END |
| Calling Procedure | EXEC proc_name; or BEGIN proc_name; END; | EXEC proc_name; or EXECUTE proc_name; |
| Exception Handling | BEGIN ... EXCEPTION WHEN ... THEN ... END; | BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH; |
| Transactions (Manual) | COMMIT; / ROLLBACK; | BEGIN TRAN; / COMMIT; / ROLLBACK; |
| CURSOR Declaration | CURSOR cur IS SELECT ...; | DECLARE cur CURSOR FOR SELECT ... |
| CURSOR Looping | OPEN cur; FETCH ...; EXIT WHEN ...; CLOSE cur; | OPEN cur; FETCH NEXT FROM cur INTO ...; WHILE @@FETCH_STATUS = 0 ... |
| Temporary Table | CREATE GLOBAL TEMPORARY TABLE ... | CREATE TABLE #temp_table (...) |
| Autonumber / Sequence | CREATE SEQUENCE seq_name ... | IDENTITY column or SEQUENCE (since SQL Server 2012) |
| String Concatenation | `’Hello’ | |
| Getting Current Date | SYSDATE | GETDATE() or SYSDATETIME() |
| Dual Table for SELECT | SELECT 1 FROM DUAL; | SELECT 1; |
🧠Key Behavioral Differences
| Feature | PL/SQL (Oracle) | T-SQL (SQL Server) |
|---|---|---|
| Compilation | PL/SQL is compiled into bytecode | T-SQL is interpreted or compiled to native code |
| Exception Handling | Structured, robust EXCEPTION block | TRY...CATCH block |
| Package Support | Yes (with procedures, functions, variables) | No native package support |
| Autonomous Transactions | Supported with PRAGMA AUTONOMOUS_TRANSACTION | Not directly supported |
| RETURNING Clause | Supported in DML (e.g., RETURNING INTO) | Only in OUTPUT clause with INSERT/UPDATE/DELETE |
| Procedural Constructs | Rich in-built procedural features | Basic procedural constructs |