整个早上我们都被这个难住了。

我有一个处理一些记录的 MSSQL 存储过程。每条记录都会生成一封包含动态内容的电子邮件，这是从另一个存储过程中填充的。

所以第一个存储过程有一个游标，处理每一行都会导致调用另一个存储过程，它本身有一个要循环的游标。第一个存储过程没有输出参数或返回值等，而第二个使用输出参数将字段返回给第一个过程。

这在 Datagrip 中运行良好。

使用 PDO(或使用其他驱动程序)从 php 调用它无法完全运行。它会生成一小批记录，然后停止(往往是 5、9、13 或 45 - 随着我们尝试不同的解决方案而发生变化)。

我们已经设法让它现在使用 PDOStatement::nextRowset 运行。我们使用第一个存储过程的查询，然后使用 while ( $stmt->nextRowset() ) ; 循环遍历(不存在/不需要的)行集。

这行得通。但是由于第一个存储过程没有返回任何东西(只是那个 pdo 似乎想要处理某种内部结果集)这看起来很脏而且效率很低。

有替代方案吗？可能是要传递给 pdo 的参数，还是存储过程中的设置？

下面有一些简化的代码来展示事物是如何相互作用的。

PHP 调用脚本。

<?php
$emailRepository = new EmailRepository(hostname, port, dbname, username, password);

$ret = $emailRepository->sendRenewalEmails();

class EmailRepository
{

    public function __construct($hostname, $port, $dbname, $username, $password)
    {
        $this->hostname = $hostname;
        $this->port = $port;
        $this->dbname = $dbname;
        $this->username = $username;
        $this->password = $password;
        $this->connect();
    }

    private function connect()
    {
        try {
            $this->db = new PDO ("sqlsrv:Server=$this->hostname,$this->port;Database=$this->dbname", $this->username, $this->password);
            $this->db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        } catch (PDOException $e) {
            var_dump("Failed to get DB handle: " . $e->getMessage());
        }
    }

    public function sendRenewalEmails()
    {
      try {
        $stmt = $this->db->prepare("EXEC membership_queue_renewal_emails");
        $stmt->execute();
        do {
          echo '>';
        } while($stmt->nextRowset());
        return true;
      } catch (Exception $e) {
        echo $e->getMessage();
      }
    }
}

第一个存储过程(大幅缩减)

CREATE PROCEDURE [dbo].[queue_renewal_emails]
AS
BEGIN

    DECLARE @curr_member_cursor     CURSOR;
    DECLARE @curr_club_cursor       CURSOR;
    DECLARE @g_personid             INT;

    DECLARE @g_emailTemplateId      INT;
    DECLARE @g_email_subject        VARCHAR(200);
    DECLARE @g_email_html           VARCHAR(max);
    DECLARE @g_email_plain          VARCHAR(max);

    DECLARE @g_personEmail          VARCHAR(128);

    SET @curr_club_cursor = CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT  DISTINCT
            bgemailTemplate.bgemte_name,
            bgemailTemplate.bgemte_emailtemplateid,
            vpersonpe.pers_emailaddress,
            vpersonpe.pers_personId,
    FROM  company               WITH(NOLOCK)
    INNER JOIN complink             WITH(NOLOCK)    ON complink.clli_companyid = complink.Comp_CompanyId
    AND complink.clli_Deleted is null
    INNER JOIN vpersonpe            WITH(NOLOCK)    ON vpersonpe.pers_personId = complink.clli_personId
    INNER JOIN bgemailTemplate      WITH(NOLOCK)    ON bgemailTemplate.bgemte_Deleted IS NULL
    WHERE vPersonPE.pers_deleted                    IS NULL
    AND   company.comp_deleted                      IS NULL
    AND   vPersonPE.pers_parentid                   IS NULL
    AND   vpersonpe.pers_status                     NOT IN ('Cancelled','Expired','Suspended','Awaiting Approval','Declined','On hold','Revoked','Expelled');

    -- loop through each course
    OPEN @curr_club_cursor;
    FETCH NEXT FROM @curr_club_cursor INTO @g_email_subject, @g_emailTemplateId, @g_personEmail, @g_personid;
    WHILE @@fetch_status = 0
    BEGIN
        EXEC dbo.populateEmail @g_emailtemplateid   /* Email template id */,
                                @g_email_plain OUTPUT /* Plain text email to have the placeholders replaced */,
                                @g_email_subject OUTPUT,
                                @g_personid ;
        FETCH NEXT FROM @curr_club_cursor INTO @g_email_subject, @g_emailTemplateId, @g_personEmail, @g_personid;
    END
    CLOSE @curr_club_cursor
    DEALLOCATE @curr_club_cursor

END
go

第二个存储过程(大大减少)。

CREATE PROCEDURE [dbo].[populateEmail]
    @p_emailtemplateid          INT,
    @p_email_text               VARCHAR(max) OUTPUT,
    @p_email_subject            VARCHAR(200) OUTPUT,
    @p_person_id                INT
AS
BEGIN

    SET NOCOUNT ON;

    -- CURSORs
    DECLARE @curr_field_cursor                  CURSOR;
    DECLARE @g_email_plain                      VARCHAR(MAX) = '';
    DECLARE @g_email_subject                    VARCHAR(200) = '';

    DECLARE @g_emte_emailtemplateid             INT;
    DECLARE @g_EmailPlaceholderId               INT;
    DECLARE @g_place_holder                     VARCHAR(128);
    DECLARE @g_source_column                    VARCHAR(128);
    DECLARE @g_prev_source_query_num            INT;
    DECLARE @g_source_query_num                 INT;

    -- Variables to read results into from each query
    DECLARE @g_q11_comp_name                    VARCHAR(180);
    DECLARE @g_q11_comp_website                 VARCHAR(300);
    DECLARE @g_q11_comp_pers_salutation         VARCHAR(30);
    DECLARE @g_q11_comp_pers_firstname          VARCHAR(90);
    DECLARE @g_q11_comp_pers_lastname           VARCHAR(120);
    -- Start processing

    SET @p_email_text = '';

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SET @curr_field_cursor = CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT a.emte_emailtemplateid,
            b.emtl_EmailPlaceholderId,
            c.empl_PlaceHolder,
            c.empl_SourceQueryNum,
            c.empl_SourceColumn,
            a.emte_plaintextemail,
            a.emte_subject
    FROM EmailTemplate a with (nolock)
    LEFT OUTER JOIN  EmailTemplateLink b with (nolock)
    ON a.emte_emailtemplateid = b.emtl_EmailTemplateId
    LEFT OUTER JOIN  EmailPlaceholder c with (nolock)
    ON b.emtl_EmailPlaceholderId = c.empl_EmailPlaceholderID
    WHERE a.emte_emailtemplateid = @p_emailtemplateid
    ORDER BY c.empl_SourceQueryNum;

    -- Loop through each required place holder for the passed email template.

    SET @g_prev_source_query_num = 0;

    OPEN @curr_field_cursor
    FETCH NEXT FROM @curr_field_cursor INTO @g_emte_emailtemplateid, @g_EmailPlaceholderId, @g_place_holder, @g_source_query_num, @g_source_column, @g_email_plain, @g_email_subject;
    WHILE @@fetch_status = 0
    BEGIN

        IF @g_prev_source_query_num = 0
        BEGIN
            SET @p_email_text = @g_email_plain;
            SET @p_email_subject = @g_email_subject;
        END;

        IF @g_source_query_num = 11
        BEGIN
            IF @g_prev_source_query_num != @g_source_query_num
            BEGIN
                SELECT @g_q11_comp_name = comp_name,
                        @g_q11_comp_website = comp_website,
                        @g_q11_comp_pers_salutation = Pers_Salutation,
                        @g_q11_comp_pers_firstname = pers_firstname,
                        @g_q11_comp_pers_lastname = pers_lastname
                FROM company with (nolock)
                LEFT OUTER JOIN vPerson with (nolock) ON company.Comp_PrimaryPersonId = vPerson.Pers_PersonId
                LEFT OUTER JOIN address with (nolock) ON company.Comp_PrimaryAddressId = address.Addr_AddressId
                WHERE company.Comp_CompanyId        = @p_person_id;
            END;

            IF @g_source_column = 'comp_name'
            BEGIN
                SET @p_email_text       = REPLACE(@p_email_text, @g_place_holder, COALESCE(@g_q11_comp_name, ''));
                SET @p_email_subject    = REPLACE(@p_email_subject, @g_place_holder, COALESCE(@g_q11_comp_name, ''));
            END;
            ELSE IF @g_source_column = 'comp_website'
            BEGIN
                SET @p_email_text       = REPLACE(@p_email_text, @g_place_holder, COALESCE(@g_q11_comp_website, ''));
                SET @p_email_subject    = REPLACE(@p_email_subject, @g_place_holder, COALESCE(@g_q11_comp_website, ''));
            END;
            ELSE IF @g_source_column = 'comp_primary_person_firstname'
            BEGIN
                SET @p_email_text       = REPLACE(@p_email_text, @g_place_holder, COALESCE(@g_q11_comp_pers_salutation, ''));
                SET @p_email_subject    = REPLACE(@p_email_subject, @g_place_holder, COALESCE(@g_q11_comp_pers_salutation, ''));
            END;
            ELSE IF @g_source_column = 'comp_primary_person_salutation'
            BEGIN
                SET @p_email_text       = REPLACE(@p_email_text, @g_place_holder, COALESCE(@g_q11_comp_pers_firstname, ''));
                SET @p_email_subject    = REPLACE(@p_email_subject, @g_place_holder, COALESCE(@g_q11_comp_pers_firstname, ''));
            END;
            ELSE IF @g_source_column = 'comp_primary_person_lastname'
            BEGIN
                SET @p_email_text       = REPLACE(@p_email_text, @g_place_holder, COALESCE(@g_q11_comp_pers_lastname, ''));
                SET @p_email_subject    = REPLACE(@p_email_subject, @g_place_holder, COALESCE(@g_q11_comp_pers_lastname, ''));
            END;
        END;

        SET @g_prev_source_query_num = @g_source_query_num;
        FETCH NEXT FROM @curr_field_cursor INTO @g_emte_emailtemplateid, @g_EmailPlaceholderId, @g_place_holder, @g_source_query_num, @g_source_column, @g_email_plain, @g_email_subject;
    END;

    CLOSE @curr_field_cursor
    DEALLOCATE @curr_field_cursor

END
go

最佳答案

as the first stored proc isn't returning anything ... is there an alternative to while ( $stmt->nextRowset() ) ;

可能是行计数消息导致客户端看到空行集。添加 SET NOCOUNT ON 作为每个存储过程的第一行。

关于php - 从 php 调用 MSSQL 存储过程，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/57360872/