SQL Server vs Oracle 存储过程语法转换12.docx

SQL Server vs Oracle 存储过程语法转换12.docx

《SQL Server vs Oracle 存储过程语法转换12.docx》由会员分享，可在线阅读，更多相关《SQL Server vs Oracle 存储过程语法转换12.docx（13页珍藏版）》请在冰豆网上搜索。

SQLServervsOracle存储过程语法转换12

一、SQLServervsOracle简单语法比较

此为本人将ORACLE函数和存储过程转换为SQLSERVER遇到的一些语法问题的经验总结，肯定不能包括所有的语法不同点。

注：

简单的语法异同

1、SQLSERVER变量必须以@开头。

2、SQLSERVER语句后不需要写分号结束符。

3、oracle变量类型number可以修改为sqlserver的decimal

4、oracle变量类型varchar2可以修改为sqlserver的varchar

5、SQLSERVER定义变量及传递参数，最好加上参数大小数值，例如：

varchar（50）

5、SQLSERVER不能用ROWID,ROWNUM（但可以用TOP代替）

6、oracle里的nvl函数，在SQLSERVER里使用ISNULL函数取代

7、SQLSERVER自定义函数不允许修改全局表数据（只允许修改自定义函数范围内表数据）,所以发生表修改的最好用存储过程实现而非函数。

1create函数或存储过程异同点

Oracle创建函数或存储过程一般是createorreplace……

SQLSERVER则是在创建之前加一条语句，先判断是否已经存在，如果存在删除已有的函数或存储过程。

函数语句

ifexists（select*fromdbo.sysobjectswhereid=object_id（N'[dbo].[函数名]'）andxtypein（N'FN',N'IF',N'TF'））

dropfunction[dbo].[函数名]

GO

存储过程

ifexists（select*fromdbo.sysobjectswhereid=object_id（N'[dbo].[存储过程名]'）andOBJECTPROPERTY（id,N'IsProcedure'）=1）

dropprocedure[dbo].[存储过程名]

GO

2结构异同点

ORACLE

Create部分

IS定义部分

BEGIN…END;实现部分

SQLSERVER

Create部分

AS定义和实现部分（AS下面的代码一般用BEGIN…END包含）

3调用参数

ORACLE输入参数参数名In参数类型

ORACLE输出参数参数名Out参数类型

SQLSERVER输入参数参数名参数类型IN（IN可以不写，系统默认）

SQLSERVER输出参数参数名参数类型OUTPUT

4变量命名及赋值

ORACLE

1、变量名随便取

2、定义格式为变量名变量类型;

3、给变量赋值为变量名:

=值；

SQLSERVER

1、变量名前面一般加@

2、定义格式为declare变量名变量类型

3、SET变量名=变量类型

5IF语句

ORACLE

IF…THEN

….

ELSE

...

ENDIF;

SQLSERVER

IF...BEGIN

……

END

ELSEBEGIN

……

END

或者

IF...

BEGIN

……

END

ELSE

BEGIN

……

END

6case语句

ORACLE

IF…THEN

….

ELSE

...

ENDIF;

SQLSERVER

IF...BEGIN

……

END

ELSEBEGIN

……

END

或者

IF...

BEGIN

……

END

ELSE

BEGIN

……

END

7游标的定义及使用及循环操作

ORACLE定义游标

CURSORCurAISSELECTaFROMtabwhere…;

SQLSERVER定义游标

DECLARECurACURSORLOCALFORSELECTaFROMtabwhere…;

ORACLE使用游标

OpenCurA;--打开游标

FetchCurAIntoISUserUnitPri;

IFCurA%NOTFOUNDTHEN--注：

如果为CurA%FOUND，看下面相同位置注释

ISUserUnitPri:

=1;

ENDIF;

CloseCurA;--关闭游标

SQLSERVER使用游标

OpenCurA--打开游标

FetchnextfromCurAInto@ISUserUnitPri

IF@@fetch_status<>0BEGIN--注：

则@@fetch_status=0

SET@ISUserUnitPri=1--没有选到记录给默认值1

END

CloseCurA--关闭游标

DEALLOCATECurA--释放占用资源

ORACLE循环操作游标（超级简洁）

FORISUserUnitPriINCurALOOP

…–-做操作

ENDLOOP;

注：

想循环中间退出循环，用EXIT

SQLSERVER循环操作游标

OpenCurA--打开游标

FetchnextfromCurAInto@ISUserUnitPri

While（@@fetch_status=0）BEGIN

….–-做操作

FetchnextfromCurAInto@ISUserUnitPri

END

CloseCurA--关闭游标

DEALLOCATECurA--释放占用资源

注：

想循环中间退出循环，用BREAK

注意：

SQLSERVER使用游标完后，需要删除游标引用（DEALLOCATEcursor_name）。

8计算时间差

ORACLE

Oracle两个时间相减得到一个以天为单位的带小数的值，需要根据自己的需要再换算成秒值。

--这里为取START_QUEUE_TIME到当前时间的秒数

（SYSDATE-START_QUEUE_TIME）*24*60*60

SQLSERVER

SQLSERVER两个时间相减得到还是时间（从1900-01-0100:

00:

00.000开始的时间）。

所以想得到以秒的时间差，这么做就麻烦了。

SQLSERVER取时间差，专门有一个DATEDIFF函数，具体看SQLSERVER帮助。

--这里为取START_QUEUE_TIME到当前时间的秒数

DATEDIFF（second,START_QUEUE_TIME,GETDATE（））

9topN问题

在sqlserver中，topN问题很容易解决，如下例：

从表stbdbdj中选取排序后的第一行数据进行赋值。

在sql中解决方法很简单，在select后面加上：

topn即可，其中n代表行数。

select top 1 @entrust_date = entrust_date,

@entrust_no = entrust_no

from run2k..stbdbdj

where entrust_date = @date

and entrust_no > @entrust_no_q

and report_status = '1'

order by entrust_date,entrust_no；

在oracle中，没有topn这个命令，我们采取把两层查询方式解决：

首先，把需要查找的字段值直接进行排序，然后在外面进行第二次查询，并使用rownum决定行数。

select entrust_date,entrust_no

into @entrust_date, @entrust_no

from （ select entrust_date,entrust_no

from stbdbdj

where entrust_date = @date

and entrust_no > @entrust_no_q

and report_status = '1'

order by entrust_date,entrust_no ）

where rownumber <=1 ;

10如何解决结果集返回时，*和变量同时存在的问题

下面例子表示，在用游标返回结果集时，同时返回一个变量的值，在sqlserver中代码如下所示：

select a.*,b.organ_id

from run2k..stbbp a,run2k..stkaccoarg b

where a.date = @entrust_date

and a.serial_no = @serial_no

and a.branch_no = b.branch_no

and a.exchange_type = b.exchange_type;

但在oracle中却没有这种用法，’*’后面必需跟from。

解决方法如下：

1）我们可以把'*'变成所需要选择的字段，就是说采用表中需要显示的全部字段表示*。

例如：

open p_cursor for

select branch_no,...,organ_id

where...

2）如果这个字段或者说变量是从另外一张表中取出来的，同样可以采用下面的办法。

open p_cursor for

select a.*,b.organ_id;

from stkaccoentrust a, stkaccoarg b

where a.branch_no = b.branch_no

and a.exchange_type = b.exchange_type

and a.init_date = v_entrust_date

and a.serial_no = v_serial_no;

11外联接问题

Sqlserver<--->oracle

a=*b<--->a（+）=b

a*=b<--->a=b（+）

12多条记录求和问题

selectsum（A+B+C）

intoD

from...

where...

groupby...

单条记录求和

selectA+B

intoC

from...

where...

13用SQLSERVER里CASE函数替换DECODE函数替换

ORACLE

decode（client_status,'0','正常，'1','冻结','2','挂失','3','销户','未知'）;

SQLSERVER没有DECODE函数

caseclient_status

when'0'then'正常'

when'1'then'冻结'

when'2'then'挂失'

when'3'then'销户'

else'未知'

end

注：

有趣的是ORACLE的CASE函数，在SQLSERVER里没有找到替代的，只好用IFELSE语句解决。

14oracle的select…into问题

ORACLE里直接取字段值，用select…into语法

selectunit_idintounitidfromcall_user_tablewhereuser_id=‘1231312’;

SQLSERVER直接取则直观的多，直接等于就可以了

select