Oracle与Sql-Server函数对照.docx

下面是Oracle支持的字符函数和它们的Microsoft SQL Server等价函数：函数 Oracle SQLServer 把字符转换为ASCII : ASCII ASCII 字串连接: CONCAT (expression + expression) 把ASCII转换为字符 CHRCHAR 返回字符串中的开始字符（左起） INSTR CHARINDEX 把字符转换为小写 LOWER LOWER 把字符转换为大写 UPPERUPPER 填充字符串的左边 LPADN/A 清除开始的空白 LTRIM LTRIM 清除尾部的空白 RTRIM RTRIM 字符串中的起始模式（pattern） INSTR PATINDEX 多次重复字符串 RPAD REPLICATE 字符串的语音表示 SOUNDEX SOUNDEX 重复空格的字串 RPAD SPACE 从数字数据转换为字符数据 TO_CHAR STR 子串 SUBSTR SUBSTRING 替换字符REPLACESTUFF 将字串中的每个词首字母大写 INITCAP N/A 翻译字符串 TRANSLATE N/A 字符串长度 LENGTH DATELENGTH or LEN 列表中最大的字符串GREATEST N/A 列表中最小的字符串 LEAST N/A 如果为NULL则转换字串 NVL ISNULL 日期相加(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 日期舍入 ROUNDCONVERT 日期截断 TRUNC CONVERT 字符串转换为日期TO_DATE CONVERT 如果为NULL则转换日期NVL ISNULL 数字转换为字符 TO_CHAR CONVERT 字符转换为数字 TO_NUMBERCONVERT 日期转换为字符TO_CHARCONVERT 字符转换为日期 TO_DATE CONVERT 16进制转换为2进制 HEX_TO_RAWCONVERT 2进制转换为16进制 RAW_TO_HEXCONVERT 用户登录账号ID数字 UID SUSER_ID 用户登录名 USER SUSER_NAME 用户数据库ID数字 UID USER_ID 用户数据库名 USER USER_NAME 当前用户 CURRENT_USER CURRENT_USER 在CONNECT BY子句中的级别LEVEL N/A Average AVGAVG Count COUNTCOUNT Maximum MAX MAX MinimumMIN MIN Summation SUMSUM VarianceVARIANCE VAR or VARP 特殊的几种：1.字串连接 Oracle使用两个管道符号（|）来作为字串连接操作符，SQL Server则使用加号（+）。Oracle: SELECT FNAME| |LNAME AS NAMEFROM STUDENT_ADMIN.STUDENT;Microsoft SQL: SELECT FNAME + + LNAME AS NAMEFROM STUDENT_ADMIN.STUDENT 2.流控制（Control-of-Flow）流控制语言控制SQL语句执行流，语句块以及存储过程。PL/SQL和Transact-SQL提供了多数相同的结构，但是还是有一些语法差别。关键字这是两个RDBMS支持的关键字。语句 Oracle PL/SQL : 声明变量 DECLARE DECLARE 语句块 BEGIN.END; BEGIN.END 条件处理 IFTHEN,ELSIFTHEN,ELSEENDIF; -Microsoft SQLServer Transact-SQL: IFBEGINENDELSE BEGINENDELSE IF CASE expression 无条件结束 RETURN RETURN 无条件结束当前程序块后面的语句 EXIT BREAK 重新开始一个WHILE循环N/A CONTINUE 等待指定间隔N/A (dbms_lock.sleep) WAITFOR 循环控制WHILE LOOP END LOOP3. 程序注释/* */, /* */, 4. 声明变量 Transact-SQL和PL/SQL的变量是用DECLARE关键字创建的。Transact-SQL变量用标记，并且就像 PL/SQL一样，在第一次创建时，用空值初始化。Oracle : DECLAREVSSN CHAR(9);VFNAME VARCHAR2(12);VLNAME VARCHAR2(20);VBIRTH_DATE DATE;VLOAN_AMOUNT NUMBER(12,2);-Microsoft SQL: DECLAREVSSN CHAR(9),VFNAME VARCHAR2(12),VLNAME VARCHAR2(20),VBIRTH_DATE DATETIME,VLOAN_AMOUNT NUMERIC(12,2) Transact-SQL不支持%TYPE和%ROWTYPE变量数据类型定义。一个Transact-SQL变量不能在DECLARE命令中初始化。在Microsoft SQL Server数据类型定义中也不能使用Oracle的NOT NULL和CONSTANT关键字。像Oracle的LONG和LONG RAW数据类型一样。文本和图形数据类型不能被用做变量定义。此外，Transact-SQL不支持 PL/SQL风格的记录和表的定义。 5. 给变量赋值Oracle和Microsoft SQL Server提供了下列方法来为本地变量赋值。Oracle Microsoft SQL Assignment operator (:=) -SET local_variable = value SELECT.INTO syntax for selecting column values from a single row- SELECT local_variable = expression FROM for assigning a literal value,an expression involving other local variables, or a column value from a single row FETCHINTO syntax- FETCHINTO syntax 这里有一些语法示例Oracle: DECLARE VSSN CHAR(9);VFNAME VARCHAR2(12);VLNAME VARCHAR2(20);BEGINVSSN := ?;SELECT FNAME, LNAME INTO VFNAME, VLNAME FROM STUDENTS WHERE SSN=VSSN;END; /-Microsoft SQL: DECLARE VSSN CHAR(9),VFNAME VARCHAR(12),VLNAME VARCHAR(20)SET VSSN = ?SELECT VFNAME=FNAME, VLNAME=LNAME FROM STUDENTS WHERE SSN = VSSN 6. 语句块Oracle PL/SQL和Microsoft SQL Server Transact-SQL都支持用BEGINEND术语来标记语句块。Transact- SQL不需要在DECLARE语句后使用一个语句块。- 如果在Microsoft SQL Server中的IF语句和WHILE循环中有多于一个语句被执行，则需要使用BEGINEND语句块。Oracle: Microsoft SQL: DECLAREDECLARE VARIABLES .BEGIN - THIS IS REQUIRED SYNTAXPROGRAM_STATEMENTS .IF .THENSTATEMENT1;STATEMENT2;STATEMENTN;END IF;WHILE . LOOPSTATEMENT1;STATEMENT2;STATEMENTN;END LOOP;END; - THIS IS REQUIRED SYNTAX DECLAREDECLARE VARIABLES .BEGIN - THIS IS OPTIONAL SYNTAXPROGRAM_STATEMENTS .IF .BEGIN STATEMENT1STATEMENT2STATEMENTNEND WHILE . BEGINSTATEMENT1STATEMENT2STATEMENTNEND END - THIS IS REQUIRED SYNTAX 7. 条件处理 Microsoft SQL Server Transact-SQL的条件语句包括IF和ELSE，但不包括Oracle PL/SQL中的ELSEIF语句。可以用嵌套多重IF语句来到达同样的效果。对于广泛的条件测试，用CASE表达式也许更容易和可读一些。Oracle Microsoft SQL DECLAREVDEGREE_PROGRAM CHAR(1);VDEGREE_PROGRAM_NAME VARCHAR2(20);BEGINVDEGREE_PROGRAM := U;IF VDEGREE_PROGRAM = U THEN VDEGREE_PROGRAM_NAME := Undergraduate;ELSIF VDEGREE_PROGRAM = M THEN VDEGREE_PROGRAM_NAME := Masters;ELSIF VDEGREE_PROGRAM = P THEN VDEGREE_PROGRAM_NAME := PhD;ELSE VDEGREE_PROGRAM_NAME := Unknown;END IF;END;/-DECLAREVDEGREE_PROGRAM CHAR(1),VDEGREE_PROGRAM_NAME VARCHAR(20)set VDEGREE_PROGRAM = USELECT VDEGREE_PROGRAM_NAME = CASE VDEGREE_PROGRAMWHEN U THEN UndergraduateWHEN M THEN MastersWHEN P THEN PhD.ELSE UnknownEND 8. 重复执行语句（循环）Oracle PL/SQL提供了无条件的LOOP和FOR LOOP。Transact-SQL则提供了WHILE循环和GOTO语句。WHILE Boolean_expressionsql_statement | statement_blockBREAK CONTINUEWHILE循环需要测试一个布尔表达式来决定一个或者多个语句的重复执行。只要给定的表达式结果为真，这个（些）语句就一直重复执行下去。如果有多个语句需要执行，则这些语句必须放在一个BEGINEND块中。Oracle: DECLARECOUNTER NUMBER;BEGINCOUNTER := 0;WHILE (COUNTER 5) LOOPCOUNTER := COUNTER + 1;END LOOPEND;/-Microsoft SQL: DECLARECOUNTER NUMERICSELECT COUNTER = 1WHILE (COUNTER 5)BEGINSELECT COUNTER = COUNTER +1END 语句的执行可以在循环的内部用BREAK和CONTINUE关键字控制。BREAK关键字使WHILE循环无条件的结束，而 CONTINUE关键字使WHILE循环跳过后面的语句重新开始。BREAK关键字同Oracle PL/SQL中的EXIT关键字是等价的。而在Oracle中没有和CONTINUE等价的关键字9. GOTO语句Oracle和Microsoft SQL Server都有GOTO语句，但是语法不同。GOTO语句使Transact-SQL跳到指定的标号处运行，在GOTO语句后指定标号之间的任何语句都不会被执行。Oracle Microsoft SQL GOTO label; GOTO label 10. PRINT语句Transact-SQL的PRINT语句执行同PL/SQL的RDBMS_OUTPUT.put_line 过程同样的操作。该语句用来打印用户给定的消息。用PRINT语句打印的消息上限是8,000个字符。定义为char或者varchar数据类型的变量可以嵌入打印语句。如果使用其它数据类型的变量，则必须使用CONVERT或者CAST函数。本地变量、全局变量可以被打印。可以用单引号或者双引号来封闭文本。11. 从存储过程返回 Microsoft SQL Server和Oracle都有RETURN语句。RETURN使你的程序从查询或者过程中无条件的跳出。RETURN是立即的、完全的、并且可以用于从过程、批处理或者语句块的任意部分跳出。在REUTRN后面的语句将不会被执行。Oracle Microsoft SQL RETURN expression: RETURN integer_expression 引发程序错误（Raising program errors）Transact-SQL的RAISERROR返回一个用户定义的错误消息，并且设置一个系统标志来记录发生了一个错误。这个功能同 PL/SQL的raise_application_error异常处理器的功能是相似的。RAISERROR语句允许客户重新取得sysmessages表的一个入口，或者用用户指定的严重性和状态信息动态的建立一条消息。在被定义后，消息被送回客户端作为系统错误消息。RAISERROR (msg_id | msg_str, severity, state, argument1 , argument2)WITH options在转换你的PL/SQL程序时，也许用不着使用RAISERROR语句。在下面的示例代码中。PL/SQL程序使用 raise_application_error异常处理器，但是Transact-SQL程序则什么也没用。包括 raise_application_error异常处理器是为了防止PL/SQL返回不明确的未经处理的异常错误消息。作为代替，当一个不可预见的问题发生的时候，异常处理器总是返回Oracle错误消息。当一个Transact-SQL失败时，它总是返回一个详细的错误消息给客户程序。因此，除非需要某些特定的错误处理，一般是不需要 RAISERROR语句的。Oracle Microsoft SQL CREATE OR REPLACE FUNCTIONDEPT_ADMIN.DELETE_DEPT(VDEPT IN VARCHAR2) RETURN NUMBER ASBEGINDELETE FROM DEPT_ADMIN.DEPTWHERE DEPT = VDEPT;RETURN(SQL%ROWCOUNT);EXCEPTIONWHEN OTHER THENRAISE_APPLICATION_ERROR (-20001,SQLERRM);END DELETE_DEPT;-/ CREATE PROCEDUREDEPT_ADMIN.DELETE_DEPTVDEPT VARCHAR(4) ASDELETE FROM DEPT_DB.DBO.DEPTWHERE DEPT = VDEPTRETURN ROWCOUNTGO 12. 实现游标Oracle在使用SELECT语句时总是需要游标，不管从数据库中请求多少行。在Microsoft SQL Server，SELECT 语句并不把在返回客户的行上附加游标作为缺省的结果集合。这是一种返回数据给客户应用程序的有效的方法。SQL Server为游标函数提供了两种接口。当在Transact-SQL批处理或者存储过程中使用游标的时候，SQL语句可用来声明、打开、和从游标中抽取，就像定位更新和删除一样。当使用来自DB-Library、ODBC、或者OLEDB程序的游标时，SQL Server显式的调用内建的服务器函数来更有效的处理游标。当从Oracle输入一个PL/SQL过程时，首先判断是否需要在Transact-SQL中采用游标来实现同样的功能。如果游标仅仅返回一组行给客户程序，就使用非游标的SELECT语句来返回缺省的结果集合。如果游标用来从行中一次取得一个数据给本地过程变量，你就必须在 Transact-SQL中使用游标。语法下表显示了使用游标的语法。操作 Oracle Microsoft SQLServer 声明一个游标 CURSOR cursor_name (cursor_parameter(s)IS select_statement; -DECLARE cursor_name CURSORLOCAL | GLOBALFORWARD_ONLY | SCROLLSTATIC | KEYSET | DYNAMIC | FAST_FORWARDREAD_ONLY | SCROLL_LOCKS | OPTIMISTICTYPE_WARNINGFOR select_statementFOR UPDATE OF column_name ,n 打开一个游标 OPEN cursor_name (cursor_parameter(s); -OPEN cursor_name 从游标中提取（Fetching） FETCH cursor_name INTO variable(s)-FETCH FROM cursor_nameINTO variable(s) 更新提取行 UPDATE table_nameSET statement(s)WHERE CURRENT OF cursor_name; UPDATE table_nameSET statement(s)WHERE CURRENT OF cursor_name 删除提取行 DELETE FROM table_name WHERE CURRENT OF cursor_name; DELETE FROM table_name WHERE CURRENT OF cursor_name 关闭游标 CLOSE cursor_name; CLOSE cursor_name 清除游标数据结构 N/A DEALLOCATE cursor_name 声明一个游标尽管Transact-SQL DECLARE CURSOR语句不支持游标参数的使用，但它确实支持本地变量。当游标打开的时候，它就使用这些本地变量的值。Microsoft SQL Server在其DECLARE CURSOR中提供了许多附加的功能。INSENSITIVE选项用来定义一个创建数据的临时拷贝以被游标使用的游标。游标的所有请求都由这个临时表来应答。因此对原表的修改不会反映到那些由fetch返回的用于该游标的数据上。这种类型的游标访问的数据是不能被修改的。应用程序可以请求一个游标类型然后执行一个不被所请求的服务器游标类型支持的Transact-SQL语句。SQL Server返回一个错误，指出该游标类型被改变了，或者给出一组参数，隐式的转换游标。欲取得一个触发SQL Server 7.0隐式的把游标从一种类型转换为另一种类型的参数的完整列表，请参阅SQL Server联机手册。SCROLL选项允许除了前向的抽取以外，向后的、绝对的和相对的数据抽取。一个滚动游标使用一种键集合的游标模型，在该模型中，任何用户提交的对表的删除和更新都将影响后来的数据抽取。只有在游标没有用INSENSITIVE选项声明时，上面的特性才起作用。如果选择了READ ONLY选项，对游标中的行的更新就被禁止。该选项将覆盖游标的缺省选项棗允许更新。UPDATE OF column_list语句用来在游标中定义一个可更新的列。如果提供了OF column_list，那么仅仅是那些列出的列可以被修改。如果没有指定任何列。则所有的列都是可以更新的，除非游标被定义为READ ONLY。重要的是，注意到一个SQL Server游标的名字范围就是连接自己。这和本地变量的名字范围是不同的。不能声明一个与同一个用户连接上的已有的游标相同名字的游标，除非第一个游标被释放。打开一个游标Transact-SQL不支持向一个打开的游标传递参数，这一点和PL/SQL是不一样的。当一个Transact-SQL游标被打开以后，结果集的成员和顺序就固定下来了。其它用户提交的对原表的游标的更新和删除将反映到对所有未加INSENSITIVE选项定义的游标的数据抽取上。对一个INSENSITIVE游标，将生成一个临时表。抽取数据Oracle游标只能向前移动棗没有向后或者相对滚动的能力。SQL Server游标可以向前或者向后滚动，具体怎么滚动，要由下表给出的数据抽取选项来决定。只有在游标是用SCROLL选项声明的前提下，这些选项才能使用。卷动选项 描述 NEXT 如果这是对游标的第一次提取，则返回结果集合的第一行；否则，在结果结合内移动游标到下一行。NEXT 是在结果集合中移动的基本方法 。NEXT是缺省的游标提取（fetch）。 PRIOR 返回结果集合的前一行。 FIRST 把游标移动到结果集合的第一行，同时返回第一行。 LAST 把游标移动到结果集合的最后一行，同时返回最后一行。 ABSOLUTE n 返回结果集合的第n行。如果n为负数，则返回倒数第n行 RELATIVE n 返回当前提取行后的第n行，如果n是负数，则返回从游标相对位置起的倒数第n行。 Transact-SQL的FETCH语句不需要INTO子句。如果没有指定返回变量，行就自动作为一个单行结果集合返回给客户。但是，如果你的过程必须把行给客户，一个不带游标的SELECT语句更有效一些。在每一个FETCH后面，FETCH_STATUS函数被更新。这和在PL/SQL中使用CURSOR_NAME%FOUND和 CURSOR_NAME%NOTFOUND变量是相似的。FETCH_STATUS函数在每一次成功的数据抽取以后被设定为0。如果数据抽取试图读取一个超过游标末尾的数据，则返回一个为-1的值。如果请求的行在游标打开以