ORACLE和SQL语法区别归纳(2)

字串连接

Oracle 使用两个管道符号（||）来作为字串连接操作符SQL Server则使用加号（+）这个差别要求你在应用程序中做小小的修改

Oracle Microsoft SQL

SELECT FNAME|| ||LNAME AS NAME

FROM STUDENT_ADMINSTUDENT

SELECT FNAME + + LNAME AS NAME

FROM STUDENT_ADMINSTUDENT

流控制（ControlofFlow）语言

流控制语言控制SQL 语句执行流语句块以及存储过程PL/SQL 和TransactSQL 提供了多数相同的结构但是还是有一些语法差别

关键字

这是两个RDBMS支持的关键字

语句 Oracle PL/SQL Microsoft SQL Server TransactSQL

声明变量 DECLARE DECLARE

语句块 BEGINEND; BEGINEND

条件处理 IF…THEN

ELSIF…THEN

ELSE

ENDIF;

IF…[BEGIN…END]

ELSE

[BEGIN…END]

ELSE IF

CASE expression

无条件结束 RETURN RETURN

无条件结束当前程序块后面的语句 EXIT BREAK

重新开始一个WHILE循环 N/A CONTINUE

等待指定间隔 N/A (dbms_locksleep) WAITFOR

循环控制 WHILE LOOP…END LOOP;

LABEL…GOTO LABEL;

FOR…END LOOP;

LOOP…END LOOP;

WHILE

BEGIN… END

LABEL…GOTO LABEL

程序注释 /* … */ /* … */

打印输出 RDBMS_OUTPUTPUT_LINE PRINT

引发程序错误（Raise program error） RAISE_APPLICATION_ERROR RAISERROR

执行程序 EXECUTEEXECUTE

语句终止符 Semicolon (;) N/A

声明变量

TransactSQL 和PL/SQL 的变量是用DECLARE关键字创建的TransactSQL 变量用@标记

并且就像PL/SQL 一样在第一次创建时用空值初始化

Oracle Microsoft SQL

DECLARE

VSSN CHAR();

VFNAME VARCHAR();

VLNAME VARCHAR();

VBIRTH_DATE DATE;

VLOAN_AMOUNT NUMBER();

DECLARE

@VSSN CHAR()

@VFNAME VARCHAR()

@VLNAME VARCHAR()

@VBIRTH_DATE DATETIME

@VLOAN_AMOUNT NUMERIC()

TransactSQL 不支持%TYPE和%ROWTYPE变量数据类型定义一个TransactSQL 变量不能在DECLARE命令中初始化

在Microsoft SQL Server数据类型定义中也不能使用Oracle 的NOT NULL和CONSTANT关键字

像Oracle 的LONG和LONG RAW数据类型一样文本和图形数据类型不能被用做变量定义

此外TransactSQL 不支持PL/SQL 风格的记录和表的定义

给变量赋值

Oracle 和Microsoft SQL Server提供了下列方法来为本地变量赋值

Oracle Microsoft SQL

Assignment operator (:=) SET @local_variable = value

SELECTINTO 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

FETCH…INTO syntax FETCH…INTO syntax

这里有一些语法示例

Oracle Microsoft SQL

DECLARE VSSN CHAR();

VFNAME VARCHAR();

VLNAME VARCHAR();

BEGIN

VSSN := ?

SELECT FNAME LNAME INTO VFNAME VLNAME FROM STUDENTS WHERE SSN=VSSN;

END;

DECLARE @VSSN CHAR()

@VFNAME VARCHAR()

@VLNAME VARCHAR()

SET @VSSN = ?

SELECT @VFNAME=FNAME @VLNAME=LNAME FROM STUDENTS WHERE SSN = @VSSN

语句块

Oracle PL/SQL 和Microsoft SQL Server TransactSQL 都支持用BEGIN…END术语来标记语句块

TransactSQL 不需要在DECLARE语句后使用一个语句块

如果在Microsoft SQL Server

中的IF语句和WHILE循环中有多于一个语句被执行则需要使用BEGIN…END语句块

Oracle Microsoft SQL

DECLARE

DECLARE VARIABLES

BEGIN THIS IS REQUIRED SYNTAX

PROGRAM_STATEMENTS

IF THEN

STATEMENT;

STATEMENT;

STATEMENTN;

END IF;

WHILE LOOP

STATEMENT;

STATEMENT;

STATEMENTN;

END LOOP;

END; THIS IS REQUIRED SYNTAX DECLARE

DECLARE VARIABLES

BEGIN THIS IS OPTIONAL SYNTAX

PROGRAM_STATEMENTS

IF

BEGIN

STATEMENT

STATEMENT

STATEMENTN

END

WHILE

BEGIN

STATEMENT

STATEMENT

STATEMENTN

END

END THIS IS REQUIRED SYNTAX

条件处理

Microsoft SQL Server TransactSQL 的条件语句包括IF和ELSE但不包括Oracle PL/SQL 中的ELSEIF语句

可以用嵌套多重IF语句来到达同样的效果对于广泛的条件测试用CASE表达式也许更容易和可读一些

Oracle Microsoft SQL

DECLARE

VDEGREE_PROGRAM CHAR();

VDEGREE_PROGRAM_NAME VARCHAR();

BEGIN

VDEGREE_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;

DECLARE

@VDEGREE_PROGRAM CHAR()

@VDEGREE_PROGRAM_NAME VARCHAR()

SELECT @VDEGREE_PROGRAM = U

SELECT @VDEGREE_PROGRAM_

NAME = CASE @VDEGREE_PROGRAM

WHEN U THEN Undergraduate

WHEN M THEN Masters

WHEN P THEN PhD

ELSE Unknown

END

重复执行语句（循环）

Oracle PL/SQL 提供了无条件的LOOP和FOR LOOPTransactSQL 则提供了WHILE循环和GOTO语句

WHILE Boolean_expression

{sql_statement | statement_block}

[BREAK] [CONTINUE]

WHILE循环需要测试一个布尔表达式来决定一个或者多个语句的重复执行

只要给定的表达式结果为真这个（些）语句就一直重复执行下去如果有多个语句需要执行则这些语句必须放在一个BEGIN…END块中

Oracle Microsoft SQL

DECLARE

COUNTER NUMBER;

BEGIN

COUNTER :=

WHILE (COUNTER <) LOOP

COUNTER := COUNTER + ;

END LOOP;

END;

DECLARE

@COUNTER NUMERIC

SELECT@COUNTER =

WHILE (@COUNTER <)

BEGIN

SELECT @COUNTER =

@COUNTER +

END

语句的执行可以在循环的内部用BREAK和CONTINUE关键字控制BREAK关键字使WHILE循环无条件的结束

而CONTINUE关键字使WHILE循环跳过后面的语句重新开始BREAK关键字同Oracle PL/SQL 中的EXIT关键字是等价的

而在Oracle 中没有和CONTINUE等价的关键字

GOTO语句

Oracle 和Microsoft SQL Server都有GOTO语句但是语法不同GOTO语句使TransactSQL 跳到指定的标号处运行

在GOTO语句后指定标号之间的任何语句都不会被执行

Oracle Microsoft SQL

GOTO label;

<> GOTO label

PRINT语句

TransactSQL 的PRINT语句执行同PL/SQL 的RDBMS_OUTPUTput_line过程同样的操作该语句用来打印用户给定的消息

用PRINT语句打印的消息上限是个字符定义为char或者varchar数据类型的变量可以嵌入打印语句

如果使用其它数据类型的变量则必须使用CONVERT或者CAST函数本地变量全局变量可以被打印可以用单引号或者双引号来封闭文本

从存储过程返回

Microsoft SQL Server和Oracle 都有RETURN语句RETURN使你的程序从查询或者过程中无条件的跳出RETURN 是立即的

完全的并且可以用于从过程批处理或者语句块的任意部分跳出在REUTRN后面的语句将不会被执行

Oracle Microsoft SQL

RETURN expression: RETURN [integer_expression]

引发程序错误（Raising program errors）

TransactSQL 的RAISERROR返回一个用户定义的错误消息并且设置一个系统标志来记录发生了一个错误

这个功能同PL/SQL 的raise_application_error异常处理器的功能是相似的

RAISERROR语句允许客户重新取得sysmessages表的一个入口或者用用户指定的严重性和状态信息动态的建立一条消息

在被定义后消息被送回客户端作为系统错误消息

RAISERROR ({msg_id | msg_str} severity state

[ argument [ argument>)

[WITH options]

在转换你的PL/SQL 程序时也许用不着使用RAISERROR语句在下面的示例代码中

PL/SQL 程序使用raise_application_error异常处理器但是TransactSQL 程序则什么也没用

包括raise_application_error异常处理器是为了防止PL/SQL 返回不明确的未经处理的异常错误消息

作为代替当一个不可预见的问题发生的时候异常处理器总是返回Oracle 错误消息

当一个TransactSQL 失败时它总是返回一个详细的错误消息给客户程序因此除非需要某些特定的错误处理

一般是不需要RAISERROR语句的

Oracle Microsoft SQL

CREATE OR REPLACE FUNCTION

DEPT_ADMINDELETE_DEPT

(VDEPT IN VARCHAR) RETURN NUMBER AS

BEGIN

DELETE FROM DEPT_ADMINDEPT

WHERE DEPT = VDEPT;

RETURN(SQL %ROWCOUNT);

EXCEPTION

WHEN OTHER THEN

RAISE_APPLICATION_ERROR

(SQLERRM);

END DELETE_DEPT;

/ CREATE PROCEDURE

DEPT_ADMINDELETE_DEPT

@VDEPT VARCHAR() AS

DELETE FROM DEPT_DBDBODEPT

WHERE DEPT = @VDEPT

RETURN @@ROWCOUNT

GO

实现游标

Oracle 在使用SELECT语句时总是需要游标不管从数据库中请求多少行在 Microsoft SQL Server

SELECT语句并不把在返回客户的行上附加游标作为缺省的结果集合这是一种返回数据给客户应用程序的有效的方法

SQL Server为游标函数提供了两种接口当在TransactSQL 批处理或者存储过程中使用游标的时候SQL 语句可用来声明

打开和从游标中抽取就像定位更新和删除一样当使用来自DBLibraryODBC或者OLEDB程序的游标时SQL Server

显式的调用内建的服务器函数来更有效的处理游标

当从Oracle 输入一个PL/SQL 过程时首先判断是否需要在TransactSQL 中采用游标来实现同样的功能如果游标仅仅返回一

组行给客户程序就使用非游标的SELECT语句来返回缺省的结果集合如果游标用来从行中一次取得一个数据给本地过程变量

你就必须在TransactSQL 中使用游标

语法

下表显示了使用游标的语法

操作 Oracle Microsoft SQL Server

声明一个游标 CURSOR cursor_name [(cursor_parameter(s))]

IS select_statement;

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR select_statement

[FOR UPDATE [OF column_name […n>]

打开一个游标 OPEN cursor_name [(cursor_parameter(s))];

OPEN cursor_name

从游标中提取（Fetching） FETCH cursor_name INTO variable(s)

FETCH FROM] cursor_name

[INTO @variable(s)]

更新提取行 UPDATE table_name

SET statement(s)…

WHERE CURRENT OF cursor_name; UPDATE table_name

SET 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

声明一个游标

尽管TransactSQL DECLARE CURSOR语句不支持游标参数的使用但它确实支持本地变量当游标打开的时候

它就使用这些本地变量的值Microsoft SQL Server在其DECLARE CURSOR中提供了许多附加的功能

INSENSITIVE选项用来定义一个创建数据的临时拷贝以被游标使用的游标游标的所有请求都由这个临时表来应答因此

对原表的修改不会反映到那些由fetch返回的用于该游标的数据上这种类型的游标访问的数据是不能被修改的

应用程序可以请求一个游标类型然后执行一个不被所请求的服务器游标类型支持的TransactSQL 语句SQL Server返回一个错误

指出该游标类型被改变了或者给出一组参数隐式的转换游标欲取得一个触发SQL Server 隐式的把游标从一种类型转换为

另一种类型的参数的完整列表请参阅SQL Server联机手册

SCROLL选项允许除了前向的抽取以外向后的绝对的和相对的数据抽取一个滚动游标使用一种键集合的游标模型在该模型中

任何用户提交的对表的删除和更新都将影响后来的数据抽取只有在游标没有用INSENSITIVE选项声明时上面的特性才起作用

如果选择了READ ONLY选项对游标中的行的更新就被禁止该选项将覆盖游标的缺省选项枣允许更新

UPDATE [OF column_list]语句用来在游标中定义一个可更新的列如果提供了[OF column_list]那么仅仅是那些列出的列可以被修改

如果没有指定任何列则所有的列都是可以更新的除非游标被定义为READ ONLY

重要的是注意到一个SQL Server游标的名字范围就是连接自己这和本地变量的名字范围是不同的

不能声明一个与同一个用户连接上的已有的游标相同名字的游标除非第一个游标被释放

打开一个游标

TransactSQL 不支持向一个打开的游标传递参数这一点和PL/SQL 是不一样的当一个TransactSQL 游标被打开以后

结果集的成员和顺序就固定下来了其它用户提交的对原表的游标的更新和删除将反映到对所有未加INSENSITIVE选项定义

的游标的数据抽取上对一个INSENSITIVE游标将生成一个临时表

抽取数据

Oracle 游标只能向前移动枣没有向后或者相对滚动的能力SQL Server游标可以向前或者向后滚动具体怎么滚动

要由下表给出的数据抽取选项来决定只有在游标是用SCROLL选项声明的前提下这些选项才能使用

卷动选项 描述

NEXT 如果这是对游标的第一次提取则返回结果集合的第一行否则在结果结合内移动游标到下一行

NEXT是在结果集合中移动的基本方法 NEXT是缺省的游标提取（fetch）

PRIOR 返回结果集合的前一行

FIRST 把游标移动到结果集合的第一行同时返回第一行

LAST 把游标移动到结果集合的最后一行同时返回最后一行

ABSOLUTE n 返回结果集合的第n行如果n为负数则返回倒数第n行

RELATIVE n 返回当前提取行后的第n行如果n是负数则返回从游标相对位置起的倒数第n行

TransactSQL 的FETCH语句不需要INTO子句如果没有指定返回变量行就自动作为一个单行结果集合返回给客户但是

如果你的过程必须把行给客户一个不带游标的SELECT语句更有效一些

在每一个FETCH后面@@FETCH_STATUS函数被更新这和在PL/SQL 中使用CURSOR_NAME%FOUND和CURSOR_NAME%NOTFOUND变量是相似的

@@FETCH_STATUS函数在每一次成功的数据抽取以后被设定为如果数据抽取试图读取一个超过游标末尾的数据则返回一个为的值

如果请求的行在游标打开以后从表上被删除了@@FETCH_STATUS函数就返回一个为的值只有游标是用SCROLL选项定义的情况下

才会返回值在每一次数据抽取之后都必须检查该变量以确保数据的有效性

SQL Server不支持Oracle 的游标FOR循环语法

CURRENT OF子句

更新和删除的CURRENT OF子句语法和函数在PL/SQL 和TransactSQL 中是一样的在给定游标中在当前行上执行定位的UPDATE和DELETE

关闭一个游标

TransactSQL 的CLOSE CURSOR语句关闭游标但是保留数据结构以备重新打开PL/SQL 的CLOSE CURSOR语句关闭并且释放所有的数据结构

TransactSQL 需要用DEALLOCATE CURSOR语句来清除游标数据结构DEALLOCATE CURSOR语句同CLOSE CURSOR是不一样的

后者保留数据结构以备重新打开DEALLOCATE CURSOR释放所有与游标相关的数据结构并且清除游标的定义

游标示例

下面的例子显示了在PL/SQL 和TransactSQL 等价的游标语句

Oracle Microsoft SQL