ORACLE内部函数大全以及与SQLSERVER的区别.docx

1、ORACLE内部函数大全以及与SQLSERVER的区别ORACLE内部函数大全以及与SQLSERVER的区别下面是Oracle支持的字符函数和它们的Microsoft SQL Server等价函数。函数 Oracle Microsoft SQLServer 把字符转换为ASCII :ASCII ASCII 字串连接: CONCAT -(expression + expression) 把ASCII转换为字符 CHR, CHAR 返回字符串中的开始字符（左起） INSTR ,-CHARINDEX 把字符转换为小写 LOWER -LOWER 把字符转换为大写 UPPER- UPPER 填充字符串的

2、左边 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 o

3、r LEN 列表中最大的字符串 GREATEST- N/A 列表中最小的字符串 LEAST -N/A 如果为NULL则转换字串 NVL- ISNULL 日期函数下面是Oracle支持的日期函数和它们的Microsoft SQL Server等价函数。函数 Oracle -Microsoft SQLServer 日期相加 (date column +/- value) or ADD_MONTHS -DATEADD 两个日期的差 (date column +/- value) or MONTHS_BETWEEN -DATEDIFF 当前日期和时间 SYSDATE -GETDATE() 一个月的最后

4、一天 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 SQLServer 数字转

5、换为字符 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 SQLServer 返回第一个非空表达式 DECODE -COALESCE 当前序列值 CURRVAL -N/A 下一个序

6、列值 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 SQLServer Average AVG - AVG

7、 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，在没有匹配的时候

8、，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,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),

9、2)FROM STUDENT_ADMIN.GRADEGROUP BY SSNCASE input_expressionWHEN when_expression THEN result_expressionELSE else_result_expressionEND CREATE VIEW STUDENT_ADMIN.STUDENT_GPA(SSN, GPA)AS SELECT SSN, ROUND(AVG(CASE gradeWHEN A THEN 4WHEN A+ THEN 4.3WHEN A- THEN 3.7WHEN B THEN 3WHEN B+ THEN 3.3WHEN B- THE

10、N 2.7WHEN C THEN 2WHEN C+ THEN 2.3WHEN C- THEN 1.7WHEN D THEN 1WHEN D+ THEN 1.3WHEN D- THEN 0.7ELSE 0END),2)FROM STUDENT_ADMIN.GRADEGROUP BY SSN CASE表达式可以支持用SELECT语句执行布尔测试，这是DECODE命令所不允许的。欲了解关于CASE表达式的详细信息，请参阅SQL Server联机手册。把值转换为不同的数据类型Microsoft SQL Server的CONVERT和CAST函数都是多目标转换函数。它们提供了相似的功能，把一种数据类型的

11、表达式转换为另一种数据类型的表达式，并且支持多种专门数据的格式。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

12、。转换 Oracle Microsoft SQLServer 字符到数字 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(sy

13、sdate)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”如果你

14、使用任何其它格式，你必须提供一个合适的日期格式模型。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:

15、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 11

16、2 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 SQLServer SELECT SSN, FNAME, LNAME, ) TUITION_PAID,TUITION_PAID/GET_SUM_MAJ

17、OR(MAJOR)AS PERCENT_MAJORFROM STUDENT_ADMIN.STUDENT SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID/SUM_MAJOR AS PERCENT_MAJORFROM STUDENT_ADMIN.STUDENT,(SELECT MAJOR, SUM(TUITION_PAID) SUM_MAJORFROM STUDENT_ADMIN.STUDENTGROUP BY MAJOR) SUM_STUDENTWHERE STUDENT.MAJOR = SUM_STUDENT.MAJOR CREATE

18、OR REPLACE FUNCTION GET_SUM_MAJOR(INMAJOR VARCHAR2) RETURN NUMBERAS SUM_PAID NUMBER;BEGINSELECT SUM(TUITION_PAID) INTO SUM_PAIDFROM STUDENT_ADMIN.STUDENTWHERE MAJOR = INMAJOR;RETURN(SUM_PAID);END GET_SUM_MAJOR; No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax. 比较操作符Oracle和Microsoft

19、 SQL Server的比较操作符几乎是一样的。算符 Oracle Microsoft SQLServer 等于 (=) (=) 大于 () () 小于 () (=) (=) 小于或等于 (=) (=) 不等于 (!=, , =) (!=, , =) 不大于，不小于 N/A ! , ! 在集合中任意成员中 IN IN 不在集合中的任何成员中 NOT IN NOT IN 集合中的任意值 ANY, SOME ANY, SOME 提交集合中的所有值 != ALL, ALL, ALL, = ALL, !=SOME, SOME, SOME, =SOME != ALL, ALL, ALL, = ALL,

20、!=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

21、recommended). 模式匹配SQL Server的LIKE关键字提供了有用的通配符搜索功能，这个功能在Oracle中不支持除了所有的RDBMS都支持的（%）和（_）通配符以外，SQL Server还支持（ ）和（）通配符。（ ）字符用来查询在一个范围内的所有单个字符。例如，如果你需要查询包含一个从a到f的字符的数据，你可以这样写：“LIKE a-f”或者“LIKE abcdef”。这些附加的通配符的有效性在下表中给出。Oracle Microsoft SQL SELECT * FROM STUDENT_ADMIN.STUDENTWHERE LNAME LIKE A%OR LNAME L

22、IKE B%OR LNAME LIKE C% SELECT * FROM STUDENT_ADMIN.STUDENTWHERE 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

23、必须设定为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使用两个管道符号（|）来作为字串连接操作符，SQ

24、L Server则使用加号（+）。这个差别要求你在应用程序中做小小的修改。Oracle Microsoft SQL SELECT FNAME| |LNAME AS NAMEFROM STUDENT_ADMIN.STUDENT-SELECT FNAME + + LNAME AS NAMEFROM STUDENT_ADMIN.STUDENT 流控制（Control-of-Flow）语言流控制语言控制SQL语句执行流，语句块以及存储过程。PL/SQL和Transact-SQL提供了多数相同的结构，但是还是有一些语法差别。关键字这是两个RDBMS支持的关键字。语句 Oracle PL/SQL -Mic

25、rosoft SQLServer Transact-SQL 声明变量 DECLARE DECLARE 语句块 BEGIN.END; BEGIN.END 条件处理 IFTHEN,ELSIFTHEN,ELSEENDIF; -IFBEGINENDELSE BEGINENDELSE IF CASE expression 无条件结束 RETURN- RETURN 无条件结束当前程序块后面的语句 EXIT BREAK 重新开始一个WHILE循环 N/A CONTINUE 等待指定间隔 N/A (dbms_lock.sleep) WAITFOR 循环控制 WHILE LOOPEND LOOP; -LABELGOTO LABEL;FOREND LOOP;LOOPEND LOOP;WHILE BEGIN ENDLABELGOTO LABEL 程序注释 /* */, - /* */, - 打印输出 RDBMS_OUT