ORACLE和SQL语法区别归纳

       字符数据类型 CHAR CHAR 都是固定长度字符资料但oracle 里面最大度为2kb，SQLServer里面最大长度为8kb

　　变长字符数据类型 VARCHAR2 VARCHAR Oracle 里面最大长度为 4kb，SQLServer里面最大长度为8kb

　　根据字符集而定的固定长度字符串 NCHAR NCHAR 前者最大长度2kb后者最大长度4kb

　　根据字符集而定的可变长度字符串 NVARCHAR2 NVARCHAR 二者最大长度都为4kb

　　日期和时间数据类型 DATE 有Datetime和Smalldatetime两种 在oracle 里面格式为DMY在SQLSerser里面可以调节，默认的为MDY

　　数字类型 NUMBER(P,S) NUMERIC[P(,S)] Oracle 里面p代表小数点左面的位数，s代表小数点右面的位数。而SQLServer里面p代表小数点左右两面的位数之和，s代表小数点右面的位数。

　　数字类型 DECIMAL(P,S) DECIMAL[P(,S)] Oracle 里面p代表小数点左面的位数，s代表小数点右面的位数。而SQLServer里面p代表小数点左右两面的位数之和，s代表小数点右面的位数。

　　整数类型 INTEGER 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 ---------------------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则转换字串 NVL-------------------- ISNULL

　　日期函数

　　下面是Oracle 支持的日期函数和它们的Microsoft SQL Server等价函数。

　　函数 Oracle --------------------Microsoft SQL 　Server

　　日期相加 (date column +/- value) or ADD_MONTHS --------------------DATEADD

　　两个日期的差 (date column +/- value) or MONTHS_BETWEEN --------------------DATEDIFF

　　当前日期和时间 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 Server等价函数。

　　函数 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

　　返回第一个非空表达式 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等价函数。

　　函数 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中的值和后面的任何表达式匹配的时候，相关的值就返回。如果没有找到任何匹配的值，就返回default_value。

　　如果没有指定default_value，在没有匹配的时候，DECODE和CASE都返回一个NULL。下表显示了该语句的语法，

　　同时给出了转换DECODE命令的示例。

　　Oracle Microsoft SQL

　　DECODE (test_value,

　　expression1, value1

　　<,expression2, value2] […>

　　[,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

　　,'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

　　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表达式的详细信息，

　　请参阅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函数相同的功能。

　　这里所指的数据类型是任何表达式将被转换成为的系统数据类型。不能使用用户定义的数据类型。长度参数是可选的，

　　该参数用于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', 'Month 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')-------------------- CONVERT(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函数的详细信息，请参阅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 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中一般可以通过其它方式来实现同样的功能。

　　在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.STUDENT,

　　(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.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 !> , !<

　　在集合中任意成员中 IN IN

　　不在集合中的任何成员中 NOT IN NOT IN

　　集合中的任意值 ANY, SOME ANY, SOME

　　提交集合中的所有值 != ALL, <> ALL, < ALL,

　　> ALL, <= ALL, >= ALL, !=　SOME, <>　SOME,

　　<　SOME, >　SOME,

　　<=　SOME, >=　SOME != ALL, <> ALL, < ALL,

　　> ALL, <= ALL, >= ALL, !=　SOME, <>　SOME,

　　<　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和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还支持（[ ]）和（[^]）通配符。

　　（[ ]）字符用来查询在一个范围内的所有单个字符。例如，如果你需要查询包含一个从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以外的所有字符都是可以接受的，你可以这样书写：

　　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联机手册。