Here are some useful information for those who knows TSQL and wants to learn PL/SQL.
Lets start with the data types
SQL Server | Oracle |
image | blob |
nchar(x) | char(n*2) |
text | clob |
datetime | date |
real | float |
float | float |
bit | number(1) |
integer | number(10) |
money | number(19,4) |
tinyint | number(3) |
smallint | number(6) |
binary(x) | raw(x) |
varbinary | raw(x) |
nvarchar(x) | varchar(x*2) |
varchar(x) | varchar2(x) |
Now lets go to the query commands:
SELECT INTO
SQL Server
SELECT getdate() SampleColumn, ‘sample text’ SampleColumn2 INTO SampleTable;
Oracle
INSERT INTO SampleTable (SampleColumn1, SampleColumn2) VALUES (sysdate, ‘sample text’);
INSERT
SQL Server
INSERT SampleTable VALUES (‘sample text’, 100);
Or
INSERT INTO SampleTable VALUES (‘sample text’, 100);
Oracle
INSERT INTO SampleTable VALUES (‘sample text’, 100);
UPDATE
SQL Server
UPDATE SampleTable1 SET SampleColumn1 = SampleTable2.SampleColumn1 FROM SampleTable1, SampleTable2 WHERE SampleTable1.SampleColumn1 LIKE ‘Test%’ and SampleTable2.SampleColumn2 = ‘some text’;
Oracle
UPDATE SampleTable1 SET SampleColumn1 = (SELECT a.SampleColumn1 FROM SampleTable2 a WHERE SampleTable2.SampleColumn2 = ‘some text’) WHERE SampleTable1.SampleColumn1 LIKE ‘Test%’;
DELETE
SQL Server
DELETE SampleTable WHERE SampleColumn LIKE ‘Test%’;
Or
DELETE FROM SampleTable WHERE SampleColumn LIKE ‘Test%’;
Oracle
DELETE FROM SampleTable WHERE SampleColumn LIKE ‘Test %’;
OUTER JOIN
SQL Server
SELECT d.DepartmentName, e.EmployeeName FROM Department d, Employees e WHERE d.EmployeeNumber *= e.EmployeeNumber;
Or
SELECT d.DepartmentName, e.EmployeeName FROM Department d LEFT OUTER JOIN Employees e ON d.EmployeeNumber = e.EmployeeNumber
Oracle
SELECT d.DepartmentName,e. EmployeeName FROM Department d, Employees e WHERE d.EmployeeNumber = e.EmployeeNumber (+);
PIVOT
SQL Server
SELECT DISTINCT Year,
Q1 = (SELECT Amount amt FROM Sales WHERE Quarter = 1 AND Year = s.Year),
Q2 = (SELECT Amount amt FROM Sales WHERE Quarter = 2 AND Year = s.Year),
Q3 = (SELECT Amount amt FROM Sales WHERE Quarter = 3 AND Year = s.Year),
Q4 = (SELECT Amount amt FROM Sales WHERE Quarter = 4 AND Year = s.Year)
FROM Sales s;
Oracle
SELECT Year,
DECODE(Quarter, 1, Amount, 0 ) Q1,
DECODE(Quarter, 2, Amount, 0 ) Q2,
DECODE(Quarter, 3, Amount, 0 ) Q3,
DECODE(Quarter, 4, Amount, 0 ) Q4
FROM Sales s;
ALIAS
SQL Server
SELECT a = DepartmentID , b = DepartmentName, c = EmployeeNumber FROM Department;
Or
SELECT DepartmentID AS a, DepartmentName AS b, EmployeeNumber AS c FROM Department;
Oracle
SELECT DepartmentID a, DepartmentName b, EmployeeNumber c FROM Department;
These are just some of my observations and what I commonly use definitely there will be a lot more!
Use Dual
SQL Server
select getdate();
Oracle
select sysdate from dual;
Select Into
SQL Server
select getdate() mycolumn into mytable;
Oracle
insert into mytable (mycolumn) values(sysdate);
Inserts
SQL Server
Insert mytable values(‘more text’);
Oracle
Insert into mytable values(‘more text’);
Updates
SQL Server
update mytable set mycolumn=myothertable.mycolumn from mytable,myothertable where mytable.mycolumn like ‘MY%’ and myothertable.myothercolumn=’some text’;
Oracle
update mytable set mycolumn=(select a.mycolumn from myothertable a where myothertable.myothercolumn=’some text) where mytable.mycolumn like ‘MY%’;
Deletes
SQL Server
delete mytable where mycolumn like ‘some%’;
Oracle
delete from mytable where mycolumn like ‘some%’;
Outer Join
SQL Server
select d.deptname, e.ename from dept d, emp e where d.empno *= e.enum;
Oracle
select d.deptname,e.ename from dept d, emp e where d.empno = e.enum (+);
SubQueries in Place of Columns
SQL Server
select distinct year,
q1 = (select Amount amt FROM sales
where Quarter=1 AND year = s.year),
q2 = (SELECT Amount amt FROM sales
where Quarter=2 AND year = s.year),
q3 = (SELECT Amount amt FROM sales
where Quarter=3 AND year = s.year),
q4 = (SELECT Amount amt FROM sales
where Quarter=4 AND year = s.year)
from sales s;
Oracle
SELECT year,
DECODE( quarter, 1, amount, 0 ) q1,
DECODE( quarter, 2, amount, 0 ) q2,
DECODE( quarter, 3, amount, 0 ) q3,
DECODE( quarter, 4, amount, 0 ) q4
FROM sales s;
Delete with Second From Clause
SQL Server
Delete from products from products, product_deletes where products.a = product_deletes.a and products.b = product_deletes.b and product_deletes.c = ‘d’;
Oracle
Delete from products where ( a, b ) in ( select a, b from product_deletes where c = ‘d’ );
Column Aliases
SQL Server
select a=deptid, b=deptname,c=empno from dept;
Oracle
select deptid a, deptname b, empno c from dept;
Sub-queries
SQL Server
SELECT ename, deptname FROM emp, dept WHERE emp.enum = 10 AND(SELECT security_code
FROM employee_security WHERE empno = emp.enum) = (SELECT security_code FROM security_master WHERE sec_level = dept.sec_level);
Oracle
SELECT empname, deptname FROM emp, dept WHERE emp.empno = 10 AND EXISTS (SELECT security_code FROM employee_security es WHERE es.empno = emp.empno AND es.security_code =
(SELECT security_code FROM security_master WHERE sec_level =dept.sec_level));