ORACLE和SQL语法区别归纳2.docx

1、ORACLE和SQL语法区别归纳2ORACLE和SQL语法区别归纳文章分类:数据库 数据类型比较 类型名称 Oracle SQLServer 比较 字符数据类型 CHAR CHAR 都是固定长度字符资料但oracle 里面最大度为2kb，SQLServer里面最大长度为8kb 变长字符数据类型 VARCHAR2 VARCHAR Oracle 里面最大长度为 4kb，SQLServer里面最大长度为8kb 根据字符集而定的固定长度字符串 NCHAR NCHAR 前者最大长度2kb后者最大长度4kb 根据字符集而定的可变长度字符串 NVARCHAR2 NVARCHAR 二者最大长度都为4kb 日期

2、和时间数据类型 DATE 有Datetime和Smalldatetime两种 在oracle 里面格式为DMY在SQLSerser里面可以调节，默认的为MDY 数字类型 NUMBER(P,S) NUMERICP(,S) Oracle 里面p代表小数点左面的位数，s代表小数点右面的位数。而SQLServer里面p代表小数点左右两面的位数之和，s代表小数点右面的位数。 数字类型 DECIMAL(P,S) DECIMALP(,S) Oracle 里面p代表小数点左面的位数，s代表小数点右面的位数。而SQLServer里面p代表小数点左右两面的位数之和，s代表小数点右面的位数。 整数类型 INTEGE

3、R INT 同为整数类型，存储大小都为4个字节 浮点数类型 FLOAT FLOAT 实数类型 REAL REAL ORACLE 内部函数大全以及与SQLSERVER的区别： 下面是Oracle 支持的字符函数和它们的Microsoft SQL Server等价函数。 函数 Oracle Microsoft SQL Server 把字符转换为ASCII :ASCII ASCII 字串连接: CONCAT -(expression + expression) 把ASCII转换为字符 CHR, CHAR 返回字符串中的开始字符（左起） INSTR ,-CHARINDEX 把字符转换为小写 LOWER

4、 -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 翻译字符串 TRANS

5、LATE -N/A 字符串长度 LENGTH- DATELENGTH or LEN 列表中最大的字符串 GREATEST- N/A 列表中最小的字符串 LEAST -N/A 如果为NULL则转换字串 NVL- ISNULL 日期函数 下面是Oracle 支持的日期函数和它们的Microsoft SQL Server等价函数。 函数 Oracle -Microsoft SQL Server 日期相加 (date column +/- value) or ADD_MONTHS -DATEADD 两个日期的差 (date column +/- value) or MONTHS_BETWEEN -DA

6、TEDIFF 当前日期和时间 SYSDATE -GETDATE() 一个月的最后一天 LAST_DAY -N/A 时区转换 NEW_TIME -N/A 日期后的第一个周日 NEXT_DAY -N/A 代表日期的字符串 TO_CHAR -DATENAME 代表日期的整数 TO_NUMBER (TO_CHAR) -DATEPART 日期舍入 ROUND -CONVERT 日期截断 TRUNC -CONVERT 字符串转换为日期 TO_DATE -CONVERT 如果为NULL则转换日期 NVL - ISNULL 转换函数 下面是Oracle 支持的转换函数和它们的Microsoft SQL Ser

7、ver等价函数。 函数 Oracle -Microsoft SQL Server 数字转换为字符 TO_CHAR -CONVERT 字符转换为数字 TO_NUMBER -CONVERT 日期转换为字符 TO_CHAR -CONVERT 字符转换为日期 TO_DATE CONVERT 16进制转换为2进制 HEX_TO_RAW -CONVERT 2进制转换为16进制 RAW_TO_HEX -CONVERT 其它行级别的函数 下面是Oracle 支持的其它行级别的函数以及它们的Microsoft SQL Server等价函数。 函数 Oracle -Microsoft SQL Server 返回第

8、一个非空表达式 DECODE -COALESCE 当前序列值 CURRVAL -N/A 下一个序列值 NEXTVAL -N/A 用户登录账号ID数字 UID -SUSER_ID 用户登录名 USER -SUSER_NAME 用户数据库ID数字 UID -USER_ID 用户数据库名 USER -USER_NAME 当前用户 CURRENT_USER - CURRENT_USER 用户环境(audit trail) USERENV -N/A 在CONNECT BY子句中的级别 LEVEL -N/A 合计函数 下面是Oracle 支持的合计函数和它们的Microsoft SQL Server等价函

9、数。 函数 Oracle -Microsoft SQL Server Average AVG - AVG Count COUNT -COUNT Maximum MAX - MAX Minimum MIN -MIN Standard deviation STDDEV -STDEV or STDEVP Summation SUM - SUM Variance VARIANCE -VAR or VARP 条件测试 Oracle 的DECODE语句和Microsoft SQL Server的CASE表达式都执行条件测试。 当test_value中的值和后面的任何表达式匹配的时候，相关的值就返回。如果没

10、有找到任何匹配的值，就返回default_value。 如果没有指定default_value，在没有匹配的时候，DECODE和CASE都返回一个NULL。下表显示了该语句的语法， 同时给出了转换DECODE命令的示例。 Oracle Microsoft SQL DECODE (test_value, expression1, value1 ,default_value ) CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(DECODE(grade ,A, 4 ,A+, 4.3 ,A-, 3.7

11、,B, 3 ,B+, 3.3 ,B-, 2.7 ,C, 2 ,C+, 2.3 ,C-, 1.7 ,D, 1 ,D+, 1.3 ,D-, 0.7 ,0),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN CASE input_expression WHEN when_expression THEN result_expression ELSE else_result_expression END CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(CASE grade

12、WHEN A THEN 4 WHEN A+ THEN 4.3 WHEN A- THEN 3.7 WHEN B THEN 3 WHEN B+ THEN 3.3 WHEN B- THEN 2.7 WHEN C THEN 2 WHEN C+ THEN 2.3 WHEN C- THEN 1.7 WHEN D THEN 1 WHEN D+ THEN 1.3 WHEN D- THEN 0.7 ELSE 0 END),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN CASE表达式可以支持用SELECT语句执行布尔测试，这是DECODE命令所不允许的。欲了解关于CASE表达式

13、的详细信息， 请参阅SQL Server联机手册。 把值转换为不同的数据类型 Microsoft SQL Server的CONVERT和CAST函数都是多目标转换函数。它们提供了相似的功能， 把一种数据类型的表达式转换为另一种数据类型的表达式，并且支持多种专门数据的格式。 CAST(expression AS data_type) CONVERT (data type(length), expression , style) CAST是一个SQL -92标准的函数。这些函数执行同Oracle 的TO_CHAR、TO_NUMBER、TO_DATE、HEXTORAW以及 RAWTOTEXT函数相同

14、的功能。 这里所指的数据类型是任何表达式将被转换成为的系统数据类型。不能使用用户定义的数据类型。长度参数是可选的， 该参数用于char、varchar、binary以及varbinary数据类型。允许的最大长度是8000。 转换 Oracle Microsoft SQL Server 字符到数字 TO_NUMBER(?) -CONVERT(numeric, ?) 数字到字符 TO_CHAR(10) -CONVERT(char, 10) 字符到日期 TO_DATE(?-JUL-97) TO_DATE(?-JUL-1997,dd-mon-yyyy) TO_DATE(July 4, 1997, Mo

15、nth dd, yyyy) -CONVERT(datetime, ?-JUL-97) CONVERT(datetime, ?-JUL-1997) CONVERT(datetime, July 4, 1997) 日期到字符 TO_CHAR(sysdate) TO_CHAR(sysdate, dd mon yyyy) TO_CHAR(sysdate, mm/dd/yyyy) -CONVERT(char, GETDATE() CONVERT(char, GETDATE(), 106) CONVERT(char, GETDATE(), 101) 16进制到2进制 HEXTORAW(?F)- CONVE

16、RT(binary, ?F) 2进制到16进制 RAWTOHEX(binary_column) -CONVERT(char, binary_column) 请注意字符串是怎样转换为日期的。在Oracle 中，缺省的日期格式模型是“DD- MON-YY”如果你使用任何其它格式， 你必须提供一个合适的日期格式模型。CONVERT函数自动转换标准日期格式，不需要任何格式模型。 从日期转换到字符串时，CONVERT函数的缺省输出是“dd mon yyyy hh:mm:ss:mmm(24h)”。 用一个数字风格代码来格式化输出，使它能输出为其它类型的日期格式模型。欲了解CONVERT函数的详细信息，请参

17、阅SQL Server联机手册。 下表显示了Microsoft SQL Server日期的缺省输出。 Without Century With Century Standard Output - 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM) 1 101 USA mm/dd/yy 2 102 ANSI yy.mm.dd 3 103 British/French dd/mm/yy 4 104 German dd.mm.yy 5 105 Italian dd-mm-yy 6 106 - dd mon yy 7 107 - mon dd, yy 8

18、108 - hh:mm:ss - 9 or 109 (*) Default milliseconds mon dd yyyy hh:mi:ss:mmm (AM or PM) 10 110 USA mm-dd-yy 11 111 Japan yy/mm/dd 12 112 ISO yymmdd - 13 or 113 (*) Europe default dd mon yyyy hh:mm:ss:mmm(24h) 14 114 - hh:mi:ss:mmm(24h) 用户定义函数 Oracle PL/SQL 函数可以在Oracle SQL 语句中使用。在Microsoft SQL Server中

19、一般可以通过其它方式来实现同样的功能。 在SQL Server中可以用表中给出的查询来代替。 Oracle Microsoft SQL Server SELECT SSN, FNAME, LNAME, ) TUITION_PAID, TUITION_PAID/GET_SUM_ MAJOR(MAJOR) AS PERCENT_MAJOR FROM STUDENT_ADMIN.STUDENT SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID/SUM_MAJOR AS PERCENT_MAJOR FROM STUDENT_ADMIN.STUDE

20、NT, (SELECT MAJOR, SUM(TUITION_PAID) SUM_MAJOR FROM STUDENT_ADMIN.STUDENT GROUP BY MAJOR) SUM_STUDENT WHERE STUDENT.MAJOR = SUM_STUDENT.MAJOR CREATE OR REPLACE FUNCTION GET_SUM_MAJOR (INMAJOR VARCHAR2) RETURN NUMBER AS SUM_PAID NUMBER; BEGIN SELECT SUM(TUITION_PAID) INTO SUM_PAID FROM STUDENT_ADMIN.

21、STUDENT WHERE MAJOR = INMAJOR; RETURN(SUM_PAID); END GET_SUM_MAJOR; No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax. 比较操作符 Oracle 和Microsoft SQL Server的比较操作符几乎是一样的。 算符 Oracle Microsoft SQL Server 等于 (=) (=) 大于 () () 小于 () (=) (=) 小于或等于 (=) (=) 不等于 (!=, , =) (!=, , =) 不大于，不小于 N/A !

22、 , ! 在集合中任意成员中 IN IN 不在集合中的任何成员中 NOT IN NOT IN 集合中的任意值 ANY, SOME ANY, SOME 提交集合中的所有值 != ALL, ALL, ALL, = ALL, !=SOME, SOME, SOME, =SOME != ALL, ALL, ALL, = ALL, !=SOME, SOME, SOME, =SOME 像模式（Like pattern） LIKE LIKE 不像模式（Not like pattern） NOT LIKE NOT LIKE X和y之间的值 BETWEEN x AND y BETWEEN x AND y 不在x和

23、y之间的值 NOT BETWEEN NOT BETWEEN 值存在 EXISTS EXISTS 值不存在 NOT EXISTS NOT EXISTS 值为|不为空 IS NULL, IS NOT NULL Same. Also = NULL, !=NULL for backward compatibility (not recommended). 模式匹配 SQL Server的LIKE关键字提供了有用的通配符搜索功能，这个功能在Oracle 中不支持 除了所有的RDBMS都支持的（%）和（_）通配符以外，SQL Server还支持（ ）和（）通配符。 （ ）字符用来查询在一个范围内的所有单个

24、字符。例如，如果你需要查询包含一个从a到f的字符的数据， 你可以这样写：“LIKE a-f”或者“LIKE abcdef”。这些附加的通配符的有效性在下表中给出。 Oracle Microsoft SQL SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE A% OR LNAME LIKE B% OR LNAME LIKE C% SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE ABC% 通配符用来标记那些不在特定范围内的字符。例如，如果除了a到f以外的所有字符都是可以接受的，你可以这

25、样书写： LIKE a - f或者LIKE abcdef。 欲了解关于LIKE关键字的详细信息，请参阅SQL Server联机手册。 在比较中使用NULL 尽管Microsoft SQL Server传统上支持SQL -92标准的和一些非标准的NULL行为，但是它还是支持Oracle 中的NULL的用法。 为了支持分布式查询，SET ANSI_NULLS必须设定为ON。 在进行连接的时候，SQL Server的SQL Server ODBC驱动程序和OLE DB提供者自动把SET ANSI_NULLS设定为ON。 这个设置可以在ODBC数据源、ODBC连接属性、或者是在连接到SQL Server之前在应用程序中设置的OLE DB连接属性中进行配置。 在从DB-Library应用程序中连接时，SET ANSI_NULLS缺省为OFF。 当SET ANSI_DEFAULTS为ON时，SET ANSI_NULLS被允许。 欲了解关于NULL用法的详细信息，请参阅SQL Server联机手册。 字串连接 Oracle 使用两个管道符号（|）来作为字串连接操作符，SQL Server则使用加号（+）。这个差别要求你在应用程序中做小小的修改。 Oracle Micr