使用游标循环和fetch将PL SQL函数转换为T-SQL

Question

该函数的代码如下：

CREATE OR REPLACE FUNCTION FTTH_GETBUSZONECODEMULTI(
    p_house_nbr        IN VARCHAR2,
    p_as_of_date       IN DATE DEFAULT SYSDATE)
RETURN VARCHAR2
AS
    CURSOR l_get_cur
    IS
         SELECT
            LTRIM(RTRIM(BZO.B2FEDC)) BusinessZoneCode
           FROM
           [not wasting your time with business logic]
    l_return VARCHAR2(32767);
BEGIN
    --
    FOR l_get_rec IN l_get_cur LOOP
       l_return := l_return || '|' || l_get_rec.BusinessZoneCode;
    END LOOP;
    --
    CASE
              WHEN l_return IS NULL THEN RETURN NULL;
              ELSE RETURN l_return || '|';
    END CASE;
    --
END FTTH_GETBUSZONECODEMULTI;

我的翻译尝试如下：

CREATE FUNCTION ftth_GETBUSZONECODEMULTI(
    @p_house_nbr        VARCHAR(4000),
    @p_as_of_date       DATETIME)
RETURNS VARCHAR(4000)
AS
BEGIN
   SET @p_as_of_date = GETDATE()
    DECLARE l_get_cur CURSOR LOCAL
    FOR
         SELECT
            LTRIM(RTRIM(BZO.B2FEDC)) BusinessZoneCode
           FROM
            [not wasting your time with business logic]
    DECLARE @l_return VARCHAR(MAX);
 
    --
    SET @l_return = isnull(@l_return, '') + '|' + ISNULL((FETCH BusinessZoneCode from l_get_cur), '');

    --
    if @l_return IS NULL begin RETURN NULL END
    if @l_return is not null BEGIN RETURN isnull(@l_return, '') + '|' END;
END

问题是，我想，我是如何尝试获取值的-即使我在其中放了一个"NEXT“，它也不能正常工作。我已经尝试了6种不同的方法来安排获取，但都不起作用。

Answer 1

以下是游标用法的一些变化，我可能弄错了游标循环结束的位置，因此您可能需要调整它：

CREATE FUNCTION ftth_GETBUSZONECODEMULTI(
    @p_house_nbr        VARCHAR(4000),
    @p_as_of_date       DATETIME)
RETURNS VARCHAR(4000)
AS
BEGIN
    declare @BusinessZoneCode varchar(500)
   SET @p_as_of_date = GETDATE()
    DECLARE l_get_cur CURSOR LOCAL
    FOR
         SELECT
            LTRIM(RTRIM(BZO.B2FEDC)) BusinessZoneCode
           FROM
            [not wasting your time with business logic]

    OPEN l_get_cur
    fetch next from l_get_cur
    into @BusinessZoneCode

    WHILE @@FETCH_STATUS = 0  
    begin 
        DECLARE @l_return VARCHAR(MAX);
 
        --
        SET @l_return = isnull(@l_return, '') + '|' + ISNULL((@BusinessZoneCode), '');

        --
        if @l_return IS NULL begin RETURN NULL END
        if @l_return is not null BEGIN RETURN isnull(@l_return, '') + '|' END

        fetch next from l_get_cur
        into @BusinessZoneCode
    end
    close l_get_cur
    deallocate l_get_cur
END