先贴两段代码
函数:给定一个日期,判断是否是休息日.
sqlserver
create function [user].[test]
(
@date_in datetime
)
returns integer
as
begin
declare @num int,
@workflag varchar(1)
select @num = count(*) from sys_holiday
where datediff(dd, today, @date_in) = 0
if (@num > 0)
select @workflag = workflag from sys_holiday
where datediff(dd, today, @date_in) = 0
if (@workflag='w') then
return 0
else
return 1
else
select @num = datepart(dw,@date_in)
if (@num = 7 or @num = 1)
return 1
else
return 0
end
oracle
create or replace function test(date_in in date)
return int
is
num int;
workflag varchar2(1);
begin
select count(*) into num from Sys_Holiday
where to_char(TODAY,'yyyy-MM-dd')=to_char(date_in,'yyyy-MM-dd');
if (num>0) then
select WORKFLAG into workflag from Sys_Holiday
where to_char(TODAY,'yyyy-MM-dd')=to_char(date_in,'yyyy-MM-dd');
if (workflag='W') then
return 0;
else
return 1;
end if;
else
select to_char(date_in,'D') into num from dual;
if (num=7 or num=1) then
return 1;
else
return 0;
end if;
end if;
end;
可以看出,两者在写法上还是存在不少区别的。
区别一:sqlserver只能写create而oracle可以写create or replace
区别二:sqlserver的传入参数和变量名都加@
区别三:sqlserver函数名加[]
区别四:一些变量类型不同,比如sqlserver的datetime
区别五:关键字不同,returns
区别六:语法不同,oracle的If else语句结尾要写end if
区别七:基本函数不同,比如sqlserver的datepart对应oracle的to_char(sysdate,‘D’),sqlserver的datediff和oracle的months_between
区别八:sqlserver中写 select @variable=column,oracle是select column into variable