Oracle与SQL函数区别.doc

SQL Server与oracle两者区别之函数区别2010-04-22 14:27Oracle和SQL Server的常用函数对比1数学函数绝对值 S：select abs(-1) value O：select abs(-1) value from dual取整(大) S：select ceiling(-001) value O：select ceil(-001) value from dual取整（小） S：select floor(-001) value O：select floor(-001) value from dual取整（截取） S：select cast(-002 as int) value O：select trunc(-002) value from dual四舍五入 S：select round(23456,4) value 23460 O：select round(23456,4) value from dual 2346e为底的幂 S：select Exp(1) value O：select Exp(1) value from dual 取e为底的对数 S：select log(7182818284590451) value O：select ln(7182818284590451) value from dual; 取10为底对数 S：select log10(10) value O：select log(10,10) value from dual; 取平方 S：select SQUARE(4) value O：select power(4,2) value from dual 取平方根 S：select SQRT(4) value O：select SQRT(4) value from dual 求任意数为底的幂 S：select power(3,4) value O：select power(3,4) value from dual 取随机数 S：select rand() value O：select sys.dbms_random.value(0,1) value from dual;取符号 S：select sign(-8) value -1 O：select sign(-8) value from dual -12数值比较求集合最大值 S：select max(value) value from (select 1 value union select -2 value union select 4 value union select 3 value)a O：select greatest(1,-2,4,3) value from dual求集合最小值 S：select min(value) value from (select 1 value union select -2 value union select 4 value union select 3 value)a O：select least(1,-2,4,3) value from dual如何处理null值（F2字段中的null以10代替） S：select F1,IsNull(F2,10) value from Tbl O：select F1,nvl(F2,10) value from Tbl3字符串函数求字符序号 S：select ascii(a) value O：select ascii(a) value from dual从序号求字符 S：select char(97) value O：select chr(97) value from dual连接 S：select 11+22+33 value O：select CONCAT(11,22)|33 value from dual子串位置-返回3 S：select CHARINDEX(s,sdsq,2) value O：select INSTR(sdsq,s,2) value from dual模糊子串的位置-返回2，参数去掉中间%则返回7 S：select patindex(%d%q%,sdsfasdqe) value O：oracle没发现，但是instr可以通过第四个参数控制出现次数 select INSTR(sdsfasdqe,sd,1,2) value from dual 返回6求子串 S：select substring(abcd,2,2) value O：select substr(abcd,2,2) value from dual子串代替-返回aijklmnef S：SELECT STUFF(abcdef, 2, 3, ijklmn) value O：SELECT Replace(abcdef, bcd, ijklmn) value from dual左补空格（LPAD的第1个参数为空格则同space函数） S：select space(10)+abcd value O：select LPAD(abcd,14) value from dual右补空格（RPAD的第1个参数为空格则同space函数） S：select abcd+space(10) value O：select RPAD(abcd,14) value from dual发音相似性比较（这2个单词返回值一样，发音相同） S：select soundex (Smith), soundex (Smythe) O：SELECT SOUNDEX (Smith), SOUNDEX (Smythe) from dualSQL Server中用SELECT DIFFERENCE(Smithers, Smythers)比较soundex的差返回04，4为同音，1最高4日期函数系统时间 S：select getdate() value O：select sysdate value from dual求日期 S：select convert(char(10),getdate(),20) value O：select trunc(sysdate) value from dualselect to_char(sysdate,yyyy-mm-dd) value from dual求时间 S：select convert(char(8),getdate(),108) value O：select to_char(sysdate,hh24:mm:ss) value from dual当月最后一天 S：不知道 O：select LAST_DAY(sysdate) value from dual本星期的某一天（比如星期日） S：不知道 O：SELECT Next_day(sysdate,7) vaule FROM DUAL;字符串转时间 S：select cast(2004-09-08as datetime) value O：SELECT To_date(2004-01-05 22:09:38,yyyy-mm-dd hh24-mi-ss)vaule FROM DUAL;求2日期某一部分的差（比如秒） S：select datediff(ss,getdate(),getdate()+3) value O：直接用两个日期相减（比如d1-d2=3）SELECT (d1-d2)*24*60*60 vaule FROM DUAL;根据差值求新的日期（比如分钟） S：select dateadd(mi,8,getdate() value O：SELECT sysdate+8/60/24 vaule FROM DUAL;ORACLE内部函数大全以及与SQLSERVER的区别：下面是Oracle支持的字符函数和它们的Microsoft SQL Server等价函数。函数 Oracle Microsoft SQLServer把字符转换为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 SQLServer日期相加 (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 SQLServer数字转换为字符 TO_CHAR -CONVERT字符转换为数字 TO_NUMBER -CONVERT日期转换为字符 TO_CHAR -CONVERT字符转换为日期 TO_DATE CONVERT16进制转换为2进制 HEX_TO_RAW -CONVERT2进制转换为16进制 RAW_TO_HEX -CONVERT其它行级别的函数下面是Oracle支持的其它行级别的函数以及它们的Microsoft SQL Server等价函数。函数 Oracle -Microsoft SQLServer返回第一个非空表达式 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 SQLServerAverage AVG - AVGCount COUNT -COUNTMaximum MAX - MAXMinimum MIN -MINStandard deviation STDDEV -STDEV or STDEVPSummation SUM - SUMVariance VARIANCE -VAR or VARP条件测试Oracle的DECODE语句和Microsoft SQL Server的CASE表达式都执行条件测试。当test_value中的值和后面的任何表达式匹配的时候，相关的值就返回。如果没有找到任何匹配的值，就返回default_value。如果没有指定default_value，在没有匹配的时候，DECODE和CASE都返回一个NULL。下表显示了该语句的语法，同时给出了转换DECODE命令的示例。Oracle Microsoft SQLDECODE (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),2)FROM STUDENT_ADMIN.GRADEGROUP BY SSNCASE input_expressionWHEN when_expression THEN result_expressionELSE else_result_expressionENDCREATE 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- THEN 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 SSNCASE表达式可以支持用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 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(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/yy2 102 ANSI yy.mm.dd3 103 British/French dd/mm/yy4 104 German dd.mm.yy5 105 Italian dd-mm-yy6 106 - dd mon yy7 107 - mon dd, yy8 108 - hh:mm:ss- 9 or 109 (*) Default milliseconds mon dd yyyy hh:mi:ss:mmm (AM or PM)10 110 USA mm-dd-yy11 111 Japan yy/mm/dd12 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 SQLServerSELECT SSN, FNAME, LNAME, ) TUITION_PAID,TUITION_PAID/GET_SUM_MAJOR(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.MAJORCREATE 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 FU