oracle函数如何转换为sqlserver函数?

oracle函数代码:
CREATE FUNCTION [test].[GETWORKORDERID] (numberPre varchar2)
return varchar2 is
PRAGMA AUTONOMOUS_TRANSACTION;
findId number(8); --最大id
nowNumber varchar2(50);
n_count number(8);
n_count2 number(8);
nowNumber2 varchar2(50);
begin

nowNumber := to_char(sysdate, 'yyyymmdd');

nowNumber2 := to_char(sysdate - 1, 'yyyymmdd');

select count(1)

into n_count2
from user_sequences t

where t.sequence_name = 'SEQ_' || numberPre || '_' || nowNumber2;

if n_count2 > 0 then

execute immediate 'drop sequence SEQ_' || numberPre || '_' ||
                  nowNumber2;

end if;

select count(1)

into n_count
from user_sequences t

where t.sequence_name = 'SEQ_' || numberPre || '_' || nowNumber;

if n_count = 0 then

execute immediate 'create sequence SEQ_' || numberPre || '_' ||
                  nowNumber ||
                  ' minvalue 10000 maxvalue 99999999 start with 10000 increment by 1 NOCYCLE NOCACHE';

end if;

execute immediate 'select SEQ_' || numberPre || '_' || nowNumber ||

                '.nextval from dual'
into findId;

commit;
return numberPre || nowNumber || findId;
end;

转换为sqlserver代码:
CREATE FUNCTION [ZZC-CSR].[GETWORKORDERID] (@numberPre nvarchar(max))
returns table
as
begin
DECLARE @findId bigint;
DECLARE @nowNumber bigint;
DECLARE @n_count bigint;
DECLARE @n_count2 bigint;
DECLARE @nowNumber2 bigint;
DECLARE @sql1 nvarchar(max);
DECLARE @sql2 nvarchar(max);
DECLARE @sql3 nvarchar(max);
DECLARE @sql4 nvarchar(max);
DECLARE @result nvarchar(max);
set @nowNumber=convert(varchar(30),getdate() ,20)
set @nowNumber2=convert(varchar(30),getdate() ,20)
select count(1)

into n_count2
from user_sequences t

where t.sequence_name = 'SEQ_' + numberPre + '_' + nowNumber2;

if n_count2 > 0 begin

set @sql1= 'drop sequence SEQ_' + numberPre + '_' + nowNumber2;
execute (@sql1) 

end ;

select count(1)

into n_count
from user_sequences t

where t.sequence_name = 'SEQ_' + @numberPre + '_' + @nowNumber;

if n_count = 0 begin

set @sql2='create sequence SEQ_ ' + @numberPre + '_' + @nowNumber +
                  ' minvalue 10000 maxvalue 99999999 start with 10000 increment by 1 NOCYCLE NOCACHE';
execute (@sql2)

end ;

set @sql3='select SEQ_ ' + @numberPre + '_' + @nowNumber + '.NEXT VALUE FOR user_sequences';
set @findId=@sql3;
set @sql4=@numberPre + @nowNumber + @findId;
insert into @result exec (@sql4)

end;

报错信息:消息 102,级别 15,状态 31,过程 GETWORKORDERID,行 43 [批起始行 0]
“BEGIN”附近有语法错误。

不知道哪里有错误了?

阅读 1.4k
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进