Oracle与SQLServer的SQL语法差异总结

1、Oracle 与 SQL Server应用差异对比分析项目Oracle 10gSQLServer 2008备注存储过CREATE OR REPLACE PROCEDURECREATE PROCEDURE 存 储 过 程 名程格式存储过程名 （ 参数列表 ） IS（ 参数列表 ） ASBegin- 存储过程内容- 存储过程内容End 存储过程名 ;存储过1、 指定参数类型，但不指定长度1、 指定参数类型，并要指定长度程参数2、 在参数及类型间要加入出标识2、 对 于 出 参 需 要 在 类 型 后 面 加(IN、OUT)OUTPUT(或 OUT)Eg:Eg:task_remark IN VARC

2、HAR2task_remark VARCHAR(10)prm_code OUT NUMBERprm_code int output使用存1、 直接使用存储过程名1、 调 用 存 储 过 程 名 前 面 需 要 加储过程exec 关键字2、 参数在存储过程后面的括号包2、 参数在存储过程后面逐个列出内列出3、 出参后面要加关键字 output 或outEg:Eg:p_wfm_getcolumns(v_tablenaEXEC P_WFM_GETCOLUMNSme,v tablecolumn);tablename , tablecolumnOUTPUT自定义CREATE OR REPLACE FUN

3、CTIONCREATE FUNCTION 函数名 ( 参数函数格函数名 （ 参数列表 ）列表 ）式RETURN 返回值类型 ISRETURNs 返回值类型 ASBEGIN- 函数内容END;自定义1、 指定参数类型，但不指定长度1、 指定参数类型，并要指定长度函数内2、 在参数及类型间要加入出标识2 、 不支持出参容(IN、OUT)Eg:Eg:task_remark IN VARCHAR2task remarkVARCHAR(10)prm_code OUT NUMBER3、 支持对数据增、删、改操作3、 不支持对数据增、删、改操作4、 支持动态 SQL语句4、 不支持动态 SQL语句使用自1、

4、 直接使用函数名1、 在函数名前面加上 dbo.定义函Eg:Eg:数V result:=f wfm isandbegiSet result=n(flow_id, step_id)dbo.f_wfm_isandbegin(flow_id, step id)游标1、 游标声明1、 游标声明DECLARE cursor 游标名 isDECLARE 游 标 名 cursor forselect 语句select 语句2、3、使用游标过程 打开 (open)- 提 取(fetch)-关闭 (close) 支持快捷使用游标，直接使用 for 循环，数据库会自动打开、 提取及关闭游标2、使用游标过程 打开

5、(open)- 提取 (fetch)- 关闭 (close)-销毁 (变量1、变量前不可加 符号1、变量前需要加 符号2、存储过程中变量声明不需要2、变量声明需要使用 Declare 关键declare字Eg:Eg:Code varchar2 ( 5);DECLARE code varchar(5) ;3、变量类型可按表中字段类型动态定义3、不支持按表字段类型动态定义Eg:V_id sysc01.id%type;赋值1、变量直接赋值 ,变量 := 表达式 ;1、变量直接赋值， Set 变量 =表达式 ;Eg:Eg:v_result := abcd;Set result = abcd2、通过 S

6、QL语句2、通过 SQL语句赋值Select 表达式 into 变量 fromSelect 变量 =表达式 from 表表Eg:Eg:SelectSelect code,name intocode=code,name=name fromv_code,v_name from sysc01sysc01 where id = 1000Where id = 1000语句结SQL 语句使用分号 ; 作为语句的结SQL 语句不需要加分号 ;作为结束符束符束(加也可)大小写Oracle 对字符区分大小写默认对字符不区分大小写， 也可修改数据库配置支持区分大小写序列1、有序列 sequence 对象，无自动

7、增长列1、无序列对象，表中有自动增长列Select1、结果集可做为表使用，使用时1、结果集可做为表使用，使用时必语法Eg:可不加别名Eg:须加别名Select * from (select * from tab1)Select * from (select * from tab1) a2、虚表 dual 的使用，对于 select2、对于 Select 计算某些与实体表无计算某些与实体表无关的表达关的表达式时，可使用不带 from式时，要使用虚表 dual的 select 语句Eg:Eg:Select round(1/3,2) from dual ;Select round(1/3,2) ;3

8、、3、Update不可关联表更新可关联表更新将 a 表语法Eg:Eg:中For cur in (select a.id,b.value from aUpdate a set a.value = isnull(b.value, ) valueinner join b on a.id = b.id ) loopUpdate a set a.value = cur.valueWhere a.id = cur.id;End loop;或Update a set a.value = (select b.value From b where b.id = a.id )From a inner join b

9、 on a.id = b.id或Update a set a.value = isnull(b.value, ) From b where a.id = b.id按 更 成b 中 value 值ID 新 表 的Delete不可关联表删除可关联表删除删除a语法Eg:Eg:表中 IDDelete a where exists (select b.idDelate a值在bfrom b where a.id = b.id )From a表中Inner join b on a.id = b.id存在的记录动态1、 普通动态 SQL语句1、 普通动态 SQL语句SQL 语Beginexec(update

10、 tab1 set column1 = 5)句Execute immediate update tab1 set或column1=5 ;exec sp_executesql Nselect * fromEnd;tableName - 字符串前一定要加 N用变量替换 SQL语句用变量替换 SQL语句V_sql := update tab1 set column1=5 Declare sql Nvarchar(1000)Execute immediate v_sql;Set sql=select * from tableNameexec sp_executesql sql2、 带出参动态 SQL语

11、句2、 带出参动态 SQL语句n_count number(10);declare count intv_sql varchar2(1000);declare sqls nvarchar(4000)v_sql := select count(*) fromset sqls=select a=count(*) fromtablename ;tableName execute immediate v_sqlexec sp_executesql sqls,Na intinto n_count;output,count output3、 动态存储过程（带入、出参）3、 动态存储过程（带入、出参）Eg:

12、Eg:v sql :=begin p testDECLARE result VARCHAR( 50);(:v1,:v2,:v3); end;DECLARE sql NVARCHAR( 1000 );execute immediate v_sqlDECLARE para NVARCHAR( 200 );using in v code, inSET sql = p_testv name, out v result ;code,name,result output注： p_test 为存储过程名SET para = codevarchar(10),namevarchar(10),resultvarc

13、har(50) outputEXECsp executesqlsql , para , 001 , 张 三 , result OUTPUT 注： p test 为存储过程名TOP 用 法1、在 Oracle 中采用伪列 rownum 获 取结果集中排在前面的部分记录 Eg: 返回结果集中前 10 条记录 Select * from sysc01 where rownum =10Rownum 可使用 、 、 =符号，如果使用 =号只可 =1 2、Rownum 列还常用于形成结果集 的顺序号，从而可获取一定序号范 围的行Eg: 获取按 code 排名第 10 到 20 行 Select * fro

14、m (Select a.*,rownum as nrow from tab01 a order by code) where nrow between 10 and 201、 SQLServer中采购 top 方式获取结 果集排在前面的部分记录 Eg:返回结果集中前 10 条记录 Select top 10 * from sysc012、 sqlserver 可通过 ROW_NUMBER() 排名函数实现Eg: 获取按 code 排名第 10 到 20 行 SELECT a.* FROM ( SELECT a.*, ROW_NUMBER() OVER( ORDER BY a. code ) A

15、S nrow FROM tab01 a) a where nrow between 10 and 20IF Else 流控制IF 条件表达式 then 语句块 Else语句块 End if ;IF 条件表达式语句块 Else语句块 End如果语句块中有多于 1 条 SQL语句， 则必须要使用 begin end 构造Case 用 法1、 表达式Case 表 达 式 when 匹 配 表 达 式 then 结果表达式 1 else 结果表达 式 2 endEg:Select case name when 张三 then 1 when 李四 then 2 else 0 end From perso

16、n 或Case when 条件表达式 then 结果 表达式 1 else 结果表达式 2 end Eg:Select case when name= 张三 then 1 when name= 李四 then 2 else 0 endFrom person2、 流控制语句 流控制与表达式结构很相似，只是1、 case 表达式 同 Oracle2、不支持流控制临时表常用函 数结果表达式换成语句块Case 表 达 式 when 匹 配 表 达 式 then 语句块 1 else 语句块 2 end case;Eg: case v_name when 张三 then v_number := 1;wh

17、en 李四 then v_number:=2;else v_number:=0end case; 或Case when 条件表达式 then 语句 块 1 else 语句块 2 end case;Eg: case when v_name = 张三 then v_number := 1;when v_name = 李四 then v_number:=2;else v_number:=0end case;1、 临时表结构需要象实体表一样 事先定义后再在 SQL 脚本中使 用，临时表名与实体表名规则 一样Eg: create temporary table WFMW02paraidNUMBER( 9

18、),vvalueVARCHAR2( 50),flowidNUMBER( 9),idNUMBER( 9)on commitdeleterows ;(1、 nvl（表达式 ,表达式 ）2、 sysdate3、 instr（ 字符表达式 1, 字符表达式1、 临时表是在执行数据库脚本会话 过程中创建并使用，临时表名前 面带有 #号（或 #全局临时表 ） Eg:create table #WFMW02paraidint ,vvalueVARCHAR( 50),flowidint ,(id int )临时表也可在使用过程中自动创建 Selectparaid,vvalue,flowid,id into #wfmw02 from wfmb01c1、 isnull( 表达式 ,表达式 )2、 getdate()3、 CHA