Difference Between PL-SQL and T-SQL

PL-SQL vs T-SQL

T-SQL (Transact SQL) is an extension of SQL developed by Microsoft. T-SQL is used in Microsoft SQL Server. PL/SQL (Procedural Language/Structured Query Language) is also a procedural extension for SQL developed by Oracle. PL/SQL is a main programming language embedded in the Oracle database.

PL/SQL

PL/SQL is a procedural extension for SQL developed by Oracle. PL/SQL programs are built up of blocks, which is the basic unit of PL/SQL. PL/SQL provides support for variables, loops (WHILE loops, FOR loops, and Cursor FOR loops), conditional statements, exceptions and arrays. A PL/SQL program contains SQL statements. These SQL statements include SELECT, INSERT, UPDATE, DELETE, etc. SQL statements like CREATE, DROP, or ALTER are not allowed in PL/SQL programs. PL/SQL functions can contain PL/SQL statements and SQL statements and it returns a value. PL/SQL procedures on the other hand cannot contain SQL statements and it does not return a value. PL/SQL also supports some object oriented programming concepts such as encapsulation, function overloading and information hiding. But it does not support inheritance. In PL/SQL, packages can be used to group functions, procedures, variables, etc. Packages allow code reusing. Using PL/SQL code on Oracle server would lead to improved performance, since the Oracle server pre-compiles the PL/SQL code before actually executing it.

T-SQL

T-SQL is an extension of SQL developed by Microsoft. T-SQL extends SQL by adding several features such as procedural programming, local variables and supporting functions for string/ data processing. These features makes T-SQL Turing complete. Any application, which needs to communicate with Microsoft SQL server, needs to send a T-SQL statement to the Microsoft SQL Server. T-SQL provides flow control capabilities using the following keywords: BEGIN and END, BREAK, CONTINUE, GOTO, IF and ELSE, RETURN, WAITFOR, and WHILE. Furthermore, T-SQL allows a FROM clause to be added to DELETE and UPDATE statements. This FROM clause would allow inserting joins in to DELETE and UPDATE statements. T-SQL also allows inserting multiples rows in to a table using the BULK INSERT statement. This would insert multiple rows in to a table by reading an external file containing data. Using BULK INSERT improves performance than using separate INSERT statements for each row that needs to be inserted.

What is the difference between PL/SQL and T-SQL?

PL/SQL is a procedural extension to the SQL provided by Oracle and it is used with the Oracle database server, while T-SQL is an extension of SQL developed by Microsoft and it is mainly used with Microsoft SQL Server. There are some differences between the data types in PL/SQL and T-SQL. For example T-SQL has two data types called DATETIME and SMALL-DATETIME, while PL/SQL has a single data type called DATE. Furthermore, to obtain the functionality of the DECODE function in PL/SQL, the CASE statement has to be used in T-SQL. Also, instead of SELECT INTO statement in T-SQL, the INSERT INTO statement has to be used in PL/SQL. In PL/SQL, there is a MINUS operator, which could be used with SELECT statements. In T-SQL the same results can be obtained by using the NOT EXISTS clause with the SELECT statements.