数据类型比较
类型名称 Oracle 字符数据类型 变长字符数据类型 根据字符集而定的固定长度字符串 根据字符集而定的可变长度字符串 日期和时间数据类型 数字类型 数字类型 整数类型 浮点数类型 实数类型 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 两个日期的差 (date column +/- value) or MONTHS_BETWEEN 当前日期和时间 SYSDATE --------------------GETDATE() 一个月的最后一天 LAST_DAY 时区转换 NEW_TIME 日期后的第一个周日 NEXT_DAY 代表日期的字符串 TO_CHAR --------------------DATENAME 代表日期的整数 TO_NUMBER (TO_CHAR)) 日期舍入 ROUND 日期截断 TRUNC 字符串转换为日期 TO_DATE 如果为NULL则转换日期 NVL -------------------- ISNULL 转换函数 下面是Oracle支持的转换函数和它们的Microsoft SQL Server等价函数。 函数 Oracle 数字转换为字符 TO_CHAR 字符转换为数字 TO_NUMBER 日期转换为字符 TO_CHAR 字符转换为日期 TO_DATE CONVERT 16进制转换为2进制 HEX_TO_RAW 2进制转换为16进制 RAW_TO_HEX 其它行级别的函数 下面是Oracle支持的其它行级别的函数以及它们的Microsoft SQL Server等价函数。 函数 Oracle 返回第一个非空表达式 DECODE -------------------------------------COALESCE 当前序列值 CURRVAL 下一个序列值 NEXTVAL 用户登录账号ID数字 UID --------------------SUSER_ID 用户登录名 USER 用户数据库ID数字 UID 用户数据库名 USER 当前用户 CURRENT_USER -------------------- CURRENT_USER 用户环境(audit trail) USERENV 在CONNECT BY子句中的级别 LEVEL 合计函数 下面是Oracle支持的合计函数和它们的Microsoft SQL Server等价函数。 函数 Oracle Average AVG -------------------- AVG Count COUNT Maximum MAX -------------------- MAX Minimum MIN Standard deviation STDDEV Summation SUM -------------------- SUM Variance VARIANCE 条件测试 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(?') 数字到字符 TO_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联机手册。 字串连接 Oracle使用两个管道符号(||)来作为字串连接操作符,SQL Server则使用加号(+)。这个差别要求你在应用程序中做小小的修改。 Oracle Microsoft SQL SELECT FNAME||' '||LNAME AS NAME FROM STUDENT_ADMIN.STUDENT ----------------------------------------------- SELECT FNAME +' '+ LNAME AS NAME FROM STUDENT_ADMIN.STUDENT |
ORACLE和SQL语法区别归纳(一)