Oracle与SQL-Server的SQL语法差别.ppt

Beginning SQL:Differences Between SQL Server and Oracle,Les KopariIndependent Consultant,A Quick Intro for SQL Server Users,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 UsersII.Some Detail:Joins,Subqueries,DeletesIII.Certain Conceptual DifferencesIV.Powerful New FeaturesV.Summary&References,Dont Use Databases,SQL Server,use mydatabase,Oracle,connect mydatabase/mypassword,Use Dual,Select Into,Inserts,Updates,SQL Server,update mytable set mycolumn=myothertable.mycolumn from mytable,myothertable where mytable.mycolumn like MY%and myothertable.myothercolumn=some text;,Updates,Oracle,update mytableset 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%;,Software,isql,osql:for queries developed in SQL Analyzer,sqlplus,SQL Server,Oracle,II.A Little More Detail,Outer JoinSub-Queries in Place of ColumnsDeletes 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 Serverselect distinct year,q1=(select Amount amt FROM saleswhere Quarter=1 AND year=s.year),q2=(SELECT Amount amt FROM saleswhere Quarter=2 AND year=s.year),q3=(SELECT Amount amt FROM saleswhere Quarter=3 AND year=s.year),q4=(SELECT Amount amt FROM saleswhere Quarter=4 AND year=s.year)from sales s;,SubQueries in Place of Columns,OracleSELECT 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 Serverdeletefrom productsfrom products,product_deleteswhere products.a=product_deletes.aand products.b=product_deletes.band product_deletes.c=d;,Delete with Second From Clause,Oracledeletefrom productswhere(a,b)in(select a,bfrom product_deleteswhere c=d);,III.More Depth,The Connect ConceptOther Conceptual DifferencesData Type DifferencesColumn AliasesSub-Queries,The Connect Concept,SQL Server Multiple databasesOracle Single Database Multiple tablespaces,schemas,users,Other Conceptual Differences,SQL ServerDatabase owner,DBOGroup/RoleNon-unique indexT-SQL stored procedure TriggerCompex ruleColumn identity property,OracleSchemaRoleIndexPL/SQL procedurePL/SQL functionBEFORE triggerAfter triggerSequence,Only in Oracle,ClustersPackagesTriggers for each rowSynonymsSnapshots,Data Type Differences,SQL ServerOracleINTEGER NUMBER(10)SMALLINT NUMBER(6)TINYINT NUMBER(3)REALFLOATFLOATFLOATBITNUMBER(1)VARCHAR(n)VARCHAR2(n)TEXTCLOBIMAGEBLOBBINARY(n)RAW(n)or BLOB,Data Type Differences,SQL ServerOracleVARBINARYRAW(n)or BLOBDATETIMEDATESMALL-DATETIMEDATEMONEYNUMBER(19,4)NCHAR(n)CHAR(n*2)NVARCHAR(n)VARCHAR(n*2)SMALLMONEYNUMBER(10,4)TIMESTAMPNUMBERSYSNAME VARCHAR2(30),VARCHAR2(128),Time,SQL Server Datetime:1/300th secondOracle Date:1 second Timestamp:1/100 millionth second,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,again,SQL ServerSELECT ename,deptnameFROM emp,deptWHERE emp.enum=10AND(SELECT security_codeFROM employee_securityWHERE empno=emp.enum)=(SELECT security_codeFROM security_master WHERE sec_level=dept.sec_level);,Sub-queries,again,OracleSELECT empname,deptnameFROM emp,deptWHERE emp.empno=10AND EXISTS(SELECT security_codeFROM employee_security esWHERE es.empno=emp.empnoAND es.security_code=(SELECT security_codeFROM security_masterWHERE sec_level=dept.sec_level);,Powerful New Features,Regular Expressions:Operators&FunctionsOperator:REGEXP_LIKEFunctions:REGEXP_INSTR REGEXP_SUBSTR REGEXP_REPLACE,Regular Expressions,Select zip from zipcode where regexp_like(zip,:digit:);,Regular Expressions,SELECT REGEXP_INSTR(Joe Smith,10045 Berry Lane,San Joseph,CA 91234-1234,:digit:5(-:digit:4)?$)AS starts_at FROM dual,Summary,This discussion has been an attempt at a light and lively introduction to the Oracle database world for those familiar with the Microsoft SQL Server database products.Much more in-depth examples are available in the references shown that follow,from which many of the examples were drawn and for which we can thank the authors involved.Welcome Aboard!,References,Oracle Migration Workbench Reference Guide for SQL Server and Sybase Adaptive Server Migrations,Release 9.2.0 for Microsoft Windows 98/2000/NT and Microsoft Windows XP,Part Number B10254-01Oracle Technology Network,OTN:http:/Better SQL Using Regular Expressions,By Alice Rischerthttp:/,