oracle与sql-server的sql语法差别.pptVIP

Beginning SQL:Differences Between SQL Server and Oracle Introduction If youre new to SQL or just new to Oracle SQL, perhaps coming from a Microsoft SQL Server environment, it may seem like the two versions should be very similar, and they are, to a certain degree, but they are also very different in some important and basic ways. Agenda I. Quick Intro for SQL Server Users II. Some Detail: Joins, Subqueries, Deletes III. Certain Conceptual Differences IV. Powerful New Features V. Summary References Don’t Use Databases Use Dual Select Into Inserts Updates Updates Deletes Software II. A Little More Detail Outer Join Sub-Queries in Place of Columns Deletes With a Second From Clause 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; SubQueries in Place of Columns 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; Delete with Second From Clause Oracle delete from products where ( a, b ) in ( select a, b from product_deletes where c = d ); III. More Depth The Connect Concept Other Conceptual Differences Data Type Differences Column Aliases Sub-Queries The Connect Concept SQL Server Multiple databases Oracle Single Database Multiple tablespaces, sche