Oracle与SQL Server的SQL使用区别.doc

Oracle与SQL Server的使用区别整理人：阮淑芳日期：2005-12-15一、数据类型序号内容（SQL SERVER）T-SQL（ORACLE）PL/SQL备注11数字类型分为精确数值、近似数值、整数、二进制数、货币等几类，其中，精确数值有DECIMAL(P,S)与NUMERIC(P,S)； 近似数值有FLOAT(N)；整数有INT、SMALLINT、TINYINT；二进制数有BINARY(N)、VARBINARY(N)；货币有MONEY、SMALLMONEY。NUMBERDECIMAL(P,S)NUMBER(P,S)NUMERIC(P,S) NUMBER(P,S)FLOAT(N) NUMBER(N)INTNUMBERSMALLINTNUMBERTINYINTNUMBERMONEYNUMBER19,4SMALLMONEYNUMBER19,412字符类型CHAR(N)VARCHAR(N)CHARVARCHAR2CHAR(N) CHAR(N)VARCHAR(N) VARCHAR2(N)13日期时间DATETIMESMALLDATETIMEDATEDATETIME DATESMALLDATETIME DATE14特殊类型BITTIMESTAMPTEXTIMAGEBINARYVARINGLONGRAWLONGRAWBLOBCLOBBFILETEXT CLOBIMAGE BLOBBIT NUMBER（1）二、自动增长字段序号内容（SQL SERVER）T-SQL（ORACLE）PL/SQL备注21数据表创建时某一字段定义为IDENTITY列，则该列为标识列。向该表添加新行时，该列值自动增长。以做主键识别没有自动增长字段的定义，但可通过创建序列（SEQUENCE）实现。 SQL SERVER向ORACLE移植做如下改变：1、去掉建表语句中有关ID列的identity声明关键字；2、创建SEQUENCE，将此SEQUENCE与需类ID化的列对应；3、在INSERT语句中对相应列引用其SEQUENCE值：SEQUENCENAME.NEXTVAL三、常用函数序号内容（SQL SERVER）T-SQL（ORACLE）PL/SQL备注31字符类函数Ascii(char_exp)Char(int_exp)Len(char_exp)Substring(exp,start,length)Upper(char_exp)Lower(char_exp)Stuff(char_exp1,start,length,Char_exp2)Ltrim(char_exp)Rtrim(char_exp)Ascii(str_exp)Chr(int_exp)Length(str_exp)Substr(exp,start,length)Upper(str_exp)Lower(str_exp)Translate(str_exp,from_str,to_str)Ltrim(str_exp1,str_exp2)Rtrim(str_exp1,str_exp2)Oracle的Lengthb(str_exp)返回字符串的字节数。32日期类函数Getdate()Sysdate将所有的GETDATE全部转换成为SYSDATE33数学类函数Abs(numeric_exp)Ceiling(numeric_exp)Exp(float_exp)Floor(numeric_exp)Power(numeric_exp,int_exp)Round(numeric_exp,int_exp)Sign(int_exp)Sqrt(float_exp)Abs(number_exp)Ceil(number_exp)Exp(number_exp)Floor(number_exp)Power(number_exp1,number_exp2)Round(number_exp1,number_exp2)Sign(number_exp)Sqrt(number_exp)3.4转换函数Convert(datatype(length),exp,format)onvert(datatype(length),exp,format)Convert(datatype(length),exp,format)To_char(datatype,str_format)To_date(str_exp,date_format)To_number(str_exp,num_format)迁移时将所有的convert全部转换成为to_char或trunc3.5其他函数AVGCOUNTMAXMINSUMSTDEVVARISNULL(check_exp,replace_value)CASEAVGCOUNTMAXMINSUMSTDDEVVARIANCENVL(check_exp,replace_value)DECODE1)ISNULL与NVL在MSSQL中ISNULL;在ORACLE中，同样的功能用NVL实现。2)CASE与DECCODE对某个值进行多个判断分支进行处理.四 、常用SQL语法序号内容（SQL SERVER）T-SQL（ORACLE）PL/SQL备注41使用局部变量1变量定义 DECLAREvariable_namedatatype,2给变量赋值“SELECT局部变量名=所赋值（初始值或数据库表的字段值或表达式）”；例：declareint_varintselectint_var=12或selectint_var=au_lDfromauthorswhereau_name=123-45-67891变量定义：VARIABLE_NAMEDATATYPE:=INITIALVALUE;2给变量赋值“局部变量名:=所赋值（初始值或表达式）;”，或“SELECT数据库表的字段值INTO局部变量名”。如：v_Num:=1;或v_stringvarchar2(50);SELECTfirst_nameINTOv_StringFROMstudentsWHEREid=v_Num;迁移时将所有的selctvar=columnfromtablewherecondition改成selectcolumnintovarfromtablewherecondition;将所有的selctvar1=var2改成var1:=var2;42标准控制结构1定义语句块BEGINStatementsEND2IF.ELSE语句IFboolean_expressionstatement|statement_blockELSEstatement|statement_block3IFEXISTS语句IFnotEXISTS(select_statement)statement|statement_blockELSEstatement|statement_block4循环语句：WHILEWHILEboolean_conditionstatement|statement_blockBREAKcondition5RETURN语句用于无条件退出一个批处理、存储过程或触发器;用于存储过程中返回状态值。1定义语句块BEGINStatements；END；2IF.THEN.ELSE语句IFboolean_expression THENstatement|statement_block；.ELSEstatement|statement_block；ENDIF；3循环语句A、LOOP循环LOOPstatement|statement_block；EXITWHENcondition；ENDLOOP；B、WHILE循环语句WHILEconditionLOOPstatement|statement_block；ENDLOOP；C、数字式FOR循环语句FORloop_counterINREVERSElow_bound.high_boundLOOPstatement|statement_block；ENDLOOP；43T-SQL中的游标提取循环语句：1) FETCHNEXTFROMcursor_nameINTOvariable_1,.variable_n2) WHILEFETCH_STATUS=0BEGINOther_statements FETCHNEXTFROMcursor_nameINTOvariable_1,.variable_nEND3)CLOSEcursor_name44事务处理语句：1开始一个事务：BEGINTRANSACTIONtransaction_name2提交一个事务：COMMITTRANSACTIONtransaction_name3回滚一个事务：ROLLBACKTRANSACTIONtransaction_name4使用事务保存点：BEGINTRANSACTIONtransaction_nameSAVETRANSACTIONsavepoint_nameROLLBACKTRANSACTIONsavepoint_nameCOMMITTRANSACTIONtransaction_name在PL/SQL中，事务的开始位置是从前一个事务结束以后执行的第一条SQL语句，或者在连接到该数据库以后所执行的第一条SQL语句。事务的结束是使用COMMIT或ROLLBACK语句标识的。1COMMIT的语法是：COMMITwork;可选的关键字work用来提高可读性。2ROLLBACK的语法是：ROLLBACKwork;可选的关键字work用来提高可读性。3ROLLBACK语句会撤消整个事务，如果使用SAVEPOINT命令，那么只有部分的事务需要被撤消，其语法是：SAVEPOINTname;这里name是保存点的名字。五 、存储过程序号内容（SQL SERVER）T-SQL（ORACLE）PL/SQL备注51定义CREATEPROCEDUREprocedure_name/*输入、输出参数的声明部分*/ASDECLARE/*局部变量的声明部分*/BEGIN/*主体SQL语句部分*/*游标声明、使用语句在此部分*/ENDCREATEORREPLACEPROCEDUREprocedure_name(/*输入、输出参数的声明部分*/)AS/*局部变量、游标等的声明部分*/BEGIN/*主体SQL语句部分*/*游标使用语句在此部分*/EXCEPTION/*异常处理部分*/END；1）PL/SQL中除了引用%TYPE和%ROWTYPE之外,不能在定义参数数据类型时给出长度和精度。 （当过程被调用执行，有实际的数据传递进来，参数的长度才被确定下来）2）ORACLE的存储过程不支持用select子句直接返回一个数据集。3）不能在ORACLE的存储过程中建数据表5.2执行Execute 过程名 （参数）CALL 过程名（参数）5.3结果返回可返回结果集不可返回结果集六 、自定义函数序号内容（SQL SERVER）T-SQL（ORACLE）PL/SQL备注61定义CREATE FUNCTION function_name（参数）RETURNSAsBEGIN End可定义三类函数：标量函数、内嵌表值函数、多语句表值函数CREATEORREPLACEFUNCTIONfunction_name (参数)RETURNreturn_typeIS|ASBEGINEND;每个函数都必须有一个return子句，因为在定义上函数必须返回一个值给调用环境。SQL SERVER2000有自定义函数对象，之前版本不支持。62调用需在函数名的前面加上前缀owner_name.function_name (参数)直接调用6.3结果返回标量函数返回单值；内嵌表值函数和多语句表值函数返回数据集；返回单一结果集说明：1）ORACLE中过程与函数的区别函数可以返回一个值给调用环境；而过程不能，过程只能通过返回参数（带“OUT”或“INOUT”）传回去数据。2）对于有返回单值的MSSQL存储过程，在数据库移值最好转换成ORALCE的函数；对于MSSQL有大量数据的处理而又不需返回值的存储过程转换成ORACLE的过程。七 、临时表的使用序号（SQL SERVER）T-SQL（ORACLE）PL/SQL备注71支持临时表ORALCE8i以上的版本才支持临时表，其创建语法为CREATEGLOBALTEMPORARYTABLEtable_name(clomn1type,column2type);迁移时，建议不再使用临时表，或把临时表在后台实际化即将之创建为正式表。八 、其他使用序号内容（SQL SERVER）T-SQL（ORACLE）PL/SQL备注81字符串大小写问题在SQL SERVER安装时选择是否区分大小写。区分大小写（用单引号括起来的字符区分大小写）82与运算&操作符Expr1 & Expr1使用函数BITANDBITAND (Expr1, Expr1)83或运算| 操作符Expr1 | Expr1使用自定义函数BITORBITOR (Expr1, Expr1)84返回字符长度Len(char_exp)使用自定义函数LENLEN(char_exp)8.5 NULL值替换ISNULL（Check_Exp1,ReplaceVal）可以是任何数据类型使用自定义函数ISNULLISNULL(Check_Char,ReplaceChar）只对字符类型使用数字类型使用NVLNVL(Check_VAL,ReplaceVAL）8.6字符串连接Str1 + Str2Str1 Str2或CONCAT(Str1 ,Strr2)87取模运算%提供两数相除后的余数。VAL1 % VAL2MOD MOD（VAL1，VAL2）8.8整数/整数整数/整数=整数如：3/2=1FLOOR(整数/整数）=整数如：3/2=1.5 FLOOR(3/2)=1 4/2=28.9返回由重复的空格组成的字符串SPACE(n)使用自定义函数SPACESPACE(n)8.9对表中不存在数据（函数）的返回直接使用SELECT 如：SELECT GETDATE() SELECT ABC AS A必须使用虚拟表DUAL，该表只包含单行。SELECT Exp FROM DUAL如：SELECT SYSDATE FROM DUAL8.10表别名允许使用AS如：SELECT * FROM ZD_RY AS RY或SELECT * FROM ZD_RY RY不允许使用AS如：SELECT * FROM ZD_RY RY为统一不再使用AS 8.11字段别名方式1：SELECT XM=MC FROM ZD_RY 方式2：SELECT MC XM FROM ZD_RY 方式3：SELECT MC AS XM FROM ZD_RY 方式1：SELECT MC XM FROM ZD_RY 方式2：SELECT MC AS XM FROM ZD_RY为统一T-SQL不再使用方式1 8.12SELECT * 使用若SELECT 语句加入非FORM表中的字段，则*前必须加表别名如：SELECT RY.*,SPACE(1) AS S FROM ZD_RY RY8.13十六进制表示0X如：SELECT * FROM ZD_KSWHERE FLAG & 0XA1=0使用自定义函数FUNC_HEXTODEC如：SELECT * FROM ZD_KS WHERE BITAND(FLAG, FUNC_HEXTODEC(0XA1)=08.14使用EXISTS子查询指定一个子查询，检测行的存在返回 TRUE 或 FALSE 值。确定一个子查询是否存在一个条件，结果为TRUE或FALSE。只能用在SELECT语句的WHERE条件下。如： select * from ZD_RY where EXISTS(select BM from ZD_KS where ZD_RY.KSBM=ZD_KS.BM );不能如下使用IF EXISTS(SELECT * FROM ZD_KS) THEN.END IF ;8.15单引用/双引号在 SQL 语句中使用单引号来定义字符串，用双引号来定义特殊的或小写字符的标识符 ( 如表名等 )8.16NULL值处理select * from zd_ks where ISNULL(mc,)等同于select * from zd_ks where mc is not null 搜索NULL值时，不能使用等号（=），如需要识别包含一个NULL值的记录，则必须使用IS NULL；如：1)select * from zd_ks where NVL(mc,) （等同NVL(mc,)null）没有结果出2)select * from zd_ks where mc is not null 有结果出8.17多语句在DELPHI中的执行例：declare c Integer select c=count(*) from zd_ry B1