将函数从 Oracle 转换为 SQL Server 时出错

Question

我正在将一个函数从 Oracle 迁移到 SQL Server 2008。此函数会引发函数中包含的 SELECT 语句无法将数据返回到客户端的错误。我该如何解决这个问题？

原始 PLSQL 代码

CREATE OR REPLACE function f_birim_cevrim_katsayi (p_ID_MAMUL in number, p_ID_BIRIMDEN in number, p_ID_BIRIME in number) 
return number
is

v_katsayi number;

begin

v_katsayi:=0;

if p_ID_BIRIMDEN!=p_ID_BIRIME then

    for c in (
         select * from CR_BIRIM_CEVRIM
         where ID_MAMUL = p_ID_MAMUL
         and (
         (ID_BIRIM = p_ID_BIRIMDEN and ID_BIRIM2 = p_ID_BIRIME)
         OR ( ID_BIRIM2 = p_ID_BIRIMDEN and ID_BIRIM = p_ID_BIRIME) )
         and VALID = 1) 
         loop

         if c.ID_BIRIM=p_ID_BIRIMDEN then
            v_katsayi:=c.MT_ORAN;
         else
            v_katsayi:=1/c.MT_ORAN;
         end if;
    end loop;
else
    v_katsayi:=1;
end if;

return round(v_katsayi,10);

exception 
when others then 
return 0;

end;

T-SQL 代码：

If Exists ( SELECT name 
                FROM sysobjects  
                WHERE name = 'f_birim_cevrim_katsayi'
                AND type = 'FN')
        DROP FUNCTION f_birim_cevrim_katsayi
    GO 
    CREATE FUNCTION f_birim_cevrim_katsayi
    (
        @p_ID_MAMUL                               FLOAT ,
        @p_ID_BIRIMDEN                            FLOAT ,
        @p_ID_BIRIME                              FLOAT 
    )
    RETURNS float 
    AS 
        BEGIN


            DECLARE @adv_error INT


            DECLARE @v_katsayi                                FLOAT 
            SELECT @v_katsayi  = 0 
            IF @p_ID_BIRIMDEN != @p_ID_BIRIME 
            BEGIN 


                DECLARE cursor_for_inline_select1 CURSOR LOCAL FOR 
                SELECT *
                FROM  CR_BIRIM_CEVRIM 
                WHERE    ID_MAMUL  = @p_ID_MAMUL
                 AND    ((ID_BIRIM  = @p_ID_BIRIMDEN
                 AND    ID_BIRIM2  = @p_ID_BIRIME)
                 OR (ID_BIRIM2  = @p_ID_BIRIMDEN
                 AND    ID_BIRIM  = @p_ID_BIRIME))
                 AND    VALID  = 1

                OPEN cursor_for_inline_select1 

                FETCH NEXT FROM  cursor_for_inline_select1  
                WHILE (@@FETCH_STATUS <> -1)
                BEGIN
                    IF   c.ID_BIRIM = @p_ID_BIRIMDEN 
                    BEGIN 
                        SELECT @v_katsayi  = c.MT_ORAN 
                    END
                    ELSE
                    BEGIN 
                        SELECT @v_katsayi  = 1/c.MT_ORAN 
                    END

                END
                CLOSE cursor_for_inline_select1
                DEALLOCATE cursor_for_inline_select1

            END
            ELSE
            BEGIN 
                SELECT @v_katsayi  = 1 
            END


     DEALLOCATE cursor_for_inline_select1
            return ROUND(@v_katsayi, 10) 
            GOTO ExitLabel1
            Exception1:

                BEGIN

     DEALLOCATE cursor_for_inline_select1
                return 0 
                            END
            ExitLabel1:

            return ROUND(@v_katsayi, 10) 

        END


    GO

Answer 1

来自创建函数

引用本地的游标操作
声明、打开的游标，
关闭并解除分配
功能。仅 FETCH 语句
使用以下方法为局部变量赋值
允许使用 INTO 子句；拿来
返回数据的语句
不允许客户端

这意味着这是错误的

DECLARE cursor_for_inline_select1 CURSOR LOCAL FOR 
            SELECT *
            FROM  CR_BIRIM_CEVRIM 
            WHERE    ID_MAMUL  = @p_ID_MAMUL
             AND    ((ID_BIRIM  = @p_ID_BIRIMDEN
             AND    ID_BIRIM2  = @p_ID_BIRIME)
             OR (ID_BIRIM2  = @p_ID_BIRIMDEN
             AND    ID_BIRIM  = @p_ID_BIRIME))
             AND    VALID  = 1

您没有将 CURSOR 输出分配给局部变量。

请描述您想要对示例数据执行的操作：我确信这是一个带有 CASE 的简单 SELECT

编辑：除非我的 pl/sql 已关闭，否则您不需要 CURSOR

CREATE FUNCTION f_birim_cevrim_katsayi (
    @p_ID_MAMUL                               FLOAT ,
    @p_ID_BIRIMDEN                            FLOAT ,
    @p_ID_BIRIME                              FLOAT 
)
RETURNS float 
AS 
BEGIN
    DECLARE @v_katsayi FLOAT;
    IF @p_ID_BIRIMDEN != @p_ID_BIRIME 
    BEGIN 
        SELECT
             @v_katsayi =  CASE
                 WHEN ID_BIRIM = @p_ID_BIRIMDEN THEN c.MT_ORAN 
                 ELSE 1/c.MT_ORAN 
             END
        FROM 
             CR_BIRIM_CEVRIM c
        WHERE
             ID_MAMUL  = @p_ID_MAMUL
             AND    ((ID_BIRIM  = @p_ID_BIRIMDEN
             AND    ID_BIRIM2  = @p_ID_BIRIME)
             OR (ID_BIRIM2  = @p_ID_BIRIMDEN
             AND    ID_BIRIM  = @p_ID_BIRIME))
             AND    VALID  = 1;
    END
    RETURN ROUND(ISNULL(@v_katsayi, 1), 10);
END
GO

Answer 2

尝试将这两行更改

SELECT @v_katsayi  = 0 

SELECT @v_katsayi  = 1 

为

SET @v_katsayi  = 0 

SET @v_katsayi  = 1 

“错过了其他几行”。无论您在哪里使用 SELECT 简单地设置变量值，请改为 SET...

另外：更改光标以抓取字段并将其放入变量中

DECLARE @theID FLOAT
DECLARE @theORAN FLOAT

DECLARE cursor_for_inline_select1 CURSOR LOCAL FOR 
                SELECT id_birim,MT_ORAN  -- Fields name instead of *
                FROM  CR_BIRIM_CEVRIM 
                WHERE    ID_MAMUL  = @p_ID_MAMUL
                 AND    ((ID_BIRIM  = @p_ID_BIRIMDEN
                 AND    ID_BIRIM2  = @p_ID_BIRIME)
                 OR (ID_BIRIM2  = @p_ID_BIRIMDEN
                 AND    ID_BIRIM  = @p_ID_BIRIME))
                 AND    VALID  = 1

                OPEN cursor_for_inline_select1 
                -- Put field value into variable
                FETCH NEXT FROM  cursor_for_inline_select1  INTO @theID,@theORan
                WHILE (@@FETCH_STATUS <> -1)
                BEGIN
                    -- Compare variable rather than field directly
                    IF   @theID = @p_ID_BIRIMDEN 
                    BEGIN 
                        SELECT @v_katsayi  = @theORan 
                    END
                    ELSE
                    BEGIN 
                        SELECT @v_katsayi  = 1/@theORan 
                    END

                END
                CLOSE cursor_for_inline_select1
                DEALLOCATE cursor_for_inline_select1