Wednesday, May 1, 2013

TSQL vs PL/SQL

For the past 8+ years I have primarily been working the Microsoft stack and TSQL. Recently I started working more with Oracle PL/SQL and I wanted to keep track of some of the key differences that I come across. First I will start out with the definitions on each. TSQL is Microsoft and Sybase's proprietary extension to Structured Query Language. SQL is a computer language developed for querying, altering and defining relational databases using declarative statements. TSQL expands by adding procedural programming, local variables and various functions. PL/SQL is Oracles procedural extension language for SQL. Oracle databases have three key programming languages, PL/SQL, SQL and Java.


Over the next couple of weeks I will be updating this article with my findings. Some very simple but some a little more complex.


  • TSQL GetDate() is replaced by PL/SQL select sysdate from dual; 
    • For those of you that don't know what dual is, its a dummy table
  • Update from sub select
    • TSQL: update s set s.value = k.value2 from dbo.SomeTable s inner join (select ID, value2 dbo.KTable) k on k.ID = s.ID
    • PL/SQL: update SomeTable s set(value) = (select value2 from KTable k where k.ID = s.ID)
    • as you can see the syntax is a little different but will accomplish the same thing
  • ISNULL is replaced with NVL
  • Top 10 is replaced with "where rownum <11 li="">
  • TSQL select @@version vs Oracle select * from v$version where banner like 'Oracle%';
  • More to come, please check back





References:

0 comments:

Post a Comment