Oracle 与 MS SQL 语法对比

目前我做了一个项目用的 SQL Server 2017数据库，老板要求上线后换成Oracle，现在开始恶补放了好久的Oracle，记录下Oracle和 MS SQL 的差异点以便学习。老规矩，资料来源会以链接的形式写在文末，感谢dalao们。
环境：Oracle - 版本11g，SQLPLUS；SQL Server - 版本2017，SSMS 2017

一、CRUD

1. 查询数据，使用列别名和表别名进行查询（不使用别名的情况下二者SELECT语法相同）

-- PLSQL 在列名或表名后面直接跟上别名，不需要单引号
SELECT T.TNAME NAME, T.TSEX SEX FROM TEACHER T;
-- T-SQL 支持多种别名声明方式，单引号和 AS 在这里可有可无，下面三种都可以实现
SELECT T.TNAME AS 'NAME', T.TSEX AS 'SEX' FROM TEACHER AS 'T';
SELECT T.TNAME AS NAME, T.TSEX AS SEX FROM TEACHER AS T;
SELECT T.TNAME NAME, T.TSEX SEX FROM TEACHER T;

不过在查询前 n 条数据上，MS SQL 和 Oracle 存在差异，T-SQL 使用 TOP 关键字限定返回条数，而 PLSQL 使用伪列 ROWNUM：

--T-SQL
SELECT TOP(10) * FROM STUDENT;

-- PLSQL
SELECT * FROM STUDENT WHERE ROWNUM <= 10;

对于 T-SQL 而言，要筛选前 n 条直接 TOP 就完事儿了；不过对于 PLSQL 而言，因为 ROWNUM 是参与条件过滤的缘故，所以你找出来的前 n 条是过滤后排序前的 n 条信息，如果你想找逆序排序后的前 n 条，就得换个其他方案了。

2. 更新 / 删除数据，二者语法没有什么明显区别：

UPDATE TEACHER SET TNAME = 'SAM_BRIDGES' [FROM TEACHER] WHERE TNAME = 'SAM_POTTER';
DELETE FROM TEACHER T WHERE T.NAME = 'HIGGS' -- or someone else.

3. 新增数据
   在单条数据插入和使用选择集插入上二者语法相似：

INSERT INTO TEACHER(TNO, TNAME, TSEX, TSALARY) VALUES(1005, 'HEART_MAN', Male, 6000);

INSERT INTO TEACHER SELECT ... FROM ... [WHERE CONDITION];

不过在插入多条数据时，二者有了不同的选择。MS SQL 允许用户在一条语句中对单表插多条数据：

INSERT INTO TEACHER
VALUES
	( ...... ), -- VALUE_1
	( ...... ); -- VALUE_2

-- 该方法同时适用于插入多条数据到变量表中

Oracle 则是使用 INSERT ALL 实现插入多条数据到单个或多个表中：

INSERT ALL
	INTO TABLE_A( COLUMN... ) VALUES( VAL_1... )
	INTO TABLE_A( COLUMN... ) VALUES( VAL_2... )
	INTO TABLE_B( COLUMN... ) VALUES( VAL_3... )
	...

二、变量定义 & 赋值 & 使用

1、MS SQL 变量

1.1、临时变量 & 全局变量

MS SQL 的变量理论上也能写一堆出来，不过在这里我们只对常用的做简单说明。
T-SQL 声明变量使用 DECLARE 关键字，此方法可直接声明一个当前语句块内可用的临时变量：

DECLARE @_NAME NVARCHAR(20);

对于 MS SQL 而言全局变量都是由系统提供的，以 ‘@@’ 作为前缀，可以直接访问。以 @@ROWCOUNT 为例，为了提高触发器的执行效率，我们可以这么写：

CREATE TRIGGER ti_tablea ON tablea AFTER UPDATE
AS
BEGIN
	-- @@ROWCOUNT 为0 时，说明没有数据发生变更，跳过剩余内容
	IF @@ROWCOUNT = 0 RETURN; 
	[ STATEMENT_BLOCKS... ]
END

这只是全局变量的其中之一，有兴趣的可以在网上查查看 SQL Server 的全局变量列表。

1.2、自定义表类型

MS SQL 允许用户自定义变量的类型，包括数据类型、表类型等，在SSMS中在 “可编辑性” 下面可以找到 “类型” 选项，自定义数据类型本质上还是在基础数据类型上进行包装而来，在这里不做过多解释，我在这里讲讲自定义表类型，因为在接下来的存储过程中可能会用到它。
有时候，我们会需要将查询结果直接传递到存储过程的内部以减少重复查询的次数。在下面这个情境中，我们需要按批次查找 Product 表，如果该批次产品有一件出现了问题，则需要把该批次的产品全部抓出来处理。这个时候我们就会用到自定义表类型，因为我们不可能把一整坨的数据跑两遍然后全扔进去：

CREATE TYPE <schema_name,sysname,dbo>.<type_name,sysname,TVP> AS TABLE 
(
	<columns_in_primary_key , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>, 
	<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>, 
	<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>, 
    PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

上述 SQL 是 MS SQL 自己的表类型创建语法，本质上就是声明一个类型名，然后设计表结构。声明完成之后，我们就可以像使用普通变量一样使用它了：

CREATE TYPE product_info AS TABLE
(
	PID INT,
	PBATCH CHAR(6) NOT NULL,
	ERROR_CODE INT NOT NULL,
	PRIMARY KEY(PID)
);

DECLARE @DEFECT_PRODUCT product_info;
SELECT PID, PBATCH, ERROR_CODE INTO @DEFECT_PRODUCT FROM [dbo].[PRODUCT] A
WHERE A.ERROR_CODE <> 0 AND A.PBATCH = 'PB_001';
IF @@ROWCOUNT > 0
BEGIN
	-- 出于个人习惯每个IF语句块我都会用 BEGIN-END 替代大括号，这也容易确定代码范围
	EXEC PROC_CHECK_DEFECT_PRODUCT @DEFECT_LIST = @DEFECT_PRODUCT;
END

MS SQL 的表类型使用起来十分轻松写意，这意味着我们可以把查询数据集像数组一样扔到我们的存储过程中当参数使用。不过要注意的是，作为参数时，表类型变量必须是只读的（readonly），不能被赋值不能被修改，使用时需注意该限制。

1.3、赋值 & 使用

在 MS SQL 中，我们使用 = 进行赋值，使用变量时需加上 @ 前缀：

DECLARE @NAME CHAR(4);					-- 定义
SET @NAME = 'NAME'; 					-- 赋值
-- SELECT NAME INTO @NAME FROM ...;		-- 另一种赋值方式
SELECT @NAME;							-- 使用变量

2、Oracle 变量类型

2.1、常见变量类型

Oracle 的变量类型分很多种，我在之前的文章里有详细说明过，这里就不重复了，想看的移步 Oracle 存储过程详解（上），顺着目录就能看见。

2.2、Oracle 的 “自定义表类型”

在前文中我们讲到了 SQL Server 的自定义表类型，在 Oracle 中是没有这样的操作的，但我们可以使用关联数组实现同样的效果。
关联数组是一种更加复杂的记录类型，使用指定类型的索引 index 进行遍历，其元素类型可以是任意一个标量类型或记录类型：

type emp_record_type is record (  
    r_name NVARCHAR(10), 
    r_salary NUMBER(7, 2)
);  
type table_employee_record is table of emp_record_type
index by NUMBER(5);
v_table_emp table_employee_record;

上述 SQL 创建了一个名为 emp_record_type 的含有两个字段的记录类型，随后通过is table of关键字创建了名为 table_employee_record 的索引类型为NUMBER(5)的关联数组，最后，我们声明了该数组的一个对象 v_table_emp。
创建完成之后，我们需要对该数组进行赋值。根据已学知识，我们需要利用 index 对数组进行遍历赋值：

FOR i IN 1..10 LOOP
BEGIN
	v_table_emp(i).r_name := CONCAT('NAME_', i);
	v_table_emp(i).r_salary := 1000 * i;
END LOOP;

这样，我们就获得了一个近似表变量的数组，在使用的时候，只需要将参数类型设定为该关联数组的类型即可读取。

2.3、赋值 & 使用

在 Oracle 中，我们通过 DECLARE 定义变量，用 := 进行赋值，使用变量时，直接使用其变量名即可：

SQL >	SET SERVEROUTPUT ON; 
SQL > 	DECLARE 
  2 >		FLAG NUMBER(3);
  3 >	BEGIN
  4 >		FLAG := 1;
  5 >		dbms_output.put_line('FLAG = ' || FLAG);
  6 >	END;
  7 > 	/

输出结果 FLAG = 1

三、表变量 & 临时表

1、表变量

在 MS SQL中，我们可以通过定义表变量来临时存储某一查询结果，它可以帮助我们让查询结果更加合理有序而不必特地创建这一表类型：

-- 定义表变量
DECLARE @VAR_TABLE AS TABLE(
	ID INT,
	NAME VARCHAR(20),
	PRIMARY KEY(ID)
)

-- 使用 INSERT 向表变量插入数据
INSERT INTO @VAR_TABLE SELECT A.stu_id, A.name FROM [dbo].[Student] A;

-- 遍历查询
SELECT * FROM @VAR_TABLE;

对于 MS SQL 而言，合理使用的表变量在存储过程中不会被重复编译，读取方便，而且使用后自动释放，不会进log；但相对的表变量不能持久化保存，不能check约束，读取内容过多的话也会消耗硬盘空间，不会被事务回滚，对于少量临时数据，建议使用表变量。

而在 Oracle 中没有表变量这一概念，如果想做到同样的效果，也是使用关联数组来实现。

2、临时表

2.1、MS SQL

在MS SQL中，我们使用 # 作为临时表的前缀，可以使用 SELECT … INTO 创建临时表，也可以使用 DECLARE：

-- 显式删除临时表以避免因存在同名表而产生异常
IF(object_id('TEMPDB..#VAR_TABLE')) IS NOT NULL DROP TABLE #VAR_TABLE;

SELECT A.stu_id, A.name INTO #VAR_TABLE FROM [LocalTest].[dbo].[Student] A;

SELECT * FROM #VAR_TABLE;

在 MS SQL 中，临时表可以保存在磁盘中，可以自由读写，对表的修改会入log，会受到事务回滚的影响。根据作用范围的不同，临时表也分全局临时表和局部临时表两种，当创建表的 session 结束时，临时表也会被自动删除，不过出于稳妥，还是手动删除为妙。

2.2、Oracle

Oracle 的临时表统一存放在临时表空间中，可分成 “事务级” 和 “会话级” 两种：

-- 事务级
CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE ON COMMIT DELETE ROW
AS ...

-- 会话级
CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE ON COMMIT PRESERVE ROW
AS ...

对 Oracle 的临时表进行修改不像 MS SQL 的临时表一样会有日志产生，但可以被truncate，当一个事务 commit 或 rollback 后，临时表的数据也会被清空，事务级的临时表在各个会话中也是相互独立的，在业务优化的时候有不错的表现。

四、存储过程

作为数据库至关重要的一环，MS SQL 和 Oracle 均支持用户通过创建存储过程来提高 SQL 效率和可维护性，可以携带基本数据类型参数或是自定义类型参数（MS SQL 的自定义类型，Oracle 的复合类型），二者主要在参数声明上有一定差别：

• MS SQL

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

• Oracle

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME (
	Name in out type, 
	Name in out type, 
	...
) 
IS
BEGIN
  -- statement
END PROCEDURE_NAME;

五、条件循环

FOR 循环和 WHILE 循环是构成方法体的重要组成部分，在 Oracle 中，我们可以借助变量和游标实现这两个循环体，而在 MS SQL 中则没有对 FOR 循环的支持，但我们仍旧可以使用游标和 WHILE 循环间接的实现 FOR 循环的效果。

5.1、FOR 循环

• MS SQL 没有 FOR 循环，所以在这里我们使用游标来实现相同的效果：

DECLARE POINTER CURSOR FOR (SELECT STU_ID, NAME FROM STUDENT);	-- 定义游标
OPEN POINTER;										-- 打开游标
DECLARE @STU_ID INT;
DECLARE @NAME VARCHAR(10);

FETCH NEXT FROM POINTER INTO @STU_ID, @NAME;		-- 移动游标，将初值存在变量中
WHILE @@FETCH_STATUS = 0							-- 判断游标内容是否为空
BEGIN
	SELECT @STU_ID, @NAME
	-- T-SQL STATEMENT BLOCK
	FETCH NEXT FROM POINTER INTO @STU_ID, @NAME;	-- 游标向下一行移动
END

CLOSE POINTER; DEALLOCATE POINTER;					-- 关闭并释放游标

在这里，我们使用游标指向一个要遍历的数据集，和 JAVA 的 ResultSet 的指针一样，游标最初指向表头而非表的第一行数据，所以我们需要先将游标下移一行使其指向第一行数据。同时，由于游标本身不保存当前行数据，我们需要定义相应的变量来存储游标带回的数据内容，对变量的修改不影响游标。在使用完毕后，使用 CLOSE 和 DEALLOCATE 关闭并释放游标，完成循环。

• Oracle 支持使用 FOR 循环，我们可以使用 FOR - LOOP 替代游标遍历集合：

BEGIN
	FOR STU_ITEM IN (SELECT * FROM STUDENT)
	LOOP
		DBMS_OUTPUT.put_line('STU_ID = ' || STU_ITEM.STU_ID);
		-- EXIT WHEN ....
	END LOOP;
END

Oracle 的 FOR 循环语法是建立在 LOOP 循环的基础之上的，可以在循环中使用 EXIT 字段结束循环，其语法习惯接近于我们在 JAVA 中使用 FOR 的习惯

5.2、WHILE 循环

• MS SQL 的 WHILE 循环在前面其实我们已经用过了，在使用游标的时候，我们就是用 WHILE 判断游标的状态：

DECLARE @CONDITION INT = 10;
WHILE @CONDITION > 0
BEGIN
	-- T-SQL STATEMENT BLOCKS
	IF .... THEN 
	BEGIN
		BREAK;
	END
END;

理论上当 WHILE 的方法体只有一条语句的时候 BEGIN - END 是可以不要的，但为了让代码看起来更有段落感、提高可阅读性，我个人还是建议在任何需要用到代码块的地方使用 BEGIN - END 作为边界。

• Oracle 的 WHILE 循环需要依靠 LOOP 实现，在中断循环的时候，使用 EXIT 替代 BREAK：

BEGIN 
	WHILE (CONDITION) LOOP
		-- PLSQL STATEMENT BLOCKS
		IF ... THEN EXIT;
		-- EXIT WHEN ...
	END LOOP;
END

Oracle 的循环完全立足于 LOOP 循环的语法基础之上，WHILE 和 FOR 均是作为 LOOP 的循环条件而存在，和 MS SQL 有很大的差别。我依稀记得 LOOP 循环似乎是可以有别名的，在 END 的时候可以通过别名结束对应的循环体，而且 Oracle 还同时支持 GOTO 语句，相较于 MS SQL 有更好的灵活性。

附录：参考资料

下面这些文章是我在写本篇时的参考资料，我一向查询的时候使用科学上网的方式看Google，所以有些网站如果打不开的话，请使用科学上网的方式访问它们。

[1] TeahOnTheNet: Oracle / PLSQL 比较系统的 Oralce 学习网站，想学 Oracle 的可以上去看看
[2] Sql Server系列：Insert语句
[3] SQL Server 中变量的声明和使用方法
[4] 【译】表变量和临时表的比较，这篇文章对 MS SQL 的表变量和临时表的分析十分到位，推荐学习
[5] ORACLE 临时表总结
[6] oracle 临时表 应用场景
[7] 利用 Oracle 数据库发送邮件
[8] Oracle 中的 FOR 和 WHILE 循环
[9] sqlserver中的循环遍历（普通循环和游标循环）