sqlserver与oracle差异汇总.doc

SQL server 与Oracle开发比较本文档主要从oracle与sql server语法上进行差异性比较，诸如两者在管理，性能，优化等方面的差异不作比较。l 概念上区别1. Oracle是一种对象关系数据库管理系统（ORDBMS),而Sql server只是关系型数据库管理系统（RDBMS）.2. Oracle使用Internet文件系统，该系统基于Java的应用程序，可以使数据库基于成为Internet的开发平台；Sql server是基于windows3. 两者支持的命令类别差不多，数据定义语言，数据操纵语言，事务处理控制语言，数据控制语言.在Oracle中，在事务控制语言中除了commit,rollback等还多了一个 Savepoint，设置保存点。4. oracle sql的扩展叫PL/SQL,主要的结构化查询工具有sql*plus，isql*plus,pl/sql等，Ms sql的扩展叫Transact-SQL，主要的结构化查询工具就是自带的查询分析器5. 主要数据类型Orace:主要支持char ,varchar2,long,number,datetime,raw,long raw,clob,blob,bfie前面三个是Character数据类型，varchar2支持可变长度的字符串，long支持可变长度的字符数据，raw,long raw用于存储二进制数据，long raw可变长度最后三个是大对象（lob)数据类型,存储非结构化的信息，例如声音剪辑，视频文件CLOG表示Character Lob,可以存储大量的字符数据，它对于存储非结构化的XML文档非常有用。BLOG表示Binary LOG,此数据类型可以存储大型二进制对象，如图形、视频剪辑，声音文件等支不支持，money,货币Sql server主要支持的文本类型char ,varchar,nchar,nvarchar,text,ntext,image，货币类型 Money,二进制binary,varbinary等等l 开发语法A. SQL语法两者的SQL语法基本上是相同的，只是一些细节方面的问题一、 表的管理：修改表的结构，如增加，删除列，创建表修改表 1.修改表的列的数据类型，大小的定义不同，如下面修改数据类型。 Ms sql是ALTER TABLE table_name ALTER COLUMN col DECIMAL (5, 2) Oracle是Alter TABLE table_name modify(col decimal(5,2) 2.增加和删除表的列，两者是相同的 Alter tabe table_name add(col definition) Alter tabe table_name drop column col_name 3 .Oracle如果不要用某列，然后又不想删除，就可以将某个列标记为未用 Alter tabe table_name set unused(tel_no).创建临时表Oracle的语法是：Create global temporary table aa(col number) on commit preserve rows;临时表只在用户会话时存在的表，或其数据在用户事务处理期间可持续存在的表,创建临时表时可以指定该表是否应当在用户会话期间持续存在（使用on commit preserve rows）On commit delete rows表示事务处理完成后删除它的行 Ms sql的语法是: Select * into #temptable from existed table二、 查询方面：1. 查表的前N行记录oracle是用rownum如select * from table_name where rownumn Ms sql是用top如select top n * from table_name2. 查询表的结构Orace可以通过desc来查看表的结构语法是：desc table_name或者使用数据字典表user_tab_coumns也可以查看到Select column_name,data_type from user_tab_coumns Where table_name= Ms sql可以通过一些系统的存储过程来看表的结构语法是：exec sp_help table_name3. 将一个表的数据添加到另外一个表中a.新表存在前提下：两者语法是一样的，如 insert into newtable（col1）select col1 from old_tableb.新表不存在前提下，可以用oracle可以用Create tabe new _table_name as select * from old_table ms sql可以用Select * into new_table from old_table三、 操作符 1. 连接操作符Oracle是|;Ms sql是+ 2. Oracle的比较操作符中不等于除了之外，还有一个!= 3.算术操作符，都是+-*/；逻辑操作符都是and,not,or(相同点) 4. oracle集合操作符除了union,union all之外还加入intersect,minus,Intersect是仅返回两个查询都有行，minus返回第一个查询有第二个查询没有的行四、 函数1. 转换函数 Oracle中有to_char(),to_date(),to_number() Ms sql中有cast,convert2. 系统日期：Oracle:sysdateMs sql: getdate()如:Select sysdate from dual一定要from子句 Select getdate()不一定要from子句3. Decode函数相当if else，或者ms中的case语句，语法是decode(value,if1,then1,if2,then2.)如decode(col1,1,true,0,fase)4. 常规函数Nvl语法是NVL(EXP1,EXP2)表示如果ex1为空则返回ex2Nvl2语法是nvl(exp1,exp2,exp3)表示如果ex1为空，则返回ex3,否则返回ex2Nullif语法是nullif(ex1,ex2)如果这两个表达式相等则返回空coalesce语法是coalesce（ex1,ex2,.exn）返回第一个非空表达式5. 分组函数Oracle中的分组函数Rollup,cubeRollup返回的结果集包含分组行和小计行，cube产生交叉报表如:Select a,b,sum(c) from tabname group by rollup(a,b)Select a,b,sum(c) from tabname group by cube(a,b) Ms sql中的分组函数compute和compute by Compute子句为行聚集函数生成汇总值，该汇总值做为一个附加的行显示在结果集中。没有GROUP BY子句的情况下，也可以使用关键字COMPUTE.此关键字使用MAX,MIN,SUM,COUNT,AVG等函数生成汇总值，而compute by则在控制中断时给出该汇总值,compute by必须包括在order by子句中。还有很多函数，诸如日期函数，字符串函数等等，就不一一作比较了。 Oracle中还有很多比较好的分析函数，也不列举了。五、 Oracle中的数据库对象同义词作用：简化SQL语句；隐藏对象的名称和所有者，提供对象的公共访问等。语法：CREATE PUBLIC SYNONYM seqname FOR OBJECT序列用来生成唯一、边续的整数，它通常用来自动生成主键或唯一值的键。创建序列：create sequence seqname increment by 1 start with 1 maxvalue 10 minvalue 1 cycle cache访问序列： Select seqname.nextval from dual将返回序列的初始值 Select seqname.currval from dual返回序列的当前值六、 Oracle中的对象类型1.抽象数据类型此类型是包含一个或多个子类型的数据类型，并且这些数据类型并不局限于标准的oracle数据类型如：create orreplace typet_mmas object(col number(3), col2 varchar2(20)/此时可以在创建表时使用该类型，如下 Create table test (aa varchar2(5),bb t_mm, cc number(10)插入记录： Insert into test values(cccc,t_mm(1,col)类型声明：用户定义的数据类型也可以声明为final,not final,instantiable, not instantiabe Not final表示允许类型派生子类型。默认是final create orreplace typet_mmas object(col number(3), col2 varchar2(20) Not final not instantiabe表示类型没有构造函数。2.可变数组可变数组有助于在单个行中存储和重复记录的属性。数据与表中的其它数据是存储在一起的，有限数目的行，不能被索引,创建可变数组的语法是： create typearray1as varray(5) of varchar2(5)向可变数组中插入记录 Insert into test1 values (2ee,array1(1,2,3,4,5) ) Select * from test1结果集如下 2ee 1,2,3,4,5 Select col2 from test1结果集如下 1,2,3,4,5 Select * from table(select t.col2 from test1 where col1=2ee);结果集如下 1 2 3 4 53.嵌套表它是包含在表中的表，对每行数据项数目没有限制，一个表在另一表中是作为一列，主表中的每一行的嵌套表可以包含若干行。创建嵌套表先创建一个类型 Create type ord_ty as object(itemcode varchar2(5),qty _ord number(5), Qty_held number(5);创建另一个抽象数据类型，即嵌套表数据类型 Create type ord_nt as table of ord_ty创建嵌套表 Create table order_m as (orderno varchar2(5),odate date, vencode varchar2(5), dets ord_nt) nested table dets store as ord_nt_tab;向嵌套表中插入数据： Insert into order_m as values（001，to_date(18-07-08,DD-MM-YY), V009,ord_nt( ord_ty(i001,10,5),ord_ty(i002,34,2);更新嵌套表的值： Update table(select e.dets from order_mas e wheree.orderno=001) p Set value（p) =ord_ty(i090,8,9) where p.itemcode=i001;删除嵌套表的值： Delete from table(select e.dets from order_mas e wheree.orderno=001) p where p.itemcode=i001;4.对象表在对象表中每一行都是一个行对象，对象表与关系表不同：对象表中的每一行都有一个OID值，即对象标识符值。该值是在创建行时分配的。可以使用create table命令来创建对象表。在创建对象类型时，Oracle中是不允许为属性定义约束条件，但是可以在创建对象表时为对象类型的属性指定约束条件。 Create table vend_master of vend_ty(vencode constraint vc_pk primary key);创建对象表与关系表语法不同表的使用方法不同插入数据可以使用抽象数据类型的构造函数，如果对象表所基于抽象数据类型又基于另一抽象数据类型，则必须多个构造函数的嵌套调用。 Insert into vend_master values (vend_ty();5.对象视图借助对象视图可以将面向对象的结构（如抽象数据类型）应用于现有已经投入使用的表，而不需重建整个应用程序B. PL/SQL与T_SQL语法一、 批处理SQL的能力毕竟有限，诸如事务处理方面，批处理于是oracle与ms都把它进行了扩展，oracle的扩展叫PL/SQL由声明部分，可执行部分，异常处理部分组成顺序如下：Declare declarations Begin Executable statements Exception Handles End Ms的扩展叫Transact_SQL,简称T_SQL.批处理：就是一次执行处理一组命令的过程。GO关键字樗着批处理的结束。如use master go复杂一点也是由三部分组成：声明部分，可执行部分，异常处理部分。 不同的是，异常处理部分一般用跳转语句来实现。例如： Declare declarations Begin Excutable statements IF ERROR 0 GOTO ERROR End ERROR: BEGINRAISERROR(20058,16,-1)return(1)END二、 逻辑控制语句 1.控制结构： Oracle If condition then .End if Caseselector when exp1 then statements Else statementsEnd case SQL SERVER IF condition Sql statements ELSE Sql statements Case selector When ex1 then statements Elsestatements End 2.迭代结构 oracle Loopstatements end loop; While condition Loopstatementendloop; Sql server While condition Begin Sql statement End面象对象编程涉及的概念有对象，类，属性和方法，面向对象的三大特性是：封装，继承和多态。将数据和函数包装到一个单元中的过程称为封装。不能从外部访问数据，只能包装在类中的那些函数才能访问数据继承可以是SQL类型的继承和方法的继承。多态是一个对象可以呈现多种形式的能力，这使得不同的对象可以具有相同的名称的方法，这些方法实现的任务相似，但实现方式却不同。三、 变量与常量Pl/sql变量与常量可以具有属性，支持的属性类型有%type,%rowtype声明引用数据库列或变量的数据类型的变量时，可以使用%type属性。如：Declare Variable_nametable_name.col_name %type使用这个优点是，不需要知道列vencode的准确数据类型 %rowtype属性提供表示表中行的记录类型。四、 过程与函数(Procedure & Function) a.建立存储过程的语法不同： Ms sql的语法是： Create procedure procedure_name ( Id int =null, name varchar(10) output )as变量定义区begin sql_statement end-CREATE FUNCTION function_name (DATE datetime)RETURNS intAS beginsql statement end面向对象的特性之一是封装，程序包就是对相关PL/SQL类型，子程序，游标，异常，变量，和常量的封装，它包含两部分程序包规格说明和程序包主体在包规格说明书中，可以声明类型，变量，常量，异常，游标，子程序程序包主体实现在程序包规格中定义的游标、子程序包头语法部分：Create or replace package package_name is|asPublic type and item declarationsSubprogram specificationsEnd package_name实例如下：Create or replace package pack_me isProcedureorder_proc(orno varchar2);Function order_fun(ornos varchar2) return varchar2;End pack_me包体语法部分：Create or replace package body package_name is|asPrivate type and item decarationsSubprogram bodiesEnd package_name;包体具体实例就不写了，包头只是起一个声明作用，具体实现部分都在包体里面。下面是创建存储过程的语法： Create or replace procedure procedure_namearg1 in|out|in outtype is|as 变量定义区 Begin Execute Sql statement Exception handlersEnd注意：参数列表那里,oracle是先定义参数是输入还是输出参数，然后再定义参数类型;而sql server正好相反创建函数的语法：Create function function_name argumentReturn datatype is|asLocal decarationBeginExcutable statementExceptionHandlesEnd; b.变量赋值1. Oracle里的用法：存储过程中边查询边给变量赋值。select某一列名into变量名from table where .;相当于sql server中的select变量名=列. From table where .注意：select * /某一列名into表名 2. Oracle直接赋值的符号是： := 五、 触发器(Trigger) Oracle Create or replace trigger trigger_name before/afterinsert/update/delete on table_name变量声明 begin Sql statement endMs sqlCreate trigger trigger_name on tablefor |after|instead ofinsert|update|deleteas sql_statements六、 游标(Curcor) Oracle中提供两种游标类型，它们是静态游标和ref游标静态游标又分为隐式游标与显式游标Ref游标，游标变量是一种引用类型隐式游标属性包括%notfound,%found,%rowcount,%isopen如：Begin Delete from ta where ord=ddd If sql%notfound then Dbms_output.put_line(未找到值) Else Dbms_output.put_line（找到并删除之） End ifEnd显示游标：可以用下面语句控制游标 Open cursorname Fetch cursor_name into var_name Close cursor_name创建游标语法： Oracle: declare variable Cursor test is select* from order Sql server Declare test cursorforselect* from order七、 错误处理(Exception & test & debug)有两种类型的异常：一种预定义，另外一种是用户自定义预定义的类型有很多，如No_data_found,Cursor_already_open对于Oracle的调试，可以借助第三方工具，如toad,Pl.sql Developer,对于sql server采用变量或者print形式进行调试 Oracle异常定义部分的示例： Exception When then statements When others then Statements End;其中是系统预定义的名字。 Raiser_application_error用于创建用户定义的错误信息的过程，用户定义的错误消息 可以指定的异常描述的更详细 Sql server在错误处理上多采用自定义。或者是用goto跳转的方式如： Begin Sql statements if error0 goto error end Error: Return;或是直接用raiserror iferror0beginraiserror(发生错误.,-1,-1)return (1)endl SQLServer和Oracle的常用函数对比 数值函数1.绝对值 S:select abs(-1) valueO:select abs(-1) value from dual2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual3.取整（小） S:select floor(-1.001) value O:select floor(-1.001) value from dual4.取整（截取）S:select cast(-1.002 as int) value O:select trunc(-1.002) value from dual 5.四舍五入S:select round(1.23456,4) value 1.23460O:select round(1.23456,4) value from dual 1.23466.e为底的幂 S:select Exp(1) value 2.7182818284590451 O:select Exp(1) value from dual 2.718281827.取e为底的对数S:select log(2.7182818284590451) value 1O:select ln(2.7182818284590451) value from dual; 18.取10为底对数S:select log10(10) value 1O:select log(10,10) value from dual; 19.取平方S:select SQUARE(4) value 16O:select power(4,2) value from dual 1610.取平方根S:select SQRT(4) value 2O:select SQRT(4) value from dual 211.求任意数为底的幂S:select power(3,4) value 81O:select power(3,4) value from dual 8112.取随机数S:select rand() value O:select sys.dbms_random.value(0,1) value from dual;13.取符号S:select sign(-8) value -1O:select sign(-8) value from dual -1-数学函数14.圆周率S:SELECT PI() value 3.1415926535897931O:不知道15.sin,cos,tan 参数都以弧度为单位例如：select sin(PI()/2) value 得到1（SQLServer）16.Asin,Acos,Atan,Atan2 返回弧度17.弧度角度互换(SQLServer，Oracle不知道)DEGREES：弧度-角度RADIANS：角度-弧度-数值间比较18. 求集合最大值S:select max(value) value from (select 1 valueunionselect -2 valueunionselect 4 valueunionselect 3 value)aO:select greatest(1,-2,4,3) value from dual19. 求集合最小值S:select min(value) value from (select 1 valueunionselect -2 valueunionselect 4 valueunionselect 3 value)aO:select least(1,-2,4,3) value from dual20.如何处理null值(F2中的null以10代替)S:select F1,IsNull(F2,10) value from TblO:select F1,nvl(F2,10) value from Tbl-数值间比较字符串函数21.求字符序号S:select ascii(a) valueO:select ascii(a) value from dual22.从序号求字符S:select char(97) valueO:select chr(97) value from dual23.连接S:select 11+22+33 valueO:select CONCAT(11,22)|33 value from dual23.子串位置 -返回3S:select CHARINDEX(s,sdsq,2) value O:select INSTR(sdsq,s,2) value from dual23.模糊子串的位置 -返回2,参数去掉中间%则返回7S:select patindex(%d%q%,sdsfasdqe) value O:oracle没发现，但是instr可以通过第四霾问刂瞥鱿执问?BRselect INSTR(sdsfasdqe,sd,1,2) value from dual 返回624.求子串S:select substring(abcd,2,2) value O:select substr(abcd,2,2) value from dual25.子串代替 返回aijklmnefS:SELECT STUFF(abcdef, 2, 3, ijklmn) valueO:SELECT Replace(abcdef, bcd, ijklmn) value from dual26.子串全部替换S:没发现O:select Translate(fasdbfasegas,fa,我 ) value from dual27.长度S:len,datalengthO:length28.大小写转换 lower,upper29.单词首字母大写S:没发现O:select INITCAP(abcd dsaf df) value from dual30.左补空格（LPAD的第一个参数为空格则同space函数）S:select space(10)+abcd valueO:select LPAD(abcd,14) value from dual31.右补空格（RPAD的第一个参数为空格则同space函数）S:select abcd+space(10) valueO:select RPAD(abcd,14) value from dual32.删除空格S:ltrim,rtrimO:ltrim,rtrim,trim33. 重复字符串S:select REPLICATE(abcd,2) value O:没发现34.发音相似性比较(这两个单词返回值一样，发音相同)S:SELECT SOUNDEX (Smith), SOUNDEX (Smythe)O:SELECT SOUNDEX (Smith), SOUNDEX (Smythe) from dualSQLServer中用SELECT DIFFERENCE(Smithers, Smythers) 比较soundex的差返回0-4，4为同音，1最高日期函数35.系统时间S:select getdate() valueO:select sysdate value from dual36.前后几日直接与整数相加减37.求日期S:select convert(char(10),getdate(),20) valueO:select trunc(sysdate) value from dualselect to_char(sysdate,yyyy-mm-dd) value from dual38.求时间S:select convert(char(8),getdate(),108) valueO:select to_char(sysdate,hh24:mm:ss) value from dual39.取日期时间的其它部分S:DATEPART 和 DATENAME 函数 （第一个参数决定） O:to_char函数 第二个参数决定参数-下表需要补充year yy, yyyy quarter qq, q (季度)month mm, m (m O无效)dayofyear dy, y (O表星期)day dd, d (d O无效)week wk, ww (wk O无效)weekday dw (O不清楚)Hour hh,hh12,hh24 (hh12,hh24 S无效)minute mi, n (n O无效)second ss, s (s O无效)millisecond ms (O无效)-40.当月最后一天S:不知道O:select LAST_DAY(sysdate) value from dual41.本星期的某一天（比如星期日）S:不知道O:SELECT Next_day(sysdate,7) vaule FROM DUAL;42.字符串转时间S:可以直接转或者select cast(2004-09-08as datetime) valueO:SELECT To_date(2004-01-05 22:09:38,yyyy-mm-dd hh24-mi-ss) vaule FROM DUAL;43.求两日期某一部分的差（比如秒）S:select datediff(ss,getdate(),getdate()+12.3) valueO:直接用两个日期相减（比如d1-d2=12.3）SELECT (d1-d2)*24*60*60 vaule FROM DUAL;44.根据差值求新的日期（比如分钟）S:select dateadd(mi,8,getdate() valueO:SELECT sysdate+8/60/24 vaule FROM DUAL;45.求不同时区时间S:不知道O:SELECT New_time(sysdate,ydt,gmt ) vaule FROM DUAL;-时区参数,北京在东8区应该是Ydt-AST ADT 大西洋标准时间BST BDT 白令海标准时间CST CDT 中部标准时间EST EDT 东部标准时间GMT 格林尼治标准时间HST HDT 阿拉斯加夏威夷标准时间MST MDT 山区标准时间NST 纽芬兰标准时间PST PDT 太平洋标准时间YST YDT YUKON标准时间 Oracle支持的字符函数和它们的Microsoft SQL Server等价函数:函数 Oracle Microsoft SQLServer 把字符转换为ASCII ASCII ASCII 字符串连接 CONCAT (expression + expression) 把ASCII转换为字符 CHR CHAR 返回字符串中的开始字符（左起） INSTR CHARINDEX 把字符转换为小写 LOWER LOWER 把字符转换为大写 UPPER UPPER 填充字符串的左边 LPAD N/A 清除开始的空白 LTRIM LTRIM 清除尾部的空白 RTRIM RTRIM 字符串中的起始模式（pattern） INSTR PATINDEX 多次重复字符串 RPAD REPLICATE 字符串的语音表示 SOUNDEX SOUNDEX 重复空格的字符串 RPAD SPACE 从数字数据转换为字符数据 TO_CHAR STR 子串 SUBSTR SUBSTRING 替换字符 REPLACE STUFF 将字符串中的每个词首字母大写 INITCAP N/A 翻译字符串 TRANSLATE N/A 字符串长度 LENGTH DATELENGTH or LEN 列表中最大的字符串 GREATEST N/A 列表中最小的字符串 LEAST N/A 如果为NULL则转